mysql慢查询日志总结

开启慢查询日志

-- 查询慢查询日志开启状态
mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/data3-slow.log |
+---------------------+-------------------------------+
2 rows in set
-- 开启慢查询日志记录
set global slow_query_log=1;
-- 查询慢查询时间
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
-- 修改慢查询时间,修改后需要重新建立会话才能看到最新设置的值
set global long_query_time=5;

慢查询输出

-- 查看默认输出,默认输出到文件,性能较高
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set
-- 可以设置输出到表
set global log_output='TABLE';
select * from mysql.slow_log;

其它配置

-- 开启这个配置,可以将未使用索引的查询也记录到日志
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
-- 开启配置
mysql> set global log_queries_not_using_indexes=1;