MySQL 8.0慢SQL定位与执行计划深度分析实战

慢SQL是数据库性能的头号杀手

生产环境中80%的数据库性能问题由慢SQL引起。一条全表扫描的SQL可能占用大量IO和CPU资源，拖垮整个实例。系统化的慢SQL定位和优化流程，是DBA和后端开发人员的必备技能。MySQL 8.0提供了完善的慢查询日志和EXPLAIN工具链，掌握这些工具能把排查效率提升数倍。

慢查询日志的配置与分析

开启慢查询日志是定位慢SQL的第一步：

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;    -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引也记录

在线配置重启后失效，建议写入my.cnf持久化：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

用mysqldumpslow分析慢日志，按查询时间排序取Top 10：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

也可以用pt-query-digest做更深入的分析，输出每个SQL的执行次数、平均耗时、返回行数等。

EXPLAIN执行计划关键字段解读

EXPLAIN是SQL优化的核心工具，每个字段都包含关键信息：

EXPLAIN SELECT u.id, u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 AND o.created_at > '2026-01-01';

type字段（访问类型，从优到差）：

system > const > eq_ref > ref > range > index > ALL

看到ALL就是全表扫描，必须优化。index是全索引扫描，虽然比ALL好但仍然扫描整棵索引树。

key字段：实际使用的索引。如果是NULL说明没走索引。

rows字段：预估扫描行数。这个值越大，SQL越慢。优化的目标就是让这个值最小化。

Extra字段关键信息：

– Using index：覆盖索引，无需回表，性能最佳。

– Using where：在存储引擎返回数据后，Server层再过滤。如果扫描行数远大于返回行数，说明过滤效率低。

– Using temporary：使用临时表，常见于GROUP BY和DISTINCT。需要优化。

– Using filesort：文件排序，未使用索引排序。大数据量时严重影响性能。

索引优化实战案例

案例1：联合索引的最左前缀原则

-- 表上有联合索引 idx_status_created(status, created_at)
-- 违反最左前缀，无法使用索引
SELECT * FROM orders WHERE created_at > '2026-01-01';

-- 符合最左前缀
SELECT * FROM orders WHERE status = 1 AND created_at > '2026-01-01';

-- 只有status也能走索引（最左前缀）
SELECT * FROM orders WHERE status = 1;

案例2：索引列上的函数导致索引失效

-- 对索引列使用函数，索引失效
SELECT * FROM users WHERE DATE(created_at) = '2026-07-23';

-- 改写为范围查询
SELECT * FROM users 
WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24';

案例3：隐式类型转换

-- phone是VARCHAR类型
-- 传入整数，MySQL会隐式转换phone为数字，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 传入字符串，走索引
SELECT * FROM users WHERE phone = '13800138000';

覆盖索引消除回表

当查询的所有字段都包含在索引中时，InnoDB直接从索引返回数据，无需回表查主键索引：

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

-- 查询只需要这三个字段，直接走覆盖索引
SELECT user_id, status, order_no FROM orders WHERE user_id = 100;

EXPLAIN中Extra显示Using index就表示命中了覆盖索引。对于高频查询场景，覆盖索引能把查询时间从几十毫秒降到1毫秒以内。

JOIN查询优化

多表JOIN的性能取决于驱动表的选择和索引配置：

-- 小表驱动大表，确保JOIN字段有索引
EXPLAIN SELECT /*+ STRAIGHT_JOIN */ o.*, u.name
FROM orders o          -- 驱动表，数据量小
STRAIGHT_JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at > '2026-01-01';

-- 确保 o.user_id 和 u.id 上都有索引
-- 驱动表的WHERE条件过滤后行数越少越好

STRAIGHT_JOIN强制MySQL按指定顺序执行JOIN，用于纠正优化器选错驱动表的情况。判断是否需要使用：先看EXPLAIN的rows估算，如果驱动表行数远大于被驱动表，就需要STRAIGHT_JOIN。

MySQL 8.0新特性：不可见索引与降序索引

不可见索引：在索引上设置invisible，优化器不会使用它，但索引结构仍然维护。用于安全验证索引是否可以删除：

-- 设为不可见
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- 观察一段时间，如果没有性能退化，再删除
ALTER TABLE orders DROP INDEX idx_status;

-- 如果发现性能下降，立即恢复
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

降序索引：MySQL 8.0真正支持降序索引，不再只是语法糖：

-- 创建降序索引
ALTER TABLE orders ADD INDEX idx_created_desc (created_at DESC);

-- 查询时匹配降序，避免filesort
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;

监控与持续优化

慢SQL优化不是一次性工作，需要建立持续监控机制：

-- 查看当前正在执行的SQL
SELECT * FROM information_schema.PROCESSLIST 
WHERE command = 'Query' AND time > 5;

-- 查看索引使用统计
SELECT * FROM sys.schema_unused_indexes;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

定期审查sys.schema_unused_indexes，超过30天未被使用的索引可以考虑删除——索引不仅占存储空间，还降低写入性能。

MySQL 8.0的Performance Schema也提供了详细的SQL执行统计：

-- 按执行时间排序的SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

掌握慢日志定位、EXPLAIN分析、索引优化三板斧，再配合MySQL 8.0的新特性工具，绝大部分数据库性能问题都能系统化地解决。