最近在网上看到一些文章里说:“change buffer 只适用于非唯一索引页。”其实这个观点是错的,先来看看官方文档对 change buffer 的介绍:

文档地址: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.

这里的意思是,缓存那些不在 buffer pool 中的二级索引页,并不是指非唯一的二级索引。那具体使用 change buffer 的条件是什么?其实具体使用条件主要汇集在以下五点:

  • 用户设置选项 innodb_change_buffering。

  • 在 mysql 的索引结构中,只有叶子结点才存储数据。因此有叶子节点才考虑是否使用 ibuf。

  • 如上面文档显示的一样,change buffer 只能缓存二级索引页,所以对于聚集索引,不可以缓存操作。聚簇索引页是由 Innodb 引擎将数据页加载到 Buffer Pool中(这个查找过程是顺序 I/O),然后进行数据记录插入或者更新、删除。

  • 因为唯一二级索引(unique key)的索引记录具有唯一性,因此无法缓存插入和更新操作,但可以缓存删除操作;

  • 表上没有 flush 操作,例如执行 flush table for export 时,不允许对表进行 ibuf 缓存 (通过 dict_table_t::quiesce 进行标识)

接下来我们结合源码和文档来看看具体操作。

源码地址:GitHub - mysql/mysql-server: MySQL Server, the world's most popular open source database, and MySQL

先看第一点设置选项尾 innodb_change_buffering,它能够针对三种类型的操作 INSERT、DELETE-MARK 、DELETE 进行缓存,三者对应 dml 语句关系如下:

  • INSERT 操作:插入二级索引。

  • 先进行 DELETE-MARK 操作,再进行INSERT操作:更新二级索引。

  • DELETE-MARK 操作:删除二级索引

    // 代码路径: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 */
    };

    此外 innodb_change_buffering 还可以通过设置其他选项来进行相应的缓存操作:

    • all:默认值,默认开启 buffer inserts、delete-marking operations、purges。

    • none:不开启 change buffer。

    • inserts:只是开启 buffer insert 操作。

    • deletes:只是开 delete-marking 操作。

    • changes:开启 buffer insert 操作和 delete-marking 操作。

    • purges:对只是在后台执行的物理删除操作开启 buffer 功能。

    第二点还需要大家进行判断条件即可,所以就不进行扩展讲解了,我们来细说一下第三点。

    第三点的具体参考函数为 ibuf_should_try,它满足 ibuf 缓存条件后,会使用两种模式去尝试获取数据页。

    这里说明一下,在 MySQL5.5 之前的版本中,由于只支持缓存 insert 操作,所以最初叫做 insert buffer,只是后来的版本中支持了更多的操作类型缓存,才改叫 change buffer,但是代码中与 change buffer 相关的 函数或变量还是以 ibuf 前缀开头。

    下面是函数的具体实现,地址在:`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);
      }

      上面加粗和标红的地方就是对唯一二级索引的判断的地方,意思是:

      • 当 ignore_sec_unique 这个变量为 0 时,如果修改的是唯一二级索引记录,就不能使用。

      • 当 ignore_sec_unique 这个变量为 1 时,如果修改的是唯一二级索引记录,还可以试着使用一下。

      ignore_sec_unique 的取值在:`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;
            }

        其中红框中的 btr_op 指的是本次修改的具体操作是什么,也就是:

        • 当此次具体的修改操作是 INSERT 操作时,ignore_sec_unique 为 0,也就是当修改的是唯一二级索引记录时,不可以使用 ibuf。

        • 当此次具体的修改操作不是 INSERT 操作时,ignore_sec_unique 为 1,也就是当修改的是唯一二级索引记录时,可以试着使用 ibuf。

        可以看到当 ibuf_should_try 函数返回 1 时,也就是可以试着用一下 ibuf,那么就把读取 buffer pool 中页面的模式改一下。

        最后总结一下,看完了本文相信大家都能比较明确的意识到,网上说只有非唯一索引才能使用 change buffer 的说法,毫无疑问是错的。只要满足了其它 4 个条件,对唯一索引进行的删除操作完全可以使用 change buffer 优化。