mysql索引 -- 索引的硬件理解(磁盘,磁盘与系统),软件理解(mysql,与系统io,buffer pool),索引结构介绍和理解(page内部,page之间,为什么是b+树)

//建表
CREATE TABLE EMP (
    emp_id INT,
    emp_name VARCHAR(255),
    job_title VARCHAR(100),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10,2),
    commission DECIMAL(10,2),
    phone_number INT
);

//生成随机数据
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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 DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(10 + RAND() * 500);
    RETURN i;
END $$
DELIMITER ;

//插入数据
DELIMITER $$
CREATE PROCEDURE insert_emp(IN start INT, IN max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;  -- 关闭自动提交
    REPEAT
        SET i = i + 1;
        INSERT INTO EMP VALUES (
            (start + i), 
            rand_string(6),    -- 随机字符串作为员工姓名
            'SALESMAN',        -- 职位
            1,                 -- 部门ID
            CURDATE(),        -- 雇佣日期
            2000,              -- 薪水
            400,               -- 佣金
            rand_num()         -- 随机数字作为电话号码
        );
    UNTIL i = max_num END REPEAT;
    COMMIT;  -- 提交事务
END $$
DELIMITER ;

//调用函数
CALL insert_emp(100001, 8000000);

不使用索引的情况下,在存储大量数据的表中查询:

若为它创建索引:

硬件理解

引入

数据库都是要实现持久化的,所以必然会涉及到磁盘上的很多io操作

所以,了解磁盘也是学习数据库的前提
我们之前已经介绍过磁盘结构 -- 磁盘物理结构介绍(磁头,扇区,如何读写,磁盘io消耗时间),CHS寻址,将磁盘线性抽象,LBA寻址,分区引入_磁带硬盘读取机构-CSDN博客
我们这里只是做一个补充

扇区

虽然扇区的物理大小不同(从外到里,大小变小),但存储容量是相同的

大部分是512字节,一些经过技术迭代的使用4kb
通过让比特位密度变化来实现

数据库文件本质

数据库对应linux下的目录文件,数据库表对应普通文件

所以,数据库中保存的数据就在文件系统下,也就是在扇区中
那么,找到一个文件的全部,本质就是找到所有保存该文件数据的扇区

找到扇区 -- 通过inode中的datablock

对表进行CURD操作=对文件数据增删查改

系统与硬件的io单位

是否直接以扇区大小为io单位?

不是
如果直接用硬件提供的数据,会让软硬件强相关
一旦更换硬件,系统也要跟着换

并且,512字节作为io单位太小了

这意味着会有更多的磁盘访问,而磁盘io速度很慢,会造成系统io效率降低
并且,每次io都需要磁盘的读写头在物理层面上进行移动,降低整体性能

考虑到内存本身就被分为若干个4kb大小的块

为了磁盘数据和内存数据更好地io交互,就采用4kb作为系统与硬件的io单位

随机访问和连续访问

随机访问

指本次io所给出的扇区地址和上次io给出扇区地址不连续

如果每次io都是随机访问,磁头可能会产生较大位移:

顺/逆时针 -- 选择扇区
磁头沿半径方向移动 -- 选择磁道

连续访问

指本次io 给出的扇区地址与上次 io 结束的扇区地址是连续的

连续访问可以减少磁头摆动次数,不仅提高了效率,也延长了磁盘的使用寿命

软件理解

mysql

mysql作为应用层软件存在

对于系统来说,它是一个进程
对于网络来说,它是一个服务

mysql是基于os之上的一个特殊文件系统,比其他文件系统会有更多的io场景

所以,io单位是16kb(是InnoDB存储引擎的标准)

可以通过show global status like 'innodb_page_size' 查看io单位大小:

16384字节=16kb
这个也被叫做page,它与系统page(页面)的关系 -- 1:4
向数据库存入数据时,就是以page为单位,存入到某个文件中
注意,这个page用于mysql和os之间的io过程(因为应用层软件无法直接与硬件交互,只能经过os来实现)

为什么是16kb

为什么mysql以16kb大小的page与os进行交互?

多加载的数据属于预加载,可以有效减少io次数
因为局部性原理,下次访问的数据可能就在上次访问位置的周围

如果需要多少,加载多少可以吗?

假设有一张表,你访问了id=2的数据,下次可能会继续访问id=3,4....的数据
如果用多少加载多少,需要io两次
如果一次加载page大小的数据,往后依次查询时就不需要再次io了

当然,我们并不能保证下一次一定就访问相邻的数据

只是有很大概率要找的数据刚好在page里

往往影响io效率的,不是io单次数据量的大小,而是io次数

如何理解mysql中page的概念

mysql内部,需要并且存在着大量page

哪些page需要执行刷新操作,哪些page是新增的,哪些page需要被清理,查找时如何遍历page...
这些都是需要被知道的

所以,mysql必须管理这些page -- 先描述,再组织

所以,page不仅仅是内存块,里面放着对应的管理信息和文件数据
并且以某种方式组织起来,可以让我们对它们执行遍历操作

以上,我们就完成了在buffer pool内部,对mysql的page进行建模

io时用的page,就是结构体对象
数据的传递,就是在传递page对象

Buffer Pool

介绍

mysql会在服务器启动时,在服务器内部申请一块大内存空间(buffer pool),作为数据缓冲区

mysql会自行管理这片空间
如何申请空间 -- mysql使用c/c++写的,所以就是调用malloc/new

如何证明会开辟空间:

查看配置文件,其中有一个字段是innodb_buffer_pool_size:
它可以控制这篇空间的大小
默认大小128M

之后对表的操作,都是在这篇区域进行

io过程

mysql执行CURD操作需要找到操作数据的位置,那么就需要经过计算

而计算需要CPU参与,CPU处理的数据是放在内存中
所以,就注定在特定时间内,mysql中的数据在磁盘和内存中都会有一部分
(先由磁盘拷贝到内核缓冲区中,再放入mysql自己维护的缓冲区中,然后执行代码进行处理)

mysql中的文件数据从os的文件缓冲区中来

调用read,write与os进行io交互
当用户申请读数据时,如果数据不在buffer pool中,mysql会委托os,由os向磁盘发起4次io(因为mysql的page是系统page的4倍大小,需要满足mysql的请求)
最后,一段时间后,由os将数据刷新到磁盘上(需要完成持久化) -- 调用fsync()

接下来,我们从结果推导原因,来理解mysql的索引结构

理解

测试表
create table if not exists user (
    id int primary key,
    age int not null,
    name varchar(16) not null
);
向表中插入5条无序数据:
insert into user (id, age, name) values(3, 18, '杨过');
insert into user (id, age, name) values(4, 16, '小龙女');
insert into user (id, age, name) values(2, 26, '黄蓉');
insert into user (id, age, name) values(5, 36, '郭靖');
insert into user (id, age, name) values(1, 56, '欧阳锋');
查询数据时,发现已经按照主键排好序了:

这是因为我们将id设置为了主键

如果没有主键:

插入的时候什么顺序,查出来就是什么顺序

为什么要排序

为了优化查询效率

page内部存放文件数据的模块,实际上也是一个链表结构

而链表并不适合查询,所以需要对查询进行优化

而且,如果要存放海量数据(比如千万级别)

就需要多个page联合保存这些数据,彼此之间使用双链表连接
这样链表里面套着链表,查找某条数据时,一定是线性的,效率太低

查找的本质是淘汰

有序可以保证查过的数据都是有效数据(万一查到最后没找着,白查了 ; 如果换成有序的也许可以提前结束查找操作)
还可以基于有序设计各种查找算法,提高效率

综上,提高效率一定是在两个方面

page内部
page之间

下面我们以innodb存储引擎中的索引结构为例

page内部

引入页目录

我们的书本中一般都会有目录结构 -- 章节号+页号

这样查找的时候,不需要遍历书本身,而是遍历目录即可

目录一般不会太多

因为不会显示连续的页码,而是跳着来
比如1-10页,10-30页

目录是为了提高对书本的检索效率

并且目录的存在很普遍,说明目录确实可以有效提高效率

而目录也是占用了整本书的一部分内容的

类比到计算机,目录也是会占用内存空间的,而那部分空间还能承载更多的数据
所以增加目录,本质上是在用空间换时间,并且这样的置换是有价值的

介绍

在page内部,目录项包含两个字段:

起始记录的key值
起始记录的指针

有了目录之后,查找page就直接遍历目录

因为数据有序,所以可以有效跳过一些数据,还不会遗漏
最后,我们只需要对其中一个子序列做遍历即可

这也就是为什么mysql会自动按照主键排序

数据有序,才能更好地支持页内目录

多个page之间

引入

虽然解决了单page的效率问题,但是如果一个表非常大,需要多个page才能存放完所有数据

mysql会在容量不足时,自动开辟新的page来保存新的数据
这多个page之间是线性关系,通过指针组织起来

如果有很多个page(比如几千个)

那么即使提升了page内部的效率也显得无济于事,因为你依然需要遍历所有的page(万一要的数据刚好在末尾呢)
采用线性操作,就注定了它的效率很低

并且遍历操作一定是在内存中进行的

就需要将该文件对应的所有page都从磁盘交换到内存中
那么io次数就增加了,使效率继续降低

所以,解决思路就是 -- 继续加目录

介绍

创建一批新的page,它们和其他page不同,他们内部不存储文件数据,存放的是两个字段:

page结构指针
指向的page中最小的起始记录编号
(和page内部的设计是类似的,只是指向的结构变了)

查找时,和单page内部的查找逻辑相同

通过记录的编号来确定目标所在位置,从而通过指针访问那个page结构
然后转入单page查找流程

一个特殊page中能管理多少个page结构?

假设是64位机,一个指针8字节,存储key值是4字节,一共就是12字节

一个page是16kb=16384字节=1365个page目录项
所以,一个特殊page可以管理1365个page结构,相当于可以管理1365*16kb=21mb的数据

数据量很大时

如果有个表特别大特别大,page结构多到需要多个特殊page来管理

那么新加的这一层目录又变成了线性遍历

怎么解决呢?继续加目录:

实际上,能到第三层就已经是相当相当大的数据量了

第三层的page依然可以管理1365个第二层的page,而第二层的page,一个就相当于21mb
那么,就可以管理1365*21mb=27gb的数据

总结

通过这种自顶向下的结构，B+树能够有效地淘汰不相关的分支，快速定位到所需的数据

每次查询时，都可以在较少的步骤中找到目标节点

这样的结构组织方式,就是数据结构中的B+树(多叉树)

所以,mysql下的innodb存储引擎,对于数据的统一管理,使用B+树形成索引结构

其他大部分存储引擎使用的索引结构也是B+树
一般我们建表插入数据时,就是在该结构下进行CURD

每张表都会以b+树的结构在内存中存放

而内存指的就是mysql服务器维护的buffer pool
这些树大部分是残缺的,因为我们不需要将所有结点都放到内存中

结构进一步理解

真实结构中,只有叶子结点会通过链表组织起来,上层都只是通过目录结构关联起来,互相之间没有关联

为啥叶子结点仍然保留指针?

实际上,这是b+树自身的特点

叶子结点天然就是相连的

其次,也是mysql期望的

有时候我们需要多行数据(也就是范围查找),如果只依靠检索功能,就比较麻烦(每行数据都要走一遍查询流程)
如果叶子结点相连,就可以直接线性遍历到所需数据

为什么只有叶子结点保存数据呢?

这样做可以让非叶子结点存储更多的目录项/目录页

就可以管理更多的page结构,形成一个"矮胖型"的的树
结合我们搜索时,是从上到下搜索(根结点->叶子结点),那么,就可以减少途径结点的个数

同时,也减少了io次数

因为只需要将经过的结点交换到内存即可
也就是按需获取 -- 以page为单位

如果没有主键呢?

也会形成这样的索引结构

因为即使没有手动设置主键,mysql也会自动向表中添加一个隐藏列,这个列自动设置了主键
当然,这个主键无法进行我们预想的排序,它只能按照插入顺序排列 (可以认为,按照插入数据排列,就是一种有序)

当我们设置主键后,会重新构建一棵按主键排好序的b+树

总结

page分为数据页和目录页

目录页只存放各个下级page的最小key值,以及指向下级page的指针

只要创建了表,必然会创建主键索引

如果表中没有设置主键,就以mysql添加的隐藏列为主键,建立b+树(这样建立的b+树会以线性方式遍历)
如果手动设置了主键,会重新构建一棵主键索引

为什么不使用其他数据结构

我们虽然知道了使用b+树的好处,那么不可以使用其他数据结构吗?

链表

线性结构,肯定不行

我们上面引入索引时,就是从链表结构一步一步引入b+树的

二叉搜索树

虽然有序,但属于"瘦高型"二叉树

每次只能淘汰一个分支,需要经过的结点更多,需要更多次io
还可能会有退化问题,导致变成线性结构

avl树/红黑树

虽然是平衡/近似平衡的结构

但依然是二叉树,需要更多次io
层高越低,io次数越少,相较于b+树来说,avl/红黑树还是太高了

哈希表

虽然哈希表可以达到O(1)的复杂度

但面对范围查找时,每个结点都需要经历一次哈希

在官方的索引实现方式中,mysql是支持hash的,不过innodb和myisam并不支持

b树

虽然名字类似,但和b+树结构不同

叶子结点之间没有连接
除叶子结点之外,上层结点内也会存放数据

这两点不同,就是mysql为什么不选择b树的原因

如果要存放数据:

那么能存储的目录项就少了,一个页目录能管理的子目录/子结点就少了
那么形成的树就要比b+树要高
就要经过更多的结点,需要更多次io

如果叶子结点之间没有相连:

在进行范围查找时,每个数据都要历经一次查找
就可能让原本不需要放入内存的结点交换进内存,这也增加了io次数