首页 » 数据库 » 学习MySQL
MySQL 开启慢查询日志

mysql 开启慢查询日志

本方案只适应于小的项目、项目未上线或者紧急情况下可采用这种方式，一旦开启慢日志查询会增加数据库的压力，所以一般采用后台对数据操作时间写入日志文件中，每一周定时进行清除日志。

mysql优化方案：开启慢查询日志，查询sql执行超过一秒以上sql等。

开启慢查询日志：可以让mysql记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

参数说明：

slow_query_log 慢查询开启状态，on开启，off关闭

slow_query_log_file 慢查询日志存放的位置（这个目录需要mysql的运行帐号的可写权限，一般设置为mysql的数据存放目录）

long_query_time 查询超过多少秒才记录

重点说明：开启慢日志版本要高，低版本无法支持，本次版本是：5.7.20

select version();  查询版本号

该版本默认情况下已开启慢日志

mysql> show databases;

mysql> use test;   //指定一个数据库

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| variable_name   | value     |
+-----------------+-----------+
| slow_query_log | on |
+-----------------+-----------+
| slow_query_log_file | yh-20161209qizc-slow.log |
+-----------------+-----------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| variable_name   | value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

//默认查询超过10秒才记录

 

1. 设置慢查询日志

方法一：全局变量设置(该方式数据库重启全部失效，得重新配置)

将 slow_query_log 全局变量设置为“on”状态

mysql> set global slow_query_log='on';

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //linux
mysql> set global slow_query_log_file='d:\\mysq\data\slow.log';         //windows

设置查询超过1秒就记录（如果有时候用命令不起作用，那么可以关闭再打开）

mysql> set global long_query_time=1;

方法二：配置文件设置（服务器重启不影响）

修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = on
slow_query_log_file = /usr/local/mysql/data/slow.log     //linux
long_query_time = 1

重启mysql服务：

service mysqld restart

 

2. 测试慢查询日志

1.执行一条慢查询sql语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

这里可以看出是那个sql以及查询时间

如果日志存在，mysql开启慢查询设置成功！ 

 

3. 日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析sql，显然是个体力活，mysql提供了日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息：

[root@db-server ~]# mysqldumpslow --help

usage: mysqldumpslow [ opts... ] [ logs... ]

parse and summarize the mysql slow query log. options are

--verbose    verbose
--debug      debug
--help       write this text to standard output
-v           verbose
-d           debug
-s order     what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time  
-r           reverse the sort order (largest last instead of first)
-t num       just show the top n queries
-a           don't abstract all numbers to n and strings to 's'
-n num       abstract numbers with at least n digits within names
-g pattern   grep: only consider stmts that include this string
-h hostname  hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all

-i name      name of server instance (if using mysql.server startup script)
-l           don't subtract lock time from total time

-s, 是表示按照何种方式排序，

  • c: 访问计数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如：得到返回记录集最多的10个sql。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个sql

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

相关文章
学习MySQL