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在某些情况下可以