MySQL 8.0.33中为何该SQL触发最左前缀匹配异常？

问题背景 #

现有MySQL 8.0.33环境下，student表结构如下：

CREATE TABLE `student`
(
    `id`     int(11)                            NOT NULL AUTO_INCREMENT,
    `stu_no` varchar(12)                        NOT NULL,
    `age`    int(11)                            NOT NULL,
    `name`   varchar(255) CHARACTER SET utf8mb4 NULL,
    PRIMARY KEY (`id`),
    KEY `idx_stu_no` (`stu_no`),
    KEY `idx_stu_name` (`name`),
    KEY `idx_stu_no_age` (`stu_no`, `age`) USING BTREE,
    KEY `idx_stu_no_age_name` (`stu_no`, `age`, `name`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

执行查询语句：

EXPLAIN SELECT * FROM student where name = (123) and age = (234);

得到的EXPLAIN结果显示查询使用了idx_stu_no_age_name索引：

+----+-------------+---------+------------+-------+----------------------------------+---------------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys                    | key                 | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+----------------------------------+---------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | student | NULL       | index | idx_stu_name,idx_stu_no_age_name | idx_stu_no_age_name | 1065    | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+----------------------------------+---------------------+---------+------+------+----------+--------------------------+

按照最左前缀匹配规则，查询条件name和age并不匹配idx_stu_no_age_name的最左前缀（该索引最左是stu_no），理论上不应选择这个联合索引。但当执行添加字段的操作后：

alter table student add column_name2 int;

再次执行相同查询，EXPLAIN结果恢复符合预期：

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_stu_name  | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

原因分析 #

• 覆盖索引的成本优势：原表中idx_stu_no_age_name联合索引包含了stu_no、age、name三个字段，而InnoDB的二级索引会自动包含主键id，所以这个索引实际上覆盖了查询SELECT *需要的所有字段（原表只有id、stu_no、age、name）。当表中数据量极小（仅1行），MySQL优化器认为遍历这个覆盖索引的成本比全表扫描或单独使用idx_stu_name更低——因为不需要回表操作，直接从索引就能获取所有数据，哪怕需要遍历整个索引，代价也可以忽略。
• 数据量与统计信息的影响：表中只有1行数据时，MySQL的统计信息无法精准反映真实的查询成本，优化器会优先选择看起来更"轻便"的覆盖索引。当新增column_name2字段后，idx_stu_no_age_name不再是覆盖索引（索引中没有column_name2字段，查询需要回表获取该字段），此时遍历这个联合索引的成本显著上升，优化器就会放弃它，转而选择成本更低的全表扫描。
• 最左前缀匹配的适用场景：最左前缀匹配的核心是利用索引的有序性快速定位数据，而这里优化器选择的是index类型的全索引扫描，并非利用索引有序性做范围或等值匹配，本质是在小数据量场景下的成本权衡，并不属于违背最左前缀匹配规则。

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