MySQL同表两次查询场景下的多列索引优化方案咨询

问题背景 #

我有如下MySQL查询语句：

SELECT * FROM table1
WHERE table1.column_1 = 'a' AND table1.column_2 = 'b'
AND :idList IN 
(SELECT id FROM table1 WHERE table1.column_3 = 'c' AND table1.column_4 = 'd')

其中:idList是查询所在函数中传入的参数。

由于table1数据量庞大且涉及列无任何索引，需要添加索引提升查询速度。已知若添加(column_1, column_2)和(column_3, column_4)两个独立索引，查询可能仅会使用其中一个。目前我尝试了两种方案：

• 先添加索引(column_1, column_2)执行查询，删除后再添加(column_3, column_4)执行查询，选择速度最快的方案
• 添加联合索引(column_1, column_2, column_3, column_4)

我知道可以用EXPLAIN分析，但当前场景下帮助不大，请问正确的索引添加方案是什么？

优化方案建议 #

• 优先创建两个独立的覆盖索引
  针对查询的两个过滤分支，分别创建覆盖索引：

  • 主查询分支：(column_1, column_2, id) —— 主查询通过column_1和column_2过滤数据，同时需要匹配子查询返回的id，将id纳入索引可避免回表操作，直接从索引中获取所需数据。
  • 子查询分支：(column_3, column_4, id) —— 子查询仅需获取符合column_3='c'和column_4='d'的id列表，该索引能让子查询直接从索引提取id，无需扫描全表回表。

  这种方案下，MySQL优化器可以分别调用两个索引处理主查询和子查询，解决只能单索引生效的问题，同时覆盖索引能大幅降低IO开销，提升查询效率。

• 不推荐使用四列联合索引
  四列联合索引(column_1, column_2, column_3, column_4)的适用场景极窄：只有当查询同时命中前导列column_1、column_2，且后续列过滤条件完全匹配时才会高效。但你的查询中，主查询和子查询的过滤列无重叠前导列，该索引对其中一个分支的过滤效果极差，甚至可能不被优化器选用。

• 优化测试方案
  你之前的测试仅验证了单个索引的效果，但最优场景是同时存在两个覆盖索引的情况。建议直接创建这两个覆盖索引后测试查询速度，而非交替删除/创建单个索引，这样才能真实反映优化后的性能。

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