MySQL索引的概念与好处

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 本文介绍了MySQL存储引擎及其索引类型,重点对比了MyISAM与InnoDB引擎的不同之处。文中详细解释了InnoDB引擎的自适应Hash索引及聚簇索引的特点,并阐述了索引的重要性及使用原因,包括提升数据检索速度、实现数据唯一性等。最后,文章还讨论了主键索引的选择与页分裂问题,并提供了使用自增字段作为主键的建议。

初识引擎

在讲述索引之前,我们需要认识MySQL的存储引擎。目前,MySQL的存储引擎共有MyISAM 、InnoDB、Memory三种,其中,InnoDB在MySQL5.5后成为默认引擎,也就是说,我们后面所讲述的引擎都是基于InnoDB引擎的。三者所支持的索引类型有所不同,但都实现了B+树索引

索引类型 InnoDB引擎 MyISAM引擎 Memory引擎
B+ 树 支持 支持 支持
Hash索引 不支持 不支持 支持
FullText索引 支持 支持 不支持

可以看到MyISAM引擎与InnoDB引擎有很大相似处,但是两者所实现的方式还是有所不同的。

MyISAM引擎与InnoDB引擎在实现上的不同

  1. Hash索引上,虽然两者都不支持Hash索引,但是InnoDB引擎支持的Hash索引是自适应的,不支持人工生成Hash索引,但是会根据表的使用情况自动为表生成哈希索引,使InnoDB能够在具有适当的工作负载和足够的缓冲池内存的系统上执行更像内存中的数据库,而不牺牲事务特性或可靠性,从而提升效能。而MyISAM则不支持 2.在MyISAM中,B+Tree叶节点的data域存放的是数据记录的地址,被称为“非聚簇索引”;而InnoDB引擎中,树的节点data域保存了完整的数据记录,而其余的索引的data域则存储相应记录主键的值,被称为“聚簇索引”。

了解了存储引擎后,下面我们继续讲索引

索引的概念

索引可以形象为一本书的目录,通过这个目录我们可以较快的找到目标数据的位置,避免全表扫描。显而易见的,索引能快速定位并找到目标数据的位置。但是,假如我们更新了某条数据,那么索引也会随之改变,从而带来性能上的影响,所以,索引能有效提升数据检索,但也会占用内存并消耗性能。

为什么要使用索引?

索引虽然带来内存的消耗,但也有诸多好处

  1. InnoDB引擎会根据优先级选择某个唯一性索引为聚簇索引的索引键,实现每一行数据的唯一性。
  2. 索引按自己编排的顺序,能够将随机IO变为顺序IO,从而提升性能
  3. 加速表和表之间的连接,实现数据的完整性
  4. 明显加快数据的检索速度,适当的建立索引能有效避免回表

聚簇索引的索引键如何选择

在创建表时,InnoDB 存储引擎会根据以下优先级选择

  1. 首先会使用主键作为聚簇索引的索引键(key)
  2. 如果没有主键,则会选择第一个不包含 NULL 值的唯一列
  3. 在上述条件都不满足的情况下,InnoDB 将自动生成一个隐式自增 id 列 所以,这也就是我们为什么必须在建表时指定主键索引的原因

为什么主键索引这么重要

首先,MySQL使用B+Tree树作为索引的数据结构,为什么选择B+Tree作为索引的数据结构,我们将在下期展开叙述。数据在B+tree上的存储大致为,数据全存储在叶子节点上,同时,在大量数据面前,数据也大概分为3-4层,大致与下面结构相似。

假如我们查询主键id为6的数据时,大概会经历下面3个阶段

  1. 在在一层中 6介于1到10之间,找到下一层数据为 1,4,7
  2. 在第二层中 6介于4到7之间, 找到下一层为 4到7
  3. 在第三层中 找到主键为6的行数据

经过3次读取节点,最终找到主键值为6的行数据。从中我们可以看出,同一个叶子节点内的各个数据是按主键顺序存放的,因此,每当有一条新的数据插入时,数据库会根据主键将其插入到对应的叶子节点中。 所以,主键索引应保持其自增性,这样就可以顺序插入新的值。如果主键不是顺序的,那么可能会插入到现有数据的中间,从而导致后面的数据也不得不重新移动,如果当前页是满的,就会发生页分裂

页分裂

在已满的数据页中(数据页的内存一般为16k),如果根据主键id要在中间插入一条新的行数据,此时数据页已经满了,于是,会选择位置将数据页分为两页,并将位置之后的数据转移到新的数据页中。而旧的数据页会产生随之而来的存储空间浪费。于是,最好用自增字段做主键。

小结

本期主要讲述了索引的概念以及优缺点,同时也讲述了在InnoDB引擎下聚簇索引的索引键如何选择,下期将讲述索引的分类以及使用,并且在索引建立上的建议。


转载来源:https://juejin.cn/post/7291546618683637819

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
128
分享
相关文章
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
360 80
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
166 22
MySQL底层概述—8.JOIN排序索引优化
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
146 18
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
145 11
MySQL底层概述—6.索引原理
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
173 22
 MySQL秘籍之索引与查询优化实战指南
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
263 10
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等