5.1 InnoDB存储引擎之索引与算法（B+树，B+树索引）

5.1 InnoDB存储引擎索引概述

InnoDB存储引擎支持以下几种常见的索引∶

1. B+树索引
2. 全文索引
3. 哈希索引

• InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
• B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和有效的索引。B+ 树索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。

注意 B+ 树中的B不是代表二叉（binary），而是代表平衡（balance），因为B+ 树是从最早的平衡二叉树演化而来，但是 B+ 树不是一个二叉树。

• 另个常常被 DBA忽视的问题是∶B+树索引并不能找到一个给定键值的具体行。B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

5.2 数据结构与算法

B+树索引是最为常见，也是在数据库中使用最为频繁的一种索引。

5.2.1 二分查找法

• 二分查找法（binary search）也称为折半查找法，用来查找一组有序的记录数组中的某一记录，其基本思想是∶将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。
• 每页 Page Directory中的槽是按照主键的顺序存放的，对于某条具体记录的查询是通过对 Page Directory 进行二分查找得到的。

5.2.2 二叉查找树和平衡二叉树

• 在介绍 B+树前，需要先了解一下二叉查找树。B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。
  

• 图5-2中的数字代表每个节点的键值，在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出，二叉查找树经过中序遍历后输出∶2、3、5、6、7、8。

• 对图5-2的这棵二叉树进行查找，如查键值为5的记录，先找到根，其键值是6，6大于5，因此查找6的左子树，找到3;而 5大于3，再找其右子树;一共找了3次。如果按 2、3、5、6、7、8的顺序来找同样需要3.次。用同样的方法再查找键值为8的这个记录，这次用了3次查找，而顺序查找需要6次。计算平均查找次数可得;顺序查找的平均查找次数为（1+2+3+4+5+6）/6=3.3次，二叉查找树的平均查找次数为（3+3+3+2+2+1）/6=2.3次。二叉查找树的平均查找速度比顺序查找来得更快。

• 二叉查找树可以任意地构造，同样是2、3、5、6、7、8这五个数字，也可以按照图5-3 的方式建立二叉查找树。
  

• 这样的二叉树效率就很低。如果需要最大性能的构造一棵二叉查找树，这时就需要一棵二叉平衡树——AVL树。

• 平衡二叉树的定义如下∶首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。显然，图5-3不满足平衡二叉树的定义，而图5-2是一棵平衡二叉树。平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需建立一棵平衡二叉树即可。

• 平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。因此对一棵平衡树的维护是有一定开销的，不过平衡二叉树多用于内存结构对象中，因此维护的开销相对较小。

5.3 B+树

• B+ 树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树由 B树和索引顺序访问方法演化而来（ISAM，是不是很熟悉?对，这也是MyISAM引擎最初参考的数据结构），但是在现实使用过程中几乎已经没有使用 B树的情况了。
• 精简地对 B+ 树做个介绍∶B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。先来看一个B+树，其高度为2，每页可存放4条记录，扇出（fan out）为5，如图5-6所示。
• 
• 从图5-6可以看出，所有记录都在叶子节点上，并且是顺序存放的，如果用户从最左边的叶子节点开始顺序遍历，可以得到所有键值的顺序排序∶5、10、15、20、25、30、50、55、60、65、75、80、85、90。

5.3.1 B+树的插入操作

B+树的插入必须保证插入后叶子节点中的记录依然排序，同时需要考虑插入到 B+树的三种情况，每种情况都可能会导致不同的插入算法。如表5-1所示。

• 因为图片显示的关系，这次没有能在各叶子节点加上双向链表指针。不过和图5-6和图5-7一样，它还是存在的。

• 可以看到，不管怎么变化，B+ 树总是会保持平衡。但是为了保持平衡对于新插入的键值可能需要做大量的拆分页（split）操作。因为 B+树结构主要用于磁盘，页的拆分意味着磁盘的操作，所以应该在可能的情况下尽量减少页的拆分操作。因此，B+ 树同样提供了类似于平衡二叉树的旋转（Rotation）功能。
• 旋转发生在 Leaf Page 已经满，但是其的左右兄弟节点没有满的情况下。这时 B+ 树并不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。在通常情况下，左兄弟会被首先检查用来做旋转操作，因此再来看图5-7 的情况，若插入键值 70，其实B+ 树并不会急于去拆分叶子节点，而是去做旋转操作，得到如图5-10 所示的操作。
  
• 旋转使B+树减少了一次拆分，这棵树的高度还是2。

5.3.2 B+ 树的删除操作

• B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶子节点中的记录依然排序，同插入一样，B+树的删除操作同样需要考虑以下表5-2中的三种情况，与插入不同的是，删除根据填充因子的变化来衡量。
  
• 根据图 5-9的 B+树来进行删除操作。首先删除键值为 70 的这条记录，该记录符合表5-2讨论的第一种情况，删除后可得到图 5-11。
  
• 接着删除键值为25的记录，这也是表5-2讨论的第一种情况，但是该值还是Index Page 中的值，因此在删除Leaf Page中的25后，还应将25的右兄弟节点的28更新到Page Index中，最后可得图5-12。
  
• 最后看删除键值为60的情况。删除Leaf Page中键值为60的记录后，Fill Factor小于 50%，这时需要做合并操作，同样，在删除Index Page中相关记录后需要做Index Page的合并操作，最后得到图 5-13。
  

5.4 B+树索引

• B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做100次 IO，2～4次的IO意味着查询时间只需0.02～0.04秒。
• 数据库中的 B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）（辅助索引有时候也被称为非聚集索引），但是不管是聚集还是辅助的索引，其内部都是B+ 树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

5.4.1 聚集索引

• InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵 B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
• 由于实际的数据页只能按照一棵 B+ 树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引|。因为聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
• 接着来看一张表，这里以人为的方式让其每个页只能存放两个行记录，如∶
  
  这棵聚集索引树的构造大致如5-14所示。

• 如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点∶ 一是前面说过的页通过双向链表链接，页按照主键的顺序排序;另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。
• 聚集索引的另一个好处是，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。如用户需要查询一张注册用户的表。查询最后注册的10位用户，由于B+ 树索引是双向链表的，用户可以快速找到最后一个数据页，并取出10条记录。若用命令EXPLAIN进行分析，可得∶
  
• 可以看到虽然使用ORDER BY 对记录进行排序，但是在实际过程中并没有进行所谓的 filesort操作，而这就是因为聚集索引的特点。
• 另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可，又如∶
  

5.4.2 辅助索引

• 对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉 InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此 InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。图5-15显示了InnoDB存储引擎中辅助索引与聚集索引的关系。
  
• 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。
• 对于其他的一些数据库，如 Microsoft SQL Server数据库，其有一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。这与 MySQL 数据库的 MyISAM存储引擎有些类似。堆表的特性决定了堆表上的索引都是非聚集的，主键与非主键的区别只是是否唯一且非空（NOT NULL）。因此这时书签是一个行标识符（Row Identifiedr，RID），可以用如"文件号∶页号∶槽号"的格式来定位实际的行数据。
• 有的 Microsoft SQL Server数据库 DBA 问过我这样的问题，为什么在 Microsoft SQL Server数据库上还要使用索引组织表?堆表的书签使非聚集查找可以比主键书签方式更快，并且非聚集可能在一张表中存在多个，我们需要对多个非聚集索引进行查找。而且对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢一些。
• 当然，在一些情况下，使用堆表的确会比索引组织表更快，但是我觉得大部分原因是由于存在OLAP（On-Line Analytical Processing，在线分析处理）的应用。其次就是前面提到的，表中数据是否需要更新，并且更新是否影响到物理地址的变更。此外另一个不能忽视的是对于排序和范围查找，索引组织表通过 B+ 树的中间节点就可以找到要查找的所有页，然后进行读取，而堆表的特性决定了这对其是不能实现的。最后，非聚集索引的离散读，的确存在上述的情况，但是一般的数据库都通过实现预读（read ahead）技术来避免多次的离散读操作。因此，具体是建堆表还是索引组织表，这取决于应用，不存在哪个更优的问题。这和 InnoDB 存储引擎好还是MyISAM存储引擎好这个问题的答案是一样的，It all depends。
  接着通过阅读表空间文件来分析 InnoDB存储引擎的非聚集索引的实际存储。还是分析上一小节所用的表t。不同的是，在表t上再建立一个列c，并对列c创建非聚集索引∶

5.4.3 B+树索引的分裂

• B+树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。例如下面的记录∶
  1、2、3、4、5、6、7、8、9
• 插入是根据自增顺序进行的，若这时插入10这条记录后需要进行页的分裂操作，那么根据5.3.1节介绍的分裂方法，会将记录5作为分裂点记录（split record），分裂后得到下面两个页∶
  P1: 1、2、3、4
  P2: 5、6、7、8、9、10
• 然而由于插入是顺序的，P1这个页中将不会再有记录被插入，从而导致空间的浪费。而P2又会再次进行分裂。
• nnoDB存储引擎的Page Header中有以下几个部分用来保存插入的顺序信息∶

1. PAGE_LAST_INSERT
2. PAGE_DIRECTION
3. PAGE_N_DIRECTION

• 通过这些信息，InnoDB 存储引擎可以决定是向左还是向右进行分裂，同时决定将分裂点记录为哪一个。若插入是随机的，则取页的中间记录作为分裂点的记录。若往同一方向进行插入的记录数量为5，并且目前已经定位（cursor）到的记录（InnoDB存储引擎插入时，首先需要进行定位，定位到的记录为待插入记录的前一条记录）之后还有3条记录，则分裂点的记录为定位到的记录后的第三条记录，否则分裂点记录就是待插入的记录。
  来看一个向右分裂的例子，并且定位到的记录之后还有3条记录，则分裂点记录如图 5-17所示。
  
• 对于图5-19的情况，分裂点就为插入记录本身，向右分裂后仅插入记录本身，这在自增插入时是普遍存在的一种情况。
  

5.4.4 B+树索引的管理

1.索引管理
索引的创建和删除可以通过两种方法，一种是 ALTER TABLE，另一种是 CREATE/DROP INDEX。通过 ALTER TABLE 创建索引的语法为∶

ALTER TABLE tbl_name
| ADD {INDEX| KEY }[ index_name]
[index_type](index_col_name,...)[index_option] ...
	
ALTER TABLE tbl_name DROP PRIMARY KEY
I DROP { INDEX| KEY} index_name

CREATE/DROP INDEX 的语法同样很简单∶

CREATE [UNIQUE] INDEX index_name 
[index_type]
ON tbl_name (index_col_name,...)

DROP INDEX index_name ON tbl_name

用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据，如前面创建的表t，列b为varchar（8000），但是用户可以只索引前100个字段，如∶

若用户想要查看表中索引的信息，可以使用命令 SHOW INDEX。下面的例子使用之前的表t，并加一个对于列（a，c）的联合索引 idx_a _c，可得∶

• 通过命令 SHOW INDEX FROM可以观察到表t上有4个索引，分别为主键索引、列上的辅助索引、b列的前100字节构成的辅助索引，以及（a、c）的联合辅助索引。接着具体阐述命令 SHOW INDEX展现结果中每列的含义。

1. Table∶索引所在的表名
2. Non_unique∶非唯一的索引，可以看到 primary key是0，因为必须是唯
3. Key_name∶索引的名字，用户可以通过这个名字来执行 DROP INDEX。
4. Seq_in_index∶索引中该列的位置，如果看联合索引idx_a_c就比较直观了。
5. Column_name∶索引列的名称。
6. Collation∶列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL 了。因为Hash根据 Hash桶存放索引数据，而不是对数据进行排序。
7. Cardinality∶非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
8. Sub_part∶是否是列的部分被索引。如果看idx b这个索引，这里显示100，表示只对b列的前100字符进行索引。如果索引整个列，则该字段为NULL。
9. Packed∶关键字如何被压缩。如果没有被压缩，则为NULL。
10. Null：是否索引的列含有NULL值。可以看到 idx b这里为Yes，因为定义的列b 允许NULL值。
11. Index_type∶索引的类型。InnoDB存储引擎只支持 B+树索引，所以这里显示的都是BTREE。
12. Comment∶注释。

Cardinality 值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。上面显示的结果主键的 Cardinality 为 2，但是很显然我们的表中有4条记录，这个值应该是4。如果需要更新索引 Cardinality 的信息，可以使用ANALYZE TABLE命令，如∶

不过，在每个系统上可能得到的结果不一样，因为ANALYZE TABLE 现在还存在一些问题，可能会影响最后得到的结果。另一个问题是MySQL 数据库对于Cardinality 计数的问题，在运行一段时间后，可能会看到下面的结果∶

• Cardinality为NULL，在某些情况下可能会发生索引建立了却没有用到的情况。或者对两条基本一样的语句执行 EXPLAIN，但是最终出来的结果不一样。一个使用索引，另外一个使用全表扫描。这时最好的解决办法就是做一次 ANALYZE TABLE 的操作。因此我建议在一个非高峰时间，对应用程序下的几张核心表做ANALYZE TABLE 操作，这能使优化器和索引更好地为你工作。

2. Fast Index Creation
MySQL 5.5版本之前（不包括5.5）存在的一个普遍被人诟病的问题是 MySQL数据库对于索引的添加或者删除的这类 DDL 操作，MySQL 数据库的操作过程为：

1. 首先创建一张新的临时表，表结构为通过命令 ALTER TABLE 新定义的结构。
2. 然后把原表中数据导入到临时表。
3. 接着删除原表。
4. 最后把临时表重名为原来的表名。

• 可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。
• InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为 Fast Index Creation（快速索引创建）的索引创建方式——简称 FIC。
• 对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB存储引擎只需更新内部视图，并将辅助索引的空间标记为可用，同时删除 MySOL数据库内部视图上对该表的索引定义即可。
• 这里需要特别注意的是，临时表的创建路径是通过参数 tmpdir进行设置的。用户必须保证 tmpdir有足够的空间可以存放临时表，否则会导致创建索引失败。
• 由于FIC在索引的创建的过程中对表加上了 S锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

3.Online Schema Change

• Online Schema Change（在线架构改变，简称 OSC）最早是由 Facebook实现的一种在线执行 DDL 的方式，并广泛地应用于Facebook的 MySOL数据库。所谓"在线"是指在事务的创建过程中，可以有读写事务对表进行操作，这提高了原有MySOL 数据库在DDL 操作时的并发性。
  Facebook采用 PHP 脚本来现实 OSC，而并不是通过修改 InnoDB存储引擎源码的方式。OSC最初由 Facebook的员工 Vamsi Ponnekanti开发。此外，OSC借鉴了开源社区之前的工具 The openarkit oolkit oak-online-alter-table。实现 OSC步骤如下∶

5. init，即初始化阶段，会对创建的表做一些验证工作，如检查表是否有主键，是否存在触发器或者外键等。
6. createCopyTable，创建和原始表结构一样的新表。
7. lterCopyTable∶对创建的新表进行 ALTER TABLE 操作，如添加索引或列等。createDeltasTable，创建 deltas表，该表的作用是为下一步创建的触发器所使用。之后对原表的所有 DML 操作会被记录到 createDeltasTable 中。
8. createTriggers，对原表创建 INSERT、UPDATE、DELETE操作的触发器。触发操作产生的记录被写入到 deltas 表。startSnpshotXact，开始 OSC操作的事务。
9. selectTableIntoOutfile，将原表中的数据写入到新表。为了减少对原表的锁定时间，这里通过分片（chunked）将数据输出到多个外部文件，然后将外部文件的数据导入到 copy 表中。分片的大小可以指定，默认值是500 000。dropNCIndexs，在导入到新表前，删除新表中所有的辅助索引。loadCopyTable，将导出的分片文件导入到新表。
10. replayChanges，将 OSC过程中原表 DML 操作的记录应用到新表中，这些记录被保存在 deltas 表中。
11. recreateNCIndexes，重新创建辅助索引。
12. replayChanges，再次进行 DML 日志的回放操作，这些日志是在上述创建辅助索引中过程中新产生的日志。
13. swapTables，将原表和新表交换名字，整个操作需要锁定2张表，不允许新的数据产生。改名很快，所以阻塞时间很短。

4. Online DDL
虽然FIC可以让InnoDB存储引擎避免创建临时表，从而提高索引创建的效率。但正如前面小节所说的，索引创建时会阻塞表上的 DML 操作。OSC 虽然解决了上述的部分问题，但是还是有很大的局限性。MySQL 5.6 版本开始支持 Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如 INSERT、UPDATE 这类 DML 操作，这极大地提高了MySQL 数据库在生产环境中的可用性。
此外，不仅是辅助索引，以下这几类 DDL操作都可以通过"在线"的方式进行操作∶

1. 辅助索引的创建与删除
2. 改变自增长值
3. 添加或删除外键约束
4. 列的重命名
   通过新的 ALTER TABLE 语法，用户可以选择索引的创建方式∶

ALTER TABLE tbl name
I ADD (INDEX| KEY)[index_name]
[index_type](index_col_name,...)[index_option]...
ALGORITHM [=](DEFAULTI INPLACEICOPY}
LOCK [=](DEFAULT| NONE| SHARED| EXCLUSIVE)

• ALGORITHM指定了创建或删除索引的算法，COPY 表示按照 MySQL5.1版本之前的工作模式，即创建临时表的方式。INPLACE 表示索引创建或删除操作不需要创建临时表。DEFAULT表示根据参数 old_alter_table来判断是通过INPLACE还是COPY的算法，该参数的默认值为 OFF，表示采用INPLACE的方式，如∶

LOCK 部分为索引创建或删除时对表添加锁的情况，可有的选择为∶
(1)NONE
执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度。
(2)SHARE
这和之前的 FIC类似，执行索引创建或删除操作时，对目标表加上一个S锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存作支持 SHARE 模式，会返回一个错误信息。
(3)EXCLUSIVE
在 EXCLUSIVE 模式下，执行索引创建或删除操作时，对目标表加上一个X锁。读写事务都不能进行，因此会阻塞所有的线程，这和 COPY方式运行得到的状态类似，但是不需要像COPY 方式那样创建一张临时表。
(4)DEFAULT
DEFAULT模式首先会判断当前操作是否可以使用NONE 模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE 模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行 DDL 的模式。

• InnoDB存储引擎实现 Online DDL 的原理是在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认的大小为128MB。若用户更新的表比较大，并且在创建过程中伴有大量的写事务，如遇到innodb_online_alter_log_max_size 的空间不能存放日志时，会抛出类似如下的错误;
  
• 对于这个错误，用户可以调大参数 innodb_online_alter_log_max_size，以此获得更大的日志缓存空间。此外，还可以设置 ALTER TABLE的模式为 SHARE，这样在执行过程中不会有写事务发生，因此不需要进行DML日志的记录。
• 需要特别注意的是，由于Online DDL 在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SOL 优化器不会选择正在创建中的索引。

5.5 Cardinality值

5.5.1 什么是Cardinality

• 并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加 B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。
• 地区字段、类型字段，它们可取值的范围很小，称为低选择性。如∶

SELECT * FROM student WHERE sex='M'

• 按性别进行查询时，可取值的范围一般只有’M’、‘F’。因此上述 SQL 语句得到的结果可能是该表 50%的数据（假设男女比例1∶1），这时添加 B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最适合的。例如，对于姓名字段，基本上在一个应用中不允许重名的出现。
• 怎样查看索引是否是高选择性的呢?可以通过 SHOW INDEX结果中的列 Cardinality 来观察。Cardinality 值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality 是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table 应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加 B+ 树索引是非常有必要的。如∶

SELECT * FROM member WHERE usernick = 'David'

表member 大约有 500万行数据。usernick字段上有一个唯一的索引。这时如果查找用户名为 David的用户，将会得到如下的执行计划∶

5.5.2 InnoDB存储引擎的 Cardinality 统计

• 上一小节介绍了Cardinality 的重要性，并且告诉读者Cardinality 表示选择性。建立索引的前提是列中的数据是高选择性的，这对数据库来说才具有实际意义。然而数据库是怎样来统计Cardinality 信息的呢?因为 MySQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+ 树索引的实现又各不相同，所以对 Cardinality 的统计是放在存储引擎层进行的。
• 此外需要考虑到的是，在生产环境中，索引的更新操作可能是非常频繁的。如果每次索引在发生操作时就对其进行 Cardinality 的统计，那么将会给数据库带来很大的负担。另外需要考虑的是，如果一张表的数据非常大，如一张表有50G 的数据，那么统计一次 Cardinality 信息所需要的时间可能非常长。这在生产环境下，也是不能接受的。因此，数据库对于Cardinality 的统计都是通过**采样（Sample）**的方法来完成的。
• 在 InnoDB存储引擎中，Cardinality 统计信息的更新发生在两个操作中∶INSERT 和UPDATE。根据前面的叙述，不可能在每次发生INSERT和 UPDATE时就去更新Cardinality 信息，这样会增加数据库系统的负荷，同时对于大表的统计，时间上也不允许数据库这样去操作。因此，InnoDB存储引擎内部对更新 Cardinality 信息的策略为∶

1. 表中1/16的数据已发生过变化。
2. stat_modified_counter>2 000 000 000。

• 第一种策略为自从上次统计Cardinality信息后，表中1/16的数据已经发生过变化，这时需要更新 Cardinality 信息。
• 第二种情况考虑的是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这这种情况。故在 InnoDB存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数，当 stat_modified_counter大于2 000 000 000时，则同样需要更新 Cardinality 信息。
• 接着考虑 InnoDB存储引擎内部是怎样来进行 Cardinality 信息的统计和更新操作的呢?同样是通过采样的方法。默认InnoDB存储引擎对8个叶子节点（Leaf Page）进行采用。采样的过程如下∶

1. 取得 B+树索引中叶子节点的数量，记为 A。
2. 随机取得 B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2…P8
3. 根据采样信息给出 Cardinality 的预估值∶Cardinality= (P1+P2+·+P8)*A/8。

• 通过上述的说明可以发现，在InnoDB存储引擎中，Cardinality值是通过对8个叶子节点预估而得的，不是一个实际精确的值。再者，每次对Cardinality 值的统计，都是通过随机取8个叶子节点得到的，这同时又暗示了另一个 Cardinality 现象，即每次得到的Cardinality 值可能是不同的。
• 当然，有一种情况可能使得用户每次观察到的索引 Cardinality值都是一样的，那就是表足够小，表的叶子节点数小于或者等于8个。这时即使随机采样，也总是会采取到这些页，因此每次得到的 Cardinality 值是相同的。
• 在 InnoDB 1.2版本之前，可以通过参数innodb_stats_sample_pages用来设置统计Cardinality 时每次采样页的数量，默认值为8。同时，参数 innodb_stats_sample_pages用来判断如何对待索引中出现的 NULL值记录。该参数默认值为 nulls_equal，表示将 NULL值记录视为相等的记录。其有效值还有nulls_unequal，nulls_ignored，分别表示将NULL 值记录视为不同的记录和忽略 NULL 值记录。例如某页中索引记录为NULL、NULL、1、2、2、3、3、3，在参数 innodb_stats_method的默认设置下，该页的Cardinality为4;若参数 innodb_stats_method为 nulls unequal，则该页的 Caridinality为5;若参数innodb_stats_sample_pages为 nulls_ignored，则 Cardinality为 3。
• 
• 当 执 行SQL语 句 ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX 以及 访问INFORMATION_SCHEMA架构下的表TABLES 和 STATISTICS时会导致InnoDB存储引擎去重新计算索引的 Cardinality 值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新Cardinality 值。
• InnoDB1.2 版本提供了更多的参数对 Cardinality 统计进行设置，这些参数如表 5-3 所示。