第83篇：MySQL索引（2026版）
📌 系列导航：《Java 100 天进阶之路》完整目录 |
⬅️ 上一篇：第82篇：Spring面试压轴题 |
➡️ 下一篇：第84篇：MySQL事务与锁
一、核心知识点
- 索引本质：排好序的快速查找数据结构
- B+Tree 索引：MySQL InnoDB 默认索引结构，叶子节点存储数据，非叶子节点存索引
- 聚簇索引 vs 二级索引：聚簇索引叶子存整行数据，二级索引叶子存主键值
- 回表：二级索引查到主键后，再到聚簇索引查完整行
- 覆盖索引：索引列包含查询所需的所有字段，无需回表
- 最左前缀原则：联合索引从左到右匹配，跳过中间列则后面失效
- 索引下推（ICP）：MySQL 5.6+，在索引遍历时直接过滤，减少回表次数
- 索引失效场景：函数操作、隐式类型转换、
%开头的模糊查询等 - MySQL 8.0+ 新特性：隐藏索引、降序索引、索引统计信息持久化
二、通俗讲解（1分钟开心学）
1. 索引是什么？
索引是数据库的“目录”。没有索引就像一本没有目录的书，要找某句话只能一页页翻（全表扫描）；有了索引，直接翻到对应页码即可。
生活类比：
图书馆的图书分类标签（索书号）就是索引。你要找《Java编程思想》，先查电脑（索引）得到索书号 TP312JA/123，然后直接去书架定位，不用逛遍整个图书馆。
2. B+Tree 为什么适合数据库？
B+Tree 特点：
- 非叶子节点只存索引，不存数据，能存放更多索引项，降低树高度（一般 2~4 层）。
- 叶子节点形成双向链表，支持范围查询和顺序遍历。
- 数据均匀分布在叶子节点，查询稳定（任何查找都 O(logN)）。
对比其他数据结构：
- Hash 索引：单点查询快，但不支持范围查询，仅 Memory 引擎默认。
- 二叉树：可能退化成链表，树高不可控。
- B-Tree：非叶子也存数据，导致每层能存的索引变少，树更高。
3. 聚簇索引 vs 二级索引
- 聚簇索引：叶子节点存储整行数据。InnoDB 中主键就是聚簇索引；没有主键则第一个 NOT NULL UNIQUE 列；都没有则隐式生成 rowid。
- 二级索引（辅助索引）：叶子节点存储主键值，查询时先找到主键，再到聚簇索引回表取完整数据。
生活类比：
聚簇索引就像按学号排好的学生档案柜，学号对应整套档案。二级索引就像按姓名建的索引卡，上面写着学号，你需要根据学号再去档案柜取档案（回表）。
4. 最左前缀原则
联合索引 (a, b, c) 相当于按 a 排序，a 相同时按 b 排序，b 相同时按 c 排序。查询时从索引最左边开始匹配，跳过中间列则后续无法使用。
- ✅
WHERE a = 1 AND b = 2 AND c = 3：全用 - ✅
WHERE a = 1 AND b = 2：用 a、b - ✅
WHERE a = 1：用 a - ❌
WHERE b = 2：无法使用（跳过了 a） - ❌
WHERE a = 1 AND c = 3：只用 a，c 失效
三、实操代码案例 + 场景说明
测试表：用户订单表，数据量 100 万。
CREATE TABLE `orders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_no` varchar(32) NOT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`status` tinyint(4) DEFAULT '0',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_user_status` (`user_id`, `status`),
KEY `idx_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.1 使用 EXPLAIN 分析执行计划
-- 1. 有效索引
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1;
-- type: ref, key: idx_user_status, rows: 很小
-- 2. 索引失效（跳过左侧）
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- type: ALL（全表扫描）
-- 3. 索引失效（隐式类型转换）
EXPLAIN SELECT * FROM orders WHERE order_no = 123; -- order_no 是 varchar，传入 int
-- key: NULL（不会用 idx_order_no）
-- 4. 索引失效（函数操作）
EXPLAIN SELECT * FROM orders WHERE DATE(create_time) = '2026-01-01';
-- key: NULL
-- 5. 覆盖索引（无需回表）
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 123;
-- Extra: Using index
3.2 索引下推优化（ICP）
MySQL 5.6+ 默认开启。对于 idx_user_status (user_id, status)：
SELECT * FROM orders WHERE user_id = 123 AND status = 1;
没有 ICP：先在二级索引找到 user_id=123 的所有主键（可能几百个），再回表读取完整行，再判断 status=1。
有 ICP：在二级索引遍历时直接判断 status=1，不匹配的就不回表，大幅减少回表次数。
3.3 排序与索引
-- 索引能支持排序，避免 filesort
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY status;
-- Extra: Using index condition（无 filesort）
-- 索引不能支持排序（跳过了 user_id）
EXPLAIN SELECT * FROM orders ORDER BY status;
-- Extra: Using filesort
3.4 MySQL 8.0+ 新特性：隐藏索引与降序索引
-- 隐藏索引：不被优化器使用，用于测试删除影响
ALTER TABLE orders ALTER INDEX idx_user_status INVISIBLE;
-- 再次查询，观察是否走索引
-- 降序索引：联合索引中指定降序
CREATE INDEX idx_user_create_desc ON orders (user_id ASC, create_time DESC);
-- 适用于 ORDER BY user_id ASC, create_time DESC 场景
四、避坑要点（高频失效场景）
| 错误写法 | 原因 | 正确做法 |
|---|---|---|
WHERE status = 1 | 联合索引跳过最左列 | 建立 status 单独索引，或带上 user_id |
WHERE order_no = 123 | 隐式类型转换（varchar → int） | 应用层类型匹配：order_no = '123' |
WHERE LEFT(name,3) = 'abc' | 函数操作破坏索引 | 改用 name LIKE 'abc%'，或冗余存储 |
WHERE create_time + 1 = ... | 列参与运算 | 改为 create_time = ... - 1 |
WHERE name LIKE '%abc' | 前模糊匹配 | 尽量放后面 LIKE 'abc%'，或使用倒排索引/ES |
OR 连接不同列 | 可能不走索引 | 拆分为 UNION，或用 IN 替代 |
!= 或 <> | 范围查询，大概率全表 | 考虑业务重构，或用 > < |
IS NULL / IS NOT NULL | 某些情况不走索引 | 根据实际数据分布，必要时建索引 |
| 统计信息过期 | 优化器选择错误执行计划 | ANALYZE TABLE 更新统计信息 |
| 在线加索引未注意 MDL 锁 | 短暂阻塞写操作 | 使用 ALGORITHM=INPLACE, LOCK=NONE |
五、面试高频考点
Q1：什么是回表？如何避免？
二级索引查到主键后，再到聚簇索引查询完整行的过程。
避免方法：使用覆盖索引（索引包含查询所需所有字段），或直接查主键。
Q2：最左前缀原则是什么？举例说明。
联合索引从左到右匹配，跳过中间列则后续无效。
例如索引(a, b, c)，条件a=1 AND c=1只用到 a，c 失效。
原因是索引排序规则。
Q3：为什么用 B+Tree 而不用 B-Tree 或 Hash？
- B+Tree：非叶子只存索引，叶子存数据且形成链表，树矮、范围查询快。
- B-Tree：非叶子也存数据，层数更高，I/O 更多。
- Hash：单点查询 O(1)，但不支持范围、排序、模糊查询。
Q4：索引下推（ICP）是什么？
MySQL 5.6+，在索引遍历时直接过滤条件，减少回表次数。
仅适用于二级索引，且条件列在索引中。
Q5：如何设计联合索引？
原则：区分度高的列在前；等值查询列在前，范围查询列在后；考虑排序需求。
示例：where a=1 and b>2 order by c，建议索引(a, c)避免 filesort。
Q6：主键为什么建议自增？
自增主键保证插入时顺序写入，减少页分裂；
UUID 主键随机插入，页分裂频繁，导致索引碎片，性能差。
Q7：在线加索引会导致锁表吗？
MySQL 5.6+ 支持 Online DDL（
ALGORITHM=INPLACE, LOCK=NONE），
主库加索引不阻塞写，但仍有短暂元数据锁（MDL）。
生产环境建议在低峰期操作。
Q8：MySQL 优化器如何选择索引？
基于索引统计信息（Cardinality，索引唯一值数量）。
如果统计信息过期，优化器可能误判，导致用错索引。
执行ANALYZE TABLE可更新统计信息。
MySQL 8.0+ 将统计信息持久化，减少问题发生。
Q9：什么是隐藏索引（Invisible Index）？
MySQL 8.0 引入，可设置索引对优化器不可见，但 DML 仍维护索引。
用于测试删除索引的影响，避免直接删除后重建的麻烦。
Q10：索引与锁的关系？
在不走索引的
UPDATE/DELETE中，InnoDB 会锁全表（实际是锁所有聚簇索引扫描到的行）。
走索引则只锁索引覆盖的行，减少锁冲突。
六、练习题
-
分析：有一张订单表，查询
SELECT * FROM orders WHERE user_id=123 ORDER BY create_time DESC LIMIT 10，如何建索引最优？💡 思路：
等值查询（user_id）放在前面，排序（create_time）放在后面：(user_id, create_time)，可同时支持过滤和排序，避免 filesort。 -
案例：某慢查询日志显示
SELECT id,name,age FROM user WHERE name LIKE '%张%'，耗时 5 秒，如何优化？💡 思路：前模糊无法走普通索引，可用倒排索引（存储反转字符串）或 Elasticsearch，或改用搜索引擎。
-
代码：使用
EXPLAIN分析你项目中的一条慢查询，找出优化点。
📊 你的学习进度
- 当前：第83篇 / 共108篇 · 进阶篇：数据库与持久层框架（第83~90篇）
- ✅ 已完成：基础篇44篇 + 第91~96篇（Redis/MQ）+ 第83篇
- 📖 正在学：第83篇
- ⏳ 待学习：第84~90篇（MySQL 事务/锁/SQL优化/MyBatis）+ 第97~108篇（微服务/物联网/AI/设计模式/面试压轴）
👉 📚 完整目录 & 学习指南 | 🔥 订阅本专栏，不错过每一篇
💡 本专栏每篇都包含：避坑表 + 面试高频考点 + 练习题。每天30分钟，100天拿offer！
👉 下一篇文章预告
内容简介：事务四大特性（ACID）、隔离级别（读未提交→读已提交→可重复读→串行化）、MVCC 原理、间隙锁与幻读、死锁排查与解决。
💡 学完这篇，你将彻底搞懂 MySQL 并发控制，面试不再怕“RR 如何解决幻读”。
🎁 福利提醒：评论区留言“MySQL索引”可领取《MySQL 索引优化实战清单》PDF。
📌 《Java 100 天进阶之路 | 从入门到上岗就业》 每天一篇，建议收藏 + 关注，一起100天拿offer！
👉 点击关注我，更新后第一时间收到推送！
399
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
