對mysql語句的效能分析與最佳化

1.使用explain，查看查詢計劃

2.使用show processlist查看查詢過程（處於哪個狀態），完整指令如下mysql -uroot -p -e 'show processlist G' |grep state: |sort|uniq -c|sort -rn 此種方法和方法3類似，應該說方法3更好用。

3.使用show profile。 預設是禁止的，需要使用set profiling = 1開啟。執行一些查詢後，鍵入show profiles可以看到前面執行語句的查詢時間以很高的精確度顯示了出來。然後使用show profile for query n就可以看到對應查詢語句的查詢執行的每個步驟以及其花費的時間。

4.使用慢日誌，並用第三方工具pt-query-digest產生分析報告。使用這種分析方法時，很有可能需要更改配置文件，可以設定成如下形式： log_slow_queries = /var/log/mysql/mysql-slow.log#日誌的存放目錄 long_query_time = 0 //捕獲所有的查詢log -queries-not-using-indexes//即使不使用索引也可以被記錄

在專案中發現程式執行的時間幾乎全部消耗在了資料庫的操作上。用pt-query-digest對慢查詢日誌做出分析報告(實際生產中無法方便的開啟和關閉慢查詢日誌，此時可以透過監聽TCP流量即使用tcpdump來模擬)，發現update和insert操作佔了所有時間的95%。

於是進一步分析執行的語句。

這條update語句各部分耗時如下：

可以看出時間主要耗費在了query end狀態中。

google上得到答案，將mysql的設定檔my.conf裡面加上一句innodb_flush_log_at_trx_commit = 0。 經過驗證，成功解決問題，速度提升非常明顯（上面的改動同時對insert操作也起了作用）。 同時留下疑問：query end是什麼狀態，為什麼要用這麼久的時間，為什麼加上innodb_flush_log_at_trx_commit = 0後效能提升會這麼大？

query end是什麼狀態？ mysql的官方文件解釋是：This state occurs after processing a query but before the freeing items state.我的理解是語句執行完畢了，但是還有一些後續工作沒做完時的狀態。

那麼freeing items 又是什麼狀態呢？ The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.就是釋放查詢快取裡面的空間（因為是update操作，所以對應的快取裡的記錄就對應的快取裡的記錄就無效了，所以需要有這一步驟做處理）。

innodb_flush_log_at_trx_commit的預設值是1，此時的行為是： the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file。 log buffer的作用:允許交易在執行完成之後才將日誌（事務需要維護一個日誌）寫到磁碟上，時間主要應該就是耗費在磁碟IO上？

而將innodb_flush_log_at_trx_commit的值改為0後，行為如下： If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the sush is wisk” but nothing is done at a transaction commit。 可以看到，改成0後，本來應該每次提交都進行的操作，變成了每秒鐘才進行一次，所以及大的節省了時間。

將innodb_flush_log_at_trx_commit的值設定為0有副作用：任何伺服器端mysql程式的崩潰會導致最後一秒的交易遺失(還沒來得及到到日誌檔案)。但是考慮到本應用程式對事務不必有如此嚴格的要求，所以這是可以接受的。

 

 以上就是對mysql語句的效能分析與優化的內容，更多相關內容請關注PHP中文網（www.php.cn）！