MySQL：SELECT语句添加字段导致性能下降的原因与解决

我有一张包含数百万行数据的表my_table，主键为id，同时存在一个名为comp_indx的复合唯一索引，包含col2、col3、col4和my_date字段。

表定义如下：

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col2` smallint(6) NOT NULL,
  `col3` smallint(6) NOT NULL,
  `col4` smallint(6) NOT NULL,
  `my_date` datetime NOT NULL,
  `col5` char(1) NOT NULL,
  `col6` char(1) NOT NULL,
  `col7` char(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `comp_indx` (`col2`,`col3`,`col4`,`my_date`)
) ENGINE=InnoDB;

示例数据：

id   col2 col3 col4 my_date             col5 col6 col7
1    1    1    1    2020-01-03 02:00:00 a    1    a
2    1    2    1    2020-01-03 01:00:00 b    2    1
3    1    3    1    2020-01-03 03:00:00 c    3    b
4    2    1    1    2020-02-03 01:00:00 d    4    2
5    2    2    1    2020-02-03 02:00:00 e    5    c
6    2    3    1    2020-02-03 03:00:00 f    6    3
7    3    1    1    2020-03-03 03:00:00 g    7    d
8    3    2    1    2020-03-03 02:00:00 h    8    4
9    3    3    1    2020-03-03 01:00:00 i    9    e

执行以下查询时效率极高：

SELECT col2, col3, max(my_date)
FROM my_table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3

对应的EXPLAIN结果：

select_type type  key       key_len rows Extra
----------- ----- --------- ------- ---- -------------------------------------
SIMPLE      range comp_indx 11      669  Using where; Using index for group-by

但当查询增加非索引字段col5、col7后，性能大幅下降：

SELECT col2, col3, max(my_date), col5, col7
FROM my_table
where col4=1 and my_date <= '2001-01-27'
group by col2, col3

对应的EXPLAIN结果：

select_type type  key       key_len rows     Extra
----------- ----- --------- ------- -------  -----------
SIMPLE      index comp_indx 11      5004953  Using where

可见查询类型从range变为index，且索引不再用于分组操作。需要了解该现象的原因及解决办法。

• 覆盖索引失效：第一个查询只用到了复合索引comp_indx里的字段（col2、col3、col4、my_date），属于覆盖索引查询，MySQL可以直接通过索引完成筛选、分组和聚合，不需要回表读取原数据，所以效率极高，且能利用索引排序特性完成分组（对应Using index for group-by）。
• 被迫全索引扫描+回表：第二个查询需要返回非索引字段col5、col7，此时覆盖索引无法满足需求，MySQL需要先通过索引找到对应行，再回表读取额外数据。优化器此时选择了全索引扫描（type: index）而非range，因为它判断全扫索引后回表的代价比先做range筛选再回表更低，但实际导致扫描行数暴增，性能骤降。
• 分组逻辑无法复用索引：当需要回表时，MySQL无法再依赖索引的有序性高效分组，必须先读取所有符合条件的数据，再在内存或临时表中完成分组聚合，进一步拖慢了速度。

方案1：扩展复合索引为覆盖索引 #

把需要查询的非索引字段col5、col7加入到复合索引末尾，让查询重新成为覆盖索引查询：

-- 保持唯一约束的版本
ALTER TABLE my_table ADD UNIQUE KEY `comp_indx_extended` (`col2`,`col3`,`col4`,`my_date`, `col5`, `col7`);

-- 不需要唯一约束的普通索引版本
ALTER TABLE my_table ADD INDEX `comp_indx_extended` (`col2`,`col3`,`col4`,`my_date`, `col5`, `col7`);

这样MySQL可以直接从索引中获取所有需要的字段，不需要回表，同时依然能利用索引的有序性完成分组和range筛选，恢复高效查询。

方案2：先聚合再关联获取非索引字段 #

先通过原覆盖索引完成分组聚合，得到每个col2,col3对应的max(my_date)，再关联原表获取col5、col7。因为原索引是唯一索引，col2,col3,max(my_date)组合对应唯一行，不会出现多行匹配：

SELECT t1.col2, t1.col3, t1.max_date, t2.col5, t2.col7
FROM (
    SELECT col2, col3, max(my_date) as max_date
    FROM my_table
    WHERE col4=1 and my_date <= '2001-01-27'
    GROUP BY col2, col3
) t1
JOIN my_table t2 
ON t1.col2 = t2.col2 
AND t1.col3 = t2.col3 
AND t1.max_date = t2.my_date
WHERE t2.col4=1;

子查询会利用原有的comp_indx高效完成聚合，然后通过唯一索引快速关联到对应行获取额外字段，避免全索引扫描。

方案3：调整索引顺序（可选） #

如果col4的筛选条件固定（比如经常查询col4=1），可以把col4放在索引最前面，提升range筛选的效率：

ALTER TABLE my_table ADD UNIQUE KEY `comp_indx_optimized` (`col4`,`col2`,`col3`,`my_date`, `col5`, `col7`);

这个方案仅适用于col4筛选值比较固定的场景，否则收益有限。

内容的提问来源于stack exchange，提问作者Wannabe-Coder