最近幫忙定位一個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的信息，則優化後的性能也不會有很大提升。
以上就是本文的全部內容，希望對大家的學習有所幫助。
