1.使用explain，查看查詢計(jì)劃

2.使用show processlist查看查詢過(guò)程（處于哪個(gè)狀態(tài)），完整命令如下 mysql -uroot -p -e ‘show processlist G’ |grep state: |sort|uniq -c|sort -rn 此種方法和方法3類似，應(yīng)該說(shuō)方法3更好用。

3.使用show profile。 默認(rèn)是禁止的，需要使用set profiling = 1開(kāi)啟。執(zhí)行一些查詢后，鍵入show profiles可以看到前面執(zhí)行語(yǔ)句的查詢時(shí)間以很高的精度顯示了出來(lái)。然后使用show profile for query n就可以看到對(duì)應(yīng)查詢語(yǔ)句的查詢執(zhí)行的每個(gè)步驟以及其花費(fèi)的時(shí)間。

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

在項(xiàng)目中發(fā)現(xiàn)程序執(zhí)行的時(shí)間幾乎全部消耗在了數(shù)據(jù)庫(kù)的操作上。用pt-query-digest對(duì)慢查詢?nèi)罩咀龀龇治鰣?bào)告(實(shí)際生產(chǎn)中無(wú)法方便的打開(kāi)和關(guān)閉慢查詢?nèi)罩荆藭r(shí)可以通過(guò)監(jiān)聽(tīng)TCP流量即使用tcpdump來(lái)模擬)，發(fā)現(xiàn)update和insert操作占到了所有時(shí)間的95%。

于是進(jìn)一步分析執(zhí)行的語(yǔ)句。

這條update語(yǔ)句各部分耗時(shí)如下：

可以看出時(shí)間主要耗費(fèi)在了query end狀態(tài)中。

google上得到答案，將mysql的配置文件my.conf里加上一句innodb_flush_log_at_trx_commit = 0。 經(jīng)過(guò)驗(yàn)證，成功解決問(wèn)題，速度提升非常明顯（上面的改動(dòng)同時(shí)對(duì)insert操作也起了作用）。 同時(shí)留下疑問(wèn)：query end是什么狀態(tài)，為什么會(huì)用這么久的時(shí)間，為什么加上innodb_flush_log_at_trx_commit = 0后性能提升會(huì)這么大？

query end是什么狀態(tài)？ mysql的官方文檔解釋是：This state occurs after processing a query but before the freeing items state.我的理解是語(yǔ)句執(zhí)行完畢了，但是還有一些后續(xù)工作沒(méi)做完時(shí)的狀態(tài)。

那么freeing items 又是什么狀態(tài)呢？ 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.就是釋放查詢緩存里面的空間（因?yàn)槭莡pdate操作，所以相應(yīng)的緩存里的記錄就無(wú)效了，所以需要有這一步做處理）。

innodb_flush_log_at_trx_commit的默認(rèn)值是1，此時(shí)的行為是： 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的作用:允許事務(wù)在執(zhí)行完成之后才將日志（事務(wù)需要維護(hù)一個(gè)日志）寫到磁盤上，時(shí)間主要應(yīng)該就是耗費(fèi)在磁盤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 flush to disk operation is performed on the log file, but nothing is done at a transaction commit。 可以看到，改成0后，本來(lái)應(yīng)該每次提交都進(jìn)行的操作，變成了每秒鐘才進(jìn)行一次，所以及大的節(jié)省了時(shí)間。

將innodb_flush_log_at_trx_commit的值設(shè)置為0有一個(gè)副作用：任何服務(wù)器端mysql程序的崩潰會(huì)導(dǎo)致最后一秒的事務(wù)丟失(還沒(méi)來(lái)得及到到日志文件中)。但是考慮到本應(yīng)用對(duì)事務(wù)不必有如此嚴(yán)格的要求，所以這是可以接受的。

來(lái)自：http://inetkiller.github.io/2014/05/20/mysql語(yǔ)句性能分析與優(yōu)化/