MySQL的索引

MySQL 的索引

1 索引的使用

1.1 什么是索引

  索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的 ROWID（相当于页码）快速找到表中对应的记录。索引的建立是表中比较有指向性的字段，相当于目录，比如说行政区域代码，同一个地域的行政区域代码都是相同的，那么给这一列加上索引，避免让它重复扫描，从而达到优化的目的。

1.2 如何创建索引

  在执行 CREATE TABLE 语句时可以创建索引，也可以单独用 CREATE INDEX 或 ALTER TABLE 来为表增加索引。

1.2.1 ALTER TABLE

  ALTER TABLE用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

ALTERTABLE table_name ADDINDEX index_name (column_list)ALTERTABLE table_name ADDUNIQUE(column_list)ALTERTABLE table_name ADDPRIMARYKEY(column_list)

  其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名 index_name 可选，缺省时，MySQL 将根据第一个索引列赋一个名称。另外，ALTER TABLE 允许在单个语句中更改多个表，因此可以在同时创建多个索引。

1.2.2 CREATE INDEX

  CREATE INDEX 可对表增加普通索引或 UNIQUE 索引。

CREATEINDEX index_name ON table_name (column_list)CREATEUNIQUEINDEX index_name ON table_name (column_list)

  table_name 、index_name 和 column_list 具有与 ALTER TABLE 语句中相同的含义，索引名不可选。另外，不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

1.2.3 CREATE TABLE

createtable T(
    id intprimarykey, 
    k intnotnull, 
    name varchar(16),index(k))engine=InnoDB;

1.2.4 创建索引的注意事项

  只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

1.3 索引类型

  在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为 PRIMARY KEY 或 UNIQUE 索引。对于单列唯一性索引，这保证单列不包含重复的值。对于多列唯一性索引，保证多个值的组合不重复。
  PRIMARY KEY 索引和 UNIQUE 索引非常类似。事实上，PRIMARY KEY 索引仅是一个具有名称 PRIMARY 的 UNIQUE 索引。这表示一个表只能包含一个 PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

1.4 删除索引

  可利用 ALTER TABLE 或 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句，DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理，语法如下。

DROPINDEX index_name ON talbe_name;ALTERTABLE table_name DROPINDEX index_name;ALTERTABLE table_name DROPPRIMARYKEY;

  其中，前两条语句是等价的，删除掉 table_name 中的索引 index_name。第3条语句只在删除 PRIMARY KEY 索引时使用，因为一个表只可能有一个 PRIMARY KEY 索引，因此不需要指定索引名。如果没有创建 PRIMARY KEY 索引，但表具有一个或多个 UNIQUE 索引，则 MySQL 将删除第一个 UNIQUE 索引。
  如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

1.5 查看索引

showindexfrom table_name;

1.6 如何选择合适的列建立索引

1. 在 where 从句，group by 从句，order by 从句，on 从句中虚线的列添加索引。
2. 索引字段越小越好（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO 也会越大）。
3. 查询中与其它表关联的字段需要添加索引。
4. 对一些经常处理的业务表应在查询允许的情况下尽量减少索引。
5. 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

2 InnoDB 的索引模型

  在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
  每一个索引在 InnoDB 里面对应一棵 B+ 树。
  假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引：如下

createtable T(
    id intprimarykey, 
    k intnotnull, 
    name varchar(16),index(k))engine=InnoDB;insertinto T (id,k)values(100,1);insertinto T (id,k)values(200,2);insertinto T (id,k)values(300,3);insertinto T (id,k)values(500,5);insertinto T (id,k)values(600,6);

  从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。

2.1 基于主键索引和普通索引的查询有什么区别。

  如上图，如果要查询 id = 500 的数据，即主键查询方式，则只需要搜索 id 这棵 B+ 树。但是如果要查询 k = 5 的数据，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
  也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

3 索引维护

  B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值 为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值 为400，就相对麻烦了，需要逻辑上挪动后面的数据空出位置。而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

3.1 为什么要使用自增主键？

  自增主键是指自增列上定义的主键，插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
  除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
  由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
  所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

3.2 业务主键在什么情况下可以使用？

  当业务所在的场景为 KV 场景时，也就是整个表只有一个索引，并且是唯一索引。这时候我们就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

3.3 索引的优化

  增加索引会有利于查询效率，但会降低 insert，update，delete 的效率，但实际上往往不是这样的，过多的索引会不但会影响使用效率，同时会影响查询效率，这是由于数据库进行查询分析时，首先要选择使用哪一个索引进行查询，如果索引过多，分析过程就会越慢，这样同样的减少查询的效率，因此我们要知道如何增加，有时候要知道维护和删除不需要的索引。

3.3.1 重复和冗余的索引

重复索引：重复索引是指相同的列以相同的顺序建立的同类型的索引。
冗余索引：冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。因为普通索引的叶子节点存储的是主键索引，如果建立的联合索引，又人为的把主键包含进去，那么这个时候就是一个冗余索引。

3.3.2 索引的维护删除

  由于业务变更，某些索引是后续不需要使用的，就要进行删除。在 MySQL 中，目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析。具体方式请查看这篇文章。

4 覆盖索引

  先看如下示例：

createtable T (
    ID intprimarykey,
    k intNOTNULLDEFAULT0, 
    s varchar(16)NOTNULLDEFAULT'',index k(k))engine=InnoDB;insertinto T (id,k,s)values(100,1,'a');insertinto T (id,k,s)values(200,2,'b');insertinto T (id,k,s)values(300,3,'c');insertinto T (id,k,s)values(500,5,'d');insertinto T (id,k,s)values(600,6,'e');

  在上面这个表中如果要查询

SELECT * FROM T WHERE k BETWEEN 3 AND 5;

这个 SQL 语句的话，索引树和上面的相同。SQL 的执行流程是怎么样的？
  1. 在 k 索引树上找到 k = 3 的记录，取得 ID = 300。
  2. 再到 ID 索引树查到 ID = 300 对应的 R3。
  3. 在 k 索引树取下一个值 k = 5，取得 ID = 500。
  4. 再回到 ID 索引树查到 ID = 500 对应的 R4。
  5. 在 k 索引树取下一个值 k = 6，不满足条件，循环结束。
  在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤1、3 和 5），回表了两次（步骤 2 和 4）。在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
  如果执行的语句是

SELECT ID FROM T WHERE K BETWEEN 3 AND 5;

，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
  需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

5 最左前缀原则

  为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。如上图，可以看到，索引项是按照索引定义里面出现的字段顺序排序的。当你的逻辑需求是查到所有名是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
  如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是" where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

5.1 在建立联合索引的时候，如何安排索引内的字段顺序？

  这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  那么，如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了。比如上面这个示例，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

5.2 前缀索引的影响

5.2.1 前缀索引对扫描行的影响

  先看如下示例：

createtable SUser(
    ID bigintunsignedprimarykey,
    email varchar(64),...)engine=innodb;-- 有如上的一张表，如果要根据email进行查询。MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。altertable SUser addindex index1(email);-- 如果指定索引长度，那么索引就不会包含整个字符串。altertable SUser addindex index2(email(6));

  第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串。而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。那么，这两种不同的定义在数据结构和存储上有什么区别呢？

  如上图，由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。但这同时带来的损失是，可能会增加额外的记录扫描次数。
  如果有如下

SELECT id,name,email FROM SUser WHERE email='[email protected]';

SQL，这个 SQL 在这两个索引定义下分别是怎么执行的？
index1：
    1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxxx.com’的这条记录，取得 ID2 的值；
    2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
    3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足email=‘zhangssxyz@xxxx.com’的条件了，循环结束。
    这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
index2：
    1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
    2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
    3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
    4. 重复上一步，直到在 index2 上取到的值不是’zhangs’时，循环结束。
    在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。
  通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。
  也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

5.2.1.1 给字符串创建前缀索引时，怎么能够确定使用多长的前缀呢？

  实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

-- 1. 首先，你可以使用下面这个语句，算出这个列上有多少个不同的值。selectcount(distinct email)as L from SUser;-- 2. 然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：selectcount(distinctleft(email,4)）as L4,count(distinctleft(email,5)）as L5,count(distinctleft(email,6)）as L6,count(distinctleft(email,7)）as L7,from SUser;

  使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4 ~ L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

5.2.1.2 其他方式

  如果前缀字符串的区分度很低，可以使用如下的方式进行索引的创建。

5.2.1.2.1 倒序存储

  比如要创建索引的是身份证号，身份证号前 6 个是地址码，如果要创建区分度高的索引，可能需要创建 12 个字符以上的索引，但是如果存储的时候把身份证号倒过来存储，每次查询的时候通过 reverse 函数转换一下。由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。

5.2.1.2.2 Hash字段

  你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。这样，索引的长度变成了4个字节，比原来小了很多。

-- 增加一个字段并在这个字段上添加索引。altertable t add id_card_crc intunsigned,addindex(id_card_crc);-- 使用新创建的字段进行索引查询并且再加上精确查询。select field_list from t where id_card_crc=crc32('input_id_card_string')and id_card='input_id_card_string';

5.2.1.2.3 使用倒序存储和使用 Hash 字段这两种方法的异同点

  首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样地，Hash 字段的方式也只能支持等值查询。
  它们的区别，主要体现在以下三个方面：
  1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 Hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 Hash 字段也差不多抵消了。
  2. 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 Hash字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
  3. 从查询效率上看，使用 Hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

5.2.2 前缀索引对覆盖索引的影响

  先看看如下的

SELECT id,email FROM SUser WHERE email='[email protected]';

，这个 SQL 如果使用 index1 的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2，就不得不回到 ID 索引再去判断 email 字段的值。即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

6 索引下推

CREATETABLE t_user (
    id int(11)NOTNULL,
    id_card varchar(32)DEFAULTNULL,
    name varchar(32)DEFAULTNULL,
    age int(11)DEFAULTNULL,
    ismale tinyint(1)DEFAULTNULL,PRIMARYKEY(id),KEY id_card_index (id_card),KEY name_age_index (name,age))ENGINE=InnoDB

  如上面的表，如果要查询

 SELECT * FROM t_user WHERE name like '张%' AND age=10 AND ismale=1;

这个 SQL 的加话，根据上面提到的最左前缀原则，它会使用name_age_index的索引信息进行查询，那是否是查询出所有的张姓姓名之后在主键索引上进行条件筛查呢？
  在 MySQL5.6 之前，只能是把性别为张的所有数据查询出来，到主键索引上找出数据行，再对比字段值。
  而 MySQL 5.6 引入的索引下推优化， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
  如果在这个表中有如下信息

INSERTINTO t_user (id,id_card,name,age,ismale)values(1,101,'张三',10,1);INSERTINTO t_user (id,id_card,name,age,ismale)values(2,201,'张六',30,1);INSERTINTO t_user (id,id_card,name,age,ismale)values(3,301,'张四',10,2);INSERTINTO t_user (id,id_card,name,age,ismale)values(4,401,'张五',10,1);

  那么根据分析会有如下情况：

  在上面两个图里面，每一条虚线箭头表示回表一次。
  在 MySQL5.6 之前，在(name,age)索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把" name 第一个字是’张’"的记录一条条取出来回表。因此，需要回表 4 次。
  在 MySQL5.6 开始，InnoDB 在(name,age)索引内部就判断了 age 是否等于 10，对于不等于 10 的
记录，直接判断并跳过。在我们的这个例子中，只需要对R1、R3、R4这三条记录回表取数据判断，就只需要回表 3 次。

7 普通索引和唯一索引在业务中应该如何选择？

7.1 示例

-- 假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语句：select name from CUser where id_card ='xxxxxxxyyyyyyzzzzz';-- 所以，你一定会考虑在id_card字段上建索引。-- 由于身份证号字段比较大，不建议把身份证号当做主键，那么现在你有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。那么应该怎么选择呢？

  接下来，我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。

7.2 查询过程

  1. 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足 k = 5 条件的记录。
  2. 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
  那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。
  InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
  因为引擎是按页读写的，所以说，当找到 k = 5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。当然，如果 k = 5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。但是，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

7.3 更新过程

7.3.1 change buffer

  当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
  需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。
  将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭的过程中，也会执行 merge 操作。
  显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

7.3.2 什么条件下可以使用 change buffer 呢？

  对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在 k = 4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
  change buffer 用的是 buffer pool 里的内存，因此不能无限增大。 change buffer 的大小，可以通过参数innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

7.3.2 更新 InnoDB 的处理流程

  更新的目标页在内存中:
  1. 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
  2. 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。
  这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。
  要更新的目标页不在内存中:
  1. 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
  2. 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。
  将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

7.3.3 change buffer 的使用场景

  因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
  因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
  反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。这个时候应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。
  在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似’历史数据’的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

7.3.4 change buffer 和 redo log的区别

  redo log相关的内容请查看这篇文章。
  假设要执行下面这个 SQL。

insertinto t(id,k)values(id1,k1),(id2,k2);

  我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存中，k2 所在的数据页不在内存中。如下图所示是带change buffer的更新状态图。

  分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib
_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。这条更新语句做了如下的操作（按照图中的数字顺序）：
  1. Page 1 在内存中，直接更新内存；
  2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息。
  3. 将上述两个动作记入redo log中（图中3和4）。
  做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。
  下面是在发生更新语句不久之后的读请求：

  1. 读 Page 1 的时候，直接从内存返回。
  2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。
  所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。