MySQL 8.0慢查询定位到索引优化的全流程实战：从pt-query-digest到覆盖索引设计

MySQL慢查询是数据库性能问题的首要表现，但定位到慢查询只是第一步，从根因分析到索引设计再到执行计划验证，才是完整的调优闭环。很多DBA看到慢查询就加索引，结果索引膨胀、写入性能下降、查询反而更慢。本文给出从慢查询定位到索引优化的系统方法论，避免常见的索引误操作。

慢查询采集与分析：pt-query-digest实战

MySQL慢查询日志是调优的原始数据来源。开启slow_query_log后，执行时间超过long_query_time的SQL会被记录。pt-query-digest是Percona工具链中最常用的分析工具，能将海量慢日志聚合为指纹级别的统计报告。

# 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- 超过500ms记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引的查询也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# pt-query-digest分析
pt-query-digest /var/log/mysql/slow.log \
    --since "2026-08-04 00:00:00" \
    --until "2026-08-04 23:59:59" \
    --limit 20 \
    --order-by Query_time:sum

# 输出关键列解读
# Rank: 排名
# Query ID: 查询指纹ID
# Response time: 总响应时间及占比
# Calls: 执行次数
# R/Call: 平均每次执行时间
# V/M: 方差/均值比，越大说明查询时间波动越大

重点关注Response time占比最高的前5条查询，它们是优化收益最大的目标。R/Call高但Calls低，可能是偶发性复杂查询；Calls高但R/Call低，可能是高频短查询累积。

EXPLAIN执行计划解读：读懂MySQL的查询路线图

EXPLAIN输出中，最关键的字段是type、key、rows和Extra。type从优到差：system > const > eq_ref > ref > range > index > ALL。key显示实际使用的索引。rows是预估扫描行数。Extra中的Using filesort和Using temporary是需要重点消除的性能杀手。

# 执行计划分析
EXPLAIN ANALYZE
SELECT o.order_id, o.total_amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-07-01'
  AND o.status = 'paid'
ORDER BY o.total_amount DESC
LIMIT 20;

/* 输出分析要点：
1. type=ALL → 全表扫描，需要加索引
2. key=NULL → 未使用任何索引
3. rows=500000 → 预估扫描50万行
4. Extra=Using filesort → 额外排序操作
5. Extra=Using temporary → 使用临时表
*/

索引设计的黄金法则：最左前缀与覆盖索引

复合索引的列顺序决定了索引的可用范围。最左前缀原则：查询条件必须从索引最左列开始匹配，跳过左侧列则索引失效。等值过滤列放前面，范围过滤列放后面，排序列根据查询模式决定位置。

-- 订单表场景：按时间范围+状态过滤+金额排序
-- 查询模式：WHERE created_at BETWEEN ? AND ? AND status = ? ORDER BY total_amount DESC

-- ❌ 错误索引：排序列在范围列之后，filesort无法消除
CREATE INDEX idx_wrong ON orders(created_at, status, total_amount);

-- ✅ 正确索引：等值条件列前置，排序紧随其后
CREATE INDEX idx_optimal ON orders(status, created_at, total_amount);
/*
执行计划变化：
type: range
key: idx_optimal
rows: 2000（从50万降至2000）
Extra: Using index condition（filesort消除）
*/

-- 覆盖索引：查询列全部包含在索引中，无需回表
-- 如果只查order_id和total_amount
SELECT order_id, total_amount FROM orders
WHERE status = 'paid' AND created_at >= '2026-07-01';
-- 覆盖索引：
CREATE INDEX idx_cover ON orders(status, created_at, order_id, total_amount);
/*
Extra: Using index → 完全从索引读取数据，零回表
*/

索引失效的常见陷阱

以下场景会导致索引无法使用：对索引列使用函数（WHERE YEAR(created_at) = 2026，应改为WHERE created_at >= ‘2026-01-01’ AND created_at < ‘2027-01-01’）；隐式类型转换（varchar列用整数查询，WHERE order_no = 123456应改为WHERE order_no = ‘123456’）；OR条件中部分列无索引（导致全表扫描，可拆分为UNION ALL或全部列加索引）；LIKE以通配符开头（WHERE name LIKE ‘%abc’无法走索引，考虑全文索引或ES）。

-- 函数导致索引失效的改造
-- ❌
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-04';
-- ✅
SELECT * FROM orders
WHERE created_at >= '2026-08-04 00:00:00'
  AND created_at < '2026-08-05 00:00:00';

-- 隐式类型转换陷阱
-- order_no是varchar类型
-- ❌ MySQL会将order_no列转为数字再比较
SELECT * FROM orders WHERE order_no = 123456;
-- ✅ 显式传字符串
SELECT * FROM orders WHERE order_no = '123456';

-- OR改写为UNION ALL
-- ❌
SELECT * FROM orders WHERE user_id = 1001 OR product_id = 2001;
-- ✅
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE product_id = 2001 AND user_id != 1001;

索引维护与监控：避免索引膨胀

索引不是越多越好，每个索引增加写入开销（INSERT/UPDATE/DELETE需同步维护索引）。定期使用sys.schema_unused_indexes视图清理无用索引，用sys.schema_index_statistics监控索引使用频率。

-- 查找冗余索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'production_db';

-- 查看索引统计信息
SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'production_db'
ORDER BY rows_selected DESC
LIMIT 20;

-- 查看索引大小
SELECT table_name, index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'production_db'
  AND stat_name = 'size'
ORDER BY size_mb DESC
LIMIT 10;

口袋网数据库团队在索引治理中遵循”一增一减”原则：每新增一个索引，必须同时清理一个无用索引，控制单表索引数量不超过6个。索引优化不是加索引，而是设计正确的索引。