0


MySQL 索引的10 个核心要点

文章目录

在这里插入图片描述

🍉1. 索引底层采用什么数据结构?为什么不用hash

索引底层数据结构是B+树

不使用hash:因为其底层是哈希表实现,等值查询,可以快速定位,一般情况效率很高,不稳定,当出现大量键重复哈希冲突,效率下降,不支持范围查询,无法用于排序分组,无法模糊查询,多列索引的最左前缀匹配原则,总要回表操作等。

🍉2. B树与B+树区别?为何用B+树?

B+树:非叶子结点不存data,只存key,查询更稳定,增大了广度(B+树出度更大,树高矮,节点小,磁盘IO次数少);叶子结点下一级指针(范围查询);索引冗余。

与红黑树相比

更少查询次数:B+树出度更大,树高更低,查询次数更少
磁盘预读原理:为了减少IO操作,往往不严格按需读取,而是预读。B+树叶子结点存储相临,读取会快一些。

存储更多索引结点:B+树只在叶子结点储存数据,非叶子结点存索引,而一个结点就是磁盘一个内存页,内存页大小固定,那么相比B树这些可以·存更多的索引结点,出度更大,树高矮,查询次数少,磁盘IO少。

🍉3. 自增主键理解?

自增主键:InnoDB引擎的自增值,其实是保存在了内存里,并且到了MySQL 8.0版本后,才有了“自增值持久化”的能力。

也就是才实现了“如果发生重启,表的自增值可以恢复为MySQL重启前的值”,具体情况是:(查看表结构,会看到自增主键=多少)

● 在MySQL 5.7及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id),然后将max(id)+1作为这个表当前的自增值。

● 举例来说:如果一个表当前数据行里最大的id是10,AUTO_INCREMENT=11。这时候,我们删除id=10的行,AUTO_INCREMENT还是11。但如果马上重启实例,重启后这个表的AUTO_INCREMENT就会变成10。也就是说,MySQL重启可能会修改一个表的AUTO_INCREMENT的值。

● 在MySQL 8.0版本,将自增值的变更记录在了redo log中,重启的时候依靠redo log恢复重启之前的值。

自增值修改机制:

  1. 如果插入数据时id字段指定为0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT值填到自增字段;
  2. 如果插入数据时id字段指定了具体的值,就直接使用语句里指定的值。

自增值新增机制:

  1. 如果准备插入的值>=当前自增值,新的自增值就是“准备插入的值+1”;
  2. 否则,自增值不变。

🍉4. 为什么自增主键不连续

● 在MySQL 5.7及之前的版本,自增值保存在内存里,并没有持久化

● 事务回滚(自增值不能回退,因为并发插入数据时,回退自增ID可能造成主键冲突)

● 唯一键冲突(由于表的自增值已变,但是主键发生冲突没插进去,下一次插入主键=现在变了的子增值+1,所以不连续)

假设,表t里面已经有了(1,1,1)这条记录,这时我再执行一条插入数据命令:

insert into t values(null, 1, 1)

; (自增id,唯一键c,普通字段d)

这个语句的执行流程就是:

1. 执行器调用InnoDB引擎接口写入一行,传入的这一行的值是(0,1,1);
2. InnoDB发现用户没有指定自增id的值,获取表t当前的自增值2;
3. 将传入的行的值改成(2,1,1);
4. 将表的自增值改成3;
5. 继续执行插入数据操作,由于已经存在c=1的记录,所以报Duplicate key error,语句返回。
这个表的自增值改成3,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为碰到唯一键c冲突,所以id=2这一行并没有插入成功,但也没有将自增值再改回去。

所以,在这之后,再插入新的数据行时,拿到的自增id就是3。也就是说,出现了自增主键不连续的情况。

🍉5. Innodb为什么推荐用自增ID

①主键页就会近乎于顺序的记录填满,提升了页面的最大填充率,不会有页的浪费。

②新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快,不会为计算新行的位置而做出额外的消耗。

③减少了页分裂和碎片的产生

UUID:大量的随机IO+页分裂导致移动大量的数据+数据会有碎片。

总结:自增ID有序,会按顺序往最后插入,而UUID无序,随机生成,随机插入,会造成频繁页分裂,内存碎片化,大量随机IO

🍉6. 索引有哪些类型

● 排好序的数据结构,可以帮助快速查找数据

● 优缺点:索引可以提高查询速度,查询使用优化隐藏器提高性能,但是也会占据物理空间,降低增删改的速度,因为还要操作索引文件

类型:

覆盖索引+回表+索引下推+联合索引

普通索引:可以重复

唯一索引:唯一,可为空,表中只有一个主键索引,可多个唯一索引

主键索引

  • 唯一,不为空,叶子结点存出了行记录数据,主键索引也称聚簇索引,对应非主键索引的叶子结点存的主键的值(二级索引),用二级索引查需要回表操作(根据二级索引查到主键,再根据主键去主键索引查)
  • 一般推荐用自增主键,保证空间利用率,减少页分裂

全文索引

覆盖索引:索引字段覆盖了查询语句涉及的字段,直接通过索引文件就可以返回查询所需的数据,不必通过回表操作。

回表:通过索引找到主键,再根据主键id去主键索引查。

索引下推

○ 在根据索引查询过程中就根据查询条件过滤掉一些记录,减少最后的回表操作

假如执行

select * from stu where name=? and age=?

没有索引下推先再存储引擎根据name筛选数据返回给server层,然后server层再根据age过滤
有索引下推直接根据name和age在存储引擎层就筛选得到结果

🍉7. InnoDB与MyISAM的区别?

7.1 MyISAM与InnoDB区别

● InnoDB聚簇索引,MyISAM非聚簇索引

● InnoDB数据与索引一起保存.ibd,MyISAM表结构.frm 索引.myi 数据.myd

● InnoDB支持事务、外键、行锁表锁,MyISAM不支持事务、外键、只支持表锁

● select count(*)

● MyISAM查询更优,InnoDB更新更优

● 都是B+tree索引

● MyISAM支持全文索引,InnoDB5.6后支持

7.2 MyISAM

● 不支持事务,但是每次查询都是原子的

● 支持表级锁,每次操作对整个表加锁

● 存储表的总行数

● 一个MyISAM表有三个文件:表结构.frm 索引.myi 数据 .myd

● 采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引不用保证唯一性。

7.3 Innodb

● 支持ACID事务,支持四种隔离级别

● 支持行级锁及外键约束,因此支持写并发

● 不存储总行

● 主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。

7.4 使用场景

大多数时候我们使用的都是 InnoDB 存储引擎,在某些读密集的情况下,使用 MyISAM 也是合适的。不过,前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点(可是~我们一般都会介意啊!)。

● MyISAM适合读多更新少的:MyISAM索引跟数据分开放,因此有读取更快的说法。

● InnoDB适合插入更新频繁的:索引与数据一起放,建立索引更复杂,使用行锁,更新频繁效率更高

● 需要事务,高并发场景用Innodb:Innodb支持事务,采用行锁

● MyISAM查询比InnoDB快,更新InnoDB快

场景:MyISAM查询更优,InnoDB更新更优

🍉8. 索引设计原则(查询快,占用空间少)

● 出现在where子句或则连接子句中的列

● 基数小的表没必要

● 使用短索引,如果索引长字符串列,应该指定前缀长度

● 定义有外键的数据列一定索引

● 不要过度索引

● 更新频繁的不适合

● 区分度不高的不适合,如性别

● 尽量扩展索引,别新建索引,如(a)->(a,b)

● 字符串字段建立索引方法

○ 1、直接创建完整索引,这样可能比较占用空间;
○ 2、创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
○ 3、倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
○ 4、额外用一个字段进行索引,额外计算开销

总结:索引设计原则要求查询快,占用空间少;一般建在where条件,匹配度高的;要求基数大,区分度高,不要过大索引,尽量扩展,用联合索引,更新频繁不适合、使用短索引。

🍉9. 索引有哪些失效场景

● 以“%”开头的like语句,索引无效,后缀“%”不影响

● or语句前后没有同时使用索引

● 列类型是字符串,一定要在条件中将数据用引号引用,否则失效(隐式转换)

● 如果mysql估计使用全表扫描比索引快,则不用索引(键值少,重复数据多)

● 组合索引要遵守最左前缀原则——不使用第一列索引 失效

● 在索引字段上使用not,<>,!= (对它处理是全表扫描)

● 对索引字段进行计算操作,字段使用函数也会失效

🍉10. 普通索引和唯一索引怎样选

查询比较

○ 查询会以页为单位将数据页加载进内存,不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果,普通索引查到第一条记录往后遍历直到不满足条件,由于都在内存中,不需要磁盘读取那么大开销,带来的额外查询开销忽略不计,所以查询性能几乎一致

更新比较

○ 唯一索引由于更新时要检查唯一性,所以需要将数据页先加载进内存才能判断,此时直接操作内存,不需要操作change buffer

○ 补充:普通索引若数据再内存中直接内存中更新,否则会将更新操作先记录到channge buffer中,等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回,这样一来,再写多读少的情况下就减少了磁盘IO,若写完就马上查询,就大可不必用change buffer,不但没提高多少效率还造成维护change buffer额外消耗

○ 将change buffer的操作对应到原始数据页的操作称为merge(可以查询来时读到内存再修改数据,后台线程也会merge,数据库正常关闭也会merge)

适合场景

○ 写多读少,选用普通索引更好,可以利用change buffer进行性能优化减少磁盘IO,将更新操作记录到change bufer,等查询来了将数据读到内存再进行修改.

在这里插入图片描述

标签: mysql java 数据库

本文转载自: https://blog.csdn.net/m0_60915009/article/details/131089817
版权归原作者 我有一颗五叶草 所有, 如有侵权,请联系我们删除。

“MySQL 索引的10 个核心要点”的评论:

还没有评论