【MySQL】索引
目录
🌈前言🌈
本期【MySQL】讲解MySQL中索引的概念,什么是索引,为什么要有索引,以及MySQL如何做到和磁盘交互,使得数据持久化,还将会展示MySQL索引的相关操作。
如果没有额外说明,默认采用的存储引擎是InnoDB。
📁 索引
在MySQL中,索引是一种特殊的数据结构,用来提高数据库查询的效率。
例如一本书,如果我们没有目录结构,那么我们想要确切的查询到某一个,是不是要线性遍历才能找到,效率非常低下,因此每一本书都有目录。在数据库中,数据都是保存在磁盘上的,因此想要查询某个数据是要进行IO的,如果我们使用线性遍历,那么IO次数会非常高,效率低下。索引就可以起到减少IO次数,提高效率的作用。
因此,索引就是数据库的一种数据结构,用来加速数据库检索操作,类似书籍目录。
索引的类型:
- 主键索引:唯一标识每一行数据,且不允许有空值。
- 唯一索引:确保索引列的值唯一,但允许空值。
- 普通索引:没有唯一性限制,可以包含重复值。
- 全文索引:主要用于文本搜索,支持对文本字段的高效查找。
- 组合索引:由多个列组成的索引,能够加速基于这些列的查询
📁 磁盘
下面我们会简单介绍一下磁盘的概念,来引入MySQL如何与磁盘交互的概念。
本文只是简单介绍了磁盘,在下面这篇文章中,详细介绍了磁盘以及文件系统的概念。
数据库文件,本质就是保存在磁盘盘片当中,也就是扇区中。因此我们查找文件,就是查找到具体某个扇区。
- 柱面(Cylinder):硬盘上的同一轨道在多个磁头上的垂直堆叠形成的圆柱形区域。每个柱面包含多个扇区。
- 磁头(Head):用于读取或写入数据的机械部件。每个硬盘有多个磁头,通常对应于每个盘片的两面。
- 扇区(Sector):硬盘上最小的数据存储单位,通常为512字节。
我们只需要知道,磁头(Heads)、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号,就可以定位要访问的扇区,这种磁盘数据定位方法叫做CHS。实际中我们通常使用LBA(逻辑区块地址法),它是一种线性地址,可以理解为虚拟地址和物理地址的区别。系统最终还会降LBA转换为CHS地址。
磁盘的基本单位是512B。但是对于OS来说太小了,并且OS不能与磁盘具体的数值强相关,这会造成OS依赖于硬件,因此OS读取磁盘的基本单位是4KB。
📁 Page
以上我们了解了磁盘,以及知道了磁盘的基本单位是512B,OS操作硬盘的基本单位是4KB。
MySQL作为一款软件,可以想象成一种特殊的文件系统*,有着更高的IO场景,为了提高基本IO的效率,MySQL进行IO的基本单位是16KB,这个基本数据单元,在MySQL中称为pape。
MySQL中的数据文件,是以page为单位保存在磁盘中的。MySQL通过page将数据保存在磁盘的过程中肯定设计计算,因此需要CPU的参与,这个过程CPU一定不能和磁盘进行直接交互(效率太低),因此MySQL会预先开辟好一段内存空间,进行缓存,之后通过缓存和磁盘数据进行IO。
1. 缓冲池(Buffer Pool)
使用缓冲池来缓存数据页面,以减少磁盘的直接访问,提高性能。
当数据被插入,更新或删除时,相关的页面首先在内存中的缓冲池进行修改。
2. 脏页(Dirty Pages)
当缓冲池的页面被修改后,这些页面称为脏页。脏页不会立即写入磁盘,而是保留在内存中,等待合适时机写入。
3. 日志(Redo Log)
将脏页写入磁盘之前,InnoDB还会将更改记录到重做日志中,以便日后系统崩溃后可以修复数据。
4. 写入策略
InnoDB使用“写回”策略,即在特定条件下将脏页写入磁盘:
- 周期性检查:数据库系统会定期检查脏页的数量,并将它们写入磁盘。
- 内存压力:当缓冲池中的内存空间不足时,系统会选择一些脏页写入磁盘,以释放空间。
- 事务提交:当一个事务提交时,相关的脏页会被写入磁盘,以确保持久性。
我们需要将page管理起来,先描述,在组织的原则,我们可以将page理解为一个结构体对象,用双向链接表管理起来。
我们有了一个16KB的page,将数据保存在page中,如果我们要在单个page中查找,如何查找呢?
我们可以看到目前只能通过线性遍历的方法,效率还是太低了。因此,在单个page中我们引入了页目录的概念。
以上是在单个page中进行查找,如果我们有多个page呢?其实方法和页目录差不多。
我们可以创建一个新的page,这个page不用来存储数据,而是用来充当目录的作用。这样我们就可以通过几次的IO,找到具体的数据。大大提高了效率。
使用一个目录项来指向某一页,而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
和页内目录不同的地方在于,这种目录管理的级别是页,而页内目录管理的级别是行。
其中,每个目录项的构成是:键值+指针。图中没有画全。
目录页的本质也是页,普通页中存的数据是用户数据,而目录页中存的数据是普通页的地址。我们每次检索数据的时候,该从哪里开始呢?虽然顶层的目录页少了,但是还要遍历啊?不用担心,可以在加目录页。
因此,最终得到的结果就是下图所示:
上述,我们就可以简单理了一下什么是B+树,即InnoDB的索引结构。
📁 索引结构
在 MySQL 中,索引结构是保存在文件中的,
索引文件:
- 对于不同的存储引擎(如 InnoDB 和 MyISAM),索引信息会保存在不同的文件中。
- InnoDB 使用表空间文件(通常是
ibdata1
或者各个.ibd
文件)来存储数据和索引。- MyISAM 存储引擎则将数据和索引分开存储,通常为
.MYD
(数据文件)和.MYI
(索引文件)。启动时的行为:
- 当 MySQL 启动时,它会读取这些文件,恢复到最后一次关闭时的状态,包括索引结构。
- 如果没有任何更改(如没有执行
ALTER TABLE
操作等),索引在重启后依旧有效。更新索引:
- 索引会随着对表数据的增加、删除或修改而自动更新。因此,任何对数据的更改都会影响相应的索引,但不需要每次启动时重新创建。
📂 B树 和 B+树
在了解B树之前,我们先来回顾一下数据结构,链表,二叉树搜索,哈希表,为什么都不行,而是采用了B树系列(本质是一个多叉树)。
链表的遍历就是线性遍历;二叉树搜索树,存在退化为线性遍历的可能;AVL树,红黑树,终究是一个二叉树,层数过高的时候,效率太低;MySQL 是支持HASH的,不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征,决定了虽然有时候也很快(O(1)),但在面对范围查找就明显不行。
B树:
B+树:
本质是B树的变形,非叶子节点不在存储数据,只有叶子节点存储数据,这样一个节点就可以存储更多的key。可以使得树更矮,所以IO操作次数更少。叶子节点相连,更便于进行范围查找。
📂 聚簇索引 和 非聚簇索引
MyISAM存储疫情同样使用B+树来索引结果,但是叶子节点data域不存数据本身而是数据记录的地址。
MyISAM 最大的特点是,将索引Page和数据Page分离,也就是叶子节点没有数据,只有对应数据的地址。 相较于 InnoDB 索引, InnoDB 是将索引和数据放在一起的。
MyISAM 这种用户数据与索引数据分离的索引方案,叫做非聚簇索引;InnoDB 这种用户数据与索引数据在一起索引方案,叫做聚簇索引。
📂 回表查询
MySQL 除了默认会建立主键索引外,我们用户也有可能建立按照其他列信息建立的索引,一般这种索引可以叫做辅助(普通)索引。
但是在InnoDB引擎下,非主键索引中叶子结点并没有数据,而是对应记录的key值(主键)
因此需要两遍索引:首先通过辅助索引获得主键,在通过主键在主键索引中获得记录,这个过程就叫做回表查询。
在 InnoDB 中,如果表没有主键,仍然可以创建辅助索引,但会有一些影响和注意事项:
- 如果没有定义主键,InnoDB 会自动为每个表创建一个隐藏的隐式主键,通常是一个 6 字节的内部行 ID。这意味着即使没有显式的主键,你仍然可以创建辅助索引。
- 没有主键的表可能会导致性能问题,因为每次通过辅助索引查找数据时,都需要通过隐式主键进行回表查找。
- 如果表数据很大,这种额外的查找可能会增加查询的开销。
📁 索引操作
索引创建原则:
1. 比较频繁作为查询条件的字段应该创建索引
2 .唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
3. 更新非常频繁的字段不适合作创建索引
4. 不会出现在where子句中的字段不该创建索引
📂 主键索引
创建主键索引:
1.
create table user1(id int primary key, name varchar(30));
2.
create table user2(id int, name varchar(30), primary key(id));
3.
create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);
主键索引特点:
1. 一个表中,最多有一个主键索引,当然可以使符合主键
2. 主键索引的效率高(主键不可重复)
3. 创建主键索引的列,它的值不能为null,且不能重复
4. 主键索引的列基本上是int
📂 唯一键索引
创建唯一键索引:
1.
create table user4(id int primary key, name varchar(30) unique);
2.
create table user5(id int primary key, name varchar(30), unique(name));
3.
create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);
唯一键索引特点:
1. 一个表中可以有多个唯一键索引
2. 查找效率高
3. 如果在某一列建立唯一键索引,必须保证这个列不能有重复项
4. 如果一个唯一键索引上指定not null,等价于主键索引。
📂 普通索引
创建普通索引:
1.
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后,指定某列为索引
);
2.
create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引
3.
create table user10(id int primary key, name varchar(20), email
varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);
普通索引特点:
1. 一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
2. 如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引
📂 查询索引
1.
show keys from 表名;
2.
show index from 表名;
3.
desc 表名;
📂 删除索引
1. 删除主键索引
alter table 表名 drop primary key;
2. 其他索引的删除
alter table 表名 drop index 索引名;
3.
drop index 索引名 on 表名
📁 总结
以上就是本期【MySQL】索引的全部内容,讲解了索引概念,MySQL与磁盘的交互,以及B+树作为MySQL的存储结构,它的实现和优势,此外展示了索引操作的相关代码。
如果感觉本期内容对你有帮助,欢迎点赞,关注,收藏Thanks♪(・ω・)ノ
原文地址:https://blog.csdn.net/jupangMZ/article/details/142723162
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!