关于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 TABLEt100w
(
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,
KEYidx_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
当执行多次insert、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