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

show variables like '%slow_query_log%';

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

show variables like '%slow_query_log%';

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>

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

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>

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

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

vim my.cnf [mysqld] long_query_time = 1 log_output = FILE 重启MySQL服务

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

set global log_queries_not_using_indexes = on;

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

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

show variables like '%slow_query_log%';

[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]#

[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

[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]#

[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]# 

# 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内容

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

SET profiling = ON;

show variables like 'profiling%';

--
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：了解MySQL查询语句执行过程(5大组件)_Mysql_
                                                                
下一篇：MySQL函数与存储过程字符串长度限制的解决_Mysql_