mysql开启慢sql查询

1.开启慢sql查询

a.是否开启慢查询日志

show variables  like '%slow_query%';
-------------------------------------------------------------------
slow_query_log	ON
slow_query_log_file	/var/lib/mysql/iZm5ef0xuq9rbzct63t5kmZ-slow.log

set global slow_query_log=1; 1开启 0关闭

2.慢sql自定义设置

a.默认超过10s记录到日志文件中

show variables like 'long_query_time%';
---------------------------------------
long_query_time	4.000000

set global long_query_time=4;设置成自己想定义的秒数。

3.慢sql存储

a.有文件和表两种存储方式，默认文件，效率高，但是不方便查看

show variables like '%log_output%';
-----------------------------------
log_output	TABLE

set global log_output='TABLE';可以设置为表存储

4.测试慢sql

select sleep(6);

select * from mysql.slow_log;
---------------------------------------------------------------------------
2019-11-18 11:22:04.913907	root[root] @  [112.255.31.104]	00:00:05.000356	00:00:00.000000	1	1	blog	0	0	1	(BLOB) 15 bytes	1035
2019-11-18 11:23:47.604081	root[root] @  [112.255.31.104]	00:00:05.000335	00:00:00.000000	1	1	blog	0	0	1	(BLOB) 15 bytes	1035
2019-11-18 11:27:.9030	root[root] @  [112.255.31.104]	00:00:06.000381	00:00:00.000000	1	1	blog	0	0	1	(BLOB) 15 bytes	1035

注：blob类型可以通过navicat工具栏的editor查看