《Java 100 天进阶之路》第83篇：MySQL索引（2026版）

折哥|智能物流与Java实战

339人浏览 · 2026-06-11 09:00:00

折哥|智能物流与Java实战 · 2026-06-11 09:00:00 发布

第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 分析你项目中的一条慢查询，找出优化点。