Author: Hu Chengqing
Member of aikesheng DBA team, good at fault analysis and performance optimization, personal blog: https://www.jianshu.com/u/a95... , welcome to the discussion.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
The principle of change buffer is known
reference material: https://juejin.im/post/684490...
For ordinary secondary indexes, when inserting, modifying and deleting secondary index records, even if the data is not in the innodb buffer pool, it is not necessary to read the data from disk to memory first. You only need to complete the DML operation in the change buffer. The data page will be read from the disk to the memory the next time it is read, and merge d with the change buffer to get the correct data. This reduces random IO in DML.
doubt
According to the above principle, there is no need to read the disk when using the change buffer secondary index. How do delete and update get affected rows?
answer
Let's make assumptions first:
- If the delete and update filter conditions are based on the primary key and unique index, read the primary key and unique index in the disk or innodb buffer pool to determine the affected rows. For the deletion or modification of records on the ordinary index page, the change buffer is used directly, and there is no need to read the ordinary index page from disk to memory separately.
- If the common secondary index is used as the filter criteria for delete and update, take delete as an example (the internal implementation of update is to delete and then insert): delete from t where a=100; If the index page is not in memory, you need to first read the a index from the disk, find the id (primary key value) corresponding to the record with a = 100, and then read the record whose id meets the conditions from the disk scan primary key index (back to the table). Then delete the records in the corresponding primary key index page and secondary index page in innodb buffer pool. change buffer is not used here.
verification
Next, two experiments are designed to verify the above hypothesis.
Experiment 1 - delete with primary key as filter condition
Create a 1 million row table with sysbench, which has a primary key and a common 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 and clear innodb buffer pool. Note the following 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 to observe the information of INSERT BUFFER AND ADAPTIVE HASH INDEX and judge 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 the delete is based on the primary key as the filter condition, for the ordinary index k, if the index page is not in memory (select * from sbtest1 where id=4, only the primary key index page is read, and the k index page will not be read), the change buffer will be used (the delete mark will be increased by 1 after each delete).
Experiment 2 - delete with the general index as the screening condition
Recreate the data, restart mysqld and empty the buffer pool. The following experimental results show that if the delete takes the ordinary index as the filter condition, for the ordinary index k, if the index page is not in memory, the change buffer will not be used. The implication is that you need to read the disk.
##delete where id=1, delete mark +1, indicating that change buffer is used 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 remains unchanged, indicating that change buffer is not used 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