1. 从一次线上慢查询说起为什么我们需要Explain那天下午监控系统突然告警一个核心业务接口的响应时间从平时的几十毫秒飙升到了十几秒。整个团队立刻紧张起来数据库的CPU使用率也冲到了90%以上。登录到数据库服务器用SHOW PROCESSLIST一看果然有几个状态是“Sending data”的查询已经执行了很长时间。问题大概率出在SQL上。我们立刻找到了那条“罪魁祸首”的SQL是一个多表关联查询看起来逻辑并不复杂。但为什么平时跑得好好的今天突然就“挂”了呢是数据量变大了还是索引失效了抑或是MySQL的优化器今天“心情不好”选错了执行路径在这种时候靠猜是没用的。你需要一个“透视镜”能够深入数据库引擎内部看清楚这条SQL到底打算怎么执行每一步的成本是多少瓶颈在哪里。这个“透视镜”就是EXPLAIN命令。它不是去实际执行你的SQLEXPLAIN ANALYZE除外而是让数据库的查询优化器告诉你“如果我来执行这条语句我打算这么做。” 通过解读这份“作战计划”我们就能精准定位问题是索引没命中、是全表扫描、还是关联顺序出了问题。对于后端开发、DBA甚至是对性能有要求的数据分析师来说EXPLAIN都是必须掌握的技能。它不局限于MySQL在 PostgreSQL、SQL Server称为执行计划、Oracle 等主流数据库中都有类似功能只是输出格式和细节略有不同。理解了一份执行计划就等于拿到了数据库性能调优的“地图”。2. 解读执行计划输出读懂每一列的含义当我们执行EXPLAIN SELECT * FROM users WHERE age 30;后会得到一张表格。这张表的每一列都承载着关键信息。我们以MySQL 8.0为例逐列拆解其含义。这是读懂计划的基础务必吃透。2.1 核心列id、select_type、tableid(查询序列号)这一列是一个编号表示SELECT语句的执行顺序。规则很简单id相同执行顺序从上到下。通常出现在包含子查询或UNION的语句中表示这些部分是同一层级按出现的顺序执行。id不同如果是子查询id序号会递增。id值越大优先级越高越先被执行。这很好理解内层子查询需要先算出结果才能供外层查询使用。id为NULL最后执行。通常表示这是一个由优化器生成的临时结果集例如UNION操作的去重步骤。select_type(查询类型)这列说明了查询的类型是简单查询还是复杂的子查询。常见的有SIMPLE最简单的SELECT不包含子查询或UNION。这是我们最希望看到的。PRIMARY查询中若包含任何复杂的子部分最外层的SELECT被标记为PRIMARY。SUBQUERY在SELECT或WHERE列表中包含了子查询该子查询被标记为SUBQUERY。DERIVED在FROM列表中包含的子查询即派生表MySQL会递归执行并将结果放入一个临时表中。DERIVED表无法建立索引如果数据量大往往是性能瓶颈。UNIONUNION中的第二个或后面的SELECT语句。UNION RESULT从UNION的匿名临时表检索结果的SELECT。注意在MySQL 5.7及之前DERIVED查询会物化到磁盘临时表性能损耗大。MySQL 8.0引入了对派生表的“合并”优化很多情况下可以避免物化执行计划中可能不再显示DERIVED而是将子查询合并到外层查询中这是一个重要的性能改进。table(访问的表)显示这一步访问的是哪张表。有时不是表名而是derivedN其中N是id值指向一个派生临时表。unionM,N指向id为M和N的查询进行UNION操作后产生的临时表。2.2 关键性能指标列type、possible_keys、key、key_lentype(访问类型)这是衡量查询性能最关键的一列。它表示MySQL决定如何查找表中的行。从最优到最差常见的类型有system const eq_ref ref range index ALL。我们至少要保证查询达到range级别最好能达到ref。system表只有一行记录等于系统表是const类型的特例。const通过主键或唯一索引一次就找到了最多返回一条记录。因为只读一次所以速度极快。SELECT * FROM users WHERE id 1;eq_ref通常出现在多表关联查询中对于前表的每一行在后表中只匹配到唯一一行。这通常是通过后表的主键或唯一非空索引关联实现的。这是除system和const之外最好的关联类型。ref非唯一性索引扫描返回匹配某个单独值的所有行。比如在age列上有一个普通索引查询WHERE age 30可能会用到ref。range只检索给定范围的行使用一个索引来选择行。关键是在WHERE子句中出现了BETWEEN、、、IN()等范围查询操作符。这比全索引扫描 (index) 好因为它只扫描索引树的一部分。index全索引扫描Full Index Scan。index与ALL的区别是index只遍历索引树通常比ALL快因为索引文件通常比数据文件小。但如果需要回表且索引覆盖不全数据量大时依然很慢。ALL全表扫描Full Table Scan。这是最坏的情况意味着MySQL必须扫描整张表来找到匹配的行。对于大表这通常是灾难性的必须通过增加索引来避免。possible_keys与keypossible_keys查询可能使用到的索引。这一列显示的是理论上可以被优化器选用的索引。如果为NULL则没有相关的索引。key查询实际使用到的索引。如果为NULL则表示没有使用索引。这一列是事实possible_keys是理论。优化器可能出于成本考虑选择了一个不在possible_keys中的索引或者决定全表扫描。实操心得如果key列为NULL而possible_keys有值这往往是一个危险信号。它可能意味着1) 你的索引建得不对比如在选择性极差的列上建索引2) 查询写法导致索引失效比如对索引列做了函数计算WHERE YEAR(create_time)20233) 表数据量很小优化器认为全表扫描比走索引回表更快。key_len(使用的索引长度)表示索引中使用的字节数。通过这个值可以算出具体使用了索引的哪些部分复合索引的前缀。计算规则取决于列的数据类型和字符集。例如一个INTNOT NULL 列key_len是4。一个VARCHAR(255)UTF8字段如果字段可为NULLkey_len 255 * 3 1长度前缀 1NULL标志位 767。如果实际查询只用到了前10个字符且索引是前缀索引key_len可能更小。作用判断是否充分使用了复合索引。如果key_len小于索引定义的长度说明只使用了索引的左前缀部分。2.3 扫描与过滤列rows、filtered、Extrarows(预估扫描行数)MySQL优化器根据统计信息预估为了找到所需的行需要读取多少行数据。这是一个预估值不是精确值但能很好地反映查询的成本。对于关联查询这个值是通过将前一个表的rows值乘以后一个表的filtered百分比得到的嵌套循环次数。filtered(过滤百分比)这是一个百分比值表示存储引擎返回的数据在Server层过滤后剩下多少满足查询条件。rows * filtered / 100可以估算出将与下一张表关联的行数。这个值越大越好。如果很低比如10%说明索引过滤性不好或者查询条件写得太宽泛。Extra(额外信息)这一列包含不适合在其他列显示但非常重要的额外信息。很多性能问题在这里露出马脚。Using index表示使用了覆盖索引即查询的列全部包含在使用的索引中无需回表查询数据行。这是性能最好的情况之一。Using where表示在存储引擎返回行之后Server层又进行了过滤。如果type是ALL或index出现这个提示通常不是好兆头说明大量数据被从磁盘读到内存后又被丢弃。Using temporary表示MySQL需要使用临时表来存储结果集常见于排序 (ORDER BY) 和分组 (GROUP BY)且没有用到索引。这通常涉及磁盘IO性能很差。Using filesort表示MySQL无法利用索引完成排序需要额外的排序步骤。这个排序可能在内存中完成也可能需要磁盘文件取决于sort_buffer_size的设置和待排序数据的大小。Using join buffer (Block Nested Loop)表示关联查询时被驱动表没有使用索引需要用到连接缓冲区来加速。这也是一个需要关注的性能点。Impossible WHEREWHERE子句的值总是false导致查不到任何数据如WHERE 10。3. 实战案例拆解从执行计划定位性能瓶颈光说不练假把式。我们结合几个具体的慢查询案例看看如何运用EXPLAIN进行诊断和优化。3.1 案例一缺失索引导致的全表扫描假设我们有一张订单表orders约有1000万行数据。业务反馈“查询用户最近订单”的接口变慢。原始SQLSELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 10;执行计划 (EXPLAIN):idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEordersALLNULLNULLNULL987654310.00Using where; Using filesort解读与诊断type: ALL最严重的警报这意味着MySQL对orders表进行了全表扫描读取了近1000万行。key: NULL没有使用任何索引。Extra: Using where; Using filesort在扫描出的1000万行中用WHERE条件过滤出user_id12345的行过滤性filtered仅10%这里10%是预估实际可能一个用户没那么多订单。然后对过滤出的结果进行文件排序 (filesort) 来满足ORDER BY。根因WHERE user_id 12345这个条件没有索引可用。ORDER BY create_time DESC加剧了问题因为没有索引排序只能靠临时文件。优化方案为user_id创建索引。CREATE INDEX idx_user_id ON orders(user_id);再次查看执行计划idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEordersrefidx_user_ididx_user_id415100.00Using index condition; Using filesort优化后分析type: ref访问类型提升到了索引等值查找优秀。rows: 15预估扫描行数从1000万骤降到15行天壤之别。但是Extra: Using filesort依然存在。因为索引只帮助定位了user_id排序create_time仍需额外步骤。进阶优化创建复合索引(user_id, create_time)。这样索引可以同时满足查询条件和排序需求实现“索引覆盖排序”。CREATE INDEX idx_user_create ON orders(user_id, create_time DESC); -- MySQL 8.0支持降序索引优化后的执行计划Extra列很可能变为Using index实现了覆盖索引连回表和文件排序都省了性能达到极致。3.2 案例二索引失效与隐式转换有一张用户表users其中phone字段是VARCHAR(20)并且建立了索引idx_phone。查询时发现速度很慢。原始SQLSELECT * FROM users WHERE phone 13800138000; -- 注意phone是字符串类型但条件写成了数字执行计划idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEusersALLidx_phoneNULLNULL10000010.00Using where解读与诊断possible_keys: idx_phone优化器知道这个索引存在。key: NULL但最终没有使用它type是ALL全表扫描。rows值很大。根因隐式类型转换。phone列是VARCHAR而查询条件13800138000是一个数字。MySQL在执行比较时会将字符串类型的phone列值隐式转换为数字相当于对索引列做了函数操作 (CAST(phone AS SIGNED))导致索引失效。优化方案确保查询条件与列类型一致。SELECT * FROM users WHERE phone 13800138000; -- 加上引号优化后执行计划会显示type: ref, key: idx_phone查询瞬间完成。避坑指南除了隐式转换导致索引失效的常见“坑”还有对索引列使用函数WHERE LEFT(name, 3) abcWHERE YEAR(date_column) 2023。对索引列进行运算WHERE age 1 20。在索引列上使用NOT,!,并非绝对但多数情况下优化器会放弃索引。使用OR连接条件如果OR前后的条件列并非都有索引会导致索引失效。复合索引未遵循最左前缀原则索引是(a, b, c)查询条件是WHERE b 1 AND c 2。3.3 案例三复杂关联与驱动表选择有两张表dept部门表数据量小约100行和emp员工表数据量大约100万行。查询某个部门的所有员工。原始SQLSELECT e.* FROM emp e INNER JOIN dept d ON e.dept_id d.id WHERE d.name 研发部;执行计划idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEdALLPRIMARY,idx_nameNULLNULL10010.00Using where1SIMPLEerefidx_dept_ididx_dept_id45000100.00Using index condition解读与诊断这是一个典型的关联查询。执行顺序是id相同从上到下。第一行 (d表)type: ALL对dept表进行了全表扫描用WHERE d.name 研发部过滤。虽然dept表只有100行全表扫描代价不大但这里name字段没有索引possible_keys有idx_name但key为NULL可能索引失效或优化器认为全表更快。第二行 (e表)对于dept表扫描过滤出的每一行假设过滤后只有1行“研发部”通过idx_dept_id索引去emp表中查找匹配的员工。type: ref效率尚可。问题虽然最终效果可能还行但驱动表第一行的全表扫描是不必要的。如果dept.name条件能更快定位整体效率会提升。优化方案确保dept.name上有高效索引并让优化器能利用它。-- 确保dept.name有索引 CREATE INDEX idx_dept_name ON dept(name); -- 或者使用STRAIGHT_JOIN慎用强制指定驱动表但通常让优化器选择更好 -- SELECT e.* FROM dept d STRAIGHT_JOIN emp e ON e.dept_id d.id WHERE d.name 研发部;优化后dept表的访问类型可能变为const或ref直接定位到“研发部”这一行然后用这一行的id去驱动大表emp的索引查询效率最优。关于驱动表选择的经验在多表关联时优化器会选择它认为成本最小的表作为驱动表即执行计划中的第一张表。通常小表驱动大表是原则因为驱动表需要被循环。我们可以通过给被驱动表的关联字段加索引来加速内层循环。使用EXPLAIN可以验证优化器是否做出了我们期望的选择。4. 高级技巧与深度优化不止于看结果掌握了基础解读和常见案例我们可以更进一步利用EXPLAIN的一些高级功能和结合其他工具进行深度优化。4.1 EXPLAIN ANALYZE获取实际执行数据MySQL 8.0.18 引入了EXPLAIN ANALYZE。它与EXPLAIN最大的区别是它会实际执行查询然后输出执行计划以及每一步的实际耗时、实际返回行数等详细信息。这对于验证优化器预估 (rows) 是否准确、查找实际执行瓶颈至关重要。EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 10;输出格式是树状的包含了每个节点的实际执行时间如actual time0.1..0.2表示首次执行到所有循环完成的时间范围、实际返回行数 (rows10)、循环次数等。解读重点对比estimated rows和actual rows如果差异巨大说明表的统计信息可能过期了需要运行ANALYZE TABLE来更新。查看每个节点的actual time找到最耗时的步骤即瓶颈。观察是否有Filter操作它表示在索引扫描后进行了额外的过滤如果过滤掉很多行说明索引效率不高。4.2 格式化输出与可视化默认的表格输出在复杂查询时可能不够直观。EXPLAIN支持多种格式化输出EXPLAIN FORMATJSON SELECT ...输出详细的JSON格式信息包含成本估算等更多细节适合程序解析。EXPLAIN FORMATTREE SELECT ...输出树形结构更清晰地展示执行流程的层次关系尤其在包含子查询或UNION时。许多数据库客户端工具如MySQL Workbench, DataGrip, Navicat都提供了可视化的执行计划功能将EXPLAIN的结果以图形化的方式展现节点大小代表成本箭头表示数据流让性能瓶颈一目了然。善用这些工具可以极大提升分析效率。4.3 结合性能模式 (Performance Schema) 与慢查询日志EXPLAIN看的是“计划”是预估。要全面诊断还需要看“实际”运行情况。慢查询日志 (Slow Query Log)记录执行时间超过long_query_time阈值的SQL。通过mysqldumpslow或pt-query-digest工具分析慢日志找到最耗时的TOP SQL再用EXPLAIN去分析它们。这是发现问题的入口。Performance SchemaMySQL内置的性能监控库。可以查看更细粒度的性能数据例如events_statements_summary_by_digest汇总所有SQL模板的执行统计次数总耗时扫描行数等找到高频或高耗时的SQL模式。events_stages_*查看SQL执行各阶段如排序、创建临时表的耗时。 结合EXPLAIN和 Performance Schema你可以知道一条SQL不仅计划怎么走实际每一步花了多少时间。4.4 优化器提示 (Optimizer Hints) 与索引提示有时优化器选择的计划并不是最优的比如错误估计了数据分布。我们可以使用优化器提示来影响它的决策。但这应该是最后的手段并且需要充分测试。使用索引提示SELECT * FROM users USE INDEX (idx_email) WHERE email LIKE a%; -- 建议使用某个索引 SELECT * FROM users IGNORE INDEX (idx_email) WHERE ...; -- 忽略某个索引 SELECT * FROM users FORCE INDEX (idx_email) WHERE ...; -- 强制使用某个索引使用优化器提示(MySQL 5.7)SELECT /* MAX_EXECUTION_TIME(1000) */ * FROM big_table; -- 设置语句最大执行时间 SELECT /* JOIN_ORDER(t1, t2) */ * FROM t1 JOIN t2 ...; -- 指定关联顺序 SELECT /* MRR(t1) */ * FROM t1 WHERE ...; -- 启用多范围读优化重要警告滥用提示是危险的。数据库的统计信息会变数据分布会变。今天你强制使用的索引可能是最快的明天数据量增长后可能就成了最慢的。提示会使SQL语句变得脆弱难以维护。优先考虑通过优化索引、重写查询、更新统计信息来让优化器做出正确选择。4.5 统计信息与索引维护优化器制定执行计划的依据是表的统计信息如索引的区分度、数据行数等。如果统计信息过期优化器就可能制定出糟糕的计划。更新统计信息对于InnoDB表运行ANALYZE TABLE table_name;来更新表的统计信息。在发生大量数据变更如批量导入、删除后建议执行此操作。索引维护索引选择性索引列不同值的数量占总行数的比例。选择性越高越接近1索引效率越高。像“性别”这种只有两三种值的列建索引通常意义不大。索引合并留意Extra中的Using union(idx_a, idx_b); Using intersect(...)。这表示优化器使用了多个索引然后合并结果。有时这不如一个合适的复合索引高效。冗余与未使用索引定期检查information_schema.STATISTICS和sys.schema_unused_indexes需要安装sys库清理那些从未被使用或重复的索引因为它们会降低写性能。解读EXPLAIN执行计划是一个从“知其然”到“知其所以然”的过程。它要求我们不仅看懂每一列的输出更要理解其背后的数据库原理——索引是如何工作的、优化器是如何基于成本做决策的、数据是如何在磁盘和内存间流动的。掌握了这个工具你就拥有了从被动救火到主动预防数据库性能问题的能力。下次面对慢查询时别急着盲目尝试先EXPLAIN一下让它告诉你问题的真相。
