数据库索引是如何工作的？

内容来自 DOC https://q.houxu6.top/?s=数据库索引是如何工作的？

当数据集大小增加时，索引变得非常重要。有人能解释在数据库无关的层面上索引是如何工作的吗？

有关对字段进行索引查询的信息，请查看如何对数据库列进行索引。

---

为什么需要索引？

当数据存储在基于磁盘的存储设备上时，它以数据块的形式存储。这些数据块被完整地访问，成为原子磁盘访问操作。磁盘块的结构与链表非常相似；它们都包含一个用于存储数据的部分、指向下一个节点（或数据块）的指针，并且不需要连续存储。

由于许多记录只能根据一个字段进行排序，我们可以得出结论：在未排序的字段上进行搜索需要进行线性搜索，这需要 (N+1)/2 个数据块访问（平均情况下），其中 N 是表跨越的块数。如果该字段是非键字段（即不包含唯一条目），则整个表空间必须在 N 个数据块访问中进行搜索。

而在一个已排序的字段上，可以使用二分查找，这需要 log2 N 个数据块访问。此外，由于给定了一个非键字段，一旦找到更高的值，就不需要再搜索表中的其他值来查找重复项，因此性能得到了显著提高。

什么是索引？

索引是一种对多个字段上的记录进行排序的方法。在表中的某个字段上创建一个索引会创建另一个数据结构，它包含该字段的值和一个指向原始记录的指针。然后对该索引结构进行排序，以便在其上执行二分查找。

索引的缺点是需要额外的磁盘空间，因为使用MyISAM引擎将这些索引一起存储在表中，这个文件可以很快达到底层文件系统的大小限制，如果在同一张表中对许多字段进行索引。

它是如何工作的？

首先，让我们概述一个数据库表模式；

字段名称       数据类型      磁盘上的大小
id（主键）     无符号整数    4 字节
firstName      字符（50）     50 字节
lastName       字符（50）     50 字节
emailAddress   字符（100）     100 字节

注意：为了准确计算磁盘上的大小，我们使用了char代替varchar。

这个示例数据库包含五百万行记录，没有进行索引。现在将分析几个查询的性能。这些查询包括使用id（一个已排序的键字段）和使用firstName（一个未排序的非键字段）的查询。

示例 1 - 已排序和未排序字段

给我们的样本数据库 r = 5,000,000 记录，每个记录的长度为 R = 204 字节，它们使用MyISAM引擎存储，该引擎使用默认的块大小 B = 1,024 字节。表的阻塞因子将是 bfr = (B/R) = 1024/204 = 5 记录每块磁盘。需要存储表的总块数为 N = (r/bfr) = 5000000/5 = 1,000,000 块。

在id字段上进行线性搜索需要平均 N/2 = 500,000 个数据块访问才能找到值，因为id字段是一个键字段。但由于id字段也是已排序的，可以进行二分查找，平均需要 log2 1000000 = 19.93 = 20 个数据块访问。我们可以看到这是一个巨大的改进。

现在firstName字段既不是已排序的也不是键字段，因此无法进行二分查找，值也不唯一，因此表需要搜索到末尾才能找到确切的 N = 1,000,000 个块访问。这就是索引旨在纠正的情况。

考虑到索引记录仅包含索引字段和指向原始记录的指针，它的大小必然小于指向多字段记录的指针，因此索引本身需要的磁盘块比原始表少，因此需要更少的迭代它的块访问。firstName字段上的索引模式如下所示；

字段名称       数据类型      磁盘上的大小
firstName       字符（50）     50 字节
(记录指针)        特殊        4 字节

注意：MySQL中的指针长度取决于表的大小，可以是2、3、4或5字节。

示例2 - 索引

给定我们的样本数据库 r = 5,000,000 记录，索引记录长度为 R = 54 字节，并使用默认块大小 B = 1,024 字节。索引的阻塞因子将是 bfr = (B/R) = 1024/54 = 18 记录每磁盘块。保留索引所需的总块数为 N = (r/bfr) = 5000000/18 = 277,778 块。

现在，使用 firstName 字段进行搜索可以利用索引来提高性能。这使得索引可以进行二分查找，平均需要 log2 277778 = 18.08 = 19 块访问。为了找到实际记录的地址，还需要进一步的块访问来读取，将总数增加到 19 + 1 = 20 块访问，与在未索引表中查找 firstName 匹配项需要的总块访问数1,000,000相比，这是一个遥远的距离。

何时应该使用它？

创建索引需要额外的磁盘空间（在上面的例子中，额外需要277,778个块，约增加了28%），并且索引过多可能会导致文件系统大小限制的问题，因此在选择要索引的字段时必须谨慎考虑。

由于索引仅用于加速记录中匹配字段的搜索，因此可以推断出，仅用于输出的字段进行索引只是浪费磁盘空间和处理时间，在进行插入或删除操作时应避免这样做。此外，考虑到二进制搜索的性质，数据的基数或唯一性很重要。在一个基数为2的字段上进行索引会将数据分成两半，而一个基数为1,000的字段将返回大约1,000条记录。鉴于这种低基数，其有效性降低为线性排序，查询优化器将在基数小于记录数的30%时避免使用索引，从而有效地使索引成为空间浪费。