1. 开启慢查询日志

mysql> show variables like 'slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /u01/mysql/mysqldb-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

修改/etc/my.cnf，加入慢查询参数
slow_query_log=1
slow_query_log_file=/u01/mysql/mysqldb-slow.log

重启数据库
[root@mysqldb mysql]# service mysql restart
Shutting down MySQL...[ OK ]
Starting MySQL.[ OK ]

mysql> show variables like 'slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /u01/mysql/mysqldb-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.01 sec)

2.慢查询相关的参数long_query_time
当查询超过long_query_time指定的时间，那么就会记录在慢查询日志文件中，默认是10秒
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

修改此参数，设置为1秒
mysql> set session long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> create table t as select * from information_schema.tables;
Query OK, 90 rows affected (0.23 sec)
Records: 90 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 90 rows affected (0.03 sec)
Records: 90 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 180 rows affected (0.04 sec)
Records: 180 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 360 rows affected (0.08 sec)
Records: 360 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 720 rows affected (0.09 sec)
Records: 720 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 1440 rows affected (1.82 sec)
Records: 1440 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 2880 rows affected (0.13 sec)
Records: 2880 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 5760 rows affected (0.14 sec)
Records: 5760 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 11520 rows affected (0.21 sec)
Records: 11520 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t;
Query OK, 23040 rows affected (1.75 sec)
Records: 23040 Duplicates: 0 Warnings: 0

查看慢查询日志内容，发现执行耗时超过1秒的sql已经被记录进去：

Time Id Command Argument
# Time: 150817 11:25:13
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 1.820071 Lock_time: 0.000196 Rows_sent: 0 Rows_examined: 2880
use test;
SET timestamp=1439825113;
insert into t select * from t;
# Time: 150817 11:25:21
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 1.758488 Lock_time: 0.000135 Rows_sent: 0 Rows_examined: 46080
SET timestamp=1439825121;
insert into t select * from t;
~

3. 慢查询相关的参数log_queries_not_using_indexes
mysql> show variables like '%log_queries_not%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_queries_not%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show index from t;
Empty set (0.03 sec)

mysql> select * from t where table_name='xxxxxxx';
Empty set (0.16 sec)

查看慢查询日志，发现已经记录下这个没有使用索引的sql：

# Time: 150817 11:39:58
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 0.158257 Lock_time: 0.084753 Rows_sent: 0 Rows_examined: 46080
SET timestamp=1439825998;
select * from t where table_name='xxxxxxx';

4.mysqldumpslow用来分析sql语句慢查询

[root@mysqldb mysql]# mysqldumpslow -s t -t 2 mysqldb-slow.log

Reading mysql slow query log from mysqldb-slow.log
Count: 2 Time=1.79s (3s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t select * from t

Count: 1 Time=0.42s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select count(*) from emp

ITPub博客

mysql慢查询日志

最新文章