MySQL 慢查询日志(Slow Query Log)(二)

root@localhost[tempdb]> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

--为便于演示，我们将全局和session级别long_query_time设置为1
root@localhost[tempdb]> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

root@localhost[tempdb]> set session long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

--Author : Leshami
--Blog : http://blog.csdn.net/leshami

root@localhost[tempdb]> create table tb_slow as select * from information_schema.columns;
Query OK, 829 rows affected (0.10 sec)
Records: 829 Duplicates: 0 Warnings: 0

root@localhost[tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 829 rows affected (0.05 sec)
Records: 829 Duplicates: 0 Warnings: 0
.....为便于演示，我们插入一些数据，中间重复过程省略
root@localhost[tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 26528 rows affected (4.40 sec)
Records: 26528 Duplicates: 0 Warnings: 0

root@localhost[tempdb]> system tail /var/lib/mysql/suse11b-slow.log
/usr/sbin/mysqld, Version: 5.5.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 141004 22:05:48
# User@Host: root[root] @ localhost []
# Query_time: 4.396858 Lock_time: 0.000140 Rows_sent: 0 Rows_examined: 53056
use tempdb;
SET timestamp=1412431548;
insert into tb_slow select * from tb_slow;

....再次插入一些记录....
root@localhost[tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 212224 rows affected (37.51 sec)
Records: 212224 Duplicates: 0 Warnings: 0

root@localhost[tempdb]> select table_schema,table_name,count(*) from tb_slow
-> group by table_schema,table_name order by 3,2;
+--------------------+----------------------------------------------+----------+
| table_schema | table_name | count(*) |
+--------------------+----------------------------------------------+----------+
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | 1024 |
| performance_schema | cond_instances | 1024 |
...........
| mysql | user | 21504 |
+--------------------+----------------------------------------------+----------+
83 rows in set (1.58 sec)

root@localhost[tempdb]> system tail /var/lib/mysql/suse11b-slow.log
# User@Host: root[root] @ localhost []
# Query_time: 37.514172 Lock_time: 0.000123 Rows_sent: 0 Rows_examined: 424448
SET timestamp=1412431806;
insert into tb_slow select * from tb_slow;
# Time: 141004 22:10:47
# User@Host: root[root] @ localhost []
# Query_time: 1.573293 Lock_time: 0.000183 Rows_sent: 83 Rows_examined: 424614
SET timestamp=1412431847;
select table_schema,table_name,count(*) from tb_slow --这条SQL被记录下来了，其查询时间为1.573293s
group by table_schema,table_name order by 3,2;

root@localhost[tempdb]> show variables like '%log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+------------------