MySQL優(yōu)化：使用慢查詢(xún)?nèi)罩径ㄎ恍瘦^低的SQL語(yǔ)句
MySQL通過(guò)慢查詢(xún)?nèi)罩?/strong>定位那些執(zhí)行效率較低的SQL 語(yǔ)句，用--log-slow-queries[=file_name]選項(xiàng)啟動(dòng)時(shí)，mysqld 會(huì)寫(xiě)一個(gè)包含所有執(zhí)行時(shí)間超過(guò)long_query_time 秒的SQL語(yǔ)句的日志文件，通過(guò)查看這個(gè)日志文件定位效率較低的SQL 。
慢查詢(xún)?nèi)罩驹诓樵?xún)結(jié)束以后才記錄，所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查詢(xún)慢查詢(xún)?nèi)罩静⒉荒芏ㄎ粏?wèn)題，可以使用show processlist命令查看當(dāng)前MySQL在進(jìn)行的線(xiàn)程，包括線(xiàn)程的狀態(tài)、是否鎖表等，可以實(shí)時(shí)地查看SQL 的執(zhí)行情況，同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。
下面我們舉例說(shuō)明一下，如何通過(guò)慢查詢(xún)?nèi)罩径ㄎ粓?zhí)行效率低的SQL 語(yǔ)句：
開(kāi)啟慢查詢(xún)?nèi)罩?，配置樣?
- [mysqld]
- log-slow-queries
在my.cnf 配置文件中增加上述配置項(xiàng)并重啟mysql服務(wù)，這時(shí)mysql慢查詢(xún)功能生效。慢查詢(xún)?nèi)罩緦?xiě)入?yún)?shù)DATADIR（數(shù)據(jù)目錄）指定的路徑下，默認(rèn)文件名是host_name-slow.log 。
和錯(cuò)誤日志、查詢(xún)?nèi)罩疽粯?，慢查?xún)?nèi)罩居涗浀母袷揭彩羌兾谋?，可以被直接讀取。下例中演示了慢查詢(xún)?nèi)罩镜脑O(shè)置和讀取過(guò)程。
首先查詢(xún)一下 long_query_time 的值 。
- mysql> show variables like 'long%';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | long_query_time | 10 |
- +-----------------+-------+
- 1 row in set (0.00 sec)
為了方便測(cè)試，將修改慢查詢(xún)時(shí)間為5秒。
- mysql> set long_query_time=5;
- Query OK, 0 rows affected (0.02 sec)
依次執(zhí)行下面兩個(gè)查詢(xún)語(yǔ)句。
第一個(gè)查詢(xún)因?yàn)椴樵?xún)時(shí)間低于5 秒而不會(huì)出現(xiàn)在慢查詢(xún)?nèi)罩局校?/p>
- mysql> select count(*) from order2008;
- +----------+
- | count(*) |
- +----------+
- | 208 |
- +----------+
- 1 row in set (0.00 sec)
第二個(gè)查詢(xún)因?yàn)椴樵?xún)時(shí)間大于5 秒而應(yīng)該出現(xiàn)在慢查詢(xún)?nèi)罩局校?/p>
- mysql> select count(*) from t_user;
- +----------+
- | count(*) |
- +----------+
- | 6552961 |
查看慢查詢(xún)?nèi)罩尽?/p>
- [root@localhost mysql]# more localhost-slow.log
- # Time: 081026 19:46:34
- # User@Host: root[root] @ localhost []
- # Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
- select count(*) from t_user;
從上面日志中，可以發(fā)現(xiàn)查詢(xún)時(shí)間超過(guò)5 秒的SQL，而小于5秒的則沒(méi)有出現(xiàn)在此日志中。
#p#
如果慢查詢(xún)?nèi)罩局杏涗泝?nèi)容很多，可以使用mysqldumpslow工具（MySQL客戶(hù)端安裝自帶）來(lái)對(duì)慢查詢(xún)?nèi)罩具M(jìn)行分類(lèi)匯總。下例中對(duì)日志文件mysql_master-slow.log進(jìn)行了分類(lèi)匯總，只顯示匯總后摘要結(jié)果：
- [root@mysql_master mysql_data]#mysqldumpslow mysql_master-slow.log
- Reading mysql slow query log from mysql_master-slow.log
- Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql_master
- select count(N) from t_user;
對(duì)于 SQL 文本完全一致，只是變量不同的語(yǔ)句，mysqldumpslow 將會(huì)自動(dòng)視為同一個(gè)語(yǔ)句進(jìn)行統(tǒng)計(jì)，變量值用N來(lái)代替。這個(gè)統(tǒng)計(jì)結(jié)果將大大增加用戶(hù)閱讀慢查詢(xún)?nèi)罩镜男?，并迅速定位系統(tǒng)的SQL 瓶頸。
注意：慢查詢(xún)?nèi)罩緦?duì)于我們發(fā)現(xiàn)應(yīng)用中有性能問(wèn)題的SQL很有幫助，建議正常情況下，打開(kāi)此日志并經(jīng)常查看分析。
【編輯推薦】
