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*FROMusersWHEREage18;输出大概长这样idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEusersALLNULLNULLNULLNULL100000Using where关键字段解读字段什么意思重点关注type访问类型最重要从优到劣system const eq_ref ref range index ALLkey实际使用的索引NULL 表示没用索引要警惕rows预估扫描行数越小越好上万就要注意了Extra额外信息Using filesort、Using temporary 都是危险信号possible_keys可能用到的索引看看有没有合适的索引被忽略了3.2 进阶用法-- 看更详细的执行计划MySQL 5.6EXPLAINFORMATJSONSELECT*FROMusersWHEREage18;-- 看实际执行情况MySQL 5.6EXPLAINANALYZESELECT*FROMusersWHEREage18;-- 看执行计划 实际执行统计MySQL 8.0.18EXPLAINANALYZESELECT*FROMusersWHEREage18;3.3 一个实际案例假设咱们有个订单表CREATETABLEorders(idBIGINTPRIMARYKEY,user_idBIGINTNOTNULL,statusVARCHAR(20)NOTNULL,created_atDATETIMENOTNULL,INDEXidx_user(user_id),INDEXidx_status(status));来条 SQLEXPLAINSELECT*FROMordersWHEREuser_id10086ANDstatusPAIDORDERBYcreated_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 对比特性MySQLPostgreSQLOracleSQL Server基本语法EXPLAIN SQLEXPLAIN SQLEXPLAIN PLAN FORSET SHOWPLAN_ALL ON看实际执行EXPLAIN ANALYZE(8.0.18)EXPLAIN ANALYZEDBMS_XPLAN.DISPLAYSET STATISTICS PROFILE ON输出格式表格 / JSON文本 / JSON / XML表格 / XML表格 / XML / JSON成本估算有相对值有相对值有绝对值最准有相对值索引提示USE INDEX/FORCE INDEX不支持强制索引/* INDEX() */WITH (INDEX())可视化工具Workbench、NavicatpgAdminSQL DeveloperSSMS4.1 MySQL vs PostgreSQL 详细对比MySQL 的 EXPLAINEXPLAINSELECT*FROMusersWHEREemailtestexample.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 的 EXPLAINEXPLAINANALYZESELECT*FROMusersWHEREemailtestexample.com;-- 输出-- Index Scan using idx_users_email on users (cost0.29..8.30 rows1 width72) (actual time0.023..0.024 rows1 loops1)-- Index Cond: (email testexample.com::text)-- Planning Time: 0.123 ms-- Execution Time: 0.045 ms特点树形结构更贴近实际执行流程cost0.29..8.30启动成本…总成本actual time真实执行时间EXPLAIN ANALYZE 才有能看到每个节点的实际耗时定位瓶颈更精准4.2 Oracle 的 EXPLAIN PLAN-- 先生成执行计划EXPLAINPLANFORSELECT*FROMusersWHEREemailtestexample.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 里按 CtrlM 也行SETSTATISTICSPROFILEON;GOSELECT*FROMusersWHEREemailtestexample.com;GOSETSTATISTICSPROFILEOFF;特点SSMS 的图形化执行计划是业界最好用的鼠标悬停能看到每个操作的详细开销能直接对比两个执行计划的差异五、EXPLAIN 核心判断法则5.1 type 字段优先级MySQLsystem const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL 目标至少达到 range最好是 ref 以上常见 type 解读type含义好坏const主键/唯一索引等值查询 最佳eq_refjoin 时主键/唯一索引匹配 很好ref普通索引等值查询 不错range索引范围查询 可接受index全索引扫描 警惕ALL全表扫描 危险5.2 Extra 里的危险信号-- Using filesort内存排序大数据量会爆炸EXPLAINSELECT*FROMusersORDERBYname;-- 没索引得排序-- Using temporary用了临时表通常发生在 GROUP BY / DISTINCTEXPLAINSELECTstatus,COUNT(*)FROMusersGROUPBYstatus;-- Using where; Using join bufferjoin 没走索引用了缓存区EXPLAINSELECT*FROMusers uJOINorders oONu.nameo.user_name;5.3 一个快速诊断 checklist□ type 是不是 ALL是 → 考虑加索引 □ key 是不是 NULL是 → 索引没生效检查条件 □ rows 是不是上万是 → 扫描量太大优化条件或索引 □ Extra 有没有 filesort有 → 优化 ORDER BY □ Extra 有没有 temporary有 → 优化 GROUP BY / DISTINCT六、常见坑与误区坑 1EXPLAIN 的 rows 是估算不是实际-- MySQL 用索引统计信息估算可能不准-- 特别是数据分布不均匀的时候ANALYZETABLEusers;-- 更新统计信息后再看坑 2EXPLAIN 和实际执行可能不一样-- MySQL 5.7 之前EXPLAIN 不走实际执行-- 有些优化如条件下推可能不会显示-- 建议用 EXPLAIN ANALYZEMySQL 8.0.18坑 3LIMIT 会影响执行计划-- 有 LIMIT 和没 LIMIT执行计划可能完全不同EXPLAINSELECT*FROMusersORDERBYcreated_at;-- 可能 filesortEXPLAINSELECT*FROMusersORDERBYcreated_atLIMIT10;-- 可能走索引坑 4参数化查询的坑-- 预编译语句可能用错误的执行计划-- 因为第一次执行的参数决定了计划-- 可以用 FORCE INDEX 或优化器提示七、实战技巧总结技巧 1快速定位慢查询-- 开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过 1 秒就记录-- 然后用 EXPLAIN 分析这些 SQL技巧 2对比优化前后-- 优化前保存执行计划EXPLAINSELECT...\G-- \G 竖排显示好复制-- 优化后对比关键指标type、key、rows、Extra技巧 3用 optimizer_trace 看详细决策过程-- MySQL 深度调试SETSESSIONoptimizer_traceenabledon;SELECT*FROMinformation_schema.OPTIMIZER_TRACE;技巧 4PostgreSQL 的可视化神器-- 生成 JSON 后可以用 https://explain.dalibo.com/ 可视化EXPLAIN(FORMAT JSON,ANALYZE,BUFFERS)SELECT*FROMusers;八、总结EXPLAIN 不是什么高深莫测的东西它就是数据库给你的执行说明书。记住这几个核心要点type看访问方式—— ALL 是红灯ref 是绿灯key看用没用索引—— NULL 就是没用要排查rows看扫描量—— 越小越好上万要警惕Extra看额外操作—— filesort、temporary 是性能杀手不同数据库的 EXPLAIN 大同小异MySQL表格直观重点关注 type 和 ExtraPostgreSQL树形结构cost 和 actual time 很有用Oracle成本绝对值最准企业级功能最全SQL Server图形化最友好SSMS 无敌写在最后说实话我刚工作那会儿也觉得 EXPLAIN 输出密密麻麻的看着头大。但踩了几次坑之后发现——这玩意儿就是数据库优化的导航仪不会用的话优化 SQL 就像闭着眼睛开车。现在我已经养成习惯了任何线上 SQL 改动必须先 EXPLAIN 确认执行计划没问题。你在实际项目中有没有被执行计划坑过或者有什么 EXPLAIN 的高级玩法欢迎在评论区交流本文基于 MySQL 8.0、PostgreSQL 15、Oracle 19c、SQL Server 2019 整理不同版本可能存在差异。