MySQL中的日志包括:错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍慢查询日志。
如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
如何开启慢日志查询?
方法一
1. 是否开启慢日志查询:
show variables like 'slow%';
注意:这里记录了慢查询日志的地址,例如,D:\phpStudy\MySQL\data\DESKTOP-IFGFGUV-slow.log
2. 开启慢日志查询:
set global slow_query_log='ON'
//mysql默认的慢日志查询默认情况下是关闭的,开启慢日志查询,这里只针对当前session,重启后失效。推荐使用修改配置文件my.cnf (方法二)
3. 查需慢查询记录的时间
show variables like 'long%';
//默认为10秒
4. 重设慢查询记录时间
set long_query_time=1
5. 重设慢查询日志地址
set global slow_query_log_file='/mysqllog/slow_log/slow_queries_3306_new.log';
6. 测试慢查询sql
select sleep(10) as a, 1 as b;
//执行sql后,可以在D:\phpStudy\MySQL\data\DESKTOP-IFGFGUV-slow.log,文件中查看具体情况
方法二
在服务器上找到mysql的配置文件my.cnf , 然后再mysqld模块里追加一下内容;记得需要重启!
vim my.cnf
[mysqld]
slow_query_log = ON
#定义慢查询日志的路径
slow_query_log_file = /tmp/slow_querys.log
#定义查过多少秒的查询算是慢查询,我这里定义的是1秒,5.6之后允许设置少于1秒,例如0.1秒
long_query_time = 1
慢日志相关设置
slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
慢日志分析
tail -f slow_query.log
查看mysql的慢查询日志
tail -f slow_query.log
# Time: 110107 16:22:11
# User@Host: root[root] @ localhost []
# Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774
SET timestamp=1294388531;
select sleep(10) as a, 1 as b;
解析:
第一行,SQL查询执行的时间 第二行,执行SQL查询的连接信息,用户和连接IP 第三行,记录了一些我们比较有用的信息,如下解析:
Query_time,这条SQL执行的时间,越长则越慢
Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent,查询返回的行数
Rows_examined,查询检查的行数,越长就当然越费时间
第四行,设置时间戳,没有实际意义,只是和第一行对应执行时间。
第五行及后面所有行(第二个# Time:之前),执行的sql语句记录信息,因为sql可能会很长。
使用mysql程序自带的mysqldumpslow命令分析
虽然慢查询日志已经够清晰,但是往往我们的日志记录到的不是只有一条sql,可能有很多很多条,如果不加以统计,估计要看到猴年马月,这个时候就需要做统计分析了。
mysqldumpslow -s c -t 10 /tmp/slow-log
这会输出记录次数最多的10条SQL语句,得出的结果和上面一般慢查询记录的格式没什么太大差别,这里就不展开来详细解析了。
参数解析:
-s:是表示按照何种方式排序,子参数如下:
c、t、l、r:分别是按照记录次数、时间、查询时间、返回的记录数来排序,
ac、at、al、ar:表示相应的倒叙;
-t:返回前面多少条的数据,这里意思就是返回10条数据了(也可以说是前十)
-g:后边可以写一个正则匹配模式,大小写不敏感的,比如:
/path/mysqldumpslow -s r -t 10 /tmp/slow-log,得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log,得到按照时间排序的前10条里面含有左连接的查询语句。