MySQL索引特性

niu_sama

于 2024-09-20 22:26:13 发布

阅读量1.2k

点赞数 10

分类专栏： MySQL 文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/weixin_74461263/article/details/142403125

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章目录

MySQL索引特性

MySQL索引特性

索引的概念

数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是 O(N)。
索引的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据。
索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护。

在这里插入图片描述

索引的价值

使用如下SQL创建一个海量数据表：

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

上述SQL中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表当中插入了八百万条记录。

SQL执行完毕后查看数据库就能看到一个名为index_demon的数据库。如下：

在这里插入图片描述
进入该数据库，在数据库中可以看到一个名为EMP的员工表。如下：

在这里插入图片描述
由于EMP表中有八百万条记录，因此在查看EMP表中的数据时可以带上limit子句。如下：

通过desc命令可以看到，目前EMP员工表中没有建立任何索引。如下：

在这里插入图片描述
查询EMP表中指定工号的员工信息，如下：

当我们给员工表中的工号建立索引后，数据库底层就会为员工表中的数据记录构建特定的数据结构，需要注意的是，由于当前员工表中的数据量较大，因此建立索引时也需要花费较长时间。如下：

在这里插入图片描述
这时再查询EMP表中指定工号的员工信息，可以看到几乎检测不到查询时耗费的时间。如下：

在这里插入图片描述
根本原因就是，给员工工号创建索引后再根据员工工号来查询数据，这时就能够直接通过底层建立的数据结构来快速定位到目标数据，从而提高数据的检索速度，这就是索引的价值。

认识磁盘

MySQL给用户提供存储服务，存储的数据在磁盘这个外设当中。
磁盘是计算机中的一个机械设备，相比于计算机的其他电子元件，磁盘的效率是比较低的。
而如何提高效率是MySQL的一个重要话题，因此我们有必要了解一下磁盘的相关内容。

磁盘的结构

磁盘的整体结构如下：

在这里插入图片描述
部分说明：

永磁铁：机械硬盘的存储方式与磁带比较类似，磁体具有记忆的功能，永磁铁是为了保证磁性的稳定。
音圈马达：硬盘读取数据的关键部位，主要作用是将存储在磁盘上的信息转换为电信号向外传输。
主轴：保证电机稳定的转动，磁盘转动才能读出数据。
空气滤波片：过滤空气硬盘透气孔中进入的空气，保证硬盘内部清洁，同时还可以防止硬盘内部的零件氧化，确保硬盘安全使用。
磁盘：硬盘一般都是铝合金制作的，主要是用来存储文件的。
磁头：用来读取盘片上的信息。
串行接口：用来连接电脑与硬盘的接口，起到传输的作用。

磁盘中的一个盘片

在这里插入图片描述

部分说明：

磁道：磁盘表面被分为许多同心圆，每个同心圆称为一个磁道，每个磁道都有一个编号，最外面的是0磁道。
扇区：每个磁道被划分成若干个扇区，每个扇区的存储容量为512字节，每个扇区都有一个编号。

说明：

由于每个扇区的存储容量相同，因此最内侧磁道上的扇区数据密度最大，而最外侧磁道上的扇区数据密度最小。
近三十年来，扇区大小一直是512字节，但最近几年正在迁移到更大、更高效的4096字节扇区，通常称为4K扇区。
数据库文件就是保存在磁盘中的一个个扇区中的，因此找到一个文件本质就是，在磁盘上找到保存该文件的所有扇区。

扇区的定位方式

一个磁盘由多个盘片叠加而成，每个盘片有两个盘面，所有盘面中半径相同的同心磁道构成一个柱面。
每个盘面都有一个对应的磁头，每个磁头都有一个编号，所有的磁头都是连在同一个磁臂上的。

在这里插入图片描述
定位扇区时采用CHS寻址方式：

磁头（Heads）：每个盘面都有一个对应的磁头，因此确定了磁头也就确定了数据在哪一个盘面。
柱面（Cylinder）：所有盘面中半径相同的同心磁道构成柱面，因此在确定了数据在哪一个盘面的基础上，再确定柱面也就确定了数据在该盘面上的哪一个磁道。
扇区（Sector）：每个磁道被划分成若干个扇区，因此在确定了数据在哪一个磁道的基础上，再确定扇区也就确定了数据在该磁道上的哪个扇区。

简单来说，CHS寻址方式就是先通过H确定数据所在的盘面，再通过C确定数据所在的磁道，最后通过S定位到目标扇区。

说明：

CHS寻址方式是磁盘定位扇区的方式，但实际CHS寻址方式对磁盘以外的设备来说没什么作用，因此系统软件在定位磁盘上的数据时采用的是LBA（Logical Block Address，逻辑区块地址）。
LBA是描述计算机存储设备上数据所在区块的通用机制，LBA和CHS之间可以通过计算公式进行相互转换，LBA存在的意义就是对底层逻辑器件进行虚拟化，让系统软件可以不用关心底层硬件具体的寻址方式，而实际底层硬件采用的还是CHS寻址方式。

操作系统与磁盘交互的基本单位

操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：

物理内存实际是被划分成一个个4KB大小的页框的，磁盘上的数据也会被划分成一个个4KB大小的页帧，因此操作系统与磁盘以4KB为单位进行IO交互，就能提高数据加载和保存的效率。
操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。
此外，以扇区的大小作为IO的基本单位太小了，这就意味着读取同样的数据内容，需要进行更多次的磁盘访问，而磁盘的效率是比较低的，这样IO效率就降低了。

因此操作系统与磁盘以4KB作为IO交互的基本单位，一方面是为了提高IO效率，另一方面是为了实现硬件和系统的解耦。

磁盘的随机访问（Random Access）与连续访问（Sequential Access）

随机访问：本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO。
连续访问：本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

需要注意的是，尽管两次IO是在同一时刻发出的，但如果它们请求的扇区地址相差很大，那也只能称为随机访问，因为连续访问中的连续指的是访问的扇区地址的连续，而不是访问时间的连续，由于连续访问不需要过多的定位，因此效率比较高。

MySQL与磁盘交互的基本单位

MySQL作为一款应用软件，可以想象成是一种特殊的文件系统，它有着更高频的IO场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page。

通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB。如下：
在这里插入图片描述

索引的理解

观察主键索引现象

创建一个用户表，表当中包含用户的id、年龄和姓名，并将用户的id设置成主键。如下：

在这里插入图片描述
创建表完毕后向表中插入一些数据，并且插入数据时没有按照主键的大小顺序插入。如下：

但最终当我们查看表中的数据时，却发现显示出来的数据是按照主键进行有序排列的。如下：

根本原因就是，因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL底层也会自动按照主键对插入的数据进行排序。

为什么MySQL与磁盘交互的基本单位是Page

MySQL与磁盘进行交互时为什么不是按需交互，而是以Page为基本单位进行交互的？

当我们查询表中的某一条记录时，如果MySQL只从磁盘中将这一条记录加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL就一定需要再次与磁盘进行IO交互。
而如果当我们查询表中的某一条记录时，MySQL直接将这条记录所在的整个Page都加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL很可能就不再需要与磁盘进行IO交互了，因为这条记录很可能也在被加载进来的Page当中，这时直接在内存中进行查询即可，大大减少了IO的次数。
当然，我们不能保证用户下一次要访问的数据一定就在本次加载进来的Page当中，但是根据统计学原理，当一个数据正在被访问时，那么下一次有很大可能会访问其周围的数据（局部性原理），因此我们有较大概率保证用户下一次要访问的数据和本次访问的数据在同一个Page当中，如果局部性原理没有起作用，那就再把对应的Page加载到内存当中即可。

也就是说，MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率。

推导主键索引结构的构建

单个Page

MySQL中要管理很多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来。
MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。
此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

假设上述测试表中的记录都在同一个Page当中，那么该Page的结构大致如下：

在这里插入图片描述
说明：

每个Page结构体内部的数据会按照主键进行排序，目的是为了优化数据查询的效率，因为单链表在查找的时候是顺序查找的，有序就意味着在查找的过程中有机会提前结束查询过程。
这也就是前面所说的，只要设置了主键，即便向表中插入的数据是乱序的，MySQL底层也会自动按照主键对插入的数据进行排序，因此查询得到的数据是按照主键进行有序排序的。

多个Page

通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

在这里插入图片描述

Page之上创建页目录

虽然在单个Page内部能够通过页内目录来快速定位数据，但在遍历Page双链表寻找目标Page时本质进行的还是线性遍历。
这时可以给各个Page结构体也建立页目录，页目录中的每个目录项都指向一个Page，而这个目录项存放的就是其指向的Page中存放的最小数据的键值。
在给各个Page结构体建立页目录后，在查询数据时就可以先通过遍历页目录找到目标数据所在的Page，然后再在该Page内部找到目标数据。

在这里插入图片描述

说明：

这里的页目录与之前的页内目录的区别在于，页目录管理的是一个个的Page，而页内目录管理的是一条条的记录。此外，页内目录与其管理的多条记录是保存在同一个Page中的，而页目录是重新申请的一个Page结构体来保存的。
随着数据量不断增大，Page变得越来越多，这时一个页目录无法管理所有的Page，这时就需要更多个的页目录。这些页目录也是一个个的Page结构体，只不过这些Page结构体中存放的不是数据记录，而是各个Page的目录信息。但是在MySQL看来，无论Page当中存储的是什么数据，都应该被管理起来，因此这些Page页目录也需要用双链表连接起来。

页目录之上再创建页目录

就算给各个Page结构体也建立了页目录，但随着数据量不断增大，页目录的数量也会越来越多，这时在遍历页目录寻找目标Page时本质进行的还是线性遍历。
类似的，我们可以不断在页目录之上再创建页目录，最终就一定能够得到一个入口页目录，这时在查询数据时就可以从入口页目录开始不断查询页目录，最终找到目标数据所在的Page，然后再在该Page内部找到目标数据。

在这里插入图片描述
说明：

最终构建出来的实际就是一棵B+树，这棵B+树就是InnoDB的索引结构，其中每一层Page的作用就是加速它的下一层的查找效率。
如果我们创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
MySQL中可能同时有大量的表正在被处理，因此Buffer Pool中可能会存在多个索引结构，也就是同时存在多个B+树结构，当我们查询表时访问的就是这张表对应的B+树结构。