MySQL數(shù)據(jù)查詢太多會(huì)OOM嗎？
線上 MySQL 直接 Select 千萬條的100G數(shù)據(jù)，服務(wù)器會(huì)裂開嗎?
假設(shè)對(duì)某100G表t執(zhí)行全表掃描，把掃描結(jié)果保存在客戶端：
# 該語句無任何判斷條件，所以全表掃描，查到的每行都可直接放到結(jié)果集，然后返給客戶端
mysql -h$host -P$port -u$user -p$pwd -e
"select * from t" > $target_file
1 那這“結(jié)果集”存在哪?
實(shí)際上MySQL讀取、發(fā)送數(shù)據(jù)流程的如下：
- 獲取一行，寫到net_buffer。該內(nèi)存大小由參數(shù)net_buffer_length定義，默認(rèn)16k
- 繼續(xù)獲取行，直到寫滿net_buffer，發(fā)出去!
- 若發(fā)送成功，則清空net_buffer，繼續(xù)讀取下一行，并寫入net_buffer
- 若發(fā)送返回EAGAIN或WSAEWOULDBLOCK，表示本地網(wǎng)絡(luò)棧(socket send buffer)寫滿，進(jìn)入等待。直到網(wǎng)絡(luò)棧重新可寫，再繼續(xù)發(fā)送
以上過程執(zhí)行流程圖如下：
可以看出一個(gè)查詢?cè)诎l(fā)送過程中：占用MySQL內(nèi)部的內(nèi)存最大就是net_buffer_length，根本達(dá)不到100G。同理socket send buffer 也達(dá)不到，若socket send buffer被寫滿，就會(huì)暫停讀數(shù)據(jù)。
所以MySQL是邊讀取邊發(fā)送，若客戶端接收得比較慢，會(huì)導(dǎo)致MySQL Server由于結(jié)果發(fā)不出去，該事務(wù)的執(zhí)行時(shí)間就會(huì)變得很長。
經(jīng)過分析，我們現(xiàn)在知道了，查詢結(jié)果是分段發(fā)給客戶端的，因此掃描全表，即使查詢返回大量數(shù)據(jù)，也不會(huì)把內(nèi)存搞滿。
以上都是Server層的處理邏輯，InnoDB引擎層又是如何處理的呢?
2 InnoDB如何處理全表掃描?
內(nèi)存中的數(shù)據(jù)頁在Buffer Pool (后文簡稱為BP)管理，BP能夠加速查詢。由于WAL機(jī)制，當(dāng)事務(wù)提交時(shí)，磁盤上的數(shù)據(jù)頁是舊的，若這時(shí)立即就有個(gè)查詢請(qǐng)求讀該數(shù)據(jù)頁，是不是得立即將redo log應(yīng)用到數(shù)據(jù)頁呢?并不!因?yàn)榇藭r(shí)，內(nèi)存數(shù)據(jù)頁的結(jié)果就是最新的，直接讀內(nèi)存頁即可，所以速度就很快啊，Buffer Pool在此就加速了查詢。
但其實(shí)BP對(duì)查詢的加速效果依賴于內(nèi)存命中率。可使用如下命令查看當(dāng)前BP命中率
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ù)。若此時(shí)做個(gè)全表掃描，會(huì)咋樣?若要掃描一個(gè)200G的表，而這個(gè)表是一個(gè)歷史數(shù)據(jù)表，平時(shí)沒有業(yè)務(wù)訪問它。按此算法掃描，就會(huì)把當(dāng)前BP里的數(shù)據(jù)全部淘汰，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容。即BP里主要放的是這個(gè)歷史數(shù)據(jù)表數(shù)據(jù)。
對(duì)于一個(gè)正在做業(yè)務(wù)服務(wù)的庫，這可不行呀。你會(huì)看到，BP內(nèi)存命中率急劇下降，磁盤壓力增加，SQL語句響應(yīng)變慢。所以，InnoDB不能直接使用原生LRU。
改良版LRU
InnoDB按 5:3 把鏈表分成New區(qū)和Old區(qū)，改良版LRU執(zhí)行流程：
- 首先，訪問New區(qū)的D1，和常規(guī)LRU一樣，將其移到鏈?zhǔn)?/li>
- 然后，訪問一個(gè)新的不存在于當(dāng)前鏈表的數(shù)據(jù)頁，這時(shí)依舊是淘汰掉鏈尾數(shù)據(jù)頁P(yáng)但新插入的數(shù)據(jù)頁DX，放在old處
- 處于old區(qū)的數(shù)據(jù)頁，每次被訪問時(shí)，都要判斷：
- 若該數(shù)據(jù)頁在LRU鏈表中存在時(shí)間>1s，就把它移動(dòng)到鏈表頭部
- 若該數(shù)據(jù)頁在LRU鏈表中存在時(shí)間<1s，位置保持不變
1s由參數(shù)innodb_old_blocks_time控制
這種改良是專門為處理類似全表掃描的操作。還是掃描上百G的歷史數(shù)據(jù)表：
- 掃描過程中，需要新插入的數(shù)據(jù)頁，都被放到old區(qū)域
- 一個(gè)數(shù)據(jù)頁里面有多條記錄，這個(gè)數(shù)據(jù)頁會(huì)被多次訪問到，但由于順序掃描，這個(gè)數(shù)據(jù)頁第一次被訪問和最后一次被訪問的時(shí)間間隔不會(huì)超過1s，因此還是保留在old區(qū)
- 再繼續(xù)掃描后續(xù)數(shù)據(jù)，之前的這個(gè)數(shù)據(jù)頁之后也不會(huì)再被訪問到，于是始終沒有機(jī)會(huì)移到鏈表頭部(New區(qū))，很快就會(huì)被淘汰
可見該策略最大的收益，就是在掃描大表時(shí)，雖然也用到BP，但對(duì)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
