Technology Sharing | MySQL: When will the change buffer take effect?

Author: Hu Chengqing

, good at failure analysis and performance optimization, personal blog: 161f25f1a6a1dd https://www.jianshu.com/u/a95ec11f67a8 , welcome to discuss.

Source of this article: original contribution

* Produced by the open source community of Aikesheng, the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

The principle of known change buffer

Reference: https://juejin.im/post/6844903875271475213

For ordinary secondary indexes, when inserting, modifying, or deleting secondary index records, even if the data is not in the innodb buffer pool, there is no need to read the data from the disk to the memory first. Only need to complete the DML operation in the change buffer, the next time the data page is read from the disk to the memory, and merge with the change buffer, so as to get the correct data. This reduces random IO during DML.

doubt

According to the above principle, using the change buffer secondary index does not need to read the disk, so how do delete and update get affected rows?

answer

Let's make an assumption first:

• If delete and update use primary key and unique index as filter conditions, read the primary key and unique index in disk or innodb buffer pool to determine affected rows. For the deletion or modification of records on ordinary index pages, the change buffer is directly used, and there is no need to read ordinary index pages from disk to memory separately.
• If delete and update use ordinary secondary indexes as filter conditions, take delete as an example (the internal implementation of update is to delete first and then insert): delete from t where a=100; Read the a index, find the id (primary key value) corresponding to the record with a = 100, and then scan the primary key index (return table) from the disk to read the records whose id satisfies the conditions to the memory. Then delete the records in the corresponding primary key index page and secondary index page in the innodb buffer pool. Change buffer is not used here.

verify

Next, two experiments were designed to verify the above hypothesis.

Experiment 1 - delete with the primary key as the filter condition

Use sysbench to create a table of 1 million rows with a primary key and a normal index:

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`)
);

Restart mysqld, clear the innodb buffer pool, pay attention to the parameters:

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

Execute delete, and use show engine innodb status\G observe INSERT BUFFER AND ADAPTIVE HASH INDEX determine whether to use 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

The above experiment shows that if delete uses the primary key as the filter condition, for ordinary index k, if the index page is not in memory (select * from sbtest1 where id=4 reads only the primary key index page, and does not read the k index page) , the change buffer will be used (the delete mark will increase by 1 after each delete).

Experiment 2 - delete with ordinary index as filter condition

Recreate the data, restart mysqld to clear the buffer pool. The following experimental results show that if delete uses a common index as a filter condition, for common index k, if the index page is not in memory, the change buffer will not be used. The implication is that the disk needs to be read.

##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