汤不热吧在日常的MySQL数据库运维和开发中，慢查询是最常见的性能瓶颈之一。一条没有优化的SQL语句可能在数据量小时表现良好，但随着数据增长到百万甚至千万级别，查询耗时可能从毫秒级飙升到秒级甚至分钟级。本文将从EXPLAIN执行计划的深度解读入手，结合真实案例，手把手教你如何定位和优化MySQL慢查询。
一、为什么需要关注EXPLAIN执行计划
MySQL的查询优化器会为每条SQL语句选择一个它认为最优的执行方案，包括使用哪些索引、表的连接顺序、数据的读取方式等。但优化器并非万能，它可能因为统计信息不准确、索引设计不合理或者SQL写法问题而选择次优的执行计划。
EXPLAIN命令就是我们窥探优化器决策过程的窗口。通过分析EXPLAIN的输出，我们可以清楚地知道：
- 查询是否使用了索引，使用了哪个索引
- 预计需要扫描多少行数据
- 表的连接顺序和连接方式
- 是否存在隐式类型转换导致索引失效
- 是否出现了文件排序（filesort）或临时表（temporary）
二、EXPLAIN输出字段详解
执行EXPLAIN SELECT ...后，MySQL会返回一张结果表，包含多个关键字段。理解每个字段的含义是优化的基础。
2.1 id与select_type
id字段标识查询中每个SELECT子句的执行顺序。id越大越先执行，id相同则从上到下执行。在复杂查询中（子查询、UNION等），id可以帮助我们理解查询的层次结构。
select_type字段标识每个SELECT的类型：
- SIMPLE：简单查询，没有子查询或UNION
- PRIMARY：最外层的查询
- SUBQUERY：WHERE或SELECT列表中的子查询
- DERIVED：FROM子句中的子查询（派生表）
- UNION：UNION中第二个及之后的SELECT
2.2 type字段——访问类型
这是EXPLAIN中最重要的字段之一，它直接反映了查询的效率。从最优到最差的排列顺序为：
- system：表只有一行（系统表），这是最优的情况
- const：通过主键或唯一索引查找，最多返回一行，非常高效
- eq_ref：在连接查询中，对于前一张表的每一行，使用主键或唯一索引精确匹配一行
- ref：使用普通索引查找，可能返回多行
- range：使用索引进行范围扫描，常见于BETWEEN、大于、小于、IN等条件
- index：全索引扫描，虽然比ALL好，但仍然扫描了整棵索引树
- ALL：全表扫描，最差的情况，数据量大时必须优化
当type为ALL或index时，说明查询没有使用有效的索引，需要重点关注。
2.3 key、key_len与ref
key字段显示MySQL实际决定使用的索引名称。如果为NULL，说明没有使用任何索引。key_len表示索引使用的字节长度，这个值可以帮助我们判断复合索引中有多少列被利用了。例如，一个由INT（4字节）加上VARCHAR(50)（utf8mb4编码，4乘以50加2等于202字节）组成的联合索引，key_len为206时表示两列都被使用了，如果只有4则表示只使用了第一列。
ref字段显示了与索引进行比较的列或常量。如果是const说明是与常量比较，如果是列名则说明是两表关联的条件。
2.4 Extra字段——隐藏的性能杀手
Extra字段包含了很多重要的附加信息，其中一些是性能优化的关键信号：
| Extra值 | 含义 | 是否需要优化 |
|---|---|---|
| Using index | 覆盖索引，查询所需数据全部从索引中获取，无需回表 | 好，无需优化 |
| Using where | 在存储引擎层过滤后，MySQL服务层还需额外过滤 | 视情况而定 |
| Using temporary | 查询需要创建临时表来存储中间结果 | 需要优化 |
| Using filesort | 无法利用索引完成排序，需要额外的排序操作 | 需要优化 |
| Select tables optimized away | 优化器直接从索引中获取结果，甚至不需要访问表 | 最优状态 |
三、常见慢查询场景与优化实战
下面通过几个真实场景，展示如何利用EXPLAIN定位问题并进行优化。每个场景都包含优化前后的对比。
3.1 场景一：索引失效的隐式转换
假设有一张用户表，手机号字段phone的类型是VARCHAR(20)，并且建有索引。当你用数字类型去查询时：
-- 优化前：索引失效
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- type: ALL, key: NULL, rows: 2000000MySQL会对phone列进行隐式类型转换（将字符串转为数字），导致索引失效。优化方法很简单，加上引号使其成为字符串：
-- 优化后：索引生效
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- type: ref, key: idx_phone, rows: 1这是一个非常隐蔽的问题，因为SQL语法上不会报错，但性能差异可能是数千倍。类似的问题还可能出现在字符集不一致的两张表进行JOIN时。
3.2 场景二：大表JOIN查询的索引优化
多表关联是MySQL中最常见的查询模式，也是最容易产生慢查询的场景。假设有订单表orders和状态日志表status_log通过外键关联，而status_log表有上亿条记录但order_id字段没有索引：
-- 优化前：status_log表全表扫描
EXPLAIN SELECT o.order_no, o.amount, s.status, s.created_at
FROM orders o
JOIN status_log s ON s.order_id = o.id
WHERE o.created_at > '2024-01-01';
-- o表: type=range, key=idx_created_at, rows=50000
-- s表: type=ALL, rows=10000000（全表扫描！）解决方案是在status_log表的order_id字段上添加索引：
ALTER TABLE status_log ADD INDEX idx_order_id (order_id);
-- 优化后
-- o表: type=range, key=idx_created_at, rows=50000
-- s表: type=ref, key=idx_order_id, rows=3仅此一个改动，查询耗时从12秒降低到0.08秒，提升了150倍。关键在于EXPLAIN清楚地显示了哪个表在做全表扫描。
3.3 场景三：ORDER BY导致的filesort
当你在查询中使用ORDER BY排序，而排序字段不在索引中或者排序方向与索引不一致时，MySQL需要额外执行文件排序操作，这在大数据集上非常耗时。
-- 优化前
EXPLAIN SELECT * FROM products
WHERE category_id = 5 AND status = 1
ORDER BY sales_count DESC
LIMIT 20;
-- type=ref, key=idx_category_status, Extra=Using filesort虽然category_id和status上有联合索引，但排序字段sales_count不在索引中，所以产生了filesort。优化方案是创建一个包含所有三列的联合索引：
ALTER TABLE products ADD INDEX idx_cat_status_sales (category_id, status, sales_count DESC);
-- 优化后：type=ref, key=idx_cat_status_sales, Extra=Using index新的索引不仅消除了filesort，还实现了覆盖索引（Using index），查询直接从索引树返回结果，无需回表读取数据行。
四、EXPLAIN进阶：EXPLAIN ANALYZE与JSON格式
4.1 EXPLAIN ANALYZE（MySQL 8.0.18+）
MySQL 8.0.18引入了EXPLAIN ANALYZE命令，它不仅展示执行计划，还会真正执行查询并返回每个步骤的实际执行时间、实际扫描行数和循环次数。这对于验证预估是否准确非常有帮助。
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > '2024-06-01'
GROUP BY c.id
ORDER BY order_count DESC
LIMIT 10;从输出中我们可以清楚看到每一步的实际耗时和行数。如果预估行数和实际行数差距很大（比如预估100行，实际100万行），说明表的统计信息过时，应该执行ANALYZE TABLE更新统计信息。
4.2 EXPLAIN FORMAT=JSON
JSON格式的EXPLAIN输出比表格格式更加详细，它包含了查询成本（query_cost）、数据读取成本等信息，适合用于自动化分析和工具集成。
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 1000\G在JSON输出中，关注query_cost字段，它是优化器估算的总成本。对比不同写法的SQL的query_cost，可以快速判断哪种写法更优。同时关注rows_examined_per_scan和rows_produced_per_join，这些值越小越好。
五、慢查询日志与系统化优化流程
单纯依赖EXPLAIN逐条优化是被动的方式。在生产环境中，我们应该建立系统化的慢查询发现和优化机制。
5.1 开启慢查询日志
-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';5.2 使用pt-query-digest分析
Percona的pt-query-digest工具能提供比mysqldumpslow更详细的分析报告，包括查询指纹、百分位耗时分布等：
# 按总执行时间排序，查看最慢的20类查询
pt-query-digest /var/log/mysql/slow.log --limit=20 --order-by=Query_time:sum5.3 performance_schema分析
MySQL 5.7+的performance_schema提供了更精确的查询性能统计，不需要开启慢查询日志即可获取数据：
SELECT
DIGEST_TEXT,
COUNT_STAR as exec_count,
ROUND(SUM_TIMER_WAIT/1e12, 2) as total_time_sec,
ROUND(AVG_TIMER_WAIT/1e12, 4) as avg_time_sec,
SUM_ROWS_EXAMINED as rows_examined,
SUM_ROWS_SENT as rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;这个查询直接从内存中的统计信息读取，开销极低，适合在生产环境定期运行以发现潜在的性能问题。
六、索引优化的核心原则
基于大量实战经验，总结出以下索引优化的核心原则，这些原则在使用EXPLAIN分析时尤其有用。
6.1 最左前缀原则
联合索引(a, b, c)等效于创建了(a)、(a, b)、(a, b, c)三个索引。WHERE条件中必须包含索引的最左列，否则索引无法使用。MySQL 8.0引入了索引跳跃扫描（Index Skip Scan），在某些场景下可以放宽这个限制。
6.2 覆盖索引减少回表
当SELECT的字段全部包含在索引中时，MySQL可以直接从索引返回结果，无需回表读取数据行。在EXPLAIN中表现为Extra显示Using index。对于高频查询，设计覆盖索引是性价比最高的优化手段之一。
-- 覆盖索引示例
ALTER TABLE orders ADD INDEX idx_cover (customer_id, status, amount, created_at);
-- 这条查询可以完全走覆盖索引
SELECT status, amount, created_at
FROM orders
WHERE customer_id = 1000 AND status = 'paid';6.3 索引选择性与前缀索引
索引的选择性（不重复值数除以总行数）越高，索引过滤效果越好。对于长字符串字段，可以使用前缀索引来减少索引空间占用。通过以下SQL找到最优前缀长度：
SELECT
COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as sel_5,
COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) as sel_8,
COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) as sel_12,
COUNT(DISTINCT email) / COUNT(*) as sel_full
FROM users;
-- 选择接近sel_full的最短前缀
ALTER TABLE users ADD INDEX idx_email_prefix (email(8));七、实际优化检查清单
在排查慢查询时，按照以下清单逐项检查，可以快速定位问题：
| 检查项 | 检查方法 | 期望结果 |
|---|---|---|
| 是否使用了索引 | EXPLAIN查看type和key字段 | type至少为range，key不为NULL |
| 是否有filesort | EXPLAIN查看Extra字段 | 不出现Using filesort |
| 是否有临时表 | EXPLAIN查看Extra字段 | 不出现Using temporary |
| 扫描行数是否合理 | EXPLAIN的rows字段 | rows越小越好 |
| 是否有隐式转换 | 对比字段类型与查询值类型 | 类型完全匹配 |
| 联合索引是否合理 | 检查key_len和最左前缀 | 尽可能多的列被利用 |
| 统计信息是否过时 | EXPLAIN ANALYZE对比预估与实际 | 预估行数与实际行数在同一量级 |
总结
MySQL EXPLAIN是DBA和开发者必备的性能诊断工具。通过理解EXPLAIN的每个字段含义，我们可以系统性地定位慢查询的根因——无论是缺少索引、隐式类型转换、还是不合理的SQL写法。配合慢查询日志和performance_schema，建立从发现问题到优化验证的完整闭环。记住，性能优化不是一次性工作，随着数据量增长和业务变化，定期review执行计划才能保持数据库的最佳状态。