數據庫系統中,為了加快處理速度,通過數據文件加載到內存,之后讀取內存中的數據。MySQL的Innodb引擎緩存池,如Oracle的SGA(系統全局內存區)共享緩沖存儲區一樣,實現對數據庫數據的管理和操作。MySQL的Innodb引擎緩存池可分為4個區域,支持不同的內存服務體系。
Buffer Pool
MySQL里保存內存數據的地方就叫緩沖池,緩沖池里包含實際數據區域和索引數據的區域。緩沖池允許直接從內存訪問頻繁使用的數據。在服務器上,通常物理內存的50%~80%的分配給緩沖池。
數據和索引加載到內存提供了更快的訪問速度,但大量的數據能否全部加載到緩沖池中,那是不可能的。所以有限的內存空間,采用經典的LRU淘汰算法。
使用LRU算法將緩沖池作為列表進行管理:當需要空間向緩沖池添加新頁時,將刪除最近最少使用的頁,并將新頁添加到列表的中間。這種中點插入策略將列表分成兩個子列表。
-
頭部,最近被訪問的新(“young”)頁面的子列表;
-
尾部,是最近訪問次數較少的舊頁面的子列表;
-
將經常使用的頁面保留在新的子列表中。舊的子列表包含較少使用的頁面;無緩沖空間的可用的時,將被驅逐;
-
默認已3,5原則劃分新舊數據區域;

-
對于監控緩沖池變化可以通過命令行可以查看。這些變化可以分析出緩沖池是否足夠滿足現有需求:
mysql> SHOW ENGINE INNODB STATUS\G;
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0 #分配給INNODB的總內存大小
Dictionary memory allocated 480465 #分析給INNODB數據字典的內存大小
Buffer pool size 8192 #分配給INNODB的總buffer pool大小,單位page
Free buffers 6975 #數據庫中innodb buffer pool中空閑page的數量
Database pages 1211 #數據庫中innodb buffer pool中非空閑page的數量。
Old database pages 467 #Old子列表中的page數量
Modified db pages 12 #當前buffer pool中被修改的page數量
Pending reads 4 #數據由磁盤讀到buffer pool,被掛起的次數
Pending writes: LRU 0, flush list 0, single page 0
#寫入被掛起的次數
#LRU鏈表的page被淘汰出內存,要寫入到磁盤
#check point操作期間page要被寫入到磁盤
#單個page要被寫入到磁盤
Pages made young 2, not young 0 #young:page由old列表移動到new列表的次數.
#not young:page由new列表移動表old列表的次數.
0.17 youngs/s, 0.00 non-youngs/s #youngs/s:平均每秒有多少個page由old移動到new
#non-youngs/s:平均每秒有多少個page由new移動到old
Pages read 1067, created 144, written 199
#從buffer pool中讀,創建,寫的page的總數
90.26 reads/s, 12.18 creates/s, 16.83 writes/s
#平均每秒從buffer pool中讀,創建,寫的page的數
Buffer pool hit rate 941 / 1000, young-making rate 0 / 1000 not 0 / 1000
#buffer pool的命中率,無限接近1。
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
#每秒read ahead的次數,每秒淘汰的page次數,隨機read ahead的次數
LRU len: 1211, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
影響緩存池
從上述監控緩沖池變化就能看出那些會影響緩沖池指標。
1.緩沖池大小
- 理想情況下,將緩沖池的大小設置為實際的最大值,緩沖池越大,InnoDB就越像一個內存數據庫,從磁盤讀取一次數據,之后讀取過程中從內存訪問數據。通過3個重要參數,構成數據&索引區域。
| 參數 | 說明 |
|---|---|
| innodb_buffer_pool_size | 緩沖池的大小(以字節為單位),InnoDB緩存表和索引數據的內存區域。 |
| innodb_buffer_pool_chunk_size | 緩沖池已塊chunk單位進行控制。innodb_buffer_pool_size應該等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍數。 |
| innodb_buffer_pool_instances | 使用散列函數,緩沖池劃分多個域,提高并發性,每個緩沖池管理自己的空閑列表、刷新列表、LRU等結構,并由自己的緩沖池互斥鎖保護。單個分配必須大于1G 才生效。建議不要超過CPU核算 |
備注:單個instance分配域必須大于1G才生效。避免潛在的性能問題,塊的數量(innodb_buffer_pool_size / innodb_buffer_pool_chunk_size)不應該超過1000。目前官方提供的innodb_buffer_pool_chunk_size沒有最大值限制。可以通過這個算法靈活控制大小。

2.新舊頁分配
緩沖池新舊配置基于協調LRU值和時間 進行替換規則:
| 參數 | 說明 |
|---|---|
| innodb_old_blocks_pct | 控制LRU列表中"old"塊的百分比。innodb_old_blocks_pct的默認值是37,對應原來的固定比率3/8。 |
| innodb_old_blocks_time | 防止緩沖池被預讀,新舊頁不停切換,可以避免由于表或索引掃描而產生的類似問題。第一次訪問一個頁面后的時間窗口(毫秒),在此期間,該頁面可以被訪問而不被移動到LRU列表的前面。 |
備注:隨著工作負載的變化,innodb_old_blocks_time參數的影響比innodb_old_blocks_pct參數更難預測,所以采取默認值即可。
3.預讀設置
InnoDB使用兩種預讀算法來提高I/O性能:線性預讀(linear read-ahead)和隨機預讀(randomread-ahead)。區別在于,線性預讀放到以extent為單位,而隨機預讀放到以extent中的page為單位。考驗的是硬件的IO處理能力。

| 參數 | 說明 |
|---|---|
| innodb_read_ahead_threshold | 控制是否將下一個extent預讀到buffer pool中,如果一個extent中的被順序讀取的page超過或者等于該參數變量時,Innodb將會異步的將下一個extent讀取到buffer pool中。 |
| innodb_random_read_ahead | 隨機預讀方式則是表示當同一個extent中的一些page在buffer pool中發現時,Innodb會將該extent中的剩余page一并讀到buffer pool中,由于隨機預讀方式給Innodb code帶來了一些不必要的復雜性,同時在性能也存在不穩定性 |
備注:因為數據的大小和行為 很難預估,當然內存足夠大,io能力強的話 完全可以都打開,所以采取默認值即可。
Change Buffer
Change Buffer是一種特殊的數據結構,當輔助索引頁不在緩沖池中時,它將更改緩存到輔助索引頁。緩沖的更改可能來自INSERT、UPDATE或DELETE等操作(DML),當其他讀操作將頁面加載到緩沖池中時,將合并這些更改。所以總結就是二級索引更新區。

與聚集索引不同,輔助索引通常不是唯一的,并且對輔助索引的插入以相對隨機的順序發生。類似地,刪除和更新可能會影響不在索引樹中鄰接位置的輔助索引頁。當受影響的頁面被其他操作讀入緩沖池時,通過合并緩存的更改,可以避免大量的隨機訪問I/O。
| 參數 | 說明 |
|---|---|
| innodb_change_buffer_max_size | 允許配置更改緩沖區的最大大小,從buffer pool總大小的百分比,這部分內存也包含在innodb_buffer_pool_size里。 |
| innodb_change_buffering | InnoDB執行更改緩沖的程度。允許的操作none,inserts,deletes,changes,purges,all |
備注:隨著工作負載的變化,innodb_old_blocks_time參數的影響比innodb_old_blocks_pct參數更難預測,所以采取默認值即可。
Change Buffer占據緩沖池總頁的百分比:
mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
(SELECT ((change_buffer_pages/total_pages)*100))
AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
| 2 | 8192 | 0.0244 |
+---------------------+-------------+-------------------------------+
備注:如果二級索引包含降序索引列,或者如果主鍵包含降序索引列,則不支持更改緩沖
Adaptive Hash Index
自適應哈希索引使InnoDB在緩沖池內存的hash內存數據庫,而不犧牲事務特性或可靠性。哈希索引通過支持對任何元素的直接查找,將索引值轉換為一種指針,從而加快查詢速度。InnoDB有一個監視索引搜索的機制。如果InnoDB注意到查詢可以從建立哈希索引中獲益,它就會自動這么做。
按照目前了解的自適應Hash索引InnoDB自創建條件如下:
- 索引中的頁訪問的模式是相同的,不包含主鍵;
- 索引命中的該頁上的記錄數要大于該頁上總記錄數的1/16;
- 索引中的某個頁已經被訪問了至少100次
滿足上面的條件就會自動添加到自適應hash索引中。自適應哈希索引特性是分區的。每個索引都綁定到一個特定的分區,每個分區都由一個單獨的鎖存器保護。
mysql> SHOW ENGINE INNODB STATUS\G;
。。。
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 10, free list len 174, seg size 176, 8 merges
#size 10:已經合并記錄頁的數量
#free list:插入緩沖中空閑列表長度
#seg size: 176顯示了當前insert buffer的長度,大小為176*16K/1024=2.75M。
#merges:合并插入的次數
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s) #Hash table size 34679表中2 使用中
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 114 buffer(s) #Hash table size 34679表中114 使用中
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
| 參數 | 說明 |
|---|---|
| innodb_adaptive_hash_index | InnoDB自適應哈希索引是否啟用。 |
| innodb_adaptive_hash_index_parts | 分區自適應哈希索引搜索系統。每個索引都綁定到一個特定的分區,每個分區由一個單獨的鎖存器保護。默認設置為8。最大可設置為512。 |
備注:MySQL的Adaptive Hash Index默認打開,按照理解基本在只讀場景下才能性能提高。因為自適應哈希本身維護鎖機制,其他操作可能會導致鎖等待,甚至導致MySQL hung或極端場景下的數據損壞。此外,AHI會消耗Buffer Pool的空間,雖然用索引構建,但索引字段不合理,這可能會影響整體性能。由于很難預先預測adaptive hash index功能是否適合特定系統和工作負載。所以IO負載高的場景下建議禁止。
Log Buffer
Log Buffer是存儲要寫入磁盤上redo文件的數據的內存區域。Log Buffer的內容定期會刷新到磁盤。保證操作日志樓盤,保證數據的一致性。通過日志緩沖區的批量處理機制,頻繁磁盤I/O交互。
| 參數 | 說明 |
|---|---|
| innodb_log_buffer_size | InnoDB寫入磁盤上日志文件的緩沖區大小(以字節為單位)。默認值是16MB。大的日志緩沖區使大型事務能夠運行,而不需要在事務提交之前將日志寫入磁盤。 |
| innodb_flush_log_at_trx_commit | 控制日志緩沖區的內容如何寫入和刷新到磁盤。 |
| innodb_flush_log_at_timeout | 控制日志緩沖區的內容刷新頻率。 |
備注:innodb_log_buffer_size的最大值可以達到4G的空間,可以支持到一次性4G的事務。但內存里尋址,也是很消耗性能的,同時占據Innodb整體緩存池的。所以MySQL里建議還是單個事務方式提交處理。
總結
通過MySQL Innodb內存結構的的理解,可以合理的設置內存參數,并且協助排查諸多這方面的問題。




