| session1 | session2 |
|---|---|
| begin select * from t where id=1 for update |
啟動(dòng)事務(wù) |
| select * from t lock in share mode (blocked) |
Sending data狀態(tài)
可見(jiàn)session2是在等鎖，狀態(tài)顯示為Sending data。
所以，查詢的結(jié)果是分段發(fā)給客戶端，因此掃描全表，查詢返回大量數(shù)據(jù)，并不會(huì)把內(nèi)存打爆。
以上是server層的處理邏輯，在InnoDB引擎里又是怎么處理？
InnoDB內(nèi)存的一個(gè)作用，是保存更新的結(jié)果，再配合redo log，避免隨機(jī)寫(xiě)盤(pán)。
內(nèi)存的數(shù)據(jù)頁(yè)是在Buffer Pool (簡(jiǎn)稱為BP)管理，在WAL里BP起加速更新的作用。
BP還能加速查詢。
由于WAL，當(dāng)事務(wù)提交時(shí)，磁盤(pán)上的數(shù)據(jù)頁(yè)是舊的，若這時(shí)馬上有個(gè)查詢來(lái)讀該數(shù)據(jù)頁(yè)，是不是要馬上把redo log應(yīng)用到數(shù)據(jù)頁(yè)？
不需要。因?yàn)榇藭r(shí)，內(nèi)存數(shù)據(jù)頁(yè)的結(jié)果是最新的，直接讀內(nèi)存頁(yè)即可。這時(shí)查詢無(wú)需讀磁盤(pán)，直接從內(nèi)存取結(jié)果，速度很快。所以，Buffer Pool能加速查詢。
而B(niǎo)P對(duì)查詢的加速效果，依賴于一個(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ù)頁(yè)都能夠直接從內(nèi)存得到，那是最好的，對(duì)應(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級(jí)別。
所以，innodb_buffer_pool_size小于磁盤(pán)數(shù)據(jù)量很常見(jiàn)。若一個(gè) Buffer Pool滿了，而又要從磁盤(pán)讀入一個(gè)數(shù)據(jù)頁(yè)，那肯定是要淘汰一個(gè)舊數(shù)據(jù)頁(yè)的。
使用的最近最少使用 (Least Recently Used, LRU)算法，淘汰最久未使用數(shù)據(jù)。
TODO
最終就是最久沒(méi)有被訪問(wèn)的數(shù)據(jù)頁(yè)P(yáng)m被淘汰。
若此時(shí)要做一個(gè)全表掃描，會(huì)咋樣？若要掃描一個(gè)200G的表，而這個(gè)表是一個(gè)歷史數(shù)據(jù)表，平時(shí)沒(méi)有業(yè)務(wù)訪問(wèn)它。
那么，按此算法掃描，就會(huì)把當(dāng)前BP里的數(shù)據(jù)全部淘汰，存入掃描過(guò)程中訪問(wèn)到的數(shù)據(jù)頁(yè)的內(nèi)容。也就是說(shuō)BP里主要放的是這個(gè)歷史數(shù)據(jù)表的數(shù)據(jù)。
對(duì)于一個(gè)正在做業(yè)務(wù)服務(wù)的庫(kù)，這可不行呀。你會(huì)看到，BP內(nèi)存命中率急劇下降，磁盤(pán)壓力增加，SQL語(yǔ)句響應(yīng)變慢。
所以，InnoDB不能直接使用原始的LRU。InnoDB對(duì)其進(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，要訪問(wèn)P3，由于P3在New區(qū)，和優(yōu)化前LRU一樣，將其移到鏈表頭部 =》狀態(tài)2
2. 之后要訪問(wèn)一個(gè)新的不存在于當(dāng)前鏈表的數(shù)據(jù)頁(yè)，這時(shí)依然是淘汰掉數(shù)據(jù)頁(yè)P(yáng)m，但新插入的數(shù)據(jù)頁(yè)P(yáng)x，是放在LRU_old處
3. 處于old區(qū)的數(shù)據(jù)頁(yè)，每次被訪問(wèn)的時(shí)候都要做如下判斷：
該策略，就是為了處理類(lèi)似全表掃描的操作量身定制。還是掃描200G歷史數(shù)據(jù)表：
4. 掃描過(guò)程中，需要新插入的數(shù)據(jù)頁(yè)，都被放到old區(qū)域
5. 一個(gè)數(shù)據(jù)頁(yè)里面有多條記錄，這個(gè)數(shù)據(jù)頁(yè)會(huì)被多次訪問(wèn)到，但由于是順序掃描，這個(gè)數(shù)據(jù)頁(yè)第一次被訪問(wèn)和最后一次被訪問(wèn)的時(shí)間間隔不會(huì)超過(guò)1秒，因此還是會(huì)被保留在old區(qū)域
6. 再繼續(xù)掃描后續(xù)的數(shù)據(jù)，之前的這個(gè)數(shù)據(jù)頁(yè)之后也不會(huì)再被訪問(wèn)到，于是始終沒(méi)有機(jī)會(huì)移到鏈表頭部（New區(qū)），很快就會(huì)被淘汰出去。
可以看到，這個(gè)策略最大的收益，就是在掃描這個(gè)大表的過(guò)程中，雖然也用到了BP，但對(duì)young區(qū)完全沒(méi)有影響，從而保證了Buffer Pool響應(yīng)正常業(yè)務(wù)的查詢命中率。
MySQL采用的是邊算邊發(fā)的邏輯，因此對(duì)于數(shù)據(jù)量很大的查詢結(jié)果來(lái)說(shuō)，不會(huì)在server端保存完整的結(jié)果集。所以，如果客戶端讀結(jié)果不及時(shí)，會(huì)堵住MySQL的查詢過(guò)程，但是不會(huì)把內(nèi)存打爆。
而對(duì)于InnoDB引擎內(nèi)部，由于有淘汰策略，大查詢也不會(huì)導(dǎo)致內(nèi)存暴漲。并且，由于InnoDB對(duì)LRU算法做了改進(jìn)，冷數(shù)據(jù)的全表掃描，對(duì)Buffer Pool的影響也能做到可控。
全表掃描還是比較耗費(fèi)IO資源的，所以業(yè)務(wù)高峰期還是不能直接在線上主庫(kù)執(zhí)行全表掃描的。
到此這篇關(guān)于淺談MySQL數(shù)據(jù)查詢太多會(huì)OOM嗎的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)查詢OOM內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：阿里 福州 溫州 山西 揚(yáng)州 無(wú)錫 三明 定西
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《淺談MySQL數(shù)據(jù)查詢太多會(huì)OOM嗎》，本文關(guān)鍵詞 淺談,MySQL,數(shù)據(jù)查詢,太多,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
