mysql之 slow log 慢查詢日誌

一、 相關引數：

• slow_query_log
 ◦ 是否開啟慢查詢日誌

• slow_query_log_file
 ◦ 慢查詢日誌檔名, 在 my.cnf 我們已經定義為slow.log，預設是 機器名 -slow.log

• long_query_time
 ◦ 制定慢查詢閾值, 單位是秒，且當版本 >=5.5.X ，支援毫秒。例如 0.5 即為 500ms
 ◦ 大於 該值，不包括值本身。例如該值為2，則執行時間正好 等於 2的SQL語句 不會記錄

• log_queries_not_using_indexes
 ◦ 將沒有使索引的SQL記錄到慢查詢日誌
    ◾ 如果一開始因為資料少，查錶快，耗時的SQL語句沒被記錄，當資料量大時，該SQL可能會執行很長時間
    ◾ 需要測試階段就要發現問題，減小上線後出現問題的桜率

• log_throttle_queries_not_using_indexes
 ◦ 限制每分鐘內，在慢查詢日誌中，去記錄沒有使用索引的SQL語句的次數；版本需要 >=5.6.X
    ◾ 因為沒有使用索引的SQL可能會短時間重複執行，為了避免日誌快速增大，限制每分鐘的記錄次數

• min_examined_row_limit
 ◦ 掃描記錄少於改值的SQL不記錄到慢查詢日誌
    ◾ 結合去記錄沒有使用索引的SQL語句的例子，有可能存在某一個表，資料量維持在幾行左右，且沒有建立索引。這種表即使不建立索引，查詢也很快，掃描記錄很小，如果確定有這種表，則可以透過此引數設定，將這個SQL不記錄到慢查詢日誌。

• log_slow_admin_statements
 ◦ 記錄超時的管理操作SQL到慢查詢日誌，比如ALTER/ANALYZE TABLE

• log_output
 ◦ 慢查詢日誌的栺式，[FILE | TABLE | NONE]，預設是FILE；版本 >=5.5
 ◦ 如果設定為TABLE，則記錄的到 mysql.slow_log

• log_slow_slave_statements
 ◦ 在從伺服器上開啟慢查詢日誌

• log_timestamps     5.7
 ◦ 寫入時區資訊。可根據需求記錄UTC時間或者伺服器本地系統時間

二、mysqldumpslow工具使用
  
  如果線上上操作，不需要 mysqldumpslow 去掃整個 slow.log ， 可以去 tail -n 10000 slow.log > last_10000_slow.log (10000 這個數字根據實際情況進行調整 ),然後進行 mysqldumpslow last_10000_slow.log

三、慢查詢日誌存入表

-- 在my.cnf 中增加 log_output = TABLE，開啟slow_query_log選項，然後重啟資料庫例項
--
mysql> show variables like "log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "slow_query_log";
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
mysql> select * from mysql.slow_log;
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| 2015-11-20 19:50:28.574677 | root[root] @ localhost [] | 00:00:04.000306 | 00:00:00.000000 | 1 | 0 | | 0 | 0 | 11 | select sleep(4) | 3 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show create table mysql.slow_log;
--
-- 表結構輸出省略
-- 關鍵一句如下：
--
ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' -- ENGINE=CSV 這裡使用的是CSV的引擎,效能較差
-- 建議將slow_log表的儲存引擎改成MyISAM
mysql> alter table mysql.slow_log engine = myisam;
ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled '-- 提示我正在記錄日誌中，不能轉換
mysql> set global slow_query_log = 0; -- 先停止記錄日誌
Query OK, 0 rows affected (0.01 sec)
mysql> alter table mysql.slow_log engine = myisam; -- 然後轉換表的引擎
Query OK, 2 rows affected (5.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> set global slow_query_log = 1; -- 再開啟記錄日誌
Query OK, 0 rows affected (0.00 sec)
mysql> show create table mysql.slow_log;
--
-- 表結構輸出省略
-- 關鍵一句如下：
--
ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' -- ENGINE 變成了MyISAM
使用 TABLE 的優勢在於方便查詢，但是記住當在備份的時候，不要備份慢查詢日誌的表，避免備份過大。
使用 FILE 也可以，需要定時清除該檔案，避免單檔案過大。