Mysql——InnoDB存储引擎索引

索引是程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响（插入和删除时会对表上的索引进行调整），而索引太少，对查询性能又会有影响。所以需要找到一个合适的平衡点。
InnoDB存储引擎支持的集中常见索引：
B+树索引
全文索引
哈希索引

一、B+树索引

B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读到内存，再在内存中进行查找，最后得到要查找的数据。（这就是select 1和select *查询时间是一样的原因）
B+树是通过二叉查找树，再由平衡二叉树，B树演化而来，但是B不是代表二叉（binary），而是代表平衡（balance）。在查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。B+索引在数据库总有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，也就是说查找某一键值的行记录最多只需要2到4次IO。
左旋：父节点和右子节点逆时针旋转，右子节点的左子节点移到父节点的右子节点上
右旋：父节点和左子节点顺时针旋转，左子节点的右子节点移到父节点的左子节点上

数据库中的B+树索引分为聚集索引和辅助索引（非聚集索引），它们内部都是B+树的，高度平衡的，叶子节点存放着索引的数据。它们的不同点在于叶子节点存放的是否是一整行的信息，即聚集索引key存放的是主键，data为一整行的数据信息，而辅助索引key存储的是索引，data为主键信息。非叶子节点中存放的是键值以及指向数据页的偏移量。

1.1、聚集索引：

InnoDB存储引擎是索引组织表，即表中数据按照顺序存放。而聚集索引就
是按照每张表的主键构造一颗B+树，同时叶子节点存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。同B+树数据结构一样，每个数据页都通过一个双向链表来进行连接。由于实际的数据页只能按照一颗B+树进行排序，所以每张表只能拥有一个聚集索引。
在多数情况下，查询优化器倾向于采用聚集索引，因为它能直接在索引的叶子节点上找到数据，而不用多次回表查询。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中主要有两点：一是数据页通过双向链表链接，数据页按照主键顺序排序，另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。
所以聚集索引的另一个好处是，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。

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

在辅助索引中，叶子节点并不包含行记录的全部数据。因此叶子节点除了包含键值外，每个叶子节点中的索引行中还包含了相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但是只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的记录（这个过程称为回表）。这也是为什么辅助索引在一般情况下没有聚集索引快的原因。

1.3、B+树索引分裂：

InnoDB存储引擎的Page Header中有以下几个部分用来保存插入的顺序信息：
PAGE_LAST_INSERT
PAGE_DIRECTION
PAGE_N_DIRECTION
通过这些信息，InnoDB存储引擎可以决定是向左还是向右进行分裂，同时决定将分裂点记录为哪一个。若插入是随机的，则取页的中间记录作为分裂点的记录。若往同一方向进行插入的记录数量为5，并且目前已经定位到的记录（InnoDB存储引擎插入时，首先需要进行定位，定位到的记录为待插入记录的前一条记录）之后还有3条记录，则分裂点的记录为定位到记录后的第三条记录，否则分裂点就是待插入的记录（这在自增插入时普遍存在的一种情况）。
来看一个向右分裂的例子,并且定位到的记录之后还有3条记录,则分裂点记录如图所示。

图5-17向右分裂且定位到的记录之后还有3条记录, split record为分裂点记录最终向右分裂得到如图5-18所示的情况。

对于图5-19的情况,分裂点就为插入记录本身,向右分裂后仅插入记录本身,这在自增插人时是普遍存在的一种情况。

1.4、覆盖索引

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

1.5、优化器

优化器选择不使用索引的情况：
优化器不使用索引的情况多发生与范围查找、JION链接操作等情况。

1.6、索引提示：

Mysql数据库支持索引提示（INDEX HINT），显示地告诉优化器使用哪个索引。一般两种情况下可能需要用到索引提示：
1.Mysql数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢，这是我们可以强制优化器使用某个索引，来提高SQL运行速度。
2.SQL语句可以选择的所有非常多，这是优化器选择执行计划时间的开销可能会大于SQL语句本身。这时也可以强制SQL使用某个索引来完成查询。
USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会根据自己的判断选择，而FORCE INDEX会强制优化器使用该索引。
所以用户确定指定某个索引来完成查询，那么最可靠的是使用FORCE INDEX，而不是USE INDEX。

1.7、Multi-Range Read优化：

Multi-Range Read优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。Multi-Range Read优化可适用于range、ref、eq_ref类型的查询：
MRR优化的好处有：
1.MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
2.减少缓冲池中页被替换的次数。
3.批量处理对键值的查询操作。
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：
1.将查询得到的辅助索引键值存放于一个缓冲中，这时缓冲中的数据是根据辅助索引键值排序的。
2.将缓存中的键值根据RowID进行排序。
3.根据RowID的排序顺序来访问实际的数据文件。
此外，若InnoDB存储引擎或者MyISAM存储引擎的缓冲池不是足够大，即不能存放下一张表中的所有
数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。若是按照主键顺序进行访问则可以将此重复行为降最低。

1.8、Index Condition Pushdown（ICP）优化

和Multi-Range Read一样，Index Condition Pushdown（ICP）同样是Mysql5.6开始支持的一种根据索引进行查询的优化方式。之前不支持Index Condition Pushdown（ICP）的Mysql数据版本，当进行索引查询时，首先根据索引来查询记录，然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown（ICP）后，Mysql数据库在去除索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch）从而提高数据库的整体性能。
Index Condition Pushdown（ICP）优化支持rang、ref、eq_ref、ref_or_null类型的查询。

二、哈希索引

InnoDB存储引擎支持的哈希索引时自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否再一张表中生成哈希索引。
哈希算法会遇到哈希碰撞，数据库中一般采用最简单的解决技术是链接法（即哈希值一样的数据以链表存储）。哈希函数分为除法散列、乘法散列、全域散列，数据库一般采用除法散列（取余）。

三、全文检索：

全文检索是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术，它可以根据需要获得全文中有关章、节、段、词、句等信息，也可以进行各种统计和分析。

3.1、倒排索引：

全文索引通常使用倒排索引（inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：
1.inverted file index，其表现形式为{单词，单词所在的文档ID}
2.full inverted index，其表现形式为{单词，（单词所在文档的ID，在具体文档中的位置）}
可以看出，对于inverted file index，仅存取文档id，二full inverted index存储的是对（pair），即（DocumentId， Position）。

3.2、InnoDB全文检索

InnoDB存储引擎从1.2.X版本开始支持全文检索的技术，其采用full inverted index的方式。在InnoDB中，将（DocumentId， Position）视为一个“ilist”。因此在全文检索的表中，有两列，一个是word字段，另一个是ilist字段，并且在word字段上设有索引。此外，由于InnoDB存储引擎在ilist字段中存放了Position信息，故可以进行Proximity Search，而MyISAM存储引擎不支持该特性。
倒排索引需要将word存放到一张表中，这个表称为Auxiliary Table（辅助表）。在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张Auxiliary Table，目前每张表根据word的Latin编码进行分区。
Auxiliary Table是持久的表，存放于磁盘上。然而在InnoDB存储引擎的全文索引中，还有另一个重要的概念FTS Index Cache（全文检索索引缓存），其用来提高全文检索的性能。
FTS Index Cache是一个红黑树结构，其根据（word，ilist）进行排序。这意味着插入的数据已经更新了对应的表，但是对全文索引的更新可能在分词操作后还在FTS Index Cache中，Auxiliary Table可能还没有更新。InnoDB存储引擎会批量对Auxiliary Table进行更新，而不是每次插入后更新Auxiliary Table。当对全文检索进行查询时，Auxiliary Table首先会将FTS Index Cache对应的word字段合并到Auxiliary Table中，然后再进行查询。这种merge操作非常类似之前介绍的Insert Buffer的功能，不同的是Insert Buffer是一个持久的对象，并且其是B+树的结构。然而FTS Index Cache的作用又和Insert Buffer是类似的，它提高了InnoDB存储引擎的性能，并且由于其根据红黑树排序后进行批量插入，其产生的Auxiliary Table相对较小。
当前InnoDB存储引擎的全文检索还存在以下的限制：
1.每张表只能由一个全文检索的索引
2.由多列组合而成的全文检索的索引列必须使用相同的字符集与排列规则
3.不支持没有单词界定符（delimiter）的语言，如中文，日语，韩文。