【面试题】MySQL 索引的最左前缀匹配原则是什么？

MySQL 的 最左前缀匹配原则（Leftmost Prefix Principle） 是理解 复合索引（多列索引） 如何工作的核心概念。它决定了查询是否能有效使用复合索引。

核心原则

复合索引可以像多个独立索引一样使用，但必须从最左侧列开始，且不能跳过中间的列。

示例说明

假设有表 users 和复合索引 INDEX idx_name_age_city (name, age, city)

✅ 能使用索引的情况

-- 1. 完全匹配三列（最优）
SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'Beijing';

-- 2. 匹配前两列
SELECT * FROM users WHERE name = 'John' AND age = 25;

-- 3. 只匹配第一列
SELECT * FROM users WHERE name = 'John';

-- 4. 范围查询在第一列，后续列可能部分使用
SELECT * FROM users WHERE name LIKE 'J%' AND age = 25;

❌ 不能使用或只能部分使用的情况

-- 1. 缺少最左列（无法使用索引，全表扫描）
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = 'Beijing';

-- 2. 跳过中间列（只能用到第一列）
SELECT * FROM users WHERE name = 'John' AND city = 'Beijing';
-- ↑ 只能使用 name 列索引，city 无法使用

-- 3. 第一列使用范围查询，后续列索引可能失效
SELECT * FROM users WHERE name > 'John' AND age = 25;
-- ↑ age 可能无法有效使用索引

特殊情况

1. 范围查询后的列索引失效

-- 索引使用情况：name(使用) → age(范围，使用) → city(失效)
WHERE name = 'John' AND age > 20 AND city = 'Beijing'
-- city 无法使用索引加速

2. LIKE 通配符

-- ✅ 前缀匹配可以使用索引
WHERE name LIKE 'Joh%'

-- ❌ 非前缀匹配无法使用索引
WHERE name LIKE '%ohn'

3. 等值查询 + 范围查询

-- ✅ 等值列放前面，范围列放后面
WHERE name = 'John' AND age > 20
-- 可以使用 name 的索引，age 可能部分使用

-- ❌ 顺序不当
WHERE age > 20 AND name = 'John'
-- 如果优化器不重写，可能无法有效使用索引

实际应用建议

1. 索引设计原则

-- 根据查询频率设计索引顺序
-- 假设查询模式：
-- 1. WHERE department = ? AND salary > ?
-- 2. WHERE department = ? AND title = ?
-- 最优索引：
CREATE INDEX idx_dept_salary_title ON employees(department, salary, title);

2. 覆盖索引优化

-- 如果查询只需要索引列，可以直接使用索引
-- 索引：(name, age)
SELECT name, age FROM users WHERE name = 'John';  -- 不需要回表

3. 索引跳跃扫描（MySQL 8.0+）

MySQL 8.0 引入了 Index Skip Scan 优化，在某些情况下可以跳过前缀列：

-- 即使查询条件没有最左列，也可能使用索引
SELECT * FROM users WHERE age = 25;
-- 8.0+ 可能通过扫描不同 name 值来使用索引

验证索引使用情况

使用 EXPLAIN 查看索引使用：

EXPLAIN SELECT * FROM users WHERE name = 'John' AND city = 'Beijing';
-- 查看 key_len 可以知道使用了多少索引列

总结要点

1. 顺序重要：索引列的顺序 = 使用优先级
2. 不能跳过：使用索引必须从最左列开始连续使用
3. 范围断后：范围查询会使后面的索引列失效
4. 优化器可能重排：MySQL 优化器可能重新排列 WHERE 条件来匹配索引
5. 选择性原则：将区分度高的列放前面（不绝对，需结合实际查询）

理解最左前缀原则有助于：

• 设计更高效的复合索引
• 避免创建冗余索引
• 编写能有效利用索引的查询语句
• 分析查询性能问题