mysql-索引分析

Mysql索引存储模型推演
一：二叉查找树
特点：左子树的所有的节点都小于父节点，右子树的所有节点都大于父节点，就是一个有序的线性表（从下往上看 2 6 11 13 17 22） 图1

二叉查找树，既能实现快速查找，又能实现快速插入
缺点：但是二叉查找树有个缺点，就是他的查找速度和这棵树的深度有关系，在最坏的情况下，会退化到0(n);

什么是最坏的情况呢？如果我们刚才插入的顺序，刚好是 2 6 11 13 17 22
;那么如图所示，它会变成链表，变成顺序查找了！ 如图2

二.平衡二叉树
特点：左右子树深度差，绝对值不超过1；什么意思呢？比如左子树的深度是2，那么右子树的深度只能是1或者3

那么我们在按照2 6 11 13 17 22 插入，如图3

那么他是如何实现平衡的呢？咱们开始分析

第一步：举个例子 ；
如图4所示，插入7，会导致左右子树绝对值差超过1，

那么我们从7 往上找，找到不平衡的最小子树 那么我们找到是4 6 8 ；然后我们将整颗树中序遍历，排序是

6变成根节点 4变成6的左节点，8变成6的右节点，剩余的照抄，平衡后的树如下图所示

平衡的问题解决了，平衡二叉树作为索引，怎么查找数据呢？
在平衡二叉树中，一个节点是大小固定的单位，作为索引，该怎么存储数据呢？
1：存储建立索引字段的值，叫做减值，比如id值
2：还要保存完整记录在磁盘上的地址
3：由于AVL是二叉树，还要保存左右节点的指针
如图所示

当我们我们用树的结构存储索引的时候，每访问一个节点，就要和磁盘发生一次io操作，innodb操作磁盘的最小的单位是一页，大小是16k。

那么一个树的节点，必须设计成16k的大小，不然就会出现读不完或者读不够的事情

怎么解决这个问题？
第一：就是让每个节点存储16k的数据
第二：节点上的关键字的数量越多，也就意味着有更多的分叉
这个就是我们要引入的多路平和二叉树

3.B Tree

查找规则是什么？比如这张表，我们要查找15，因为15小于7，走左边，因为15大于12，走右边，在磁盘快7里面，我们只找了3次，这是不是比Avl效率高些

B tree是如何实现一个节点，保存很多数据，并保持平衡的？

比如一个节点，最大路数是3，那么当我们插入1 2 3，当我们插入3的时候，本来应该在第一个磁盘快，但是如果一个节点有3个关键字的时候，意味着有4个指针，子节点会变成4路，这个时候必须进行分裂，把2提上去，把1和3变成2的子节点；
如果删除节点，会有相反的合并操作

4：一张innodb表可能有很多索引，数据是肯定只有一份的，那数据是在哪个索引的叶子节点上呢？

这里给大家介绍一个聚集索引；就是索引的减值逻辑顺序和表数据行的逻辑存储顺序是一致的。

问题来了？主键索引之外的索引是不是也存储一份完整的数据？
答案是否定的，因为这样会带来额外的存储空间和计算消耗。
他们的叶子节点没有完整数据，怎么检索数据呢？
比如我们在name字段上建立的普通索引，当我们检索name=myl,的时候，二级索引存储的是这条记录对应的主键值 比如 myl id=1;

4:索引的使用原则

是不是建立索引越多越好呢？肯定不是，索引也会带来一定的开销
1：建议在离散度高的字段上建立索引
2：如果b+tree里面重复值太多，mysql优化器发现走索引和全表扫描差不多，不一定会走索引
3：联合索引最左匹配：
比如我们在user表的name和phone字段上建立联合索引
联合索引在B+tree上是最复杂的结构，它是按照从左到右的顺序来建立搜索树的，name在左边，phone在右边

什么时候用到联合索引？所以当我们创建联合索引的时候，最常用的列放在最左边

5：索引条件下推：只适用于二级索引，目标是减少访问表的完整行的读数量，从而减少io操作，这里说的下推是意思是过滤的动作在存储引擎做完，而不需要在server层做。

6：索引的创建和使用

用到索引：
6.1：在用于where判断order排序和join的on,group by 字段上面建立索引
6.2： 索引的个数不要过多
6.3：离散度低的不要建立索引，比如姓名
6.4: 频繁更新的值 不建议作为索引
6.5: 随机无序的值，不建议作为索引
6.6: 组合索引，把离散度高的放到前面
6.7

用不到索引：
6.8索引列使用函数
6.9 like前面加%
6.91 not like不可以，！=和 not in在某些情况下可以