MySQL 8.0慢查询诊断与索引优化全链路实战：从日志到执行计划

慢查询不是看一眼EXPLAIN就能解决的

MySQL慢查询诊断的常见误区：拿到一条慢SQL，跑一下EXPLAIN，看到type=ALL就加索引，看到Using filesort就改排序字段。这种头痛医头的做法解决不了系统性问题。真正的慢查询治理需要全链路视角——从慢查询日志捕获、执行计划分析、索引策略设计到查询重写，每一步都有具体的判断标准和技术手段。

慢查询日志的正确打开方式

慢查询日志是诊断的起点，但默认配置捕获的阈值太粗。生产环境中应该同时开启慢查询日志和性能采样：

-- 慢查询日志配置
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 500ms，根据业务SLA调整
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_row_limit = 100;  -- 排除扫描行数少的查询
SET GLOBAL log_slow_admin_statements = ON;

-- 性能采样——Performance Schema
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_statements_history_long');

慢查询日志文件需要定期轮转，避免单个文件过大影响磁盘IO：

# mysqladmin轮转慢查询日志
mysqladmin -u root -p flush-logs slow
# 配合logrotate定时执行
# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    postrotate
        mysqladmin -u root flush-logs slow
    endscript
}

用pt-query-digest做慢查询聚合分析

原始慢查询日志可读性差，同一个查询模板可能有数百种参数组合。pt-query-digest按查询指纹聚合，快速定位TOP N慢查询：

# 分析慢查询日志，输出TOP 20
pt-query-digest /var/log/mysql/slow.log \
  --limit 20 \
  --order-by Query_time:sum \
  --output report \
  > /tmp/slow_report.txt

# 输出示例（关键字段解读）：
# Rank Query ID         Response time  Calls  R/Call  V/M
# ==== ================ ============== ====== ======= ====
# 1    0x5A8E3B2C1D...  2345.0000    1230   1.9098  0.12
#      SELECT * FROM orders WHERE user_id=? AND status=? AND created_at>?
#      -- 这个查询被调用1230次，总耗时2345秒
#      -- V/M=0.12表示执行时间方差较小，属于持续性问题

# 按查询指纹分组后，进一步分析执行计划
pt-query-digest /var/log/mysql/slow.log \
  --filter '$event->{arg} =~ /FROM orders/' \
  --limit 5 \
  --print

EXPLAIN执行计划深度解读

EXPLAIN输出12个字段，每个字段都包含具体的性能判断标准，而不是简单看type值：

type字段——访问类型，从好到差依次为：system > const > eq_ref > ref > range > index > ALL。实际业务中，单行查询至少要达到ref级别，范围查询至少达到range级别。index看起来比ALL好，但本质上仍然是全扫描，只是扫描索引而非数据文件。

Extra字段——额外信息是性能问题的富矿：

-- EXPLAIN分析实战
EXPLAIN FORMAT=JSON
SELECT o.id, o.order_no, u.name, o.total_amount
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.total_amount DESC
LIMIT 20;

-- JSON格式输出包含更详细的成本估算
--重点关注：
-- 1. "filtered": 存储引擎返回行数中被Server层过滤后剩余的比例
--    低于10%说明索引选择性差
-- 2. "cost_info": MySQL优化器的成本估算
--    对比不同索引方案的cost差异
-- 3. "attached_condition": 下推到存储引擎的条件
--    没有下推的条件会在Server层过滤，效率低

索引设计策略：从选择性到覆盖索引

索引设计不是”给WHERE条件加索引”这么简单。需要综合考虑选择性、覆盖度、排序优化和写入成本。

选择性优先——索引列的选择性等于不同值数量除以总行数。选择性越接近1，索引过滤效果越好。经验上选择性低于0.1的列不适合做索引前缀：

-- 计算列选择性
SELECT
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity,
  COUNT(DISTINCT created_at) / COUNT(*) AS created_selectivity
FROM orders;

-- 结果示例：
-- status_selectivity: 0.0003  (差，只有几种状态值)
-- user_selectivity: 0.85      (好，用户ID分散度高)
-- created_selectivity: 0.92   (好，时间分散度高)

-- 联合索引列顺序：选择性高的列放前面
-- 正确：(user_id, status, created_at)
-- 错误：(status, user_id, created_at) -- status在前会把大量行锁定

覆盖索引消除回表——当查询只需要索引列数据时，可以直接从索引中获取结果，无需回表读取聚簇索引。这在高并发查询场景下效果显著：

-- 原始查询：需要回表
SELECT id, order_no, total_amount
FROM orders
WHERE user_id = 12345
  AND status = 'PAID';

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

-- 现在EXPLAIN的Extra显示Using index——无需回表
-- 查询性能从50ms降到2ms，QPS提升25倍

查询重写：把优化器做不到的优化手动做

MySQL优化器不会重写所有低效的SQL模式。以下几种常见的低效写法需要手动优化：

子查询转JOIN

-- 低效：子查询产生临时表
SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE region = '华东'
);

-- 高效：改写为JOIN
SELECT o.*
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.region = '华东';

-- 如果users表很大，还可以用EXISTS替代IN
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM users u
  WHERE u.id = o.user_id AND u.region = '华东'
);

分页深翻页优化

-- 低效：深翻页扫描大量行后丢弃
SELECT * FROM orders
ORDER BY id DESC
LIMIT 100000, 20;

-- 高效：游标分页，利用索引定位起始点
SELECT * FROM orders
WHERE id < last_seen_id  -- 上一页最后一条的ID
ORDER BY id DESC
LIMIT 20;

-- 如果必须用OFFSET，延迟关联减少回表
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders
  ORDER BY id DESC
  LIMIT 100000, 20
) tmp ON o.id = tmp.id;

隐式类型转换导致索引失效

-- 索引失效：user_id是VARCHAR，传入整型参数
SELECT * FROM orders WHERE user_id = 12345;
-- MySQL会将user_id列转为数字比较，导致索引无法使用

-- 正确：参数类型与列类型一致
SELECT * FROM orders WHERE user_id = '12345';

-- 检查是否存在隐式转换
-- Performance Schema可以捕获类型不匹配的查询
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%user_id = %'
ORDER BY SUM_TIMER_WAIT DESC;

InnoDB Buffer Pool调优

索引优化解决的是单个查询的效率问题，Buffer Pool调优解决的是整体IO效率问题。Buffer Pool命中率直接决定数据库的吞吐量上限：

-- Buffer Pool关键指标
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 核心指标解读：
-- Innodb_buffer_pool_read_requests: 逻辑读总次数
-- Innodb_buffer_pool_reads: 磁盘读次数
-- 命中率 = 1 - (reads / read_requests)
-- 目标值：> 99%

-- 如果命中率低于99%，需要增大Buffer Pool
-- 配置建议：分配物理内存的60%-75%给Buffer Pool
-- 32GB内存的服务器：24GB给Buffer Pool

SET GLOBAL innodb_buffer_pool_size = 24 * 1024 * 1024 * 1024;

-- 多Buffer Pool实例减少锁争用
SET GLOBAL innodb_buffer_pool_instances = 8;  -- 每个3GB

Buffer Pool预热也很重要——MySQL重启后Buffer Pool为空，所有查询都走磁盘，性能断崖式下跌。8.0+支持关闭时转储Buffer Pool状态、启动时预热：

-- 配置Buffer Pool预热
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;

-- 手动触发转储和加载
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- 查看预热进度
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

慢查询治理是一个持续过程：定期分析慢查询日志→按影响范围排序→EXPLAIN深度分析→索引设计或查询重写→验证效果→更新基线。每轮迭代都会压缩慢查询的生存空间，把系统整体响应时间推向更低的水平。