InnoDB内存架构之更改缓冲区

InnoDB内存架构之更改缓冲区

文章目录

• 1 问题背景
• 2 前言
• 3 回顾
• 4 更改缓冲区
• 5 更改缓冲区的配置

1 问题背景

前面研究了InnoDB内存架构之Buffer Pool缓冲池，今天来研究更改缓冲区。

参考自：MySQL官方文档的InnoDB之更改缓冲区

2 前言

在本篇博客中，“更改缓冲区” 在绝大部分的情况下是一个名词，其由英文Change Buffer翻译过来。更改 在绝大部分情况下也是一个名词。

3 回顾

InnoDB的架构如下图所示：

4 更改缓冲区

原文
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 buffered changes, which may result from INSERT, UPDATe, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

更改缓冲区是一个特殊的数据结构，当二级索引页不在缓冲池中时，它会将更改缓存到二级索引页中。由INSERT、UPDATE、DELETE等DML *** 作导致的缓冲更改，稍后会在其他读取 *** 作将页面加载到缓冲池中时合并。

二级索引的介绍

原文
secondary index
A type of InnoDB index that represents a subset of table columns. An InnoDB table can have zero, one, or many secondary indexes. (Contrast with the clustered index, which is required for each InnoDB table, and stores the data for all the table columns.)

A secondary index can be used to satisfy queries that only require values from the indexed columns. For more complex queries, it can be used to identify the relevant rows in the table, which are then retrieved through lookups using the clustered index.

Creating and dropping secondary indexes has traditionally involved significant overhead from copying all the data in the InnoDB table. The fast index creation feature makes both CREATE INDEX and DROp INDEX statements much faster for InnoDB secondary indexes.

See Also clustered index, Fast Index Creation, index.

二级索引是InnoDB索引中的一种，它是数据库表的列子集。一个InnoDB表可以有0个、1个或者多个二级索引。与聚集索引做对比，聚集索引是每个表都需要的，并且聚集索引存储表的所有列表。（总结得出，二级索引只存索引的值，聚集索引能存表所有列的值）
一个二级索引通常只能满足某些简单查询——只查询索引列的值。如果涉及更复杂的查询，通常通过二级索引标识相关的行，然后通过聚集索引查找出来。（例子：比如查询的列不在二级索引中，那么只能通过二级索引找到相关的行，然后再通过聚集索引查询出要查询的列值。）

更改缓冲区的结构如下：

原文
Unlike clustered indexes, secondary indexes are usually nonunique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates may affect secondary index pages that are not adjacently located in an index tree. Merging cached changes at a later time, when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read secondary index pages into the buffer pool from disk.

不像聚集索引，二级索引通常不是唯一的，并且以相对随机的顺序插入二级索引。类似地，删除和更新 *** 作可能会影响索引树中不相邻的二级索引页。稍后合并缓存的更改，被影响的页通过其他 *** 作被读进缓冲池，可以避免大量的随机IO访问（如果没有缓冲区，这将需要从磁盘读取二级索引页到缓冲池中）

总结：更改缓冲区的作用，笔者理解是避免直接从磁盘读取二级索引到缓冲池，这将会导致大量的随机IO访问 （为什么是大量的？为什么是随机的？为什么是IO？答：针对第一个问题，因为二级索引不是唯一的；针对第二个问题，因为二级索引是以随机的顺序插入的；针对第三个问题，因为二级索引存在磁盘上）。 将二级索引读进更改缓冲区，稍后合并缓存的更改，此时不需从磁盘读二级索引，而是从更改缓冲区直接读取到缓冲池，减少大量的随机IO访问。

原文
Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

当系统大部分空闲的时候、缓慢关闭期间运行的清除 *** 作，会定期地将更新的索引页写入到磁盘上。与将每个值立即写入磁盘相比，清除 *** 作可以更有效地为一系列索引值写入到磁盘。

原文
Change buffer merging may take several hours when there are many affected rows and numerous secondary indexes to update. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed, and even after a server shutdown and restart (see Section 15.21.3, “Forcing InnoDB Recovery” for more information).

当有很多行以及众多的二级索引要更新时，更改缓冲区的合并可能需要几个小时。在这段时间内，磁盘IO增加，这将导致一个磁盘绑定查询的显著缓慢。在一个事务被提交后，甚至一个服务器关闭并且重启服务器之后，更改缓冲区的合并可能也会继续发生。详情看强制InnoDB恢复

原文
In memory, the change buffer occupies part of the buffer pool. On disk, the change buffer is part of the system tablespace, where index changes are buffered when the database server is shut down.

在内存中，更改缓冲区占用缓冲池的一部分。在磁盘上，更改缓冲区是系统表空间的一部分，当数据库服务器关闭后，改变的索引会被缓冲在那里。

原文
Change buffering is not supported for a secondary index if the index contains a descending index column or if the primary key includes a descending index column.

如果所索引包含降序索引列或者主键包含降序索引列，则二级索引不支持更改缓冲。

5 更改缓冲区的配置

有关的配置可详情见InnoDB的更改缓冲区