Mysql系列（十）—Msql之InnoDB索引

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

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

这里的哈希索引是前面提到过的自适应哈希索引。为innodb自己视情况而定建立的，不能进行认为干预。

B+树索引就是传统意义上的索引。注意的是B不是代表binary，而是代表balance。

涉及到的数据结构和算法

二分查找法

二叉查找树和平衡二叉树

可以看到下图的两颗都是二叉查找树，但是因为构建的方式不同。右图的二叉树查找效率就很低了。

由此引出了平衡二叉树，在符合二叉查找树的基础上要求任何节点的两个子树高度差最大为1.

B+树

B+树索引分为聚集索引和辅助索引，其内部都是B+树索引。两者的区别是聚集索引节点存放着所有的数据，辅助索引存放指向聚集索引的偏移量。每个叶子节点称为数据页，每个数据页之间通过双向链表进行连接。由于实际的数据页只能按照一颗B+树进行排序。因此一张表只能有一个聚集索引。在多数情况下，查询优化器更倾向于采用聚集索引，因为其能直接在叶子节点上找到数据。

聚集索引顺序存储并不是在物理上连续而是在逻辑上连续，双向链表保证了他的连续性。双向列表包含页与页之间的和每个页中记录之间的。

辅助索引

辅助索引也称为非聚集索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值外，每个叶子节点中的索引行还包含一个书签。该书签用来告诉innodb存储引擎哪里可以找到与索引相对应的行数据。复制索引可以有多个。如果一个高度为3的辅助索引，聚集索引树的高度同样为3，如果对辅助索引进行搜索，那么一共需要6次逻辑IO访问得到最终的结果。

B+树索引的分裂

B+树索引的分裂并不是总从中间开始的，如果这样可能会造成空间的浪费。如下：

因此innodb存储引擎可以根据一些头信息来决定向左还是向右分裂。随机插入则采用取页的中间记录作为分裂点的记录。

Cardinality值

并不是所有的查询列都需要添加索引，那么什么时候应该添加索引呢？一般经验是，在访问表中很少一部分时使用b+树索引才有意义。比如，对于性别字段、地区字段、类型字段，他们的可选择性范围很小，称为低选择性，即不适合建索引。那么如何查看索引是否是高选择性呢？可以通过show index结果中列的Cardinality来观察。Cardinality值表示索引中不重复记录数量的预估值。

那么数据库是怎么样来统计Cardinality值的呢？Cardinality值的统计是放在存储引擎层的，因为不同的存储引擎实现是不一样的。还需要考虑的是索引的更新操作可能是非常频繁的，每次索引操作都去统计会造成很大的压力。如果一张表有很多的数据，那么统计一次花费的时间也是比较长的。因此，Innodb存储引擎对Cardinality的统计是通过采样的方式进行的。

Cardinality值发生在INSERT和UPDATE两个操作中。但是不可能每次发生操作都去更新，更新的策略为：

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

第一种为自上次统计过之后，表中1/16的数据已发生过变化。第二种情况，存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数，当大于2000000000次数，也同样需要更新Cardinality信息。

那么Cardinality值是怎么更新的呢？同样也是通过采样的方式。默认对8个叶子节点进行采样。具体过程如下：

1. 取得索引中叶子节点的数量记为A
2. 随机取得索引中的8个叶子节点，统计每个页不同记录的个数，即为p1,p2......p8
3. 根据采样信息算出预估值Cardinality值=（p1+...+p8）*A/8

Cardinality值是对8个叶子节点的数据预估得来的，所以一定不是准确值。因为其采用为随机性，所以每次统计都有可能是不同的。可以通过参数innodb_stats_sample_pages来设置统计时采样的数量。

那么统计的时候对于null值怎么处理的呢？默认为nulls_equal，即将null值记录视为相等的记录。该参数可以通过innodb_stats_method来修改，其有效值还有nulls_unequal,nulls_ignored，分别表示将null值视为不同的记录和忽略null值记录。

 

B+树索引的使用

联合索引

联合索引是指对表上的多个列进行索引。从本质上来说，联合索引也是一颗b+树，不同的是联合索引的键值的数量不是1，而是大于等于2.键值是根据联合的值进行排序的，也就是第一个、第二个...因此，对于select * from table where a=xxx and b=xxx会使用到(a,b)联合索引。select * from table where  b=xxx则不会使用到，其满足一个最左匹配原则。

                                                             

覆盖索引

覆盖索引也称为索引覆盖，即从辅助索引中就可以得到查询的记录，而不是需要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以大大减少IO操作。另一个好处是，统计类的查询，innodb存储引擎会尽量选择辅助索引来统计。

注意：select * from table  where a=xxx and b=xxx 对于（a,b）的联合索引会失效，这是因为查询是* ，返回的结果中联合索引只有a,b的值，满足不了要求，所以优化器会选择聚集索引来查数据。

索引提示

Mysql数据库支持索引提示，可以显式的告诉优化器使用哪个索引。例如：

select * from  table use index(a) where a=1 and b=2.对于a索引和（a,b）的联合索引，虽然提示使用a索引了，但是mysql还是会选择联合索引。

select * from  table force index(a) where a=1 and b=2.此查询会强制使用a索引

Multi-Range Read 优化

Mysql5.6版本开始支持，Multi-Range Read的目的是为了减少磁盘的随机访问，将随机访问转换为较为顺序的数据访问。Multi-Range Read优化适用于range,ref,eq_ref类型的查询。

MRR优化有以下几个好处

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

对于Innodb和myisam存储引擎的范围查询和join查询操作，MRR的工作方式如下：

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

官方测试优化的性能还是比较大的，如下：

                

若使用了MRR特性，会在Extra列显示Using MRR。

同时MRR还可以将某些范围查询，拆分为键值对，一次来进行批量的数据查询。这样的好处是，在拆分的过程中直接过滤一些不符合查询条件的数据。例如：

select * from table where key1>=1000 and key1<2000 and key2=10000,此表有（key1,key2）的联合索引

MRR启用后，优化器会将查询条件进行拆分为（1000，1000）（1001，1000）（1002，1000）...（1999，1000）进行查询，不满足的段直接过滤掉。

 

Index Condition Pushdown(ICP)优化

同样为Mysql5.6版本开始支持的索引查询优化方式。传统的查询为根据索引来查找记录，然后再根据where条件来过滤。在支持该特性后，mysql会在取出索引的同时进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层sql层对记录的索取，从而提高数据库的整体性能。

该优化支持range、ref、eq_ref、ref_or_null类型的查询，当使用时，可在extra列看到Using index condition提示。