[MySQL高级](一) EXPLAIN用法和结果分析

前言在数据库性能优化工作中SQL 查询性能分析是至关重要的一环。MySQL 提供的 EXPLAIN 命令是分析和理解 SQL 执行计划的强大工具它能够揭示查询执行的内部机制帮助我们识别性能瓶颈并制定优化策略。本文旨在为数据库开发者和运维人员提供一个全面的 EXPLAIN 使用指南。我们将从 EXPLAIN 的基本概念入手详细解读执行计划中每个字段的含义并通过实际案例演示如何分析复杂查询的执行顺序。最后我们将总结一套基于 EXPLAIN 结果的索引优化决策流程帮助您在实际工作中快速定位并解决 SQL 性能问题。无论您是刚刚接触 EXPLAIN 的新手还是希望深化理解的资深开发者相信本文都能为您提供有价值的参考。1. EXPLAIN简介使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。通过EXPLAIN我们可以分析出以下结果表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询使用方式如下EXPLAIN SQL语句EXPLAIN SELECT * FROM t1;执行计划包含的信息2. 执行计划各字段含义详解2.1 idselect查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序id的结果共有3种情况id相同执行顺序由上至下总结加载表的顺序如上图table列所示t1 → t2 → t3id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行id相同不同同时存在如上图所示在id为1时table显示的是derived2这里指的是指向id为2的表即t3表的衍生表。2.2 select_type表示查询的类型主要用于区别普通查询、联合查询、子查询等复杂查询。常见和常用的值有如下几种SIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARYSUBQUERY在SELECT或WHERE列表中包含了子查询DERIVED在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表中UNION若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVEDUNION RESULT从UNION表获取结果的SELECT2.3 table显示这一行的数据是关于哪张表的2.4 type显示查询使用了哪种类型从最好到最差依次是system const eq_ref ref range index all一般来说得保证查询至少达到range级别最好能达到ref。system表只有一行记录等于系统表这是const类型的特例平时不会出现可以忽略不计const表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL就能将该查询转换为一个常量。首先进行子查询得到一个结果的d1临时表子查询条件为id 1是常量所以type是constid为1的相当于只查询一条记录所以type为system。eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref非唯一性索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行可能会找到多个符合条件的行属于查找和扫描的混合体。range只检索给定范围的行使用一个索引来选择行key列显示使用了哪个索引。一般在where语句中出现between、、、in等的查询这种范围扫描索引比全表扫描要好。indexFull Index Scanindex与All区别为index类型只遍历索引树。这通常比ALL快因为索引文件通常比数据文件小。id是主键所以存在主键索引allFull Table Scan将遍历全表以找到匹配的行2.5 possible_keys 和 key下面是一个根据 EXPLAIN 结果选择或调整索引的决策流程图可以帮助你系统地分析执行计划并制定优化策略flowchart TD A[开始分析 EXPLAIN 结果] -- B{type 字段是否为 ALL?} B -- 是 -- C[全表扫描需要优化] B -- 否 -- D{possible_keys 是否为空?} D -- 是 -- E[检查 WHERE 条件字段 考虑创建合适索引] D -- 否 -- F{key 是否为 NULL?} F -- 是 -- G[索引未使用 检查索引失效原因] F -- 否 -- H[索引已使用] C -- I[分析 WHERE 条件 创建合适索引] E -- I G -- I H -- J{分析 key_len 和 ref} J -- K[检查索引覆盖度 考虑复合索引] I -- L{Extra 字段分析} K -- L L -- M{是否有 Using filesort?} M -- 是 -- N[考虑优化 ORDER BY 添加排序字段索引] M -- 否 -- O{是否有 Using temporary?} O -- 是 -- P[优化 GROUP BY/ORDER BY 调整查询结构] O -- 否 -- Q{是否有 Using where?} Q -- 是 -- R[检查 WHERE 条件索引使用 考虑覆盖索引] Q -- 否 -- S[索引使用良好] N -- T[输出优化建议] P -- T R -- T S -- T T -- U[结束] style A fill:#e1f5fe style C fill:#ffebee style E fill:#fff3e0 style G fill:#fff3e0 style I fill:#f3e5f5 style N fill:#e8f5e8 style P fill:#e8f5e8 style R fill:#e8f5e8 style S fill:#c8e6c9 style T fill:#bbdefb style U fill:#f5f5f5流程图解读type 字段分析首先检查 type 字段是否为 ALL全表扫描如果是则需要优先优化。索引可用性检查查看 possible_keys 是否为空如果为空说明没有合适的索引可用。索引使用情况检查 key 字段是否为 NULL如果为 NULL 说明索引未使用需要分析原因。索引效率分析分析 key_len索引长度和 ref索引引用列判断索引覆盖度。Extra 字段分析重点关注 Using filesort、Using temporary、Using where 等关键信息。优化建议输出根据分析结果给出具体的索引优化建议。通过这个决策流程你可以系统地分析 EXPLAIN 结果找出索引使用的问题并制定相应的优化策略。possible_keys显示可能应用在这张表中的索引一个或多个。查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。key实际使用的索引如果为NULL则没有使用索引可能原因包括没有建立索引或索引失效查询中若使用了覆盖索引select后要查询的字段刚好和创建的索引字段完全相同则该索引仅出现在key列表中2.6 key_len表示索引中使用的字节数可通过该列计算查询中使用的索引的长度在不损失精确性的情况下长度越短越好。key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的。2.7 ref显示索引的哪一列被使用了如果可能的话最好是一个常数。哪些列或常量被用于查找索引列上的值。2.8 rows根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数也就是说用的越少越好。2.9 Extra包含不适合在其他列中显式但十分重要的额外信息2.9.1 Using filesort九死一生说明MySQL会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为文件排序。2.9.2 Using temporary十死无生使用了临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。2.9.3 Using index发财了表示相应的select操作中使用了覆盖索引Covering Index避免访问了表的数据行效率不错。如果同时出现using where表明索引被用来执行索引键值的查找如果没有同时出现using where表明索引用来读取数据而非执行查找动作。2.9.4 Using where表明使用了where过滤2.9.5 Using join buffer表明使用了连接缓存。比如说在查询的时候多表join的次数非常多那么可以将配置文件中的缓冲区的join buffer调大一些。2.9.6 impossible wherewhere子句的值总是false不能用来获取任何元组SELECT * FROM t_user WHERE id 1 AND id 2;2.9.7 select tables optimized away在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。2.9.8 distinct优化distinct操作在找到第一匹配的元组后即停止找同样值的动作3. 综合实例分析执行顺序分析执行顺序1select_type为UNION说明第四个select是UNION里的第二个select最先执行【select name, id from t2】执行顺序2id为3是整个查询中第三个select的一部分。因查询包含在from中所以为DERIVED【select id, name from t1 where other_column】执行顺序3select列表中的子查询select_type为subquery为整个查询中的第二个select【select id from t3】执行顺序4id列为1表示是UNION里的第一个selectselect_type列的primary表示该查询为外层查询table列被标记为derived3表示查询结果来自一个衍生表其中derived3中的3代表该查询衍生自第三个select查询即id为3的select。【select d1.name ...】执行顺序5代表从UNION的临时表中读取行的阶段table列的union1,4表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】4. 总结与优化建议通过EXPLAIN分析SQL执行计划是MySQL性能优化的重要手段。在实际工作中我们应该关注type字段尽量让查询达到range级别以上避免出现ALL全表扫描合理使用索引确保possible_keys和key字段显示使用了合适的索引避免文件排序和临时表Extra字段中出现Using filesort和Using temporary时需要特别关注优化连接查询多表连接时注意连接顺序和索引使用定期分析执行计划对复杂查询定期使用EXPLAIN进行分析及时发现性能问题掌握EXPLAIN的各个字段含义能够帮助我们快速定位SQL性能瓶颈制定有效的优化策略。5. 实战优化案例下面我们通过一个具体的电商订单查询案例演示如何从 EXPLAIN 分析到索引优化的完整排查过程。5.1 问题场景假设有一个电商订单表orders表结构如下CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, order_status TINYINT NOT NULL COMMENT 0-待支付 1-已支付 2-已发货 3-已完成 4-已取消, order_amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL, INDEX idx_user_id (user_id), INDEX idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;现有查询需求查找某个用户在特定时间段内已完成的订单并按订单金额降序排列SELECT id, user_id, product_id, order_amount, create_time FROM orders WHERE user_id 1001 AND order_status 3 AND create_time BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59 ORDER BY order_amount DESC LIMIT 10;5.2 优化前 EXPLAIN 分析执行 EXPLAIN 查看执行计划EXPLAIN SELECT id, user_id, product_id, order_amount, create_time FROM orders WHERE user_id 1001 AND order_status 3 AND create_time BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59 ORDER BY order_amount DESC LIMIT 10;执行计划结果分析字段值含义分析id1简单查询select_typeSIMPLE简单SELECT查询tableorders查询orders表typeref使用了非唯一索引扫描possible_keysidx_user_id, idx_create_time可能使用user_id或create_time索引keyidx_user_id实际使用了user_id索引key_len4使用索引长度为4字节INT类型refconst使用常量值查找rows1250预计扫描1250行ExtraUsing where; Using filesort使用WHERE过滤需要文件排序问题诊断索引选择问题虽然possible_keys中有idx_create_time但优化器选择了idx_user_id索引。Using filesort由于ORDER BY order_amount DESC而order_amount字段没有索引导致需要额外的文件排序操作。过滤效率低使用idx_user_id索引后还需要在结果集中过滤order_status3和create_time范围条件。5.3 优化方案根据EXPLAIN分析我们创建复合索引来优化查询-- 创建复合索引包含查询条件和排序字段 CREATE INDEX idx_user_status_time_amount ON orders(user_id, order_status, create_time, order_amount);索引设计思路user_id作为等值查询条件放在最左order_status第二个等值查询条件create_time范围查询条件放在等值条件之后order_amount排序字段让索引支持ORDER BY避免filesort5.4 优化后 EXPLAIN 分析创建索引后再次执行EXPLAIN字段值含义分析id1简单查询select_typeSIMPLE简单SELECT查询tableorders查询orders表typerange范围扫描比ref更好possible_keysidx_user_id, idx_create_time, idx_user_status_time_amount多个索引可用keyidx_user_status_time_amount使用了新创建的复合索引key_len13索引使用长度13字节INTINTDATETIMEDECIMALrefNULL范围查询不使用refrows85预计扫描行数从1250降到85大幅减少ExtraUsing where; Using index使用索引覆盖效率极高5.5 优化效果对比指标优化前优化后提升效果typerefrange扫描类型更优keyidx_user_ididx_user_status_time_amount使用更合适的复合索引rows125085扫描行数减少93%ExtraUsing where; Using filesortUsing where; Using index消除filesort使用覆盖索引执行时间约120ms约15ms性能提升8倍5.6 优化总结通过这个案例我们可以总结出以下优化经验复合索引设计原则将等值查询条件放在最左范围查询条件次之排序字段放在最后。避免Using filesort通过创建包含排序字段的索引让MySQL能够利用索引的有序性避免额外的排序操作。覆盖索引优势当索引包含所有查询字段时Extra会显示Using index避免回表查询大幅提升性能。定期分析执行计划对于核心业务查询应定期使用EXPLAIN分析及时发现性能退化问题。索引选择性考虑user_id1001的选择性可能不高但结合order_status和create_time后复合索引的选择性大大提升。这个案例展示了如何通过EXPLAIN分析定位性能问题设计合适的复合索引并验证优化效果的全过程。在实际工作中应结合业务场景和数据分布特点灵活运用这些优化技巧。