目錄全表掃描對server層的影響全表掃描對InnoDB的影響InnoDB內(nèi)存管理小結(jié)

我的主機(jī)內(nèi)存只有100G，現(xiàn)在要全表掃描一個(gè)200G大表，會不會把DB主機(jī)的內(nèi)存用光？

邏輯備份時(shí)，可不就是做整庫掃描嗎？若這樣就會把內(nèi)存吃光，邏輯備份不是早就掛了？所以大表全表掃描，看起來應(yīng)該沒問題。這是為啥呢？

全表掃描對server層的影響

假設(shè)，我們現(xiàn)在要對一個(gè)200G的InnoDB表db1. t，執(zhí)行一個(gè)全表掃描。當(dāng)然，你要把掃描結(jié)果保存在客戶端，會使用類似這樣的命令：

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

InnoDB數(shù)據(jù)保存在主鍵索引上，所以全表掃描實(shí)際上是直接掃描表t的主鍵索引。這條查詢語句由于沒有其他判斷條件，所以查到的每一行都可以直接放到結(jié)果集，然后返回給客戶端。

那么，這個(gè)“結(jié)果集”存在哪里呢？服務(wù)端無需保存一個(gè)完整結(jié)果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：

獲取一行，寫到net_buffer。這塊內(nèi)存的大小是由參數(shù)net_buffer_length定義，默認(rèn)16k 重復(fù)獲取行，直到net_buffer寫滿，調(diào)用網(wǎng)絡(luò)接口發(fā)出去 若發(fā)送成功，就清空net_buffer，然后繼續(xù)取下一行，并寫入net_buffer 若發(fā)送函數(shù)返回EAGAIN或WSAEWOULDBLOCK，就表示本地網(wǎng)絡(luò)棧（socket send buffer）寫滿了，進(jìn)入等待。直到網(wǎng)絡(luò)棧重新可寫，再繼續(xù)發(fā)送

查詢結(jié)果發(fā)送流程

可見：

一個(gè)查詢在發(fā)送過程中，占用的MySQL內(nèi)部的內(nèi)存最大就是net_buffer_length這么大，不會達(dá)到200G socket send buffer 也不可能達(dá)到200G（默認(rèn)定義/proc/sys/net/core/wmem_default），若socket send buffer被寫滿，就會暫停讀數(shù)據(jù)的流程

所以MySQL其實(shí)是“邊讀邊發(fā)”。這意味著，若客戶端接收得慢，會導(dǎo)致MySQL服務(wù)端由于結(jié)果發(fā)不出去，這個(gè)事務(wù)的執(zhí)行時(shí)間變長。

比如下面這個(gè)狀態(tài)，就是當(dāng)客戶端不讀socket receive buffer內(nèi)容時(shí)，在服務(wù)端show processlist看到的結(jié)果。

服務(wù)端發(fā)送阻塞

若看到State一直是“Sending to client”，說明服務(wù)器端的網(wǎng)絡(luò)棧寫滿了。

若客戶端使用–quick參數(shù)，會使用mysql_use_result方法：讀一行處理一行。假設(shè)某業(yè)務(wù)的邏輯較復(fù)雜，每讀一行數(shù)據(jù)以后要處理的邏輯若很慢，就會導(dǎo)致客戶端要過很久才取下一行數(shù)據(jù)，可能就會出現(xiàn)上圖結(jié)果。

因此，對于正常的線上業(yè)務(wù)來說，若一個(gè)查詢的返回結(jié)果不多，推薦使用mysql_store_result接口，直接把查詢結(jié)果保存到本地內(nèi)存。

當(dāng)然前提是查詢返回結(jié)果不多。如果太多，因?yàn)閳?zhí)行了一個(gè)大查詢導(dǎo)致客戶端占用內(nèi)存近20G，這種情況下就需要改用mysql_use_result接口。

若你在自己負(fù)責(zé)維護(hù)的MySQL里看到很多個(gè)線程都處于“Sending to client”，表明你要讓業(yè)務(wù)開發(fā)同學(xué)優(yōu)化查詢結(jié)果，并評估這么多的返回結(jié)果是否合理。

若要快速減少處于這個(gè)狀態(tài)的線程的話，可以將net_buffer_length設(shè)置更大。

有時(shí)，實(shí)例上看到很多查詢語句狀態(tài)是“Sending data”，但查看網(wǎng)絡(luò)也沒什么問題，為什么Sending data要這么久？一個(gè)查詢語句的狀態(tài)變化是這樣的：

MySQL查詢語句進(jìn)入執(zhí)行階段后，先把狀態(tài)設(shè)置成 Sending data 然后，發(fā)送執(zhí)行結(jié)果的列相關(guān)的信息（meta data) 給客戶端 再繼續(xù)執(zhí)行語句的流程 執(zhí)行完成后，把狀態(tài)設(shè)置成空字符串。

即“Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”，而可能是處于執(zhí)行器過程中的任意階段。比如，你可以構(gòu)造一個(gè)鎖等待場景，就能看到Sending data狀態(tài)。

讀全表被鎖：

session1 session2 begin select * from t where id=1 for update 啟動事務(wù)select * from t lock in share mode (blocked)

Sending data狀態(tài)

可見session2是在等鎖，狀態(tài)顯示為Sending data。

僅當(dāng)一個(gè)線程處于“等待客戶端接收結(jié)果”的狀態(tài)，才會顯示'Sending to client' 若顯示成“Sending data”，它的意思只是“正在執(zhí)行”

所以，查詢的結(jié)果是分段發(fā)給客戶端，因此掃描全表，查詢返回大量數(shù)據(jù)，并不會把內(nèi)存打爆。

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

全表掃描對InnoDB的影響

InnoDB內(nèi)存的一個(gè)作用，是保存更新的結(jié)果，再配合redo log，避免隨機(jī)寫盤。

內(nèi)存的數(shù)據(jù)頁是在Buffer Pool (簡稱為BP)管理，在WAL里BP起加速更新的作用。BP還能加速查詢。

由于WAL，當(dāng)事務(wù)提交時(shí)，磁盤上的數(shù)據(jù)頁是舊的，若這時(shí)馬上有個(gè)查詢來讀該數(shù)據(jù)頁，是不是要馬上把redo log應(yīng)用到數(shù)據(jù)頁？

不需要。因?yàn)榇藭r(shí)，內(nèi)存數(shù)據(jù)頁的結(jié)果是最新的，直接讀內(nèi)存頁即可。這時(shí)查詢無需讀磁盤，直接從內(nèi)存取結(jié)果，速度很快。所以，Buffer Pool能加速查詢。

而BP對查詢的加速效果，依賴于一個(gè)重要的指標(biāo)，即：內(nèi)存命中率。可以在show engine innodb status結(jié)果中，查看一個(gè)系統(tǒng)當(dāng)前的BP命中率。一般情況下，一個(gè)穩(wěn)定服務(wù)的線上系統(tǒng)，要保證響應(yīng)時(shí)間符合要求的話，內(nèi)存命中率要在99%以上。

執(zhí)行show engine innodb status ，可以看到“Buffer pool hit rate”字樣，顯示的就是當(dāng)前的命中率。比如下圖命中率，就是100%。

若所有查詢需要的數(shù)據(jù)頁都能夠直接從內(nèi)存得到，那是最好的，對應(yīng)命中率100%。

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

在大約十年前，單機(jī)的數(shù)據(jù)量是上百個(gè)G，而物理內(nèi)存是幾個(gè)G；現(xiàn)在雖然很多服務(wù)器都能有128G甚至更高的內(nèi)存，但是單機(jī)的數(shù)據(jù)量卻達(dá)到了T級別。

所以，innodb_buffer_pool_size小于磁盤數(shù)據(jù)量很常見。若一個(gè) Buffer Pool滿了，而又要從磁盤讀入一個(gè)數(shù)據(jù)頁，那肯定是要淘汰一個(gè)舊數(shù)據(jù)頁的。

InnoDB內(nèi)存管理

使用的最近最少使用 (Least Recently Used, LRU)算法，淘汰最久未使用數(shù)據(jù)。

基本LRU算法

TODO

InnoDB管理BP的LRU算法，是用鏈表實(shí)現(xiàn)的： state1，鏈表頭部是P1，表示P1是最近剛被訪問過的數(shù)據(jù)頁 此時(shí)，一個(gè)讀請求訪問P3，因此變成狀態(tài)2，P3被移到最前 狀態(tài)3表示，這次訪問的數(shù)據(jù)頁不存在于鏈表，所以需要在BP中新申請一個(gè)數(shù)據(jù)頁P(yáng)x，加到鏈表頭。但由于內(nèi)存已滿，不能申請新內(nèi)存。于是清空鏈表末尾Pm數(shù)據(jù)頁內(nèi)存，存入Px的內(nèi)容，放到鏈表頭部

最終就是最久沒有被訪問的數(shù)據(jù)頁P(yáng)m被淘汰。若此時(shí)要做一個(gè)全表掃描，會咋樣？若要掃描一個(gè)200G的表，而這個(gè)表是一個(gè)歷史數(shù)據(jù)表，平時(shí)沒有業(yè)務(wù)訪問它。

那么，按此算法掃描，就會把當(dāng)前BP里的數(shù)據(jù)全部淘汰，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容。也就是說BP里主要放的是這個(gè)歷史數(shù)據(jù)表的數(shù)據(jù)。

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

所以，InnoDB不能直接使用原始的LRU。InnoDB對其進(jìn)行了優(yōu)化。

改進(jìn)的LRU算法

InnoDB按5:3比例把鏈表分成New區(qū)和Old區(qū)。圖中LRU_old指向的就是old區(qū)域的第一個(gè)位置，是整個(gè)鏈表的5/8處。即靠近鏈表頭部的5/8是New區(qū)域，靠近鏈表尾部的3/8是old區(qū)域。

改進(jìn)后的LRU算法執(zhí)行流程：

1. 狀態(tài)1，要訪問P3，由于P3在New區(qū)，和優(yōu)化前LRU一樣，將其移到鏈表頭部 =》狀態(tài)22. 之后要訪問一個(gè)新的不存在于當(dāng)前鏈表的數(shù)據(jù)頁，這時(shí)依然是淘汰掉數(shù)據(jù)頁P(yáng)m，但新插入的數(shù)據(jù)頁P(yáng)x，是放在LRU_old處3. 處于old區(qū)的數(shù)據(jù)頁，每次被訪問的時(shí)候都要做如下判斷：

若該數(shù)據(jù)頁在LRU鏈表中存在的時(shí)間超過1s，就把它移動到鏈表頭部 若該數(shù)據(jù)頁在LRU鏈表中存在的時(shí)間短于1s，位置保持不變。1s是由參數(shù)innodb_old_blocks_time控制，默認(rèn)值1000，單位ms。

該策略，就是為了處理類似全表掃描的操作量身定制。還是掃描200G歷史數(shù)據(jù)表：4. 掃描過程中，需要新插入的數(shù)據(jù)頁，都被放到old區(qū)域5. 一個(gè)數(shù)據(jù)頁里面有多條記錄，這個(gè)數(shù)據(jù)頁會被多次訪問到，但由于是順序掃描，這個(gè)數(shù)據(jù)頁第一次被訪問和最后一次被訪問的時(shí)間間隔不會超過1秒，因此還是會被保留在old區(qū)域6. 再繼續(xù)掃描后續(xù)的數(shù)據(jù)，之前的這個(gè)數(shù)據(jù)頁之后也不會再被訪問到，于是始終沒有機(jī)會移到鏈表頭部（New區(qū)），很快就會被淘汰出去。

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

小結(jié)

MySQL采用的是邊算邊發(fā)的邏輯，因此對于數(shù)據(jù)量很大的查詢結(jié)果來說，不會在server端保存完整的結(jié)果集。所以，如果客戶端讀結(jié)果不及時(shí)，會堵住MySQL的查詢過程，但是不會把內(nèi)存打爆。

而對于InnoDB引擎內(nèi)部，由于有淘汰策略，大查詢也不會導(dǎo)致內(nèi)存暴漲。并且，由于InnoDB對LRU算法做了改進(jìn)，冷數(shù)據(jù)的全表掃描，對Buffer Pool的影響也能做到可控。

全表掃描還是比較耗費(fèi)IO資源的，所以業(yè)務(wù)高峰期還是不能直接在線上主庫執(zhí)行全表掃描的。

到此這篇關(guān)于淺談MySQL數(shù)據(jù)查詢太多會OOM嗎的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)查詢OOM內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！