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';

生产环境建议long_query_time设为0.5-1秒。设太低会记录大量噪音，设太高会漏掉重要问题。

用pt-query-digest分析Top慢查询：

pt-query-digest /var/log/mysql/slow.log --limit 10

# 输出示例
# Rank Query ID           Response time  Calls
# ==== ================== ============== =====
# 1    0x3F8E1A2B4C5D6E7F  120.3s   45%   328
# 2    0x8A9B0C1D2E3F4A5B   67.2s   25%   1025

按总响应时间排序，优先优化占时最多的查询。一条高频慢查询比十条低频慢查询影响更大。

第二步：执行计划深度解读

拿到慢SQL后，EXPLAIN是分析起点：

EXPLAIN ANALYZE
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 50;

关键列含义：

• type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描
• key：实际使用的索引。如果为NULL说明没走索引
• rows：预估扫描行数，越大越慢
• Extra：额外信息。出现Using filesort或Using temporary要重点关注

常见问题与处理：

第三步：索引策略选择

索引不是越多越好。每个索引占用磁盘空间，降低写入速度，优化器选择索引也有开销。

场景1：等值查询 + 排序

-- 原始SQL
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY created_at DESC LIMIT 50;

-- 索引设计：把等值条件放前面，排序字段放后面
CREATE INDEX idx_status_created ON orders(status, created_at DESC);

-- 这样查询可以走索引查找+索引有序，避免filesort

场景2：范围查询后的列无法走索引

-- 原始SQL
SELECT * FROM orders WHERE status = 'PENDING' AND amount > 1000 AND customer_id = 5;

-- 错误索引：范围条件amount放中间，customer_id无法走索引
CREATE INDEX idx_status_amount_cid ON orders(status, amount, customer_id);

-- 正确索引：等值条件放前面，范围条件放最后
CREATE INDEX idx_status_cid_amount ON orders(status, customer_id, amount);

最左前缀原则：联合索引中，范围条件（>, <, BETWEEN, LIKE前缀）右侧的列无法利用索引。

场景3：覆盖索引避免回表

-- 原始SQL
SELECT id, status, amount FROM orders WHERE status = 'PENDING';

-- 覆盖索引：查询的所有列都在索引中，不需要回表
CREATE INDEX idx_cover_status ON orders(status, amount, id);

第四步：索引失效的常见原因

有索引不代表一定会走索引。以下情况索引失效：

-- 1. 对索引列使用函数
WHERE DATE(created_at) = '2026-07-01'  -- 索引失效
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02'  -- 索引有效

-- 2. 隐式类型转换
WHERE varchar_col = 123  -- 索引失效，改为 = '123'

-- 3. LIKE左模糊
WHERE name LIKE '%张'  -- 索引失效
WHERE name LIKE '张%'   -- 索引有效

-- 4. OR条件中有无索引列
WHERE indexed_col = 1 OR non_indexed_col = 2  -- 全表扫描

-- 5. NOT IN / NOT EXISTS大结果集
WHERE id NOT IN (SELECT order_id FROM returns)  -- 大结果集时性能差

第五步：优化效果验证

优化前后对比，用实际执行时间说话：

-- 优化前
EXPLAIN ANALYZE SELECT ...;  -- 记录执行时间和扫描行数

-- 添加索引
CREATE INDEX idx_xxx ON table(col);

-- 优化后
EXPLAIN ANALYZE SELECT ...;  -- 对比执行时间

用sys schema做持续监控：

-- 查看Top 10慢查询
SELECT * FROM sys.statement_analysis 
ORDER BY avg_latency DESC LIMIT 10;

-- 查看未走索引的查询
SELECT * FROM sys.statements_with_full_table_scans 
ORDER BY no_index_used_count DESC LIMIT 10;

线上操作注意事项

大表加索引用ALGORITHM=INPLACE, LOCK=NONE（MySQL 8.0+默认），避免锁表。加索引期间DML正常执行，但DDL会占用IO，建议在低峰期执行：

ALTER TABLE orders ADD INDEX idx_status_created(status, created_at DESC),
ALGORITHM=INPLACE, LOCK=NONE;

对于千万级大表，考虑用pt-online-schema-change做在线DDL，避免主从延迟。