MySQL 索引的10 个核心要点

这篇具有很好参考价值的文章主要介绍了MySQL 索引的10 个核心要点。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。



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 索引的10 个核心要点文章来源地址https://www.toymoban.com/news/detail-478320.html

到了这里,关于MySQL 索引的10 个核心要点的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【Jetpack】ViewModel + LiveData + DataBinding 综合使用 ( 核心要点说明 | 组合方式 | 代码示例 )

    ViewModel 架构组件 是 视图 View 与 数据模型 Model 之间 数据交互的 桥梁 ; 传统 Android 开发中 , 视图 View 与 数据模型 Model 都在 Activity 中维护 , 导致 二者有很高的耦合度 , 不利于代码维护 ; 引入了 ViewModel 架构组件后 , 视图 View 与 数据模型 Model 之间实现了解耦 , 同时也能 保证二

    2024年02月01日
    浏览(45)
  • 【Jetpack】Room + ViewModel + LiveData 综合使用 ( 核心要点说明 | 组合方式 | 代码示例 )

    在上一篇博客 【Jetpack】使用 Room 框架访问 Android 平台 SQLite 数据库 ( 导入依赖 | 定义 Entity 实体类 | 定义 Dao 数据库访问对象接口 | 定义数据库实例类 ) 中 , 实现了 使用 Room 框架访问 Android 中的 SQLite 数据库的操作 , 每当数据库中的数据发生变化时 , 就需要开启线程 , 重新获

    2024年02月06日
    浏览(44)
  • 分布式可视化作业调度平台 DolphinScheduler MasterServer 设计核心要点揭秘

    点击蓝字 关注我们 作者 | 欧阳业伟 01 导读 Apache DolphinScheduler 是一个分布式易扩展的可视化 DAG 工作流任务调度平台,致力于解决数据处理流程中错综复杂的依赖关系,使得调度系统在数据处理流程中开箱即用。自 2019 年开源以来,得益于其自身的稳定性、易用性、扩展性和

    2024年02月14日
    浏览(36)
  • 微服务网关:Spring Cloud Zuul 升级 Spring Cloud Gateway 的核心要点

    在routes路由规则中,根据path去匹配,如果匹配中,就使用对应的路由规则进行请求转发 如果无法从routes中匹配,则根据path用“/”去截取第一段作为服务名进行请求转发,转发时,默认将第一段截取调 如果截取的服务名不在注册中心中存在服务,则报错404 在routes路由规则中

    2024年02月07日
    浏览(64)
  • 第87讲:XtraBackup备份工具的核心技术要点及全库备份、恢复案例

    XtraBackup是Percona公司开源的一款MySQL InnoDB(包括XtraDB,MyISAM)数据库备份工具,基于InnoDB的崩溃恢复功能,由于支持不锁表的在线物理热备而被广泛应用于生产环境。 XBK备份恢复原理: XtraBackup备份执行的瞬间,会立即触发checkpoint机制,将已提交事务的脏页数据从内存刷新到

    2024年02月02日
    浏览(39)
  • 10、PHP语法要点3(foreach、trait)

    1、foreach 循环用于遍历数组,在vscode中输入foreach时默认的提示是这样的。 提示中除了给了$value之外,还给了$key值,key值一般在关联数组中自行定义的。但是普通数组中的key值是数组的索引,所以vscode这样提示也是没错的。 输出结果可以看出,key值输出的是索引值。  2、P

    2024年02月16日
    浏览(38)
  • Hudi的核心概念 —— 索引(Index)

    Hudi 通过索引机制提供高效的 upserts,具体是将给定的 hoodie key(record key(记录键) + partition path)与文件 id(文件组)建立唯一映射。这种映射关系,数据第一次写入文件后保持不变, 所以,一个 FileGroup 包含了一批 record 的所有版本记录。Index 用于区分消息是 INSERT 还是 UPDAT

    2024年02月14日
    浏览(37)
  • (02)Cartographer源码无死角解析-(80) 核心要点→local坐标系、子图坐标系、切片坐标系、地图坐标系等相转换与联系

    讲解关于slam一系列文章汇总链接:史上最全slam从零开始,针对于本栏目讲解(02)Cartographer源码无死角解析-链接如下: (02)Cartographer源码无死角解析- (00)目录_最新无死角讲解:https://blog.csdn.net/weixin_43013761/article/details/127350885   文末正下方中心提供了本人 联系方式, 点击本人照片

    2024年02月16日
    浏览(44)
  • 【ELK02】ES的重要核心概念和索引常用操作-索引文档管理、文档搜索

    1.1索引(index) 类似于关系型数据中的库-database ,一个es的集群中可以有多个索引,每个索引都是一批独立的存储数据,按照一定的数据结构保存,方便查询. 1.2类型(type) 类似于关系型数据库中的表格-table ,一个索引中可以有多个类型,每个类型中的数据结构是一致的. 注意:6.x中使用类

    2024年02月04日
    浏览(42)
  • 【ES】Elasticsearch核心基础概念:文档与索引

    es的核心概念主要是:index(索引)、Document(文档)、Clusters(集群)、Node(节点)与实例,下面我们先来了解一下Document与Index。 在讲解Document与Index概念之前,我们先来了解一下RESTful APIs,因为下面讲解Document和Index的时候会使用到。 当我们把es服务器启动起来之后,要怎么调用呢?

    2024年02月05日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包