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

【問題現(xiàn)象】

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

【處理過程】

1）explain

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

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

2）show processlist;

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

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

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

查詢一下“Sending data”狀態(tài)的含義，原來這個(gè)狀態(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ù)讀取出來返回個(gè)客戶端。

3）show profile

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

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

結(jié)果如下：

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

4）排查對(duì)比

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

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

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

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

【原理研究】

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

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

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

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

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

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

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

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

【解決方法】

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

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

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

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com