InnoDB索引

1.概述

InnoDB存储引擎支持一下几种索引

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

本文参考了姜承尧先生的《MySQL技术内幕InnoDB存储引擎》一书

2.B+ 树索引

B+ 树索引并不能找到给定字符的具体位置，而是将字符所在的页读取到内存中，然后再内存中查找数据。B+树中的B不是代表二叉(binary), 而是代表平衡(balance)

B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondary），其内部全是B+树结构，高度平衡。聚集索引与辅助索引的区别是 : 叶子节点存放的是否是一整行的信息

• 2.1 聚集索引

聚集索引就是按照每张表的主键构建一颗B+树，叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每个数据页都通过一个双向链表进行连接

由于每个数据页只能按一颗B+树进行排序，因此每张表只能有一个聚集索引，查询优化器倾向于采用聚集索引，因为聚集索引能在B+树索引的叶子节点上直接找到数据。

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

• 2.2 辅助索引

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

辅助索引并不会影响聚集索引，所以每张表可以有多个辅助索引。
当通过辅助索引查找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获取指向主键索引的主键，然后通过主键索引来找到一个完整的行记录。

3.Cardinality 值

• Cardinality 值的作用

对于查询条件中出现的列不一定都需要添加索引，对于反复出现重复字段的，例如性别，类型、地区等重复性高的列，他们取值范围很小，属于低选择性，这时添加索引是没有必要的，相反，对于数据几乎没有重复的字段，属于高选择性的添加B+树索引最合适。

查看索引是否属于高选择性，可以通过SHOW INDEX 中的Cardinality 值，它表示索引中不重复记录数量的预估值。他并不是一个准确值

4.B+ 树索引的使用

• 4.1 联合索引

创建方法和单个索引创建方法一样，不同之处是有多个索引列。

CREATE TABLE t{
	a INT,
	b INT,
	PRIMARY KEY (a),
	KEY idx_a_b (a,b)
} ENGINE = INNODB

索引 idx_a_b 是联合索引，联合的列为(a,b)。

何时需要使用联合索引呢？
先看联合索引底层的实现， 多个键值对的B+树和单个键的B+树没有什么不同，都是按顺序存放的。

这样就限制了查找方式，如果我们这样查SELECT * FROM t WHERE a = xxx and b = xxx显然这样能使用这个联合索引找到（a,b）
但是我们使用SELECT * FROM t WHERE b = xxx查找b，就不会通过索引来查找，因为构建的B+是先根据a的大小进行排序的，索引通过b找不到位置。

• 4.2 覆盖索引

覆盖索引是从辅助索引中就可以得到查询记录，不需要查询聚集索引，因为辅助索引是不包含整行信息的，所以其大小远小于聚集索引。

• 4.3 优化器选择不使用索引的情况

这种情况多发生于有固定查询范围并且需要对数据整行数据进行查询，例如

SELECT * FROM TABLES WHERE ID>10000 AND ID<102000

• 4.4 Multi-Range Read（MRR） 优化

优化的目的是减少磁盘的随机访问，并将随机访问转化为较为有序的数据访问，这对于IO-Bound类型的SQL查询语句带来性能的极大提升。MRR优化可用于range、ref、eq_ref类型的查询。

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

对于InnoDB 和 MyISAM 存储引擎的范围查询和JOIN 查询操作，MRR的工作方式如下：

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

—————————————————————————————————————————————————————————————————————————————————

• 4.5 Index Condition Pushdown （ICP）优化

之前版本进行索引查询的时候，首先根据索引来查找记录，然后在根据WHERE条件来过滤记录，支持Index Condition Pushdown 后MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。

5 全文检索

B+树索引的特点是支持前缀进行查找，例如SELECT * FROM blog WHERE content like 'xxx%'。对于SELECT * FROM blog WHERE content like '%xxx'，并不支持，这种模式会导致InnoDB扫描整个表，速度会非常慢。那么现在就需要一种方案解决这个问题。那就是全文索引。InnoDB 1.2版本开始，已经全面支持了全文索引(Full-Text Search )。全文检索是将存储于数据库中的整本书或整篇文章中的任意内容单词查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。全文索引使用倒排索引实现，辅助表中存储了单词与单词自身在一个或多个文档中的映射。将整个表中单词所在的行及其位置全部索引出来。这样在需要从中检索出行中释放含有某个单词就很快了。

• 5.1 倒排索引

InnoDB存储引擎采用 full inverted index 的方式实现全文检索。