Java 王者修炼手册【Mysql篇 - 索引+ SQL优化】：拆解索引 + 执行计划 + SQL优化核心原理

大家好，我是程序员强子。

又来提升英雄熟练度了~~ 今天专注提升 Mysql的索引 + Sql 优化相关~~

来看看今天有哪些知识点：

索引
- B+树与红黑树对比
- 最左匹配原则/索引下推/前缀索引/索引合并机制..
- 索引与排序
执行计划
- 字段详解
SQL优化实战
- 子查询
- 深度分页
- count查询

来不及解释了，快点上车~

索引

B+树

层级结构

根节点 / 枝节点
- 仅存储索引键 + 下一层节点指针，不存实际数据，确保树的高度极低
- 通常 2-4 层，支持千万级数据快速定位
叶子节点
- 所有叶子节点通过双向链表连接，方便范围查询和排序；
- 存储内容分两种对应聚簇 / 非聚簇索引

核心优势

范围查询（如 BETWEEN）
排序（如 ORDER BY）
磁盘 I/O 次数固定，比较少，取决树的高度

为什么选 B+ 树而非红黑树？

树的高度
- 红黑树是二叉树，数据量越大，树的高度越高（千万级数据可能达 30 层），每次查询需多次磁盘 I/O；
- B+ 树是多路平衡树，高度固定在 2-4 层，I/O 次数极少
范围查询能力
- 红黑树需遍历整棵树才能完成范围查询；
- B+ 树叶子节点是有序链表，直接遍历链表即可，效率远超红黑树
数据存储密度
- B+ 树非叶子节点仅存索引键和指针，单节点可存储更多索引项，进一步降低树高；
- 红黑树每个节点存完整数据，存储密度低

为什么B+非叶子节点不保存整行数据？

InnoDB每页 16KB 固定，假设主键 + 指针≈14 字节，数据≈100 字节；

B 树（非叶子存数据）：一页只能存≈16384÷(14+100)≈143 个条目；
B + 树（非叶子不存数据）：一页能存≈16384÷14≈1170 个条目。

B + 树的 目录层 能塞更多索引，树更矮（2-3 层就能存千万行）

使用规则

最左匹配原则

联合索引的索引键按定义顺序排序，查询时必须从左到右匹配，中间不能中断，否则无法命中索引。

案例：联合索引 idx_a_b_c(a, b, c)

命中索引
- WHERE a=1
- WHERE a=1 AND b=2
- WHERE a=1 AND b=2 AND c=3
未命中索引
- WHERE b=2（跳过 a）
- WHERE a=1 AND c=3（中断 b）

覆盖索引

查询的所有字段都包含在索引中，无需回表

示例：

假设学生表student有字段：id（主键）、name、age、score，创建联合索引idx_age_score(age, score)：

普通查询（需回表）
- SELECT id, name FROM student WHERE age=18;
- name不在索引中，需先查idx_age_score得主键id，再查聚簇索引拿name。
覆盖索引查询（无需回表）
- SELECT age, score FROM student WHERE age=18;
- 查询字段age、score均在idx_age_score中，直接从辅助索引返回数据

简单来说：直接 绕过回表，查询字段全在索引中；

索引下推

目的就是为了极少 回表次数

存储引擎在遍历索引时，先过滤索引中能匹配的条件，再将结果返回给服务器层

示例

以上面student表的联合索引idx_name_age(name, age)为例

SELECT * FROM student WHERE name LIKE '岑%' AND age=18;

如果是无索引下推：

存储引擎先找出所有name以 “张” 开头的索引记录，返回对应的主键（比如 100 个）
服务器层拿着这 100 个主键查聚簇索引（回表） ，逐一判断age=18，最终可能只留 10 个符合的

有索引下推：

存储引擎遍历索引时，同时检查age=18
仅返回符合name LIKE '张%' AND age=18的主键（比如 10 个）
服务器层只需回表 10 次，效率大幅提升

有适用条件：

仅适用于辅助索引（聚簇索引叶子节点是数据，无需下推）；
支持联合索引的后续字段过滤、like前缀匹配（%张不行，因为无法走索引）、数值比较等条件。

简单来说： 减少回表次数，先过滤索引中的条件再回表

前缀索引

对长字符串字段（如手机号、邮箱），仅取前 N 个字符建立索引

案例：对手机号字段建立前缀索引

CREATE INDEX idx_phone_prefix ON user(phone(11)); -- 手机号固定 11 位，取完整长度
-- 若为邮箱，可根据分布取前 6 位：CREATE INDEX idx_email_prefix ON user(email(6));

范围条件后的字段无法命中

若联合索引中某字段用范围条件（>、<、BETWEEN 等）

该字段右侧的索引字段无法被利用

案例：联合索引 idx_a_b_c(a,b,c)

SELECT * FROM t WHERE a=1 AND b>2 AND c=3;

实际命中：仅 a=1 和 b>2 部分，c=3 无法利用索引（因 b 是范围条件，后续字段顺序被打乱）
优化：若 c 条件频繁使用，可调整索引顺序为 (a,c,b)

查询命中多个独立索引

当查询条件涉及多个字段，且每个字段都有独立索引

比如 a 有索引 idx_a，b 有索引 idx_b）

MySQL 可能会触发索引合并（Index Merge）

那什么是索引合并呢？来来来，跟强子仔细研究一下

索引合并

交集合并

多条件用 AND 连接，取多个索引结果的交集

需满足条件：每个索引都能精准匹配，如 =、IN

案例：WHERE a=1 AND b=2（a 和 b 各有单列索引）

分别用 idx_a 查 a=1 的主键列表

用 idx_b 查 b=2 的主键列表

取两个列表的交集，再回表查数据

并集合并

多条件用 OR 连接，取多个索引结果的并集

需满足条件：每个索引都能匹配部分条件，且无范围查询

案例：WHERE a=1 OR b=2（a 和 b 各有单列索引）

分别用 idx_a 和 idx_b 查结果，合并去重后回表

排序合并

多条件用 OR 连接，但包含范围查询，

需先排序再合并（效率较低）

案例：WHERE a>1 OR b>2（a 和 b 各有单列索引）

局限性

效率低：需扫描多个索引，再合并结果（涉及排序、去重），成本高于扫描单个联合索引。
适用场景窄：仅支持简单条件（AND/OR 连接的等值或范围查询），复杂条件（如 GROUP BY、ORDER BY）无法合并。
回表次数多：多个索引的结果需分别回表，比联合索引的单次回表成本高

常见索引失效场景

索引字段用函数 / 表达式

WHERE SUBSTR(phone,1,3)='138'

无法命中 phone 索引

隐式类型转换

WHERE phone=13800138000

phone 是字符串，与数字比较

模糊查询

WHERE name LIKE '%张三'

前缀模糊无法命中索引

联合索引中断匹配

上文案例： idx_a_b_c 中跳过中间字段

使用 OR 连接非索引字段

WHERE a=1 OR b=2

若 b 无索引，整个查询无法命中 a 的索引

不知道大家会不会遇到这个问题：

明明有索引却走全表
明明看着索引A更符合条件，却走了索引B

可用 trace 工具查看优化器的 “决策过程”，分析成本计算、索引筛选逻辑~

那什么是trace工具呢？接下来跟强子仔细研究一下~

trace工具

核心作用

查看优化器如何计算 全表扫描成本 与 索引扫描成本
定位为什么优化器不选预期的索引，比如统计信息过时、成本估算偏差

使用步骤

步骤 1：开启 trace

-- 开启 trace，设置格式为 JSON，大小限制 102400
SET optimizer_trace = "enabled=on", end_markers_in_json=on;
SET optimizer_trace_max_mem_size = 102400;

步骤 2：执行目标 SQL（如慢查询）

-- 执行选错索引的 SQL
SELECT * FROM user WHERE age BETWEEN 20 AND 30;

步骤 3：查看 trace 结果

-- 查询 trace 日志，重点看 join_optimization 阶段
SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

步骤 4：分析关键信息

在 trace 结果的 join_optimization → considered_execution_plans 中，可看到：

cost：全表扫描成本（如 1000）与索引扫描成本（如 1200），优化器会选成本低的。
index：优化器考虑的索引列表（如 idx_age），若索引未被考虑，可能是统计信息过时。

步骤 5：关闭 trace

SET optimizer_trace = "enabled=off";

排序

MySQL 排序分为 索引排序和 文件排序，两者性能差异极大

索引排序

利用索引本身的有序性，直接从索引中获取有序数据

无需额外排序操作

ORDER BY 的字段顺序、排序方向（ASC/DESC）与索引完全一致，且满足最左匹配原则。

案例：表 user 有联合索引 idx_age_name(age ASC, name ASC)，执行查询

SELECT age, name FROM user ORDER BY age ASC, name ASC;

文件排序

当无法利用索引排序时，MySQL 需要先从表中读取数据

再在内存或磁盘中对结果集进行排序

内存排序有什么特点？

当待排序数据量 ≤sort_buffer_size（默认 256KB）时，直接在内存的 排序缓冲区中完成排序

步骤：

从表中读取满足 WHERE 条件的记录，将 ORDER BY 字段和主键（用于回表取完整数据）存入排序缓冲区；
在排序缓冲区中按 ORDER BY 字段排序；
按排序结果的主键回表，获取完整数据并返回

磁盘排序有什么特点？

当待排序数据量＞ sort_buffer_size 时，需借助临时文件分块排序，再合并结果

步骤：

分块读取数据，每块大小不超过 sort_buffer_size，在内存中排序后写入临时文件；
对所有临时文件进行 归并排序（多路合并），得到全局有序的结果；
按排序结果的主键回表，返回完整数据

执行计划

EXPLAIN 输出共 8 个核心字段（id、select_type、table、type、possible_keys、key、rows、Extra）

字段名	核心作用
id	标记查询的执行顺序（id 相同按从上到下执行，id 越大越先执行，如子查询）
select_type	区分查询类型（如 SIMPLE 简单查询、SUBQUERY 子查询、DERIVED 派生表）
table	当前行操作的表名（可能是表别名或派生表编号，如 `<font style="color:rgb(0, 0, 0);"><derived2></font>` ）
type	访问类型（性能优先级：const > eq_ref > ref > range > index > ALL）
possible_keys	优化器认为 “可能适用” 的索引（不一定实际使用）
key	实际使用的索引（NULL 表示没走任何索引，需重点关注）
rows	预估扫描的行数（数值越小越好，直接反映查询的 “范围大小”）
Extra	额外执行信息（包含关键优化点，如 Using index、Using filesort）

接着跟着强子脚步，重点字段解读~

type

type 代表 MySQL 查找数据的方式，优先级从高到低对应性能从快到慢

type	适用场景	案例
const	主键 / 唯一索引的等值查询（仅查 1 条数据，速度最快）	SELECT * FROM user WHERE id=1; id 是主键
eq_ref	多表 JOIN 时，关联字段是主键 / 唯一索引（每张表仅匹配 1 条）	SELECT * FROM user u JOIN order o ON u.id=o.user_id; o.user_id 是主键
ref	普通索引的等值查询（匹配多条，但比全表快）	SELECT * FROM user WHERE phone='13800138000'; phone 是普通索引
range	索引的范围查询（如 BETWEEN、>、IN 等）	SELECT * FROM user WHERE age BETWEEN 20 AND 30; age 是索引
index	扫描整个索引树（比全表快，但仍需遍历所有索引项）	`SELECT COUNT(*) FROM user;` 有索引 idx_age，扫描整个索引计数
ALL	全表扫描（遍历所有数据行，性能最差，必须优化）

key

实际使用的索引

若 key=NULL：说明没走任何索引，大概率是慢 SQL（除非表数据极少，如几十条）。
若 key≠possible_keys
- 优化器从 可能的索引 中选了一个更优的
- 比如统计信息显示某索引扫描行数更少

案例：表 user 有索引 idx_phone（phone）和 idx_email（email）

执行 SELECT * FROM user WHERE phone='13800138000';

possible_keys
- idx_phone,idx_email
- 优化器认为两个索引都可能用，但实际只需要 idx_phone
key：idx_phone（实际使用的索引）

rows

预估扫描行数

是优化器根据统计信息估算的 需要扫描的行数，数值越小，查询范围越精准

全表扫描（type=ALL）：rows 接近表的总数据量（如 10 万条）。
索引命中（type=ref）：rows 可能只有几十或几百条（如查 phone='13800138000'，rows=1）

rows 是 预估值

若统计信息过时（如刚批量插入数据未更新统计），可能与实际行数偏差大

需用 ANALYZE TABLE 表名刷新统计

Extra

Extra 包含 SQL 执行的细节

Extra 信息	含义与影响	案例
Using index	覆盖索引查询字段均在索引中，无需回表，性能优	SELECT id,phone FROM user WHERE phone='13800138000'; 索引 idx_phone 包含 id 和 phone
Using filesort	文件排序无法用索引排序，需在内存 / 磁盘排序，性能差	SELECT * FROM user ORDER BY age; age 无索引
Using temporary	临时表需创建临时表存储中间结果，如 GROUP BY 非索引字段，性能差	SELECT name,COUNT(*) FROM user GROUP BY name; name 无索引
Using where	过滤条件在执行器处理索引仅用于定位数据，需再过滤，无性能问题	SELECT * FROM user WHERE age>20; age 是索引，先查索引再过滤
Using join buffer	多表 JOIN 未走索引，用内存缓冲区暂存数据性能差，需加关联索引

总结

看 type：优先保证 type 是 const/ref/range，避免 ALL/index。
看 key：key 不为 NULL 是基础，尽量用联合索引覆盖多条件。
看 rows：预估行数越小越好，超过 1000 需检查索引是否精准。
看 Extra：杜绝 Using filesort/Using temporary，追求 Using index。

SQL优化

子查询优化

子查询（尤其是关联子查询）容易产生临时表（Using temporary），导致频繁磁盘 I/O；

子查询会将中间结果存入临时表，若子查询结果量大，临时表的创建和读取会严重拖慢性能

JOIN 可通过索引直接关联，效率更高

案例

-- 查“有订单的用户”信息，子查询获取有订单的 user_id
SELECT id, name FROM user 
WHERE id IN (SELECT user_id FROM `order` WHERE status = 1);

执行结果

子查询生成临时表存储 user_id，再与 user 表匹配，耗时 300ms，
EXPLAIN 显示 Extra=Using temporary

优化后

-- 用 INNER JOIN 关联，order.user_id 加索引
SELECT DISTINCT u.id, u.name 
FROM user u
INNER JOIN `order` o ON u.id = o.user_id 
WHERE o.status = 1;

-- 建索引：关联字段加索引（关键）
CREATE INDEX idx_order_user_id ON `order`(user_id, status); -- 覆盖 status 过滤条件

执行结果

通过索引直接关联两张表，无临时表，耗时 50ms
EXPLAIN 显示 type=ref

深度分页优化

LIMIT 1000000, 10 会扫描前 1000010 条数据，再丢弃前 1000000 条，效率极低；

优化核心是 用索引定位起始位置

案例

SELECT id, name, create_time FROM user 
ORDER BY create_time DESC 
LIMIT 1000000, 10;

执行结果

扫描 1000010 条数据，耗时 800ms
EXPLAIN 显示 Using filesort

书签法（游标分页）

通过 唯一索引字段（如 id、create_time+id）记录上次查询的最后一个值

直接跳转到起始位置，避免扫描无用数据

-- 1. 先查上一页最后一条数据的 create_time 和 id（假设上一页最后一条 id=1000000，create_time='2024-01-01'）
-- 2. 用“create_time < 上一页时间 AND id > 上一页 id”定位起始位置（避免时间重复导致漏数据）
SELECT id, name, create_time FROM user 
WHERE create_time < '2024-01-01' AND id > 1000000
ORDER BY create_time DESC, id DESC 
LIMIT 10;

-- 建索引：覆盖排序和过滤条件（关键）
CREATE INDEX idx_user_create_id ON user(create_time DESC, id DESC);

执行结果

直接扫描 10 条数据，耗时 20ms
EXPLAIN 显示 type=range、Extra=Using index

count查询

优化核心是 避免每次扫表统计

通过缓存或 统计表 提前存储结果

方案 1：用 Redis 缓存计数

增删数据时同步更新 Redis 的计数 key，查询时直接读 Redis，避免扫表。

// 1. 新增用户时，Redis 计数+1
redisTemplate.opsForValue().increment("user:total:count", 1);

// 2. 删除用户时，Redis 计数-1
redisTemplate.opsForValue().decrement("user:total:count", 1);

// 3. 查询总行数，直接读 Redis
Long total = redisTemplate.opsForValue().get("user:total:count");

方案 2：用统计表存储计数

建一张专门的计数表，记录各表的总行数，增删数据时更新计数表，查询时读计数表

-- 1. 建计数表
CREATE TABLE table_count (
  table_name VARCHAR(50) PRIMARY KEY, -- 表名
  total_count BIGINT NOT NULL         -- 总行数
);

-- 2. 初始化 user 表计数
INSERT INTO table_count VALUES ('user', (SELECT COUNT(*) FROM user));

-- 3. 新增用户时，更新计数表
INSERT INTO user (name, phone) VALUES ('张三', '13800138000');
UPDATE table_count SET total_count = total_count + 1 WHERE table_name = 'user';

-- 4. 查询 user 表总行数，直接读计数表
SELECT total_count FROM table_count WHERE table_name = 'user';

总结

今天强子带我们本文围绕 MySQL 性能优化核心方向展开

先对比了 B + 树与红黑树在索引场景的适配差异，
详解最左匹配原则、索引下推等关键索引机制，
厘清了索引与排序的内在关联；
再拆解执行计划的核心字段，为优化提供判断依据；
最后落地到 SQL 实战优化，针对子查询、深度分页、count 查询这三大高频痛点给出解决方案

这些知识点既是面试高频考点，也是日常开发中提升系统性能的关键~

熟练度刷不停，知识点吃透稳，下期接着练～

Java 王者修炼手册【Mysql篇 - 索引+ SQL优化】：拆解 索引 + 执行计划 + SQL优化 核心原理

索引

B+树

层级结构

核心优势

分类

聚簇索引（主键索引）

非聚簇索引（二级索引）

使用规则

最左匹配原则

覆盖索引

索引下推

前缀索引

范围条件后的字段无法命中

查询命中多个独立索引

索引合并

交集合并

并集合并

排序合并

局限性

常见索引失效场景

索引字段用函数 / 表达式

隐式类型转换

模糊查询

联合索引中断匹配

使用 OR 连接非索引字段

trace工具

核心作用

使用步骤

排序

索引排序

文件排序

执行计划

type

key

rows

Extra

总结

SQL优化

子查询优化

案例

优化后

深度分页优化

案例

书签法（游标分页）

count查询

方案 1：用 Redis 缓存计数

方案 2：用统计表存储计数

总结

Java 王者修炼手册【Mysql篇 - 索引+ SQL优化】：拆解索引 + 执行计划 + SQL优化核心原理