MySQL 8.0慢SQL诊断与索引优化实战指南

线上MySQL出现慢查询是运维和开发每天都会面对的问题。一条慢SQL可能拖垮整个数据库实例，影响所有依赖该实例的业务。诊断和优化慢SQL不是玄学，而是一套有章可循的方法论：定位→分析→优化→验证。本文完整演示MySQL 8.0慢SQL的诊断流程和索引优化的实战手法。

慢查询日志配置与自动采集

慢查询日志是诊断的入口。MySQL 8.0默认未开启，需要手动配置：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;        -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未用索引也记录
SET GLOBAL min_examined_row_limit = 100;         -- 扫描行<100不记录

-- 日志输出到文件（性能更好）或表（方便查询）
SET GLOBAL log_output = 'FILE,TABLE';

-- 查询慢日志表
SELECT * FROM mysql.slow_log
WHERE start_time > NOW() - INTERVAL 1 HOUR
ORDER BY query_time DESC
LIMIT 10;

生产环境推荐用Percona的pt-query-digest工具定期分析慢日志，生成Top N报告：

# 每小时分析一次慢日志
pt-query-digest /var/lib/mysql/mysql-slow.log \
  --since "1h" \
  --limit "95%" \
  --order-by Query_time:sum \
  --output slowlog-report.txt

# 报告中重点关注：
# 1. Rank - 按总耗时排序的查询排名
# 2. Response time - 该查询占总慢查询时间的百分比
# 3. Rows examine/sent - 扫描行数与返回行数的比值（越大越低效）
# 4. Query template - 参数化后的SQL模板，用于定位同类查询

EXPLAIN执行计划深度解读

EXPLAIN是分析单条SQL执行路径的标准工具。MySQL 8.0的EXPLAIN FORMAT=TREE和EXPLAIN ANALYZE提供更直观的输出：

-- 传统EXPLAIN
EXPLAIN SELECT o.order_id, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.create_time > '2026-07-01'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;

-- MySQL 8.0: 带实际执行耗时
EXPLAIN ANALYZE
SELECT o.order_id, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.create_time > '2026-07-01'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;

EXPLAIN输出的关键字段判读标准：

EXPLAIN字段诊断清单 = {
    "type": {
        "system/const": "最优，单行匹配",
        "eq_ref": "优，唯一索引关联",
        "ref": "良，非唯一索引匹配",
        "range": "可接受，索引范围扫描",
        "index": "差，全索引扫描",
        "ALL": "最差，全表扫描，必须优化",
    },
    "key": "实际使用的索引，NULL表示未用索引",
    "rows": "预估扫描行数，越少越好",
    "filtered": "过滤比例，100%最佳，低于10%需关注",
    "Extra": {
        "Using index": "好，覆盖索引，无需回表",
        "Using where": "需在Server层过滤",
        "Using temporary": "差，使用了临时表",
        "Using filesort": "差，额外排序",
        "Using index condition": "好，ICP下推优化生效",
    }
}

索引优化实战：从单列到联合索引

索引优化的核心原则：高选择性列在前，查询条件列全覆盖，避免回表查询。

场景一：联合索引的列顺序

-- 查询: WHERE status = 'PAID' AND create_time > '2026-07-01'
-- orders表有1000万行，status='PAID'约300万行

-- 错误索引：create_time在前
ALTER TABLE orders ADD INDEX idx_create_time_status (create_time, status);
-- 问题：先按create_time范围扫描，再在结果中过滤status
-- MySQL只能用到create_time的前缀，status走不了索引

-- 正确索引：等值条件在前，范围条件在后
ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);
-- 优化：先精确定位status='PAID'，再在B+树叶子节点顺序扫描create_time
-- 索引扫描行数从300万降到30万

场景二：覆盖索引消除回表

-- 查询只需要order_id和amount两个字段
SELECT order_id, amount FROM orders
WHERE status = 'PAID' AND create_time > '2026-07-01';

-- 覆盖索引：把SELECT的列也包含进索引
ALTER TABLE orders ADD INDEX idx_cover (status, create_time, order_id, amount);

-- EXPLAIN Extra显示 "Using index" 表示完全走索引，无需回表
-- 性能提升：从磁盘随机IO变成索引顺序读取，QPS提升3-5倍

场景三：ORDER BY优化

-- 排序查询
SELECT order_id, amount FROM orders
WHERE status = 'PAID'
ORDER BY amount DESC
LIMIT 20;

-- 问题：status索引过滤后，amount无序，需要filesort
-- 方案1：如果status值域较小，用(status, amount)联合索引
ALTER TABLE orders ADD INDEX idx_status_amount (status, amount);
-- 索引按status分组、amount有序，可直接取前20条，无需排序

-- 方案2：MySQL 8.0降序索引
ALTER TABLE orders ADD INDEX idx_status_amount_desc (status, amount DESC);

MySQL 8.0新特性：不可见索引与索引跳扫

不可见索引（Invisible Index）是MySQL 8.0的运维利器。删除索引前先设为不可见，观察一周无影响再真正删除，避免误删导致性能事故：

-- 将索引设为不可见（优化器不再使用，但索引数据仍在维护）
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;

-- 观察期（1-2周），如果无慢查询飙升，则安全删除
ALTER TABLE orders DROP INDEX idx_old;

-- 如果出现问题，秒级恢复
ALTER TABLE orders ALTER INDEX idx_old VISIBLE;

索引跳扫（Skip Scan）在MySQL 8.0.13+版本支持。当联合索引第一列区分度低、查询未指定第一列条件时，优化器会按第一列的不同值分别执行范围扫描：

-- 联合索引 (region, create_time)
-- region只有5个值：EAST/WEST/NORTH/SOUTH/CENTER
-- 查询未指定region
SELECT * FROM orders WHERE create_time > '2026-07-01';

-- MySQL 8.0自动执行：
-- region=EAST AND create_time>'2026-07-01'
-- UNION region=WEST AND create_time>'2026-07-01'
-- ... 共5次内部范围扫描
-- Extra显示 "Using index for skip scan"

线上慢SQL治理流程

单条SQL的优化是点，系统化的治理是面。完整的慢SQL治理流程：

慢SQL治理SOP:

1. 采集: 慢日志 + pt-query-digest -> Top20慢查询清单
2. 分类:
   - 全表扫描型 -> 加索引 / 改写SQL
   - 索引失效型 -> 检查隐式转换、函数调用、OR条件
   - 锁等待型 -> 检查事务粒度、隔离级别
   - 临时表/filesort型 -> 优化GROUP BY/ORDER BY索引
3. 优化: 先用不可见索引验证，再正式上线
4. 监控: Prometheus + Grafana看板跟踪QPS/RT/慢查询数趋势
5. 回归: 每周Review慢日志，防止劣化

常见索引失效场景速查：
- WHERE YEAR(create_time) = 2026  -> 函数破坏索引，改范围查询
- WHERE varchar_col = 123          -> 隐式类型转换
- WHERE col LIKE '%keyword'       -> 前缀通配符
- WHERE a = 1 OR b = 2            -> OR条件拆分或用UNION
- WHERE col IN (SELECT ...)       -> 子查询改JOIN

慢SQL治理是持续工程，不是一次性任务。建立采集→分析→优化的闭环，配合自动化告警和定期Review，才能把数据库性能稳定在合理水位。