程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 解決MySQL Sending data導致查詢很慢問題的方法與思路,mysqlsending

解決MySQL Sending data導致查詢很慢問題的方法與思路,mysqlsending

編輯:MySQL綜合教程

解決MySQL Sending data導致查詢很慢問題的方法與思路,mysqlsending


最近幫忙定位一個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的信息,則優化後的性能也不會有很大提升。

以上就是本文的全部內容,希望對大家的學習有所幫助。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved