索引的真正工作原理

我们知道索引使数据库更快。但为什么呢？这不仅仅是魔法;它是关于有效地导航数据的物理存储方式并减少数据库必须完成的工作量，尤其是在从磁盘读取时。

将您的数据库表（也许是一个表）视为大量记录（SQL 中的行、NoSQL 中的文档）的集合。这些记录不仅漂浮在内存中;它们必须序列化并持久存储在磁盘（HDD 或 SSD）上。Users

慢车道：没有索引的生活 （全表扫描）

让我们直观地了解数据如何到达磁盘。想象一下我们的表格：Users

CREATE TABLE Users (
    ID INT PRIMARY KEY, -- Let's say 4 bytes
    Name VARCHAR(60),   -- 60 bytes
    Age INT,            -- 4 bytes
    Bio TEXT,           -- 128 bytes (can be large!)
    TotalBlogs INT      -- 4 bytes
);

根据这些大小，每个用户记录在存储时占用空间。4 + 60 + 4 + 128 + 4 = 200 bytes

现在，磁盘不会逐字节读取数据。他们以块（或页）的形式读取。常见的数据块大小为 4KB，但为简单起见，让我们使用每个数据块 600 字节的转录示例。

如果每条记录是 200 字节，一个块是 600 字节，我们可以将用户记录放入单个磁盘块中。600 / 200 = 3

假设我们的表有 100 行。要存储这些，我们需要：

由于我们不能有一个区块的一小部分，我们需要 34 个区块来按顺序存储整个表。Users100 rows / 3 rows/block = 33.33 blocks

现在，关键部分是：磁盘 I/O 速度很慢！从磁盘读取块比从内存中读取慢几个数量级。

请考虑以下查询：

SELECT * FROM Users WHERE Age = 23;

如果没有 上的索引，数据库如何找到这些用户？它别无选择，只能执行全表扫描：Age

1. 将块 1（包含第 1-3 行）读入内存。
2. 检查这 3 行中的每一行。保留匹配项。丢弃块（概念上）。Age
3. 将块 2（包含第 4-6 行）读入内存。
4. 检查第 4-6 行。保留匹配项。丢弃该块。Age
5. ...对所有 34 个块重复此作。

即使只有两个用户有 ，数据库仍然必须读取和处理所有 34 个区块才能找到它们。如果读取一个块需要假设的 1 秒（根据 transcript 的插图 - 实际上要快得多，但原则成立），则此查询需要 34 秒。这很痛苦。Age = 23

快车道：索引简介

索引本质上是一个单独的、更小的、结构特殊的表，它的作用类似于主表数据的快速查找指南。它存储索引列的值和指向实际数据行的指针（如行 ID 或主键）。

让我们在列上创建一个索引：Age

CREATE INDEX idx_users_age ON Users(Age);

这个索引在概念上可能看起来像这样（它通常存储在更复杂的结构中，如 B+ 树，但让我们简化一下）：idx_users_age

主要特点：

1. 较小：每个索引条目仅包含索引值（4 字节）和指针（4 字节）= 8 字节。比完整的 200 字节记录小得多！AgeID
2. 排序：索引按索引值 （） 排序。这对于快速查找至关重要。Age

现在，让我们计算 100 行表的索引大小：
。100 entries * 8 bytes/entry = 800 bytes

使用我们的 600 字节块，索引需要多少个块？
-> 2 个块。800 bytes / 600 bytes/block = 1.33 blocks

查询如何使用索引运行

让我们再次运行相同的查询：

SELECT * FROM Users WHERE Age = 23;

这一次，数据库查询计划程序看到索引并使用它：Age

1. 扫描索引：它不是扫描主表，而是扫描微小的索引。由于索引已排序，因此它可以快速找到其中 .在最坏的情况下（使用转录文本的简单扫描模型），它会读取整个索引。那是多少个区块？只有 2 个区块。Age = 23
   • （优化说明：使用 B+ 树的真实数据库甚至更智能，通常不需要读取所有索引块，但让我们坚持使用简单的模型进行比较。
2. 确定相关的行 ID：通过扫描索引，数据库会找到其中 .在我们的示例中，它得到 和 。Age = 23Row ID 1Row ID 4
3. 获取实际行：现在，有了特定的 Row ID，数据库直接进入包含这些行的主表块。
   • 行 ID 1 位于块 1 中。读取块 1（读取 1 块）。
   • 行 ID 4 位于块 2 中。读取块 2（读取 1 块）。
4. 返回结果：收集第 1 行和第 4 行的完整数据并返回它们。

此索引查询中读取的磁盘块总数：
。2 blocks (for index scan) + 1 block (for row 1) + 1 block (for row 4) = **4 blocks**

回报：比较性能

• 无索引：34 次数据块读取（34 秒假设）
• 带索引：4 次数据块读取（4 秒假设）

在这个简化的示例中，性能提高了 8 倍，只需添加一个索引即可！在具有更大表和更复杂查询的实际场景中，差异可能更加明显（100 倍、1000 倍或更高！

为什么这对您很重要？

1. 减少磁盘 I/O：这是核心胜利。索引大大减少了从慢速磁盘存储中读取的块数。
2. 目标读物：数据库不是盲目地扫描所有内容，而是使用索引来准确确定它需要的数据块。
3. 查询规划：了解数据库的查询计划程序尝试对 、 、 和 sometimes 子句中的列使用索引。WHEREJOIN ONORDER BYGROUP BY
4. 指标选择性：索引在值多样化（高选择性）的列上效果最佳。为仅包含 'M'、'F'、'Other' 的列编制索引可能不如为列编制索引有效。genderemail
5. 权衡：索引不是免费的！它们会占用磁盘空间并略微减慢 、 和作速度，因为索引也需要更新。根据您的读取模式明智地选择索引。INSERTUPDATEDELETE

结论

索引不是灵丹妙药，但它们可以说是优化数据库读取性能的最有影响力的工具。通过创建这些更小的排序查找结构，我们大大减少了对昂贵的全表扫描的需求，最大限度地减少了磁盘 I/O 并使我们的查询更加顺利。

下次您遇到慢速查询时，不要只是投入更多硬件。分析查询计划 （），检查 and 子句，并确保在右侧列上具有适当的索引。您的用户（和您的服务器）会感谢您。

出处：https://dev.to/