理解mysql的slow log

PS：所有的前提，慢查询时间设置为3s

1 log_output参数为TABLE时，慢查询记录到mysql.slow_log表里，但这时这个系统表没有任何索引，我们一般可以在start_time列自行加上索引方便检索，类似这样

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

2 start_time指的是SQL结束时间，而不是开始执行时间。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-06-13 11:34:12 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sleep(20);
+-----------+
| sleep(20) |
+-----------+
|         0 |
+-----------+
1 row in set (19.99 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-06-13 11:34:47 |
+---------------------+
1 row in set (0.00 sec)

   start_time: 2016-06-13 11:34:46
     user_host: ucloudbackup[ucloudbackup] @  [10.10.249.91]
    query_time: 00:00:20
     lock