一起聊聊MySQL資料查詢太多會OOM嗎

主機記憶體只有100G，現在要全表掃描一個200G大表，會不會把DB主機的記憶體用光？

邏輯備份時，可不就是做整庫掃描嗎？若這樣就會把記憶體吃光，邏輯備份不是早就掛了？

所以大表全表掃描，看起來應該沒問題。這是為啥呢？

全表掃描對server層的影響

假設，我們現在要對一個200G的InnoDB表db1. t，執行一個全表掃描。當然，你要把掃描結果儲存在使用者端，會使用類似這樣的命令：

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

InnoDB資料儲存在主鍵索引上，所以全表掃描實際上是直接掃描表t的主鍵索引。這條查詢語句由於沒有其他判斷條件，所以查到的每一行都可以直接放到結果集，然後返回給使用者端。

那麼，這個「結果集」存在哪裡呢？

伺服器端無需儲存一個完整結果集。取資料和發資料的流程是這樣的：

獲取一行，寫到**「net_buffer」。這塊記憶體的大小是由引數「net_buffer_length」**定義，預設16k

重複獲取行，直到**「net_buffer」**寫滿，呼叫網路介面發出去

若傳送成功，就清空**「net_buffer」，然後繼續取下一行，並寫入「net_buffer」**

若傳送函數返回**「EAGAIN」或「WSAEWOULDBLOCK」**，就表示本地網路棧（socket send buffer）寫滿了，進入等待。直到網路棧重新可寫，再繼續傳送

查詢結果傳送流程

可見：

• 一個查詢在傳送過程中，佔用的MySQL內部的記憶體最大就是**「net_buffer_length」**這麼大，不會達到200G

• socket send buffer 也不可能達到200G（預設定義/proc/sys/net/core/wmem_default），若socket send buffer被寫滿，就會暫停讀資料的流程

所以MySQL其實是「邊讀邊發」。這意味著，若使用者端接收得慢，會導致MySQL伺服器端由於結果發不出去，這個事務的執行時間變長。

比如下面這個狀態，就是當用戶端不讀**「socket receive buffer」**內容時，在伺服器端show processlist看到的結果。

伺服器端傳送阻塞

若看到State一直是「Sending to client」，說明伺服器端的網路棧寫滿了。

若使用者端使用–quick引數，會使用mysql_use_result方法：讀一行處理一行。假設某業務的邏輯較複雜，每讀一行資料以後要處理的邏輯若很慢，就會導致使用者端要過很久才取下一行資料，可能就會出現上圖結果。

因此，對於正常的線上業務來說，若一個查詢的返回結果不多，推薦使用**「mysql_store_result」**介面，直接把查詢結果儲存到本地記憶體。

當然前提是查詢返回結果不多。如果太多，因為執行了一個大查詢導致使用者端佔用記憶體近20G，這種情況下就需要改用**「mysql_use_result」**介面。

若你在自己負責維護的MySQL裡看到很多個執行緒都處於「Sending to client」，表明你要讓業務開發同學優化查詢結果，並評估這麼多的返回結果是否合理。

若要快速減少處於這個狀態的執行緒的話，可以將**「net_buffer_length」**設定更大。

有時，範例上看到很多查詢語句狀態是「Sending data」，但檢視網路也沒什麼問題，為什麼Sending data要這麼久？

一個查詢語句的狀態變化是這樣的：

• MySQL查詢語句進入執行階段後，先把狀態設定成 「Sending data」

• 然後，傳送執行結果的列相關的資訊（meta data) 給使用者端

• 再繼續執行語句的流程

• 執行完成後，把狀態設定成空字串。

即「Sending data」並不一定是指「正在傳送資料」，而可能是處於執行器過程中的任意階段。比如，你可以構造一個鎖等待場景，就能看到Sending data狀態。

讀全表被鎖：

Sending data狀態

可見session2是在等鎖，狀態顯示為Sending data。

• 僅當一個執行緒處於「等待使用者端接收結果」的狀態，才會顯示"Sending to client"

• 若顯示成「Sending data」，它的意思只是「正在執行」

所以，查詢的結果是分段發給使用者端，因此掃描全表，查詢返回大量資料，並不會把記憶體打爆。

以上是server層的處理邏輯，在InnoDB引擎裡又是怎麼處理？

全表掃描對InnoDB的影響

InnoDB記憶體的一個作用，是儲存更新的結果，再配合redo log，避免隨機寫盤。

記憶體的資料頁是在Buffer Pool (簡稱為BP)管理，在WAL裡BP起加速更新的作用。

BP還能加速查詢。

由於WAL，當事務提交時，磁碟上的資料頁是舊的，若這時馬上有個查詢來讀該資料頁，是不是要馬上把redo log應用到資料頁？

不需要。因為此時，記憶體資料頁的結果是最新的，直接讀記憶體頁即可。這時查詢無需讀磁碟，直接從記憶體取結果，速度很快。所以，Buffer Pool能加速查詢。

而BP對查詢的加速效果，依賴於一個重要的指標，即：記憶體命中率。

可以在show engine innodb status結果中，檢視一個系統當前的BP命中率。一般情況下，一個穩定服務的線上系統，要保證響應時間符合要求的話，記憶體命中率要在99%以上。

執行show engine innodb status ，可以看到「Buffer pool hit rate」字樣，顯示的就是當前的命中率。比如下圖命中率，就是100%。

若所有查詢需要的資料頁都能夠直接從記憶體得到，那是最好的，對應命中率100%。

InnoDB Buffer Pool的大小是由引數 **「innodb_buffer_pool_size」**確定，一般建議設定成可用實體記憶體的60%~80%。

在大約十年前，單機的資料量是上百個G，而實體記憶體是幾個G；現在雖然很多伺服器都能有128G甚至更高的記憶體，但是單機的資料量卻達到了T級別。

所以，**「innodb_buffer_pool_size」**小於磁碟資料量很常見。若一個 Buffer Pool滿了，而又要從磁碟讀入一個資料頁，那肯定是要淘汰一箇舊資料頁的。

InnoDB記憶體管理

使用的最近最少使用 (Least Recently Used, LRU)演演算法，淘汰最久未使用資料。

基本LRU演演算法

InnoDB管理BP的LRU演演算法，是用連結串列實現的：

• state1，連結串列頭部是P1，表示P1是最近剛被存取過的資料頁

• 此時，一個讀請求存取P3，因此變成狀態2，P3被移到最前

• 狀態3表示，這次存取的資料頁不存在於連結串列，所以需要在BP中新申請一個資料頁Px，加到連結串列頭。但由於記憶體已滿，不能申請新記憶體。於是清空連結串列末尾Pm資料頁記憶體，存入Px的內容，放到連結串列頭部

最終就是最久沒有被存取的資料頁Pm被淘汰。

若此時要做一個全表掃描，會咋樣？若要掃描一個200G的表，而這個表是一個歷史資料表，平時沒有業務存取它。

那麼，按此演演算法掃描，就會把當前BP裡的資料全部淘汰，存入掃描過程中存取到的資料頁的內容。也就是說BP裡主要放的是這個歷史資料表的資料。

對於一個正在做業務服務的庫，這可不行呀。你會看到，BP記憶體命中率急劇下降，磁碟壓力增加，SQL語句響應變慢。

所以，InnoDB不能直接使用原始的LRU。InnoDB對其進行了優化。

改進的LRU演演算法

InnoDB按5:3比例把連結串列分成New區和Old區。圖中LRU_old指向的就是old區域的第一個位置，是整個連結串列的5/8處。即靠近連結串列頭部的5/8是New區域，靠近連結串列尾部的3/8是old區域。

改進後的LRU演演算法執行流程：

狀態1，要存取P3，由於P3在New區，和優化前LRU一樣，將其移到連結串列頭部 =》狀態2

之後要存取一個新的不存在於當前連結串列的資料頁，這時依然是淘汰掉資料頁Pm，但新插入的資料頁Px，是放在**「LRU_old」**處

處於old區的資料頁，每次被存取的時候都要做如下判斷：

若該資料頁在LRU連結串列中存在的時間超過1s，就把它移動到連結串列頭部

若該資料頁在LRU連結串列中存在的時間短於1s，位置保持不變。1s是由引數**「innodb_old_blocks_time」**控制，預設值1000，單位ms。

該策略，就是為了處理類似全表掃描的操作量身客製化。還是掃描200G歷史資料表：

4. 掃描過程中，需要新插入的資料頁，都被放到old區域

5. 一個資料頁裡面有多條記錄，這個資料頁會被多次存取到，但由於是順序掃描，這個資料頁第一次被存取和最後一次被存取的時間間隔不會超過1秒，因此還是會被保留在old區域

6. 再繼續掃描後續的資料，之前的這個資料頁之後也不會再被存取到，於是始終沒有機會移到連結串列頭部（New區），很快就會被淘汰出去。

可以看到，這個策略最大的收益，就是在掃描這個大表的過程中，雖然也用到了BP，但對young區完全沒有影響，從而保證了Buffer Pool響應正常業務的查詢命中率。

小結

MySQL採用的是邊算邊發的邏輯，因此對於資料量很大的查詢結果來說，不會在server端儲存完整的結果集。所以，如果使用者端讀結果不及時，會堵住MySQL的查詢過程，但是不會把記憶體打爆。

而對於InnoDB引擎內部，由於有淘汰策略，大查詢也不會導致記憶體暴漲。並且，由於InnoDB對LRU演演算法做了改進，冷資料的全表掃描，對Buffer Pool的影響也能做到可控。

全表掃描還是比較耗費IO資源的，所以業務高峰期還是不能直接線上上主庫執行全表掃描的。

推薦學習：

以上就是一起聊聊MySQL資料查詢太多會OOM嗎的詳細內容，更多請關注TW511.COM其它相關文章！