MySQL技术内幕 五 ：索引与算法

一：索引概述：InnoDB支持两种常见索引，一种是B+树索引一种是哈希索引。哈希索引是自适应的，引1擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表里生成哈希索引

    B+树索引就是传统意义上的索引，这是关系型数据库中最常用、有效的索引、B+树索引的构造类似于二叉树，根据键值快速找到数据。 记住：B不是二叉（binary），而是代表平衡（balance）,从最早的平衡二叉树演化来的。但不是一个二叉树。

二：二叉查找树

    二叉查找树特性：只要满足了左节点的数据比根节点小，右节点的数据比根节点大的树形结构即可，不一定非要左右对称。但是这种情况会导致

当出现该情况后，二叉查找树的性能就比较低效，没能发挥二叉树的2N次幂查找的优势，因此需要改进，因此引出了新的定义----平衡二叉树

三：平衡二叉树：符合二叉查找树的定义，其次满足任何节点的左右两个子树的高度最大差为1。（应该是左节点（或者是有两个子节点的左节点）与右节点（有两个子节点的最底层节点）的差距）。当插入或者删除、更新时，要保证其平衡二叉树的性质，需要通过多次左旋或者右旋来保证平衡

此时需要一定的开销来维护

四：B+树

        B+树是为磁盘或者其他直接存取辅助设备而设计的一种平衡查找树。在B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。下面的B+树，高度为2，每页存放4条记录，扇出为5

五：B+树索引

        B+树索引本质就是B+树在数据库中的实现，但是B+索引在数据库中有一个特点：高扇出性（也就是一个索引里面存储了大范围数据），因此其高度一般在2-3层，最多只需要2-3次io，一秒100次一般，所以只需0.03s左右

       B+树索引分为  聚集索引（clustered index）和辅助聚集索引（secondary index）.无论哪个，内部都是B+树的，高度平衡。叶节点存放着所有的数据。

        1>聚集索引：聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页，这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

        重点来了：由于实际数据页只能按照一颗B+树进行排序（不可能为了不同索引创建不同个数据格式也就是不同个表），因此每张表只能拥有一个聚集索引。许多情况下，查询优化器非常倾向于采用聚集索引，因为能够在索引的叶节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。许多文档说：聚集索引按照顺序物理的存储数据，这样维护成本非常高。所以它的存储并不是物理上的连续，是逻辑上的连续。一是我们前面说过的页通过双向链表链接，页按照主键的顺序排列。另一个是每个页中的记录也是通过双向链表进行维护，物理存储上可以同样不按照主键存储。

        2>非聚集索引（辅助索引）：叶级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签，该书签用来告诉存储引擎，哪里可以找到与索引相对应的行数据。因为是索引组织表，因此书签就是相应行数据的聚集索引键（本质上还是通过这个索引去找到对应的聚集索引所在的地方，间接查询出来）

    说明：当通过辅助索引来寻找数据时，引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，在通过主键索引来找到一个完整的行记录。

    3>新建索引问题：对于索引的添加或者删除操作，数据库先创建一张新的临时表，然后把数据倒入临时表，删除原表，再把临时表重命名为原来的表名。对于一张大表，添加和删除索引需要很长的时间。从InnoDB Plugin开始，支持快速索引创建方法。只限定与辅助索引，对于主键的操作还是需要重建一张表，操作时会对表加上一个s锁，因此创建的过程中该表只能进行读操作。

    六：B+树索引的使用

        访问表中很少一部分行时，使用B+树索引才有意义。对于性别字段、地区字段、类型字段，可取值范围很小，低选择性。假如对性别进行索引，如：select * from student where sex = “M”，此时可能结果是50%的数据。这时添加索引完全没有必要。如果某个字段取值范围很广，几乎没有重复，高选择性，最合适。

        但是！！！！！！：如果从表中取出很少一部分行时，对这个字段添加B+树索引是非常必要的，但是如果出现了访问字段是高选择性，但是取出的行数据占表中大部分的数据时，只是数据库不会使用B+树索引。（优化器没有使用索引），mysql数据库的优化器会通过explain的rows字段预估查询可能得到的行，如果大于某一个值，则B+树会选择全表的扫表，至于这个值，可能在20%左右，即超过20%，优化器不会使用索引，而进行全表扫描，但是优化器选择的该策略不一定最优，因为预估的返回行和实际返回行可能差距比较大，因此需要根据实际情况是否强制使用索引。

    七：顺序读、随机读与预读取

        为什么索引使用原则为 高选择、取出表中少部分数据。但是为什么少部分？

            概念：顺序读：是指顺序地读取磁盘上的块（Block），随机读 是指访问的块不是连续的，需要磁盘的磁头不断移动。当前传统磁盘的瓶颈之一就是随机读取的速度较低。在数据库中，顺序读是指根据索引的叶节点数据就能顺序地读取所需的行数据，这个顺序只是逻辑地顺序读，在物理磁盘上还是随机读取。但相对来说，物理磁盘上的数据还是比较顺序的。因为是根据区来管理，区是64个连续页，如根据主键进行读取，或许通过辅助索引的叶节点就能读取到数据。

        随机读：一般是访问辅助叶节点不能完全得到结果的。需要根据辅助索引叶节点中的主键去找实际行数据。一般来说，辅助索引和主键所在的数据段不同，因此访问是随机的方式。正因为读取的方式是随机的，并且随机读的性能远低于顺序读，因此优化器才会选择全表的扫描方式，而不是去走辅助索引。

        总结：之所以会因为数据量大而不走索引，是因为数据区是由区来管理，里面有64个连续页，当数据量少的时候，都在同一个区或者其他区（索引组织表会使得索引是采用数组方式顺序排列，区挨得近），此时就是顺序读，走索引，而当数据量过大时，此时已经产生许多区（或者导致不在同一个段里面），一般来说辅助索引和主键所在的数据段不同，因此就会随机读取，此时优化器经过分析觉得随机读性能太低，因此不走设置的索引，而采用全表读取

        为了提高读取性能，InnoDB存储引擎引入了预读取技术，是指通过一次 io 请求将多个页预读取到缓冲池中，并且估计预读取的多个页马上会被访问。传统的io每次只读取1页。（192页）