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

【問題現象】

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

【處理過程】

1）explain

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

從explain的結果來看，整個語句的索引設計是沒有問題的，除了第一個表因為業務需要進行整表掃描外，其它的表都是通過索引訪問

2）show processlist;

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

于是想到了使用 show processlist查看sql語句執行狀態，查詢結果如下：

發現很長一段時間，查詢都處在 “Sending data”狀態

查詢一下“Sending data”狀態的含義，原來這個狀態的名稱很具有誤導性，所謂的“Sending data”并不是單純的發送數據，而是包括“收集 + 發送 數據”。

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

3）show profile

為了進一步驗證查詢的時間分布，于是使用了show profile命令來查看詳細的時間分布

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

結果如下：

從結果可以看出，Sending data的狀態執行了216s

4）排查對比

經過以上步驟，已經確定查詢慢是因為大量的時間耗費在了Sending data狀態上，結合Sending data的定義，將目標聚焦在查詢語句的返回列上面

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

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

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

【原理研究】

至此問題已經明確，但原理上我們還需要繼續探究。

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

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

我們使用show table status來查看表的相關信息：

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

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

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

這樣的設計也是符合邏輯的，因為overflow page本來就是存放大數據的，如果也放在緩存里面，就會出現一次大數據列（blob、text、varchar）查詢，可能就將所有的緩存都更新了，這樣會導致其它普通的查詢性能急劇下降。

【解決方法】

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

1）查詢時去掉description的查詢，但這受限于業務的實現，可能需要業務做較大調整

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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com