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

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

MYSQL數(shù)據(jù)庫【問題現(xiàn)象】

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

MYSQL數(shù)據(jù)庫【處理過程】

MYSQL數(shù)據(jù)庫1)explain

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

MYSQL數(shù)據(jù)庫

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

MYSQL數(shù)據(jù)庫2)show processlist;

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

MYSQL數(shù)據(jù)庫于是想到了使用 show processlist查看sql語句執(zhí)行狀態(tài),查詢結(jié)果如下：

MYSQL數(shù)據(jù)庫

MYSQL數(shù)據(jù)庫發(fā)現(xiàn)很長一段時間,查詢都處在 “Sending data”狀態(tài)

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

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

MYSQL數(shù)據(jù)庫3)show profile

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

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

MYSQL數(shù)據(jù)庫結(jié)果如下：

MYSQL數(shù)據(jù)庫

MYSQL數(shù)據(jù)庫從結(jié)果可以看出,Sending data的狀態(tài)執(zhí)行了216s

MYSQL數(shù)據(jù)庫4)排查對比

MYSQL數(shù)據(jù)庫經(jīng)過以上步驟,已經(jīng)確定查詢慢是因為大量的時間耗費在了Sending data狀態(tài)上,結(jié)合Sending data的定義,將目標聚焦在查詢語句的返回列上面

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

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

MYSQL數(shù)據(jù)庫

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

MYSQL數(shù)據(jù)庫【原理研究】

MYSQL數(shù)據(jù)庫至此問題已經(jīng)明確,但原理上我們還需要繼續(xù)探究.

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

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

MYSQL數(shù)據(jù)庫我們使用show table status來查看表的相關(guān)信息：

MYSQL數(shù)據(jù)庫

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

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

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

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

MYSQL數(shù)據(jù)庫【解決方法】

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

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

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

MYSQL數(shù)據(jù)庫以上就是本文的全部內(nèi)容,希望對大家的學習有所幫助.

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/3309.html