慢查询日志

• 是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
• 通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

慢日志设置 

• long_query_time：设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
• slow_query_log：指定是否开启慢查询日志（on表示开启，off表示关闭）
• slow_query_log_file：指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
• min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，如果是全盘扫描的语句同样速度很慢，所以不建议设置该配置
• log_queries_not_using_indexes：记录不使用索引的慢查询日志

配置实例 

[mysqld]
slow_query_log=on 
slow_query_log_file=/application/mysql/data/slow.log
long_query_time=0.5
log_queries_not_using_indexes

配置以上信息后重启MySQL服务，查看县相关信息

mysql> show variables like '%slow%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| log_slow_admin_statements | OFF                              |
| log_slow_slave_statements | OFF                              |
| slow_launch_time          | 2                                |
| slow_query_log            | ON                               |
| slow_query_log_file       | /application/mysql/data/slow.log |
+---------------------------+----------------------------------+
5 rows in set (0.00 sec)

mysql> show variables like '%long%';
+--------------------------------------------------------+----------+
| Variable_name                                          | Value    |
+--------------------------------------------------------+----------+
| long_query_time                                        | 0.500000 |
| performance_schema_events_stages_history_long_size     | 10000    |
| performance_schema_events_statements_history_long_size | 10000    |
| performance_schema_events_waits_history_long_size      | 10000    |
+--------------------------------------------------------+----------+
4 rows in set (0.00 sec)

mysql> show variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | ON    |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
2 rows in set (0.00 sec)

这时，我们做一下测试。

mysql> select sleep(2);
mysql> select sleep(3);
mysql> select sleep(1);
mysql> select sleep(2);
mysql> select sleep(4);
mysql> select sleep(2);
mysql> select * from city;
mysql> select * from city where id <100;
mysql> select * from city where id <50;
mysql> select * from city where id <400;
mysql> select * from city;

然后再去/application/mysql/data/slow.log中查看日志：

[root@db01 data]# cat slow.log 
/application/mysql/bin/mysqld, Version: 5.6.36-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /application/mysql/tmp/mysql.sock
Time                 Id Command    Argument
# Time: 190814 16:10:36
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 2.001043  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770236;
select sleep(2);
# Time: 190814 16:10:42
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 3.000600  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770242;
select sleep(3);
# Time: 190814 16:10:49
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 2.000931  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770249;
select sleep(2);
# Time: 190814 16:10:53
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 1.001557  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770253;
select sleep(1);
# Time: 190814 16:11:00
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 2.000891  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770260;
select sleep(2);
# Time: 190814 16:11:07
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 4.001174  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770267;
select sleep(4);
# Time: 190814 16:11:14
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 2.000609  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1565770274;
select sleep(2);
# Time: 190814 16:12:19
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 0.378485  Lock_time: 0.000132 Rows_sent: 4079  Rows_examined: 4079
use world;
SET timestamp=1565770339;
select * from city;
# Time: 190814 16:12:33
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 0.001041  Lock_time: 0.000049 Rows_sent: 239  Rows_examined: 239
SET timestamp=1565770353;
select * from country;
[root@db01 data]# 

或者通过MySQL自带的工具mysqldumpslow命令更简便的查看

mysqldumpslow -s c -t 10 /application/mysql/data/slow.log   #按照次数，最多10条记录
mysqldumpslow -s at -t 10 /application/mysql/data/slow.log   #按照时间倒叙，最多10条记录

-s：是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t：是top n的意思，即为返回前面多少条的数据；

------------ 扩展 ---------------

关键字：pt-query-diagest    percona-toolkit   mysqlsla