概述(慢查询定义与危害)
慢查询——顾名思义,耗时很长、响应很慢的查询
危害——
导致整个数据库响应变慢(过多消耗cpu或者io)
短连接场景下还可能引起耗尽连接数
慢查询日志相关参数(开启慢查询)
以下参数均是是在Mysql的配置文件/etc/my.cnf中配置的,配置在[mysqld]下。
参数名 | 含义 |
---|---|
slow_query_log | 是否开启慢查询 |
long_query_time | 查询消耗超过多少时间认为是慢查询,单位秒 |
slow-query-log-file | 慢查询的保存文件 |
log_output | 日志的输出形式(FILE或者TABLE) |
log-queries-not-using-indexes | 记录没有使用索引的查询 |
示例参数配置
# 慢查询相关
slow_query_log = 1
long_query_time = 1
# 日志的输出形式
log_output = FILE
slow-query-log-file = /var/log/mysql/mysql-slow.log
修改参数之后记得重启mysqld服务,可以在mysql命令行验证修改是否生效。命令如下:
mysql> show global variables like 'slow%' ;
mysql> show global variables like 'long%' ;
mysql> show global variables like 'log%' ;
经过上面的配置mysql的慢日志就打开了,然后mysql就会记录各条查询相关信息了,下面是一个人为构造的演示例子。
演示例子(自造慢查询:)
下面是用来新建测试用的表和查询语句的sql命令。
mysql> use test;
mysql> create table b (id int not null primary key, name varchar(100) not null);
mysql> select sleep(1);
mysql> select * from b where name = 'a';
mysql> select * from b where name = 'b';
mysql> select * from b where name = 'c';
我们可以直接看下mysql-slow.log中的内容,cat或vim都行,只是看一眼:)
# cat /var/log/mysql/mysql-slow.log
下面是文件中个参数意义的介绍:
Querytime: sql的请求执行时间
Locktime: sql 等锁的时间
Rows_examined: sql处理过程中扫描的行数;通常,该值越大,对cpu/io消耗也就越大
慢查询分析
首先是慢查询分析工具——mysqldumpslow,示例用法如下:
# mysqldumpslow -t 10 -s t /path/to/slow.log
参数说明:
- -t:返回前面多少条的数据,即top几;
- -s:排序方式。c , t , l , r 分别表示记录次数、时间、查询时间的多少、返回的记录数排序;ac , at , al , ar 表示相应的倒叙;
TABLE类型慢日志处理
直接做工具处理相对比较麻烦,但我们可以变相处理。
处理思路:
- 将mysql.slow_log表的数据转成日志文件形式
- 然后再用mysqldumpslow进行分析
这里提供一个工具(python脚本,需要先安装MySQL-python依赖库),可以将mysql.slow_log表导出成日志格式
https://gist.github.com/sennajox/fdff82cc02b161d0141d
例如,要列出2016-5-1至2016-5-18期间的慢查询
# python slow_log_dump.py 10.6.29.129 3306 root 123456 '2016-5-1' '2016-5-18'
优化分析
记录slowlog仅仅是第一步,更重要的是找到慢查询的根源。
而这里,我们可以利用explain命令,对慢查询sql执行explain得到对应的执行计划进行分析,从而确定如何优化。
mysql> explain select * from b where name = 'a';
各个参数说明
关键的列明 | 含义 |
---|---|
type | 使用了什么查询方式,由好到差,依次为const > eq_ref > ref > range > index > ALL注意: index表示遍历索引;而ref表示使用非唯一的索引 |
key | 最终使用到的索引 |
rows | 扫描的行数 |
ref | 使用了索引的那一列,可能为列名或者const |
extra | 附加信息,比如”Using Where”、“Using Index”、“Using filesort”、“Using temporary” |
explain示例1
-- 实际SQL,查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引,前面加上explain即可
explain select * from emp where name = 'Jefabc';
优化要点
1.严禁在MySQL执行复杂的计算,以免过多消耗CPU,把复杂的事情交给程序处理
2.尽可能少用关联查询,如果不得不使用,最好不超过2个表,以免笛卡尔积过大,一方面增加读取磁盘几率,一方面有可能生成文件临时表,极大影响IO
3.适当的使用索引,且仅对离散程度比较高的列使用,让索引的效果可以最大化;索引是牺牲了写性能来换取读性能,因此也不能过多
4.控制单表的数据量,多作分库分表