DB研討會 : [轉貼]如何利用 MySQL 攻破數據庫性能瓶頸？

作者 | 阿文

責編 | 郭芮

今天和大家分享一下 MySQL 的慢日誌。

在實際工作中，我面對很多用戶會經常碰到一個問題，那就是在使用 MySQL 時，執行一條語句需要很長時間返回，這是為什麼呢？當你在使用 MySQL 中，當發現一些性能問題的時，比如執行一個語句要很長時間才返回結果，我們稱之為慢查詢，一般來說，發生慢查詢的原因有：

• 你的索引設計有問題，可能會導致每次執行語句都是全表掃瞄，這樣很耗費時間；

• 你的 SQL 語句沒有寫好，可能會導致查詢時間變長；

• MySQL 選擇了錯誤的索引，同樣會導致全表掃瞄。

通常情況下，在業務上線之前，會在測試環境裡面，把 MySQL 的慢查詢打開，也就是把 longquerytime 設置為 0，這樣確保每一條語句都被記錄到慢日誌中去，具體的配置可以參考下文。

MySQL 的慢查詢日誌是MySQL提供的一種日誌記錄，它是用來記錄在MySQL中響應時間超過閥值的語句。系統默認情況下， MySQL 並不啟動慢查詢日誌，需要我們手動來設置這個參數，當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響。

慢查詢日誌支持將日誌記錄寫入文件，也支持將日誌記錄寫入數據庫表。

慢查詢配置

默認情況下slowquerylog的值為OFF，表示慢查詢日誌是禁用的，可以通過設置slowquerylog的值來開啟，如下所示：

in set (0.00 sec)

開啟慢查詢日誌：

0.00 sec)

然後看狀態：

in set (0.00 sec)

使用set global slowquerylog=1開啟了慢查詢日誌只對當前數據庫生效，如果MySQL重啟後則會失效。如果要永久生效，就必須修改配置文件my.cnf（其它系統變量也是如此）。

例如如下所示：

slow_query_log_file=/var/lib/mysql/mysql-slow.log

參數說明：

• slowquerylog 慢查詢開啟狀態；

• slowquerylog_file 慢查詢日誌存放的位置；

• longquerytime 查詢超過多少秒才記錄。

那麼開啟了慢查詢日誌後，什麼樣的SQL才會記錄到慢查詢日誌裡面呢？ 是否會被寫入到慢日誌是由參數longquerytime控制，默認情況下longquerytime的值為10秒，可以使用命令修改，也可以在my.cnf參數里面修改。關於運行時間正好等於longquerytime的情況，並不會被記錄下來。也就是說，在 MySQL
源碼裡是判斷大於longquerytime，而非大於等於。從MySQL 5.1開始，longquerytime開始以微秒記錄SQL語句運行時間，之前僅用秒為單位記錄。如果記錄到表裡面，只會記錄整數部分，不會記錄微秒部分。

查看long_time 值，默認是 10 秒。

00 sec)

設置值，例如這裡設置為 5 秒：

0.01 sec)

如上所示，修改了變量longquerytime，但是查詢變量longquerytime的值還是10。

使用命令 set global longquerytime=5修改後，需要重新連接或新開一個會話才能看到修改值。因為通過用show variables like 'longquerytime'查看是當前會話的變量值。

也可以不用重新連接會話，而是用 show global variables like 'longquerytime'; 如下所示：

00 sec)

我們設置longquerytime的值為2：

0.00 sec)

然後執行一條超時5秒的語句（需要重新連接 MySQL）：

5.01 sec)

然後查看慢日誌可以看到類似信息：

5);

logoutput 參數是指定日誌的存儲方式。logoutput='FILE'表示將日誌存入文件，默認值是'FILE'。log_output='TABLE'表示將日誌存入數據庫，這樣日誌信息就會被寫入到mysql.slowlog表中。MySQL數據庫支持同時兩種日誌存儲方式，配置的時候以逗號隔開即可，如：logoutput='FILE,TABLE'。日誌記錄到系統的專用日誌表中，要比記錄到文件耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲得更高的系統性能，那麼建議優先記錄到文件。

01 sec)

設置為表：

5.00 sec)

執行 select * from mysql.slowlog; 觀察慢查詢日誌裡每類語句的輸出，其中querytime表示query語句的執行時間，單位是為秒，lock time是鎖定的時間，rowssent是query語句執行返回的記錄數，而rowsexamined則是優化器估算的掃瞄行數，querytime、rowsexamined、rowssent 三個值可以大致衡量一條查詢的成本，特別留意 rowsexamined 字段是否與預期一致。

系統變量log-queries-not-using-indexes：未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。如果調優的話，建議開啟這個選項。另外，開啟了這個參數，其實使用full index scan的sql也會被記錄到慢查詢日誌。

0.00 sec)

    mysql>

系統變量logslowadmin_statements表示是否將慢管理語句例如ANALYZE TABLE和ALTER TABLE等記入慢查詢日誌：

0.00 sec)

查詢有多少條慢日誌，可以使用系統變量：

0.01 sec)

日誌分析工具mysqldumpslow

MySQL 自帶了一個查看慢日誌的工具 mysqldumpslow，執行mysqldumpslow --help 可以查看其相關參數和說明：

time

參數解釋：

• -s:是表示按照何種方式排序；

• c: 訪問計數；

• l: 鎖定時間；

• r: 返回記錄；

• t: 查詢時間；

• al:平均鎖定時間；

• ar:平均返回記錄數；

• at:平均查詢時間；

• -t:是top n的意思，即為返回前面多少條的數據；

• -g:後邊可以寫一個正則匹配模式，大小寫不敏感的。

例如， 得到返回記錄集最多的10個SQL：

log

得到訪問次數最多的10個SQL：

log

通過以上方式我們可以配置和發現慢 SQL，那麼在發現慢 SQL 語句之後，我們可以通過對數據庫進行優化來提升 SQL 的執行速度，比如加索引或修改該應用的實現方式等。