Change Buffer only applies to non-unique index pages? wrong

Recently I saw some articles on the Internet that said: "Change buffer is only applicable to non-unique index pages." In fact, this view is wrong. Let's take a look at the introduction of change buffer in official documents:

Document address: https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool.

The meaning here is that caching secondary index pages that are not in the buffer pool does not refer to non-unique secondary indexes. What are the specific conditions for using change buffer? In fact, the specific conditions of use are mainly collected in the following five points:

• The user sets the option innodb_change_buffering.
• In the index structure of mysql, only leaf nodes store data. Therefore, there are leaf nodes to consider whether to use ibuf.
• As shown in the documentation above, the change buffer can only cache secondary index pages, so for clustered indexes, operations cannot be cached. Clustered index pages are loaded by the Innodb engine into the Buffer Pool (this search process is sequential I/O), and then data records are inserted, updated, or deleted.
• Because the index records of the unique secondary index (unique key) are unique, insert and update operations cannot be cached, but delete operations can be cached;
• There is no flush operation on the table. For example, when flush table for export is executed, ibuf caching of the table is not allowed (identified by dict_table_t::quiesce)

Next, we combine the source code and documentation to see the specific operation.

Source address: GitHub - mysql/mysql-server: MySQL Server, the world's most popular open source database, and MySQL

Let's first look at the end of the first setting option innodb_change_buffering, which can cache three types of operations INSERT, DELETE-MARK, and DELETE. The three corresponding dml statements are related as follows:

• INSERT operation: Insert secondary index.
• First perform the DELETE-MARK operation, and then perform the INSERT operation: update the secondary index.
• DELETE-MARK operation: delete secondary index

 // 代码路径：storage/innobase/include/ibuf0ibuf.h
 
/* Possible operations buffered in the insert/whatever buffer. See
ibuf_insert(). DO NOT CHANGE THE VALUES OF THESE, THEY ARE STORED ON DISK. */
typedef enum {
  IBUF_OP_INSERT = 0,
  IBUF_OP_DELETE_MARK = 1,
  IBUF_OP_DELETE = 2,

  /* Number of different operation types. */
  IBUF_OP_COUNT = 3
} ibuf_op_t;

/** Combinations of operations that can be buffered.
@see innodb_change_buffering_names */
enum ibuf_use_t {
  IBUF_USE_NONE = 0,
  IBUF_USE_INSERT,             /* insert */
  IBUF_USE_DELETE_MARK,        /* delete */
  IBUF_USE_INSERT_DELETE_MARK, /* insert+delete */
  IBUF_USE_DELETE,             /* delete+purge */
  IBUF_USE_ALL                 /* insert+delete+purge */
};

In addition, innodb_change_buffering can also perform corresponding caching operations by setting other options:

• all: Default value, buffer inserts, delete-marking operations, and purges are enabled by default.
• none: do not enable change buffer.
• inserts: just enable the buffer insert operation.
• deletes: just enable delete-marking operations.
• changes: Enable buffer insert operation and delete-marking operation.
• purges: Enable the buffer function for physical delete operations that are only performed in the background.

The second point still needs everyone to judge the conditions, so we will not expand the explanation, let's talk about the third point in detail.

The specific reference function of the third point is ibuf_should_try. After it meets the ibuf cache condition, it will use two modes to try to get the data page.

Let me explain here that in versions before MySQL 5.5, it was originally called insert buffer because it only supported the cache insert operation, but it was changed to change buffer when more operation types were supported in later versions. Change buffer related functions or variables still start with the ibuf prefix.

The following is the specific implementation of the function, the address is: storage/innobase/include/ibuf0ibuf.ic

 /** A basic partial test if an insert to the insert buffer could be possible and
 recommended. */
static inline ibool ibuf_should_try(
    dict_index_t *index,     /*!< in: index where to insert */
    ulint ignore_sec_unique) /*!< in: if != 0, we should
                             ignore UNIQUE constraint on
                             a secondary index when we
                             decide */
{
  return (innodb_change_buffering != IBUF_USE_NONE && ibuf->max_size != 0 &&
          index->space != dict_sys_t::s_dict_space_id &&
          !index->is_clustered() && !dict_index_is_spatial(index) &&
          !dict_index_has_desc(index) &&
          index->table->quiesce == QUIESCE_NONE &&
          (ignore_sec_unique || !dict_index_is_unique(index)) &&
          srv_force_recovery < SRV_FORCE_NO_IBUF_MERGE);
}

The place in bold and red is the place where the unique secondary index is judged, which means:

• When the ignore_sec_unique variable is 0, it cannot be used if the only secondary index record is to be modified.
• When the ignore_sec_unique variable is 1, if you modify the unique secondary index record, you can also try to use it.

The value of ignore_sec_unique is: storage/innobase/btr/btr0cur.cc

 if (btr_op != BTR_NO_OP &&
        ibuf_should_try(index, btr_op != BTR_INSERT_OP)) {
      /* Try to buffer the operation if the leaf
      page is not in the buffer pool. */

      fetch = btr_op == BTR_DELETE_OP ? Page_fetch::IF_IN_POOL_OR_WATCH
                                      : Page_fetch::IF_IN_POOL;
    }

The btr_op in the red box refers to the specific operation of this modification, that is:

When the specific modification operation is an INSERT operation, ignore_sec_unique is 0, that is, when the modification is a unique secondary index record, ibuf cannot be used.

• When the specific modification operation is not an INSERT operation, ignore_sec_unique is 1, that is, when the modification is a unique secondary index record, you can try to use ibuf.
• It can be seen that when the ibuf_should_try function returns 1, that is, you can try to use ibuf, then change the mode of reading pages in the buffer pool.

Finally, to sum up, after reading this article, I believe that everyone can clearly realize that the online statement that only non-unique indexes can use change buffers is undoubtedly wrong. As long as the other four conditions are met, the delete operation on the unique index can be optimized using the change buffer.

Recommended reading

JavaScript memory management mechanism

130 lines of code to get nucleic acid statistics, programmers have a lot of energy during the anti-epidemic period