MySQL 8.0 SQL查询优化实战：慢查询定位与执行计划深度分析

MySQL性能调优中，SQL查询优化是最直接、收益最高的手段。相比硬件扩容和参数调整，一条劣质SQL的优化效果可能相当于增加数倍服务器资源。本文从慢查询日志定位、EXPLAIN执行计划解读到索引优化策略，提供一套完整的SQL查询优化操作流程。

慢查询日志配置与采集方案

慢查询日志是SQL优化的入口，记录执行时间超过阈值的SQL语句。配置方式：

-- 动态开启（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 阈值1秒
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

生产环境建议阈值设置为0.5秒或更低，避免遗漏。慢日志文件增长快，需配合logrotate定期轮转。

使用mysqldumpslow快速汇总分析：

# 按查询时间排序，取前20条
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 按查询次数排序
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

对于更精细的分析，推荐Percona的pt-query-digest，它能输出每条SQL的执行次数、平均时间、95分位时间和完整的执行计划建议。

EXPLAIN执行计划关键字段解读

拿到慢SQL后，第一步是EXPLAIN分析：

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

关键字段含义：

type：访问类型，从优到差依次为system > const > eq_ref > ref > range > index > ALL。生产环境要求至少达到ref级别，ALL（全表扫描）必须优化。

key：实际使用的索引名。为NULL表示未走索引。

rows：预估扫描行数。这个值是优化器的估算值，可能偏差较大，需结合Handler_read%状态变量验证。

Extra：额外信息，重点关注以下值：

• Using index：覆盖索引，性能最优
• Using where：在存储引擎返回数据后，Server层再过滤
• Using filesort：额外排序操作，大数据量时严重拖慢查询
• Using temporary：使用了临时表，通常出现在GROUP BY无索引场景
• Using index condition：ICP下推，减少了回表次数

索引设计原则与常见反模式

索引是最核心的优化手段，但索引设计不当反而降低写入性能：

最左前缀原则：联合索引(a, b, c)能支持a、(a,b)、(a,b,c)的查询条件，但无法支持(b,c)的查询。索引列顺序应按照区分度从高到低排列。

覆盖索引：查询的列全部包含在索引中，无需回表。对高频查询场景效果显著：

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_status_created_amount (status, created_at, amount);

-- 查询只涉及这三列时，Extra会显示Using index
SELECT status, created_at, amount FROM orders WHERE status = 'PAID';

索引失效的常见场景：

1. 对索引列使用函数：WHERE YEAR(created_at) = 2026 → 改为范围查询WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

2. 隐式类型转换：WHERE varchar_col = 123，MySQL会将varchar转换为数字比较，导致索引失效 → 保持类型一致WHERE varchar_col = '123'

3. LIKE以通配符开头：WHERE name LIKE '%张' → 考虑全文索引或ES

4. OR条件跨越不同索引：WHERE a = 1 OR b = 2 → 拆分为UNION ALL或创建联合索引

分库分表方案下的SQL优化差异

分库分表后SQL优化面临新问题：跨片查询性能急剧下降；JOIN操作受限；排序和分页需要合并处理。

跨片分页查询优化：

-- 错误做法：各分片分别执行 LIMIT 100, 10 然后合并
-- 正确做法：各分片查询 LIMIT 0, 110，在中间件层合并排序后取第100-110条

-- 更优方案：使用游标分页代替偏移分页
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 10;

跨片JOIN替代方案：冗余字段（将关联数据冗余到主表中）、宽表设计（预先JOIN好存入ES或ClickHouse）、应用层组装（先查主表ID列表，再批量查关联表）。

SQL查询优化工具链与自动化实践

手动EXPLAIN效率低，生产环境推荐工具链配合：

MySQL Sys Schema：内置视图快速定位问题SQL：SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

Performance Schema：开启events_statements_history_long消费线程，记录所有SQL执行历史，配合sys.schema_index_statistics分析索引使用率，清理无用索引。

自动化巡检：编写脚本每日收集Top20慢SQL，对比前日变化，新增慢SQL自动触发告警推送到企业IM。代码示例：

#!/bin/bash
# 每日慢SQL报告
REPORT=$(pt-query-digest --limit 20 --since 24h /var/log/mysql/slow.log)
echo "$REPORT" | curl -X POST 'https://hooks.example.com/notify' \
  -H 'Content-Type: application/json' \
  -d "{\"text\": \"$(echo "$REPORT" | head -50 | sed 's/"/\\"/g')\"}"

数据库高可用架构下，慢SQL优化需要在从库上验证效果，确认无副作用后再上线主库。MySQL 8.0的resource_group功能可以将优化前的查询路由到低优先级资源组，降低对在线业务的影响。