change buffer官方说明： https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html
change buffer官方解答： https://dev.mysql.com/doc/refman/5.7/en/faqs-innodb-change-buffer.html
设计背景：
与聚集索引不同，二级索引通常是非唯一的，像delete和update操作的二级索引树中通常都是顺序相对随机不相邻的索引页，
这样会照成大量随机I/O
工作机制：
DML(insert,update,delete)操作使用到的二级索引页如果不在buffer pool中，就把它缓存到buffer pool的change buffer中。后续再进行合并操作，批量写回disk( master thread在服务器空闲时和slow shutdown期间会合并缓冲更改)。可避免大量随机I/O
注：change buffer不支持descending index,Clustered indexes,full-text indexes,spatial indexes
控制参数:
innodb_change_buffering :change buffer缓存级别有
innodb_change_buffering values include:
-
all 默认开启
The default value: buffer inserts, delete-marking operations, and purges.
-
none
Do not buffer any operations.
-
inserts
Buffer insert operations.
-
deletes
Buffer delete-marking operations.
-
changes (其实update操作就是做的 inserts and delete-marking)
Buffer both inserts and delete-marking operations.
-
purges
Buffer physical deletion operations that happen in the background.
innodb_change_buffer_max_size:change buffer是buffer pool中一块区域，5.6版本后默认大小是buffer pool的25%，参数
innodb_change_buffer_max_size
控制。
注：在内存中， change buffer占用buffer pool的一部分。在磁盘上， change buffer是system tablespace的一部分，当数据库服务器关闭时，索引更改将在其中缓冲。
使用监控
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
mysql> SHOW ENGINE INNODB STATUS\G-------------------------------------INSERT
BUFFER
AND
ADAPTIVE HASH
INDEX-------------------------------------Ibuf:
size
1,
free
list len 0, seg
size
2, 0 merges#
size
：change buffer中使用的页数。change buffer大小等于seg
size
-（1+
free
list len）#1+表示change buffer header page使用seg
size
：更改缓冲区的大小，以页为单位。merged operations:
insert
0,
delete
mark 0,
delete
0discarded operations:
insert
0,
delete
mark 0,
delete
0Hash
table
size
4425293, used cells 32, node heap has 1 buffer(s)13577.57 hash searches/s, 202.47 non-hash searches/s |
change buffer 占用率
|
1
2
3
4
5
6
7
8
9
10 |
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 |+
---------------------+-------------+-------------------------------+| 25 | 8192 | 0.3052 |+
---------------------+-------------+-------------------------------+ |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69978212/viewspace-2729609/ ，如需转载，请注明出处，否则将追究法律责任。
