線上 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

1. 繼續獲取行，直到寫滿net_buffer，發出去!
2. 若發送成功，則清空net_buffer，繼續讀取下一行，并寫入net_buffer
3. 若發送返回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