SQL 索引解释，Pt。 1

如果使用得当，SQL 数据库索引会非常有效，以至于看起来像是魔术。 但是下面的一系列练习将表明，大多数 SQL 索引的逻辑——以及正确使用它们——是非常简单的。

在本系列SQL Indexes Explained中，我们将介绍使用索引来访问数据以及以所有现代 RDBMS 的方式设计索引的动机。 然后，我们将查看用于返回特定查询模式的数据的算法。

您不必对索引有太多了解就能够遵循SQL Indexes Explained 。 只有两个前提：

• SQL 基础知识
• 任何编程语言的基本知识

SQL 索引解释将涉及的主要主题是：

• 为什么我们需要 SQL 数据库索引； 使用索引可视化执行计划
• 索引设计：哪些索引使查询快速高效
• 我们如何编写查询以有效地使用索引
• SQL中使用索引对读写效率的影响
• 覆盖索引
• 分区，它对读写的影响，以及何时使用它

这不仅仅是一个 SQL 索引教程——它深入了解了索引的底层机制。

我们将通过练习和分析我们解决问题的方法来弄清楚 RDBMS 如何使用索引。 我们的练习材料包含只读的 Google 表格。 要进行练习，您可以复制 Google 表格（文件 → 制作副本）或将其内容复制到您自己的 Google 表格中。

在每个练习中，我们将展示一个使用 Oracle 语法的 SQL 查询。 对于日期，我们将使用 ISO 8601 格式YYYY-MM-DD 。

练习 1：客户的所有预订

第一个任务——暂时不要这样做——是从 Reservation 电子表格中为酒店预订系统的特定客户查找所有行，并将它们复制到您自己的电子表格中，模拟以下查询的执行：

 SELECT * FROM Reservations WHERE ClientID = 12;

但是我们想遵循一种特定的方法。

方法一：不排序，不过滤

第一次尝试时，不要使用任何排序或过滤功能。 请记录所花费的时间。 生成的工作表应包含 73 行。

此伪代码说明了无需排序即可完成任务的算法：

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

在这种情况下，我们必须检查所有 841 行以返回并复制满足条件的 73 行。

方法 2：仅排序

对于第二次尝试，根据ClientID列的值对工作表进行排序。 不要使用过滤器。 记录时间，并与不排序数据完成任务所花费的时间进行比较。

排序后，方法如下所示：

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

这一次，我们必须“仅”检查 780 行。 如果我们能以某种方式跳到第一行，那将花费更少的时间。

但是如果我们必须为这个任务开发一个程序，这个解决方案会比第一个解决方案还要慢。 那是因为我们必须首先对所有数据进行排序，这意味着每一行都必须至少访问一次。 仅当工作表已按所需顺序排序时，此方法才有效。

练习 2：从给定日期开始的预订数量

现在的任务是统计 2020 年 8 月 16 日的签到人数：

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

使用练习 1 中的电子表格。测量和比较完成任务所花费的时间（有和没有排序）。 正确的计数是 91。

对于没有排序的方法，算法与练习 1 中的算法基本相同。

排序方法也类似于上一个练习中的方法。 我们将把循环分成两部分：

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

练习 3：刑事调查

警察检查员要求查看 2020 年 8 月 13 日至 14 日抵达酒店的客人名单。

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

方法 1：仅按日期排序

检查员希望快速列出清单。 我们已经知道我们最好根据到达日期对表格/电子表格进行排序。 如果我们刚刚完成练习 2，我们很幸运，表格已经排序。 因此，我们应用类似于练习 2 中的方法。

请尝试记录时间，您必须阅读的行数以及列表中的项目数。

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

使用这种方法，我们必须读取 511 行来编译 46 位客人的列表。 如果我们能够精确地向下滑动，我们实际上不必从重复循环中执行 324 次读取来定位 8 月 13 日的第一个到达。 但是，我们仍然需要读取 100 多行来检查客人是否以3的HotelID到达酒店。

检查员等了那么久，但并不高兴：我们只提供了一个无意义的 ID 列表，而不是客人的姓名和其他相关数据。

我们将在本系列的稍后部分回到这个方面。 让我们首先找到一种更快地准备列表的方法。

方法二：先按酒店，再按日期

要根据HotelID然后DateFrom对行进行排序，我们可以选择所有列，然后使用 Google 表格菜单选项Data → Sort range 。

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

在将第一个到达我们酒店之前，我们不得不跳过前 338 个到达。 在那之后，我们在 8 月 13 日找到了 103 名较早到达的人。 最后，我们复制了ClientID的 46 个连续值。 它帮助我们在第三步中复制了一个连续的 ID 块。 太糟糕了，我们不能以某种方式从那个街区跳到第一行。

方法3：仅按酒店排序

现在使用仅按HotelID排序的电子表格尝试相同的练习。

应用于仅按HotelID排序的表的算法效率低于我们按HotelID和DateFrom （按该顺序）排序时的效率：

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

在这种情况下，我们必须读取HotelID为3的所有 166 次到达酒店，并检查每一次的DateFrom属于请求的时间间隔。

方法 4：按日期排序，然后按酒店排序

我们是HotelID再按DateFrom排序，反之亦然，这真的很重要吗？ 让我们找出答案：尝试DateFrom排序，然后按HotelID 。

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

我们找到了第一行的相关日期，然后阅读更多内容，直到我们找到第一个到达酒店的人。 之后，对于许多行，两个条件都得到了满足，即正确的日期和正确的酒店。 但是，在到达 3 号酒店后，我们在同一天到达了 4 号、5 号等酒店。 在他们之后，我们不得不再次读取第二天的酒店 1 和 2 的行，直到我们能够读取连续到达我们感兴趣的酒店的信息。

正如我们所看到的，所有方法在完整的行集中间都有一个连续的数据块，代表部分匹配的数据。 方法 2 和 4 是唯一允许我们在部分匹配结束之前完全停止算法的方法。

方法 4 在两个块中具有完全匹配的数据，但方法 2 是唯一一种目标数据都在一个连续块中的方法。

从数字上看，很明显方法 2 在这种情况下具有最大的优势。

SQL 索引解释：结论和下一步

做这些练习应该使以下几点变得清晰：

1. 从正确排序的表中读取更快。
2. 如果一个表还没有排序，排序比从一个未排序的表中读取要花费更多的时间。
3. 找到一种方法来跳转到与排序表中的搜索条件匹配的第一行将节省大量读取。
4. 提前对表格进行排序会很有帮助。
5. 为最频繁的查询维护表的排序副本会很有帮助。

现在，表的排序副本听起来几乎就像数据库索引。 SQL Indexes Explained中的下一篇文章介绍了基本的索引实现。 谢谢阅读！