B+树及为什么使用B+树作为mysql索引
都知道mysql索引使用的数据结构是B+树,那么就需要先了解B+树的相关原理和使用方式
平衡二叉树
平衡二叉树与普通的二叉树的区别在于:二叉树中任意一个节点的左右子树的高度相差不能大于 1就是平衡二叉树
二叉树(树结构查询快)->平衡二叉树(解决二叉树出现极端情况的问题)->B树(多节点,高度远小于红黑树等)->B+树(外接链表,增加范围查找的功能.非叶子结点只保存索引不保存数据节省空间)
选择B+树的原因
- 类似“目录”功能,树只保存索引,下挂的链表保存数据
- 增加双向链表,符合范围查找的功能
- InorDB的主键使用聚集索引,这样物理空间也是连续的存储,更方便页的查找
页及mysql索引如何实现的
操作系统从磁盘中取数据,是按照页取的,即一次取出一页=4Kb.mysql获取磁盘中数据也是按照页获取,只是这里的页默认是16kb
页的构成:
-
用户数据区域:按照顺序存储,形成一个长链表(长链表的查询会很慢,所以需要页目录配合使用)
-
页目录:简化查询操作,其中存储的其实就是这个页中的索引信息(类比上面B+树图中上三层数据)
-
页头:包含前后指针,指向其他页的内存地址,使用该指针,所有的页之间构成了一个双向指针的长链表
为了更快定位到所查询数据的页是哪个,将这个双向指针的长链表使用B+树构成一个索引
下面就相当于是一个主键索引(聚簇索引)
总结:
B+树的非叶子结点就是所有页数据构成的一个树结构
B+树的叶子结点,存储的就是各个页的数据信息,页与页之间使用双向链表相互连接
所以这里的查询操作就变成了
- 首先去页构成的目录中使用二分查找到需要获取的页
- 从磁盘中取出页
- 在页目录中找到数据区域分配的组信息
- 在数据区域找到对应的数据
mysql索引及索引失效的各个情况
索引在存储中的目的:就是作为一个页构成的目录.提升获取对应页的速度(因为页存在磁盘中,所以如果全表扫描一个一个便利,就需要进行很多次的IO操作.而使用索引命中了对应的页就只需要进行一次IO操作即可)
主键索引:
- 使用B+树维护的一个树形结构,叶子结点是对应的页.非叶子结点是页构成的一个查询目录
- 页中包含页目录和数据区,页目录中的数据将数据区分成不同的区,方便查找
最左前缀原则
原理:构建的联合索引,虽然有多个字段,但是最先比较的都是最左边的字段,如果没有最左边的字段,那么就没办法在B+树上进行判断走向
即:其实创建联合索引的时候,实际上是从左向后创建索引
举例:index(a,b,c),其实会创建类似index(a),index(a,b),index(a,b,c)的索引信息.而针对只有b和c的数据查询是命中不了索引的
聚簇索引和非聚簇索引
使用非聚簇索引的目的:
- 减少空间的使用,因为如果创建一个索引就备份所有页的数据是很消耗空间的
- 进行update操作就需要对所有索引的数据进行修改
索引失效
规则:
- 联合索引最佳左前缀原则
- 针对索引的任何操作都会失效(函数、计算等)`select * from t where left(name,4)='July'
- 多次回表会使索引失效(覆盖索引:查询的字段是索引结果信息,不需要回表就可以获取)
- 索引上使用
!=、<>、is null、is not null会失效 - 使用like且以通配符开头会失效
select * from t where name like '%鱼'会失效,但是select * from t where name like '香%'不一定会失效 - 索引字段是字符串,但是查询时候字符串未加‘’会失效
- 索引字段使用
or会失效,建议使用union修改
字符串索引
会根据字符串的字符规则进行排序,可能针对不同的国家的字符顺序不一样
问题
面试问题
简单介绍下对mysql索引的理解
mysql索引包括主键索引、唯一索引、普通索引、联合索引及MyISAM引擎自带的全文索引等
InnoDB引擎默认索引的实现数据结构是B+树,为了更快命中所查询数据在磁盘中存储的是哪个页,从而减少查询时间及减少磁盘IO的次数
创建索引使用create [索引属性] index 索引名 on 表名(索引字段(索引条件等))
索引是一种以空间换时间的一种系统优化方式
默认创建表后会生成一个主键索引,如果不指定主键索引会自动获取表中的一个唯一索引生成主键索引,如果没有会自动虚拟出一列rowId作为主键索引
为什么innoDB要使用B+树作为索引的数据结构
B+树的数据结构有如下几个特点便于mysql的搜索
- 每个节点可以有多个值(联合索引)
- 叶子结点包含全部的索引数据(结果查询)
- 叶子结点使用双向指针进行指向(便于范围查找)
索引演化也是按照需求来的,具体可以看上面的索引演化过程
说下聚簇索引和非聚簇索引的区别
聚簇索引一般也称为主键索引,其叶子结点是整张表的数据
非聚簇索引的叶子结点的值是聚簇索引的key信息.使用非聚簇查询之后还需要再进行“回表”操作
主键索引、普通索引和联合索引有什么区别
答案可以参照上文中介绍的联合索引结构,以及下面描述的主键索引和普通索引之间的区别来区分
为什么主键建议使用自增的,而不是uuid
- 插入数据的时候主键会维护一个聚集索引,索引使用的是B+树实现,B+树在增加数据的时候,从中间插入的效率要远大于从末尾插入一个数据
- 在页中存储的数据,如果从中间插入数据,会导致相应后续的数据需要”换页“的问题
为什么一般认定2500万行数据的表就会出现查询效率的骤降
- 按照每行的数据计算所得,一行数据1kb,再加上指针目录等,基本上可以发现二层的B+树的结构就是2500万行数据差不多
- 如果超过2500万行数据,这个B+树的索引就需要增加至三层
索引在查找中起到的作用?
- 定位到需要从磁盘中获取的页数据,全表扫描就需要多次从磁盘中获取数据
- 类似二分查找的方式快速定位
面试问你联合索引什么情况下会命中索引
-
最左前缀原则
-
mysql也有自己校验的优化器,即如果判定走索引的消耗比全表扫描的消耗还大,就不会进行索引数据
类似全表扫描只需要一次查询出来,但是如果使用非聚簇索引还需要进行非常多次的回表操作,这个时候就不会进行索引
如果创建表时没有创建主键索引
- mysql会检查有没有唯一索引,如果有的话就默认定义为主键索引
- 如果没有唯一索引,就会创建一个隐藏的主键索引(rowId)
常见的面试索引命中问题
表(a,b,c,d,e),index(b,c,d),primary(a)
| 查询语句 | 是否使用索引 | 原因 |
|---|---|---|
| select * from t where b>1 | 否 | 因为需要进行多次回表操作 |
| select a,b,c,d from t where b>1 | 是 | 查询条件在非聚簇索引上就可以获得,不需要回表 |
| select b from t | 是 | 使用索引,但是是索引扫描. 因为索引存储数据量要小,所以分配的页少, 进行数据库的IO操作次数更少 |
| select * from t order by b,c,d | 都可 | 不使用索引(数据量少):全表扫描,需要进行重新排序 数据量少时直接在内存中排序速度快,如果速度量过多内存不足 以构成排序操作就需要进行一些规则性的操作(比如一次比较一部分)) 使用索引:不需要排序直接使用索引排序,需要大量的回表操作 |
什么叫做覆盖索引
注意,并不是一种索引类型,而是一种索引操作
即查询结果信息可以直接在索引树结束获得,不需要再进行回表来获得数据.这种查询操作就叫做覆盖索引
说下索引下推
mysql5.6新增针对查询优化的
在正常进行索引查询的时候,如果还有其他查询字段,会先使用索引字段命中一部分数据,再使用其他字段进行比较.之后再进行回表操作获取整张表的数据返回
举例子
创建了name和age的联合索引
select * from t1 where name=“李” and age =10
mysql5.6之前:B+索引命中name->搜索->查找到多个结果->多个结果回表->回表结果判断age=10的返回
mysql5.6之后:B+索引命中name->搜索->查找到多个结果->判断该次索引结果的age是否=10->符合条件的结果进行回表并返回
也就是说,mysql针对联合索引的查找字段,增加了在非聚簇索引获得值之后判断另一个参数是否满足条件这一步再进行回表操作
简单介绍下explain的使用
这块有点大,后期补充,可以先了解这位作者的杰克思勒讲的很详细
普通索引和唯一索引
区别: 唯一索引指定的字段保证唯一性
性能分析:
查询效率
普通索引在索引命中第一条数据之后,还会在叶子节电继续往后面查询,直到查询到不是对应参数为止
唯一索引,命中第一条之后直接返回,不会再继续查询数据
但是在innoDB是按照页数据来查询,所以相邻的数据查询出来的消耗很小(不排除相邻两条数据刚好在不同页上的情况).所以上述区别微乎其微
插入和更新效率
唯一索引的特征:不允许索引字段重复
当数据在内存页中时:
- 普通索引:找到位置,插入数据
- 唯一索引:找到位置,判断是否重复,插入数据
当要插入的数据不在内存页中时:
- 普通索引:更新记录到change buffer(没设计IO操作)
- 唯一索引:从磁盘中获取到数据页,判断是否重复,插入这个值到磁盘中(涉及两次IO操作)
唯一索引不能使用change buffer(因为需要校验是否会产生重复的数据,所以需要将磁盘中的数据查询到缓存中,这个操作就已经违背了change buffer的初衷(消耗更大),所以不会使用change buffer,而是查询出来直接更新)
字符串字段创建索引
正常针对字符串创建索引,只是针对整个字符串创建索引alter table User add index idx_index1(tel)
业务上经常会有类似这种场景
查找手机号前三个字符是移动号段“134,135,136,137, 138,139,147,150,151, 152,157,158,159,178,182,183,184,187,188,198，170，171，165”
所以我们会有类似这样的查询语句
select * from User where tel like '134%';
经常会使用手机号来查询用户信息 select * from User where tel ='12345678912';
所以针对索引字段是字符串,我们可以类似这种创建索引的方式alter table User add index idx_index1(tel(3))也就是取tel的前三个字符生成一个非聚集索引
但是使用前缀索引,会导致查询的时候比正常索引多执行一步操作
获取到索引值之后需要比对是否是整体结果信息
使用前缀索引优点:可以减少索引在内存中占用的空间,以前是索引整个tel,现在只用索引前三个字符即可.缺点是:进行一些查询操作的时候,多了查询的操作行为,增加了查询的消耗
change buffer
mysql进行数据更新时,如果更新的数据刚好在内存中,那么就直接更新,如果没有在内存中.就会先将更新的操作缓存在change buffer中.
change buffer实际更新数据的情况有以下几个情况
- 下次访问该数据页的时候,会同时将
change buffer中的数据进行执行从而使查找的数据获得更新 - 定时自动将数据更新到磁盘的数据页上:
merge操作
change buffer的职能本质就是减少磁盘的IO操作
change buffer用的是buffer pool中的内存空间,可以使用设置数据库参数innodb_change_buffer_max_size来指定change buffer可以占用的buffer pool的百分比空间
使用场景:写多读少的业务场景 .而对于如果业务场景中所有的更新操作后面都带着查询,那么建议关掉change buffer
所以在生产环境中,mysql是有一个内存命中率的概念(命中率越高,说明使用的操作越高效)
学习
诸葛老师讲的很干(干货满满):诸葛老师bilibili
索引失效和优化:张啊咩
觉得不错点个赞呗,不然吐个槽呗
