mysql數據量大查詢慢?有人删了千萬級的數據，結果導緻頻繁的慢查詢，今天小編就來聊一聊關于mysql數據量大查詢慢?接下來我們就一起去研究一下吧!

mysql數據量大查詢慢

有人删了千萬級的數據，結果導緻頻繁的慢查詢。

線上收到大量慢查詢告警，于是檢查慢查詢的SQL，發現不是啥複雜SQL，這些SQL主要針對一個表，基本都是單行查詢，看起來應該不會有慢查詢。這種SQL基本上都是直接根據索引查找出來的，性能應該極高。

是否可能慢查詢不是SQL問題，而是MySQL生産服務器的問題？特殊情況下，MySQL出現慢查詢還真不是SQL問題，而是他自己生産服務器的負載太高，導緻SQL語句執行慢。比如現在MySQL服務器的

磁盤I/O負載高，每秒執行大量高負載的随機I/O，但磁盤本身每秒能執行的随機I/O有限，導緻正常SQL在磁盤執行時，若跑一些随機IO，你的磁盤太忙，顧不上你了，導緻你本來很快的一個SQL，要等很久才能執行完畢，這時就可能導緻正常SQL也變成慢查詢。

也許網絡負載高，導緻你一個SQL語句要發到MySQL，光是等待獲取一個和MySQL的連接，都很難，要等很久或MySQL自己網絡負載太高，帶寬打滿，帶寬打滿後，你一個SQL也許執行很快，但其查出來的數據返回給你，網絡都送不出去，也會變成慢查詢。

若CPU負載過高，也會導緻CPU過于繁忙去執行别的任務，沒時間執行你的SQL。

所以慢查詢不一定是SQL本身導緻，若覺得SQL不應該會慢查詢，結果他那個時間段跑這個SQL 就是慢，應排查當時MySQL服務器的負載，尤其看看磁盤、網絡及 CPU 的負載，是否正常。

當某個離線作業瞬間大批量把數據往MySQL裡灌入的時，他一瞬間服務器磁盤、網絡以及CPU的負載會超高。

此時你一個正常SQL執行下去，短時間内一定會慢查詢，類似問題，優化手段更多是控制你導緻MySQL負載過高的那些行為，比如灌入大量數據，最好在業務低峰期灌入，别影響高峰期的線上系統運行。

但看了下MySQL服務器的磁盤、網絡以及CPU負載，一切正常，似乎也不是這問題導緻。看起來無解了？

慢 SQL 的頭兩步排查手段：

• 檢查SQL是否有問題，看執行計劃
• 檢查MySQL服務器的負載

這兩種辦法都不奏效之後，第三步：用MySQL profilling工具去細緻的分析SQL語句的執行過程和耗時。

這個工具可以對SQL語句的執行耗時進行非常深入和細緻的分析

打開profiling，使用

set profiling=1

接着MySQL就會自動記錄查詢語句的profiling信息。此時若執行show profiles，就會給你列出各種查詢語句的profiling信息，會記錄下來每個查詢語句的query id，所以你要針對你需要分析的query找到對他的query id，我們當時就是針對慢查詢的那個SQL語句找到了query id。

然後針對單個查詢語句，看其profiling信息，使用show profile cpu, block io for query xx，這裡的xx是數字，此時就可以看到具體的profile信息。

除了cpu以及block io以外，還能指定去看這個SQL語句執行時候的其他各項負載和耗時。

會給你展示出來SQL語句執行時候的各種耗時，比如磁盤IO的耗時，CPU等待耗時，發送數據耗時，拷貝數據到臨時表的耗時等，SQL執行過程中的各種耗時都會展示。

檢查該SQL語句的profiling信息後，發現問題，其Sending Data耗時最高，幾乎使用1s，占據SQL執行耗時的99%！其他環節耗時低可以理解，畢竟這種簡單SQL執行速度真的很快，基本就是10ms級别，結果跑成1s，那肯定Sending Data就是問題根源！

這Sending Data在幹啥呢？

MySQL官方釋義：為一個SELECT語句讀取和處理數據行，同時發送數據給客戶端的過程，簡單來說就是為你的SELECT語句把數據讀出來，同時發送給客戶端。

但這過程為啥這麼慢？profiling确實是提供給我們更多的線索了，但似乎還是沒法解決問題。但已經捕獲到異常關鍵點，就是Sending Data的耗時很高！

接着：

show engine innodb status

看innodb存儲引擎的一些狀态，此時發現一個奇怪的指标：history list length，值特别高，達到上萬。

MVCC就是多個事務在對同一個數據， 有人寫，有人讀，此時可以有多種隔離級别，對一個數據有個多版本快照鍊條，才能實現MVCC和各種隔離級别。

所以當你有大量事務執行時，就會構建這種undo多版本快照鍊條，此時history list length就會很高。然後在事務提交後，會有一個多版本快照鍊條的自動purge清理機制，清理了，該值就會降低。一般該值不應過高，所以注意到第二個線索：history list length過高，即大量的undo多版本鍊條數據沒有清理。推測可能有的事務長時間運行，所以其多版本快照不能被purge清理，進而導緻history list length過高。

經過這倆線索推測，在大量簡單SQL變成慢查詢時，SQL因為Sending Data環節異常，耗時過高；同時此時出現一些長事務長時間運行，大量的頻繁更新數據，導緻有大量undo多版本快照鍊條，還無法purge清理。

因為發現有大量的更新語句在活躍，而且有那種長期活躍的長事務一直在跑而沒有結束，問了下系統負責人，在後台跑了個定時任務：他居然開了一個事務，然後在一個事務裡删除上千萬數據，導緻該事務一直在運行。

這種長事務的運行會導緻你删除時，僅隻是對數據加了一個删除标記，事實上并沒有徹底删除。此時你若和長事務同時運行的其它事務裡再查詢，他在查詢時可能會把那上千萬被标記為删除的數據都掃描一遍。因為每次掃描到一批數據，都發現标記為删除了，接着就會再繼續往下掃描，所以才導緻一些查詢語句很慢。

那為何你啟動一個事務，在事務裡查詢，憑什麼就要去掃描之前那個長事務标記為删除狀态的上千萬的垃圾數據？講道理，那些數據都被删了，跟你沒關系了呀，你可以不去掃描他們 嘛！

而問題症結在于，那個删除千萬級數據的事務是個長事務！即當你啟動新事務查詢時，那個删除千萬級數據的長事務一直在運行，它是活躍的！結合MVCC的Read View機制，當你啟動一個新事務查詢時，會生成一個Read View。你的新事務查詢時，會根據ReadView去判斷哪些數據可見及可見的數據版本号，因為每個數據都有個版本鍊條，有時你能可見的僅是這個數據的一個曆史版本。

所以正是因為該長事務一直在運行，還在删除大量數據，而且這些數據僅是邏輯删除，所以此時你新開事務的查詢還是會讀到所有邏輯删除數據，也就會出現千萬級的數據掃描，導緻了慢查詢！

所以禁止在業務高峰期運行那種删除大量數據的語句，因為這可能導緻一些正常的SQL都變慢查詢，因為那些SQL也許會不斷掃描你标記為删除的大量數據，好不容易掃描到一批數據，結果發現是标記為删除的，于是繼續掃描下去，導緻慢查詢！

直接kill那個正在删除千萬級數據的長事務，所有SQL很快恢複正常。此後，大量數據清理全部放在淩晨執行，那個時候就沒什麼人使用系統了，所以查詢也很少。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!