如何利用 MySQL 攻破資料庫性能瓶頸？

知識 06-24

打開今日頭條，查看更多圖片

作者 | 阿文

責編 | 郭芮

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

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

你的索引設計有問題，可能會導致每次執行語句都是全表掃描，這樣很耗費時間；
你的 SQL 語句沒有寫好，可能會導致查詢時間變長；
MySQL 選擇了錯誤的索引，同樣會導致全表掃描。

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

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

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

如何利用 MySQL 攻破資料庫性能瓶頸？

慢查詢配置

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

開啟慢查詢日誌：

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

然後看狀態：

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

例如如下所示：

[root@mysql ~]# vim /etc/my.cnf
slow_query_log=1
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 秒。

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

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

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

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

我們設置longquerytime的值為2：

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

[root@mysql ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> show variables like "long_query_time";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.02 sec)
mysql> select sleep(5)
-> ;
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.01 sec)

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

[root@mysql ~]# cat /var/lib/mysql/mysql-slow.log
/usr/sbin/mysqld, Version: 5.7.21 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.7.21-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2018-02-09T06:59:47.782111Z
# User@Host: root[root] @ localhost [] Id: 4
# Query_time: 5.000252 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1518159587;
select sleep(5);

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

設置為表：

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

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

mysql> show variables like "log_queries_not_using_indexes";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "log_queries_not_using_indexes";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql>

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

mysql> show variables like "log_slow_admin_statements";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> set global log_slow_admin_statements=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like "log_slow_admin_statements";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_admin_statements | ON |
+---------------------------+-------+
1 row in set (0.00 sec)

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

如何利用 MySQL 攻破資料庫性能瓶頸？

日誌分析工具mysqldumpslow

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

[root@mysql ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), "at" is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don"t abstract all numbers to N and strings to "S"
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is "*", i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don"t subtract lock time from total time

參數解釋：

-s:是表示按照何種方式排序；
c: 訪問計數；
l: 鎖定時間；
r: 返回記錄；
t: 查詢時間；
al:平均鎖定時間；
ar:平均返回記錄數；
at:平均查詢時間；
-t:是top n的意思，即為返回前面多少條的數據；
-g:後邊可以寫一個正則匹配模式，大小寫不敏感的。

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

mysqldumpslow -s r -t 10 /mysql/mysql_slow.log

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

mysqldumpslow -s c -t 10 /mysql/mysql_slow.log

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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！

請您繼續閱讀更多來自 CSDN 的精彩文章:

※少林寺步入 5G 時代
※史上最強最貴Mac Pro誕生，iPadOS 和 iOS 分家！WWDC19 全面總結

TAG:CSDN |