MySQL针对不同SECURITY_ID未选择最优索引问题排查

背景回顾 #

先梳理下你的场景细节：

表结构 #

CREATE TABLE `idx_weight` ( 
    `ID` bigint(20) NOT NULL AUTO_INCREMENT, 
    `SECURITY_ID` bigint(20) NOT NULL COMMENT, 
    `CONS_ID` bigint(20) NOT NULL, 
    `EFF_DATE` date NOT NULL, 
    `WEIGHT` decimal(9,6) DEFAULT NULL, 
    PRIMARY KEY (`ID`), 
    UNIQUE KEY `BPK_AK` (`SECURITY_ID`,`CONS_ID`,`EFF_DATE`), 
    KEY `idx_weight_ix` (`SECURITY_ID`,`EFF_DATE`) 
) ENGINE=InnoDB AUTO_INCREMENT=75334536 DEFAULT CHARSET=utf8

两个查询的执行计划差异 #

查询1（SECURITY_ID=1782）

SQL语句：

explain select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight where security_id = 1782

执行计划：

+----+-------------+------------+------+----------------------+---------------+---------+-------+--------+-------------+
| id | select_type | table      | type | possible_keys        | key           | key_len | ref   | rows   | Extra       |
+----+-------------+------------+------+----------------------+---------------+---------+-------+--------+-------------+
| 1  | SIMPLE      | idx_weight | ref  | BPK_AK,idx_weight_ix | idx_weight_ix | 8       | const | 887856 | Using index |
+----+-------------+------------+------+----------------------+---------------+---------+-------+--------+-------------+

查询2（SECURITY_ID=26622）

SQL语句：

explain select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight where security_id = 26622

执行计划：

+----+-------------+------------+------+----------------------+--------+---------+-------+----------+-------------+
| id | select_type | table      | type | possible_keys        | key    | key_len | ref   | rows      | Extra       |
+----+-------------+------------+------+----------------------+--------+---------+-------+----------+-------------+
| 1  | SIMPLE      | idx_weight | ref  | BPK_AK,idx_weight_ix | BPK_AK | 8       | const | 10700002 | Using index |
+----+-------------+------------+------+----------------------+--------+---------+-------+----------+-------------+

临时解决方案（添加GROUP BY） #

SQL语句：

explain select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate from idx_weight where security_id = 26622 group by security_id

执行计划：

+----+-------------+------------+-------+----------------------+---------------+---------+------+-------+---------------------------------------+
| id | select_type | table      | type  | possible_keys        | key           | key_len | ref  | rows  | Extra                                 |
+----+-------------+------------+-------+----------------------+---------------+---------+------+-------+---------------------------------------+
| 1  | SIMPLE      | idx_weight | range | BPK_AK,idx_weight_ix | idx_weight_ix | 8       | NULL | 10314 | Using where; Using index for group-by |
+----+-------------+------------+-------+----------------------+---------------+---------+------+-------+---------------------------------------+

核心原因拆解 #

1. 索引统计信息的偏差是根源 #

你注意到的同一列SECURITY_ID在两个索引中的基数（Cardinality）不一致，是问题的核心：

+------------+---------------+--------------+-------------+-------------+
| NON_UNIQUE | INDEX_NAME    | SEQ_IN_INDEX | COLUMN_NAME | CARDINALITY |
+------------+---------------+--------------+-------------+-------------+
| 0          | BPK_AK        | 1            | SECURITY_ID | 74134       |
| 0          | BPK_AK        | 2            | CONS_ID     | 638381      |
| 0          | BPK_AK        | 3            | EFF_DATE    | 68945218    |
| 1          | idx_weight_ix | 1            | SECURITY_ID | 61393       |
| 1          | idx_weight_ix | 2            | EFF_DATE    | 238564      |
+------------+---------------+--------------+-------------+-------------+

InnoDB的索引统计是通过采样生成的，并非实时精确值。如果两个索引的采样时机、采样范围不同，就会出现基数差异。对于SECURITY_ID=26622，优化器基于BPK_AK的高基数（74134）预估了10700002行，错误地认为BPK_AK的成本更低——但实际上idx_weight_ix是更紧凑的覆盖索引（仅包含SECURITY_ID和EFF_DATE，而BPK_AK还要携带CONS_ID），磁盘IO成本远低于前者。

2. 无GROUP BY时的优化器逻辑盲区 #

当没有指定GROUP BY时，MySQL把查询视为单组聚合。此时优化器只会评估“扫描所有匹配行并计算min/max”的成本，不会利用idx_weight_ix的有序性做优化。而添加GROUP BY SECURITY_ID后，触发了Using index for group-by优化：因为idx_weight_ix是SECURITY_ID,EFF_DATE的有序索引，同一SECURITY_ID的EFF_DATE是按顺序存储的，优化器可以直接定位到该值的第一条和最后一条记录，不需要扫描所有匹配行，所以预估行数骤降，效率大幅提升。

3. 缓冲池只是表象问题 #

你提到首次查询耗时超1分钟，第二次缩到10秒，确实是因为首次访问BPK_AK时数据不在InnoDB缓冲池，需要从磁盘读取；第二次数据被缓存后耗时降低。但这只是索引选择错误带来的附加问题，核心还是优化器的统计偏差导致了错误的索引选择。

可行解决方案 #

1. 更新索引统计信息：执行ANALYZE TABLE idx_weight;，让InnoDB重新采样计算索引基数，消除两个索引的统计差异，帮助优化器做出正确选择。
2. 强制指定索引：如果更新统计后仍有问题，可以用FORCE INDEX强制使用更优的idx_weight_ix：

   select SECURITY_ID, min(EFF_DATE) as startDate, max(EFF_DATE) as endDate 
   from idx_weight FORCE INDEX(idx_weight_ix) 
   where security_id = 26622;
   

3. 保留GROUP BY写法：由于添加GROUP BY SECURITY_ID的语义和原查询一致（WHERE已限定单个SECURITY_ID），且能触发更高效的索引优化，可以将这个写法作为长期方案。

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