《Mysql入門解決MySQL Sending data導致查詢很慢問題的方法與思路》要點：
本文介紹了Mysql入門解決MySQL Sending data導致查詢很慢問題的方法與思路，希望對您有用。如果有疑問，可以聯系我們。

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

MYSQL數據庫【問題現象】

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

MYSQL數據庫【處理過程】

MYSQL數據庫1)explain

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

MYSQL數據庫

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

MYSQL數據庫2)show processlist;

MYSQL數據庫explain看不出問題,那到底慢在哪里呢?

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

MYSQL數據庫

MYSQL數據庫發現很長一段時間,查詢都處在 “Sending data”狀態

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

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

MYSQL數據庫3)show profile

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

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

MYSQL數據庫結果如下：

MYSQL數據庫

MYSQL數據庫從結果可以看出,Sending data的狀態執行了216s

MYSQL數據庫4)排查對比

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

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

MYSQL數據庫于是采取了對比的方法,看看“不返回description的結果”如何.show profile的結果如下：

MYSQL數據庫

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

MYSQL數據庫【原理研究】

MYSQL數據庫至此問題已經明確,但原理上我們還需要繼續探究.

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

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

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

MYSQL數據庫

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

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

MYSQL數據庫但實測結果卻并沒有提高,因此從這個測試可以推論Innodb并沒有將溢出頁(overflow page)緩存到內存里面.

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

MYSQL數據庫【解決方法】

MYSQL數據庫找到了問題的根本原因,解決方法也就不難了.有幾種方法：

MYSQL數據庫1)查詢時去掉description的查詢,但這受限于業務的實現,可能需要業務做較大調整

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

MYSQL數據庫以上就是本文的全部內容,希望對大家的學習有所幫助.

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/3309.html