当前位置：首页 > news >正文

SQL 性能调优：EXPLAIN 详解与慢查询优化案例

news 2026/8/15 1:08:38

各位架构师、数据库的“老中医”，大家好！今天我们来聊聊数据库的“体检报告”——EXPLAIN。

当你的接口响应慢得像蜗牛，CPU 飙升得像火箭时，千万别急着重启数据库，也别盲目地加索引。这时候，你需要的是给 SQL 拍一张“X 光片”，看看它到底是在“跑步”（高效索引扫描），还是在“散步”（全表扫描）。

今天，我们用硬核的方式，“彻底”拆解 SQL 性能调优。

第一步：捕捉“嫌疑人”——慢查询日志

在优化之前，你得先知道是谁在拖后腿。MySQL 有个自带的“监控摄像头”，叫慢查询日志。

开启方式（临时生效）：

-- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; -- 设置阈值：超过 2 秒的 SQL 才会被记录（生产环境建议设为 1 或 0.5） SET GLOBAL long_query_time = 2;

分析工具：
日志文件通常位于/var/lib/mysql/slow-query.log。别用记事本一行行看，太累！用 MySQL 自带的工具mysqldumpslow：

# 按查询时间排序，取前 10 条“最慢”的 SQL mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log

第二步：拍“X 光片”——EXPLAIN 详解

拿到慢 SQL 后，在它前面加上EXPLAIN，就能得到它的执行计划。

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

结果里字段很多，别慌。作为架构师，你只需要重点关注三个“命门”：type、key和Extra。

1. type：扫描类型（性能的生命线）

这是最重要的指标，它决定了 MySQL 是怎么找数据的。性能从优到差，等级森严：

const / eq_ref：这是“特快专递”。通过主键或唯一索引查询，直接定位，只读一行。
ref：这是“普通快递”。通过普通索引查询，找到匹配的行。
range：这是“区间扫描”。比如WHERE id > 10，只扫描一部分索引。
index：这是“全索引扫描”。虽然也是扫描，但只扫索引树，不扫数据页，比全表快一点。
ALL：这是“地毯式搜索”。全表扫描！看到ALL，就像看到医生在体检报告上写了个“危”，必须优化！

洞察：
一般要求 SQL 至少达到range级别，最好是ref。如果是ALL，说明你的索引在“罢工”。

2. key：实际使用的索引

key：MySQL 实际用了哪个索引。如果是NULL，说明没走索引。
possible_keys：MySQL 觉得可以用哪些索引。
注意：如果possible_keys有一堆索引，但key是NULL，说明 MySQL 的优化器“犯傻”了，或者索引失效了。

3. Extra：额外信息（隐藏的性能杀手）

这里的信息量最大，也是“坑”最多的地方：

Using index：完美！覆盖索引。MySQL 直接在索引里就找到了所有需要的数据，连表都不用回（不用查数据页）。
Using where：普通。需要在存储引擎层根据条件过滤。
Using filesort：警告！文件排序。说明 MySQL 无法利用索引来完成排序，必须把数据取出来放到内存或磁盘上单独排序。这是性能杀手！
Using temporary：严重警告！使用了临时表。常见于GROUP BY或ORDER BY字段不一致时。先把数据塞进临时表，处理完再返回，效率极低。

第三步：对症下药——常见优化套路

1. 索引为什么会“迷路”（失效）？

明明建了索引，为什么type还是ALL？通常是因为你触犯了“索引禁忌”：

对索引列“动刀”：

-- 错误：在索引列上做计算，索引直接报废 SELECT * FROM orders WHERE YEAR(create_time) = 2026; -- 正确：把计算移到右边 SELECT * FROM orders WHERE create_time >= '2026-01-01';

模糊查询的前导通配符：

-- 错误：LIKE '%abc'，因为索引是从左到右的，前面模糊相当于大海捞针 -- 正确：LIKE 'abc%'，走范围扫描

类型隐式转换：

-- 错误：phone 字段是字符串，你却传了数字 SELECT * FROM users WHERE phone = 13800000000; -- 正确：加引号 SELECT * FROM users WHERE phone = '13800000000';

2. 分页优化：LIMIT 1000000, 10 的痛

当用户翻到第 100 万页时，你的 SQL 可能会慢死：

SELECT * FROM products LIMIT 1000000, 10;

原理：MySQL 会扫描前 1000010 条记录，然后丢弃前 1000000 条，只返回最后 10 条。这简直是浪费生命！

优化方案（延迟关联）：
利用覆盖索引，先只查主键，再回表。

SELECT p.* FROM products p JOIN (SELECT id FROM products LIMIT 1000000, 10) AS tmp ON p.id = tmp.id;

解释：子查询tmp利用了覆盖索引（只查 id），速度极快。拿到 10 个 id 后，再跟原表关联，瞬间完成。

索引绝对不是越多越好

索引是一把双刃剑：它在加速查询（读操作）的同时，会显著拖慢数据的写入和更新（写操作），并消耗宝贵的存储和内存资源。索引是用空间和写入性能换取读取性能的工具。优秀的程序员不会盲目堆砌索引，而是像狙击手一样，精准地只为最关键的查询路径提供支援。

写入性能的“多米诺骨牌”效应

这是索引过多最直接的代价。在 InnoDB 引擎中，数据的增删改（INSERT/UPDATE/DELETE）不仅仅是修改数据页，还必须同步维护所有相关的二级索引。

底层原理：
当你插入一行数据时，数据库不仅要写入聚簇索引（主键索引），还要找到该行数据在所有二级索引树中的位置并插入。
如果一张表有 10 个索引，一次INSERT操作实际上变成了11 次磁盘 I/O 操作（1次数据页 + 10次索引页）。
更糟糕的是，这会导致频繁的页分裂（Page Split）。为了保持 B+ 树的有序性，插入新数据可能导致索引页满了，需要分裂出新页，这会极大地消耗 CPU 和 I/O 资源。

内存（Buffer Pool）的“挤兑”

数据库的性能很大程度上依赖于内存缓存（如 MySQL 的 Buffer Pool）。内存是有限的资源。

底层原理：
- 索引也是要加载到内存中的。如果你建立了大量低频使用的索引，这些索引页会挤占 Buffer Pool 的空间。
- 结果就是：真正热点的数据页（Data Page）被置换出内存，导致核心业务查询时发生大量的磁盘 I/O（缺页中断），反而降低了整体系统的吞吐量。

查询优化器的“选择困难症”

你可能认为索引多了，优化器（Optimizer）的选择就多了，查询会更快。其实恰恰相反。

底层原理：
- 当一张表上有几十个索引时，MySQL 的查询优化器在生成执行计划时，需要计算和评估每一条路径的成本。
- 这不仅增加了 SQL解析阶段的 CPU 开销，还可能导致优化器“眼花”，错误地选择了一个次优索引（比如选了区分度很低的索引），导致查询性能不升反降。

冗余与维护成本

很多索引其实是重复的，或者根本用不上。

最左前缀原则的冗余：
如果你已经建立了一个联合索引(a, b)，那么单独给a再建一个索引就是完全多余的。因为(a, b)索引的最左前缀已经覆盖了a的查询需求。
维护噩梦：
随着数据量的增长，索引会产生碎片（Fragmentation）。索引越多，碎片整理（OPTIMIZE TABLE或Rebuild Index）的时间就越长，线上运维的风险也越大。

什么时候索引会“失效”？

即使你建了很多索引，如果写法不对，它们也会全部失效，变成摆设。以下情况索引会“迷路”：

对索引列做运算：WHERE YEAR(create_time) = 2026（索引失效，全表扫描）。
模糊查询前导通配符：LIKE '%abc'（索引失效）。
类型隐式转换：字符串字段没加引号phone = 1380000（索引失效）。

“索引设计法则”

为了平衡读写性能，建议遵循以下原则：

按需创建，少而精：
只给查询频率高、区分度大（基数大）的字段建索引。不要给“性别”、“状态”这种只有几个值的字段单独建索引。
利用联合索引（覆盖索引）：
尽量使用联合索引（如(a, b, c)）来覆盖多个查询场景，减少回表操作。
定期清理：
利用sys.schema_unused_indexes(MySQL 5.7+) 或 Performance Schema 定期排查从未使用的索引，果断删除。
写入优先场景：
对于日志表、流水表这种写多读少的表，尽量少建索引，甚至只保留主键索引，以保证写入吞吐量。