EXPLAIN-从入门到精通-数据库优化必备神器

EXPLAIN：从入门到精通，数据库优化必备神器

你有没有遇到过这种情况——一条 SQL 在本地跑得飞快，一上线就卡成 PPT？
今天咱们就来聊聊数据库优化的"照妖镜"：EXPLAIN。

一、问题引入：那条让我加班到凌晨的 SQL

上周有个线上告警，一个查询接口响应时间从 50ms 暴涨到 15 秒。

我第一反应是：是不是数据量暴增了？查了一下，也就从 10 万涨到了 12 万，不至于啊。

第二反应：是不是锁竞争？看了监控，也没啥锁等待。

最后祭出大招——EXPLAIN一看，好家伙，全表扫描！原来新来的实习生把索引字段的类型改了，导致索引失效。

说白了，EXPLAIN 就是数据库的"CT 机"，能让你一眼看穿 SQL 的执行真相。

二、EXPLAIN 到底是什么？

2.1 一句话定义

EXPLAIN 是数据库提供的执行计划分析工具，它会告诉你：数据库打算怎么执行你的 SQL，是走索引还是全表扫描，先查哪张表，怎么 join，预估要扫描多少行…

2.2 为什么要用它？

咱们写 SQL 的时候，心里想的是"我要什么数据"，但数据库想的是"我怎么高效地拿到这些数据"。

这中间有个鸿沟——你以为的"高效"和数据库实际的"执行"可能差十万八千里。

EXPLAIN 就是帮你填这个沟的。

三、EXPLAIN 怎么用？（以 MySQL 为例）

3.1 基础用法

-- 直接在 SQL 前面加个 EXPLAINEXPLAINSELECT*FROMusersWHEREage>18;

输出大概长这样：

关键字段解读：

3.2 进阶用法

-- 看更详细的执行计划（MySQL 5.6+）EXPLAINFORMAT=JSONSELECT*FROMusersWHEREage>18;-- 看实际执行情况（MySQL 5.6+）EXPLAINANALYZESELECT*FROMusersWHEREage>18;-- 看执行计划 + 实际执行统计（MySQL 8.0.18+）EXPLAINANALYZESELECT*FROMusersWHEREage>18;

3.3 一个实际案例

假设咱们有个订单表：

CREATETABLEorders(idBIGINTPRIMARYKEY,user_idBIGINTNOTNULL,statusVARCHAR(20)NOTNULL,created_atDATETIMENOTNULL,INDEXidx_user(user_id),INDEXidx_status(status));

来条 SQL：

EXPLAINSELECT*FROMordersWHEREuser_id=10086ANDstatus='PAID'ORDERBYcreated_atDESCLIMIT10;

不好的执行计划长这样：

type: ref key: idx_user rows: 5000 Extra: Using where; Using filesort

问题在哪？

• 用了idx_user索引，但还要回表 5000 行
• Using filesort：内存排序，数据量大就惨了

优化方案：建个联合索引

ALTERTABLEordersADDINDEXidx_user_status_created(user_id,status,created_at);

优化后的执行计划：

type: ref key: idx_user_status_created rows: 50 Extra: Using index -- 覆盖索引，不用回表了！

从 5000 行扫描降到 50 行，还没有 filesort，这就是 EXPLAIN 的价值。

四、主流数据库 EXPLAIN 对比

4.1 MySQL vs PostgreSQL 详细对比

MySQL 的 EXPLAIN：

EXPLAINSELECT*FROMusersWHEREemail='test@example.com';-- 输出：-- +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |-- +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+-- | 1 | SIMPLE | users | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |-- +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

特点：

• 表格形式，直观好读
• type字段是核心判断依据
• Extra里的Using filesort、Using temporary是性能杀手

PostgreSQL 的 EXPLAIN：

EXPLAINANALYZESELECT*FROMusersWHEREemail='test@example.com';-- 输出：-- Index Scan using idx_users_email on users (cost=0.29..8.30 rows=1 width=72) (actual time=0.023..0.024 rows=1 loops=1)-- Index Cond: (email = 'test@example.com'::text)-- Planning Time: 0.123 ms-- Execution Time: 0.045 ms

特点：

• 树形结构，更贴近实际执行流程
• cost=0.29..8.30：启动成本…总成本
• actual time：真实执行时间（EXPLAIN ANALYZE 才有）
• 能看到每个节点的实际耗时，定位瓶颈更精准

4.2 Oracle 的 EXPLAIN PLAN

-- 先生成执行计划EXPLAINPLANFORSELECT*FROMusersWHEREemail='test@example.com';-- 再查看SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);-- 输出：-- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-- |-----|-------------------|---------------|-------|-------|------------|----------|-- | 0 | SELECT STATEMENT | | 1 | 72 | 2 (0)| 00:00:01 |-- | 1 | INDEX UNIQUE SCAN| PK_USERS_EMAIL| 1 | 72 | 2 (0)| 00:00:01 |

特点：

• 成本是绝对值（基于 I/O、CPU、内存的综合估算）
• Cost (%CPU)能看出 CPU 占比
• 企业级功能最全，但也是最复杂的

4.3 SQL Server 的 Execution Plan

-- 开启实际执行计划（SSMS 里按 Ctrl+M 也行）SETSTATISTICSPROFILEON;GOSELECT*FROMusersWHEREemail='test@example.com';GOSETSTATISTICSPROFILEOFF;

特点：

• SSMS 的图形化执行计划是业界最好用的
• 鼠标悬停能看到每个操作的详细开销
• 能直接对比两个执行计划的差异

五、EXPLAIN 核心判断法则

5.1 type 字段优先级（MySQL）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 目标：至少达到 range，最好是 ref 以上

常见 type 解读：

5.2 Extra 里的危险信号

-- 🚨 Using filesort：内存排序，大数据量会爆炸EXPLAINSELECT*FROMusersORDERBYname;-- 没索引，得排序-- 🚨 Using temporary：用了临时表，通常发生在 GROUP BY / DISTINCTEXPLAINSELECTstatus,COUNT(*)FROMusersGROUPBYstatus;-- 🚨 Using where; Using join buffer：join 没走索引，用了缓存区EXPLAINSELECT*FROMusers uJOINorders oONu.name=o.user_name;

5.3 一个快速诊断 checklist

□ type 是不是 ALL？是 → 考虑加索引 □ key 是不是 NULL？是 → 索引没生效，检查条件 □ rows 是不是上万？是 → 扫描量太大，优化条件或索引 □ Extra 有没有 filesort？有 → 优化 ORDER BY □ Extra 有没有 temporary？有 → 优化 GROUP BY / DISTINCT

六、常见坑与误区

坑 1：EXPLAIN 的 rows 是估算，不是实际

-- MySQL 用索引统计信息估算，可能不准-- 特别是数据分布不均匀的时候ANALYZETABLEusers;-- 更新统计信息后再看

坑 2：EXPLAIN 和实际执行可能不一样

-- MySQL 5.7 之前，EXPLAIN 不走实际执行-- 有些优化（如条件下推）可能不会显示-- 建议用 EXPLAIN ANALYZE（MySQL 8.0.18+）

坑 3：LIMIT 会影响执行计划

-- 有 LIMIT 和没 LIMIT，执行计划可能完全不同EXPLAINSELECT*FROMusersORDERBYcreated_at;-- 可能 filesortEXPLAINSELECT*FROMusersORDERBYcreated_atLIMIT10;-- 可能走索引

坑 4：参数化查询的坑

-- 预编译语句可能用错误的执行计划-- 因为第一次执行的参数决定了计划-- 可以用 FORCE INDEX 或优化器提示

七、实战技巧总结

技巧 1：快速定位慢查询

-- 开启慢查询日志SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;-- 超过 1 秒就记录-- 然后用 EXPLAIN 分析这些 SQL

技巧 2：对比优化前后

-- 优化前保存执行计划EXPLAINSELECT...\G-- \G 竖排显示，好复制-- 优化后对比关键指标：type、key、rows、Extra

技巧 3：用 optimizer_trace 看详细决策过程

-- MySQL 深度调试SETSESSIONoptimizer_trace="enabled=on";SELECT*FROMinformation_schema.OPTIMIZER_TRACE;

技巧 4：PostgreSQL 的可视化神器

-- 生成 JSON 后，可以用 https://explain.dalibo.com/ 可视化EXPLAIN(FORMAT JSON,ANALYZE,BUFFERS)SELECT*FROMusers;

八、总结

EXPLAIN 不是什么高深莫测的东西，它就是数据库给你的"执行说明书"。

记住这几个核心要点：

1. type看访问方式—— ALL 是红灯，ref 是绿灯
2. key看用没用索引—— NULL 就是没用，要排查
3. rows看扫描量—— 越小越好，上万要警惕
4. Extra看额外操作—— filesort、temporary 是性能杀手

不同数据库的 EXPLAIN 大同小异：

• MySQL：表格直观，重点关注 type 和 Extra
• PostgreSQL：树形结构，cost 和 actual time 很有用
• Oracle：成本绝对值最准，企业级功能最全
• SQL Server：图形化最友好，SSMS 无敌

写在最后

说实话，我刚工作那会儿也觉得 EXPLAIN 输出密密麻麻的，看着头大。

但踩了几次坑之后发现——这玩意儿就是数据库优化的导航仪，不会用的话，优化 SQL 就像闭着眼睛开车。

现在我已经养成习惯了：任何线上 SQL 改动，必须先 EXPLAIN 确认执行计划没问题。

你在实际项目中有没有被执行计划坑过？或者有什么 EXPLAIN 的高级玩法？欢迎在评论区交流！

本文基于 MySQL 8.0、PostgreSQL 15、Oracle 19c、SQL Server 2019 整理，不同版本可能存在差异。