MySQL 8.0查询优化：慢SQL诊断与执行计划深度解读指南

慢查询日志的配置与分析起点

MySQL性能调优的第一步是找到问题SQL。慢查询日志是最直接的诊断工具，它记录执行时间超过阈值的所有SQL语句。生产环境中需要合理配置阈值，避免日志量过大影响IO性能：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志，阈值设为0.5秒
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
SET GLOBAL min_examined_row_limit = 100;        -- 至少扫描100行才记录

开启后使用mysqldumpslow或pt-query-digest分析慢日志：

# 按平均执行时间排序Top 10
$ mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# pt-query-digest更强大，支持指纹聚合
$ pt-query-digest /var/lib/mysql/slow.log --limit 10

# 输出示例：
# Rank Query ID        Response time  Calls
# 1    0x5A3B2C1D...  1523.4s 45.2%  892
# 2    0x7E8F9A0B...   876.1s 26.1%  3456

pt-query-digest的指纹聚合功能可以将参数不同但结构相同的SQL归为一类，快速定位到哪类查询最耗时间。

EXPLAIN执行计划的关键列深度解读

找到慢SQL后，用EXPLAIN分析执行计划。MySQL 8.0的EXPLAIN输出包含12列，其中以下几个最为关键：

EXPLAIN FORMAT=TREE
SELECT o.order_id, o.total_amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-07-01'
  AND o.status = 'paid'
ORDER BY o.total_amount DESC
LIMIT 20;

type列：访问类型，从优到差排列：system > const > eq_ref > ref > range > index > ALL。生产环境应避免ALL（全表扫描）和index（全索引扫描）。eq_ref出现在JOIN时主键或唯一索引匹配，是最优的JOIN访问方式。

key列：实际使用的索引名称。如果为NULL表示未使用索引，需要重点关注。

rows列：MySQL预估需要扫描的行数。这个值基于统计信息，不一定精确，但量级是对的。如果rows值远大于实际返回行数，说明索引选择性差。

Extra列：额外信息，常见的关键值：

-- Using index: 覆盖索引，不需要回表，性能最优
-- Using where: 存储引擎返回数据后还需要在Server层过滤
-- Using temporary: 使用临时表，通常出现在GROUP BY无索引时
-- Using filesort: 额外排序操作，需要优化索引避免
-- Using index condition: 索引下推(ICP)，5.6+特性，减少回表次数

索引优化实战：覆盖索引与索引下推

覆盖索引是指查询的所有列都包含在索引中，无需回表读取行数据。以下是一个覆盖索引优化的案例：

-- 优化前：需要回表读取total_amount和status
EXPLAIN SELECT order_id, total_amount, status
FROM orders
WHERE user_id = 10086;

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_user_cover (user_id, total_amount, status);

-- 优化后：Using index，零回表
EXPLAIN SELECT order_id, total_amount, status
FROM orders
WHERE user_id = 10086;
-- Extra: Using index

索引下推（Index Condition Pushdown, ICP）是MySQL 5.6+的优化，将WHERE条件在索引扫描阶段过滤，减少回表次数：

-- 联合索引 (user_id, status)
-- 没有ICP：先按user_id从索引找到所有行，回表后再过滤status
-- 有ICP：在索引扫描时就过滤status，只对满足条件的行回表

EXPLAIN SELECT * FROM orders
WHERE user_id = 10086 AND status LIKE 'paid%';
-- Extra: Using index condition

ICP的生效条件：联合索引的最左前缀匹配到user_id后，剩余的status条件可以在索引上继续过滤。这比回表后再过滤效率高得多。

MySQL 8.0窗口函数的查询优化

MySQL 8.0引入的窗口函数在报表场景中替代了传统的自连接和变量模拟写法，但不当使用会导致性能问题：

-- 查找每个用户金额最高的3笔订单
-- 优化前：自连接写法，O(n^2)
SELECT o1.*
FROM orders o1
WHERE (
    SELECT COUNT(*)
    FROM orders o2
    WHERE o2.user_id = o1.user_id AND o2.total_amount >= o1.total_amount
) <= 3
ORDER BY o1.user_id, o1.total_amount DESC;

-- 优化后：窗口函数，O(n log n)
SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY total_amount DESC
        ) AS rn
    FROM orders
) ranked
WHERE rn <= 3;

窗口函数的性能关键在于PARTITION BY和ORDER BY的列是否有索引。上述查询如果在user_id和total_amount上建有联合索引，执行效率可提升数倍。

SQL查询优化的系统化方法论

单条SQL优化相对简单，复杂的是系统性地发现和解决性能问题。推荐的工作流程：第一，开启慢查询日志并用pt-query-digest聚合Top N慢SQL；第二，对每条慢SQL执行EXPLAIN分析，识别全表扫描、filesort、临时表等瓶颈；第三，通过添加或调整索引消除瓶颈；第四，验证优化效果并记录到团队知识库。

还需要关注统计信息的准确性。MySQL的执行计划依赖统计信息，如果统计信息过期，优化器可能选择错误的索引。生产环境建议设置自动更新统计信息的频率：

-- 手动更新表统计信息
ANALYZE TABLE orders;

-- 查看统计信息更新时间
SHOW TABLE STATUS LIKE 'orders';

-- MySQL 8.0默认启用innodb_stats_auto_recalc
-- 大表变更超过10%行时自动重新计算统计信息
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

数据库性能调优不是一次性工作，需要随着数据量增长和查询模式变化持续迭代。将慢SQL监控纳入日常运维流程，配合定期执行ANALYZE TABLE保持统计信息准确性，才能确保查询性能长期稳定。