《MYSQL教程MySQL慢查詢優(yōu)化之慢查詢?nèi)罩痉治龅膶嵗坛獭芬c：
本文介紹了MYSQL教程MySQL慢查詢優(yōu)化之慢查詢?nèi)罩痉治龅膶嵗坛蹋Ｍ麑δ杏谩Ｈ绻幸蓡枺梢月?lián)系我們。
數(shù)據(jù)庫響應(yīng)慢問題最多的就是查詢了.現(xiàn)在大部分數(shù)據(jù)庫都提供了性能分析的贊助手段.例如Oracle中會幫你直接找出慢的語句,并且提供優(yōu)化方案.在MySQL中就要自己開啟慢日志記錄加以分析(記錄可以保存在表或者文件中,默認是保存在文件中,我們系統(tǒng)使用的就是默認方式).MYSQL數(shù)據(jù)庫
先看看MySQL慢查詢?nèi)罩纠锩娴挠涗涢L什么樣的：MYSQL數(shù)據(jù)庫
Time Id Command Argument # Time: 141010 9:33:57 # User@Host: root[root] @ localhost [] Id: 1 # Query_time: 0.000342 Lock_time: 0.000142 Rows_sent: 3 Rows_examined: 3 use test; SET timestamp=1412904837; select * from t;
這個日志應(yīng)該很好理解了,第一個#記錄時間戳,第二個#記錄執(zhí)行命令的用戶和地址信息,第三個#記錄執(zhí)行查詢的時間、鎖的時間、返回行數(shù)、被掃描的行數(shù).接著后面記錄真正執(zhí)行的SQL語句.還可以通過以下命令看看cvs存儲格式每個字段意義.MYSQL數(shù)據(jù)庫
SHOW CREATE TABLE mysql.slow_log;
接下來說說如何獲取和分析慢日志吧.MYSQL數(shù)據(jù)庫
查看MySQL慢日志參數(shù)MYSQL數(shù)據(jù)庫
進入啟動好的MySQL,執(zhí)行以下命令MYSQL數(shù)據(jù)庫
mysql> show variables like '%slow_query%';
+---------------------------+----------------------------------------+ | Variable_name | Value | +---------------------------+----------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/cloudlu-slow.log | +---------------------------+----------------------------------------+
這里告訴我們慢日志的日志存放位置,慢日志是否有開啟.
那么什么樣的查詢需要被日志呢?在MySQL中, 沒有index的查詢 以及 超過指定時間同時超過指定掃描行數(shù)的查詢 需要記錄在慢日志查詢里面.MYSQL數(shù)據(jù)庫
那么它們的參數(shù)又是怎么查看的呢?MYSQL數(shù)據(jù)庫
沒有index的查詢記錄開關(guān)MYSQL數(shù)據(jù)庫
mysql> show global variables like '%indexes%';
+----------------------------------------+-------+ | Variable_name | Value | +----------------------------------------+-------+ | log_queries_not_using_indexes | OFF | | log_throttle_queries_not_using_indexes | 0 | +----------------------------------------+-------+
第一個參數(shù) 表示是否開啟記錄沒有index的查詢,第二個MYSQL數(shù)據(jù)庫
參數(shù)用來做日志記錄的流量控制,一分鐘可以記錄多少條,默認0是表示不限制.MYSQL數(shù)據(jù)庫
超過指定時長的查詢開關(guān)MYSQL數(shù)據(jù)庫
mysql> show global variables like '%long_query%';
+-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
就一個參數(shù)指定超過多少時長的查詢需要被記錄MYSQL數(shù)據(jù)庫
超過指定行數(shù)的掃描查詢開關(guān)MYSQL數(shù)據(jù)庫
mysql> show variables like '%min_examined_row_limit%';
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | min_examined_row_limit | 0 | +------------------------+-------+ 1 row in set (0.00 sec)
默認是0,代表不現(xiàn)在掃描行數(shù)MYSQL數(shù)據(jù)庫
設(shè)置開啟MySQL慢日志參數(shù)MYSQL數(shù)據(jù)庫
進入MySQL,輸入以下命令或者在MySQL的啟動配置文件里面修改或者給MySQL添加啟動參數(shù),進入MySQL后的修改如下：MYSQL數(shù)據(jù)庫
set global long_query_time=0.1; set global log_queries_not_using_indexes=on; set global slow_query_log = on;
這里要斟酌的有2點,第一是超過什么時長的日志是有問題的,這個由系統(tǒng)需求來決定.第二是沒有使用indexes的日志每分鐘要記錄多少條,要防止日志太多對性能產(chǎn)生影響.MYSQL數(shù)據(jù)庫
在實際的日志分析中,通常慢日志的log數(shù)量不少,同時相同的查詢被記錄的條數(shù)也會很多,這里就需要如何從慢日志查詢中找到最有問題,最需要優(yōu)化的日志.在這方面,有很多分析工具,最基本的分析工具就是MySQL自帶的mysqldumpslow,mysqldumpslow(Perl腳本)的輸出示例：MYSQL數(shù)據(jù)庫
[root@cloudlu bin]# ./mysqldumpslow -s t -t 1 /usr/local/mysql/data/cloudlu-slow.log
?
MYSQL數(shù)據(jù)庫
Reading mysql slow query log from /usr/local/mysql/data/cloudlu-slow.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (3), root[root]@localhost select * from t
一看就非常清楚,它的輸出主要 統(tǒng)計不同慢sql的出現(xiàn)次數(shù)(Count 1),執(zhí)行最長時間(Time 0.00s),累計總耗費時間(Time 0s),等待鎖的時間(Lock 0.00s),等待鎖的總時間(Lock 0s),發(fā)送給客戶端的行總數(shù)(Rows 3.0),掃描的行總數(shù)(Rows 3),用戶(root)以及sql語句本身.它最常用的參數(shù)包含：MYSQL數(shù)據(jù)庫
對于一般的分析已經(jīng)差不多了,不過對于百分比等等數(shù)據(jù)mysqldumpslow就不夠完善了.所以世界上多了很多各種MySQL慢日志分析工具,比較優(yōu)秀的有mysqlsla(Perl腳本)和pt-query-digest(Perl腳本),可以提供Count, sql的執(zhí)行次數(shù)及占總的slow log數(shù)量的百分比,Time, 執(zhí)行時間, 包含總時間, 平均時間, 最小, 最大時間, 時間占到總慢sql時間的百分比,95% of Time, 去除最快和最慢的sql, 覆蓋率占95%的sql的執(zhí)行時間,Lock Time, 等待鎖的時間,95% of Lock , 95%的慢sql等待鎖時間,Rows sent, 結(jié)果行統(tǒng)計數(shù)量, 包含平均, 最小, 最大數(shù)量,Rows examined, 掃描的行數(shù)量,還可以生成表報,存儲分析結(jié)果.這里就不一一介紹了.MYSQL數(shù)據(jù)庫
通過這些慢日志分析軟件定位到了慢查詢語句就已經(jīng)完成了SQL優(yōu)化的一大半.接下來通過在MySQL中執(zhí)行explain或者desc命令查看慢查詢語句,可以看出為什么SQL查詢慢.MYSQL數(shù)據(jù)庫
mysql> explain select * from test.t \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: NULL
1 row in set (0.00 sec)
它的輸出格式細節(jié)可以關(guān)注MySQL explain format,在輸出中最要注意的是：
1. type：ALL是效率最差,最要注意的MYSQL數(shù)據(jù)庫
2. key：是否有使用Key,key長度如何MYSQL數(shù)據(jù)庫
3. Extra：最好不要出現(xiàn)filesort以及temporary,最主要是要關(guān)注在orderby和groupby.MYSQL數(shù)據(jù)庫
Note: SQL優(yōu)化是個很復(fù)雜的過程,有可能出現(xiàn)拆東墻補西墻的情況：好比給數(shù)據(jù)庫表加入了索引之后,確實查詢快了,可是存儲空間加多了,插入刪除操作耗時也增加了,如果在一個寫多讀少的系統(tǒng)中,執(zhí)行這種優(yōu)化可能會起到反效果.所以優(yōu)化完之后千萬不能大意,要持續(xù)監(jiān)控系統(tǒng),防止出現(xiàn)引入新瓶頸的情況.MYSQL數(shù)據(jù)庫
歡迎參與《MYSQL教程MySQL慢查詢優(yōu)化之慢查詢?nèi)罩痉治龅膶嵗坛獭酚懻摚窒砟南敕ǎS易PHP學院為您提供專業(yè)教程。
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/12690.html
