MySQL最左前缀原则：联合索引的匹配规则与避坑指南

核心原理与定义

MySQL 中的索引通常基于 B+ 树结构存储。在使用联合索引（Composite Index）时，最左前缀原则规定：查询必须从联合索引的最左边字段开始匹配。如果查询中省略了索引定义中最左边的一个或多个字段， MySQL 将无法利用该联合索引进行搜索。

例如，建立了一个包含 (a, b, c) 三个字段的联合索引：

• 使用 WHERE c = '1'：不会走索引。
• 使用 WHERE b = '1' AND c = '2'：也不会走索引。

这是因为 MySQL 无法在排序后的数据结构中快速定位到非首字段的起始位置。

全索引顺序与查询优化器

为了验证联合索引的行为，我们可以建立一个员工记录表并创建联合索引。

第1步：建表与插入数据

首先创建一个用于测试的员工表：

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

第2步：创建联合索引

在 name、age 和 pos 三个字段上建立联合索引：

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name, age, pos);

通过 SHOW INDEX FROM staffs; 确认索引已成功创建。

第3步：测试字段顺序颠倒的情况

即使 SQL 语句中字段的书写顺序与索引定义不一致，只要包含了所有字段，查询优化器通常都能识别并利用索引。以下三条语句的执行计划基本一致，均使用了联合索引：

1. SELECT * FROM staffs WHERE name='z3' AND age=22 AND pos='manager';
2. SELECT * FROM staffs WHERE pos='manager' AND name='z3' AND age=22;
3. SELECT * FROM staffs WHERE age=22 AND pos='manager' AND name='z3';

注意： 这得益于 MySQL 的查询优化器。它会根据成本模型自动重新排列查询条件的执行顺序，以生成最高效的执行计划。因此，不要手动按索引顺序书写 SQL，交给优化器处理即可。

部分索引匹配与失效场景

在实际开发中，最常遇到的问题是只使用联合索引中的部分字段。这时，“最左前缀”原则的作用变得尤为明显。

1. 连续前缀匹配（正序）

如果查询条件紧密贴合索引的前部，无论是否覆盖完整索引，都能有效利用索引：

• SELECT ... WHERE name='z3';（仅第一列）
• SELECT ... WHERE name='z3' AND age=22;（前两列）

在上述情况下，查询类型通常为 ref，表示通过索引查找特定值，效率较高。

2. 断点匹配与乱序

当查询跳过了索引的第一个字段时，情况会发生改变：

1. 只查第一个字段：SELECT ... WHERE name='z3'; → 正常走索引。
2. 跳过中间字段：SELECT ... WHERE name='z3' AND pos='manager'; → 可以使用到索引（MySQL会尽可能使用左侧部分）。
3. 只查最后字段：SELECT ... WHERE pos='manager'; → 索引失效，转为全表扫描（type: all）。

当 type 变为 all 时，意味着 MySQL 放弃了索引，直接读取磁盘上的每一行数据进行过滤。虽然有时也可能显示为 index（全索引扫描），但其代价依然远高于 ref。

模糊查询的影响

在涉及字符串模糊匹配时，通配符的位置决定了索引是否生效。最左前缀原则同样适用：

1. SELECT ... WHERE name LIKE '3%';
   开头确定，符合最左前缀，使用 range 或 index 类型索引。
2. SELECT ... WHERE name LIKE '%3';
   结尾确定，但开头不确定，索引失效，通常表现为 ALL（全表扫描）。
3. SELECT ... WHERE name LIKE '%3%';
   两头都不确定，无法利用排序特性，索引失效，表现同上。

范围查询导致后续索引失效

这是最容易被忽视的性能陷阱。如果联合索引的某一列参与了范围查询（如 >, <, BETWEEN），那么该列右侧的所有索引都将失效。

问题分析

考虑以下文章表查询：

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

如果未加优化，此查询可能涉及全表扫描且出现 Using filesort（文件排序），性能极差。

尝试建立复合索引

初步思路是创建包含所有相关字段的索引：(category_id, comments, views)。

CREATE INDEX idx_article_ccv ON article(category_id, comments, views);

然而，由于 comments > 1 是一个范围条件，MySQL 在定位完 category_id=1 后，需要在子区间内处理 comments 的范围。一旦进入范围搜索，B+树的有序性就被打破，后续的 views 索引就无法再被利用来进行排序。结果依然是 Using filesort。

删除该无效索引：

DROP INDEX idx_article_ccv ON article;

最终优化方案

要消除文件排序，必须让索引的最后一个字段也是等值匹配。因此，我们应该将范围条件的字段移出索引，或者调整字段顺序。在这里，我们建立只包含等值条件和排序字段的索引：

CREATE INDEX idx_article_cv ON article(category_id, views);

在这个新索引下，category_id 用于精确定位，views 保持了全局的顺序。虽然 comments > 1 无法走索引过滤，但它不再阻碍 views 进行有序的遍历查找，从而成功消除了 filesort。

总结

MySQL 联合索引的使用核心在于“最左前缀”。为了确保索引高效运作，请遵循以下准则：查询条件应从左往右连续匹配；避免在索引中间使用范围查询；对于排序操作，确保排序字段位于索引末尾且未被前面的范围查询阻断。

以上就是 MySQL 索引最左前缀原则的详细分析，更多关于 MySQL 数据库优化的资料请关注本站其它相关文章！