MySql优化索引详解(二)

一.索引的本质
理解：正确的创建合适的索引是数据库优化的基础，索引是为了加速对表中数据行的检索而创建的一种分散存储数据结构
1.在RDBMS系统（关系型数据库管理系统）中数据的索引都是硬盘级索引 硬盘级索引：大部分索引都存放在硬盘中，少部分会放在缓存中
解释：

如果建立了索引，先会到索引目录中存储的指针找到对应的硬盘地址，然后根据硬盘地址找对应的表内容
INNODB 引擎中有HASH索引还有BETREE索引
hash索引的优劣势？
查询快 缺点：不支持范围查询（key进行hashCode可以得到数组的下标）
是否存在自适应Hash索引？
官方文档上面显示的是没有，但是随着索引查询，他会自己建立一个key Value的缓存区，key就是主键索引
Mysql采用的数据结构？
Mysql底层采用的索引数据结构是B+树结构
二.各种数据结构以及缩影的推演过程
mySql为什么选择B+tree?
先来排除一下为什么不使用其他的树状结构
1.二叉搜索树：
二叉树是n(n>=0)个结点的有限集合，该集合或者为空集（称为空二叉树），或者由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树组成。

二叉树特点：
由二叉树定义以及图示分析得出二叉树有以下特点：
1）每个结点最多有两颗子树，所以二叉树中不存在度大于2的结点。
2）左子树和右子树是有顺序的，次序不能任意颠倒。
3）即使树中某结点只有一棵子树，也要区分它是左子树还是右子树。

如果是正常数据 二分查找左小右大命中数据，如果是不规则数据。 比如 A,C,F,J这种分支都在右边，查找就相当于全表查找了
2.平衡二叉树：定义：它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。这个方案很好的解决了二叉查找树退化成链表的问题，把插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)。但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间，不过相对二叉查找树来说，时间上稳定了很多。

通过右旋解决上面不规则数据的问题，但是要不停的计算
相对于二叉搜索树多了一个数据区，查找方法与二叉搜索树类似。数据区中存储的可能是指针，也可能是数据。
二叉树存在的缺陷：
1.树的高度太高，就是如果存在太多数据，会不停的找下去
2.没有利用好操作系统跟磁盘的IO交互特性，操作系统查找数据是按照页查找的，假设每次查找的数据大小为4K，但是使用二叉树做一次IO操作只能拿到一个关键字两个字节点引用，和一个数据区，极大的浪费了空间
3.多路平衡树(B-树)：
性质：
（1）每个节点最多拥有m个子树
（2）根节点最少有2个子树
（3）分支节点最少拥有m/2棵子树
（4）所有叶节点都在同一层，每个节点最多有m-1个key，并且以升序排列

（1）获取根节点的关键字进行比较，当前根节点关键字为M，E要小于M（26个字母顺序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）；
（2）拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；
（3）拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）；
关键字D,G 根据字节码排序
空间划分分别是 负无穷大–>D,D,D–>G,G,G–>无穷大
公式：关键字个数=子节点的路数-1
多路平衡树可以解决二叉树的问题，第一个高度问题，从瘦高–>矮胖，第二个内存的问题，能很好的利用内存
4.加强版多路平衡树(B+树)
（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，这样使得B+树每个节点所能保存的关键字大大增加；
（2）B+树叶子节点保存了父节点的所有关键字和关键字记录的指针，每个叶子节点的关键字从小到大链接；
（3）B+树的根节点关键字数量和其子节点个数相等;
（4）B+的非叶子节点只进行数据索引，不会存实际的关键字记录的指针，所有数据地址必须要到叶子节点才能获取到，所以每次数据查询的次数都一样；

关键字采用左闭合区间,主节点，子节点没有数据区查询的索引值就算是等于子节点值也不会停留，会一直走下去，直到叶子节点，进行查询，所有数据区都会在叶子节点
例如关键字5，28，65
空间划分：5<= x <28,28<=x<65 65<=x
优点：
1.IO能力强于B树，因为加载的磁盘块没有数据区了，所有加载的数据会更多，会更加精准；2.基于索引结构，扫库，扫表能力强于B树，只扫叶子节点，B树需要扫所有满足条件的子节点，以便获取数据
3.底层是双向链表结构，就是首个叶子节点的末尾值，指向下一个叶子节点的首部值，天然有序强于B树的排序；4.查询性能会更稳定，强于B树，B树获取索引值的时候，如果子节点没有，会一直找下去，直到找到后，命中，相应的每个磁盘加载的空间和时长不确定，有的耗时长，有的耗时短。而B+树，他就算是获取的索引值等于子节点，也不会停留，最终会到叶子节点查询。
B+树相比于B树及其他树形数据结构来说，更适合用来做存储索引，原因如下：
1.B+ 树的磁盘读写代价更低，B+ 树由于非叶子节点只会存储索引，因此B+ 树的非叶子节点相对于B 树来说更小，如果把所有同一内部节点的关键字存储在同一盘块中，那么该盘块所能容纳的关键字数量也越多，一次性读入内存中的关键字也就越多，相对来说IO读写次数也就降低了
2.B+ 树的查询效率更加稳定，因为具体数据存储在叶子节点中，所以无论查询任何数据都需要从根节点走到叶子节点，那么所有查询的长度也就相同，这样每个数据查询的效率就几乎是相同的
3.B+ 树更有利于对数据库的扫描，B 树在提高了磁盘IO的同时并没有解决遍历元素效率低下的问题，而B+ 树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对数据库中频繁使用的范围查询来说B+ 树更高效
三.MySql如何落地B+Tree索引
Myisam:会生成2个文件，后缀myi,存放索引的，myd:存放数据的，查询时先查询索引，查到对应的索引后，根据索引对应的指针去找对应指针存储的数据
innodb:只生成一个文件，后缀myd

INNODB中的主键索引(聚集索引)最末尾保存的内容，就是这条数据的值，辅助索引：保存的是主键索引的值,为什么不保存地址呢？因为二叉树为了保证树的绝对平衡会不停分裂合并来改变数据的存储位置，如果存的是地址的话，设置完主键索引的值以后，还有来维护辅助索引的值，消耗更大。INNODB中核心索引是：主键索引
四.索引机制中很重要的原则
1.列的离散性
比值越高离散性越好，就是重复率越低，离散性越好
若列的离散性越好，列的选择性就越好，选择性越好的列作为索引更合适，离散性很差的列作为索引可能会适得其反 公式：count(distinct name):count(name)
2.最左匹配原则
简而言之，对索引项中的关键字对比，一定是从左往右依次进行
3.联合索引
如果是单列索引的话，就是一个列就是用一个关键字做的索引，如果是联合索引的话，就是一个列中有多个关键，用特殊字符 逗号拼接起来的一个索引
4.覆盖索引
通过索引项的信息可直接返回所需的查询列，则该索引称之为查询SQL的覆盖所有
5.三星索引
第一颗星：where后条件匹配的索引关键字列越多扫描的数据将越少
第二颗星：避免再次排序
第三颗星:尽可能运用到覆盖索引进行数据的扫描，减少回表IO操作
思考：
1.数据结构的性能特点，决定了数据库的检索性能？
是的，因为数据结构查询性能越高，数据库检索就越快，B+树的结构就是
2.索引额外问题之索引是建立越多越好吗？
答案是否定的，所谓物极必反：
数据量小的表不需要建立索引，建立索引会增加额外的索引维护开销
数据变更需要维护索引，因此更多的索引意味着更多的维护成本
更多的索引也意味着需要更多的存储空间