老實說這部分相關的知識點早就已經準備好了，只是苦於不知道該組織這些內容而已，昨晚想到了該如何組織這部分內容。
本系列的文章不加說明，一般都是在InnoDB資料引擎討論。
在開始看本篇文章之前，建議先看:
- SQL查詢模型和查疑補漏
- LeetCode刷題四部曲之SQL篇(一)
- MySQL優化學習手札(一)
- MySQL優化學習筆記手札(二)
buffer pool 快取池 的引入
簡介
我們在那裡已經嘮叨過一條SQL由客戶端傳送給MySQL的服務端會發生些什麼了，對此沒有了解的，建議翻一下《MySQL優化學習手札(一)》
到目前為止，從巨集觀上來看，SQL被髮送給MySQL服務端之後，MySQL的儲存引擎根據SQL從磁碟中提取出來對應的資料，但你知道磁碟的速度相對於的記憶體的速度是很慢的，即使是固態硬碟。如果每次提取資料都從磁碟中提取資料，那未免有點太慢了吧。對於InnoDB作為儲存引擎的表, MySQL的開發者設計了快取，來避免每次提取資料都從快取池裡面提取資料。InnoDB儲存引擎在處理客戶端的請求時，當需要訪問一個頁的一條記錄時, 就會把完整的頁的資料載入到記憶體中，也就是說即使我們只需要訪問一個頁的一條記錄，那也需要把整個頁的資料載入到記憶體中。將整個頁載入到記憶體中後就可以進行讀寫訪問了, 在進行讀寫訪問之後並不著急把該頁對應的記憶體空間釋放掉，而是將其快取起來，這樣將來有請請求再次訪問該頁面時，就可以避免直接從磁碟中提取資料了。
為了快取磁碟中的頁，在MySQL伺服器啟動的時候就向作業系統申請了一片連續的記憶體，這片記憶體也就是buffer pool，通過
SHOW ENGINE INNODB STATUS;可以看到快取池的基本狀態:
Buffer Pool中預設的快取頁大小和在磁碟上預設的頁大小是一樣的，都是16KB。每個快取頁都會有一些對應的控制資訊，包括頁號、快取頁在Buffer Pool中的地址等，每個快取頁對應的控制資訊佔用記憶體大小是相同，我們稱控制資訊所佔用的 記憶體為控制塊，控制塊和快取頁是一一對應的，都位於快取池中，控制塊位於快取頁之前。像下面這樣:
這個碎片是啥? 申請的記憶體空間在分配了控制塊和快取頁，不夠一個控制塊和快取頁所需的記憶體空間唄，又假設控制塊和快取頁所佔用的記憶體一樣，我們極端假設一下快取池只有57KB，只夠湊一個控制塊和快取頁，剩下25KB，這25KB就是記憶體碎片。
每個控制塊大約佔用快取頁大小的5%，在MySQL 5.7.21這個版本佔用的大小是808位元組。而我們設定的innodb_buffer_pool_size並不包含這部分控制塊佔用的記憶體大小，也就是說InnoDB在Buffer Pool向作業系統申請連續的記憶體空間時，這片連續的記憶體空間一般會比innodb_buffer_pool_size的值大5%左右。Buffer Pool的大小可以通過MySQL的配置檔案my.ini來指定，現在我們來看一下我MySQL下面Buffer Pool的配置:
InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and row data. The bigger you set this the less disk I/O is needed to
access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it
too large, though, because competition of the physical memory may cause paging in the operating system. Note that on 32bit systems you might be limited to 2-3.5G of user level memory per process, so do not set it too high.InnoDB儲存引擎，使用快取池快取索引和行資料，快取池越大磁碟IO越少，在專門的資料庫伺服器上你可以將這個引數調整為機器實體記憶體的百分之八十以上。不要設定的太高，否則程式間競爭實體記憶體可能會導致作業系統的分頁(關於作業系統管理記憶體相關的知識，我已經忘的差不多了，今年打算重修，對這塊瞭解的可以在評論區留言)。注意在32位作業系統上，每個程式可能會被限制只能使用2-3.5G的記憶體。
innodb_buffer_pool_size=8M
總共快取池的大小是8M
innodb_buffer_pool_instances=8
這個不代表有8個快取池
innodb_buffer_pool_size/innodb_buffer_pool_instances 是每個快取池例項的大小，當innodb_buffer_pool_size的值小於1G，innodb_buffer_pool_instances這個引數設定就是無效的。MySQL官方推薦在innodb_buffer_pool_size大於1G的情況下，設定多個buffer pool例項。
free連結串列(空閒連結串列)
MySQL啟動的時候，就會完成對Buffer Pool的初始化過程，就是先向作業系統申請Buffer Pool的記憶體空間，然後把它劃分成若干對控制塊和快取頁，但目前快取池中所有的控制塊和快取頁還沒有儲存資訊，隨著MySQL開始接收查詢請求，InnoDB開始從磁碟頁中提取資料，快取池的控制塊和快取頁開始儲存資訊，為了確定從磁碟上提取的頁該放到哪個快取頁，區分哪些快取頁是空閒的，哪些快取頁是已經被使用的，快取頁對應的控制塊就派上了用場，MySQL的開發者將所有空閒的快取頁對應的控制塊做為一個結點放到一個連結串列中，這個連結串列我們稱之為free連結串列。剛剛初始化完成的快取池所有的快取頁都是空閒的，所以每一個快取頁對應的控制塊都會被加入到free連結串列中。
黃色的結點是連結串列的頭結點，記錄連結串列首結點和尾結點，以及當前連結串列中結點的數量等資訊。連結串列的頭結點佔用的記憶體空間並不大，在MySQL5.7.21這個版本，每個頭結點只佔用40位元組大小。有了這個free連結串列之後事就好辦了，每當需要從磁碟中載入一個頁到快取池中時，就從空閒連結串列中取一個空閒的快取頁，並且把該快取頁對應的控制塊的資訊填上(就是該頁對應的頁號之類的資訊)，並且把該快取頁對應的free連結串列結點從連結串列中移除，表示該快取頁已經被使用了。
LRU連結串列
Buffer Pool本質上是InnoDB向作業系統申請的一塊連續的記憶體空間,是有限的,我們不可能將所有的磁碟頁都載入進入記憶體，那我們該如何快取資料頁呢？或者說我們該如何制定快取策略呢？ 理想的情景是訪問某個資料頁的時候，這個資料頁已經在快取池裡面了。這也就是LRU，LRU全稱: Least Recently Used, 按照最近最少使用的，也就是說當Buffer Pool中不再有空閒的快取頁時，就需要淘汰部分最近很少使用的快取頁，那麼我們該如何知道哪些快取頁最近頻繁使用，哪些最近很少使用呢？ 我們可以藉助於連結串列，採取LRU策略:
當我們需要訪問某個頁時:
- 如果該頁不在快取池裡，，就把該頁從磁碟載入到快取池中的快取頁時，就把該快取頁對應的控制塊作為連結串列的頭結點。
- 如果該頁已經快取在快取池裡，則直接把該頁對應的控制塊移動到連結串列頭部。
那麼該連結串列的尾部就是最近最少使用的快取頁嘍，當Buffer Pool中的空閒快取頁使用完時，到LRU連結串列的尾部找些快取頁淘汰就可以了。
這個問題還沒有被完全解決, 雖然從巨集觀上來看LRU策略沒什麼問題, 但我們仍然需要根據實際情況對LRU策略做些小補丁, 專門應對一些特殊的狀況:
- 需要掃描全表的查詢語句(沒有用到索引, 沒有根WHERE子句的查詢)，掃描全表意味著需要訪問該表所有的資料頁, 假設這個表中記錄非常多，又假設這張表資料比較大，那麼就意味著快取池中的所有頁都被換了一次血，其他查詢語句在執行時又得執行一次從磁碟載入到快取池的操作。這種全表掃描的語句執行頻率也不高，但是每次執行都會把快取池中的快取頁換一次血，嚴重影響其他查詢對快取池的使用，從而大大降低了快取的命中率(快取的命中率 = 快取頁的訪問次數 除以 快取頁在快取的次數)。
MySQL的預判, 像是我們玩英雄聯盟會預判對手的操作一樣，MySQL也有對查詢請求的預判，我們稱之為預讀, 所謂的預讀，就是InnoDB不僅僅會載入查詢請求所對應的資料頁, 還會額外載入一些資料頁, 根據觸發方式不同，預讀又可以細分為下邊兩種:
- 線性預讀
隨機預讀
介紹這兩種預讀, 需要懂一些MySQL如何組織資料，我們這裡來簡單介紹一下，到目前為止我們知道InnoDB以頁為基本單位管理資料，每個索引都對應著一棵B+樹，該B+樹的每個結點都是一個資料頁，資料頁之間不是位於連續的記憶體空間，因為資料頁之間有雙向連結串列來維護著這些頁的順序。InnoDB的聚簇索引的葉子結點儲存了完整的使用者記錄，也就是所謂的索引即資料，資料即索引。
為了更好的管理資料頁，MySQL在資料頁的基礎上設計了表空間(table space 有的也稱file space)這個概念, 這個表空間是一個抽象的概念，它可以對應檔案系統上一個或多個真實檔案(不同的表空間對應的檔案數量可能不同)。每一個表空間可以被劃分為許多個頁，我們的表資料就存放在表空間的資料頁裡。
InnoDB表空間又分為多種型別:
- 系統表空間: 需要注意的一點是，在MySQL伺服器中，系統表空間只有一份。從MySQL 5.5.7到 MySQL5.6.6之間的各版本，我們表中的資料會被預設儲存在這個系統表空間中。
- 獨立表空間
那這些資料儲存在磁碟上的哪裡呢？ 我們可以通過:
SHOW VARIABLES LIKE 'datadir';命令來檢視資料目錄：
我們看下這個資料目錄下有什麼:
數了數一共六個資料夾剛好對應六個資料庫, 我們進studydatabase這個資料夾看一下，
studydatabase一共有三張表: score、student、student_info, 一張表對應兩個檔案, 表名.frm儲存表的結構資訊，ibd儲存表的資料資訊。
對於表空間來說，MySQL設立了區的概念來管理頁(英文名: extent)，對於16KB的頁來說，連續64個頁就是一個區。有了區這個概念我們回頭接著來介紹MySQL的預判:
- 線性預讀
如果順序訪問某個區的頁面超過這個innodb_read_ahead_threshold這個系統變數的值，就會觸發一次下一個區中全部的頁面到Buffer Pool的請求。
- 隨機預讀
如果Buffer Pool中已經快取了某個區的13個連續的頁面，不論這些頁面是不是順序讀取的，MySQL都會非同步將這些頁面所在區的所有頁面載入到快取池中。我們可以通過innodb_random_read_ahead來關閉開啟隨機預讀。預設為關閉狀態。
預判本來是個好事情, 但是如果預判出錯, 這些預讀的頁都放到LRU連結串列的頭部，碰巧我們的快取池也不大，這就會導致LRU連結串列會被淘汰掉，大大降低快取命中率。
針對這兩種情況，MySQL將LRU連結串列按照一定比例分成兩段。分別是:
- 一部分儲存使用頻率非常高的快取頁，這部分連結串列我們稱之為熱資料或者young區域
- 另一部分儲存使用頻率不是很高的快取頁，所以這一部分連結串列也叫冷資料，或者old區域。
熱區域的資料和冷區域的資料是不固定的，冷區域的資料也可能被轉化為熱區域的資料。我們可以通過:
SHOW VARIABLES LIKE 'innodb_old_blocks_pct';來檢視熱區域和冷區域的比例,
預設情況下old區域佔37%，有了這個劃分之後，上面兩種情況的補丁就好打了:
- 針對預讀這種情況，初次載入資料頁到快取池中，會被放在old區域的頭部，這樣預讀的資料頁卻不經常被訪問到的，就會慢慢從連結串列的尾部淘汰。
針對全表掃描這種查詢頻率十分低的場景，對某個處在old區域的快取頁進行第一次訪問時在它對應的控制塊中記錄下這個訪問時間，如果後序的訪問時間與第一次訪問的時間在某個時間間隔內，那麼該頁面就不會從old區域移動到young區域的頭部。這個間隔時間是由innodb_old_blocks_time控制的 , 通過innoddb_old_blocks_time來控制:
SHOW VARIABLES LIKE 'innodb_old_blocks_time';
事實上僅僅這兩個補丁還不夠，還得繼續打下去，但繼續講下去並不是本篇的主題。
flush連結串列的管理
如果我們修改的某條資料，先將該頁載入進入到快取頁中，然後直接對快取頁進行修改，那麼此時快取頁的資料就和磁碟頁的資料不一致了。當然，我們也可以修改完快取頁的時候馬上同步到磁碟對應的頁上，但是頻繁的和記憶體進行互動相當影響效能，畢竟磁碟讀寫的速度相當慢。所以每次修改完快取頁的時候，MySQL並不會立即把修改同步到磁碟上，而是在某個時間點進行同步。
但如果不立即進行同步的話，那我們該如何知道Buffer Pool中的哪些頁是髒頁呢？連結串列同志，請再次出場，所有的髒頁對應的控制塊都會作為結點加入到一個連結串列中，因為這個連結串列對應的快取頁都是需要被重新整理到磁碟上的，所以也叫flush連結串列。
InnoDB還有其他形式的連結串列，比如unzip LRU連結串列用於管理解壓頁等等，我們上面介紹buffer pool配置的時候，預設配置是8個，Buffer Pool本質上來說Buffer Pool是InnoDB向作業系統申請的一塊連續的記憶體空間，在多執行緒環境下，訪問快取池的連結串列都需要加鎖處理，在快取池比較大且高併發訪問下，單個buffer pool 可能會影響處理速度，所以如果單個buffer pool特別大的時候，我們可以將它們拆分為若干個小的Buffer Pool。每個Buffer Pool都是獨立的，多執行緒併發訪問並不會相互影響，從而提高處理併發的能力。
buffer pool的一些注意事項
在MySQL 5.7.5 之前，Buffer Pool的大小隻能在MySQL啟動的時候指定，在MySQL 5.7.5之後的版本包括MySQL5.7.5，MySQL支援在執行時調整快取池大小，MySQL以chunk為單位向作業系統申請記憶體地址空間，也就是快取池又可以看做是若干chunk組成的。一個chunk代表一個連續的記憶體地址空間。這個chunk的大小由配置檔案中的innodb_buffer_pool_chunk_size來指定。
為了保證每一個buffer pool例項中包含的chunk數量相同，innodb_buffer_pool_size 必須是 innodb_buffer_pool_chunk_size 乘 innodb_buffer_pool_instances的倍數。如果你設定的不是，MySQL會自動調整，舉個例子，innodb_buffer_pool_chunk_size 乘 innodb_buffer_pool_instances = 2G，你指定的是9G，MySQL會自動將其提升為10G。
如果服務啟動的時候，innodb_buffer_pool_size大於 innodb_buffer_pool_chunk_size 乘 innodb_buffer_pool_instances，那麼innodb_buffer_pool_chunk_size 的值會被設定為 innodb_buffer_pool_size / innodb_buffer_pool_instances.舉個例子, 假設你將innodb_buffer_pool_size設定為2G，innodb_buffer_pool_chunk_size = 128M，innodb_buffer_pool_instances = 32，也就是4G。那麼innodb_buffer_pool_chunk_size會被調整為64M。
總結一下
本文基本上的寫作思路是，現在MySQL查詢速度比較緩慢，在有效的利用索引的情況下，還能怎麼提升MySQL的執行速度，帶著疑問去閱讀掘金小冊《MySQL 是怎樣執行的：從根兒上理解 MySQL》的buffer pool章節，最終的答案就是檢視buffer pool的配置是否合理，多數的內容都從其摘錄而來，用帶著問題的方式又將其的內容又組合了一下。