MySQL慢查询定位与索引优化实战指南

开启慢查询日志与关键参数

慢查询日志是MySQL性能调优的入口。默认未开启，需要在my.cnf中配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

参数说明：

– long_query_time = 1：执行超过1秒的SQL记录到慢日志

– log_queries_not_using_indexes = 1：未使用索引的查询也记录

– min_examined_row_limit = 100：扫描行数低于100的查询不记录

在线动态开启（无需重启）：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

mysqldumpslow分析慢查询日志

MySQL自带的mysqldumpslow工具对慢日志做聚合分析：

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

输出中SQL的具体参数值被N替代，相同的SQL模板被归为一类。重点关注Query_time和Rows_examined的比值——扫描10万行只返回10条，说明索引效率极低。

EXPLAIN执行计划深度解读

定位到慢SQL后，用EXPLAIN分析执行计划：

EXPLAIN SELECT o.order_id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending' AND o.created_at > '2026-01-01';

关键字段解读：

type列（访问类型，从优到差）：

– const/system：主键或唯一索引等值查询，最优

– eq_ref：JOIN时使用主键或唯一索引

– ref：非唯一索引等值查询

– range：索引范围扫描

– index：全索引扫描

– ALL：全表扫描，必须优化

Extra列（额外信息）：

– Using index：覆盖索引，无需回表，最优

– Using where：存储层返回数据后在Server层过滤

– Using temporary：使用临时表

– Using filesort：额外排序操作

– Using index condition：索引下推（ICP）

索引设计原则与常见反模式

1. 最左前缀原则：联合索引(a, b, c)只能用于a、(a,b)、(a,b,c)的查询条件。

-- 索引：idx_abc (a, b, c)
SELECT * FROM t WHERE a = 1 AND b = 2;        -- 命中索引
SELECT * FROM t WHERE a = 1 AND c = 3;         -- 仅命中a
SELECT * FROM t WHERE b = 2 AND c = 3;          -- 不命中

2. 避免索引列做函数运算：

-- 无法使用索引
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 改写为范围查询，命中索引
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

3. 避免隐式类型转换：字段是varchar，查询条件传整数会导致索引失效：

-- phone是varchar类型，以下查询索引失效
SELECT * FROM users WHERE phone = 13800138000;
-- 正确写法
SELECT * FROM users WHERE phone = '13800138000';

4. 避免SELECT *：只查需要的列，增加覆盖索引概率，减少回表次数。

索引下推（ICP）优化

MySQL 5.6引入的Index Condition Pushdown将部分WHERE过滤下推到存储引擎层执行，减少回表次数：

-- 联合索引：(last_name, first_name)
SELECT * FROM people
WHERE last_name = 'Smith' AND first_name LIKE '%ohn';

无ICP：存储引擎通过last_name找到所有主键，回表取完整行，Server层再过滤。

有ICP：存储引擎在索引中直接用first_name LIKE '%ohn'过滤，不符合的不回表。

SHOW VARIABLES LIKE 'optimizer_switch';
-- index_condition_pushdown=on

分页查询优化

深分页（LIMIT 100000, 10）的性能问题：MySQL需要扫描前100010行再丢弃前100000行。

方案一：延迟关联

-- 原始慢查询
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 优化：先通过子查询用覆盖索引获取ID
SELECT * FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;

子查询只扫描索引树，速度比原始查询快10-100倍。

方案二：游标分页

-- 第一页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 10;
-- 下一页（记录上一页最后一条的id）
SELECT * FROM orders WHERE id > 100010 ORDER BY id LIMIT 10;

游标分页避免了OFFSET扫描，时间复杂度从O(N)降到O(logN)。

线上索引变更的安全操作

直接ALTER TABLE ADD INDEX在大表上会锁全表。使用pt-online-schema-change做在线DDL：

pt-online-schema-change \
  --alter "ADD INDEX idx_status_created(status, created_at)" \
  --host=127.0.0.1 --user=admin --password=xxx \
  D=production,t=orders \
  --execute

工具创建影子表，通过触发器同步增量数据，最后原子替换原表。全程线上业务不受影响。