| Variable_name | Value |
|---|---|
| long_query_time | 1.000000 |
| slow_query_log | ON |
| slow_query_log_file | D:\MySQL\mysql-5.6.31-winx64\data\zhoupq_sh-slow.log |
概述
資料庫查詢快慢是影響專案效能的一大因素，對於資料庫，我們除了要優化 SQL，更重要的是得先找到需要優化的 SQL。如何找到低效的 SQL 是寫這篇文章的主要目的。
MySQL 資料庫有一個“慢查詢日誌”功能，用來記錄查詢時間超過某個設定值的SQL，這將極大程度幫助我們快速定位到癥結所在，以便對症下藥。至於查詢時間的多少才算慢，每個專案、業務都有不同的要求，傳統企業的軟體允許查詢時間高於某個值，但是把這個標準放在網際網路專案或者訪問量大的網站上，估計就是一個bug，甚至可能升級為一個功能性缺陷。
為避免誤導讀者，特申明本文的討論限制在 Win 64位 + MySQL 5.6 範圍內。其他平臺或資料庫種類及版本，我沒有嘗試過，不做贅述。
設定日誌功能
關於慢查詢日誌，主要涉及到下面幾個引數：
- slow_query_log ：是否開啟慢查詢日誌功能（必填）
- long_query_time ：超過設定值，將被視作慢查詢，並記錄至慢查詢日誌檔案中（必填）
- log-slow-queries ：慢查詢日誌檔案（不可填），自動在 \data\ 建立一個 [hostname]-slow.log 檔案
也就是說，只有滿足以上三個條件，“慢查詢功能”才可能正確開啟或關閉。
命令
以命令列的方式，設定引數，不需要重啟 MySQL 服務，注意許可權和引數作用域：
# slow_query_log 需要超級許可權
mysql> set global slow_query_log = ON;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
# 以管理員身份登入
D:\MySQL\mysql-5.6.31-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# slow_query_log 是全域性變數
mysql> set slow_query_log = ON;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.04 sec)
# 設定查詢“超時”時間
mysql> set GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)複製程式碼配置檔案
以配置檔案的方式設定，需要重啟 MySQL 服務。在 my.ini 中的 [mysqld] 下：
# 慢日誌相關配置
slow_query_log = ON
long_query_time = 1複製程式碼日誌路徑
慢查詢日誌檔案不可設定路徑，系統會自動建立 ：\data\[hostname]-slow.log。我在配置檔案中以多種形式定義了日誌問價路徑，均報錯：
# zhoupq_sh.err
[ERROR] MySQL: unknown variable 'log-slow-queries=/data/mysql-slow.log'
[ERROR] MySQL: unknown variable 'log-slow-queries=\data\mysql-slow.log'
[ERROR] MySQL: unknown variable 'log-slow-queries=D:\MySQL\mysql-5.6.31-winx64\data\mysql-slow.log'複製程式碼也許是我沒有找到合適的方法，但是自定義慢查詢日誌檔案這條路對我來說走不通，實際上，我不需要自定義這個檔案。
檢驗
mysql> show variables like '%query%';
+------------------------------+------------------------------------------------------+
| Variable_name | Value |
+------------------------------+------------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | D:\MySQL\mysql-5.6.31-winx64\data\zhoupq_sh-slow.log |
+------------------------------+------------------------------------------------------+
13 rows in set (0.00 sec)複製程式碼從結果中可以看出，slow_query_log 和 long_query_time 均以更新為設定值，slow_query_log_file 沒有指定，卻自動生成。
此時的 slow_query_log_file 是沒有資料的，除了一些系統資訊。接下來就改檢測一下“慢查詢日誌”是否能達到我們的要求。
慢查詢被記錄
我寫了幾條 SQL 語句，按照預先的設定，查詢時間超過 1s 的查詢將被寫入日誌。
SELECT t.* FROM subscribe t limit 0,1;
SELECT t.product, t.vendor, COUNT(DISTINCT t.id) AS vulcnt FROM temp t WHERE 1 = 1 GROUP BY t.vendor, t.product ORDER BY vulcnt DESC;複製程式碼 第一條 SQL 執行時間 0.001s：
第二條 SQL 執行時間大於 2s：
只有第二條 SQL 被“慢查詢日誌”記錄：
# Time: 170407 14:44:23
# User@Host: root[root] @ localhost [127.0.0.1] Id: 2
# Query_time: 2.133122 Lock_time: 0.106006 Rows_sent: 23878 Rows_examined: 160312
use testDB;
SET timestamp=1491547463;
SELECT
t.product,
t.vendor,
COUNT(DISTINCT t.id) AS vulcnt
FROM
temp t
WHERE
1 = 1
GROUP BY
t.vendor,
t.product
ORDER BY
vulcnt DESC;複製程式碼小結
記住三個引數：
- slow_query_log
- long_query_time
- log-slow-queries
log-slow-queries 不可自定義
- 生產環境請關閉“慢查詢日誌”功能，節約空間
- 命令方式 不需要重啟 MySQL 服務，而配置檔案方式 需要重啟