MySQL數據查詢太多會OOM嗎？
線上 MySQL 直接 Select 千萬條的100G數據，服務器會裂開嗎?
假設對某100G表t執行全表掃描，把掃描結果保存在客戶端：
# 該語句無任何判斷條件，所以全表掃描，查到的每行都可直接放到結果集，然后返給客戶端
mysql -h$host -P$port -u$user -p$pwd -e
"select * from t" > $target_file
1 那這“結果集”存在哪?
實際上MySQL讀取、發送數據流程的如下：
- 獲取一行，寫到net_buffer。該內存大小由參數net_buffer_length定義，默認16k
- 繼續獲取行，直到寫滿net_buffer，發出去!
- 若發送成功，則清空net_buffer，繼續讀取下一行，并寫入net_buffer
- 若發送返回EAGAIN或WSAEWOULDBLOCK，表示本地網絡棧(socket send buffer)寫滿，進入等待。直到網絡棧重新可寫，再繼續發送
以上過程執行流程圖如下：
可以看出一個查詢在發送過程中：占用MySQL內部的內存最大就是net_buffer_length，根本達不到100G。同理socket send buffer 也達不到，若socket send buffer被寫滿，就會暫停讀數據。
所以MySQL是邊讀取邊發送，若客戶端接收得比較慢，會導致MySQL Server由于結果發不出去，該事務的執行時間就會變得很長。
經過分析，我們現在知道了，查詢結果是分段發給客戶端的，因此掃描全表，即使查詢返回大量數據，也不會把內存搞滿。
以上都是Server層的處理邏輯，InnoDB引擎層又是如何處理的呢?
2 InnoDB如何處理全表掃描?
內存中的數據頁在Buffer Pool (后文簡稱為BP)管理，BP能夠加速查詢。由于WAL機制，當事務提交時，磁盤上的數據頁是舊的，若這時立即就有個查詢請求讀該數據頁，是不是得立即將redo log應用到數據頁呢?并不!因為此時，內存數據頁的結果就是最新的，直接讀內存頁即可，所以速度就很快啊，Buffer Pool在此就加速了查詢。
但其實BP對查詢的加速效果依賴于內存命中率。可使用如下命令查看當前BP命中率
show engine innodb status
一般穩定服務的線上系統，要保證響應性能，內存命中率得在99%以上。
InnoDB Buffer Pool的大小由參數innodb_buffer_pool_size 確定，推薦設成可用物理內存的60%~80%。
3 InnoDB內存管理
使用最近最少使用 (Least Recently Used，LRU)算法，淘汰最久未使用的數據。若此時做個全表掃描，會咋樣?若要掃描一個200G的表，而這個表是一個歷史數據表，平時沒有業務訪問它。按此算法掃描，就會把當前BP里的數據全部淘汰，存入掃描過程中訪問到的數據頁的內容。即BP里主要放的是這個歷史數據表數據。
對于一個正在做業務服務的庫，這可不行呀。你會看到，BP內存命中率急劇下降，磁盤壓力增加，SQL語句響應變慢。所以，InnoDB不能直接使用原生LRU。
改良版LRU
InnoDB按 5:3 把鏈表分成New區和Old區，改良版LRU執行流程：
- 首先，訪問New區的D1，和常規LRU一樣，將其移到鏈首
- 然后，訪問一個新的不存在于當前鏈表的數據頁，這時依舊是淘汰掉鏈尾數據頁P但新插入的數據頁DX，放在old處
- 處于old區的數據頁，每次被訪問時，都要判斷：
- 若該數據頁在LRU鏈表中存在時間>1s，就把它移動到鏈表頭部
- 若該數據頁在LRU鏈表中存在時間<1s，位置保持不變
1s由參數innodb_old_blocks_time控制
這種改良是專門為處理類似全表掃描的操作。還是掃描上百G的歷史數據表：
- 掃描過程中，需要新插入的數據頁，都被放到old區域
- 一個數據頁里面有多條記錄，這個數據頁會被多次訪問到，但由于順序掃描，這個數據頁第一次被訪問和最后一次被訪問的時間間隔不會超過1s，因此還是保留在old區
- 再繼續掃描后續數據，之前的這個數據頁之后也不會再被訪問到，于是始終沒有機會移到鏈表頭部(New區)，很快就會被淘汰
可見該策略最大的收益，就是在掃描大表時，雖然也用到BP，但對young區全無影響，從而保證了Buffer Pool響應正常業務的查詢命中率。
參考：
[1]. https://cloud.tencent.com/developer/article/1767570
[2]. https://juejin.cn/post/6854573221258199048
[3].https://time.geekbang.org/column/article/79407
