关于mysql:技术分享-MySQLchange-buffer-何时生效

37次阅读

共计 4358 个字符,预计需要花费 11 分钟才能阅读完成。

作者:胡呈清

爱可生 DBA 团队成员,善于故障剖析、性能优化,集体博客:https://www.jianshu.com/u/a95…,欢送探讨。

本文起源:原创投稿

* 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。


已知 change buffer 的原理

参考资料:https://juejin.im/post/684490…

对于一般二级索引,当插入、批改、删除二级索引记录时,即便数据不在 innodb buffer pool 中,也不须要先把数据从磁盘读取到内存。只须要在 change buffer 中实现 DML 操作,下次读取时才会从磁盘读取数据页到内存,并与 change buffer 进行 merge,从而失去正确的数据。这缩小了 DML 时的随机 IO。

疑难

依照上述原理,应用 change buffer 二级索引不须要读取磁盘,那 delete、update 是如何失去 affected rows 的?

无妨先作出假如:

  • 如果 delete、update 是以主键、惟一索引做为筛选条件,则读取磁盘或者 innodb buffer pool 中的主键、惟一索引来确定 affected rows。对于一般索引页上记录的删除或者批改,还是间接应用 change buffer,不须要独自将一般索引页从磁盘上读取到内存。
  • 如果 delete、update 是以一般二级索引做为筛选条件,以 delete 为例(update 外部实现是先 delete 再 insert):delete from t where a=100; 如果索引页不在内存中,则须要先从磁盘读取 a 索引,找到 a = 100 的记录对应的 id(主键值),再从磁盘扫描主键索引(回表)将 id 满足条件的记录读取到内存。而后在 innodb buffer pool 中把对应的主键索引页、二级索引页中的记录删除。这里不应用 change buffer。

验证

接下来设计两个试验来验证上述假如。

试验 1 - 以主键为筛选条件做 delete

用 sysbench 造一张 100 万行的表,表中有一个主键和一个一般索引:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
);

重启 mysqld,清空 innodb buffer pool,留神参数:

innodb_buffer_pool_size = 64M
innodb_buffer_pool_load_at_startup = 0
innodb_buffer_pool_dump_at_shutdown = 0
innodb_buffer_pool_dump_pct = 0

执行 delete,并应用 show engine innodb status\G 察看INSERT BUFFER AND ADAPTIVE HASH INDEX 局部信息,判断是否应用 change buffer:

mysql> delete from sbtest1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 1 merges
merged operations:
 insert 0, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
mysql> delete from sbtest1 where id=2;
Query OK, 1 row affected (0.00 sec)
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 2 merges
merged operations:
 insert 0, delete mark 2, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
mysql> delete from sbtest1 where id=3;
Query OK, 1 row affected (0.00 sec) 
 -------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 3 merges
merged operations:
 insert 0, delete mark 3, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
mysql> select * from sbtest1 where id=4;
mysql> delete from sbtest1 where id=4;
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 4 merges
merged operations:
 insert 0, delete mark 4, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

上述试验阐明:如果 delete 是以主键做为筛选条件,对于一般索引 k,如果索引页不在内存中(select * from sbtest1 where id=4 读取的只是主键索引页,不会读取 k 索引页),会应用 change buffer(每次 delete 后,delete mark 都减少 1)。

试验 2 - 以一般索引为筛选条件做 delete

从新造数据,重启 mysqld 清空 buffer pool。上面试验后果阐明:如果 delete 以一般索引做为筛选条件,对于一般索引 k,如果索引页不在内存中,不会应用 change buffer。话中有话就是须要读取磁盘了。

##delete where id=1,delete mark +1,阐明应用了 change buffer
mysql>  delete from sbtest1 where id=1;
Query OK, 1 row affected (0.01 sec)
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 1 merges
merged operations:
 insert 0, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
##delete where k=367246,delete mark 不变,阐明没有应用 change buffer
mysql> select * from sbtest1 where id=2;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  2 | 367246 | 42909700340-70078987867-62357124096-35495169193-85675377266-14643719347-30417020186-80900182681-50382374444-66260611196 | 74781290517-41121402981-50604677924-34464478849-89102349959 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> delete from sbtest1 where k=367246;
Query OK, 1 row affected (0.01 sec)

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 1 merges
merged operations:
 insert 0, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

正文完
 0