一、开启mysql慢查询日志功能
1、查看是否开启 未使用索引的SQL记录日志查询
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)
开启 未使用索引的SQL记录日志查询
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
2、查看超过多长时间的查询记入慢查询日志中
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
默认10s，为做测试，修改为0，即记录所有。
mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)
ps：需要断开重新连接，才会查看到更新的状态
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
3、查看是否开启 mysql慢查询日志功能
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
开启慢查询日志功能
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.01 sec)
4、查看日志记录位置
mysql> show variables like 'slow_query_log_file';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/bogon-slow.log |
+---------------------+--------------------------------------+
1 row in set (0.00 sec)
5、执行若干SQL语句，看是否记入日志
查看最后20条记录如下，成功记录了最后20条SQL执行语句。
[root@bogon ~]# tail -20 /usr/local/mysql/data/bogon-slow.log
# Time: 190115 17:46:21
# User@Host: root[root] @ localhost []
# Query_time: 0.000329 Lock_time: 0.000166 Rows_sent: 40 Rows_examined: 40
SET timestamp=1547545581;
show tables;
# Time: 190115 17:46:36
# User@Host: root[root] @ localhost []
# Query_time: 0.054437 Lock_time: 0.000115 Rows_sent: 104 Rows_examined: 104
SET timestamp=1547545596;
select * from TABLES;
# Time: 190115 17:49:12
# User@Host: root[root] @ localhost []
# Query_time: 0.001611 Lock_time: 0.000705 Rows_sent: 21 Rows_examined: 21
SET timestamp=1547545752;
desc TAbles;
# Time: 190115 17:49:41
# User@Host: root[root] @ localhost []
# Query_time: 0.002142 Lock_time: 0.000056 Rows_sent: 104 Rows_examined: 104
SET timestamp=1547545781;
select VERSION from Tables;
6、慢查日志的存储格式
主要有五部分组成
# Time: 190115 17:54:44 执行时间
# User@Host: root[root] @ localhost [] 执行主机
# Query_time: 0.000073 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 执行信息
SET timestamp=1547546084; 时间戳
select database(); 执行语句
二、慢查询日志分析工具
1、mysql自带的 mysqldumpslow工具
使用mysqldumpslow查看最近6条记录
[root@bogon ~]# mysqldumpslow -t 6 /usr/local/mysql/data/bogon-slow.log Reading mysql slow query log from /usr/local/mysql/data/bogon-slow.log
Count: 1 Time=0.05s (0s) Lock=0.00s (0s) Rows=104.0 (104), root[root]@localhost
select * from TABLES Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select databas() Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=104.0 (104), root[root]@localhost
select VERSION from Tables Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=21.0 (21), root[root]@localhost
desc TAbles Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
show variables like 'S' Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=40.0 (80), root[root]@localhost
show tables
2、pt-query-digest分析工具
1）安装
[root@bogon ~]# wget wget percona.com/get/pt-query-digest
[root@bogon ~]# chmod u+x pt-query-digest
[root@bogon ~]# mv /root/pt-query-digest /usr/bin/
2）启动出现错误提示
[root@bogon bin]# pt-query-digest --help
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/pt-query-digest line 2470.
BEGIN failed--compilation aborted at /usr/bin/pt-query-digest line 2470.
安装perl-Digest-MD5即可
[root@bogon bin]# yum install perl-Digest-MD5
