MySQL慢查询定位与索引性能调优：从EXPLAIN分析到覆盖索引优化

MySQL慢查询是数据库性能问题的最常见来源。一条执行时间从10ms退化到2000ms的SQL，在高并发场景下可导致连接池耗尽和连锁超时。本文从慢查询日志配置、EXPLAIN执行计划分析到索引优化策略，演示完整的SQL性能调优流程。

慢查询日志配置与采集

开启慢查询日志是定位问题的第一步。动态配置无需重启MySQL：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 阈值设为1秒（生产环境建议0.5秒）
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 验证配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

long_query_time设为1秒可捕获大部分有问题的查询，排查阶段可临时设为0.1秒获取更全面的慢查询样本。log_queries_not_using_indexes会记录所有全表扫描的查询，即使执行时间未超阈值。

使用pt-query-digest分析慢查询日志，按执行频次和总耗时排序定位优先优化目标：

# 按总耗时排序，Top 10慢查询
pt-query-digest --order-by Query_time:sum --limit 10 /var/log/mysql/slow.log

# 按调用次数排序
pt-query-digest --order-by Count --limit 10 /var/log/mysql/slow.log

pt-query-digest将相似SQL归并统计，输出每个SQL的执行次数、总耗时、最大/最小/平均耗时、返回行数等关键指标。优先优化”总耗时高且执行频次高”的SQL。

EXPLAIN执行计划深度解读

对目标SQL执行EXPLAIN，分析查询执行路径：

EXPLAIN SELECT o.order_id, o.amount, u.username, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'PAID' AND o.created_at >= '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN输出的关键字段：

+----+--------+----------+--------+---------+------+----------+--------------------------+
| id | table  | type     | key    | key_len | rows | filtered | Extra                    |
+----+--------+----------+--------+---------+------+----------+--------------------------+
|  1 | o      | ref      | idx_st | 102     | 5000 |   33.33  | Using where; Using filesort |
|  1 | u      | eq_ref   | PRIMARY| 8       | 1    |  100.00  | NULL                     |
|  1 | p      | eq_ref   | PRIMARY| 8       | 1    |  100.00  | NULL                     |
+----+--------+----------+--------+---------+------+----------+--------------------------+

type字段表示访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。index表示全索引扫描，比ALL好但仍然扫描全部索引条目。

Extra字段是优化的重要线索：Using filesort表示需要额外排序操作；Using temporary表示使用了临时表；Using index表示覆盖索引，无需回表；Using join buffer表示JOIN效率低。

上述执行计划中，orders表的Extra出现Using filesort，说明created_at排序未能利用索引。rows=5000表示预估扫描5000行。需要创建联合索引优化。

联合索引设计与最左匹配原则

联合索引遵循最左匹配原则，索引列的顺序决定可用范围。对于WHERE status=’PAID’ AND created_at>=’2026-07-01′ ORDER BY created_at DESC的查询，最优索引设计：

-- 联合索引：status在前，created_at在后
-- 等值条件列在前，范围条件列在后
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 验证优化后执行计划
EXPLAIN SELECT order_id, amount FROM orders 
WHERE status = 'PAID' AND created_at >= '2026-07-01'
ORDER BY created_at DESC LIMIT 20;
-- 优化后: type=range, Extra=Using index, rows=20

索引列顺序的设计原则：等值条件列放前面，范围条件列放后面，排序字段利用索引有序性避免filesort。这样WHERE条件利用索引前缀过滤，ORDER BY利用索引后缀的有序性，一次索引扫描同时满足过滤和排序。

常见索引设计误区：

-- 错误：范围条件列在前，等值条件列在后
-- created_at的范围扫描后，status无法走索引
ALTER TABLE orders ADD INDEX idx_bad (created_at, status);

-- 正确：等值在前，范围在后
ALTER TABLE orders ADD INDEX idx_good (status, created_at);

-- 验证：idx_bad的key_len仅包含created_at长度
-- idx_good的key_len包含status + created_at长度
EXPLAIN SELECT * FROM orders 
WHERE status = 'PAID' AND created_at >= '2026-07-01';

覆盖索引与回表优化

当查询所需的列全部包含在索引中时，InnoDB直接从索引返回数据，无需回表读取聚簇索引。覆盖索引可减少50%-90%的I/O：

-- 查询只需order_id和amount
SELECT order_id, amount FROM orders 
WHERE status = 'PAID' AND created_at >= '2026-07-01';

-- 当前索引 idx_status_created (status, created_at) 不含amount
-- 执行计划Extra: Using index condition（需要回表）

-- 添加覆盖索引，包含查询所需的所有列
ALTER TABLE orders ADD INDEX idx_covering (status, created_at, order_id, amount);

-- 优化后执行计划:
-- Extra: Using index（覆盖索引，无需回表）
-- 性能提升约3-5倍

覆盖索引以空间换性能，索引字段过多会导致索引体积膨胀和写入性能下降。生产环境中应对高频查询的TOP 10 SQL建立覆盖索引，低频查询不必过度优化。

深分页优化与子查询改写

LIMIT深分页（如LIMIT 100000, 20）在数据量大时性能急剧下降，因为MySQL需要扫描并丢弃前100000行：

-- 慢查询：扫描100020行，丢弃前100000行
SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY created_at DESC 
LIMIT 100000, 20;
-- 执行时间: 2.3s

-- 优化方案1：游标分页（推荐）
-- 利用上一页最后一条记录的created_at作为起点
SELECT * FROM orders 
WHERE status = 'PAID' AND created_at < '2026-08-04 15:30:00'
ORDER BY created_at DESC 
LIMIT 20;
-- 执行时间: 5ms

-- 优化方案2：延迟关联（子查询覆盖索引定位ID）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders 
    WHERE status = 'PAID' 
    ORDER BY created_at DESC 
    LIMIT 100000, 20
) t ON o.order_id = t.order_id
ORDER BY o.created_at DESC;
-- 执行时间: 120ms（子查询走覆盖索引）

游标分页方案在连续翻页场景下性能最优，但无法直接跳转到指定页码。延迟关联方案兼容传统分页，性能提升来自子查询仅扫描索引列（order_id在联合索引中），大幅减少回表次数。

子查询改写也是常见优化点。MySQL对子查询的优化能力有限，IN子查询在大数据量下常退化为相关子查询：

-- 慢：IN子查询可能逐行执行
SELECT * FROM orders 
WHERE user_id IN (
    SELECT id FROM users WHERE vip_level >= 5
);

-- 快：改写为JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level >= 5;

MySQL 8.0+对IN子查询有半连接优化，但仍建议在生产SQL中统一使用JOIN写法，避免版本差异导致的性能波动。