MySQL索引

Hoshino373

于 2023-04-21 16:43:10 发布

阅读量496

点赞数

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

本文链接：https://blog.csdn.net/m0_59938453/article/details/130222548

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

文章详细介绍了MySQL与磁盘交互的基础知识，包括磁盘结构、数据定位以及随机访问和连续访问的概念。重点讨论了MySQL中InnoDB存储引擎的Page单位、索引的构建，尤其是B+树的作用，如何通过B+树提高查询效率，以及主键索引、聚簇索引和非聚簇索引的区别。此外，还提到了索引操作如创建和删除，以及索引创建的原则。

摘要由CSDN通过智能技术生成

一、基本概念

索引：

提高对海量数据的检索速度。只要执行了正确的创建索引操作，查询速度就可能提高成百上千倍。
但是查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的 IO 。

示例：创建一个内有海量数据的表，看看在没有索引的情况下会有什么问题。

执行下面文件的所有 SQL 语句，会创建一个名为 test_index 的数据库，在该数据库中会创建一个名为 EMP 的员工表，表中会插入八百万条记录。

index_data.sql 文件：

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

-- 构建一个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);

在 MySQL 中使用source scott_data.sql所在路径命令，就会依次执行文件中的 SQL 语句。

查看 EMP 表的结构，发现该表没有建立任何索引。

根据员工编号来查询一个员工。可以看到每一次查询都要花费较长的时间。

创建索引。

再根据员工编号来查询一个员工。可以看到每一次查询所花费的时间很少。

可以很明显地看到，创建索引之后，能够大幅度地提高对海量数据的检索速度。

二、认识磁盘

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。
磁盘是计算机中的一个机械设备，相比于计算机的其他电子元件，磁盘效率是比较低的，再加上 IO 本身的特征。
所以，如何提高效率，是 MySQL 的一个重要话题。

1.磁盘的结构

在这里插入图片描述

从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大。
那么，所有扇区都是默认 512 字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。
不过最新的磁盘技术已经慢慢地让扇区大小不同了，但是我们现在暂时不考虑。

数据库文件，其实就是保存在磁盘的盘片当中，也就是上面的一个个小格子中，即所说的扇区。当然，数据库文件很大，也很多，所以一定需要占据多个扇区。

所以，要找到一个文件的全部，本质就是要在磁盘中找到所有保存文件的扇区。
而如果能够定位任何一个扇区，那么便能找到所有的扇区，因为查找方式是一样的。

2.定位扇区

在这里插入图片描述

多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面。
每个盘面都有一个磁头，那么磁头和盘面的对应关系便是一对一的。
所以，我们只需要知道磁头（Heads）、柱面（Cylinder）、扇区（Sector）对应的编号，即可在磁盘上定位所要访问的扇区，这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用的并不是 CHS（但硬件是），而是 LBA 。系统将 LBA 地址最后会转化成为 CHS，交给磁盘去进行数据读取。

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

如果 OS 直接使用硬件提供的数据大小进行交互，那么 OS 的 IO 代码就和硬件强相关了（耦合度高）。换言之，如果硬件发生变化，那么 OS 的 IO 代码也必须跟着变化。
以 512 字节作为 IO 的基本单位，还是太小了。而 IO 单位小，就意味着读取同样多的数据，就需要进行更多次的磁盘访问，这样会带来效率的降低。

因此，OS 与磁盘进行 IO 交互的基本单位是 4KB 。这既能提高 IO 效率，又能降低 OS 和磁盘的耦合度。

3.磁盘的随机访问与连续访问

随机访问：本次 IO 所给出的扇区地址和上次 IO 结束的扇区地址不连续，磁头在两次 IO 操作之间需要做比较大的移动动作才能开始本次 IO 。
连续访问：本次 IO 所给出的扇区地址和上次 IO 结束的扇区地址是连续的，磁头能很快地开始本次 IO 。

说明：

尽管相邻的两次 IO 操作是在同一时刻发出的，但如果它们请求的扇区地址相差很大的话，那么也只能称为随机访问，而非连续访问。
磁头是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。

三、MySQL 与磁盘交互的基本单位

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统，它有着更高频的 IO 场景。
所以，为了提高基本的 IO 效率， MySQL 进行 IO 的基本单位是 16KB 。

注：本文统一使用 InnoDB 存储引擎进行讲解。

16384 / 1024 = 16 。

也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB 引擎使用 16KB 进行 IO 交互。即 MySQL 和磁盘进行数据交互的基本单位是 16KB（在忽略掉中间的 OS，重点突出 MySQL 和磁盘之间关系的情况下）。

这个基本单位，在 MySQL 这里叫做 Page（和系统的 Page 不相同）。

四、建立共识

在这里插入图片描述

MySQL 中的数据文件，是以 Page 为单位保存在磁盘当中的。
MySQL 的 CRUD 操作，都需要通过计算，找到对应的插入位置或者找到对应要修改或者查询的数据。
而只要涉及计算，就需要 CPU 参与，而为了便于 CPU 参与，一定要能够先将数据移动到内存当中。
所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是 IO 了。而此时 IO 的基本单位就是 Page 。
为了更好的进行上面的操作，MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存（其实就是一块很大的内存空间，来和磁盘数据进行 IO 交互）。
为了提高效率，一定要尽可能地减少系统和磁盘 IO 的次数。

五、索引的理解

1.观察主键索引现象

建立一张测试表。

插入多条记录。
查看插入结果。

我们在插入数据时并没有按照主键的大小顺序进行插入，但显示出来的数据是按照主键的大小顺序有序排列的，即 MySQL 会自动按照主键的大小对插入的数据进行排序。

为何 MySQL 和磁盘进行 IO 交互的时候，要以 Page 为基本单位进行交互呢？用多少加载多少不好吗？

MySQL 在查询表中的某一条记录时，如果从磁盘中只将这一条记录加载到内存当中，那么需要 1 次 IO 。每查询一条记录，就需要 1 次 IO 。
但如果多条记录都被保存在一个 Page 中（16KB，能保存很多条记录），那么在第一次 IO 查询时，整个 Page 会被加载到 MySQL 的 Buffer Pool 中，这里完成了一次 IO 。若再继续查找其他记录，就很有可能不需要再进行 IO 了，而是直接在内存中查询，这大大减少了 IO 的次数。
虽然不能严格保证下一次查询的数据一定在这个 Page 里面，但是由于局部性原理，是有很大的概率在 Page 里面的。
往往 IO 效率低下的最主要矛盾不是 IO 单次数据量的大小，而是 IO 的次数。MySQL 与磁盘进行交互时以 Page 为基本单位，可以减少 IO 的次数，进而提高效率。

2.理解单个 Page

在 MySQL 中，在任何一个时刻，一定会有大量的 Page 存在于 MySQL 内部。
MySQL 本身要对 Page 进行管理。先描述，再组织。存在一个用于描述 Page 的结构体。

可以简单理解成一个个独立文件是由一个或者多个 Page 构成的。

不同的 Page，在 MySQL 中，都是 16KB，使用 prev 和 next 构成双向链表。

因为存在主键，所以 MySQL 会默认按照主键给数据进行排序，从上面的 Page 内数据记录可以看出，数据是有序且彼此关联的。

页内部存放数据的模块，实质上也是一个链表的结构，链表的特点是增删快，查询修改慢，所以优化查询的效率是必须的。
插入数据时对其进行排序，目的就是为了优化查询的效率。
正是因为有序，所以在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且如果运气好的话，可以提前结束查找过程。

通过上面的分析，我们知道，上面的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。

如果有大量的数据，那么一定需要多个 Page 来保存这些数据，并且多个 Page 彼此使用双链表连接起来。而且每个 Page 内部的数据也是基于链表的。那么，查找特定的一条记录，也一定是线性查找，这样效率太低了。

3.页内目录

为了提高查询效率，可以在一个 Page 内部引入目录：

在 Page 结构体内部引入页内目录，将 Page 内部存储的数据记录按照键值划分为若干个区域，页内目录就分别指向各自区域中最小键值的数据记录。

在一个 Page 内部引入了目录之后，加快了查询的速度。

比如，我们要查找 id=4 的记录，在之前必须线性遍历 4 次才能拿到结果，而现在直接通过目录2[3]，就能直接定位到要查询记录所在的区域，提高了效率。

在插入数据时，MySQL 会自动按照键值的大小对插入的数据进行排序，其实就是为了方便地引入页内目录。

4.理解多个 Page

MySQL 中每一页的大小只有 16KB ，单个 Page 大小固定，所以随着数据量的不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

在单表数据不断被插入的情况下，MySQL 会在容量不足的时候自动开辟新的 Page 来保存新的数据，然后通过指针的方式，将所有的 Page 组织起来。

注：上面的图是理想情况，因为要保证整体有序，所以新插入的数据不一定会在新 Page 上面，这里仅仅做演示。

这样，我们就可以通过遍历多个 Page，在 Page 内部通过目录来快速定位数据。可是，这样也有效率问题，在 Page 之间，也是需要进行线性遍历的（如果 Page 本身就在内存中，则需要进行线性检测；如果不在内存中，依旧需要进行大量的 IO，先把 Page 加载到内存中，再进行线性检测），这样就显得我们之前的 Page 内部的目录有点杯水车薪了。

5.页目录

解决方案，其实就是我们之前的思路，给 Page 也带上目录：

给 Page 建立页目录，页目录中的每一个目录项都指向某一页，而这个目录项存放的就是其指向的页中存放的最小数据的键值。
其中，每个目录项的构成是：键值 + 指针。（图中没有画全）
继续沿用该思路，在目录页之上再创建页目录，最终一定能够得到一个入口页目录。

其实目录页的本质也是页，普通页中存放的数据是用户数据，而目录页中存放的数据是普通页的地址。即，页目录和页内目录不同的地方在于，页目录管理的级别是页，而页内目录管理的级别是记录。

查询数据时，通过入口页目录开始，不断向下查询页目录（比较目录项中的键值），最终找到目标数据所在的页，然后再在该页内查询页内目录（比较目录项中的键值）找到目标数据。
注：实际上每个 Page 所存放的数据远不止图上所画出来的，这里只是为了演示所以才没画全。

事实上，这就是传说中的 B+ 树。至此，我们已经给我们的 user 表构建完了主键索引。

其实就是给 Page 的内部和外部都形成目录结构，构建一棵 B+ 树，提高 Page 内部和 Page 之间的查询效率！

建立索引，即构建 B+ 树数据结构，解决了线性遍历和大量 IO 的问题。

有了 B+ 树，在查询时就不需要线性遍历了。
有了 B+ 树，就实现了在查询过程中按需加载对应的 Page（不用加载太多无效的 Page），减少 IO 的次数。

复盘一下：

所有的数据最终可以在磁盘中，也可以在 MySQL 的 Buffer Pool 内存中。
所有的数据都必须以 Page 为单位进行 IO，以 Page 为单位组织。
将热点数据，以 B+ 树的形式将所有的 Page 进行组织所形成的数据结构，与其配套的查找算法，叫做索引。
Page 分为目录页（非叶子节点）和数据页（叶子节点）。目录页中只保存目录项，不保存用户的数据记录。
查找的时候，不需要线性遍历，只需要自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了 IO 次数。

6.使用 B+ 树的原因

InnoDB 在建立索引结构来管理数据时，其他数据结构为何不行？

链表？线性遍历，效率太低。
二叉搜索树？有可能发生退化问题而退化成为线性链表，造成效率低下。
AVL & 红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相较于多阶 B+，树整体过高。大家都是自顶向下找，而层高越高，意味着加载的 Page 数越多，系统与硬盘 IO 交互的次数越多，效率越低。
Hash？官方的索引实现方式中，MySQL 是支持 Hash 的，不过 InnoDB 和 MyISAM 并不支持。根据 Hash 其算法特征，查找的时间复杂度最快是 O(1)，最差是 O(n)，但是，不能很好地支持范围查找。
B 树？最值得比较的是 InnoDB 为何不用B树作为底层索引？

B 树 VS B+ 树：
① B 树节点，既有数据，又有 Page 指针。而 B+ 树，只有叶子节点有数据，其他目录页，只有键值和 Page 指针。
② B+ 叶子节点，全部相连，而 B 树没有。

为何选择 B+ ？
① B+ 树的节点不存储数据，这样的一个节点就可以存储更多的键值，可以使得树更矮，所以 IO 操作次数更少。
② B+ 树的叶子节点是相连的，更便于进行范围查找。

B+ 树在哪里呢？

在磁盘上有完整的 B+ 树和数据。
在内存中有局部高频被访问的 B+ 树的核心 Page 。
查找一定会伴生着根据 B+ 树进行 Page 的换入换出。

MySQL 就是一个用户级的文件系统。

7.聚簇索引 VS 非聚簇索引

MyISAM 最大的特点是，将索引页和数据页分离，也就是叶子节点没有数据，只有对应数据的地址。
而 InnoDB 索引，其叶子节点是将索引和数据放在一起的。

MyISAM 这种将用户数据与索引数据分离的索引方案，叫做非聚簇索引。
InnoDB 这种将用户数据与索引数据放在一起的索引方案，叫做聚簇索引。

通过查看一个表对应的文件个数，可以证明是聚簇索引还是非聚簇索引。

InnoDB：ibd 文件是表的数据和索引文件，表的数据和索引是放在一起的，即 InnoDB 是聚簇索引。
MyISAM：MYD 文件是表的数据文件，MYI 文件是表的索引文件，表的数据和索引是分离的，即 MyISAM 是非聚簇索引。

MySQL 除了可以建立主键索引外，我们用户也有可能按照其他列信息建立索引，这种索引叫做辅助（普通）索引。

InnoDB 存储引擎 - 辅助索引：

下图是基于 InnoDB 的 Col3（MyISAM 图的 Col3）建立的辅助索引。

可以看到，InnoDB 的非主键索引中的叶子节点并没有数据，而只有对应记录的主键值。
所以，通过辅助索引找到目标记录，可能需要进行两遍索引：首先检索辅助索引获得主键值，然后再根据主键值到主键索引中检索获得记录。这种过程，就叫做回表查询（其实也不一定需要进行回表查询，因为查询的有可能就是这条记录对应的主键值）。