MySQL 5.7联合索引最左前缀匹配疑问：第三条SQL为何能用索引？

背景信息 #

使用MySQL 5.7版本，创建了一张包含1,332,660条记录的测试表，建表语句如下：

CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_name` varchar(500) DEFAULT NULL,
`data_time` varchar(100) DEFAULT NULL,
`data_value` decimal(50,8) DEFAULT NULL,
`data_code` varchar(100) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_time_value` (`data_name`,`data_time`,`data_value`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

该表存在联合索引idx_name_time_value，由data_name、data_time、data_value三个字段组成。

执行三条SQL并查看explain结果：

• 第一条SQL（使用索引）：

explain select  *  from test    where data_name ='abc' and  data_time = '2022-06-15 00:00:00' and data_value=75.1

• 第二条SQL（未使用索引）：

explain select  *  from test    where   data_time = '2022-06-15 00:00:00' and data_value=75.1

• 第三条SQL（使用索引）：

explain select  data_name  from test    where   data_time = '2022-06-15 00:00:00' and data_value=75.1

用户疑问 #

1. 根据最左前缀匹配规则，第二条SQL未使用索引符合预期，但第三条SQL为何能使用索引？
2. 第三条SQL虽然使用了索引，但explain结果中rows值和第二条未使用索引的SQL相等，为何会出现类似全表扫描的情况？

解答 #

1. 第三条SQL使用索引的原因 #

这是覆盖索引的特性在起作用。联合索引idx_name_time_value包含了data_name、data_time、data_value三个字段，而第三条SQL的查询字段只有data_name，过滤条件是data_time和data_value。

MySQL执行这条SQL时，会选择扫描整个联合索引——因为索引本身已经包含了查询需要的所有数据（data_name），不需要回表查询原表的其他字段。这种场景下，即使不满足最左前缀匹配的等值/范围查询条件，MySQL也会优先选择使用索引来避免回表操作，这属于索引的全扫描场景。

而第二条SQL需要查询所有字段（select *），如果用联合索引的话，找到符合条件的记录后还得通过主键回表拿其他字段的数据。优化器评估后认为：扫描整个联合索引再回表的成本，比直接全表扫描更高，所以最终选择了全表扫描。

2. rows值相等的原因 #

第三条SQL做的是索引全扫描，需要遍历整个联合索引的所有叶子节点来筛选符合data_time和data_value条件的记录；第二条SQL是全表扫描，遍历整个表的所有数据页。

在explain的rows字段中，这两种扫描方式的估算检查记录数相近——因为InnoDB的二级索引叶子节点和表记录是一一对应的（二级索引叶子节点存的是主键值，每条索引对应一条表记录）。所以优化器估算的rows值会和全表扫描的rows值差不多，看起来像全表扫描，但本质是扫描索引而非原表，只是因为要遍历所有索引条目，所以估算行数接近全表行数。

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