原文网址：https://www.youyong.top/article/11593f61bb3be
mysql能记录下所有执行超过long_query_time时间的SQL语句, 帮我们找到执行慢的SQL, 方便我们对这些SQL进行优化。
原文网址：https://www.youyong.top/article/11593f61bb3be
查看目前慢查询日志的情况
show variables like '%log_output%'; //默认是FILE
show variables like '%quer%';
log_output 默认是FILE，表示慢查询日志输入至日志文件，可以通过set修改输出为TABLE
log_queries_not_using_indexes 默认是OFF，表示是否记录没有使用索引的查询
slow_query_log 默认是OFF，表示是否打开慢查询
long_query_time默认是 10.000000，表示记录超过时间的慢查询打开慢查询，记录查询时间超过5秒的慢查询，将慢查询结果输出至slow_log表中
set global slow_query_log = ON;
SET GLOBAL long_query_time = 5; //10.000000
SET GLOBAL log_queries_not_using_indexes = ON; //是否打开看个人需要
set global log_output='TABLE'; // FILE
select * from MySQL.slow_log order by start_time desc;
恢复慢查询的参数设置情况
SET global slow_query_log = OFF;
SET GLOBAL long_query_time = 10.000000;
SET GLOBAL log_queries_not_using_indexes = OFF;
SET global log_output='FILE'; // FILE
show variables like '%slow%';
+---------------------+------------------------------------------+
| Variable_name |Value |
+---------------------+------------------------------------------+
| log_slow_queries| ON |
| slow_launch_time| 2 |
| slow_query_log |ON |
|slow_query_log_file | /data/mysql/slow_queries_3306.log |
+---------------------+------------------------------------------+
4 rows in set(0.00 sec)
show variables like 'log_output%';
log_output FILE
将慢查询输出到文件并修改日志文件目录
set global slow_query_log=0;
show variables like '%slow%';
+---------------------+------------------------------------------+
| Variable_name |Value |
+---------------------+------------------------------------------+
| log_slow_queries| OFF |
| slow_launch_time| 2 |
| slow_query_log |OFF |
|slow_query_log_file | /data/mysql/slow_queries_3306.log |
+---------------------+------------------------------------------+
4 rows in set(0.00 sec)
set global slow_query_log_file='/data/mysql/new-slow.log';
Query OK, 0 rows affected (0.03 sec)
set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
show variables like '%slow%';
+---------------------+----------------------------------------------+
| Variable_name |Value |
+---------------------+----------------------------------------------+
| log_slow_queries| ON |
| slow_launch_time| 2 |
| slow_query_log |ON |
|slow_query_log_file | /data/mysql/new_slow.log |
+---------------------+----------------------------------------------+
4 rows in set(0.00 sec)
show variables like '%log_output%'; // 目前是FILE
查看日志文件
[mysql@xxx-xxx ~]$ more /data/mysql/new-slow.log
6, backup the oldbig slow log file to other directory.
清空slow_log表
select * from mysql.slow_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;
CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
`user_host`mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time`time NOT NULL,
`rows_sent`int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db`varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id`int(11) NOT NULL,
`server_id`int(10) unsigned NOT NULL,
`sql_text`mediumtext NOT NULL
) ENGINE=CSVDEFAULT CHARSET=utf8 COMMENT='Slow log';
SET GLOBAL slow_query_log = 'ON';
DROP TABLE mysql.slow_log_drop;
