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

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

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

假设有表 users 和复合索引 INDEX idx_name_age_city (name, age, city)
✅ 能使用索引的情况

1. -- 1. 完全匹配三列（最优）
2. SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'Beijing';
4. -- 2. 匹配前两列
5. SELECT * FROM users WHERE name = 'John' AND age = 25;
7. -- 3. 只匹配第一列
8. SELECT * FROM users WHERE name = 'John';
10. -- 4. 范围查询在第一列，后续列可能部分使用
11. SELECT * FROM users WHERE name LIKE 'J%' AND age = 25;

复制代码

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

1. -- 1. 缺少最左列（无法使用索引，全表扫描）
2. SELECT * FROM users WHERE age = 25;
3. SELECT * FROM users WHERE city = 'Beijing';
5. -- 2. 跳过中间列（只能用到第一列）
6. SELECT * FROM users WHERE name = 'John' AND city = 'Beijing';
7. -- ↑ 只能使用 name 列索引，city 无法使用
9. -- 3. 第一列使用范围查询，后续列索引可能失效
10. SELECT * FROM users WHERE name > 'John' AND age = 25;
11. -- ↑ age 可能无法有效使用索引

复制代码

特殊情况

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

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

复制代码

2. LIKE 通配符

1. -- ✅ 前缀匹配可以使用索引
2. WHERE name LIKE 'Joh%'
4. -- ❌ 非前缀匹配无法使用索引
5. WHERE name LIKE '%ohn'

复制代码

3. 等值查询 + 范围查询

1. -- ✅ 等值列放前面，范围列放后面
2. WHERE name = 'John' AND age > 20
3. -- 可以使用 name 的索引，age 可能部分使用
5. -- ❌ 顺序不当
6. WHERE age > 20 AND name = 'John'
7. -- 如果优化器不重写，可能无法有效使用索引

复制代码

实际应用建议

1. 索引设计原则

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

复制代码

2. 覆盖索引优化

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

复制代码

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

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

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

复制代码

验证索引使用情况

使用 EXPLAIN 查看索引使用：

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

复制代码

总结要点

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

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

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

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！