MySQL IN条件传20个参数时索引失效查询慢如何优化

这个现象完全属于MySQL优化器的正常工作表现，不存在异常。
优化器选择执行计划的核心依据是成本估算：

1. 你的community_code字段区分度仅0.0019，换算下来全表该字段总共只有约85个不同取值，单个编码平均对应500+行数据
2. 走该字段二级索引查询整行数据时，除了索引本身的扫描成本，还需要加上回表查询主键对应整行数据的随机IO成本
3. 当IN条件传入5个参数时，优化器估算走索引需要回表的总行数约2500行，成本远低于全表扫描4.5万行的成本，因此选择索引
4. 当IN条件传入20个参数时，优化器估算需要回表的总行数超过1万行，叠加随机IO的高成本，总估算成本超过全表顺序扫描的成本，因此直接选择全表扫描

• 强制指定索引：如果业务上确认这20个编码实际匹配的行数远低于全表总行数，可以直接在SQL中加hint强制走索引，绕过优化器的成本判断，写法参考：

SELECT
    t.* 
FROM
    table t FORCE INDEX (index_community_code)
WHERE
    t.community_code IN ('13091264', '13091266', ......)

• 避免查询全字段：不要使用SELECT t.*的写法，只返回业务实际需要的列。如果查询的所有列都能被包含在索引中，会触发覆盖索引，完全省去回表的IO成本，不仅查询速度会大幅提升，优化器选择索引的概率也会显著提高。
• 拆分IN查询：在应用层把20个查询参数拆分为多组，每组控制在5个参数以内，多次查询走索引后再在应用层合并结果，不需要修改数据库配置就能稳定触发索引。
• 优化索引结构：community_code本身区分度极低，单独建索引的效率天然较差。如果业务中该字段通常会和其他查询条件组合使用，可以建立联合索引，把区分度更高的查询字段放在联合索引的最左侧，提升索引筛选效率。
• 调整数据库参数：可以适当调低实例的max_seeks_for_key参数，让优化器降低对索引查找的成本估算，使其更倾向于选择索引；如果是因为IN参数过多触发优化器改用统计值估算导致成本计算偏差，也可以调整eq_range_index_dive_limit参数，保证20个参数范围内仍然使用精确的index dive方式计算成本。注意全局参数调整前必须评估对库内其他业务SQL的影响，避免出现大面积执行计划异常。

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