MySQL-change_buffer验证

关于change buffer原理大家已经耳熟能详了，这里就不再详细说明

直接说重点

• 针对二级索引的更新（insert、delete、update）缓冲
• 将理论上的多次IO转变为一次IO，提升性能
• 只能针对非唯一普通索引有效
• 能大大提升写多读少场景的效果

接下来通过实操为大家演示change_buffer的作用

开启change buffer

1、设置参数innodb_change_buffering

all：全部
none：禁用
insert
delete
update

2、本次实验我们设置为all，也是生产常用

set global innodb_change_buffering=all;

测试环境准备

1、使用t100w表测试

表结构如下：
CREATE TABLE t100w (
id int(11) DEFAULT NULL,
num int(11) DEFAULT NULL,
k1 char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
k2 char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_k1 (k1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

注意重点，k1字段上有普通索引

2、重启数据库

目的是为了刷新change_buffer统计信息便于观察

select name,count from information_schema.innodb_metrics where subsystem like ‘change_buffer’ \G;

测试insert

1、向表中插入数据

insert into t100w values(999999,12300000,‘II’,‘Dc’,‘2023-11-20 09:44:55’);

2、查看change_buffer统计信息

mysql> select name,count from information_schema.innodb_metrics where subsystem like ‘change_buffer’ \G;
*************************** 1. row ***************************
name: ibuf_merges_insert
count: 1
*************************** 2. row ***************************
name: ibuf_merges_delete_mark
count: 0
*************************** 3. row ***************************
name: ibuf_merges_delete
count: 0
*************************** 4. row ***************************
name: ibuf_merges_discard_insert
count: 0
*************************** 5. row ***************************
name: ibuf_merges_discard_delete_mark
count: 0
*************************** 6. row ***************************
name: ibuf_merges_discard_delete
count: 0
*************************** 7. row ***************************
name: ibuf_merges
count: 1
*************************** 8. row ***************************
name: ibuf_size
count: 1

可以看到 ibuf_merges_insert已经加1

测试update

1、修改表中数据

update t100w set k1=‘PP’ where id=99999999;

2、查看change buffer统计信息

mysql> select name,count from information_schema.innodb_metrics where subsystem like ‘change_buffer’ \G;
*************************** 1. row ***************************
name: ibuf_merges_insert
count: 0
*************************** 2. row ***************************
name: ibuf_merges_delete_mark
count: 1
*************************** 3. row ***************************
name: ibuf_merges_delete
count: 0
*************************** 4. row ***************************
name: ibuf_merges_discard_insert
count: 0
*************************** 5. row ***************************
name: ibuf_merges_discard_delete_mark
count: 0
*************************** 6. row ***************************
name: ibuf_merges_discard_delete
count: 0
*************************** 7. row ***************************
name: ibuf_merges
count: 1
*************************** 8. row ***************************
name: ibuf_size
count: 1

可以看到 ibuf_merges_delete_mark加1

测试delete

1、删除表中数据

delete from t100w where id=99999999;

2、查看change buffer统计信息

mysql> select name,count from information_schema.innodb_metrics where subsystem like ‘change_buffer’ \G;
*************************** 1. row ***************************
name: ibuf_merges_insert
count: 0
*************************** 2. row ***************************
name: ibuf_merges_delete_mark
count: 1
*************************** 3. row ***************************
name: ibuf_merges_delete
count: 0
*************************** 4. row ***************************
name: ibuf_merges_discard_insert
count: 0
*************************** 5. row ***************************
name: ibuf_merges_discard_delete_mark
count: 0
*************************** 6. row ***************************
name: ibuf_merges_discard_delete
count: 0
*************************** 7. row ***************************
name: ibuf_merges
count: 1
*************************** 8. row ***************************
name: ibuf_size
count: 1

可以看到 ibuf_merges_delete_mark加1加1
当执行多次update、delete时，数值会随修改行数累加

彩蛋

当先使用二级索引查询后，再修改、删除数据
1、利用二级索引查询数据

select * from test.t100w where k1=‘ss’;
±-------±-------±-----±-----±--------------------+
| id | num | k1 | k2 | dt |
±-------±-------±-----±-----±--------------------+
| 558 | 658360 | ss | HIde | 2023-11-20 15:48:05 |
| 1813 | 965222 | ss | IJ45 | 2019-07-09 16:01:43 |
| 2721 | 126730 | ss | IJ78 | 2019-07-09 16:01:44 |
| 10666 | 128147 | ss | MNmn | 2019-07-09 16:01:51 |
| 14427 | 333499 | ss | JKbc | 2019-07-09 16:01:55 |
| 909090 | 513553 | ss | JKde | 2019-07-09 16:01:57 |

2、修改数据

update t100w set k1=‘WW’ where id=909090;

3、查看统计信息

select name,count from information_schema.innodb_metrics where subsystem like ‘change_buffer’ \G;
*************************** 1. row ***************************
name: ibuf_merges_insert
count: 0
*************************** 2. row ***************************
name: ibuf_merges_delete_mark
count: 0
*************************** 3. row ***************************
name: ibuf_merges_delete
count: 0
*************************** 4. row ***************************
name: ibuf_merges_discard_insert
count: 0
*************************** 5. row ***************************
name: ibuf_merges_discard_delete_mark
count: 0
*************************** 6. row ***************************
name: ibuf_merges_discard_delete
count: 0
*************************** 7. row ***************************
name: ibuf_merges
count: 0
*************************** 8. row ***************************
name: ibuf_size
count: 1

可以看到统计信息没有变化

这里也再次验证了，当数据页已经在buffer_pool中时便不会再用到change_buffer