MySQL 530万行多对多关联表IN+HAVING慢查询优化求助

• 优先创建覆盖联合索引 (post_id, main_id)，该索引完全匹配你的查询逻辑：前缀post_id可以快速过滤WHERE子句中IN条件对应的行，后续main_id字段可以直接用于GROUP BY分组，不需要回表查询主数据，COUNT运算也可以直接在索引上完成，性能会有量级提升。
• 如果你使用的是InnoDB引擎，建议直接将(post_id, main_id)设为表的联合主键，主键聚集索引的查询性能优于普通二级索引。如果有反向查询（按main_id查关联post_id）的需求，可以额外补充二级索引 (main_id, post_id)。

• 移除IN子句中数值的多余引号：如果post_id是整数类型，将IN ('134','140','187')改为IN (134,140,187)，避免隐式类型转换导致索引失效。
• 简化HAVING计数逻辑：只要你的posts_tag表保证(post_id, main_id)唯一无重复行，就可以将COUNT(DISTINCT post_id) = 3改为COUNT(*) = 3，省去去重运算的开销，IN子句有多少个元素，就将等值条件的数值改为对应数量即可。
• 长IN场景优化：如果IN子句中的ID数量超过50个，可以构造临时表存储需要查询的post_id，再和posts_tag表做JOIN查询，效率优于超长IN子句。

• 两个关联字段统一使用无符号整数类型，不要使用字符串类型，缩小字段存储体积，降低索引占用空间，提升查询效率。
• 读多写少场景可以新增预聚合汇总表：提前存储每个main_id对应的所有post_id集合（可以用BITMAP、JSON或者SET类型存储），查询时直接判断目标post_id是否全部存在于集合中，不需要每次做分组聚合，查询效率可以提升10倍以上，写入时同步更新汇总表即可。
• 数据量持续增长的话可以做分表：按照post_id哈希分表或者main_id范围分表，每次查询只扫描对应分表的数据，避免全表扫描530万行数据。

• 调整innodb_buffer_pool_size为物理内存的50%_{70%（你的服务器可以设为32G}45G），让热点索引和数据完全缓存在内存中，避免磁盘IO开销。
• 适当调大sort_buffer_size和tmp_table_size参数，避免GROUP BY分组时产生临时磁盘表，提升分组运算效率。

• 新增NOT IN条件时，建议改为LEFT JOIN + IS NULL的写法，性能优于NOT IN，同时可以避免NULL值带来的逻辑错误。比如要排除关联了post_id=200的main_id，写法为：

  SELECT t1.main_id FROM posts_tag t1
  LEFT JOIN posts_tag t2 ON t1.main_id = t2.main_id AND t2.post_id = 200
  WHERE t1.post_id IN (134,140,187) AND t2.main_id IS NULL
  GROUP BY t1.main_id
  HAVING COUNT(*) = 3
  

• 关联其他同结构表时，保证关联字段类型完全一致，且都有对应的索引，查询时优先过滤数据量小的表，用小结果集驱动大表运算。

内容的提问来源于stack exchange，提问作者alexfsk