這篇文章主要講解了“MySQL的buffer pool有什么用”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL的buffer pool有什么用”吧！
應用系統分層架構，為了加速數據訪問，會把最常訪問的數據，放在緩存(cache)里，避免每次都去訪問數據庫。操作系統，會有緩沖池(buffer pool)機制，避免每次訪問磁盤，以加速數據的訪問。MySQL作為一個存儲系統，同樣具有緩沖池(buffer pool)機制，以避免每次查詢數據都進行磁盤IO。今天，和大家聊一聊InnoDB的緩沖池。
緩存表數據與索引數據，把磁盤上的數據加載到緩沖池，避免每次訪問都進行磁盤IO，起到加速訪問的作用。速度快，那為啥不把所有數據都放到緩沖池里？
凡事都具備兩面性，拋開數據易失性不說，訪問快速的反面是存儲容量?。?/p>
（1）緩存訪問快，但容量小，數據庫存儲了200G數據，緩存容量可能只有64G；
（2）內存訪問快，但容量小，買一臺筆記本磁盤有2T，內存可能只有16G；
因此，只能把“最熱”的數據放到“最近”的地方，以“最大限度”的降低磁盤訪問。
如何管理與淘汰緩沖池，使得性能最大化呢？在介紹具體細節之前，先介紹下“預讀”的概念。
什么是預讀？
磁盤讀寫，并不是按需讀取，而是按頁讀取，一次至少讀一頁數據（一般是4K），如果未來要讀取的數據就在頁中，就能夠省去后續的磁盤IO，提高效率。
預讀為什么有效？
數據訪問，通常都遵循“集中讀寫”的原則，使用一些數據，大概率會使用附近的數據，這就是所謂的“局部性原理”，它表明提前加載是有效的，確實能夠減少磁盤IO。
按頁(4K)讀取，和InnoDB的緩沖池設計有啥關系？
（1）磁盤訪問按頁讀取能夠提高性能，所以緩沖池一般也是按頁緩存數據；
（2）預讀機制啟示了我們，能把一些“可能要訪問”的頁提前加入緩沖池，避免未來的磁盤IO操作；
InnoDB是以什么算法，來管理這些緩沖頁呢？
最容易想到的，就是LRU(Least recently used)。畫外音：memcache，OS都會用LRU來進行頁置換管理，但MySQL的玩法并不一樣。
傳統的LRU是如何進行緩沖頁管理？
最常見的玩法是，把入緩沖池的頁放到LRU的頭部，作為最近訪問的元素，從而最晚被淘汰。這里又分兩種情況：
（1）頁已經在緩沖池里，那就只做“移至”LRU頭部的動作，而沒有頁被淘汰；
（2）頁不在緩沖池里，除了做“放入”LRU頭部的動作，還要做“淘汰”LRU尾部頁的動作；
如上圖，假如管理緩沖池的LRU長度為10，緩沖了頁號為1，3，5…，40，7的頁。假如，接下來要訪問的數據在頁號為4的頁中：
（1）頁號為50的頁，原來不在緩沖池里；
（2）把頁號為50的頁，放到LRU頭部，同時淘汰尾部頁號為7的頁；
傳統的LRU緩沖池算法十分直觀，OS，memcache等很多軟件都在用，MySQL為啥這么矯情，不能直接用呢？
這里有兩個問題：
（1）預讀失效；
（2）緩沖池污染；
什么是預讀失效？
由于預讀(Read-Ahead)，提前把頁放入了緩沖池，但最終MySQL并沒有從頁中讀取數據，稱為預讀失效。
如何對預讀失效進行優化？
要優化預讀失效，思路是：
（1）讓預讀失敗的頁，停留在緩沖池LRU里的時間盡可能短；
（2）讓真正被讀取的頁，才挪到緩沖池LRU的頭部；
以保證，真正被讀取的熱數據留在緩沖池里的時間盡可能長。
具體方法是：
（1）將LRU分為兩個部分：
新生代(new sublist)
老生代(old sublist)
（2）新老生代收尾相連，即：新生代的尾(tail)連接著老生代的頭(head)；
（3）新頁（例如被預讀的頁）加入緩沖池時，只加入到老生代頭部：
如果數據真正被讀?。A讀成功），才會加入到新生代的頭部
如果數據沒有被讀取，則會比新生代里的“熱數據頁”更早被淘汰出緩沖池
假如有一個頁號為50的新頁被預讀加入緩沖池：
（1）50只會從老生代頭部插入，老生代尾部（也是整體尾部）的頁會被淘汰掉；
（2）假設50這一頁不會被真正讀取，即預讀失敗，它將比新生代的數據更早淘汰出緩沖池；
繼續舉例，假如批量數據掃描，有51，52，53，54，55等五個頁面將要依次被訪問。
加入“老生代停留時間窗口”策略后，短時間內被大量加載的頁，并不會立刻插入新生代頭部，而是優先淘汰那些，短期內僅僅訪問了一次的頁。
參數：innodb_buffer_pool_size
介紹：配置緩沖池的大小，在內存允許的情況下，DBA往往會建議調大這個參數，越多數據和索引放到內存里，數據庫的性能會越好。
參數：innodb_old_blocks_pct
介紹：老生代占整個LRU鏈長度的比例，默認是37，即整個LRU中新生代與老生代長度比例是63:37。
畫外音：如果把這個參數設為100，就退化為普通LRU了。
參數：innodb_old_blocks_time
介紹：老生代停留時間窗口，單位是毫秒，默認是1000，即同時滿足“被訪問”與“在老生代停留時間超過1秒”兩個條件，才會被插入到新生代頭部。
總結
（1）緩沖池(buffer pool)是一種常見的降低磁盤訪問的機制；
（2）緩沖池通常以頁(page)為單位緩存數據；
（3）緩沖池的常見管理算法是LRU，memcache，OS，InnoDB都使用了這種算法；
（4）InnoDB對普通LRU進行了優化：將緩沖池分為老生代和新生代，入緩沖池的頁，優先進入老生代，頁被訪問，才進入新生代，以解決預讀失效的問題頁被訪問，且在老生代停留時間超過配置閾值的，才進入新生代，以解決批量數據訪問，大量熱數據淘汰的問題
思路，比結論重要。解決了什么問題，比方案重要。
問題 ：我的主機內存只有100G，現在要對一個200G的大表做全表掃描，會不會把數據庫主機的內存用光了？
這個問題確實值得擔心，被系統OOM（out of memory）可不是鬧著玩的。但是，反過來想想，邏輯備份的時候，可不就是做整庫掃描嗎？如果這樣就會把內存吃光，邏輯備份不是早就掛了？
所以說，對大表做全表掃描，看來應該是沒問題的。但是，這個流程到底是怎么樣的呢？
假設，我們現在要對一個200G的InnoDB表db1. t，執行一個全表掃描。當然，你要把掃描結果保存在客戶端，會使用類似這樣的命令：
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
你已經知道了，InnoDB的數據是保存在主鍵索引上的，所以全表掃描實際上是直接掃描表t的主鍵索引。這條查詢語句由于沒有其他的判斷條件，所以查到的每一行都可以直接放到結果集里面，然后返回給客戶端。
那么，這個“結果集”存在哪里呢？
實際上，服務端并不需要保存一個完整的結果集。取數據和發數據的流程是這樣的：
獲取一行，寫到net_buffer中。這塊內存的大小是由參數net_buffer_length定義的，默認是16k。
重復獲取行，直到net_buffer寫滿，調用網絡接口發出去。
如果發送成功，就清空net_buffer，然后繼續取下一行，并寫入net_buffer。
如果發送函數返回EAGAIN或WSAEWOULDBLOCK，就表示本地網絡棧（socket send buffer）寫滿了，進入等待。直到網絡棧重新可寫，再繼續發送。
這個過程對應的流程圖如下所示。
圖1 查詢結果發送流程
從這個流程中，你可以看到：
一個查詢在發送過程中，占用的MySQL內部的內存最大就是net_buffer_length這么大，并不會達到200G；
socket send buffer 也不可能達到200G（默認定義/proc/sys/net/core/wmem_default），如果socket send buffer被寫滿，就會暫停讀數據的流程。
也就是說，MySQL是邊讀邊發的，如果客戶端接收得慢，會導致MySQL服務端由于結果發不出去，這個事務的執行時間變長。
比如下面這個狀態，就是我故意讓客戶端不去讀socket receive buffer中的內容，然后在服務端show processlist看到的結果。
圖2 服務端發送阻塞
如果你看到State的值一直處于“Sending to client”，就表示服務器端的網絡棧寫滿了。
如果客戶端使用–quick參數，會使用mysql_use_result方法。這個方法是讀一行處理一行。你可以想象一下，假設有一個業務的邏輯比較復雜，每讀一行數據以后要處理的邏輯如果很慢，就會導致客戶端要過很久才會去取下一行數據，可能就會出現如圖2所示的這種情況。
因此，對于正常的線上業務來說，如果一個查詢的返回結果不會很多的話，我都建議你使用mysql_store_result這個接口，直接把查詢結果保存到本地內存。
當然前提是查詢返回結果不多。有同學說到自己因為執行了一個大查詢導致客戶端占用內存近20G，這種情況下就需要改用mysql_use_result接口了。
另一方面，如果你在自己負責維護的MySQL里看到很多個線程都處于“Sending to client”這個狀態，就意味著你要讓業務開發同學優化查詢結果，并評估這么多的返回結果是否合理。
而如果要快速減少處于這個狀態的線程的話，將net_buffer_length參數設置為一個更大的值是一個可選方案。
與“Sending to client”長相很類似的一個狀態是“Sending data”，這是一個經常被誤會的問題。有同學問我說，在自己維護的實例上看到很多查詢語句的狀態是“Sending data”，但查看網絡也沒什么問題啊，為什么Sending data要這么久？
實際上，一個查詢語句的狀態變化是這樣的（注意：這里，我略去了其他無關的狀態）：
MySQL查詢語句進入執行階段后，首先把狀態設置成“Sending data”；
然后，發送執行結果的列相關的信息（meta data) 給客戶端；
再繼續執行語句的流程；
執行完成后，把狀態設置成空字符串。
也就是說，“Sending data”并不一定是指“正在發送數據”，而可能是處于執行器過程中的任意階段。比如，你可以構造一個鎖等待的場景，就能看到Sending data狀態。
圖 4 Sending data狀態
可以看到，session B明顯是在等鎖，狀態顯示為Sending data。
也就是說，僅當一個線程處于“等待客戶端接收結果”的狀態，才會顯示"Sending to client"；而如果顯示成“Sending data”，它的意思只是“正在執行”。
現在你知道了，查詢的結果是分段發給客戶端的，因此掃描全表，查詢返回大量的數據，并不會把內存打爆。
在server層的處理邏輯我們都清楚了，在InnoDB引擎里面又是怎么處理的呢？ 掃描全表會不會對引擎系統造成影響呢？
內存的數據頁是在buffer pool 中管理的，在WAL里Buffer Pool 起到了加速更新的作用。而實際上，Buffer Pool 還有一個更重要的作用，就是加速查詢。
由于有WAL機制，當事務提交的時候，磁盤上的數據頁是舊的，那如果這時候馬上有一個查詢要來讀這個數據頁，是不是要馬上把redo log應用到數據頁呢？
答案是不需要。因為這時候內存數據頁的結果是最新的，直接讀內存頁就可以了。你看，這時候查詢根本不需要讀磁盤，直接從內存拿結果，速度是很快的。所以說，Buffer Pool還有加速查詢的作用。
而Buffer Pool對查詢的加速效果，依賴于一個重要的指標，即：內存命中率。
你可以在show engine innodb status結果中，查看一個系統當前的BP命中率。一般情況下，一個穩定服務的線上系統，要保證響應時間符合要求的話，內存命中率要在99%以上。
執行show engine innodb status ，可以看到“Buffer pool hit rate”字樣，顯示的就是當前的命中率。比如圖5這個命中率，就是99.0%。
圖6 基本LRU算法
InnoDB管理Buffer Pool的LRU算法，是用鏈表來實現的。
在圖6的狀態1里，鏈表頭部是P1，表示P1是最近剛剛被訪問過的數據頁；假設內存里只能放下這么多數據頁；
這時候有一個讀請求訪問P3，因此變成狀態2，P3被移到最前面；
狀態3表示，這次訪問的數據頁是不存在于鏈表中的，所以需要在Buffer Pool中新申請一個數據頁Px，加到鏈表頭部。但是由于內存已經滿了，不能申請新的內存。于是，會清空鏈表末尾Pm這個數據頁的內存，存入Px的內容，然后放到鏈表頭部。
從效果上看，就是最久沒有被訪問的數據頁Pm，被淘汰了。
這個算法乍一看上去沒什么問題，但是如果考慮到要做一個全表掃描，會不會有問題呢？
假設按照這個算法，我們要掃描一個200G的表，而這個表是一個歷史數據表，平時沒有業務訪問它。
那么，按照這個算法掃描的話，就會把當前的Buffer Pool里的數據全部淘汰掉，存入掃描過程中訪問到的數據頁的內容。也就是說Buffer Pool里面主要放的是這個歷史數據表的數據。
對于一個正在做業務服務的庫，這可不妙。你會看到，Buffer Pool的內存命中率急劇下降，磁盤壓力增加，SQL語句響應變慢。
所以，InnoDB不能直接使用這個LRU算法。實際上，InnoDB對LRU算法做了改進。
圖 7 改進的LRU算法
在InnoDB實現上，按照5:3的比例把整個LRU鏈表分成了young區域和old區域。圖中LRU_old指向的就是old區域的第一個位置，是整個鏈表的5/8處。也就是說，靠近鏈表頭部的5/8是young區域，靠近鏈表尾部的3/8是old區域。
改進后的LRU算法執行流程變成了下面這樣。
圖7中狀態1，要訪問數據頁P3，由于P3在young區域，因此和優化前的LRU算法一樣，將其移到鏈表頭部，變成狀態2。
之后要訪問一個新的不存在于當前鏈表的數據頁，這時候依然是淘汰掉數據頁Pm，但是新插入的數據頁Px，是放在LRU_old處。
處于old區域的數據頁，每次被訪問的時候都要做下面這個判斷：
若這個數據頁在LRU鏈表中存在的時間超過了1秒，就把它移動到鏈表頭部；
如果這個數據頁在LRU鏈表中存在的時間短于1秒，位置保持不變。1秒這個時間，是由參數innodb_old_blocks_time控制的。其默認值是1000，單位毫秒。
這個策略，就是為了處理類似全表掃描的操作量身定制的。還是以剛剛的掃描200G的歷史數據表為例，我們看看改進后的LRU算法的操作邏輯：
掃描過程中，需要新插入的數據頁，都被放到old區域;
一個數據頁里面有多條記錄，這個數據頁會被多次訪問到，但由于是順序掃描，這個數據頁第一次被訪問和最后一次被訪問的時間間隔不會超過1秒，因此還是會被保留在old區域；
再繼續掃描后續的數據，之前的這個數據頁之后也不會再被訪問到，于是始終沒有機會移到鏈表頭部（也就是young區域），很快就會被淘汰出去。
可以看到，這個策略最大的收益，就是在掃描這個大表的過程中，雖然也用到了Buffer Pool，但是對young區域完全沒有影響，從而保證了Buffer Pool響應正常業務的查詢命中率。
感謝各位的閱讀，以上就是“MySQL的buffer pool有什么用”的內容了，經過本文的學習后，相信大家對MySQL的buffer pool有什么用這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
