服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL常用慢查询分析工具详解

MySQL常用慢查询分析工具详解

2022-08-14 20:07博学谷狂野架构师 Mysql

这篇文章主要介绍了MySQL常用慢查询分析工具详解，文章围绕主题展开详细的内容介绍，具有一定的参考价值，需要的小伙伴可以参考一下

引言

在日常的业务开发中
MySQL 出现慢查询是很常见的

大部分情况下会分为两种情况：

  • 1、业务增长太快
  • 2、要么就是SQL 写的太xx了

所以
对慢查询 SQL 进行分析和优化很重要
其中 mysqldumpslow 是 MySQL 服务自带的一款很好的分析调优工具

1、调优工具mysqldumpslow

1.1调优工具常用设置

1、什么是MySQL 慢查询日志

MySQL提供的一种慢查询日志记录，用来记录在MySQL查询中响应时间超过阀值的记录 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中

2、如何查看慢查询设置情况

慢查询的时间阈值设置

?
1
show variables like '%slow_query_log%';

MySQL常用慢查询分析工具详解

解释：

  • slow_query_log //是否开启，默认关闭，建议调优时才开启
  • slow_query_log_file //慢查询日志存放路径

3、如何开启慢查询日志记录

1) 命令开启

?
1
set global slow_query_log =1; //只对当前会话生效，重启失效

执行成功

再次执行

?
1
show variables like '%slow_query_log%';

先关闭客户端连接，再进行重新连接，即可看到设置生效

发现开启了mysqldumpslow调优工具

?
1
2
3
4
5
6
7
8
9
10
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | ON                                        |
| slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.02 sec)
 
mysql>

2）配置文件开启

?
1
2
3
4
5
vim my.cnf
在[mysqld]下添加：
slow_query_log = 1
slow_query_log_file = /opt/mysql-5.7.28/data/linux-141-slow.log
重启MySQL服务

修改并且重启后

发现开启了mysqldumpslow调优工具

?
1
2
3
4
5
6
7
8
9
10
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | ON                                        |
| slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.02 sec)
 
mysql>

3）哪些 SQL 会记录到慢查询日志

?
1
2
-- 查看阀值（大于），默认10s
show variables like 'long_query_time%';

MySQL常用慢查询分析工具详解

默认值是10秒

4）如何设置查询阀值

命令设置

?
1
2
-- 设置慢查询阀值
set global long_query_time = 1;

备注：另外开一个session或重新连接 ，才会看到变化

执行成功发发现慢sql的时间变成了1秒

MySQL常用慢查询分析工具详解

配置文件设置：

?
1
2
3
4
5
vim my.cnf
[mysqld]
long_query_time = 1
log_output = FILE
重启MySQL服务

执行成功发发现慢sql的时间变成了1秒

MySQL常用慢查询分析工具详解

5）如何把未使用索引的 SQL 记录写入慢查询日志

?
1
2
-- 查看设置，默认关闭
show variables like 'log_queries_not_using_indexes';

我们发现，未使用索引的sql默认是不记录到慢查询日志的

MySQL常用慢查询分析工具详解

开启配置：

?
1
set global log_queries_not_using_indexes = on;

执行如下：

MySQL常用慢查询分析工具详解

6）模拟数据

?
1
2
3
4
-- 睡眠2s再执行
select sleep(2);
-- 查看慢查询条数
show global status like '%Slow_queries%';

我们发现，每执行一次select sleep(2)，之后，再通过show global status ...命令，他的值就会+1

MySQL常用慢查询分析工具详解

1.2 调优工具常用命令

语法格式

?
1
mysqldumpslow [ OPTS... ] [ LOGS... ] //命令行格式

常用到的格式组合：

  • -s 表示按照何种方式排序
  • c 访问次数
  •  l 锁定时间
  •  r 返回记录
  •  t 查询时间
  • al 平均锁定时间
  •  ar 平均返回记录数
  •  at  平均查询时间
  • -t 返回前面多少条数据
  • -g 后边搭配一个正则匹配模式，大小写不敏感

1、拿到慢日志路径

?
1
show variables like '%slow_query_log%';

日志路径为：/opt/mysql-5.7.28/data/linux-141-slow.log

查看日志

?
1
2
3
4
5
6
7
8
9
10
11
12
[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-09-15T01:40:31.342430Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 2.000863  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use itcast;
SET timestamp=1631670031;
-- 睡眠2s再执行
select sleep(2);
[root@linux-141 mysql-5.7.28]#

2、得到访问次数最多的10条SQL

?
1
2
3
4
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t  10 /opt/mysql-5.7.28/data/linux-141-slow.log
-bash: ./bin/mysqldumpslow: /usr/bin/perl: 坏的解释器: 没有那个文件或目录
[root@linux-141 mysql-5.7.28]# yum -y install perl perl-devel
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t  10 /opt/mysql-5.7.28/data/linux-141-slow.log

3、按照时间排序的前10条里面含有左连接的SQL

?
1
2
3
4
5
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s t -t 10 -g "left join"  /opt/mysql-5.7.28/data/linux-141-slow.log
 
Reading mysql slow query log from /opt/mysql-5.7.28/data/linux-141-slow.log
Died at ./bin/mysqldumpslow line 167, <> chunk 28.
[root@linux-141 mysql-5.7.28]#

1.3 慢日志文件分析

1、查看慢查询日志

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-09-15T01:40:31.342430Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 2.000863  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use itcast;
SET timestamp=1631670031;
-- 睡眠2s再执行
select sleep(2);
# Time: 2021-09-15T01:50:32.130305Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 3.001904  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1631670632;
select sleep(3);
# Time: 2021-09-15T01:50:55.064372Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 4.008082  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1631670655;
select sleep(4);
# Time: 2021-09-15T01:51:01.343463Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 5.007035  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1631670661;
select sleep(5);
# Time: 2021-09-15T01:51:07.737834Z                                         ###### 执行SQL时间
# User@Host: root[root] @  [192.168.36.1]  Id:     2                        ###### 执行SQL的主机信息
# Query_time: 6.009129  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0    ###### SQL的执行信息
SET timestamp=1631670667;                                                    ###### SQL执行时间
select sleep(6);                                                            ###### SQL内容
[root@linux-141 mysql-5.7.28]#

属性解释

?
1
2
3
4
5
# Time: 2021-09-15T01:51:07.737834Z                                         ###### 执行SQL时间
# User@Host: root[root] @  [192.168.36.1]  Id:     2                        ###### 执行SQL的主机信息
# Query_time: 6.009129  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0    ###### SQL的执行信息
SET timestamp=1631670667;                                                    ###### SQL执行时间
select sleep(6);                                                            ###### SQL内容

2、 调优工具show profile

tips：

show profile，它也是调优工具

也是MySQL服务自带的分析调优工具

不过这款更高级

比较接近底层硬件参数的调优。

简介：

show profile是MySQL服务自带更高级的分析调优工具

比较接近底层硬件参数的调优

1、查看show profile设置

?
1
2
-- 默认关闭，保存近15次的运行结果
show variables like 'profiling%';

MySQL常用慢查询分析工具详解

通过上面我们发现，show profile工具默认是关闭状态，15表示保存了近15次的运行结果。

2、开启调优工具

执行下面的命令开启

?
1
SET profiling = ON;

再次查看状态

?
1
show variables like 'profiling%';

MySQL常用慢查询分析工具详解

3、查看最近15次的运行结果

?
1
2
3
4
5
6
7
8
-- 查看最近15次的运行结果
show profiles;
 
-- 可以显示警告和报错的信息
show warnings;
 
-- 慢查询语句
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

显示最近15次的运行结果

MySQL常用慢查询分析工具详解

4、诊断运行的SQL

接下来，我们一起诊断一下query id为23的慢查询

?
1
2
3
4
-- 语法
SHOW PROFILE cpu,block io FOR QUERY query id;
-- 示例
SHOW PROFILE cpu,block io FOR QUERY 129;

开始执行：

MySQL常用慢查询分析工具详解

 解释：通过Status一列，可以看到整条SQL的运行过程

  • 1. starting //开始
  • 2. checking permissions //检查权限
  • 3. Opening tables //打开数据表
  • 4. init //初始化
  • 5. System lock //锁机制
  • 6. optimizing //优化器
  • 7. statistics //分析语法树
  • 8. prepareing //预准备
  • 9. executing //引擎执行开始
  • 10. end //引擎执行结束
  • 11. query end //查询结束
  • 12. closing tables //释放数据表
  • 13. freeing items //释放内存
  • 14. cleaning up //彻底清理
?
1
2
3
4
5
6
7
8
9
10
11
查看类型选项
SHOW PROFILE...后面的列，即：SHOW PROFILE ALL, BLOCK IO, ... FOR QUERY 209;
ALL //显示索引的开销信息
BLOCK IO //显示块IO相关开销
CONTEXT SWITCHES  //上下文切换相关开销
CPU //显示CPU相关开销信息
IPC //显示发送和接收相关开销信息
MEMORY //显示内存相关开销信息
PAGE FAULTS //显示页面错误相关开销信息
SOURCE //显示和source_function,source_file,source_line相关的开销信息
SWAPS //显示交换次数相关开销的信息

重要提示：

?
1
2
3
4
5
如出现以下一种或者几种情况，说明SQL执行性能极其低下，亟需优化
* converting HEAP to MyISAM  //查询结果太大，内存都不够用了往磁盘上搬了
* Creating tmp table //创建临时表：拷贝数据到临时表，用完再删
* Copying to tmp table on disk //把内存中临时表复制到磁盘，危险
* locked //出现死锁

到此这篇关于MySQL常用慢查询分析工具详解的文章就介绍到这了,更多相关MySQL慢查询工具内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.51cto.com/boxuegu/5488880

  • mysql
  • 慢查询
  • 分析工具

    • 延伸 · 阅读

    精彩推荐
    • MysqlMySQL学习教程之聚簇索引

      MySQL学习教程之聚簇索引

      这篇文章主要给大家介绍了关于MySQL学习教程之聚簇索引的相关资料，文中通过图文介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需...

      超人不会飞9112021-02-28
    • MysqlMacOS下mysql 8.0.18 安装配置方法图文教程

      MacOS下mysql 8.0.18 安装配置方法图文教程

      这篇文章主要介绍了MacOS下mysql 8.0.18 安装配置方法图文教程，并成功命令行操作，文中安装步骤介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们...

      赵延超5302020-12-13
    • MysqlMysql时间轴数据 获取同一天数据的前三条

      Mysql时间轴数据 获取同一天数据的前三条

      这篇文章主要介绍了Mysql时间轴数据 获取同一天数据的前三条 ,本文通过实例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考...

      一只怀揣IT梦想的蜗牛4462020-11-26
    • MysqlMAC下Mysql5.7+ MySQL Workbench安装配置方法图文教程

      MAC下Mysql5.7+ MySQL Workbench安装配置方法图文教程

      这篇文章主要为大家详细介绍了MAC下Mysql5.7+ MySQL Workbench安装配置方法图文教程，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...

      small小阮4742020-09-30
    • Mysql关于MySQL中savepoint语句使用时所出现的错误

      关于MySQL中savepoint语句使用时所出现的错误

      这篇文章主要介绍了关于MySQL中savepoint语句使用时所出现的错误,字符串出现e时所产生的问题也被作为MySQL的bug进行过提交,需要的朋友可以参考下 ...

      MYSQL教程网4532020-05-06
    • MysqlNO Route to Host 连接mysql数据库

      NO Route to Host 连接mysql数据库

      本文主要讲解NO Route to Host 连接mysql数据库的方法，有需要的朋友可以参考一下。...

      mysql教程网10772020-06-29
    • MysqlMySQL视图和索引专篇精讲

      MySQL视图和索引专篇精讲

      这篇文章主要介绍了MySQL的视图和索引用法与区别详解，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友...

      桃花键神5242022-03-09
    • Mysqlmysql 5.7.18 zip版安装配置方法图文教程（win7）

      mysql 5.7.18 zip版安装配置方法图文教程（win7）

      这篇文章主要为大家详细介绍了win7下mysql 5.7.8 zip版安装配置方法图文教程，具有一定的参考价值，感兴趣的小伙伴们可以参考一下 ...

      路飞_luffy3082020-08-12
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 10048 　 1175 　 2003 　 1366 　 28000 　 1577 　 1135 　 1786 　 1093 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图