線上 MySQL 直接 Select 千萬條的100G數(shù)據(jù)，服務(wù)器會裂開嗎?

公司主營業(yè)務(wù)：成都做網(wǎng)站、網(wǎng)站設(shè)計、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。成都創(chuàng)新互聯(lián)公司是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)公司推出鐵東免費做網(wǎng)站回饋大家。

假設(shè)對某100G表t執(zhí)行全表掃描，把掃描結(jié)果保存在客戶端：

 # 該語句無任何判斷條件，所以全表掃描，查到的每行都可直接放到結(jié)果集，然后返給客戶端
 mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from t" > $target_file

1 那這“結(jié)果集”存在哪?

實際上MySQL讀取、發(fā)送數(shù)據(jù)流程的如下：

• 獲取一行，寫到net_buffer。該內(nèi)存大小由參數(shù)net_buffer_length定義，默認16k

1. 繼續(xù)獲取行，直到寫滿net_buffer，發(fā)出去!
2. 若發(fā)送成功，則清空net_buffer，繼續(xù)讀取下一行，并寫入net_buffer
3. 若發(fā)送返回EAGAIN或WSAEWOULDBLOCK，表示本地網(wǎng)絡(luò)棧(socket send buffer)寫滿，進入等待。直到網(wǎng)絡(luò)棧重新可寫，再繼續(xù)發(fā)送

以上過程執(zhí)行流程圖如下：

可以看出一個查詢在發(fā)送過程中：占用MySQL內(nèi)部的內(nèi)存最大就是net_buffer_length，根本達不到100G。同理socket send buffer 也達不到，若socket send buffer被寫滿，就會暫停讀數(shù)據(jù)。

所以MySQL是邊讀取邊發(fā)送，若客戶端接收得比較慢，會導(dǎo)致MySQL Server由于結(jié)果發(fā)不出去，該事務(wù)的執(zhí)行時間就會變得很長。

經(jīng)過分析，我們現(xiàn)在知道了，查詢結(jié)果是分段發(fā)給客戶端的，因此掃描全表，即使查詢返回大量數(shù)據(jù)，也不會把內(nèi)存搞滿。

以上都是Server層的處理邏輯，InnoDB引擎層又是如何處理的呢?

2 InnoDB如何處理全表掃描?

內(nèi)存中的數(shù)據(jù)頁在Buffer Pool (后文簡稱為BP)管理，BP能夠加速查詢。由于WAL機制，當事務(wù)提交時，磁盤上的數(shù)據(jù)頁是舊的，若這時立即就有個查詢請求讀該數(shù)據(jù)頁，是不是得立即將redo log應(yīng)用到數(shù)據(jù)頁呢?并不!因為此時，內(nèi)存數(shù)據(jù)頁的結(jié)果就是最新的，直接讀內(nèi)存頁即可，所以速度就很快啊，Buffer Pool在此就加速了查詢。

但其實BP對查詢的加速效果依賴于內(nèi)存命中率?？墒褂萌缦旅畈榭串斍癇P命中率

show engine innodb status

一般穩(wěn)定服務(wù)的線上系統(tǒng)，要保證響應(yīng)性能，內(nèi)存命中率得在99%以上。

InnoDB Buffer Pool的大小由參數(shù)innodb_buffer_pool_size 確定，推薦設(shè)成可用物理內(nèi)存的60%~80%。

3 InnoDB內(nèi)存管理

使用最近最少使用 (Least Recently Used，LRU)算法，淘汰最久未使用的數(shù)據(jù)。若此時做個全表掃描，會咋樣?若要掃描一個200G的表，而這個表是一個歷史數(shù)據(jù)表，平時沒有業(yè)務(wù)訪問它。按此算法掃描，就會把當前BP里的數(shù)據(jù)全部淘汰，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容。即BP里主要放的是這個歷史數(shù)據(jù)表數(shù)據(jù)。

對于一個正在做業(yè)務(wù)服務(wù)的庫，這可不行呀。你會看到，BP內(nèi)存命中率急劇下降，磁盤壓力增加，SQL語句響應(yīng)變慢。所以，InnoDB不能直接使用原生LRU。

改良版LRU

InnoDB按 5:3 把鏈表分成New區(qū)和Old區(qū)，改良版LRU執(zhí)行流程：

• 首先，訪問New區(qū)的D1，和常規(guī)LRU一樣，將其移到鏈首
• 然后，訪問一個新的不存在于當前鏈表的數(shù)據(jù)頁，這時依舊是淘汰掉鏈尾數(shù)據(jù)頁P但新插入的數(shù)據(jù)頁DX，放在old處
• 處于old區(qū)的數(shù)據(jù)頁，每次被訪問時，都要判斷：

• 若該數(shù)據(jù)頁在LRU鏈表中存在時間>1s，就把它移動到鏈表頭部
• 若該數(shù)據(jù)頁在LRU鏈表中存在時間<1s，位置保持不變

1s由參數(shù)innodb_old_blocks_time控制

這種改良是專門為處理類似全表掃描的操作。還是掃描上百G的歷史數(shù)據(jù)表：

• 掃描過程中，需要新插入的數(shù)據(jù)頁，都被放到old區(qū)域
• 一個數(shù)據(jù)頁里面有多條記錄，這個數(shù)據(jù)頁會被多次訪問到，但由于順序掃描，這個數(shù)據(jù)頁第一次被訪問和最后一次被訪問的時間間隔不會超過1s，因此還是保留在old區(qū)
• 再繼續(xù)掃描后續(xù)數(shù)據(jù)，之前的這個數(shù)據(jù)頁之后也不會再被訪問到，于是始終沒有機會移到鏈表頭部(New區(qū))，很快就會被淘汰

可見該策略最大的收益，就是在掃描大表時，雖然也用到BP，但對young區(qū)全無影響，從而保證了Buffer Pool響應(yīng)正常業(yè)務(wù)的查詢命中率。

參考：

[1]. https://cloud.tencent.com/developer/article/1767570

[2]. https://juejin.cn/post/6854573221258199048

[3].https://time.geekbang.org/column/article/79407

本文題目：MySQL數(shù)據(jù)查詢太多會OOM嗎？
當前網(wǎng)址：http://uogjgqi.cn/article/cogpchd.html