五:MySQL索引与算法

概述

InnoDB存储引擎支持以下几种常见的索引:
B+ 树索引，全文索引， 哈希索引. 其中 哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引。不能人为干预是否在一张表中生成哈希索引。B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引.B+树索引的构造类似于二叉树，根据键值快速找到数据。

数据结构和算法

二分查找法

二分查找法用来查找一组有序的记录数组中的某一记录。基本思想是: 将记录按有序化(递增或递减)排序,在查找过程中采用跳跃式方式查找,即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将待查序列缩小为左半部分,否则为右半部分，通过一次比较，将查找区间缩小一半。在之前的介绍中， 每页中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过Page Directory 进行二分查找得到的

二叉查找树和平衡二叉树

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。

对上图二叉查找树进行中序后输出:2，3，5，6，7，8。
显然构造好的二叉树的查找效率会比顺序查找效率要高。因为好的构造二叉树相当于二分法查找法.
但是也有不好的二叉查找树:比如

上图的查找效率就比较低了，因此就引出了平衡二叉查找树。即平衡二叉树，又称AVL树。平衡二叉树定义如下:首先符合二叉查找树定义,其次必须满足任何节点的两个子树的高度最大差为1.显然5-3不满足平衡二叉树的定义。平衡二叉树的查询速度比较快，但是维护一课平衡二叉树的代价比较大。需要一次或多次左旋和右旋来得到插入或更新后树的平衡性。因此对一课平衡树的维护是具有一定开销的，不过平衡二叉树多用于内存结构对象中，因此维护的开销相对较小。

B+树

简单定义介绍：B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树.在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。

B+树的插入操作

B+树的插入必须保证插入后叶子节点中的记录依然排序，同时需要考虑插入到B+树的三种情况.每种情况都可能会导致不同的插入算法。

比如对于如下图的一颗B+树。

第一种情况:

  插入28这个值，此时发现Leaf Page 和Index Page 都没有蛮，直接插入得到如下图:

第二种情况

接着插入70 这个键值,这时原先的Leaf Page 已经满了。当时Index Page 没有满。属于第二种情况，需要拆分Leaf Page。得到如下图： 此时各个叶子节点的双向链表指针没有画出，实际存在。

第三种情况

插入键值95. 此时 Leaf Page 和 Index Page 都满了。此时需要做两次拆分.

可以看到，不管怎么变化，B+树总是会保持平衡。为了保持平衡对于新插入的键值需要做大量的拆分页操作。页的拆分意味着磁盘的操作，所以需要尽量避免，因此 B+树同样提供了类似平衡二叉树的旋转功能。比如 上面的情况二，插入70的时候。B+树并不会急于拆分叶子节点，而是做旋转操作：

B+树的删除操作

B+树使用填充因子来控制树的删除变化,50% 是填充因子的可设置最小值。B+ 树的删除操作同样必须保证删除后叶子节点种的记录依然排序，删除同样有三种情况考虑:

第一种情况:

对5-9 B+树进行删除70操作，直接删除:

第二种情况:

删除60这个键值.合并节点，得到如下:

B+树索引

数据库中的B+树索引 可以分为聚集索引(clustered index)和辅助索引(secondary index)。其内部都是B+树，即高度平衡的，叶子节点存放着所有的数据。不同点是 叶子节点存放的数据不同，聚集索引叶子节点存放的是数据，而辅助索引存放的是聚集索引的索引值。

聚集索引

聚集索引就是按照每张表的主键构造一棵B+树.同时叶子节点中存放的即为整张表的行数据，也将聚集索引的叶子节点称为数据页。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序,因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外定义了数据的逻辑顺序，聚集索引能够特别快的访问针对范围值的查询。
聚集索引的另外一个好处是，它对于主键的排序查找和范围查找速度非常快。主要是因为数据页是通过双向链表进行连接。

辅助索引

辅助索引也称为非聚集索引（Secondary Index）叶子节点并不包含行记录的全部数据，除了包含键值以外，还包含主键的值索引值.

Cardinality 值

这个值来描述字段区分度是否高。如果字段的区分度较高，就适合加索引.

B+树索引的使用

不同引用的B+树索引的使用

在OLTP（联机事务处理系统）应用中，查询操作只从数据库中取得一小部分数据。这样建立索引后，查询经过该索引取得数据是比较有意义的。
但是在OLAP(联机分析处理系统）中，这个主要是大量的查询会用的比较多。此时的索引主要是用于关联查询，方便关联查询的更快.

组合索引

组合索引 也是B+树结构。比如组合索引 (a,b) 内部结构 是首先根据a进行排序，然后再根据b进行排序。即当a相同的数据，b是排好序了的。例如:

所以组合索引 有个优势，当查询数据的时候，需要根据 第二个字段进行排序的时候，这个效率会更高一些，因为已经默认排序了.

覆盖索引

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录，使用覆盖索引的一个好处是辅助索引记录的信息较小，可以大大减少IO操作.
有种情况查询统计信息。select count() from table_A where b>1 and b<999.
有组合索引(a,b). 上述语句会走组合索引。 默认查询所有的a,然后用b进行过滤。由于是统计，不需要查询聚集索引，所以上述语句 可以换算成:
select count() from table_A where a=‘所有’ and A>1 and b<999;

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

当使用辅助索引查询到的数据较多的时候，此时得到的主键值较多，并且是非顺序的，导致根据这些id去查询数据效率会比较低(随机查找).可能会采用全表扫描。
比如 select * from table_B where a>10000; //其中a字段有辅助索引.
通过a 辅助索引能很快找到满足条件的主键索引，然后此时主键索引是乱序的，根据主键去捞数据，不是顺序读取，如果数量很大的话，这样性能也会比较差，优化器最终可能不走这个a索引了。

索引提示

索引提示是指我们需要显式的告诉优化器选择哪个索引。这个对于我们开发场景确实会比较少。一般我们都不会对这个进行干预，由优化器选择具体哪个索引。我们可以了解下。具体的语法如下:
select * from t force index(a) where a=1 and b=2;//这个sql ,强制使用索引a 去进行搜索。

Multi-Range Read 优化

优化的目的是减少磁盘的随机访问，将随机访问转换为较为顺序的数据访问，对于IO-bound类型的SQL查询语句会带来性能的极大的提升。Multi-Range Read 优化可使用于range,ref,eq_ref类型的查询。
1.在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，然后再按照主键排序的顺序进行Io查询，此时就变成了顺序读取。
2.MRR优化 有时候会对查询条件进行拆分。
比如 select * from C where a>100 and a<10000 and b=10000。//该表有组合索引(a,b)。如果没有开启MRR的话， 上述流程 是: 首先根据a 进行范围过滤，然后再根据b进行过滤。
如果根据a过滤有大量的数据是不满足b的条件的话，这里会很浪费性能。
开启MRR的话，会同时根据a和b进行查询，满足才把数据从磁盘捞到内存中。

Index Condition Pushdown 优化

Index Condition Pushdown 是5.6版本支持，之前不支持时，当进行索引查询，首先根据索引查找记录，然后再根据where 条件来过滤记录。
支持Index Condition Pushdown后，Mysql 数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。这样 可以大大减少上层sql层对记录的索取，从而提高数据库的整体性能。Index Condition Pushdown 优化支持range,ref,eq_ref,ref_or_null 类型的查询