本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • 【MySQL运维】慢查询日志配置、分析与explain优化

【MySQL运维】慢查询日志配置、分析与explain优化

发布:TangLu2020-2-11 11:10分类: MySQL 标签: mysql

一、什么是MySQL慢查询日志

MySQL的慢查询日志可以记录一些已经执行完成但是执行时间过慢的查询语句,在生产中数据库的性能问题大多数都是由于慢查询语句导致,所以分析慢查询日志可以帮助管理员分析问题所在,起到对SQL进行优化的作用。在一个慢查询日志中,需要关注查询次数多、执行时间长、IO大、未命中索引的SQL


二、配置MySQL慢查询日志

1、编辑my.cnf文件,在[mysqld]模块下增加以下内容以开启慢查询日志:

[mysqld]
slow_query_log = on #开启慢查询日志 
slow_query_log_file = /data/mysql/logs/mysql-slow.log #指定日志文件保存路径,不指定的话默认在数据库文件目录下,名为hostname-slow.log 
long_query_time = 2 #指定达到多少秒才算慢查询 
# long-queries-not-using-indexes #记录没有使用索引的查询语句 
# min_examined_row_limit = 1000 #记录查找了多达1000次而引起的慢查询 
# log-slow-admin-statements #记录ALTER TABLE等语句引发的慢查询 
# log-slow-slave-statements #记录从服务器产生的慢查询


2、重启MySQL服务使配置生效

service mysqld restart 


3、登陆数据库查询配置生效情况

mysql > show variables like '%slow%'; 


4、执行一个慢查询语句测试

select sleep(3); 


5、查看慢查询日志文件,如图箭头处显示了执行用户、执行时间以及查询语句。慢查询产生的原因一般是lock_time锁等待时间太长,或者是examined处理的数据太多导致

QQ图片20150807150352.png

 

三、MySQL慢查询日志分析工具

方法1:官方自带的mysqldumpslow,使用示例:

mysqldumpslow -t 10 mysql-slow.log  #显示top10的慢查询语句
mysqldumpslow -s al -t 10 mysql-slow.log  #返回10条平均锁时间最长的SQL
#常用选项
#-s:排序方法,分有al(平均锁时间)、ar(平均返回记录)、at(平均查询时间)等
#-t:返回前N条数据
#-g:正则匹配模式


QQ截图20150807151604.png

 

方法2:第三方工具pt-query-digest(推荐使用),由percona-toolkit软件包所提供。该工具包可以使用yum安装,然后就可以直接使用了。使用示例:

pt-quert-digest mysql-slow.log > slow_log.report  #直接分析慢查询日志并将结果输出到文件
pt-quert-digest --since=12h mysql-slow.log > slow_log.report #分析最近12个小时的慢查询
pt-quert-digest --since '2020-02-11 09:30:00' --until '2020-02-11 11:30:00'mysql-slow.log > slow_log.report #指定时间范围进行分析


分析结果中profile部分是对查询结果进行分组统计,query部分则是具体每个SQL的执行情况了,文章开始提到的几个关注点在pt-query-digest中都有展示:

查询次数多且执行时间长的SQLpt-query-digest分析结果中的前几个值

IO大的SQL:注意pt-query-digest中的Rows examine项

未命中索引的SQL:注意pt-query-digest中的Row examine(扫描行数)和Rows send(实际发送行数)的对比,如果扫描行数大于了发送行数说明命中次数不高


四、使用explain分析慢查询语句

1、explain使用方法

explain是MySQL自带的命令行工具,该命令可以显示SQL的执行计划,通过执行结果可以看出SQL是通过索引查询还是遍历整个表等信息。该语句不会真正执行后面的SQL,所以不会担心占用系统资源。下面是使用示例

mysql > EXPLAIN SELECT id,name FROM table;


explain1.png


2、explain返回结果说明

id:ID相同的SQL为一组,同一组中的SQL从上往下执行。在所有组中ID越大优先级越高。

table:这一条SQL是对哪张表在做操作

type:重要列,显示连接使用了何种类型。性能从高到底分为const(基于主键或者唯一索引的查找)、eq_reg(范围查找)、ref(基于索引查找)、range(基于索引的范围查找)、index(基于索引扫描)、all(全表扫描)

possible_keys:显示可能应用在这张表中的索引,如果为空代表没有索引。

key:实际用到的索引

key_len:索引长度,越短越好

ref:显示索引哪一列被使用了

rows:表扫描的行数

extra:扩展列,如果出现以下两种情况代表需要优化SQL语句:Using filesort,这是说MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行; Using temporary,这是说MySQL用到了临时表来存储结果,这通常发生在对不通的列集进行ORDER BY上,而不是GROUP BY上。

温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《【MySQL运维】慢查询日志配置、分析与explain优化
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-232.html  [生成短网址]

已有 0/2186 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~