MySQL 8.0慢查询定位与索引优化全流程实战指南

慢查询定位：从开启日志到自动化分析

MySQL性能问题的排查起点永远是慢查询日志。没有慢查询日志，优化就是盲猜。开启慢查询日志并设置合理阈值：

-- 动态开启慢查询日志（无需重启）
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;

-- 持久化配置（my.cnf）
[mysqld]
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

日志开启后，用mysqldumpslow做初步分析：

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

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 按访问次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

对于慢查询量大的系统（日均万条以上），推荐用Percona PMM或pt-query-digest做聚合分析，避免手动翻日志。

EXPLAIN执行计划深度解读

找到慢查询后，用EXPLAIN分析执行计划。MySQL 8.0的EXPLAIN输出包含12个字段，核心关注5个：

type字段：访问类型，从好到差依次为system > const > eq_ref > ref > range > index > ALL。生产环境的目标是把所有查询的type控制在range及以上。出现ALL（全表扫描）必须优化。

key字段：实际使用的索引名。如果为NULL，说明没有使用索引。

rows字段：预估扫描行数。这个值越接近实际越好，差距大说明统计信息不准确，需要ANALYZE TABLE更新。

filtered字段：按条件过滤后剩余行比例。100%表示全部行都满足条件，10%表示只有10%的行满足。type=ref且filtered=10%说明索引选择度差，大量行需要回表过滤。

Extra字段：重点关注Using filesort（额外排序）和Using temporary（临时表），这两个出现意味着查询还有优化空间。

-- 实战案例：一个慢查询的EXPLAIN分析
EXPLAIN SELECT o.id, o.order_no, 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.created_at DESC
LIMIT 20;

-- 输出分析：
-- o表: type=ALL, rows=500000, Extra=Using where; Using filesort
-- u表: type=eq_ref, rows=1
-- 问题：orders全表扫描+filesort

索引设计实战：从单列到复合索引的优化路径

上例的优化路径：

第一步：为WHERE条件加索引

-- 为status和created_at创建复合索引
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 优化后EXPLAIN：
-- o表: type=range, key=idx_status_created, rows=50000, Extra=Using where
-- 全表扫描变为范围扫描，扫描行数从50万降到5万
-- 但Extra中仍有Using filesort，因为ORDER BY的created_at在索引中是第二列

第二步：调整复合索引列顺序

复合索引遵循最左前缀匹配原则。当前索引(status, created_at)中，WHERE status = ‘PAID’ AND created_at >= ‘2026-07-01’两个条件都能命中索引，但ORDER BY created_at DESC无法利用索引排序（因为created_at不是第一列）。调整方案：

-- 如果业务中status的筛选性较强（PAID状态占比较小），保持现有顺序
-- 如果需要完全消除filesort，考虑把排序字段放到最前
ALTER TABLE orders ADD INDEX idx_status_created_v2 (status, created_at DESC);

-- MySQL 8.0支持降序索引，ORDER BY created_at DESC可以直接利用索引排序
-- 优化后EXPLAIN：
-- o表: type=range, key=idx_status_created_v2, Extra=Using where; Using index
-- filesort消除

回表优化：覆盖索引与延迟关联

复合索引idx_status_created_v2解决了扫描范围和排序问题，但查询还需要返回order_no字段，这个字段不在索引中，需要回表查询。当匹配行数较多时（如5万行），回表5万次代价很大。

方案一：覆盖索引

将查询需要的全部字段纳入索引：

ALTER TABLE orders ADD INDEX idx_status_created_cover
(status, created_at DESC, order_no, user_id);

-- 此时EXPLAIN的Extra出现Using index，表示无需回表
-- 但这个索引包含4个字段，维护成本高，且只适用这一条查询

方案二：延迟关联（推荐）

先通过子查询用覆盖索引获取主键，再用主键关联获取完整数据：

SELECT o.id, o.order_no, u.name
FROM orders o
JOIN (
    SELECT id FROM orders
    WHERE status = 'PAID' AND created_at >= '2026-07-01'
    ORDER BY created_at DESC
    LIMIT 20
) t ON o.id = t.id
JOIN users u ON o.user_id = u.id;

-- 子查询走覆盖索引只返回20个id，外层关联只需回表20次
-- 从回表5万次降到回表20次，性能提升1000倍以上

统计信息与执行计划稳定性

MySQL优化器基于统计信息选择执行计划。统计信息不准确会导致执行计划突变——昨天还快的查询今天突然慢了，原因就是统计信息更新后优化器选择了不同的索引。

关键操作：

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

-- 查看统计信息更新时间
SELECT table_name, last_analyzed
FROM mysql.innodb_table_stats
WHERE database_name = 'production';

-- MySQL 8.0自动更新统计信息的触发条件：
-- 表数据变化超过16%（innodb_stats_auto_recalc默认值）
-- 对于大表，自动更新可能延迟，建议在业务低峰期手动执行

-- 通过Optimizer Hint强制使用指定索引（极端场景使用）
SELECT /*+ INDEX(o idx_status_created_v2) */ o.id, o.order_no
FROM orders o
WHERE o.status = 'PAID' AND o.created_at >= '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

线上索引变更的安全操作

大表加索引是高风险操作。直接ALTER TABLE会锁表，线上环境不可接受。MySQL 8.0的Online DDL可以在加索引期间允许DML操作，但仍有注意事项：

-- 安全的在线加索引方式
ALTER TABLE orders ADD INDEX idx_status_created_v2 (status, created_at DESC),
ALGORITHM=INPLACE, LOCK=NONE;

-- ALGORITHM=INPLACE: 不拷贝全表数据，只构建索引
-- LOCK=NONE: 允许并发DML
-- 但创建索引仍需扫描全表构建B+Tree，大表（千万行级）可能耗时分钟级

-- 更安全的做法：使用pt-online-schema-change工具
pt-online-schema-change \
  --alter "ADD INDEX idx_status_created_v2(status, created_at DESC)" \
  --host=127.0.0.1 --port=3306 --user=admin --password=xxx \
  D=production,t=orders \
  --max-load=Threads_running=100 \
  --critical-load=Threads_running=200 \
  --chunk-size=1000 \
  --execute

pt-osc通过创建影子表+增量同步+rename切换的方式，在全过程中保持原表可读写。–max-load和–critical-load参数控制同步速率，避免对线上造成压力。MySQL性能调优是系统工程：先定位慢查询（慢日志+EXPLAIN），再逐层优化（索引到SQL到架构），最后保障稳定性（统计信息+安全变更）。每一层都有对应的工具和方法论，按顺序操作比盲目调参效率高得多。