sql优化-3-慢查询
慢查询旦志
MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阀值的SQL语句(long_query_time 默认10秒）
开启慢查询
慢查询日志默认是关闭的；建议：开发调优时打开，而最终部署时关闭。
检查是否开启了慢查询日志: show variables like '%slow_query_log%';
mysql> show variables like '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/centos8-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)
临时开启(在内存中开启，重启mysql服务后失效)：set global slow_query_log = 1 ;
永久开启 在/etc/my.cnf 中追加配置：
vi /etc/my.cnf
[mysqld]下追加
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
慢查询阀值
多久的SQL算是慢查询
查看慢查询阀值：show variables like '%long_query_time%';
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
临时修改阀值：set global long_query_time = 5 ; 设置完毕后，重新登陆后起效（不需要重启服务)
永久设置阀值： 在/etc/my.cnf中追加配置:
vi /etc/my.cnf
[mysqld]下追加
long_query_time=3
查看慢SQL
select sleep(4);
1.查询超过阀值的SQL数
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 5 |
+---------------+-------+
1 row in set (0.00 sec)
2.通过日志文件查看具体慢SQL
慢查询的sql被记录在了日志中，因此可以通过日志查看具体的慢SQL。
cat /var/lib/mysql/localhost-slow.log
[root@centos8 ~]# cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.5.58-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.5.58-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 220712 14:50:30
# User@Host: root[root] @ localhost []
# Query_time: 4.009862 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1657608630;
select sleep(4);
# Time: 220712 14:50:34
# User@Host: root[root] @ localhost []
# Query_time: 4.017147 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1657608634;
select sleep(4);
# Time: 220712 14:50:38
# User@Host: root[root] @ localhost []
# Query_time: 4.019439 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1657608638;
select sleep(4);
# Time: 220712 14:50:42
# User@Host: root[root] @ localhost []
# Query_time: 4.003495 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1657608642;
select sleep(4);
# Time: 220712 14:50:46
# User@Host: root[root] @ localhost []
# Query_time: 4.010954 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1657608646;
select sleep(4);
[root@centos8 ~]#
3.通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件快速找出需要定位的慢SQL
mysqldumpslow --help
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式
[root@centos8 ~]# mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
-- 获取返回记录最多的3个SQL
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 5 Time=4.01s (20s) Lock=0.00s (0s) Rows=1.0 (5), root[root]@localhost
select sleep(N)
Died at /usr/bin/mysqldumpslow line 161, <> chunk 5.
[root@centos8 ~]# mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
-- 获取访问次数最多的3个SQL
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 5 Time=4.01s (20s) Lock=0.00s (0s) Rows=1.0 (5), root[root]@localhost
select sleep(N)
Died at /usr/bin/mysqldumpslow line 161, <> chunk 5.
[root@centos8 ~]# mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
-- 按照时间排序，前10条包含left join查询语句的SQL
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Died at /usr/bin/mysqldumpslow line 161, <> chunk 5.
[root@centos8 ~]#
