MySQL最左前缀原则

什么是“最左前缀原则”？

最左前缀原则（Leftmost Prefix Principle） 指的是：MySQL 中的联合索引（也称为复合索引，即由多个列组成的索引）会首先按照最左边的列进行排序，然后在相同左边列值的基础上，再按下一列排序，以此类推。

因此，当你的 SQL 查询条件中包含了联合索引的最左面的一个或多个列时，这个索引才可能被使用。

可以把联合索引想象成一个电话簿。电话簿首先按照姓氏排序，在姓氏相同的情况下，再按照名字排序。如果你只知道这个人的名字（而不是姓氏），你就无法利用这个排序规则快速找到他，必须从头到尾翻阅整个电话簿。

姓氏 + 名字 = 联合索引
只知道姓氏 = 使用索引的最左前缀
只知道名字 = 无法使用这个索引

联合索引的结构

假设我们有一张 users 表，并创建了一个联合索引 idx_name_age (name, age)。

id	name	age	city
1	Alice	28	Beijing
2	Bob	25	Shanghai
3	Charlie	30	Guangzhou
4	Alice	24	Shenzhen
5	Bob	32	Hangzhou

索引 idx_name_age 在磁盘中的存储逻辑结构大致是这样的（类似于一个有序的列表），它首先按 name 排序，name 相同时再按 age 排序：

name	age	主键 id (隐式)
Alice	24	4
Alice	28	1
Bob	25	2
Bob	32	5
Charlie	30	3

注意：实际上索引还包含了指向对应行数据的指针（通常是主键id），这里为了简化而直接列出了id。

最左前缀原则的几种应用场景

我们以索引 idx_name_age (name, age) 为例，来分析各种查询条件是否能有效利用索引。

场景 1：完全匹配所有列（最佳情况）

1	SELECT * FROM users WHERE name = 'Alice' AND age = 24;

索引使用情况： ✅ 完全使用。
解释：查询条件从索引的最左列 name 开始，并且精确匹配了下一列 age。MySQL 可以非常快速地在索引树中定位到 (Alice, 24) 这条记录。

场景 2：只匹配最左列

1	SELECT * FROM users WHERE name = 'Alice';

索引使用情况： ✅ 使用索引。
解释：查询条件包含了索引的最左列 name。由于索引首先是按 name 排序的，MySQL 可以快速找到所有 name 为 ‘Alice’ 的索引条目（它们是连续存储的）。

场景 3：匹配最左列和中间列（范围查询）

1	SELECT * FROM users WHERE name = 'Alice' AND age > 25;

索引使用情况： ✅ 部分使用。
解释：MySQL 首先能用 name = ‘Alice’ 在索引中定位到第一个 Alice 的记录，然后由于 age 在索引中也是有序的，它可以快速扫描所有 name 为 ‘Alice’ 且 age > 25 的记录。这里对 name 是等值查询，对 age 是范围查询。

场景 4：没有最左列

1	SELECT * FROM users WHERE age = 25;

索引使用情况： ❌ 无法使用索引（除非使用覆盖索引，见下文第5点）。
解释：因为索引的第一列 name 没有出现在查询条件中。MySQL 无法利用索引的有序性来快速查找 age，它只能进行全表扫描（Full Table Scan），遍历每一行记录来检查 age 是否等于 25。这就好比让你在无序的电话簿里找所有叫“伟”的人，你无法快速定位。

场景 5：跳过了中间列

1 2	SELECT * FROM users WHERE name = 'Bob' AND city = 'Hangzhou'; -- 假设我们还有一个索引 idx_name_city_age (name, city, age)，但查询是 WHERE name = ‘Bob’ AND age = 25;

索引使用情况：对于 idx_name_age：✅ 部分使用。
解释：查询条件包含了最左列 name，所以索引 idx_name_age 依然可以被使用。MySQL 会用 name = ‘Bob’ 快速找到所有 Bob 的记录。但是，由于条件中没有 age，而 city 又不在这个索引中，对于找到的每一个 Bob，MySQL 都需要回表（回到主键索引）去检查那条记录的 city 字段是否为 ‘Hangzhou’。
关键点：如果索引是 idx_name_city_age (name, city, age)，而查询是 WHERE name = ‘Bob’ AND age = 25，那么只能用到 name 这一列，因为跳过了中间的 city 列。

场景 6：模糊查询 LIKE

1 2	-- 前缀匹配 SELECT * FROM users WHERE name LIKE 'Ali%';

索引使用情况： ✅ 可以使用索引。
解释：‘Ali%’ 是一个左前缀，索引的有序性依然可以发挥作用。

1
2
3

-- 中缀或后缀匹配
SELECT * FROM users WHERE name LIKE '%lice%';
SELECT * FROM users WHERE name LIKE '%ice';

索引使用情况： ❌ 无法使用索引。
解释：因为开头是通配符 %，MySQL 无法知道从哪里开始查找，索引的有序性失效，只能全表扫描。

范围查询后的列索引失效

这是一个非常重要的衍生规则：在联合索引中，如果某一列使用了范围查询（>, <, BETWEEN, LIKE ‘%’ 等），那么它右边的列将无法再使用索引进行进一步筛选。

1 2	-- 索引: (name, age, city) SELECT * FROM users WHERE name = ‘Alice’ AND age > 25 AND city = ‘Beijing’;

在这个查询中：

name = ‘Alice’：✅ 使用索引（等值查询）
age > 25：✅ 使用索引（范围查询）
city = ‘Beijing’：❌ 无法使用索引

解释：MySQL 在索引中先找到所有 name=‘Alice’ 的记录，然后在这些记录中扫描 age > 25 的部分。然而，对于 age 是范围查询，其对应的 city 值在索引中不再是有序的。因此，MySQL 无法再利用索引对 city 进行快速筛选，它只能将所有满足 name=‘Alice’ AND age>25 的记录找出来，然后回表到主键索引中去逐条判断 city 是否等于 ‘Beijing’。

覆盖索引（Covering Index）—— 一个例外

覆盖索引是指一个查询只需要从索引中就能获取所有需要的数据，而无需回表读取数据行。

在场景 4 中，我们说到 SELECT * FROM users WHERE age = 25; 无法使用索引 idx_name_age。这是因为即使它从索引中找到了 age=25 的条目（需要全索引扫描），它还需要通过主键 id 回表去获取其他所有列（name, city 等）的数据，这个回表的成本可能比直接全表扫描还高，所以优化器会选择全表扫描。

但是，如果查询只请求索引中包含的列，情况就完全不同了：

-- 查询只请求了 age，而 age 在索引 idx_name_age 中
SELECT age FROM users WHERE age = 25;

-- 或者，查询请求了索引中的列
SELECT name, age FROM users WHERE age = 25;

索引使用情况： ✅ 使用索引（但方式是扫描整个索引，而不是高效查找）。
解释：虽然无法利用索引的有序性，但优化器发现需要的数据（age，甚至 name 和 age）完全包含在索引 idx_name_age 中。它会选择扫描整个索引（Index Scan）而不是扫描整个表（Table Scan），因为索引通常比表数据小得多，磁盘 I/O 更少，速度更快。

总结与实践建议

核心定义：最左前缀原则要求查询条件必须从联合索引的最左列开始，才能有效利用该索引。
索引顺序至关重要：创建联合索引时，列的顺序决定了索引的威力。应根据查询的频率和 筛选性（Cardinality，即索引列不同值的数量）来安排顺序。
- 将最常用作查询条件的列放在左边。
- 将筛选性更高（更唯一）的列放在左边，这样可以更快地缩小查找范围。
避免索引失效：
- 避免在查询条件中跳过联合索引的中间列。
- 谨慎使用范围查询，意识到范围查询之后的索引列会失效。
- 避免在索引列上使用函数或计算（如 WHERE YEAR(date_column) = 2023），这会导致索引失效。
利用覆盖索引：精心设计联合索引，使其能够覆盖一些高频查询的所有字段，可以极大提升查询速度，避免回表操作。
EXPLAIN 是你的好朋友：任何关于索引使用的猜测都应该通过 EXPLAIN 命令来验证。查看 EXPLAIN 输出中的 key（使用的索引）、key_len（使用的索引长度）和 Extra（是否使用覆盖索引：Using index）等字段，是分析和优化查询性能的标准流程。