1、通过慢查询日志获取慢sql
慢查询日志是Mysql提供的一种日志记录,它用来记录在mysql中响应时间超过阈值的语句,具体就是指运行时间超过long_query_time值的sql,就会被记录到慢查询日志当中去!long_query_time的默认值是10;默认情况也是不会开启慢查询日志的功能;
手动打开慢查询日志:
##查看慢查询日志功能是否开启(如下图所示)
1、show variables like '%slow_query_log%';
##开启慢查询日志,
2、set global slow_query_log=1;##只对当前数据库生效,永久生效需要修改配置文件
##查询阈值时间
3、show variables like 'long_query_time';
##设置阈值时间3秒
4、set global long_query_time = 3;//如果看不到修改的值,需要重新打开一个会话,或者重新连接
##可以使用sleep来模拟复杂的sql,执行完就会被抓取到慢查询的日志中区
5、select sleep(4);
##查看当前系统有多少条慢sql语句
6、show global status like '%slow_queries%';
通过日志分析工具mysqldumpslow 来分析慢查询日志
1、mysqldumpslow --help
##参数说明
s : 表示按照何种方式进行查询
c : 访问的次数
l : 锁定的时间
r : 返回记录
t : 查询时间
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
t : 返回前面多少条记录
g : 后面搭配正则匹配模式,大小写不明感
例如:
##获取返回记录数最多的10个数据
2、mysqldumpslow -s r -t 10 /usr/local/mysql/data/macdeMacBook-Pro-slow.log
##得到访问次数最多的10个sql
3、mysqldumpslow -s c -t 10 /usr/local/mysql/data/macdeMacBook-Pro-slow.log
##得到按照时间排序的前10条含有左连接的查询语句
4、mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/data/macdeMacBook-Pro-slow.log
##在使用命令的时候最好结合| more来查看,防止出现爆屏的情况
5、mysqldumpslow -s r -t 10 /usr/local/mysql/data/macdeMacBook-Pro-slow.log | more
2、通过explain+慢sql进行分析
explain查看sql的执行过程,可以查看sql执行走索引的情况,是否使用到了索引,是否涉及到文件内排序等。索引的使用需要满足最左前缀匹配原则。
![explain简单分析](https://img-blog.csdnimg.cn/20200729101125354.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1NTU3Nw==,size_16,color_FFFFFF,t_70)
3、通过show profile命令查询在Mysql服务器里面的执行细节和生命周期
show profile
定义:
是mysql提供用来分析当前会话中语句执行的资源的消耗情况。可以用于sql调优的测量。默认情况下处于关闭状态,并保存最近15次的运行结果
相关命令
##查看当前profiling是否打开
1、show variables like 'profiling';
##打开profiling
2、set profiling=on;
##执行show profiles;查看最后15条语句执行状况
3、show Profiles;//执行结果如下图所示
##查看sql执行的生命周期,各过程执行的时间状况;
4、show profile cpu,block io for query query_ID;
type:参数有
all : 显示所有的开销信息
block io : 显示块io相关开销
context awitches : 上下文切换开销
cpu : 显示CPU相关开销信息
ipc : 显示发送和接受相关的开销信息
memory : 显示内存相关开销信息
page faults : 显示页面错误相关开销信息
source : 显示和source_function,source_file,source_line相关开销信息
swaps : 显示交换次数相关开销信息
show profile查询优化分析
==》converting HEAP to MyISAM查询结果值太大,内存不够用往磁盘上搬
==》creating tmp table创建临时表,拷贝临时数据到临时表,用完再删除(可能由group by引起,由索引使用不当引起)
==》coping to tmp table on disk : 吧内存中临时表复制到磁盘
==》locked
4、SQL数据库的参数调优
可以修改sort_buffer等参数的值