root@localhost[(none)]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
--从下面的查询中可知，2个系统变量log_slow_queries，slow_query_log同时被置为on
root@localhost[(none)]> show variables like '%slow%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/suse11b-slow.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 :
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)