MySql技术内幕 - 索引

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

• B + Tree索引
• 全文索引
• 哈希索引

前面已经提到过，就是MySql 的哈希索引是自适应的，我们不能人为的指定一张表是哈希索引。

B+Tree索引其实大家都会有一个误区，就是其实他并不能直接定位到一行记录。我们通过上一节聊到的MySql块存储就能知道，在每个索引块上存在很多条记录

而B+tree索引只能找到对应的存储块，然后会将其加载到内存中，而数据在一个块中是有序的，这时再通过二分查找就能够确定我们要找到记录了。

因为MySql中用的最多的就是B+Tree，所以这里简单提一下平衡二叉树，毕竟B+Tree是通过二叉树，再到平衡二叉树演化而来的

什么是平衡二叉树（AVL树）：满足任何节点的两个子树的最大高度查为1

 

一、B+Tree

　　B+Tree是为磁盘或者其他直接存储辅助设备设计的一种平衡查找树，在B+Tree中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由叶子节点的指针进行链接。

插入操作：

　　插入新值之后也要保证树的平衡，所以对于Leaf Page和 Index Page是否满了，会进行不同的页拆分，整体上来说就是先将新值插入到叶子节点中，如果叶子节点满了，就将中间的值存储到上层的Index Page中，如果Index Page也满了，那么继续向上分裂即可。在将中将值上升到Index Page 节点之后，剩下的小于中间值的被分裂到左边的Leaf Page中，大于等于中间值的被分裂到右边的Leaf Page中。

　　当然这属于也的拆分，因为B+Tree结构主要用于磁盘，所以拆分页就意味着磁盘操作，所以我们尽量要避免拆分页的操作，所以B+Tree在拆分页之外也提供了类似于平衡二叉树的旋转。

旋转发生在Leaf Page已满的情况下，但是其左右兄弟节点没有满的情况下，这时B+Tree并不急于去做拆分页的操作，而是将记录移动到所在页的兄弟节点上，在通常情况下，左兄弟会被首先检查用来做旋转操作。

 

删除操作：

　　B+ Tree使用填充因子来控制树的删除变化，50%是填充因子可设置的最小值，B+Tree的删除操作同样必须保证删除之后的叶子节点中的记录依然是有序的

删除操作会判断删除了对应值之后，节点是否小于填充因子，如果是小于，那么对于Index Page则会合并下层节点。如果删除Index Page节点中的一个值，那么会将其兄弟节点更新到Index Page节点中。

二、B+Tree索引

　　在MySql中的B+Tree索引一般都是2～4层，也就是说查找一行键值记录时最多只需要2～4次IO

　　数据库中的B+Tree索引可以氛围聚集索引（clustered index）和辅助索引（secondary index）,但是不管是聚集索引还是辅助索引，其内部都是B+Tree的，叶子节点存放着所有的数据。

唯一不同的是，叶子节点存放的是否是一整行的信息。

聚集索引：

　　InnoDB存储引擎表是索引组织表，即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+Tree，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分，同B+Tree结构一样，每个数据页都通过一个双向链表进行链接。

　　由于实际的数据页只能按照一颗B+Tree进行排序，因此每张表只能拥有一个聚集索引。在大多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+Tree的叶子节点上直接找到数据。因此，由于定义了数据的逻辑顺序，聚集索引能够特别快的访问范围内的查询。

　　聚集索引的存储不是物理连续的，而是逻辑连续的。这其中有两点，一是叶子节点之间通过双向链表链接，页按照主键顺序排序，另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

　　聚集索引的另一个好处是，他对于主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。

辅助索引（非聚集索引）：

 　　对于辅助索引，叶子节点并不包含行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签，该书签用来告诉InnoDB存储引擎在哪里可以找到与索引相对应的行数据，由于InnoDB是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相对应行数据的聚集索引键。

　　辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向组件索引的主键，然后再通过主键索引来找到一个完整的行记录。

三、索引的管理

　　我们可以通过show index from tablename 的方式来查看一张表的索引，在展示出的索引的相信信息中有一个值非常的关键，就是Cardinality,这个值表示索引中唯一值的数目的估值，所以简单的说这个值越接近表中的行记录数，这个索引的效果就越好，如果非常低那就要考虑是不是要删除掉这个索引了，但是同时这个值却不是实时更新的，因为开销代价特别大，这时我们可以通过ANYLYZE TABLE 或者是alter table set engine = innodb 的方式来强制重新统计这个信息。

　　

Fast Index Creation(FIC):

　　在MySql 5.5之前对数据库进行索引的添加或者是删除这类DDL操作，MySql的操作过程是：

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

　　可以发现如果通过这样的方式，那么如果是一张很大的表，那么就需要很长的时间，如果这个时候有用户需要对这个表进行操作，那么就会被阻塞。

　　所以InnoDB引擎从1.0.x版本开始支持一种FIC的索引创建方式。

　　对于辅助索引的创建，InnoDB会先在原表上添加一个S锁，在创建的过程中，不需要重建表，因此速度比之前快了很多，并且数据库的可用性也提高了很多。

　　删除辅助索引的操作就更加简单了，只需要更新内部的视图，并将辅助索引的空间标记为可用，同时删除MySql 数据库内部视图上对该表的索引定义即可。

　　由于在FIC的过程中该添加了S锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标进行写操作，那么数据库依然是不可用的。

　　此外FIC方式只能限定于辅助索引，对于主键的创建和删除同样需要创建一张临时表。

Online DDL :

　　FIC 虽然不再需要创建临时表，可以提高一些索引创建的效率，但是对于DML的操作依然是受限的。所以MySql 5.6版本开始支持Online DDL的操作。

　　其允许辅助索引创建的同时，还允许其他DML的操作，除了DML操作，下面几种操作也支持Online的方式：

1. 辅助索引的创建与删除
2. 改变自增长值
3. 添加或删除外键约束
4. 列的重命名

　　创建的语法为：

　　alter table tablename | add {index|key} [index_name] [index_type] (index_col,...) [index_option] ...

　　algorithm [=] {default | inplace | copy}

　　lock [=] {default | none | shared | exclusive}

　　其中algorithm指定了创建或者删除索引的算法，copy表示按照MySql 5.1版本之前的工作模式，即创建临时表的方式。inplace表示索引创建或删除操作不需要创建临时表

　　default表示根据参数old_alter_table来判断是通过inplace还是copy。该参数默认是Off，表示会按照inplace的方式。

　　lock部分为索引创建或者删除时对表添加锁的情况，可选择为：

　　　　（1）NONE

　　　　　　执行索引创建或删除操作时，对目标表不添加任何锁，即事务仍然可以进行读写操作，不会阻塞。

　　　　（2）SHARE

　　　　　　这个和之前FIC比较类似，执行索引的创建或删除时，对目标表添加上一个S锁，对并发地读事务，依然可以正常的进行，但是遇到写事务就会被阻塞等待

　　　　（3）EXCLUSIVE

　　　　　　执行索引创建或删除操作时，对目标表添加上一个X锁，读写事务都u不能进行，因此会阻塞住全部的线程，这种模式与copy类似，但是不会创建一张新的临时表

　　　　（4）default

　　　　　　该模式首先会判断当前操作的表是否处于可以使用NONE模式，若不能则判断是否可以使用SHARE模式，最后判断是否可以使用exclusive模式，也就是说default会通过

　　　　　　判断事务的最大并发性来选择执行DDL的模式

　　InnoDB存储引擎实现Online DDL的原理是在执行创建或删除索引操作的同时，将Insert,Update,delete这类DML操作的日志写入到一个缓存中，待完成索引的创建后再将重做应用到表上

以此达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认是128M，所以如果目标表比较大，而且在创建过程中写事务又比较多，可以通过调整该参数的大小，或者使用share的模式，来减少写事务的操作。

　　需要特别注意的是，由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建的过程中，SQL优化器不会选择正在创建的索引。

四、Cardinality 值

　　因为MySql数据库中有各种不同的存储引擎，而每种存储引擎对B+Tree索引的实现又各不相同，所以对Cardinality的统计是放在存储引擎层进行的。

这里如果表中的数据量非常大，或者是更新操作非常的频繁，那么要想完全统计一次Cardinality是非常耗时的，所以一般都是通过采样的方式来完成的。

在InnoDB存储引擎中，Cardinality的统计信息更新发生在两个操作中：Insert和Update：

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

第一种策略为自从上次统计Cardinality信息后，表中1/16的数据已经发生过很大的变化，这时需要更新Cardinality信息

第二种情况是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生的变化还是这一行数据，则第一种更新策略就不再适用

所以针对这种情况在InnoDB存储引擎内部有一个stat_modified_counter，用来表示发生变化的次数，所以当次数大于一定数目的时候也会更新Cardinality。

五、B+Tree索引的使用

　　覆盖索引：就是从辅助索引中可以直接得到查询的记录，因为辅助索引中包含的部分记录，所以相比于聚集索引，需要更少的IO

　　Multi-Range Read (MRR) : 为了减少磁盘随机访问，并且将随机访问转化为较为顺序的数据访问。

　　有如下几个好处：

1. MRR使数据访问变得较为顺序，在查询辅助索引时，首先根据得到的查询结果按照主键进行排序，并按照主键排序的顺序进行书签查找
2. 减少缓冲池中页被替换的次数
3. 批量处理对键值的查询操作

　　具体的处理流程为：

1. 将查询得到的辅助索引的键值存放于一个缓存中，这时缓存中的数据是按照辅助索引的键值排序的
2. 将缓存中的键值根据RowID进行排序
3. 根据RowID的排序顺序来访问实际的数据文件

　　Index-Range Pushdown（ICP）：是MySql 5.6版本开始支持的一种根据索引进行查询的优化方式：

　　之前的MySql不支持Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录，在支持了Pushdown之后，MySql会在取出索引的同时

　　判断是是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层，这样可以整体提高数据库的性能。　　

六、全文检索

　　全文检索功能是在InnoDB 1.2.x版本开始支持的，全文检索一般通过倒排索引来实现，它在辅助表中存储了单词与单词自身在一个或多个文档中所在的位置之间的映射

　　这种关系常利用关联数组实现，其拥有两种表现形式：

1. inverted file index，其表现形式为{单词，单词所在文档的ID}
2. full inverted index ,其表现形式为 {单词，（单词所在文档的ID，在具体文档中的位置）}