MySQL 索引原理、执行计划分析与 SQL 调优实践

本文系统梳理 MySQL 索引底层数据结构、存储引擎差异、执行计划解读，以及业务场景中常见的 SQL 调优手段，适用于具备一定 Java 后端开发基础的读者。所有示例基于 MySQL 8.x + InnoDB 引擎。

---

目录

1. 索引底层数据结构
2. 存储引擎与索引存储方式
3. 聚簇索引与二级索引
4. 联合索引与最左前缀原则
5. EXPLAIN 执行计划详解
6. 索引失效场景分析
7. SQL 调优实战方法论
8. optimizer_trace 分析工具
9. 索引设计规范

---

一、索引底层数据结构

索引是帮助 MySQL 高效获取数据的、预先排好序的数据结构。理解各种数据结构的特性，是判断 MySQL 为何选择 B+ 树作为主力索引结构的关键。

1.1 各数据结构对比

数据结构	特点	局限性
二叉搜索树	左小右大，查询 O(log n)	顺序插入退化为链表，树高不可控
红黑树	自平衡，保证 O(log n)	数据量大时树高依然过深，磁盘 I/O 次数多
Hash 表	等值查询 O(1)	不支持范围查询，不支持排序，哈希冲突处理复杂
B-Tree	多路平衡搜索树，每节点存索引+数据，减少树高	非叶子节点存 data，同等空间下可放索引数量少
B+ 树	非叶子节点只存索引，叶子节点存完整数据且双向链表相连	—
Full-Text 索引	倒排索引，适合全文搜索	不适合精确匹配，中文需额外分词插件

1.2 B-Tree 详解

B-Tree（多路平衡查找树）是数据库领域专门针对磁盘访问特点设计的数据结构：

• 每个节点可存放多个索引元素（阶数 m 表示最多 m 个子节点）
• 所有叶子节点处于同一层（等深），保证最坏情况查询稳定
• 节点内的索引值从左到右递增排列
• 叶子节点的指针为空

工程意义：MySQL InnoDB 一次磁盘 I/O 读取一个数据页（默认 16 KB），B-Tree 节点大小与页大小对齐，极大降低磁盘 I/O 次数。

1.3 B+ 树详解

B+ 树是 InnoDB 实际使用的索引结构，相较 B-Tree 有以下关键改进：

1. 非叶子节点只存索引，不存数据：同等 16 KB 空间内可存放更多索引键，树的高度更低
2. 叶子节点存放完整的索引字段值 + 数据（或主键值）：数据只在叶子层，查询路径稳定
3. 叶子节点通过双向链表相连：范围查询只需找到起点后顺序遍历，效率极高

容量估算：

假设主键为 bigint（8 字节），每个子节点指针占 6 字节，则每个非叶子节点可存放约 16KB / 14B ≈ 1170 个指针。叶子节点存一条完整数据记录假设平均 1 KB，则可存 16 条。三层 B+ 树的理论容量为：

1170 × 1170 × 16 ≈ 21,902,400 条

这也是为什么通常说 InnoDB 三层 B+ 树能支撑千万级数据量，且查询只需 3 次 I/O。

可视化参考：Data Structure Visualizations

---

二、存储引擎与索引存储方式

MySQL 最常用的两种存储引擎在索引实现上存在本质差异。

2.1 MyISAM（非聚集索引）

MyISAM 的索引文件和数据文件是完全分离的：

• .frm：表结构定义文件
• .MYD（MyData）：存放表的实际数据行
• .MYI（MyIndex）：存放索引，B+ 树叶子节点存放的是数据行的物理地址

查询流程：通过 MYI 找到物理地址 → 按地址读取 MYD 中的数据行，需要两次文件 I/O。

2.2 InnoDB（聚集索引）

InnoDB 的索引和数据存储在同一文件中：

• .frm：表结构定义文件（MySQL 8.0 已整合进 .ibd）
• .ibd：存放索引树 + 数据行，两者合二为一

InnoDB 主键索引的 B+ 树叶子节点中直接存储完整的数据行，这种结构称为聚集索引（Clustered Index）。因此，按主键查询只需一次 B+ 树遍历，效率极高。

MySQL 8.0 起取消了独立的 .frm 文件，表定义元数据统一存入数据字典（data dictionary），物理上合并至 .ibd。

2.3 为什么 InnoDB 推荐使用自增整型主键？

这是一个设计层面的常见面试考点，涉及三个维度：

1. 为什么必须有主键？

InnoDB 表的数据组织方式本身就是一棵以主键为键的 B+ 树。如果建表时不显式定义主键，InnoDB 会依次查找：

• 是否存在 NOT NULL 的唯一索引 → 若有，以该列为隐式主键
• 若仍无，内部自动生成一个 6 字节的隐藏列 DB_ROW_ID 作为主键

使用隐藏列作主键的问题：无法被业务利用，且 DB_ROW_ID 是一个表级单调递增计数器（通过互斥锁维护），高并发写入时会产生锁竞争。

2. 为什么推荐整型？

• B+ 树节点的索引比较（查找、插入定位）需要频繁做键值大小比较
• 整型比较（8 字节 bigint）比字符串比较（UUID 通常 36 字节）快且内存占用小
• 非叶子节点能放更多指针，树更"矮胖"，I/O 次数更少

3. 为什么推荐自增？

自增主键保证新插入的行总是追加到当前 B+ 树的最右侧叶子节点，不会触发页分裂。

如果使用随机主键（如 UUID），新行有较大概率插入到已满的中间页，导致频繁的页分裂：MySQL 需要将该页约一半的数据迁移到新页，并更新父节点指针，写入放大严重，在高并发写入场景下性能下降显著。

分库分表场景的补充：当系统需要水平分片时，自增主键会产生跨分片的 ID 冲突，此时应改用分布式 ID 方案（雪花算法 Snowflake ID、Leaf、UidGenerator 等），牺牲单调自增性但保证全局唯一且趋势递增。

---

三、聚簇索引与二级索引

3.1 聚簇索引（主键索引）

InnoDB 中每张表有且仅有一个聚簇索引，即以主键构建的 B+ 树，叶子节点存放完整数据行。

3.2 二级索引（Secondary Index / 辅助索引）

所有非主键索引统称为二级索引（也叫辅助索引）。其 B+ 树的叶子节点存储的是索引列的值 + 对应行的主键值，而非完整数据行。

回表（Bookmark Lookup / Row Lookup）：使用二级索引查询时，先在二级索引 B+ 树中找到主键值，再用该主键值到聚簇索引 B+ 树中查找完整数据行，这个两次 B+ 树遍历的过程称为回表。回表会带来额外的随机 I/O，是二级索引查询性能的主要开销之一。

3.3 覆盖索引（Covering Index）

如果查询所需的所有字段（SELECT 的列 + WHERE/ORDER BY 的列）都包含在索引列中，MySQL 无需回表，直接在索引 B+ 树叶子节点即可获取全部数据，这种情况称为覆盖索引。

-- 假设存在联合索引 idx_name_age(name, age)
-- 以下查询可命中覆盖索引（无需回表）
SELECT name, age FROM employee WHERE name = 'Alice';

-- 以下查询无法覆盖（需要 position 字段，但索引中没有）
SELECT name, age, position FROM employee WHERE name = 'Alice';

执行计划中 Extra 列显示 Using index 即表示命中覆盖索引。

3.4 索引下推（Index Condition Pushdown，ICP）

问题背景：在使用联合索引查询时，对于不满足最左前缀的列，5.6 之前的 MySQL 需要先回表取出完整行，再在 Server 层进行过滤，导致大量无效回表。

ICP 机制：MySQL 5.6 引入索引下推优化（默认开启）。对于联合索引中能在索引层判断的条件，直接在存储引擎层完成过滤，减少回表次数。

-- 联合索引 idx_name_age(name, age)
SELECT * FROM employee WHERE name LIKE 'A%' AND age = 25;

• 无 ICP：先用 name LIKE 'A%' 找到所有符合的索引记录 → 逐一回表 → Server 层过滤 age = 25
• 有 ICP：先用 name LIKE 'A%' 在索引层定位 → 同时在索引层判断 age = 25 → 只对满足两个条件的行回表

执行计划中 Extra 列显示 Using index condition 表示启用了 ICP。

---

四、联合索引与最左前缀原则

4.1 联合索引的数据结构

联合索引按照字段声明顺序，依次对多列进行排序后构建 B+ 树。

以 KEY idx_name_age_pos (name, age, position) 为例，排序规则为：

1. 先按 name 排序（字典序）
2. name 相同时按 age 排序
3. name 和 age 都相同时按 position 排序

这意味着若查询条件不包含 name，则索引对 age 和 position 的排序是混乱的，无法用于快速定位。

4.2 最左前缀原则

联合索引从最左列开始向右匹配，中间不能跳过列，遇到范围查询后续列索引失效。

-- 创建联合索引
KEY idx_name_age_pos (name, age, position) USING BTREE

-- ✅ 命中索引：使用了 name（最左列）
EXPLAIN SELECT * FROM employee WHERE name = 'Bill' AND age = 31;
-- key = idx_name_age_pos

-- ❌ 未命中索引：跳过了 name，直接从 age 开始
EXPLAIN SELECT * FROM employee WHERE age = 30 AND position = 'dev';
-- key = NULL（全表扫描）

-- ❌ 未命中索引：跳过了 name 和 age
EXPLAIN SELECT * FROM employee WHERE position = 'manager';
-- key = NULL（全表扫描）

-- ⚠️ 部分命中：name 命中索引，age 之后因范围查询导致 position 失效
EXPLAIN SELECT * FROM employee WHERE name = 'Bill' AND age > 25 AND position = 'dev';
-- key_len 体现只用了 name + age 两列

MySQL 优化器会对 WHERE 条件中的字段顺序自动调整，因此 WHERE age = 31 AND name = 'Bill' 等价于 WHERE name = 'Bill' AND age = 31，不影响索引命中，不必纠结 SQL 书写顺序。

4.3 联合索引的核心优势

1. 减少索引维护开销：一个 (a, b, c) 联合索引等效于覆盖了 (a)、(a, b)、(a, b, c) 三种查询模式，避免创建多个单列索引
2. 更容易命中覆盖索引：查询字段包含在联合索引内即可避免回表
3. 排序优化：ORDER BY 与联合索引列顺序一致时可直接利用索引有序性，避免 filesort

4.4 单列索引 vs 联合索引

维度	单列索引	联合索引
适用场景	单条件高频查询、基数高的独立查询字段	多条件组合查询、需要覆盖索引优化的场景
索引数量	每列独立维护	一个索引覆盖多种查询模式
写入开销	每个单列索引独立更新	单次更新维护一棵树
典型误区	盲目对每列都建索引	字段顺序设计不合理导致索引利用率低

---

五、EXPLAIN 执行计划详解

EXPLAIN 是分析 SQL 性能瓶颈最直接的工具，在 SELECT 语句前加上 EXPLAIN 关键字，MySQL 会返回该语句的执行计划而非实际执行结果。

EXPLAIN SELECT * FROM employee WHERE name = 'Bill' AND age = 31;

5.1 关键字段解读

字段	说明
id	查询中每个 SELECT 的标识符，id 越大优先执行；相同 id 从上到下执行
select_type	查询类型，见下表
table	当前行访问的表名
type	访问类型，性能从好到差排序，见下表
possible_keys	可能用到的索引列表
key	实际使用的索引，NULL 表示未用索引
key_len	实际使用的索引字节数，联合索引可通过此字段判断用了几列
ref	与索引比较的列或常量
rows	估算需要扫描的行数，越小越好
filtered	存储引擎返回的数据中满足 WHERE 条件的百分比
Extra	额外信息，是优化的重要参考，见下表

5.2 type 字段（访问类型）

性能从优到差依次为：

system > const > eq_ref > ref > range > index > ALL

type 值	场景	说明
system	表只有一行	最优，常量折叠
const	主键或唯一索引等值查询	最多返回一行，结果作为常量处理
eq_ref	JOIN 中被驱动表用主键/唯一索引关联	每次关联恰好匹配一行
ref	非唯一索引等值查询	可能返回多行
range	索引范围查询（>、<、BETWEEN、IN、LIKE 'a%'）	只扫描索引的一个区间
index	全索引扫描	遍历整个索引树，比 ALL 快（索引比数据文件小），但仍是全量扫描
ALL	全表扫描	最差，需重点关注

优化目标：生产环境中核心业务 SQL 的 type 至少应达到 range，关键路径查询应达到 ref 或 const。

5.3 Extra 字段常见值

Extra 值	含义	优化方向
Using index	命中覆盖索引，无需回表	良好状态
Using where	Server 层需进一步过滤，存储引擎未完全过滤	考虑索引覆盖更多条件
Using index condition	启用了索引下推（ICP）	良好状态
Using temporary	使用了临时表（常见于 GROUP BY、DISTINCT）	优化索引或查询结构
Using filesort	内存或磁盘排序，未能利用索引有序性	调整索引顺序以匹配 ORDER BY
Using join buffer	JOIN 时驱动表无法利用索引，使用了连接缓冲区	被驱动表关联列加索引

5.4 key_len 计算规则

key_len 用于判断联合索引命中了几列，计算规则如下：

• int：4 字节，允许 NULL 额外 +1 字节
• bigint：8 字节
• varchar(n)（utf8mb4）：n × 4 + 2 字节（+2 为变长字段长度标识），允许 NULL 再 +1
• char(n)（utf8mb4）：n × 4，允许 NULL +1

示例：idx_name_age_pos(name varchar(24) NOT NULL, age int NOT NULL, position varchar(20))，若 key_len = 102，可推算：

• name：24×4+2 = 98（NOT NULL，无额外 +1）
• age：4（NOT NULL）
• 合计 98+4 = 102，说明联合索引命中了 name + age 两列，position 未参与。

实际排查时，根据字段定义（是否允许 NULL、是否变长）逐列累加 key_len，即可精确判断索引命中了几列。

---

六、索引失效场景分析

以下场景会导致索引无法被使用，是日常 SQL Review 的重点检查项。

6.1 违反最左前缀原则

-- 联合索引 idx_name_age_pos(name, age, position)
-- ❌ 跳过最左列，索引失效
WHERE age = 25 AND position = 'dev';

6.2 索引列上进行函数或计算操作

-- ❌ 对索引列做函数操作，无法走索引
WHERE LEFT(name, 3) = 'Ali';
WHERE create_time + INTERVAL 1 DAY = NOW();

-- ✅ 改写为对常量做操作，保持索引列干净
WHERE create_time >= NOW() - INTERVAL 1 DAY;

6.3 索引列发生隐式类型转换

-- phone 字段为 varchar，传入整型，MySQL 会做隐式转换 → 索引失效
-- ❌ 
WHERE phone = 13812345678;

-- ✅
WHERE phone = '13812345678';

字符串与数字比较时，MySQL 会将字符串转换为数字，导致索引列上存在隐式函数调用，索引失效。

6.4 LIKE 通配符位于最左侧

-- ❌ 前缀通配符无法利用索引有序性
WHERE name LIKE '%Alice%';
WHERE name LIKE '%Alice';

-- ✅ 仅右侧通配符可利用索引
WHERE name LIKE 'Alice%';

若业务确实需要前缀模糊查询，可考虑全文索引（FULLTEXT）或引入 Elasticsearch。

6.5 使用 OR 条件（非全部列有索引时）

-- 若 name 有索引但 salary 没有，OR 导致整体退化为全表扫描
-- ❌
WHERE name = 'Alice' OR salary > 10000;

-- ✅ 改写为 UNION ALL（两个子查询各自走各自的索引）
SELECT * FROM employee WHERE name = 'Alice'
UNION ALL
SELECT * FROM employee WHERE salary > 10000 AND name != 'Alice';

6.6 使用 != 或 <>

不等于操作无法利用 B+ 树的有序性进行范围定位（需要扫描除等值点以外的所有数据），通常退化为全表扫描。若数据分布极度不均（如 99% 的行不满足条件），优化器可能仍走索引，但属于特例。

6.7 IS NULL / IS NOT NULL

取决于字段的 NULL 值比例和统计信息，优化器自行判断是否走索引。建议尽量将字段设为 NOT NULL DEFAULT 以保持行为可预期，同时减少一字节存储。

6.8 范围查询后的联合索引列失效

-- age > 25 是范围查询，导致 position 列的索引失效
WHERE name = 'Bill' AND age > 25 AND position = 'dev';
-- 实际只用到 name + age 两列的索引

---

七、SQL 调优实战方法论

7.1 调优思路总览

慢查询日志定位 → EXPLAIN 分析执行计划 → 确定瓶颈类型 → 针对性优化 → 验证效果

7.2 慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记入慢查询日志

-- 慢查询日志分析工具
-- mysqldumpslow -s t -t 10 /path/to/slow.log  （按总耗时排序，取前10条）
-- pt-query-digest /path/to/slow.log            （Percona 工具，分析更详细）

7.3 覆盖索引优化

避免使用 SELECT *，只查询实际需要的列，通过合理设计联合索引让查询命中覆盖索引，消除回表。

-- 场景：频繁查询用户名和状态
-- 创建覆盖索引
ALTER TABLE user ADD INDEX idx_status_name (status, name);

-- 查询可命中覆盖索引（Extra: Using index）
SELECT name FROM user WHERE status = 1;

7.4 避免深度分页

分页查询 LIMIT offset, size 在 offset 很大时，MySQL 需要扫描 offset + size 行后丢弃前 offset 行，性能随偏移量线性下降。

-- ❌ offset 为 100000，实际扫描 100010 行
SELECT * FROM order_info ORDER BY id LIMIT 100000, 10;

-- ✅ 游标分页（基于上一页最大 id）
SELECT * FROM order_info WHERE id > #{lastMaxId} ORDER BY id LIMIT 10;

-- ✅ 延迟关联写法（先走覆盖索引取主键，再关联取完整数据）
SELECT o.* FROM order_info o
JOIN (SELECT id FROM order_info ORDER BY id LIMIT 100000, 10) t ON o.id = t.id;

7.5 JOIN 优化

InnoDB 的 JOIN 执行算法主要有三种：

• Nested Loop Join（NLJ）：驱动表逐行取出，到被驱动表做索引查找，被驱动表需有索引
• Block Nested Loop Join（BNLJ）：被驱动表无索引时，将驱动表数据放入 join_buffer，减少磁盘 I/O，但内存压力大
• Hash Join（MySQL 8.0.18 引入）：大表等值连接首选，内存充足时性能优于 BNLJ

-- 优化原则
-- 1. 小表驱动大表（MySQL 优化器通常会自动选择）
-- 2. 被驱动表的关联字段必须有索引
-- 3. 复杂多表 JOIN 拆分为多次简单查询（减少锁竞争、提升缓存命中）

-- 查看 join_buffer_size（Block Nested Loop 使用）
SHOW VARIABLES LIKE 'join_buffer_size';

7.6 ORDER BY / GROUP BY 优化

利用索引有序性避免 filesort：

-- 联合索引 idx_name_age(name, age)
-- ✅ ORDER BY 与索引列顺序一致，无需 filesort
SELECT * FROM employee WHERE name = 'Alice' ORDER BY age;

-- ❌ 排序方向不一致，无法利用索引
SELECT * FROM employee WHERE name = 'Alice' ORDER BY age DESC, position ASC;

-- ✅ 排序方向完全一致（均 DESC），8.0 支持降序索引
KEY idx_name_age (name, age DESC)

filesort 内部机制：

• 单路排序：将查询所需的所有列一次性读入 sort_buffer，内存不足时溢出到磁盘（性能更差）
• 双路排序（rowid 排序）：sort_buffer 只存 rowid + 排序列，排序后回表取其他列

可通过调整 sort_buffer_size 和 max_length_for_sort_data 影响排序行为，但更根本的优化是设计合适的索引。

7.7 IN 子查询优化

-- IN 后的子查询在 MySQL 5.6 之前不会走索引，5.6+ 引入子查询物化（Subquery Materialization）
-- ✅ 改写为 JOIN 通常更稳定
SELECT * FROM order_info
WHERE user_id IN (SELECT id FROM user WHERE city = 'Shenzhen');

-- 改写为：
SELECT o.* FROM order_info o
JOIN user u ON o.user_id = u.id
WHERE u.city = 'Shenzhen';

7.8 大批量写入优化

-- ❌ 逐行 INSERT，每次写入触发一次事务提交和索引维护
for each row:
    INSERT INTO t VALUES (...);

-- ✅ 批量 INSERT（每批 500~1000 条为宜）
INSERT INTO t VALUES (...), (...), (...);

-- ✅ 大批量导入时的 InnoDB 优化（减少索引维护和约束检查开销）
SET UNIQUE_CHECKS = 0;          -- 跳过唯一性检查
SET FOREIGN_KEY_CHECKS = 0;     -- 跳过外键检查
-- 执行批量 INSERT ...
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

7.9 将大范围查询拆分为多个小范围

-- ❌ 一次性删除大量数据，持有行锁时间长，阻塞其他写操作
DELETE FROM log WHERE create_time < '2024-01-01';

-- ✅ 分批删除
WHILE rows_deleted > 0:
    DELETE FROM log WHERE create_time < '2024-01-01' LIMIT 1000;
    SLEEP(10ms);  -- 适当让出锁资源

---

八、optimizer_trace 分析工具

当 EXPLAIN 无法完整展示优化器的决策逻辑时（例如为什么优化器放弃了某个索引），可以使用 optimizer_trace 获取优化器内部的详细决策过程。

-- 1. 开启 trace（仅当前会话有效）
SET optimizer_trace = 'enabled=on', optimizer_trace_max_mem_size = 1000000;
SET SESSION optimizer_trace_format = 'JSON';

-- 2. 执行目标 SQL
SELECT * FROM employee WHERE name = 'Bill' AND age = 31;

-- 3. 查看 trace 结果（JSON 格式，内容较长）
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 4. 关闭（避免影响其他会话分析）
SET optimizer_trace = 'enabled=off';

重点关注 trace 中的以下节点：

• rows_estimation：各索引的行数估算，优化器选择索引的核心依据
• considered_execution_plans：备选执行计划及其 cost 评估
• attached_conditions_computation：条件下推的处理过程
• chosen：最终选择的执行计划标记

注意：optimizer_trace 对性能有一定影响，不建议在生产环境长期开启，仅用于排查特定 SQL 的优化器行为。

---

九、索引设计规范

9.1 适合建索引的场景

• 频繁作为 WHERE 条件的字段
• 经常用于 ORDER BY、GROUP BY 的字段
• JOIN 关联字段（外键列）
• 基数（Cardinality）较高的列（如用户 ID、手机号），区分度低的列（如性别、状态）索引效果差

9.2 不适合建索引的场景

• 数据量极小的表（全表扫描比 B+ 树查找更快）
• 频繁写入但读取较少的表（索引维护开销超过查询收益）
• 极低基数字段（如 is_deleted、gender）
• 从不出现在查询条件中的列

9.3 索引数量控制

• 单表索引数量建议不超过 5~6 个（具体视业务而定）
• 多个单列索引并不等价于一个联合索引，优化器同一条 SQL 只会选择一个最优索引（5.0 之后通过 index merge 有限支持多索引，但可靠性差）
• 定期使用 information_schema.STATISTICS 或 sys.schema_unused_indexes 检查长期未被使用的索引并清理

9.4 索引维护建议

-- 查看表的索引详情
SHOW INDEX FROM employee;

-- 查看索引的基数（Cardinality）
-- Cardinality 越接近表总行数，区分度越高，索引越有效
SHOW INDEX FROM employee\G

-- 统计信息过旧时手动更新
ANALYZE TABLE employee;

-- 检查未使用的索引（MySQL 8.0）
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';

---

附录：索引优化口诀（速查版）

全值匹配最优先，最左前缀要遵守
范围之后全失效，索引列上莫计算
隐式转换要避免，LIKE 通配写右侧
OR 需两侧都有索，覆盖索引少回表
SELECT * 要少写，EXPLAIN 是好帮手

---

参考资料：

• MySQL 8.0 官方文档 - Optimization
• MySQL 8.0 官方文档 - EXPLAIN Output Format
• 《高性能 MySQL》（第四版）
• Data Structure Visualizations