mysql常见慢sql_mysql 慢SQL分析

开启慢SQL记录

为什么要开启慢SQL记录

mysql在运行过程中，某些SQL可能会执行较长时间，我们通过配置一些东西，把这些SQL记录下来，以便我们分析和优化SQL。

首先先查看是否已经开启了记录设置

mysql> show variables like '%query%';

+------------------------------+-------------------------+

| Variable_name | Value |

+------------------------------+-------------------------+

| long_query_time | 1.000000 |

| slow_query_log | ON |

| slow_query_log_file | /var/log/mysql_slow.log |

+------------------------------+-------------------------+

slow_query_log ON为已开启记录慢SQL，OFF表示关闭

long_query_time 阈值时间，当执行时间超过这个值的SQL会被记录下来

slow_query_log_file 记录的位置

使用命令行设置

set global slow_query_log = ON;

set long_query_time = 1;

set slow_query_log_file = '/var/log/mysql_slow.log';

通过这种方式设置，当mysql重新启动后设置会丢失，需要重新设置

通过修改my.conf设置

slow_query_log = ON

long_query_time = 1

slow_query_log_file = /var/log/mysql_slow.log

log_output=FILE,TABLE

在mysql的配置文件-my.cnf 的[mysqld]后面添加以上参数即可。log_output表示记录的方式，FILE表示文件，TABLE表示数据库(mysql.slow_log表)

需要注意的地方

必须保证slow_query_log_file文件存在，且mysql有读写权限才行。笔者在实验的时候就是因为没有权限，导致每次设置都不成功。

记录格式

# Time: 2018-07-07T09:00:28.454918Z

# User@Host: huangzp[huangzp] @ [119.145.99.50] Id: 12

# Query_time: 45.012748 Lock_time: 0.000220 Rows_sent: 254 Rows_examined: 1356773

SET timestamp=1530954028;

select userId from chatting_log_201702 where apType = 2 and userId = 'anywillfour';

第一行：SQL开始执行的时间

第二行：SQL执行的用户

第三行：Query_time(SQL查询时间)， Lock_time(锁时间)，Rows_sent(返回的行数)， Rows_examined(查询的行数)

第四行：SQL开始执行的时间对应的时间戳

第五行：SQL详情

记录分析(mysqldumpslow)

mysqldumpslow -s t -t 10 -g 'log' mysql_slow.log

MySQL自带的慢SQL分析工具

-s : 按照哪种规则排序

c: 访问计数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t : 显示前几个记录

-g : 有点像grep， 后跟正则

[root@VM_centos log]# mysqldumpslow -s t -t 10 mysql_slow.log

Reading mysql slow query log from mysql_slow.log

Count: 1 Time=157.27s (157s) Lock=0.00s (0s) Rows=1153145.0 (1153145), huangzp[huangzp]@[127.0.0.1]

select userId from chatting_log_201702 where apType = N

Count: 2 Time=69.46s (138s) Lock=0.00s (0s) Rows=1.0 (2), huangzp[huangzp]@[127.0.0.1]

select count(*) from chatting_log_201702 where apType = N and userId = 'S'

Count: 1 Time=45.01s (45s) Lock=0.00s (0s) Rows=254.0 (254), huangzp[huangzp]@[127.0.0.1]

select userId from chatting_log_201702 where apType = N and userId = 'S'

Count: 1 Time=42.18s (42s) Lock=0.00s (0s) Rows=49906.0 (49906), huangzp[huangzp]@[127.0.0.1]

select * from chatting_log_201702 where roomId = 'S'

Count: 1 Time=38.38s (38s) Lock=0.00s (0s) Rows=10732.0 (10732), huangzp[huangzp]@[127.0.0.1]

select userId from chatting_log_201702 where apType = N and roomId = 'S'

Count: 1 Time=8.02s (8s) Lock=0.00s (0s) Rows=1.0 (1), huangzp[huangzp]@[127.0.0.1]

select count(*) from chatting_log_201702 where userId = 'S'

以上就是显示按照查询时间排序前十的SQL。