已建索引的多值WHERE子句查询缓慢的原因与优化方法

问题背景 #

有一张checked_result表，包含idx、A、B、C四列，数据量20万+。已创建16个索引，涵盖单列索引（idx、A、B、C）、双列组合索引（AB、BC、AC）、三列组合索引（ABC），以及idx与其他列的组合索引（(idx,A)、(idx,B)、(idx,C)、(idx,A,B)、(idx,A,C)、(idx,B,C)、(idx,A,B,C)）。

单值过滤的查询速度很快：

SELECT * FROM checked_result WHERE (A in ('123')) AND idx >= 0 ORDER BY B DESC LIMIT 10
SELECT * FROM checked_result WHERE (A in ('123')) AND idx >= 0 ORDER BY B DESC, C ASC LIMIT 10

但当A列使用多值IN过滤时，查询速度极慢：

SELECT * FROM checked_result WHERE (A in ('123','456')) AND idx >= 0 ORDER BY B DESC
SELECT * FROM checked_result WHERE (A in ('123','456')) AND idx >= 0 ORDER BY B DESC, C ASC LIMIT 10

执行计划对比 #

分别执行以下两条语句：

EXPLAIN QUERY PLAN SELECT * FROM checked_result WHERE (A in ('123')) AND idx >= 0
EXPLAIN QUERY PLAN SELECT * FROM checked_result WHERE (A in ('123','456')) AND idx >= 0

得到的执行计划结果：

• 单值IN：SEARCH TABLE checked_result USING INDEX idx_checked_result_A_B (A=?)
• 多值IN：SCAN TABLE checked_result USING INDEX idx_checked_result_A_B_C

原因分析 #

1. 单值IN的高效索引利用：当A是单值时，数据库可以通过idx_checked_result_A_B（A,B组合索引）精准定位所有A='123'的行，且索引本身按A、B排序，刚好匹配ORDER BY B DESC的需求，加上LIMIT 10，只需从索引中取前10条符合条件的数据即可，无需全量扫描或额外排序，因此速度快。
2. 多值IN的索引选择偏差：当A是多值时，数据库优化器判断需要匹配多个A值，可能认为直接扫描idx_checked_result_A_B_C索引的成本更低，但这种扫描是范围扫描而非精准定位，无法直接利用索引顺序满足多A值的合并排序需求。另外idx >=0的条件几乎无过滤效果（通常idx为自增ID，大部分行都满足），优化器会忽略该条件对应的索引。
3. 排序成本激增：多值IN匹配的行数远多于单值，若没有合适的索引直接提供排序后的结果，数据库需要先取出所有符合条件的行，再进行ORDER BY排序，20万+数据的排序会消耗大量CPU和内存，导致查询变慢。

优化方案 #

• 创建针对性组合索引
  针对查询的过滤条件和排序需求，创建**(A, B DESC, C ASC)**组合索引。该索引的优势：

  • 先按A分组，每个A值对应的行按B降序、C升序排列，完美匹配ORDER BY B DESC, C ASC的要求；
  • 对于多值IN查询，数据库可分别在每个A值对应的索引段中按顺序取数据，合并多个A值的结果集后，通过LIMIT 10快速返回前10条数据，无需额外排序。

  创建索引语句：

  CREATE INDEX idx_A_Bdesc_Casc ON checked_result (A, B DESC, C ASC);
  

• 调整查询逻辑（可选）
  如果无法新增索引，可将多值IN拆分为多个单值查询，用UNION ALL合并结果后再排序取TOP 10：

  SELECT * FROM (
      SELECT * FROM checked_result WHERE A='123' AND idx >=0 ORDER BY B DESC, C ASC
      UNION ALL
      SELECT * FROM checked_result WHERE A='456' AND idx >=0 ORDER BY B DESC, C ASC
  ) t ORDER BY B DESC, C ASC LIMIT 10;
  

  这种方式能让每个子查询都利用idx_checked_result_A_B索引快速获取数据，再合并排序，避免全表扫描。

• 清理冗余索引
  现有16个索引存在大量冗余（比如AB索引和ABC索引，当查询用到A和B时，ABC索引也能被利用，但冗余索引会增加写入开销）。建议仅保留必要索引：

  • 主键索引（idx）；
  • 常用查询的组合索引（如上述的(A, B DESC, C ASC)）；
  • 其他高频查询对应的索引。

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