MySQL加速原理

旧城等待， 2023-10-04 23:33 4阅读 0赞

MySQL必加速原理。对于HDD硬盘来说呀，都会将盘片划分成一个个大小都是512字节的扇区。但是对于SSD固态硬盘来说 ，不存在真正的扇区 ，就没有扇区这个定义了，它是由4KB的配置组成的。

先看应用场景最广泛的HDD硬盘。如果我们现在有这样一张买车购表。一共有32条数据，如果假设每条记录占用的存储空间是512字节，那么它就会占用32个扇区来存储这些数据。试想一下，如果我们现在要查找的目标数据的ID等于32，如果现在也没有建立索引，就一定会发生全面扫描，那么一定需要32次IO才能找到目标数据吗？其实并不是这样的，因为操作系统 ，都有连续读和缓冲数据的原理。

在计算机领域，当一个数据被用到时，通常它附近的数据也会被用到。因此磁盘在每一次发生读的操作的时候呢都会在找到目标数据之后都会去连续读取4KB的数据，比如说磁盘的磁头的转动，再连续读取4KB的数据。这样的话，它我在文件系统中，就把一个块的大小定义成了4KB。相当于每次IO操作，它会加载八个三区的数据到内存中。因此我们可以看见到在文件系统中，即使这个文件只存储了一个字节的数据，但是它占用的物理空间仍然是4KB。而MYSQL呢，又觉得4KB太小了。在MYSQL中，最小的存储单元是页。一个页的占用的存储空间呢是16KB。

这个是默认值，我们可以修改的。相当于是每次IO操作呢都可以缓存16KB的数据，它是以16KB作为一个存储单元 ，最小缓存16KB，然后会把这个16KB的数据呢放到八分铺中，在八分铺中每个bit buffer的大小恰好是16KB，而bit buffer的空间释放也不需要等待ZVM的GC操作。因此 ，就不会发生stop the world，这个效率就非常高了。因此，对于这个32行数据的表 ，即使我们不创建索引，我们在查找数据的时候也未必会感受到慢，这就是顺序读和八号铺给我们带来的便利。

但是当数据量达到几十万、几百万甚至上千万的时候，我们就不得不创建索引了。在mysql中 所有的数据都是被存储在配置中的 ，由配置去管理这些内容，因此 即使是我们看，呃，即使这样非常小的一张表。这个school表 ，非常小，它只有三列两行数据，但是它占用的存储空间是96KB。 ，它是16KB的六位，因为在MYSQL中 ，统计数据的占用空间是根据这个配置来计算的。一个配G的大小呢是16KB，如果。一行买折扣的记录是512字节，那么一个配置最多可以存储32条数据。因此我们可以假设这样一个场景 ，在我们这个假设的场景中，如果使用的是SSD硬盘，那么每一次加载数据呢，就相当于是对于IDD硬盘来说，可能要发生32次IO读 ，就是这种即使顺序读吧，才能把这个32条数据加载到。

内存中，但是对于SSSD硬盘来说，它的一次读呢，因为它的快呢是4KB ，相当于它读取的速度会更快。但是SSD的造价也是比较高的 ，这也就是说，呃，为什么SDD硬盘的应用范围是更广。好，我们回过头来。如果这个数据 。呃，都是这样杂乱无章的存储在这些配置中，那么这个查找数据就变成了大问题了，因为我们不知道这个数据究竟是放到哪个配置中，我们也不可能把所有的配置都变了一遍 。因此 ，这个时候我们就联想到了B加速索引，我们可以给ID这一列数据创建索引。当然这个索引呢，它也是要占用物理存储空间的，假设我们这个ID的数据类型是。并呢占用八字节的存储空间，而在in DB中索引指针，它占用的存储空间是六个字节。那么。一行索引占用的空间就是八加六等于14个字节，那么一个P字呢就可以存储。1170条索引。这是个什么概念呢？就相当于我们可以建立一个1170阶的一级索引，1170阶的索引就是指一个配置 ，可以指向1170个叶子节点。

我们就可以构建成，把这构建成这样的一个B加数，本来呀，这个B加数的叶子节点，它是一个单向的列表 ，列单向的链表，但是这个买circle呢，就把它优化成了双向链表，双向链表的好处我就不给大家说了 。我们想象一下，如果每个叶子节点可以存储32行记录，那么一级索引就可以 ，索引到1170乘以32等于37440条数据。而现在的数高仅仅是二。可见这个B加树 是一棵矮树，树的高度越矮的话，那么查找效率就越高。比如我们现在要查找的目标元素ID等于34，找这个数据。

那么在第一次发生IO磁盘操作的时候，他首先要访问根节点，把根节点，也就是这个一级索引加载到内存中。 ，也就是加载到buffer，把这个加载到buffer铺中，因为34 它是在33和64之间，因此就会在内存中去顺序查找，找到根节点，这个是33。然后访问根据点33指向的。这个叶子节点。现在呢？发生第二次操作，将这个月子节点的数据内容全部加载到内存中。然后顺序遍历叶子节点的顺序。找到，一直找到ID等于34这条数据为止。可见 ，现在仅仅发生了两次磁盘的IO操作。就找到了我们想要的目标数据。可见这个查找效率呢还是非常高的，在里边的这个都是发生的内存的顺序查找。想象一下，如果我们的数据不仅仅只有37000条，可能远远大于37000条。

这个时候呢，我们 ，就会联想到可以创建二级索引。二级索引呢的每个索引内容 ，存储的都是索引值，因此它也可以最多存储 1170个索引。去指向1170个子节点。二级索引呢，那么它就可以存储多少条数据呢？就是1170乘以1170，再乘以32， ，这是4300万条数据，相当于到了四千万级别的这个数据量。而每个二级索引呢，它又可以指向1170个一级索引 。而每个一级索引呢，又可以指向1170个叶子节点，这样呢，我们就把一个B加数给它构建完了。这时树的高度是三。如果我们现在要做一个范围查找，想查找ID在34到64之间的数据。

首先呢，它是将根节点加载到内存中，然后找到34所属的根节点一。然后找到根据点一指向的一级索引。将一级索引的内容加载到内存中，这是发生了第二次IO操作。在一级索引中呢，顺序查找34所属的这个索引33，然后把33指向的叶子节点再次加载到内存中。然后在内存中进行顺序查找，当找到目标节点是34 的时候呢，然后开始顺序向后遍历，一直变利到。64为止。

这都是内存便利。如果我们现在。查找的范围呢，扩大一些，比如说在66，最大值是66，那么他就通过这个指针再去找下一个配置的内容，然后去顺序读。可见 ，即使在这种千万级别的MYSQL表中，如果要想查找某条数据，或者用范围查找所花费的IO，也仅仅就是三次就可以做到。B加速呢？所有的索引查询发生的IO次数 ，都是树的高度。这就是B加数给我们带来的便利。B加数仅仅在叶子节点上存储数据，非叶子节点呢，存储的都是索引的信息。

因此呢，这个非叶节点占用的空间也不大。但是这也毕竟是占用空间 。因此。B加速呢，它也是一种用空间换时间的做法，用空间换时间的这种做法在计算机领域是非常普遍的。比如我们前面给大家讲解的跳跃表的原理，也是用空间换时间的做法。关于必加数呢？我们后面会逐渐给大家介绍B加处的插入删除操作，以及买收购表的索引失效的原因。