InnoDB存储引擎 索引与算法总结

数据结构与算法

• 二分查找法
• 二叉树
• 平衡二叉树

B+ Tree

• B+树的插入操作：这主要就是B+树分裂相关的知识点，传统的B+树插入，首先会检查叶子节点是否还有空间存放要插入的数据，如果有就直接存放，如果叶子节点没有，就将叶子节点中间元素提取至父节点，该叶子节点进行分裂，如果父节点有空间就直接存放刚刚提取叶子节点的中间元素，如果没有那么父节点也需要进行一次分裂。
• B+树的删除操作：这需要与填充因子挂钩，填充因子最小的取值是50%，删除后，如果叶子节点与中间索引节点都没有小于填充因子，则不会产生合并节点，如果叶子节点小于了填充因子，那么就会对其兄弟节点进行节点合并，然后更新中间索引节点，中间索引节点也是一样，如果小于了填充因子也会和兄弟节点进行合并，然后更新更上一层的索引节点。

B+ 树索引

• 聚集索引：按主键进行创建索引树，叶子节点存放整行的数据，一张表只能有一个聚集索引。聚集索引只是逻辑上连续，并不是物理上连续，节点之间采用双向链表，并且叶子节点中 页的数据也是采用双向链表。

• 辅助索引：我们创建的非聚集索引都是辅助索引，一张表可以存在多个赋值索引，赋值索引的叶子节点存储的是一个书签，该书签告诉InnoDB该从哪里找到该记录对应的整行数据

• B+树索引的分裂：上面提到的B+树插入操作时进行的分裂只是最简单的一种情况。InnoDB采用B+树索引的分裂则有些不同，之前提到的分裂有一个问题，那就是空间利用率不高，假如是一个自增的情况，当一个节点满了后从中间元素进行分裂，那么分裂后的左边子节点就不会再存放值了，这样就导致了空间利用率不高的情况。B+树索引的分裂方式有些不同：假如一个页中只能存放5条记录，这时有一条待插入的数据，先找到待插入的位置，然后找到记录点(待插入位置的前一个元素)，如果记录点之后还有第三条记录，则之后的第三条记录为分裂点，否则就是待插入元素为分裂点。

• B+树索引的管理：首先就是创建的语法，如何创建索引；还可以使用show index form 表名来查看一张表创建的索引情况。
  下面介绍使用show index form 表名各个字段的含义
  Table: 索引所在的表名
  Non_unique： 是否为非唯一索引，如果为0就表示该索引是唯一索引
  Key_name: 索引的名字
  Seq_in_index: 对于联合索引来说，可以查看该列在索引中的位置
  Column_name: 索引列名称
  Collation: 列以什么方式存储在索引中，A或Null，B+树索引总是A ，hash索引则会先null
  Cardinality：索引中唯一值的预估值
  Sub_part: 是否为列的部分被索引，因为我们可以对一个字段的前N的字节进行创建索引
  Packed： 关键字如何被压缩，如果没有被压缩则显示null
  Null： 是否索引的列中含有null值
  Index_type: 索引的类型， B+树或hash
  Comment： 备注

• Fast Index Creation： 在Mysql5.5版本之前，索引的创建过程一直是一个诟病。具体过程如下：首先创建一个临时表，然后把原表的数据导入进临时表，删除原表，将临时表的表名改为原表的表名。在这个期间对表的修改操作都将不可用。后来就出现了FIC机制，提高创建索引时的效率，该机制不会在创建临时表，在创建索引时会对数据加一个S锁。FIC方式只限定于辅助索引，对于主键的创建和删除同样需要创建一行临时表

• Online Schema Change机制

• Online DDL机制，fic机制虽然不会产生临时表，但是在创建索引时还是不能对数据进行更新操作，Online DDL机制则允许在创建索引时也能执行更新操作，实现原理就是在创建索引时，所有的更新操作会被记录到缓存中，等索引创建完成后再重新应用到表上，该缓冲大小由innodb_online_alter_log_max_size控制 默认128MB，如果在创建索引时，有大量的更新操作，导致该缓冲的容量使用完了，那么在执行更新操作是会报一个错误的。

Cardinality

该值表示索引列中唯一值的预估值，Mysql的查询优化器会根据该值的大小来决定是否要使用该索引。Cardinality的统计只会发生在Insert和Update操作之后，但并不是每一次触发Insert和Update都会重新计数一次，InnoDB存储引擎内部对Cardinality的更新机制有两点：第一点是表中的数据有1/6发生的变化，第二点是有一个计数器来记录每一次发生变化的次数，当计数器达到20亿也会触发更新。
上面提到了什么情况下会对Cardinality值进行更新，接下来是如何进行，InnoDB对Cardinality值的更新是采用了一个随机取样的机制，从叶子节点中默认随机读取8页数据，然后统计每页中该索引字段的唯一值，然后对统计后八页的值求平均值，然后乘总叶子节点数量得到最终的预估值。
关于Cardinality还有一些其他的知识点，比如通过innodb_stats_transient_sample_pages参数来控制随机抽样采用多少页，默认是8；还有统计数量时对null的判断情况，还有执行哪些命令也会触发更新Cardinality操作……

B+树索引的应用

• 联合索引
• 覆盖索引
• 查询优化器不使用索引的情况：要查询的字段太多，需要进行回表操作
• 强制执行某个索引force index(索引名)，避免应索引太多，优化器来决定最终到底选择哪个索引的耗时。
• Multi Range Read优化：MRR优化，将从辅助索引查询到的值再按照Row_id 进行一个排序，然后再去查询，将对磁盘的随机访问改为顺序访问，第二个特定是在进行某些范围查询时会对查询条件进行一个拆分，可以在拆分的过程中过滤掉一些不符合查询条件的数据。
  是否启用MRR是通过optionizer_switch 中的标记flag来控制的
  参数read_rnd_buffer_size 用来控制键值的缓冲区大小
• ICP优化：mysql数据库在使用索引查询数据时，会同时判断where条件进行过滤。

哈希算法

• 哈希表
• 直接寻址表
• InnoDB存储引擎使用哈希算法来对字典进行查找，起冲突机制采用链表方式，哈希函数采用除法散列方式
• 自适应哈希索引

全文检索

• InnoDB 1.2.X 开始支持全文检索
• 全文检索通常使用倒排索引来实现，利用一个辅助表来存储单词与单词自身在文档中的映射关系，有两种表现形式：inverted file index 其表现形式是单词+单词所在文档id ；full inverted index 其表现形式是单词+单词所在文档id+具体文档中的位置。InnoDB存储引擎采用的是full inverted index形式。将documentId，position视为一个ilist，因此在全文检索表中有两个列Word+ilist。
• InnoDB存储引擎为了提高全文检索的并行性能，共有6张辅助表。
• FTS Index Cache，全文检索索引缓存，是一个红黑树结果，根据word ，ilist进行排序。这意味着插入的数据已经插入到了对应的数据表中，但是对全文索引的更新可能在分词操作后还在FTS Index Cache中，辅助表可能还没有更新，InnoDB会批量对辅助表进行更新。当对全文检索开始查询时，辅助表会先将FTS Index Cache中对应的word字段合并到辅助表，然后在进行查询。innodb_ft_cache_size来控制FTS Index Cache的大小，默认32MB。
• FTS Document ID：该列与word列进行映射，InnoDB存储引擎会在辅助表中自动创建该列，并且会为该列创建一个索引，我们也可以直接创建，但是该列的类型必须为bigin unsigned not null。文档中分词的插入操作是在事务提交时完成的，然而对于删除操作，在事务提交时，并不会删除辅助表中的数据，而只是删除FTS Index Cache中的记录。
• 对于辅助表中删除的记录，InnoDB存储引擎会记录FTS Document ID，并将其保存在DELETED auxiliary table中。设置innodb_ft_aux_table后，用户同样可以访问information_schema架构下的表innodb_ft_deleted来看删除的FTS Document ID
• 用户可以使用optimize table手工将已经删除的记录从索引中彻底删除，innodb_ft_num_word_optimize来限制每次实际删除的分词数量。
• Mysql数据库通过match(被查询的列) against(以何种模式进行查询) 语法支持全文检索的查询。查询的模式有以下几种：Natural Language、Boolean、Query Expansion。
• 若表没有创建倒排索引，则执行match函数是会报错的
• 查询的结果是根据相关性进行倒序排序的