最近幫忙定位一個mysql查詢很慢的問題，定位過程綜合各種方法、理論、工具，很有代表性，分享給大家。

【問題現(xiàn)象】

使用sphinx支持倒排索引，但sphinx從mysql查詢源數(shù)據(jù)的時候，查詢的記錄數(shù)才幾萬條，但查詢的速度非常慢，大概要4~5分鐘左右

【處理過程】

1）explain

首先懷疑索引沒有建好，于是使用explain查看查詢計(jì)劃，結(jié)果如下：

從explain的結(jié)果來看，整個語句的索引設(shè)計(jì)是沒有問題的，除了第一個表因?yàn)闃I(yè)務(wù)需要進(jìn)行整表掃描外，其它的表都是通過索引訪問

2）show processlist;

explain看不出問題，那到底慢在哪里呢？

于是想到了使用 show processlist查看sql語句執(zhí)行狀態(tài)，查詢結(jié)果如下：

發(fā)現(xiàn)很長一段時間，查詢都處在 “Sending data”狀態(tài)

查詢一下“Sending data”狀態(tài)的含義，原來這個狀態(tài)的名稱很具有誤導(dǎo)性，所謂的“Sending data”并不是單純的發(fā)送數(shù)據(jù)，而是包括“收集 + 發(fā)送 數(shù)據(jù)”。

這里的關(guān)鍵是為什么要收集數(shù)據(jù)，原因在于：mysql使用“索引”完成查詢結(jié)束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“數(shù)據(jù)行”上將需要返回的數(shù)據(jù)讀取出來返回個客戶端。

3）show profile

為了進(jìn)一步驗(yàn)證查詢的時間分布，于是使用了show profile命令來查看詳細(xì)的時間分布

首先打開配置：set profiling=on;
執(zhí)行完查詢后，使用show profiles查看query id;
使用show profile for query query_id查看詳細(xì)信息;

結(jié)果如下：

從結(jié)果可以看出，Sending data的狀態(tài)執(zhí)行了216s

4）排查對比

經(jīng)過以上步驟，已經(jīng)確定查詢慢是因?yàn)榇罅康臅r間耗費(fèi)在了Sending data狀態(tài)上，結(jié)合Sending data的定義，將目標(biāo)聚焦在查詢語句的返回列上面

經(jīng)過一 一排查，最后定為到一個description的列上，這個列的設(shè)計(jì)為：`description`varchar(8000) DEFAULT NULL COMMENT '游戲描述',

于是采取了對比的方法，看看“不返回description的結(jié)果”如何。show profile的結(jié)果如下：

可以看出，不返回description的時候，查詢時間只需要15s，返回的時候，需要216s，兩者相差15倍

【原理研究】

至此問題已經(jīng)明確，但原理上我們還需要繼續(xù)探究。

這篇淘寶的文章很好的解釋了相關(guān)原理：innodb使用大字段text，blob的一些優(yōu)化建議

這里的關(guān)鍵信息是：當(dāng)Innodb的存儲格式是 ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT)的時候，Innodb只會存儲前768字節(jié)的長度，剩余的數(shù)據(jù)存放到“溢出頁”中。

我們使用show table status來查看表的相關(guān)信息：

可以看到，平均一行大約1.5K，也就說大約1/10行會使用“溢出存儲”，一旦采用了這種方式存儲，返回?cái)?shù)據(jù)的時候本來是順序讀取的數(shù)據(jù)，就變成了隨機(jī)讀取了，所以導(dǎo)致性能急劇下降。

另外，在測試過程中還發(fā)現(xiàn)，無論這條語句執(zhí)行多少次，甚至將整個表select *幾次，語句的執(zhí)行速度都沒有明顯變化。這個表的數(shù)據(jù)和索引加起來才150M左右，而整個Innodb buffer pool有5G，緩存整張表綽綽有余，如果緩存了溢出頁，性能應(yīng)該大幅提高才對。

但實(shí)測結(jié)果卻并沒有提高，因此從這個測試可以推論Innodb并沒有將溢出頁（overflow page）緩存到內(nèi)存里面。

這樣的設(shè)計(jì)也是符合邏輯的，因?yàn)閛verflow page本來就是存放大數(shù)據(jù)的，如果也放在緩存里面，就會出現(xiàn)一次大數(shù)據(jù)列（blob、text、varchar）查詢，可能就將所有的緩存都更新了，這樣會導(dǎo)致其它普通的查詢性能急劇下降。

【解決方法】

找到了問題的根本原因，解決方法也就不難了。有幾種方法：

1）查詢時去掉description的查詢，但這受限于業(yè)務(wù)的實(shí)現(xiàn)，可能需要業(yè)務(wù)做較大調(diào)整

2）表結(jié)構(gòu)優(yōu)化，將descripion拆分到另外的表，這個改動較大，需要已有業(yè)務(wù)配合修改，且如果業(yè)務(wù)還是要繼續(xù)查詢這個description的信息，則優(yōu)化后的性能也不會有很大提升。

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助。

最新評論