SQL索引解释-Pt.2

在《SQL索引详解》的第一课中，我们学习了如何使用排序来加快数据检索的速度。虽然在对行进行排序后，我们的查询执行速度更快，但排序涉及到对每条行至少读取一次，并将其移动。这使得该方法比简单地按顺序读取整个表更慢，效率也更低。

合乎逻辑的结论似乎是，我们应该维护一个给定表的分类副本--我们将正式称之为SQL索引，前缀为IX_。这样一来，第一篇文章中的检索算法就可以适用了，而且我们在开始之前不需要对表进行排序。

索引是表的一个分类副本

让我们看看这个想法的具体实现，还是使用谷歌表格。我们的预订表变成了包含相同数据的五个表的集合。每张表都是根据不同的列进行排序的。

这里的练习是为了不象以前的SQL索引教程文章那样严格--它们可以凭感觉而不是靠计时器和行数来完成。有些练习看起来非常相似，但这一次，我们要探索的是

当使用单独的索引而不是排序的主表时，如何更有效地检索数据
在修改数据时如何保持每个索引和表的顺序

前面的教程侧重于读取，但在许多常见的现实世界的数据动态中--包括我们的酒店预订--我们必须考虑到索引对写入性能的影响，无论是插入新数据还是更新现有数据。

初步练习-取消一个预订

为了感受一下使用排序表策略的SQL索引性能，你的任务是删除客户12的预订，从2020年8月22日开始，在4号酒店。请记住，你必须从该表的所有副本中删除一条记录，并保持正确的排序。

完成了吗？应该很清楚，保持表的多个排序副本的想法并不像它看起来那样好。如果你仍然有任何疑问，你也可以尝试重新插入你刚刚删除的预订或改变现有预订的日期。

虽然表的分类副本可以更快地检索，但正如我们刚才所了解的，数据修改是一场噩梦。每当我们需要添加、删除或更新一个现有的行时，我们将不得不检索表的所有副本，找到一个行和/或应该添加或移动的地方，最后移动数据块。

使用行地址的SQL索引

这个电子表格包含使用不同方法的索引。索引行仍然根据特定的标准进行排序，但我们不保留索引行中的所有其他信息。相反，我们只保留 "行地址"，即保留表中的行的地址--代表表本身--在H列。

所有的RDBMS实现都使用操作系统级的能力，通过物理地址快速找到磁盘上的块。行地址通常由块地址和行在块中的位置组成。

让我们做几个练习来学习这种索引设计是如何工作的。

练习1：一个客户的所有预订

和第一篇文章一样，你要模拟执行下面的SQL查询。

SELECT *
FROM Reservations
WHERE ClientID = 12;

同样，有两种合理的方法。第一种是简单地从Reservations表中读取所有行，只获取与标准相匹配的行。

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

第二种方法涉及从IX_ClientID表读取数据，对于任何符合标准的项目，根据rowAddress值在保留表中找到一条记录。

Get first row from IX_ClientID where ClientID = 12

While IX_ClientID.ClientID = 12
  Fetch Reservations.* where rowAddress = IX_ClientID.rowAddress
  Write down Reservations.*
  Get next row from IX_ClientID

在这里，表达式Get first row from ，通过一个类似于上一篇文章中的循环来实现。

Repeat
  Fetch next row from IX_ClientID
Until ClientID >= 12

你可以通过向下滑动直到找到一行，或者在rowAddress列上使用一个过滤器来找到具有给定rowAddress的行。

如果只有少量的保留要返回，使用索引的方法会更好。然而，如果要返回成百上千--有时甚至只有几十条行，仅仅直接使用保留表会更快。

读取量取决于ClientID的值。对于最大的值，你必须读取整个索引，而对于最小的值，它是在索引的开始。平均值是行数的一半。

我们以后会回到这部分，并提出一个有效的解决方案。现在，让我们把重点放在找到符合我们条件的第一行之后的部分。

练习2：在某一特定日期开始的预订的数量

任务是使用新的索引设计，计算2020年8月16日的入住人数。

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

无论涉及多少行，使用适当的索引进行计数的方法都优于表扫描。原因是我们根本不需要访问预订表--我们在索引本身就有我们需要的所有信息：

Count := 0
Get first row from IX_DateFrom where DateFrom >= '2020-08-16'

While found and DateFrom < '2020-08-17'
  Count := Count + 1
  Get next row from IX_DateFrom

Write down Count

该算法与使用排序表的算法基本相同。然而，索引行比表行短得多，所以我们的RDBMS将不得不从磁盘上读取更少的数据块。

练习3：刑事调查（给定酒店和日期范围的客人名单）

让我们准备一份在2020年8月13日和14日到达3号酒店的客人名单。

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;

我们可以从Reservations表中读取所有的行，或者使用其中一个可用的索引。在对一个按照特定标准排序的表做了同样的练习后，我们发现索引IX_HotelID_DateFrom 是最有效的。

Get first row from IX_HotelID_DateFrom where
  HotelID = 3 and
  DateFrom between '2020-08-13' and '2020-08-14'

While found and DateFrom < '2020-08-15' and IX_HotelID_DateFrom.HotelID = 3
  Fetch Reservations.* where rowAddress = IX_HotelID_DateFrom.rowAddress
  Write down Reservations.ClientID
  Get next row from IX_HotelID_DateFrom

我们可以设计一个更有效的索引吗？

我们访问该表是因为ClientID ，这是我们所报告的客人名单中唯一需要的信息。如果我们在SQL索引中包含这个值，我们就根本不需要访问该表。试着准备一个只从这样一个索引读取的列表，IX_HotelID_DateFrom_ClientID 。

Get first row from IX_HotelID_DateFrom_ClientID where 
  HotelID = 3 and 
  DateFrom between '2020-08-13' and '2020-08-14'

While found and HotelID = 3 and DateFrom < '2020-08-15'
  Write down ClientID
  Get next row from IX_HotelID_DateFrom_ClientID

当索引包含了查询执行的所有必要信息时，我们就说该索引涵盖了该查询。

练习4：客人的名字而不是ID的列表

对于调查犯罪的警察来说，一份客人的ID列表是没有用的。我们需要提供名字：

SELECT c.ClientName
FROM Reservations r
JOIN Clients c ON r.ClientID = c.ClientID
WHERE r.DateFrom BETWEEN (
    TO_DATE('2020-08-13', 'YYYY-MM-DD') AND
    TO_DATE('2020-08-14', 'YYYY-MM-DD')
  ) AND
  r.HotelID = 3;

为了提供一个列表，除了来自Reservations 表的数据外，我们还需要一个包含客人信息的Clients 表，这个表可以在这个Google表中找到。

这个练习与前面的练习类似，方法也是如此。

Get first row from IX_HotelID_DateFrom_ClientID where 
  HotelID = 3 and 
  DateFrom between '2020-08-13' and '2020-08-14'

While found and HotelID = 3 and DateFrom < '2020-08-15' 
  Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID
  Write down Clients.ClientName
  Get next row from IX_HotelID_DateFrom_ClientID

表达式Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID ，可以通过表扫描或使用我们的索引来实现。如果我们使用表扫描，对于来自While 循环的每一个ClientID ，我们将不得不平均从Clients 表中读取一半的行。

-- Get row from Clients using table scan
Repeat
  Fetch next row from Clients
Until ClientID = IX_HotelID_DateFrom_ClientID.ClientID or not found
If found
  Write down ClientName

到目前为止，我们所考虑的索引实现--让我们称它为 "平面 "索引实现--不会有太大帮助。我们将不得不从索引中读取相同数量的行（尽管行数较少），然后使用RowAddress ，跳转到Clients 中的行。

-- Get row from Clients using flat index
Repeat
  Fetch next row from Clients_PK_Flat
Until ClientID >= IX_HotelID_DateFrom_ClientID.ClientID
If found
  Fetch Clients.* where rowAddress = Clients_PK_Flat.rowAddress
  Write down ClientName

注意：在这里，PK 指的是 "主键"，这个术语我们将在本系列的后面进行探讨。

有没有一种方法可以在不需要读取这么多行的情况下完成这个任务？是的--这正是B-树索引的作用。

平衡树（B-tree）索引

让我们把来自Clients_PK_Flat 的行分成四行块，并创建一个包含块中最后一个ClientID 的值和块起始地址的列表（列IndexRowAddress ）。由此产生的数据库索引数据结构--你可以在Client_PK_2Levels表中找到。试试这个新的结构如何帮助你找到一个ClientID 28的客户。这个算法应该是这样的：

Fetch Level2.*
Loop
  Leaf_address := Level3Address
  Exit when ClientID >= 28
  Fetch next row from Level2

Fetch Level3.* where Level3Address = Leaf_address -- 3-21
Loop
 Client_address := RowAddress
 Exit when ClientID >= 28
 Fetch next row from Level 3

Fetch Clients.* where rowAddress = Client_address -- 42
Write down Clients.*

你可能已经发现我们可以增加一个层次。正如你在IX_Clients_PK标签中看到的那样，第1层由4行组成。为了找到ClientID为28的客人的名字，你必须从主键结构中读取三个数据块（节点），每层一个，最后跳到地址为42的客户行。

这个SQL索引的结构被称为平衡树。当从根节点到每个叶级节点的路径具有相同的长度，即其所谓的B-树深度时，该树就是平衡的。在我们的例子中，这个深度是3。

从现在开始，我们会认为每个索引都有B树结构，即使我们的电子表格只包含叶级条目。关于B-树，最重要的事实是

B-树索引结构是市场上所有主要RDBMS最常用的索引。
平衡树的所有级别都是按关键列值排序的。
数据是以块为单位从磁盘上读取的。
一个B-树节点包含一个或多个块。
影响查询性能的最重要因素是从磁盘上读取的块的数量。
每一个新的B树层中的项目数量，从根开始，到叶子层结束，以指数形式增加。

练习5：刑事调查，第二部分

现在，警察检查员正在寻找A市所有酒店的相应客人的姓名、到达日期和酒店名称的列表：

SELECT
  h.HotelName,
  r.DateFrom as CheckInDate,
  c.ClientName
FROM Reservations r
JOIN Clients c ON r.ClientID = c.ClientID
JOIN Hotels h ON r.HotelID = h.HotelID
WHERE r.DateFrom BETWEEN (
    TO_DATE('2020-08-13', 'YYYY-MM-DD') AND
    TO_DATE('2020-08-14', 'YYYY-MM-DD')
  ) AND
  h.City = 'A';

方法一

如果我们使用索引IX_DateFrom_HotelID_ClientID ，那么对于日期范围内的每一行，我们都必须访问Hotels表，并检查该酒店是否来自A市。如果是，我们也必须访问Clients表，以读取客户的名字：

For each row from IX_DateFrom_HotelID_ClientID where
    DateFrom between '2020-08-13' and '2020-08-14'
  For each row from Hotels where
      HotelID = IX_DateFrom_HotelID_ClientID.HotelID
    If Hotels.City = 'A' then
      Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID
      Write down
        Hotels.HotelName,
        IX_HotelID_DateFrom_ClientID.DateFrom,
        Clients.ClientName

方法2

使用IX_HotelID_DateFrom_ClientID 给我们一个更有效的执行计划。

For each row from Hotels where City = 'A'
  For each row from IX_HotelID_DateFrom_ClientID where
      HotelID = Hotels.HotelID and
      DateFrom between '2020-08-13' and '2020-08-14'
    Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID
    Write down
      Hotels.HotelName,
      IX_HotelID_DateFrom_ClientID.DateFrom,
      Clients.ClientName

从Hotels 表中，我们找到了来自A市的所有酒店。知道了这些酒店的ID，我们就可以从IX_HotelID_DateFrom_ClientID 索引中读取后续项目。这样，在找到每个酒店和日期的B树叶层的第一行后，我们就不会读取A市以外的酒店的预订。

在这里，我们可以看到，当我们有一个适合我们目标的数据库索引时，一个额外的连接实际上可以使查询更快。

B树结构以及每当有行插入、更新或删除时如何更新的问题，将在我解释分区的动机及其影响时更详细地讨论。重点是，只要我们使用索引，就可以考虑快速进行这一操作。

SQL中的索引查询-细节决定一切

当涉及到索引和数据库时，在SQL语言层面上的工作在一定程度上隐藏了实现细节。这些练习是为了帮助你感受到在使用不同的SQL索引时，执行计划是如何工作的。读完这篇文章后，我希望你能够在给定可用索引的情况下猜测最佳执行计划，并设计出能够使查询尽可能快速有效的索引。

在本系列的下一部分中，我们将使用和扩展新获得的技能，调查和了解在SQL中使用索引的最常见的最佳做法和反模式。我有一个想在下一部分解决的良好和最佳实践的清单，但为了使下一篇文章更符合你的需要和经验，请随时发表你自己希望看到的问题。

在《SQL索引详解》的最后一部分，我们还将了解表和索引分区，使用它的正确和错误动机，以及它对查询性能和数据库维护的影响。

了解基础知识

什么是SQL中的表？

SQL中的表是一个数据库对象，它包含以行和列组织的数据。在这方面，它类似于电子表格。每一行代表一条由列值组成的记录。表的定义包括每一列的名称和数据类型以及适用于表数据的约束。

什么是SQL表的索引？

SQL表可以有与之相关的索引。索引的作用是支持快速数据检索。除此之外，索引还被用来执行唯一键约束和加快外键约束的评估。

SQL索引的解释及实例