MySQL索引核心知识体系：最左前缀、失效场景与设计原则
一、索引基础铺垫
1.1 索引本质与数据结构
- 本质：数据库为了加速查询，对数据表中一列或多列的值进行排序的一种数据结构
- 主流实现：InnoDB存储引擎默认使用B+树索引
- 特点：所有数据都存储在叶子节点，非叶子节点仅存储索引键和指针
- 优势：范围查询高效、磁盘I/O次数少、支持排序和分组
- 索引类型：主键索引（聚簇索引）、二级索引（非聚簇索引）、联合索引、唯一索引、全文索引等
1.2 聚簇索引与二级索引的关系
- 聚簇索引：数据行的物理存储顺序与索引顺序一致，一个表只能有一个聚簇索引（通常是主键）
- 二级索引：叶子节点存储的是主键值而非数据行本身
- 回表查询：通过二级索引找到主键后，再通过聚簇索引获取完整数据行的过程
- 覆盖索引：查询所需的所有列都包含在索引中，无需回表，性能最优
二、联合索引最左前缀匹配原则（核心）
2.1 原则定义
联合索引是对表中多个列按顺序建立的索引。最左前缀匹配原则指：查询条件必须从索引的最左前列开始，并且不能跳过索引中的列，否则无法使用该索引的全部或部分。
2.2 匹配规则详解
假设我们建立了联合索引 idx_a_b_c(a, b, c)，以下是各种查询条件的索引使用情况：
| 查询条件 | 索引使用情况 | 说明 |
|---|---|---|
WHERE a=1 |
✅ 完全使用索引a列 | 匹配最左前缀 |
WHERE a=1 AND b=2 |
✅ 完全使用索引a、b列 | 连续匹配 |
WHERE a=1 AND b=2 AND c=3 |
✅ 完全使用索引a、b、c列 | 完整匹配 |
WHERE b=2 AND a=1 |
✅ 完全使用索引a、b列 | MySQL优化器会自动调整条件顺序 |
WHERE a=1 AND c=3 |
⚠️ 仅使用索引a列 | 跳过了b列，c列无法使用索引 |
WHERE b=2 AND c=3 |
❌ 完全不使用索引 | 跳过了最左列a |
WHERE a>1 AND b=2 |
⚠️ 仅使用索引a列 | 范围查询后的列无法使用索引 |
WHERE a=1 AND b>2 AND c=3 |
⚠️ 使用索引a、b列 | 范围查询后的c列无法使用索引 |
WHERE a=1 AND b LIKE 'abc%' AND c=3 |
⚠️ 使用索引a、b列 | 前缀匹配后的c列无法使用索引 |
2.3 特殊情况说明
- MySQL优化器的作用：当查询条件中列的顺序与联合索引顺序不一致时，优化器会自动调整顺序以匹配最左前缀原则
- 范围查询的截断效应：一旦在索引的某一列上使用了范围查询（>、<、>=、<=、BETWEEN），该列之后的所有列都无法使用索引
- 前缀匹配的局限性：LIKE 'abc%' 可以使用索引，但 LIKE '%abc' 或 LIKE '%abc%' 无法使用索引
- 覆盖索引的例外：即使查询条件不完全符合最左前缀，但如果查询的列都包含在联合索引中，MySQL可能会使用索引扫描（index scan）代替全表扫描
2.4 最左前缀原则的底层原理
B+树的索引是按照联合索引的列顺序构建的：
- 首先按照第一列a排序
- 在a值相同的情况下，再按照第二列b排序
- 在a、b值都相同的情况下，最后按照第三列c排序
因此，只有从最左列开始查询，才能利用B+树的有序性快速定位数据。如果跳过了前面的列，后面的列在B+树中是无序的，无法进行高效查找。
三、索引失效场景（高频考点）
3.1 违反最左前缀匹配原则
- 跳过联合索引的最左列
- 在联合索引中跳过中间列
- 范围查询后的列无法使用索引
3.2 在索引列上使用函数或表达式
-- 索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2023;
SELECT * FROM user WHERE age + 1 = 30;
SELECT * FROM user WHERE UPPER(name) = 'ZHANGSAN';
-- 索引有效（改写为函数在右侧）
SELECT * FROM user WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM user WHERE age = 29;
SELECT * FROM user WHERE name = 'zhangsan';
3.3 使用不等于操作符
!=、<>、NOT等操作符会导致索引失效- 例外：如果索引列的值分布非常不均匀，MySQL可能会选择使用索引
-- 索引失效
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE age <> 30;
3.4 使用IS NULL和IS NOT NULL
IS NULL可以使用索引IS NOT NULL通常会导致索引失效- 例外：如果索引列中NULL值占比很高，
IS NOT NULL可能会使用索引
-- 索引有效
SELECT * FROM user WHERE email IS NULL;
-- 索引失效
SELECT * FROM user WHERE email IS NOT NULL;
3.5 字符串不加引号导致隐式类型转换
当索引列是字符串类型，但查询条件中传入的是数值类型时，MySQL会自动进行隐式类型转换，导致索引失效。
-- 假设phone列是VARCHAR类型
-- 索引失效（隐式转换：CAST(phone AS SIGNED) = 13800138000）
SELECT * FROM user WHERE phone = 13800138000;
-- 索引有效
SELECT * FROM user WHERE phone = '13800138000';
3.6 使用LIKE通配符开头
-- 索引失效
SELECT * FROM user WHERE name LIKE '%zhang';
SELECT * FROM user WHERE name LIKE '%zhang%';
-- 索引有效
SELECT * FROM user WHERE name LIKE 'zhang%';
3.7 OR连接的条件中存在非索引列
如果OR连接的两个条件中，有一个条件没有使用索引，那么整个查询都不会使用索引。
-- 假设name有索引，age没有索引
-- 索引失效
SELECT * FROM user WHERE name = 'zhangsan' OR age = 30;
-- 索引有效（改写为UNION）
SELECT * FROM user WHERE name = 'zhangsan'
UNION
SELECT * FROM user WHERE age = 30;
3.8 MySQL优化器选择不使用索引
即使查询条件满足索引使用规则，MySQL优化器也可能因为以下原因选择全表扫描：
- 表的数据量很小（通常小于1000行）
- 查询返回的数据量占表总数据量的比例很高（通常超过20%-30%）
- 索引的选择性很低（即不同值的数量很少，如性别列）
3.9 使用JOIN时关联条件的数据类型不一致
当两个表进行JOIN操作时，如果关联条件的列数据类型不一致，会导致隐式类型转换，从而使索引失效。
四、索引设计原则（最佳实践）
4.1 基础设计原则
- 优先为查询频繁的列建立索引：索引是为了加速查询，只对经常出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的列建立索引
- 联合索引优于多个单列索引：当查询条件涉及多个列时，建立联合索引比建立多个单列索引效率更高
- 索引列的选择性越高越好：选择性 = 不同值的数量 / 表的总行数。选择性越高，索引的效率越高
- 尽量使用覆盖索引：让查询所需的所有列都包含在索引中，避免回表查询
- 控制索引的数量：索引不是越多越好，过多的索引会增加数据插入、更新、删除的开销
- 避免冗余索引：如果已经有了联合索引
idx_a_b(a, b)，那么单独的索引idx_a(a)就是冗余的，可以删除
4.2 联合索引设计原则
- 遵循最左前缀匹配原则：将最常用的查询条件列放在联合索引的最左边
- 将选择性高的列放在前面：在满足最左前缀原则的前提下，将选择性高的列放在联合索引的前面
- 将范围查询的列放在后面：因为范围查询会截断后面的列，所以应将范围查询的列放在联合索引的最后面
- 考虑排序和分组需求：如果查询中有ORDER BY或GROUP BY子句，应将相关列包含在联合索引中，并保持顺序一致
4.3 特殊场景设计原则
- 长字符串列的索引设计：对于VARCHAR(255)这样的长字符串列，可以考虑使用前缀索引
-- 为email列的前10个字符建立索引 CREATE INDEX idx_email_prefix ON user(email(10));- 优点：减少索引占用的空间
- 缺点：无法使用覆盖索引，ORDER BY和GROUP BY也无法使用前缀索引
- NULL值的处理：尽量避免在索引列上使用NULL值，可以使用默认值（如0、''）代替
- 频繁更新的列不宜建立索引：更新索引的开销很大，频繁更新的列会导致索引维护成本过高
- 小表不需要建立索引：对于数据量很小的表，全表扫描比使用索引更快
4.4 索引维护原则
- 定期检查和删除无用索引：使用
sys.schema_unused_indexes视图查看未使用的索引 - 定期分析索引使用情况：使用
EXPLAIN分析查询语句的执行计划，确认索引是否被正确使用 - 避免在业务高峰期进行索引操作：创建和删除索引会锁表，影响业务正常运行
- 对于大表，考虑使用在线DDL工具：如pt-online-schema-change、gh-ost等，避免锁表
五、实战案例与最佳实践
5.1 案例1：联合索引设计
需求：用户表user(id, name, age, gender, create_time)，经常执行以下查询：
SELECT * FROM user WHERE name = ? AND age > ?;
SELECT * FROM user WHERE name = ? AND gender = ?;
SELECT * FROM user ORDER BY create_time DESC;
最佳索引设计：
- 建立联合索引
idx_name_age(name, age)满足第一个查询 - 建立联合索引
idx_name_gender(name, gender)满足第二个查询 - 建立索引
idx_create_time(create_time)满足第三个查询
说明：不能建立 idx_name_age_gender(name, age, gender) 来同时满足前两个查询，因为第一个查询中age是范围查询，gender列无法使用索引。
5.2 案例2：覆盖索引优化
原始查询：
SELECT id, name, age FROM user WHERE name = ? AND age = ?;
优化前：建立索引 idx_name_age(name, age)，需要回表查询id列
优化后：建立联合索引 idx_name_age_id(name, age, id)，成为覆盖索引，无需回表
5.3 案例3：索引失效优化
原始查询（索引失效）：
SELECT * FROM order WHERE DATE(create_time) = '2023-05-01';
优化后（索引有效）：
SELECT * FROM order WHERE create_time >= '2023-05-01 00:00:00'
AND create_time < '2023-05-02 00:00:00';
六、常见误区与面试考点
6.1 常见误区
- 误区：索引越多，查询速度越快
纠正：过多的索引会增加数据修改的开销，并且可能导致优化器选择错误的索引 - 误区：只要在列上建立了索引，查询就一定会使用索引
纠正：索引失效场景很多，需要使用EXPLAIN分析执行计划 - 误区：联合索引的列顺序不影响查询效率
纠正：联合索引的列顺序非常重要，必须遵循最左前缀匹配原则 - 误区：NULL值不能使用索引
纠正：IS NULL可以使用索引，IS NOT NULL通常不能使用索引
6.2 高频面试考点
- 什么是最左前缀匹配原则？底层原理是什么？
- 列举至少10种索引失效的场景
- 联合索引和多个单列索引有什么区别？应该如何选择？
- 什么是覆盖索引？有什么优势？
- 什么是回表查询？如何避免回表查询？
- 如何设计一个好的索引？
- 为什么MySQL使用B+树作为索引的数据结构，而不是B树或哈希表？
- 聚簇索引和非聚簇索引有什么区别？
