数据库索引和磁盘的关系大揭秘

一、序言

都说加索引能加快查询的速度，其实通过索引本质上是减少磁盘的读取次数，到底索引和磁盘的关系是怎样的呢？

---

二、磁盘结构

首先我们先了解一下操作系统是怎么从磁盘中读取数据的，操作系统通常是以页为单位从磁盘中读取数据，磁盘可以理解为一个圆盘，每个圆盘上有若干磁道。

比如下面的图有4种不同颜色的磁道，每条磁道上每个半圆代表一个扇区，也就是一条磁道在这里被划分为4个扇区。

操作系统从磁盘中读取数据时，也是按页(扇区)来读取，读取一块数据，我们称之为1个block块。

不同硬盘每个扇区的存储容量不一样。

• 机械硬盘：每个扇区为512B。
• 固态硬盘(SSD)：page大小为4KB。

备注：为什么要以页为单位从磁盘读取数据呢？
如果每次只查询1条记录，由于磁盘检索速度比较慢，会有一定的性能消耗。但如果每次读一页数据，会减少磁盘读取的次数。

---

三、磁盘中数据存储

假设有一张用户表，表中共有用户ID、用户名、密码、头像、备注5个字段，字段长度如下：

根据字段长度定义，一条记录的大小为128字节，而机械硬盘一个扇区最多能存储512字节，也就说是一次扇区扫描最多能读取4条记录。

我们把从磁盘扇区读取的数据称之为1个block，

• 一个block可以存储512 / 128 = 4条记录
• 100条记录则需要100 / 4 = 25个block

也就是说读取user_id为100的记录最多需要访问25个block，也就是25次磁盘查询。

如果说要读取user_id为1000的记录，则最多需要进行250次的磁盘查询，如果数据量更大呢？这个时候索引的作用就出来了。

四、索引在磁盘中的存储

上面的例子中，数据量越大，磁盘查询的的次数也就越多，我们可以做一个优化，对user_id列加上索引。

上面我们说了假设该用户表1条记录占128字节，1个block可以存放512 / 128 = 4条记录。

现在对user_id列加上索引，该索引会保存该列的数据和数据指针(指向数据所在的磁盘扇区)。

假设数据指针为6字节，那么一条索引将占用该列长度10 + 6 = 16字节，1个block(512字节)可以存放512 / 16 = 32条索引记录。100条索引记录需要占用100 / 32 = 4个block。

上面说了100条数据记录需要占用100 / 4 = 25个block。以这100条数据为例，我们用4个block存索引数据，25个block存储列数据。

这个时候读取user_id为100个记录，我们先扫描索引，最多读取4个block就可以找到该数据所在磁盘扇区，再经过1次磁盘查询就可以找到该数据，也就是说最多经过5次磁盘查询。

如果是读取user_id为1000的记录，最多读取32个block索引数据加上1个block列数据，也就是33次磁盘查询就能找到。

而且内存访问比磁盘快，因为索引数据比较小，我们完全可以将索引数据加载到内存，这样访问会更快。

备注：InnoDB中索引数据和列数据在同一个(.ibd)文件，而索引数据总是在文件的最前面，查询数据时先扫描索引。

---

五、二级索引

问题又来了，如果数据量越来越大，变成10w条，100w条，就算建立10w条，100w条索引，速度还是会越来越慢，该怎么办呢？

我们可以针对索引再见索引，什么意思呢？

上面说了，1000条数据需要构建32个索引block，如果我们以32为单位，只存32条二级索引记录，再次构建二级索引，则只需要1个索引block。（二级索引只存放user_id值为32及其倍数的数据）

以这1000条数据为例，我们用1个block存二级索引数据，32个block存1级索引数据，250个block存列数据。

这个时候如果读取user_id为1000的记录，只需要读取1个二级索引block，1个一级索引块，再经过1次磁盘查询就能找到，也就是最多3次磁盘查询。

当然，随着数据量越来越大，二级索引数据也会越来越大，为了更好理解，我们将它转换为树的形式，是不是觉得很熟悉。

当然实际索引数据结构，像B+树等多路平衡树会更加复杂，这个我们后面再分享。