慢查询日志配置与自动化分析

性能调优的第一步是找到瓶颈。MySQL慢查询日志是最直接的性能诊断工具，但默认未开启。

-- 开启慢查询日志
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';

-- 确认生效
SHOW VARIABLES LIKE 'slow_query%';

生产环境不建议长期开启全量慢查询日志，会影响IO。建议在排查窗口期临时开启，用pt-query-digest分析。

# 用Percona Toolkit分析慢查询
pt-query-digest /var/log/mysql/slow.log --limit 95% --outliers F=3,S=1M

# 输出样例：
# Rank Query ID       Response time  Calls  R/Call  V/M
# ==== ============= ============== ====== ======= ====
#    1 0x5A1B2C3D...  1254.5 62.3%   3451   0.364  0.12
#    2 0x7E8F9A0B...   432.1 21.5%    892   0.484  0.05

Rank 1的查询贡献了62.3%的响应时间，这就是优化重点。拿到Query ID后回slow.log找原始SQL。

EXPLAIN执行计划深度解读

拿到慢SQL后，用EXPLAIN分析执行计划。很多人只看type列，这不够，每一列都有含义。

EXPLAIN FORMAT=JSON SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at BETWEEN '2026-07-01' AND '2026-07-31'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;

关键列解读：

type：访问类型，从优到差：system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描，必须优化。

key：实际使用的索引。为NULL说明没走索引。

rows：预估扫描行数。这个值×filtered百分比才是实际返回行数的参考。

Extra：重点关注以下几种：
– Using filesort：排序未走索引，需要额外排序操作
– Using temporary：使用了临时表，常见于GROUP BY无索引场景
– Using index condition：ICP下推，是好事
– Using where; Using join buffer：关联查询没有索引，依赖缓冲区处理

索引设计策略：避免无效索引

不是加了索引就能加速。以下几种情况索引无效：

1. 对索引列使用函数或表达式

-- 无效：对created_at使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- 有效：范围查询
SELECT * FROM orders WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24';

2. 联合索引的最左前缀原则

-- 索引：idx_user_status_created (user_id, status, created_at)
-- 能走索引
WHERE user_id = 1
WHERE user_id = 1 AND status = 'PAID'
WHERE user_id = 1 AND status = 'PAID' AND created_at > '2026-07-01'

-- 不能走索引（跳过了user_id）
WHERE status = 'PAID'
WHERE status = 'PAID' AND created_at > '2026-07-01'

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

-- 如果user_id是varchar类型
-- 无效：传入整数，MySQL做隐式转换
SELECT * FROM users WHERE user_id = 12345;
-- 有效：传入字符串
SELECT * FROM users WHERE user_id = '12345';

分页查询优化：告别OFFSET

深度分页（OFFSET 100000 LIMIT 20）的问题是MySQL要扫描前100020行然后丢弃前100000行。

-- 慢：传统OFFSET分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 快：游标分页（适合连续翻页场景）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 折中方案：子查询延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

子查询方案只走覆盖索引查id，再用id回表取完整数据，IO量大幅减少。

线上索引变更的安全生产流程

线上加索引会锁表（MySQL 5.6之前）或消耗大量IO（Online DDL期间）。安全做法：

# 1. 使用pt-online-schema-change无锁加索引
pt-online-schema-change   --alter "ADD INDEX idx_status_created (status, created_at)"   --execute   --max-load=Threads_running=100   --critical-load=Threads_running=200   --chunk-size=1000   D=production,t=orders

# 2. 或使用gh-ost（GitHub的方案）
gh-ost   --user=root --password=xxx   --host=127.0.0.1   --database=production   --table=orders   --alter="ADD INDEX idx_status_created (status, created_at)"   --allow-on-master   --chunk-size=1000   --execute

两个工具的核心原理相同：创建影子表 → 增量同步数据 → 同步完成后原子切换。全程不锁原表。建议在低峰期执行，同时设置max-load阈值——如果主库压力超过阈值自动暂停。