mysql|mysql 慢SQL分析

  • 开启慢SQL记录
  1. 为什么要开启慢SQL记录
    mysql在运行过程中,某些SQL可能会执行较长时间,我们通过配置一些东西,把这些SQL记录下来,以便我们分析和优化SQL。
  2. 首先先查看是否已经开启了记录设置
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 记录的位置
  1. 【mysql|mysql 慢SQL分析】使用命令行设置
    set global slow_query_log = ON;
    set long_query_time = 1;
    set slow_query_log_file = '/var/log/mysql_slow.log';
    通过这种方式设置,当mysql重新启动后设置会丢失,需要重新设置
  2. 通过修改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表)
  1. 需要注意的地方
    必须保证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.012748Lock_time: 0.000220 Rows_sent: 254Rows_examined: 1356773 SET timestamp=1530954028; select userId from chatting_log_201702 whereapType = 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 10mysql_slow.logReading mysql slow query log from mysql_slow.log Count: 1Time=157.27s (157s)Lock=0.00s (0s)Rows=1153145.0 (1153145), huangzp[huangzp]@[127.0.0.1] select userId from chatting_log_201702 whereapType = NCount: 2Time=69.46s (138s)Lock=0.00s (0s)Rows=1.0 (2), huangzp[huangzp]@[127.0.0.1] select count(*) from chatting_log_201702 whereapType = N and userId = 'S'Count: 1Time=45.01s (45s)Lock=0.00s (0s)Rows=254.0 (254), huangzp[huangzp]@[127.0.0.1] select userId from chatting_log_201702 whereapType = N and userId = 'S'Count: 1Time=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: 1Time=38.38s (38s)Lock=0.00s (0s)Rows=10732.0 (10732), huangzp[huangzp]@[127.0.0.1] select userId from chatting_log_201702 whereapType = N and roomId = 'S'Count: 1Time=8.02s (8s)Lock=0.00s (0s)Rows=1.0 (1), huangzp[huangzp]@[127.0.0.1] select count(*) from chatting_log_201702 whereuserId = 'S'

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

    推荐阅读