MySQL慢查询

慢查询（slow log）可以帮助我们定位到特定的SQL语句进行SQL语句层面的优化，例如，慢查询日志会记录那些执行时间超过给定值得SQL语句，从而定位到问题的所在。

开启慢查询

查看MySQL数据库实例关于慢查询的参数

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | OFF                               |
| slow_query_log_file       | /var/lib/mysql/localhost-slow.log |
+---------------------------+-----------------------------------+
5 rows in set (0.00 sec)

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

其中，

slow_query_log便是开启慢查询的参数

slow_query_log_file是慢查询日志文件的路径

log_query_time是查询时间的最大值，超过这个时间就会被慢查询日志记录。

下面是开始慢查询，然后将查询时间最大值设为2秒，执行sleep3秒，查看慢查询日志的例子

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.03 sec)

mysql> set long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> system cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 150102 16:56:54
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 3.001084  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test2;
SET timestamp=1420189014;
select sleep(3);

慢查询还有一个参数log_queries_not_using_indexes,用来表示每分钟允许记录到慢查询日志的未使用索引的SQL语句的次数，默认为0，表示没有限制，但是生产环境中，这类语句会大量占据慢查询日志的大小，给DBA的分析带来困扰。

除了使用日志文件查看慢查询日志，还可以使用表的形式查看。在数据库名为mysql的模式下，有一张叫做slow_log的表，可以记录慢查询的输出，只需改变日志的输出模式log_output，参数log_output是全局的动态的，可以在运行时动态的改变。

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

mysql> set global log_output='table';
Query OK, 0 rows affected (0.00 sec)

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

指的注意的是slow_log这张表的引擎室CSV，对于查询的效率可能不是很高，但是作为日志的追加插入效率很高。

mysql> show create table 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,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

关于慢查询的另外一个有用的参数是long_query_io表示将超过指定逻辑IO次数（逻辑IO包含物理IO，表示物理IO和缓冲池读取次数之和）的SQL语句记录到慢查询日志中去。