如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

MySQL 的 EXPLAIN 语句是性能优化的关键工具用于分析查询执行计划。一、基本用法1. 基本语法-- 标准用法EXPLAINSELECT*FROMusersWHEREage25;-- 详细格式MySQL 8.0EXPLAINFORMATJSONSELECT*FROMusersWHEREage25;-- 分析实际执行MySQL 8.0.18EXPLAINANALYZESELECT*FROMusersWHEREage25;2. 查看表结构信息-- 查看表的索引信息辅助分析SHOWINDEXFROMusers;-- 查看表结构DESCusers;二、EXPLAIN 输出字段详解字段说明优化重点id查询标识符子查询的执行顺序select_type查询类型识别复杂查询table访问的表表连接顺序partitions匹配的分区分区表优化type访问类型核心指标possible_keys可能使用的索引索引选择key实际使用的索引索引有效性key_len索引长度索引使用程度ref索引比较的列连接条件rows预估扫描行数性能关键filtered过滤百分比查询效率Extra额外信息优化建议三、关键字段深度解析1. type 字段访问类型从好到坏排序-- 最佳const/system常量查询EXPLAINSELECT*FROMusersWHEREid1;-- type: const-- 优秀eq_ref唯一索引关联EXPLAINSELECT*FROMusers uJOINorders oONu.ido.user_idWHEREu.id1;-- type: eq_ref (对于orders表)-- 良好ref非唯一索引EXPLAINSELECT*FROMusersWHEREemailtestexample.com;-- type: ref-- 一般range范围扫描EXPLAINSELECT*FROMusersWHEREageBETWEEN20AND30;-- type: range-- 较差index全索引扫描EXPLAINSELECTCOUNT(*)FROMusers;-- type: index-- 最差ALL全表扫描❌EXPLAINSELECT*FROMusersWHEREnameLIKE%john%;-- type: ALL2. Extra 字段重要信息-- 使用索引覆盖很好EXPLAINSELECTid,nameFROMusersWHEREage25;-- Extra: Using index-- 使用临时表需要优化EXPLAINSELECTDISTINCTdepartmentFROMusers;-- Extra: Using temporary-- 使用文件排序需要优化EXPLAINSELECT*FROMusersORDERBYcreate_timeDESC;-- Extra: Using filesort-- 使用WHERE条件过滤EXPLAINSELECT*FROMusersWHEREage25ANDstatus1;-- Extra: Using where四、实战分析案例案例1基础查询优化-- 原始查询性能差EXPLAINSELECT*FROMordersWHEREcustomer_id100ANDorder_date2024-01-01;-- 可能的输出-- type: ALL (全表扫描)-- key: NULL (未使用索引)-- rows: 10000 (扫描行数多)优化方案-- 添加复合索引CREATEINDEXidx_customer_dateONorders(customer_id,order_date);-- 再次分析EXPLAINSELECT*FROMordersWHEREcustomer_id100ANDorder_date2024-01-01;-- type: range-- key: idx_customer_date-- rows: 50 (显著减少)案例2JOIN 查询优化-- 多表连接查询EXPLAINSELECTu.name,o.order_no,p.product_nameFROMusers uJOINorders oONu.ido.user_idJOINproducts pONo.product_idp.idWHEREu.city北京ANDo.statuscompleted;-- 分析要点-- 1. 检查每个表的type字段-- 2. 确认连接使用的索引-- 3. 查看rows乘积连接成本优化策略-- 确保连接字段有索引CREATEINDEXidx_user_cityONusers(city);CREATEINDEXidx_order_user_statusONorders(user_id,status);CREATEINDEXidx_product_idONproducts(id);案例3子查询优化-- 低效的子查询EXPLAINSELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREamount1000);-- 可能的优化改为JOINEXPLAINSELECTDISTINCTu.*FROMusers uJOINorders oONu.ido.user_idWHEREo.amount1000;五、高级分析技巧1. JSON 格式详细分析MySQL 8.0EXPLAINFORMATJSONSELECT*FROMusersWHEREage25;-- 输出包含详细的成本估算-- query_cost: 查询总成本-- read_cost: 读取成本-- eval_cost: 计算成本-- prefix_cost: 前缀成本2. EXPLAIN ANALYZE 实际执行分析-- MySQL 8.0.18 支持显示实际执行数据EXPLAINANALYZESELECT*FROMusersWHEREage25;-- 输出示例-- - Filter: (users.age 25) (cost103.45 rows500) (actual time0.125..2.456 rows480 loops1)-- - Table scan on users (cost103.45 rows1000) (actual time0.089..1.234 rows1000 loops1)3. 分析 UPDATE/DELETE 语句-- 先使用SELECT模拟分析EXPLAINSELECT*FROMordersWHEREstatuspendingANDcreate_date2024-01-01;-- 然后执行实际的UPDATE/DELETEUPDATEordersSETstatusexpiredWHEREstatuspendingANDcreate_date2024-01-01;六、常见问题及解决方案问题1全表扫描type: ALL症状rows 值很大key 为 NULL解决添加合适的索引问题2文件排序Using filesort症状ORDER BY 没有使用索引解决为排序字段添加索引或使用覆盖索引问题3临时表Using temporary症状GROUP BY 或 DISTINCT 操作解决为分组字段添加索引问题4索引选择错误症状possible_keys 有索引但 key 为 NULL解决使用 FORCE INDEX 或优化查询条件七、性能优化检查清单✅ 索引优化检查type 字段是否为 const/eq_ref/ref/rangekey 字段是否使用了合适的索引key_len 是否合理避免过长索引是否使用了覆盖索引Using index✅ 查询优化检查rows 字段值是否过大是否有 Using filesort 或 Using temporary连接顺序是否合理WHERE 条件是否有效利用索引✅ 表设计检查表是否有合适的主键索引选择性是否足够高是否存在冗余索引八、实用脚本工具1. 自动化分析脚本-- 生成优化建议SELECTTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAMEFROMinformation_schema.STATISTICSWHERETABLE_SCHEMAyour_databaseANDTABLE_NAMEyour_tableORDERBYTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;2. 监控慢查询-- 开启慢查询日志SETGLOBALslow_query_log1;SETGLOBALlong_query_time2;-- 查看慢查询SHOWVARIABLESLIKEslow_query%;SELECT*FROMmysql.slow_log;总结EXPLAIN 是 MySQL 性能优化的核心工具使用时重点关注type 字段确保不是 ALLkey 字段确认使用了正确的索引rows 字段预估扫描行数要尽量少Extra 字段避免 Using filesort 和 Using temporary通过系统性的 EXPLAIN 分析可以快速定位性能瓶颈并进行针对性优化。