学习

诸葛老师讲的很干(干货满满):诸葛老师bilibili

索引失效和优化:张啊咩

B+树及为什么使用B+树作为mysql索引

都知道mysql索引使用的数据结构是B+树,那么就需要先了解B+树的相关原理和使用方式

平衡二叉树

平衡二叉树与普通的二叉树的区别在于:二叉树中任意一个节点的左右子树的高度相差不能大于 1就是平衡二叉树

二叉树(树结构查询快)->平衡二叉树(解决二叉树出现极端情况的问题)->B树(多节点,高度远小于红黑树等)->B+树(外接链表,增加范围查找的功能.非叶子结点只保存索引不保存数据节省空间)

选择B+树的原因

1. 类似“目录”功能,树只保存索引,下挂的链表保存数据
2. 增加双向链表,符合范围查找的功能
3. InorDB的主键使用聚集索引,这样物理空间也是连续的存储,更方便页的查找

页及mysql索引如何实现的

操作系统从磁盘中取数据,是按照页取的,即一次取出一页=4Kb.mysql获取磁盘中数据也是按照页获取,只是这里的页默认是16kb

页的构成:

• 用户数据区域:按照顺序存储,形成一个长链表(长链表的查询会很慢,所以需要页目录配合使用)

• 页目录:简化查询操作,其中存储的其实就是这个页中的索引信息(类比上面B+树图中上三层数据)

• 页头:包含前后指针,指向其他页的内存地址,使用该指针,所有的页之间构成了一个<font color="red">双向指针的长链表</font>

为了更快定位到所查询数据的页是哪个,将这个双向指针的长链表使用B+树构成一个索引

下面就相当于是一个<font color='red'>主键索引(聚簇索引)</font>

总结:

<font color='red'>B+树的非叶子结点就是所有页数据构成的一个树结构</font>

<font color='red'>B+树的叶子结点,存储的就是各个页的数据信息,页与页之间使用双向链表相互连接</font>

所以这里的查询操作就变成了

1. 首先去页构成的目录中使用二分查找到需要获取的页
2. 从磁盘中取出页
3. 在页目录中找到数据区域分配的组信息
4. 在数据区域找到对应的数据

mysql索引及索引失效的各个情况

索引在存储中的目的:就是作为一个页构成的目录.提升获取对应页的速度(因为页存在磁盘中,所以如果全表扫描一个一个便利,就需要进行很多次的IO操作.而使用索引命中了对应的页就只需要进行一次IO操作即可)

主键索引:

1. 使用B+树维护的一个树形结构,叶子结点是对应的页.非叶子结点是页构成的一个查询目录
2. 页中包含页目录和数据区,页目录中的数据将数据区分成不同的区,方便查找

最左前缀原则

原理:构建的联合索引,虽然有多个字段,但是最先比较的都是最左边的字段,如果没有最左边的字段,那么就没办法在B+树上进行判断走向

即:其实创建联合索引的时候,实际上是从左向后创建索引

举例:index(a,b,c),其实会创建类似index(a),index(a,b),index(a,b,c)的索引信息.而针对只有b和c的数据查询是命中不了索引的

聚簇索引和非聚簇索引

使用非聚簇索引的目的:

1. 减少空间的使用,因为如果创建一个索引就备份所有页的数据是很消耗空间的
2. 进行update操作就需要对所有索引的数据进行修改

索引失效

规则:

1. 联合索引最佳左前缀原则
2. 针对索引的任何操作都会失效(函数、计算等)`select * from t where left(name,4)='July'
3. 多次回表会使索引失效(覆盖索引:查询的字段是索引结果信息,不需要回表就可以获取)
4. 索引上使用!=、<>、is null、is not null会失效
5. 使用like且以通配符开头会失效 select * from t where name like '%鱼'会失效,但是select * from t where name like '香%'不一定会失效
6. 索引字段是字符串,但是查询时候字符串未加‘’会失效
7. 索引字段使用or会失效,建议使用union修改

字符串索引

会根据字符串的字符规则进行排序,可能针对不同的国家的字符顺序不一样

问题

面试问题

1. 为什么主键建议使用自增的,而不是uuid

   • 插入数据的时候主键会维护一个聚集索引,索引使用的是B+树实现,B+树在增加数据的时候,从中间插入的效率要远大于从末尾插入一个数据
   • 在页中存储的数据,如果从中间插入数据,会导致相应后续的数据需要”换页“的问题

2. 为什么一般认定2500万行数据的表就会出现查询效率的骤降

   • 按照每行的数据计算所得,一行数据1kb,再加上指针目录等,基本上可以发现二层的B+树的结构就是2500万行数据差不多
   • 如果超过2500万行数据,这个B+树的索引就需要增加至三层

3. 索引在查找中起到的作用?

   • 定位到需要从磁盘中获取的页数据,全表扫描就需要多次从磁盘中获取数据
   • 类似二分查找的方式快速定位

4. 面试问你联合索引什么情况下会命中索引

   • 最左前缀原则

   • mysql也有自己校验的优化器,即如果判定走索引的消耗比全表扫描的消耗还大,就不会进行索引数据

     类似全表扫描只需要一次查询出来,但是如果使用非聚簇索引还需要进行非常多次的回表操作,这个时候就不会进行索引

5. 如果创建表时没有创建主键索引

   1. mysql会检查有没有唯一索引,如果有的话就默认定义为主键索引
   2. 如果没有唯一索引,就会创建一个隐藏的主键索引(rowId)

6. 常见的面试索引问题

   表(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	都可	不使用索引(数据量少):全表扫描,需要进行重新排序 数据量少时直接在内存中排序速度快,如果速度量过多内存不足 以构成排序操作就需要进行一些规则性的操作(比如一次比较一部分)) 使用索引:不需要排序直接使用索引排序,需要大量的回表操作

普通索引和唯一索引

区别:
唯一索引指定的字段保证唯一性

性能分析

查询效率

普通索引在索引命中第一条数据之后,还会在叶子节电继续往后面查询,直到查询到不是对应参数为止

唯一索引,命中第一条之后直接返回,不会再继续查询数据

但是在innoDB是按照页数据来查询,所以相邻的数据查询出来的消耗很小(不排除相邻两条数据刚好在不同页上的情况).所以上述区别微乎其微

插入和更新效率

唯一索引的特征:不允许索引字段重复

当数据在内存页中时:

• 普通索引:找到位置,插入数据
• 唯一索引:找到位置,判断是否重复,插入数据

当要插入的数据不在内存页中时:

• 普通索引:更新记录到change buffer(没设计IO操作)
• 唯一索引:从磁盘中获取到数据页,判断是否重复,插入这个值到磁盘中(涉及两次IO操作)

<font color="red">唯一索引不能使用change buffer</font>(因为需要校验是否会产生重复的数据,所以需要将磁盘中的数据查询到缓存中,这个操作就已经违背了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的前三个字符生成一个非聚集索引

但是使用前缀索引,会导致查询的时候比正常索引多执行一步操作
获取到索引值之后需要比对是否是整体结果信息

<font color="red">使用前缀索引优点:可以减少索引在内存中占用的空间,以前是索引整个tel,现在只用索引前三个字符即可.缺点是:进行一些查询操作的时候,多了查询的操作行为,增加了查询的消耗</font>

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是有一个内存命中率的概念(命中率越高,说明使用的操作越高效)

觉得不错点个赞呗,不然吐个槽呗