[MySQL / MariaDB] 使用Slow Query Log來偵錯

當服務異常緩慢，想要debug有沒有可能是哪些SQL拖慢了整體速度時，可以使用MySQL或MariaDB內建的Slow Query Log將執行很久的SQL語句給記錄下來。

啟用slow query log的機制

> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.000 sec)

(此篇直接用SET GLOBAL VARIABLES的方式設定的變數在MySQL重新啟動後會回復預設值，如果想要永久修改必須帶在啟動參數或修改設定檔)

設定會被當成slow query的門檻值 (單位: 秒)

> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.000 sec)

設定slow query log機制的log儲存方式

> SET GLOBAL log_output = 'FILE';
Query OK, 0 rows affected (0.000 sec)

查看log存檔位置

> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log_file | ubuntu-slow.log |
+---------------------+-----------------+
1 row in set (0.001 sec)

好了以後，我們來測試看看當發生slow query時有沒有被資料庫正確記錄下來

> SELECT SLEEP(6);
+----------+
| SLEEP(6) |
+----------+
|        0 |
+----------+
1 row in set (6.000 sec)

打開slow query log的存檔 /var/lib/mysql/ubuntu-slow.log (根據作業系統每個人路徑不一定跟此篇一樣)，我們可以看到剛剛的slow query被正確的記錄了:)

...
# Time: 210615 17:56:55
# User@Host: root[root] @ localhost []
# Thread_id: 11694  Schema:   QC_hit: No
# Query_time: 6.000225  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1652003815;
SELECT SLEEP(6);

最後偵錯完畢時可以將它關閉

> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.000 sec)