1. 从一次慢查询引发的“血案”说起上周我负责的一个核心业务系统突然报警一个原本运行平稳的报表查询接口响应时间从毫秒级飙升到了十几秒。DBA把慢日志甩过来我一看SQL本身并不复杂几个表关联加上一些常规的筛选条件。第一反应是索引失效了检查了一遍该有的索引都在。那是数据量突增查了查增量也在正常范围内。一时间有点抓瞎。这时候最直接、也最有效的武器就该登场了EXPLAIN。我习惯性地在DBeaver里选中那条SQL点了一下“执行计划”的按钮。结果弹出来的窗口里只有一些简单的统计信息比如“返回行数”、“成本估算”至于数据是怎么走的先扫描了哪个表用了哪种连接方式有没有用到我精心设计的索引一概没有。这感觉就像医生看病只告诉你“病人发烧了”但没告诉你到底是哪里发炎、什么病原体引起的。这个经历让我意识到很多开发者包括曾经的我对EXPLAIN的理解可能还停留在“知道有这么个东西”的层面。我们用它但未必真正“读懂”它。尤其是在不同的数据库客户端工具里EXPLAIN的输出形式五花八门有的过于简化有的又过于晦涩。今天我就结合这次排查经历和多年踩坑经验带你彻底拆解EXPLAIN执行计划。这不是一篇简单的命令手册翻译而是一个一线工程师的“读图指南”。我们会从最基础的输出字段讲起一直深入到如何根据执行计划反推优化方案甚至聊聊那些官方文档里不会写的、关于执行计划“谎言”的真相。无论你是使用MySQL、PostgreSQL还是其他主流关系型数据库EXPLAIN的核心思想是相通的。掌握它你就拥有了直接与数据库优化器对话的能力慢查询排查的效率会提升一个数量级。2. EXPLAIN 输出读懂优化器的“作战地图”当你对一条SQL语句执行EXPLAIN在MySQL中是EXPLAIN [SQL]在PostgreSQL中是EXPLAIN (ANALYZE, BUFFERS) [SQL]后者能提供更详细的运行时信息数据库优化器并不会真正执行这条语句而是基于当前的统计信息如表大小、索引分布、数据直方图模拟出一条它认为最优的执行路径并将这条路径以表格或树形结构展示出来。这份输出就是我们的“作战地图”。以MySQL的EXPLAIN为例其输出通常包含以下几个关键列每一列都揭示了执行计划的一个侧面id: 执行计划的序列号。它表示查询中SELECT语句的执行顺序。id相同执行顺序从上到下id不同如果是子查询id值会递增id值越大优先级越高越先执行。但要注意在包含UNION或复杂子查询时id可能为NULL这通常代表一个结果集合并的步骤。select_type: 查询的类型。这是理解查询复杂度的第一扇窗。SIMPLE: 简单的SELECT查询不包含子查询或UNION。PRIMARY: 查询中最外层的SELECT或者包含子查询时的主查询。SUBQUERY: 在SELECT或WHERE列表中包含的子查询。DERIVED: 在FROM子句中包含的子查询派生表MySQL会为它创建一张临时表。UNION: UNION操作中第二个及以后的SELECT。UNION RESULT: UNION操作的结果。看到DERIVED派生表和复杂的SUBQUERY时就要警惕了它们往往意味着额外的临时表创建和数据处理是性能的潜在瓶颈。table: 显示这一行数据是关于哪张表的。有时这里会出现表示这是一个临时表由id为N的查询结果派生而来。partitions: 匹配的分区信息。如果你的表做了分区这里会显示查询命中了哪些分区。type:这是判断查询效率的黄金指标它描述了表是如何被连接的从最优到最差大致是systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL。 我们重点关注最常见的几种const/system: 通过主键或唯一索引进行等值查询最多返回一行。性能极致如SELECT * FROM user WHERE id 1。eq_ref: 在连接查询中使用主键或唯一索引进行关联。对于前一个表的每一行当前表都只返回一行。这是性能最好的连接类型。ref: 使用非唯一索引进行等值扫描可能返回多行。例如WHERE name ‘张三‘如果name上有索引。range: 利用索引进行范围扫描如BETWEEN、IN()、、等操作。index:全索引扫描。它遍历了整个索引树但比全表扫描ALL要快因为索引文件通常比数据文件小。常见于覆盖索引的查询Extra列会出现Using index。ALL:全表扫描。这是最糟糕的情况意味着数据库需要逐行检查整张表。对于大表这通常是性能灾难的信号。possible_keys: 查询可能使用到的索引。优化器基于WHERE子句和连接条件识别出的候选索引。如果这一列为NULL不一定没索引可用也可能是因为查询条件无法有效利用索引。key: 查询实际决定使用的索引。如果为NULL则表示未使用索引。这里有一个关键点优化器选择key时是基于其内部成本模型估算的。它认为使用某个索引的“成本”最低但不一定在真实运行时就是最快的。这就是为什么有时我们“觉得”应该走A索引但执行计划却走了B索引的原因。key_len: 使用的索引的长度字节数。这个值可以帮助你判断索引是否被完全利用。例如你有一个联合索引(col1, col2, col3)如果查询只用到col1和col2进行等值匹配那么key_len就是这两列的长度之和。如果key_len小于索引总长说明索引未被最左前缀完全覆盖。ref: 显示索引的哪一列被用于查找。可能是常量const也可能是另一张表的列名。rows:这是一个估算值表示优化器认为执行该步骤需要扫描的行数。这是一个非常重要的参考指标但它基于统计信息可能不准确。一个rows值很大的步骤很可能就是性能瓶颈所在。filtered: 这是一个百分比表示存储引擎层返回的数据在经过服务器层WHERE条件过滤后剩余行数的百分比。rows * filtered / 100可以粗略估算出将与下一张表进行连接的行数。值越小说明服务器层过滤掉的无效数据越多可能意味着索引筛选性不够好。Extra: 包含额外的执行信息这里常常藏着“魔鬼”或“天使”。Using index: 表示使用了覆盖索引所有需要的数据都能从索引中取得无需回表查询数据行。这是性能优化的理想状态之一。Using where: 表示服务器层在存储引擎返回行之后又进行了额外的过滤。这说明索引可能没有完全覆盖查询条件。Using temporary: 表示为了执行查询需要创建临时表来保存中间结果。这通常发生在GROUP BY、DISTINCT、UNION等操作时对性能影响较大。Using filesort: 表示无法利用索引完成排序需要在内存或磁盘上进行额外的排序操作。对于大数据集这非常消耗资源。Using join buffer: 表示连接查询时无法基于索引高效完成需要用到连接缓冲区。常见于关联字段没有索引的情况。注意EXPLAIN的输出是基于统计信息的“估算”。rows和filtered列的值是估算值可能与实际运行时扫描的行数有较大出入。当表数据发生重大变化如大量增删后统计信息如果没有及时更新优化器就可能制定出错误的“作战计划”。这时你需要使用ANALYZE TABLEMySQL或ANALYZEPostgreSQL来更新统计信息。3. 实战演练逐行解剖一个复杂查询的执行计划光说不练假把式。我们来看一个相对复杂的多表关联查询并逐行解读其EXPLAIN输出。假设我们有一个简单的电商数据库-- 查询用户“张三”在2023年购买的所有商品名称和订单金额 EXPLAIN SELECT u.name, o.order_amount, p.product_name FROM users u JOIN orders o ON u.id o.user_id JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE u.name ‘张三‘ AND o.create_time ‘2023-01-01‘ ORDER BY o.create_time DESC;假设我们已有索引users(name),orders(user_id, create_time),order_items(order_id),products(id)。一个可能的MySQLEXPLAIN输出如下为便于说明简化了部分列idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEurefnamename102const1100.00Using where; Using index1SIMPLEorefuser_id,idx_u_ctidx_u_ct8db.u.id,const10100.00Using index condition1SIMPLEoireforder_idorder_id4db.o.id5100.00NULL1SIMPLEpeq_refPRIMARYPRIMARY4db.oi.product_id1100.00NULL现在我们来扮演侦探一行行分析这份“地图”第一行 (id1, table‘u‘)select_type: SIMPLE这是一个简单的驱动查询。type: ref说明通过name索引进行等值查找。因为name可能不唯一所以是ref而非const。key: name实际使用了我们为users.name字段建立的索引。rows: 1优化器估算通过名字“张三”只能找到1条记录假设用户名唯一。这是一个非常好的起点。Extra: Using where; Using indexUsing index是亮点说明查询u.name这个字段时索引已经覆盖无需回表。Using where可能只是形式上的因为条件u.name ‘张三‘在索引查找时已经应用。第二行 (id1, table‘o‘)注意id也是1说明它与u表是同一执行层级属于连接操作。type: ref这里ref列显示为db.u.id,const意味着它使用了一个索引该索引的第一列是user_id等于u.id并且可能还有第二列是create_time与常量‘2023-01-01‘比较。这对应了我们假设的联合索引orders(user_id, create_time)。key: idx_u_ct证实了它使用了这个联合索引。key_len: 8我们需要知道user_id和create_time的字段类型来精确计算但这里表明索引的两列都被用到了。rows: 10优化器估算对于找到的这1个用户在2023年之后他大概有10个订单。Extra: Using index condition这是一个重要的优化称为“索引条件下推”。意思是对于o.create_time ‘2023-01-01‘这个条件MySQL在存储引擎层使用索引进行过滤而不是将所有user_id匹配的订单行都取出来再到服务器层过滤。这大大减少了需要向上传递的数据量。第三行 (id1, table‘oi‘)type: ref通过order_id索引查找订单项。ref: db.o.id使用上一行结果订单o的id来查找。rows: 5估算每个订单平均有5个商品项。第四行 (id1, table‘p‘)type: eq_ref这是最好的连接类型之一。通过products表的主键id进行连接对于每一个order_items中的product_idproducts表都只返回唯一的一行。key: PRIMARY使用了主键。rows: 1每次连接精确返回一行。整体执行顺序分析 根据id相同和table列的顺序我们可以推断出大致的执行流程数据库首先从users表别名u出发利用name索引快速定位到用户“张三”这一行。然后拿着找到的用户id去orders表别名o利用(user_id, create_time)联合索引高效地找出该用户2023年之后的所有订单。这里用到了“索引条件下推”优化。接着对于找到的每一个订单去order_items表别名oi利用order_id索引找到该订单下的所有商品项。最后对于每一个商品项通过products表别名p的主键获取商品的详细信息。这个执行计划看起来相当高效驱动表users筛选性极好rows1后续的关联都通过索引完成类型多为ref或eq_ref没有出现全表扫描ALL或文件排序Using filesort因为ORDER BY o.create_time可能已经在orders表的索引中按顺序获取了。这是一个健康的执行计划。4. 当执行计划“说谎”统计信息失真与优化器局限然而现实往往比理想骨感。我们之所以要深入理解EXPLAIN正是因为这份“地图”有时会误导我们。它基于的统计信息可能过时优化器的成本模型也可能做出不符合我们直觉的选择。我遇到最多的“坑”就来源于此。场景一rows估算严重失准还记得我开头提到的那个慢查询吗它的执行计划里某一步的rows估算值是500但实际运行时该步骤扫描了超过50万行。巨大的差异直接导致了性能雪崩。原因是什么那张表在前一晚有一个定时的批量数据归档任务删除了大量历史数据随后又插入了新的批次数据。表的体积变化很大但自动更新的统计信息没有及时触发或者采样率不够高导致优化器仍然认为数据分布是“老样子”。实操心得对于数据变化频繁的表特别是日增删量大的业务表不要完全依赖数据库的自动统计信息更新。在核心业务低峰期比如凌晨对关键大表定期执行手动更新统计信息的命令ANALYZE TABLE table_name;in MySQL。这是保持执行计划可靠性的基础维护操作。场景二优化器“弃用”了你认为最好的索引你为WHERE status 1 AND category_id 5 AND create_time ‘xxx‘精心设计了一个联合索引(category_id, status, create_time)。但EXPLAIN显示它竟然选择了另一个单列索引(category_id)甚至进行了全表扫描为什么数据倾斜如果category_id5的数据占了全表的90%那么使用(category_id)索引筛选后还是要回表访问绝大部分数据行。优化器可能认为与其回表这么多次不如直接全表扫描ALL更省成本。成本模型偏差优化器的成本计算包括IO成本和CPU成本。它认为走你设计的联合索引虽然筛选更精确rows估算小但可能需要回表如果索引不是覆盖索引而回表的随机IO成本很高。相比之下它可能觉得全表扫描的顺序IO“更划算”——尽管这通常不符合我们的直觉。场景三Using filesort和Using temporary的隐形消耗EXPLAIN告诉你有个Using filesort。对于小数据量内存排序很快你可能感觉不到。但当ORDER BY或GROUP BY的数据集无法被索引满足且数据量很大时这个操作可能需要在磁盘上创建临时文件进行排序速度会急剧下降。Using temporary创建临时表更是如此尤其是当临时表无法放在内存中而必须使用磁盘时。避坑指南看到Using filesort和Using temporary要高度警觉。尝试通过调整索引为ORDER BY/GROUP BY的字段建立索引并注意列顺序或重写查询例如通过子查询先限制数据量再排序来消除它们。在MySQL中可以尝试调整sort_buffer_size和tmp_table_size参数但这只是缓解治本之策还是优化查询和索引。面对优化器的“错误”选择我们并非无能为力。这就是下一章要讨论的“干预”手段。5. 高级技巧如何干预与优化执行计划当我们确信优化器选错了路或者执行计划存在已知的瓶颈时就需要进行人工干预。干预的核心思路是为优化器提供更准确的信息或强制它选择我们指定的路径。5.1 使用索引提示Index Hints这是最直接的干预方式。在SQL语句中明确告诉数据库使用哪个索引或者忽略哪个索引。MySQL:SELECT * FROM table_name USE INDEX (index_name) WHERE ...; -- 建议使用 SELECT * FROM table_name FORCE INDEX (index_name) WHERE ...; -- 强制使用 SELECT * FROM table_name IGNORE INDEX (index_name) WHERE ...; -- 忽略某个索引PostgreSQL: PostgreSQL默认不支持索引提示因为它更相信自己的优化器。但在极端情况下可以通过设置会话级参数来禁用某些扫描类型变相引导如SET enable_seqscan off;来禁用全表扫描。但这非常不推荐在生产环境随意使用因为它影响全局。重要提醒索引提示是一把双刃剑。今天你强制使用了索引A可能因为数据分布变化明天索引A就成了最差选择。它使SQL语句失去了灵活性增加了维护成本。我的经验是只在经过严谨测试、确认为长期有效的优化方案并且优化器始终无法自动选择时才考虑使用索引提示并务必加上详细的注释说明原因。5.2 优化查询写法很多时候换一种写法就能得到完全不同的执行计划。避免SELECT *只查询需要的列。这不仅能减少网络传输更重要的是如果所有需要的列都包含在一个索引中覆盖索引查询就能在索引中完成避免回表性能提升巨大。我的例子中Extra: Using index就是覆盖索引的功劳。将子查询转化为连接JOIN优化器对JOIN的优化通常比对子查询更成熟。特别是关联子查询Correlated Subquery很容易导致NESTED LOOP式的低效执行。-- 可能低效的关联子查询 SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id AND o.amount 1000); -- 通常更高效的JOIN写法 SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id o.user_id WHERE o.amount 1000;谨慎使用ORWHERE a 1 OR b 2这样的条件如果a和b上都有单列索引MySQL可能无法高效地使用它们转而选择全表扫描。可以考虑用UNION改写SELECT * FROM table WHERE a 1 UNION SELECT * FROM table WHERE b 2;前提是a1和b2的结果集交集很小。5.3 利用EXPLAIN ANALYZE获取真实运行时信息PostgreSQLMySQL的EXPLAIN只是预测而PostgreSQL的EXPLAIN ANALYZE会真正执行查询并返回实际的执行时间、实际扫描的行数等。这是诊断性能问题的终极利器。EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE ...;输出中会包含actual time0.028..0.029实际执行时间启动时间..总时间。rows100实际返回的行数。loops1该节点执行的次数。Buffers: shared hit5 read10缓存命中情况可以清晰看到有多少数据是从内存读取hit多少是从磁盘读取read。通过对比EXPLAIN的估算rows和EXPLAIN ANALYZE的实际rows你可以立刻发现统计信息是否准确。通过观察actual time可以精准定位到耗时最长的执行步骤。5.4 分页查询的大坑与优化LIMIT 10000, 20这种深度分页是经典的性能杀手。EXPLAIN可能显示它用了索引但它实际上需要先扫描并丢弃前10000行成本极高。 优化思路延迟关联先通过覆盖索引在索引中完成筛选和排序拿到主键ID再回表查询具体数据。SELECT * FROM your_table t INNER JOIN ( SELECT id FROM your_table WHERE ... -- 你的条件 ORDER BY create_time DESC LIMIT 10000, 20 ) AS tmp ON t.id tmp.id;基于游标的分页Seek Method如果排序字段唯一或能保证顺序唯一记录上一页最后一条记录的值下一页查询时直接从这个值开始。-- 假设按唯一ID分页上一页最后一条ID是 10000 SELECT * FROM your_table WHERE id 10000 ORDER BY id LIMIT 20;6. 工具与可视化让执行计划一目了然命令行看EXPLAIN的输出对于复杂查询来说不够直观。幸好我们有强大的可视化工具。6.1 MySQL Workbench / DBeaver 的可视化解释像MySQL Workbench和DBeaver这样的数据库客户端都提供了将EXPLAIN结果可视化的功能。它们通常将执行计划渲染成树形图或流程图节点大小代表成本rows连线代表数据流方向。一眼就能看出哪个步骤最“胖”成本最高整个查询的瓶颈在哪里。这对于向非技术同事解释性能问题或者快速进行团队内部分享非常有帮助。6.2pt-visual-explain(Percona Toolkit)这是命令行爱好者的福音。pt-visual-explain工具可以将文本格式的EXPLAIN输出转换成ASCII艺术风格的树状图直接在终端里展示清晰的执行路径比看表格直观得多。6.3 性能模式Performance Schema与慢查询日志Slow Query LogEXPLAIN是静态分析而性能模式MySQL或pg_stat_statementsPostgreSQL提供了动态的运行时洞察。你可以找到消耗总时间最多的SQL语句然后再针对性地对其做EXPLAIN分析。慢查询日志则记录了所有超过阈值的查询及其执行时间是发现潜在性能问题的第一现场。结合使用静态的EXPLAIN和动态的运行时监控你就能构建起从发现问题慢查询日志到定位原因EXPLAIN分析瓶颈再到验证优化效果对比优化前后EXPLAIN ANALYZE结果的完整性能优化闭环。回到我开头遇到的那个问题最后是怎么解决的呢我并没有在DBeaver那个简化的执行计划视图里纠结。而是直接连接到数据库服务器在命令行里执行了EXPLAIN FORMATJSON拿到了更详细的JSON格式执行计划。然后我用pt-visual-explain工具将其可视化清晰地看到瓶颈是一个错误的NESTED LOOP连接其中一张小表被多次全表扫描。根据这个线索我检查了相关表的统计信息发现果然已经很久没更新。执行ANALYZE TABLE后优化器生成了正确的执行计划查询瞬间恢复正常。所以别再只把EXPLAIN当作一个简单的命令。把它当作你和数据库优化器沟通的桥梁一份需要仔细研读的“作战地图”。读懂它你就能在复杂的系统里精准地找到那条通往高性能的路径。