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

又来提升英雄熟练度了~~ 今天专注提升 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 层就能存千万行）

分类

聚簇索引（主键索引）

• 核心特点：以主键为索引键，叶子节点直接存储完整数据记录，整个表的物理存储顺序与索引顺序一致

• 注意：一张表只能有一个聚簇索引，若未显式定义主键，InnoDB 会选择唯一非空索引，若无则生成隐藏主键（row_id）

非聚簇索引（二级索引）

• 核心特点：以非主键字段为索引键，叶子节点仅存储索引键 + 主键值 ，查询时需通过主键回查聚簇索引获取完整数据（即 “回表”）

使用规则

最左匹配原则

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

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

• 命中索引 WHERE a=1WHERE a=1 AND b=2WHERE 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，大小限制 102400SET optimizer_trace = "enabled=on", end_markers_in_json=on;SET optimizer_trace_max_mem_size = 102400;

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

-- 执行选错索引的 SQLSELECT * 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）

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

type

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

key

实际使用的索引

• 若 key=NULL：说明没走任何索引，大概率是慢 SQL（除非表数据极少，如几十条）。

• 若 key≠possible_keys 优化器从 可能的索引 中选了一个更优的比如统计信息显示某索引扫描行数更少

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

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

• possible_keysidx_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 执行的细节

总结

1. 看 type：优先保证 type 是 const/ref/range，避免 ALL/index。

2. 看 key：key 不为 NULL 是基础，尽量用联合索引覆盖多条件。

3. 看 rows：预估行数越小越好，超过 1000 需检查索引是否精准。

4. 看 Extra：杜绝 Using filesort/Using temporary，追求 Using index。

SQL 优化

子查询优化

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

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

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

案例

-- 查“有订单的用户”信息，子查询获取有订单的 user_idSELECT 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 uINNER 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 > 1000000ORDER 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 计数+1redisTemplate.opsForValue().increment("user:total:count", 1);
// 2. 删除用户时，Redis 计数-1redisTemplate.opsForValue().decrement("user:total:count", 1);
// 3. 查询总行数，直接读 RedisLong 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 查询这三大高频痛点给出解决方案

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

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