MySQL联合索引最左前缀原则与索引跳跃扫描

结果或目标

明确MySQL联合索引在查询优化中的行为模式，特别是理解在何种情况下可以不满足“最左前缀”条件依然使用索引，从而编写更灵活且高效的SQL语句。

准备条件

在进行验证之前，我们需要具备以下环境和数据基础：

• MySQL版本： 建议使用 MySQL 8.0 及以上版本，因为“索引跳跃扫描”是在该版本中正式优化的功能。
• 测试环境： 本地开发环境或测试服务器均可。

真实操作顺序

第1步：构建测试表结构与模拟数据

目的： 创建一个符合场景的表，设置具有低区分度（唯一值很少）的首列索引，以便触发跳跃扫描。

操作： 执行以下SQL脚本创建用户表并在 gender 和 name 字段上建立联合索引。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `gender` tinyint NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';

关键参数说明：

• KEY idx_gender_name (gender,name): 定义了联合索引，第一列为 gender，第二列为 name。
• gender: 作为枚举类型（通常只有男/女两个值），其唯一值数量远少于总行数，这是触发跳跃扫描的前提条件。

可观察的完成状态： 表中已插入部分测试数据（模拟用户数据），确保有非空记录即可。

第2步：执行非最左前缀查询并分析执行计划

目的： 验证在不包含联合索引第一列（gender）的情况下，仅根据第二列（name）查询是否能命中索引。

入口： MySQL客户端或数据库管理工具。

动作： 执行以下查询语句，并使用 EXPLAIN 查看执行计划：

EXPLAIN SELECT * FROM user WHERE name='一灯';

重要参数： WHERE name='一灯' 仅指定了联合索引的第二列。

典型结果：

• 如果使用的是 MySQL 8.0+，type 可能显示为 ref 或 range，且 key 列为 idx_gender_name。
• Extra 列将显示 Using index for skip scan，表明引擎使用了索引跳跃扫描优化。

原理解析：

传统观念认为只有满足最左前缀（如先查 gender）才能走索引。但在本例中，由于 gender 只有极少数的离散值（如0和1），MySQL优化器会自动将该查询转化为类似以下的逻辑：

SELECT ... WHERE gender = 0 AND name = '一灯' UNION ALL SELECT ... WHERE gender = 1 AND name = '一灯'

即分别扫描 gender=0 和 gender=1 两种情况下的 name 索引树，合并结果后返回。这种方式避免了全表扫描，显著提升了性能。

第3步：手动构造等效查询（可选验证）

目的： 理解底层原理，确认跳过首列等价于对首列所有可能值的并集查询。

动作： 显式写出包含首列所有枚举值的查询：

SELECT * FROM user WHERE gender IN (0,1) AND name='一灯';

对比： 此查询在传统模式下也必然走索引。自动的跳跃扫描本质上是优化器帮开发者完成了这一步。

完成判断

通过以下步骤确认操作成功：

1. 执行 EXPLAIN 后，key 列明确指向联合索引 idx_gender_name。
2. Extra 字段包含 Using index for skip scan（针对MySQL 8.0+）。
3. 查询耗时远低于同等条件下的全表扫描（可通过比对不使用索引的查询时长确认）。

原文有依据的排错

问题：为什么我的旧版MySQL（如5.7）无法使用索引跳跃扫描？

解答： 索引跳跃扫描（Index Skip Scan）是优化器在较新版本中增强的特性。在 MySQL 5.7 及更早版本中，如果查询条件不包含联合索引的第一列（最左前缀），优化器通常会拒绝使用该索引而导致全表扫描。若需在这些版本实现类似效果，必须手动补充第一列的过滤条件（如 WHERE first_col IN (...)）。

简要总结

MySQL联合索引严格遵循最左前缀原则，但也存在特例。在MySQL 8.0中，当联合索引首列的值非常少（低基数）时，即使查询条件省略了该列，引擎也会智能地启用“索引跳跃扫描”，自动拆分查询路径以利用后续索引列。这要求我们在日常开发与面试中，不仅要掌握基础的理论，还需关注新版本带来的性能优化机制，避免知识滞后。

以上就是MySQL联合索引最左前缀原则与索引跳跃扫描的详细内容，更多关于MySQL联合索引遵内容请搜索本站以前的文章或继续浏览下面的相关文章！