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

MySQL 的 EXPLAIN 语句是性能优化的关键工具，用于分析查询执行计划。

一、基本用法

1. 基本语法

-- 标准用法EXPLAINSELECT*FROMusersWHEREage>25;-- 详细格式（MySQL 8.0+）EXPLAINFORMAT=JSONSELECT*FROMusersWHEREage>25;-- 分析实际执行（MySQL 8.0.18+）EXPLAINANALYZESELECT*FROMusersWHEREage>25;

2. 查看表结构信息

-- 查看表的索引信息（辅助分析）SHOWINDEXFROMusers;-- 查看表结构DESCusers;

二、EXPLAIN 输出字段详解

三、关键字段深度解析

1. type 字段（访问类型，从好到坏排序）

-- 最佳：const/system（常量查询）EXPLAINSELECT*FROMusersWHEREid=1;-- type: const-- 优秀：eq_ref（唯一索引关联）EXPLAINSELECT*FROMusers uJOINorders oONu.id=o.user_idWHEREu.id=1;-- type: eq_ref (对于orders表)-- 良好：ref（非唯一索引）EXPLAINSELECT*FROMusersWHEREemail='test@example.com';-- type: ref-- 一般：range（范围扫描）EXPLAINSELECT*FROMusersWHEREageBETWEEN20AND30;-- type: range-- 较差：index（全索引扫描）EXPLAINSELECTCOUNT(*)FROMusers;-- type: index-- 最差：ALL（全表扫描）❌EXPLAINSELECT*FROMusersWHEREnameLIKE'%john%';-- type: ALL

2. Extra 字段重要信息

-- 使用索引覆盖（很好）EXPLAINSELECTid,nameFROMusersWHEREage>25;-- Extra: Using index-- 使用临时表（需要优化）EXPLAINSELECTDISTINCTdepartmentFROMusers;-- Extra: Using temporary-- 使用文件排序（需要优化）EXPLAINSELECT*FROMusersORDERBYcreate_timeDESC;-- Extra: Using filesort-- 使用WHERE条件过滤EXPLAINSELECT*FROMusersWHEREage>25ANDstatus=1;-- Extra: Using where

四、实战分析案例

案例1：基础查询优化

-- 原始查询（性能差）EXPLAINSELECT*FROMordersWHEREcustomer_id=100ANDorder_date>'2024-01-01';-- 可能的输出：-- type: ALL (全表扫描)-- key: NULL (未使用索引)-- rows: 10000 (扫描行数多)

优化方案：

-- 添加复合索引CREATEINDEXidx_customer_dateONorders(customer_id,order_date);-- 再次分析EXPLAINSELECT*FROMordersWHEREcustomer_id=100ANDorder_date>'2024-01-01';-- type: range-- key: idx_customer_date-- rows: 50 (显著减少)

案例2：JOIN 查询优化

-- 多表连接查询EXPLAINSELECTu.name,o.order_no,p.product_nameFROMusers uJOINorders oONu.id=o.user_idJOINproducts pONo.product_id=p.idWHEREu.city='北京'ANDo.status='completed';-- 分析要点：-- 1. 检查每个表的type字段-- 2. 确认连接使用的索引-- 3. 查看rows乘积（连接成本）

优化策略：

-- 确保连接字段有索引CREATEINDEXidx_user_cityONusers(city);CREATEINDEXidx_order_user_statusONorders(user_id,status);CREATEINDEXidx_product_idONproducts(id);

案例3：子查询优化

-- 低效的子查询EXPLAINSELECT*FROMusersWHEREidIN(SELECTuser_idFROMordersWHEREamount>1000);-- 可能的优化：改为JOINEXPLAINSELECTDISTINCTu.*FROMusers uJOINorders oONu.id=o.user_idWHEREo.amount>1000;

五、高级分析技巧

1. JSON 格式详细分析（MySQL 8.0+）

EXPLAINFORMAT=JSONSELECT*FROMusersWHEREage>25;-- 输出包含详细的成本估算：-- query_cost: 查询总成本-- read_cost: 读取成本-- eval_cost: 计算成本-- prefix_cost: 前缀成本

2. EXPLAIN ANALYZE 实际执行分析

-- MySQL 8.0.18+ 支持，显示实际执行数据EXPLAINANALYZESELECT*FROMusersWHEREage>25;-- 输出示例：-- -> Filter: (users.age > 25) (cost=103.45 rows=500) (actual time=0.125..2.456 rows=480 loops=1)-- -> Table scan on users (cost=103.45 rows=1000) (actual time=0.089..1.234 rows=1000 loops=1)

3. 分析 UPDATE/DELETE 语句

-- 先使用SELECT模拟分析EXPLAINSELECT*FROMordersWHEREstatus='pending'ANDcreate_date<'2024-01-01';-- 然后执行实际的UPDATE/DELETEUPDATEordersSETstatus='expired'WHEREstatus='pending'ANDcreate_date<'2024-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/range？
• key 字段是否使用了合适的索引？
• key_len 是否合理（避免过长索引）？
• 是否使用了覆盖索引（Using index）？

✅ 查询优化检查

• rows 字段值是否过大？
• 是否有 Using filesort 或 Using temporary？
• 连接顺序是否合理？
• WHERE 条件是否有效利用索引？

✅ 表设计检查

• 表是否有合适的主键？
• 索引选择性是否足够高？
• 是否存在冗余索引？

八、实用脚本工具

1. 自动化分析脚本

-- 生成优化建议SELECTTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAMEFROMinformation_schema.STATISTICSWHERETABLE_SCHEMA='your_database'ANDTABLE_NAME='your_table'ORDERBYTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;

2. 监控慢查询

-- 开启慢查询日志SETGLOBALslow_query_log=1;SETGLOBALlong_query_time=2;-- 查看慢查询SHOWVARIABLESLIKE'slow_query%';SELECT*FROMmysql.slow_log;

总结

EXPLAIN 是 MySQL 性能优化的核心工具，使用时重点关注：

1. type 字段：确保不是 ALL
2. key 字段：确认使用了正确的索引
3. rows 字段：预估扫描行数要尽量少
4. Extra 字段：避免 Using filesort 和 Using temporary

通过系统性的 EXPLAIN 分析，可以快速定位性能瓶颈并进行针对性优化。