MySQL慢查询及解决方案

一、前言

对于生产业务系统来说，慢查询也是一种故障和风险，一旦出现故障将会造成系统不可用影响到生产业务。当有大量慢查询并且SQL执行得越慢，消耗的CPU资源或IO资源也会越大，因此，要解决和避免这类故障，关注慢查询本身是关键。

二、慢查询

2.1 什么是慢查询？

慢查询，顾名思义，执行很慢的查询。当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个SQL语句就是需要优化的。慢查询被记录在慢查询日志里。慢查询日志默认是不开启的。如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

2.2 慢查询配置

以MySQL数据库为例，默认慢查询功能是关闭的，当慢查询开关打开后，并且执行的SQL语句达到参数设定的阈值后，就会触发慢查询功能打印出日志。

1、慢查询日志

操作	SQL语句
查询是否开启	show variables like "slow_query_log";
开启慢查询	set global slow_query_log = 1/on;
关闭慢查询	set global slow_query_log = 0/off;

2、未使用索引是否开启日志

操作	SQL语句
查询是否开启	show variables like "log_queries_not_using_indexes";
开启记录	set global log_queries_not_using_indexes=1/on;
关闭记录	set global log_queries_not_using_indexes=0/off;

3、慢查询时间设置

操作	SQL语句
查询阈值	show variables like "long_query_time";
设置阈值	set global long_query_time= X;

注意：上述参数设置在当前数据库生效，MySQL重启后会失效。如需永久生效，必须修改配置文件my.cnf

4、慢查询路径

show variables like "slow_query_log_file%";

示例路径：/apps/log/mysql/slow3306.log

三、慢查询日志分析

3.1 mysqldumpslow工具

使用mysqldumpslow工具分析慢查询日志：

# 查询用时最多的10条慢sql
mysqldumpslow -s t -t 10 -g "select" /data/mysql/data/dcbi-3306/log/slow.log

日志字段说明：

字段	含义
Count	该SQL语句执行了多少次
Time	执行时间，括号内为累计时间
Lock	锁定时间，括号内为累计时间
Rows	返回的记录数，括号内为累计记录数

通过慢查询日志分析，可以更有针对性和更快捷地处理出现慢查询的SQL语句问题。

四、慢查询解决方案

4.1 索引失效

常见索引失效情况：

1. LIKE关键字查询
   • 匹配字符串第一个字符为"%"：索引不起作用
   • "%"不在第一个位置：索引才会起作用
2. 多列索引查询
   • 只有查询条件使用了第一个字段时，索引才会被使用
   • 遵循左匹配原则

4.2 SQL语句优化

1. 查询语句应尽量避免全表扫描，首先考虑在WHERE和ORDER BY子句上建立索引
2. 避免使用IN和NOT IN，尽量用EXISTS和NOT EXISTS（后者可能导致全表扫描）
3. 避免在WHERE子句中对字段进行NULL判断（会导致全表扫描）
4. 避免在WHERE子句中使用or作为连接条件（会导致全表扫描）
5. 避免使用!=或<>操作符（会导致全表扫描）
6. 使用like "%abc%"或like "%abc"会导致全表扫描，like "abc%"会使用索引
7. 使用UNION时，考虑用UNION ALL代替（后者不进行排序去重，性能更高）
8. 避免在WHERE子句中使用表达式操作符
9. 避免在WHERE子句中对字段使用函数
10. SELECT *语句尽量避免使用（解析时需转换为所有列名，有额外开销）
11. WHERE子句中，表连接条件应写在其他条件之前（解析从后向前）
12. 联合索引字段顺序应与WHERE子句条件顺序一致
13. FROM子句中写在末尾的表将被优先处理，应选择记录较少的表作为基表
14. 使用>=代替>操作符，性能更高效

4.3 表结构优化

1. 每条SQL语句最多只可能使用一个索引
2. InnoDB表必须指定主键（自动生成的主键性能不足、并发不足）
3. 区分度不大的字段不要建立索引
4. 一个字段只需建一种索引
5. 大文本字段或BLOB字段不要建立索引
6. 连接查询的连接字段应建立索引
7. 排序字段一般要建立索引
8. 分组统计字段一般要建立索引
9. 正确使用联合索引（第一个字段可单独使用）
10. 索引一般用于记录比较多的表

五、总结

在日常写SQL和写程序时多关注基本的SQL语句，在业务复杂的系统中，除了上述基本的点外，尽管使用了索引，也还需要从业务本身出发。如：当查询的数量过大时，时间索引已经不满足，可以改为分批次来查询控制数量等。

关键要点汇总：

类别	核心要点
慢查询定义	执行时间超过long_query_time（默认10秒）的SQL
日志开启	slow_query_log = ON
阈值设置	long_query_time = X
分析工具	mysqldumpslow
索引失效	LIKE以%开头、多列索引未使用左匹配
优化原则	避免全表扫描、合理建立索引、表结构优化

---

原文链接：CSDN原文