深入浅出 MySQL慢查询定位与优化攻略

慢查询定位

开启慢查询

SHOW VARIABLES LIKE 'slow_query_log%'

SET global slow_query_log = ON;
SET global slow_query_log_file = 'h122-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

long_query_time

指定慢查询的阈值，单位是秒，默认值为10秒。

log_queries_not_using_indexes

控制是否记录未使用索引的查询语句。

查看慢日志

日志包含：

• time：时间戳
• User@Host：执行查询的用户和主机
• Query_time：SQL语句执行的总耗时
• Lock_time：等待表锁的时间
• Rows_sent：返回记录行数
• Rows_examined：扫描的记录总行数

mysqldumpslow

perl mysqldumpslow.pl -t 5 -s xxx-slow.log

慢查询优化

常见优化方案

只取需要的列与行

• SELECT列表写全，不要SELECT *
• 加好WHERE

为过滤与连接建索引

• 优先给WHERE/JOIN/ORDER BY/GROUP BY中出现的列建索引

避免函数包裹列与隐式类型转换

模糊查询避免前导通配符

• LIKE ‘%abc’无法用到普通B-Tree索引

分页优化（深分页杀手）

SELECT id, name FROM t
WHERE id > ?
ORDER BY id
LIMIT 20;

覆盖索引

• 让查询只在索引层完成，避免回表

合理拆分OR/优先使用EXISTS

SELECT ... FROM t WHERE type=1 AND cond...
UNION ALL
SELECT ... FROM t WHERE type=2 AND cond...