MySQL慢查詢

MySQL慢查詢

慢查詢（slow log）可以協助我們定位到特定的SQL語句進行SQL語句層面的最佳化，例如，慢查詢日誌會記錄那些執行時間超過給定值得SQL語句，從而定位到問題的所在。

開啟慢查詢

查看MySQL資料庫執行個體關於慢查詢的參數

1. mysql> show variables like '%slow%'; 
2. +---------------------------+-----------------------------------+  
3. | Variable_name             | Value                             | 
4. +---------------------------+-----------------------------------+  
5. | log_slow_admin_statements | OFF                               | 
6. | log_slow_slave_statements | OFF                               | 
7. | slow_launch_time          | 2                                 | 
8. | slow_query_log            | OFF                               | 
9. | slow_query_log_file       | /var/lib/mysql/localhost-slow.log | 
10. +---------------------------+-----------------------------------+  
11. 5 rows in set (0.00 sec) 
12.  
13. mysql> show variables like '%long_query_time%'; 
14. +-----------------+-----------+  
15. | Variable_name   | Value     | 
16. +-----------------+-----------+  
17. | long_query_time | 10.000000 | 
18. +-----------------+-----------+  
19. 1 row in set (0.02 sec) 

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秒，查看慢查詢日誌的例子

1. mysql> set global slow_query_log=ON; 
2. Query OK, 0 rows affected (0.03 sec) 
3.  
4. mysql> set long_query_time=2; 
5. Query OK, 0 rows affected (0.00 sec) 
6.  
7. mysql> show variables like '%long_query_time%'; 
8. +-----------------+----------+  
9. | Variable_name   | Value    | 
10. +-----------------+----------+  
11. | long_query_time | 2.000000 | 
12. +-----------------+----------+  
13. 1 row in set (0.00 sec) 
14.  
15. mysql> select sleep(3); 
16. +----------+  
17. | sleep(3) | 
18. +----------+  
19. |        0 | 
20. +----------+  
21. 1 row in set (3.00 sec) 
22.  
23. mysql> system cat /var/lib/mysql/localhost-slow.log 
24. /usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with: 
25. Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock 
26. Time                 Id Command    Argument 
27. # Time: 150102 16:56:54 
28. # User@Host: root[root] @ localhost []  Id:     1 
29. # Query_time: 3.001084  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0 
30. use test2; 
31. SET timestamp=1420189014; 
32. select sleep(3); 

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.sockTime                 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: 0use test2;SET timestamp=1420189014;select sleep(3);

慢查詢還有一個參數log_queries_not_using_indexes,用來表示每分鐘允許記錄到慢查詢日誌的未使用索引的SQL語句的次數，預設為0，表示沒有限制，但是生產環境中，這類語句會大量佔據慢查詢日誌的大小，給DBA的分析帶來困擾。 

除了使用記錄檔查看慢查詢日誌，還可以使用表的形式查看。在資料庫名為mysql的模式下，有一張叫做slow_log的表，可以記錄慢查詢的輸出，只需改變日誌的輸出模式log_output，參數log_output是全域的動態，可以在運行時動態改變。

1. mysql> show variables like '%log_output%'; 
2. +---------------+-------+  
3. | Variable_name | Value | 
4. +---------------+-------+  
5. | log_output    | FILE  | 
6. +---------------+-------+  
7. 1 row in set (0.00 sec) 
8.  
9. mysql> set global log_output='table'; 
10. Query OK, 0 rows affected (0.00 sec) 
11.  
12. mysql> select sleep(3); 
13. +----------+  
14. | sleep(3) | 
15. +----------+  
16. |        0 | 
17. +----------+  
18. 1 row in set (3.00 sec) 

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，對於查詢的效率可能不是很高，但是作為日誌的追加插入效率很高。

1. mysql> show create table slow_log\G 
2. *************************** 1. row *************************** 
3.        Table: slow_log 
4. Create Table: CREATE TABLE `slow_log` ( 
5.   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
6.   `user_host` mediumtext NOT NULL, 
7.   `query_time` time NOT NULL, 
8.   `lock_time` time NOT NULL, 
9.   `rows_sent` int(11) NOT NULL, 
10.   `rows_examined` int(11) NOT NULL, 
11.   `db` varchar(512) NOT NULL, 
12.   `last_insert_id` int(11) NOT NULL, 
13.   `insert_id` int(11) NOT NULL, 
14.   `server_id` int(10) unsigned NOT NULL, 
15.   `sql_text` mediumtext NOT NULL, 
16.   `thread_id` bigint(21) unsigned NOT NULL 
17. ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' 

mysql> show create table slow_log\G*************************** 1. row ***************************       Table: slow_logCreate 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語句記錄到慢查詢日誌中去。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：