codesphere

外观

SQL数据存储2,511 字 · 约 6 分钟

读懂 Explain：从执行计划找到慢查询的真正原因 ​

EXPLAIN 的每一列都有定义，但逐列背下来并不能帮你优化 SQL。真正有用的是一套读法：先看访问类型和扫描行数，再看 Extra 里的额外代价，最后用 EXPLAIN ANALYZE 拿实际数据验证。

本文所有输出都来自同一张确定性生成的 10 万行订单表，在 MySQL 8.4.11 上实测，脚本与原始输出见文末配套实验：

sql
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT           NOT NULL,
  status      VARCHAR(16)   NOT NULL,   -- CREATED / PAID / SHIPPED / CLOSED 各约 1/4
  amount      DECIMAL(10,2) NOT NULL,
  phone       VARCHAR(20)   NOT NULL,
  created_at  DATETIME      NOT NULL,
  KEY idx_customer_created (customer_id, created_at),
  KEY idx_phone (phone)
);
-- 10 万行，5000 个客户，每个客户 20 单，订单日期分布在 240 天内

一、先说结论 ​

  • 先看 type 和 rows。 type 说明怎么找数据，rows 说明预计要读多少行。ALL 配上几十行无所谓，ref 配上几十万行才是问题。
  • 再看 Extra。 Using filesort、Using temporary 表示额外的排序和临时表；Using index 表示覆盖索引，不需要回表。
  • key_len 能看出联合索引用了几列。 它是判断「最左前缀到底命中了几列」最直接的依据。
  • rows 只是估算。 MySQL 8.0.18 起可以用 EXPLAIN ANALYZE 真正执行一次，拿到每一步的实际行数和耗时；估算与实际差距大时，问题往往出在统计信息或条件写法上。
  • 索引失效的常见原因很具体：在索引列上套函数、字符串列和数字比较、联合索引缺少最左列、排序字段不在索引里。

二、三列定方向：type、rows、key ​

2.1 type：怎么找数据 ​

扫描范围越来越大const最多一行主键 / 唯一索引等值eq_ref联表时每次一行被驱动表唯一索引ref可能多行非唯一索引等值range索引范围> < BETWEEN INindex扫描整棵索引只是省了回表ALL全表扫描逐行读聚簇索引
图 1 · type 列从左到右，需要扫描的范围越来越大；range 往往可以接受，index 和 ALL 需要结合 rows 判断

还有 system（表只有一行）和 NULL（优化阶段就得出结果，不需要访问表）两种特殊情况，实际业务中较少关注。

实测对比：同一个客户的订单，按客户查和按日期查：

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- type=ref    key=idx_customer_created  key_len=4  rows=20

EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-06-01';
-- type=ALL    key=NULL                  rows=99841  filtered=33.33

第二条没有以 created_at 开头的索引，idx_customer_created 的第一列是 customer_id，用不上，只能全表扫描。

2.2 rows 与 filtered：预计读多少、留下多少 ​

  • rows：存储引擎预计要读取的行数。
  • filtered：读出来的行中，预计有百分之多少满足剩余条件。

两者相乘约等于这一步输出的行数。上面第二条查询 rows=99841、filtered=33.33，意思是「读近 10 万行，留下约三分之一」。读得多、留得少是最典型的浪费。

2.3 key 与 key_len：联合索引用了几列 ​

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND created_at >= '2026-06-01';
-- type=range  key=idx_customer_created  key_len=9  rows=8  Extra=Using index condition

key_len=9 等于 INT（4 字节）加 DATETIME（5 字节），说明联合索引的两列都用上了；只按 customer_id 查询时是 4。

常见类型的长度可以这样估算：INT 4、BIGINT 8、DATETIME 5、VARCHAR(n) 在 utf8mb4 下为 n × 4 + 2，列允许 NULL 时再加 1。实测 idx_phone 的 key_len 是 82，正好是 VARCHAR(20) 的 20 × 4 + 2。

三、Extra：藏在最后一列的额外代价 ​

值含义需要处理吗
Using index覆盖索引，所需列都在索引里，不回表好事
Using index condition索引条件下推（ICP），在存储引擎层先用索引过滤好事
Using whereServer 层还要再过滤看 rows 与 filtered
Using filesort需要额外排序，数据量大时会用磁盘大结果集时需要处理
Using temporary需要临时表，常见于 GROUP BY、DISTINCT大结果集时需要处理
Backward index scan反向扫描索引来满足 DESC 排序好事，避免了排序

3.1 覆盖索引：只查索引里有的列 ​

sql
EXPLAIN SELECT id, created_at FROM orders WHERE customer_id = 42 AND created_at >= '2026-06-01';
-- type=range  key_len=9  Extra=Using where; Using index

id 是主键，二级索引的叶子节点本来就存着它，所以这条查询完全不需要回到聚簇索引。把 SELECT * 改成只取需要的列，是成本最低的优化之一。

3.2 排序字段在不在索引里 ​

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY amount DESC LIMIT 10;
-- type=ref  rows=20  Extra=Using filesort

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
-- type=ref  rows=20  Extra=Backward index scan

同一个客户下，索引里的 created_at 已经有序，按它排序可以直接反向读索引；amount 不在索引里，只能先取出 20 行再排序。20 行的排序无所谓，但如果某个大客户有几十万单，Using filesort 就会变成瓶颈。

四、EXPLAIN ANALYZE：用实际数据验证估算 ​

EXPLAIN 只给估算，EXPLAIN ANALYZE 会真正执行查询，并输出每一步的实际耗时和行数。它输出的是树形结构，读法和普通 EXPLAIN 的表格不同：

执行计划节点（缩进越深越先执行）actual rowsLimit: 5 row(s)55Sort: COUNT(*) DESC45Group aggregate: count(0)35,000Filter: status = 'PAID'225,000Index scan using idx_customer_created1100,000数据向上流动
图 2 · 树形计划从缩进最深的节点开始执行，数据逐层向上流动；先找实际行数最大的那一层，它通常就是瓶颈

对应的原始输出（节选）：

text
-> Limit: 5 row(s)  (actual time=60.8..60.8 rows=5 loops=1)
    -> Sort: `COUNT(*)` DESC, limit input to 5 row(s) per chunk  (actual time=60.8..60.8 rows=5 loops=1)
        -> Stream results  (cost=11087 rows=4972) (actual time=0.911..60.4 rows=5000 loops=1)
            -> Group aggregate: count(0)  (cost=11087 rows=4972) (actual time=0.909..60 rows=5000 loops=1)
                -> Filter: (orders.`status` = 'PAID')  (cost=10088 rows=9984) (actual time=0.905..58.9 rows=25000 loops=1)
                    -> Index scan on orders using idx_customer_created  (cost=10088 rows=99841) (actual time=0.879..53.8 rows=100000 loops=1)

这是「统计已支付订单最多的 5 个客户」：

sql
SELECT customer_id, COUNT(*) FROM orders WHERE status = 'PAID'
GROUP BY customer_id ORDER BY COUNT(*) DESC LIMIT 5;

读法：

  1. 找实际行数最大的一层：最内层扫描了整棵 idx_customer_created 索引，读了 10 万行。
  2. 看过滤比例：Filter 之后只剩 2.5 万行，四分之三的读取被浪费了。
  3. 看时间落在哪：actual time=a..b 中，a 是返回第一行的时间，b 是返回全部行的时间，单位毫秒。总耗时 60.8ms，其中 53.8ms 在最内层扫描。

加一个以 status 开头的联合索引后：

sql
ALTER TABLE orders ADD KEY idx_status_customer (status, customer_id);
text
-> Covering index lookup on orders using idx_status_customer (status='PAID')
     (cost=5172 rows=47252) (actual time=0.153..3.35 rows=25000 loops=1)

最内层变成只读 2.5 万行的覆盖索引查找，7 次采样的中位耗时从 60.4ms 降到 5.23ms。

也要注意 EXPLAIN ANALYZE 会真正执行语句：SELECT 会完整跑一遍，UPDATE、DELETE 会真的修改数据。对写语句和很慢的查询，应在隔离的测试环境或 replica 上使用。

五、四种常见的索引失效 ​

5.1 在索引列上套函数 ​

sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';
-- Table scan on orders (actual rows=100000)，即使 created_at 上有单列索引

索引里存的是 created_at 的原值，DATE(created_at) 的结果不在索引中。改写成范围条件：

sql
SELECT * FROM orders WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';

这里有一个实测中容易忽略的细节：只改写条件还不够。在只有 idx_customer_created 的情况下，改写后仍然是全表扫描，因为这个索引的第一列是 customer_id。补上 created_at 的单列索引后，才变成：

text
-> Index range scan on orders using idx_created over ('2026-06-01 00:00:00' <= created_at < '2026-06-02 00:00:00')
     (cost=187 rows=415) (actual time=0.0924..0.391 rows=415 loops=1)

MySQL 8.0.13 起也支持函数索引（如 KEY ((DATE(created_at)))），但改写成范围条件通常更通用。

5.2 字符串列与数字比较 ​

sql
EXPLAIN SELECT * FROM orders WHERE phone = 13800000042;
-- type=ALL  possible_keys=idx_phone  key=NULL  rows=99841

EXPLAIN SELECT * FROM orders WHERE phone = '13800000042';
-- type=ref  key=idx_phone  key_len=82  rows=1

phone 是 VARCHAR，与数字比较时，MySQL 会把每一行的字符串转换成数字再比较，索引就用不上了。possible_keys 里有 idx_phone、key 却是 NULL，就是这种问题的典型信号。在 Java 里，这通常是 MyBatis 参数类型写成了 Long。

5.3 联合索引缺少最左列 ​

见第二节：WHERE created_at >= ... 用不上 (customer_id, created_at)。联合索引按第一列排序，第一列相同时才按第二列排序，跳过第一列就没有顺序可用。

5.4 统计信息不准导致选错计划 ​

实测中有两处估算和实际差距明显：

查询估算行数实际行数
created_at 一天范围，无合适索引时的过滤结果约 1.1 万（多次运行在 10,816—11,100 之间）415
status = 'PAID' 覆盖索引查找47,25225,000

估算来自统计信息的抽样，同一份数据重新 ANALYZE TABLE 后也会变化。估算偏差大时，优化器可能选错索引或连接顺序。可以先执行 ANALYZE TABLE 更新统计信息；对数据分布很不均匀的列，MySQL 8.0 起还可以用直方图（ANALYZE TABLE ... UPDATE HISTOGRAM ON col）。不建议一上来就用 FORCE INDEX，数据分布变化后它会成为新的隐患。

六、排查慢查询的顺序 ​

  1. 拿到真实 SQL 和参数：从慢查询日志或 APM 中取，不要凭代码猜，参数类型不同计划可能完全不同。
  2. EXPLAIN 看方向：type、key、key_len、rows、Extra。
  3. EXPLAIN ANALYZE 看事实（隔离的测试环境或 replica）：实际行数最大、耗时最长的那一层是哪一层。
  4. 按原因处理：改写条件、补联合索引、改成覆盖索引、更新统计信息。
  5. 回归验证：同样的参数再跑一次 EXPLAIN ANALYZE，对比实际行数和耗时。

七、常见误区 ​

  • 「type 不是 ALL 就没问题」：index 也是扫描整棵索引；ref 在数据倾斜时也可能读几十万行。
  • 「rows 就是实际读取行数」：它是估算，偏差可能达到几十倍，要用 EXPLAIN ANALYZE 确认。
  • 「possible_keys 里有索引就会用」：它只是候选，key 才是实际选择。
  • 「Using filesort 表示用了磁盘文件」：它表示需要额外排序，数据量小时在内存中完成。
  • 「加了索引就能加速」：条件写法不对、最左列不匹配时，索引根本不会被使用。

小结 ​

读执行计划的顺序是：type 和 rows 判断读了多少，key_len 判断联合索引用了几列，Extra 判断有没有额外的排序和临时表，最后用 EXPLAIN ANALYZE 找到实际行数最大的那一层。大多数慢查询的原因都很朴素：条件写法让索引失效，或者索引的列顺序不匹配查询。

表结构与索引设计的整体思路，见 数据库设计与 SQL 调优。

配套实验

参考资料

文章以 CC BY-NC-SA 4.0 授权 · 代码片段以 MIT 授权

© 2025-present Elijah Du