用SQL索引和分区解决瓶颈问题

在《SQL索引详解》的第一课中，我们了解到，当数据已经按照特定列的值排序时，SELECT 查询会更快。

在第二课中，我们学习了B树索引的基本结构以及如何使用它们来减少我们在执行查询时访问的数据量。我们还弄清楚了如何实现连接多个表的查询，以及索引如何加快这种查询的速度。

我们还强调了在SQL中使用索引有帮助的两种情况。当索引是覆盖式索引时，包含了查询中的所有列--来自WHERE 条件、JOIN 条件和SELECT 列表--我们完全避免读取相应的表。另外，当索引将访问的数据块的数量减少到表大小的一小部分时，索引也会有所帮助。

否则，扫描整个表比从索引中读取并随机地来回跳转到相应的表行更有效率。

SQL范围查询

可以利用索引的查询通常包括大大减少一个或多个列的可能取值范围的条件。范围查询根据 "列A的值必须在X和Y之间 "这样的条件来限制数据。

第二课练习四中的查询就是一个很好的例子。

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;

这里我们有两个范围。第一个是日期范围，即2020年8月13日和2020年8月14日之间的时期。第二个是最小的数字范围。这个条件相当于r.HotelID BETWEEN 3 AND 3 。

练习1：周期（日期和时间范围查询）

让我们在Reservations 表中添加一个名为CheckInTime 的列。你可以在这个电子表格中看到样本数据。注意有一个单一的索引涵盖了CheckInTime 和ClientId 。

写一个查询，返回2020年8月15日入住的客户姓名。

没有经验的SQL开发人员通常会写下面的查询

SELECT c.ClientName
FROM Reservations r
JOIN Clients c ON r.ClientID = c.ClientID
WHERE TO_DATE(r.CheckInTime, 'YYYY-MM-DD') = '2020-08-15';

他们认为查询的执行会是这样的

Get first row from IX_CheckInTime_ClientID where
  TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15'

While found and TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' 
  Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID
  Write down Clients.ClientName
  Get next row from IX_CheckInTime_ClientID

问题是，在写这篇文章的时候，没有一个RDBMS能够生成这样的执行计划。他们认为TO_DATE （Oracle语法）是一个将列CheckInTime 的值转化为非索引的东西的函数。所以，他们倾向于生成的执行计划看起来像这样。

For each row from IX_CheckInTime_ClientID
  If TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' then
    Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID
    Write down Clients.ClientName

执行这个会比从Reservations 表中读取所有的行更快，因为索引行比表的行更窄。更小的行意味着需要从磁盘上访问的块更少。

然而，我们知道第一个执行计划的效率会高很多。为了说服我们的RDBMS使用这种方法，我们需要重写这个查询。

SELECT c.ClientName
FROM Reservations r
JOIN Clients c ON r.ClientID = c.ClientID
WHERE r.CheckInTime >= TO_DATE('2020-08-15 00:00:00', 'YYYY-MM-DD HH:MI:SS')
  AND r.CheckInTime < TO_DATE('2020-08-16 00:00:00', 'YYYY-MM-DD HH:MI:SS');

这是一个适当的范围查询，每个好的RDBMS都能理解。我们的RDBMS知道，我们要的是Reservations 表中的数据，其中CheckInTime的值--而不是从它派生出来的--属于定义好的范围。它产生的执行计划将更像是

Get first row from IX_CheckInTime_ClientID where
  CheckInTime >= '2020-08-15 00:00:00'

While found and CheckInTime < '2020-08-16 00:00:00' 
  Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID
  Write down Clients.ClientName
  Get next row from IX_CheckInTime_ClientID

这就是我们真正想要的：不仅要利用索引本身，还要利用它被排序的事实。

练习2：LIKE ，在开始时使用通配符

这一次，我们的侦探来到酒店，对嫌疑人的信息很模糊：只是知道他的姓以"-儿子 "结尾。侦探想知道所有这些客人的名字和姓氏。

SELECT FirstName, LastName
FROM Clients
WHERE LastName LIKE '%son';

对于Clients 表和LastName 的索引，我们将使用这个电子表格。写下该查询将返回的结果。思考一下你可以应用的不同方法。

表扫描方法

最简单的策略是从表中读取所有的数据，当客人的姓氏以"-儿子 "结尾时，写下他们的名字。

For each row from Clients
  If LastName like '%son' then write down FirstName, LastName

在这里，我们将不得不按顺序读取整个表。

使用索引

让我们试着利用LastName 列上的索引。进入IX_LastName表，用它来找到所有满足给定标准的客户，并写下他们的名字。

事实证明，你必须阅读整个索引才能从表中找到所有的安徒生、罗宾逊和汤普森。这比使用表扫描要好吗？除了读取整个索引，你还必须为每个匹配的条目从表中找到相应的行，使用rowAddress ，然后从那里写下FirstName 。

For each row from IX_LastName
  If LastName like '%son' then
    Fetch Clients.* where RowAddress = IX_LastName.RowAddress
    Write down FirstName, LastName

对我们来说，按顺序读表更简单、更快。对于我们的RDBMS来说，这将取决于满足标准的行的百分比。如果在一个大表中只有少数的Andersons、Robinsons和Thompsons，那么RDBMS会从窄得多的索引条目中读取较少的数据块，即使在找到一个匹配项时要从表中读取几个块。否则，表的扫描需要更少的时间。

索引中的数据排序并不能为这样的查询提供任何帮助。索引行的较小尺寸可能会有帮助--但只是有时。

练习3：LIKE ，结尾处有通配符

下次我们的侦探来的时候，我们需要找到所有姓氏以 "Rob-"开头的客户。

SELECT FirstName, LastName
FROM Clients
WHERE LastName LIKE 'Rob%';

试着从同一个电子表格中提取与查询相匹配的数据。

如果你使用表扫描的方法，你就错过了充分利用索引的机会IX_LastName 。从索引中找到第一个以 "Rob-"开头的条目（Roberts），读取后续的行（包括Robertses和Robinsons），并在LastName 不再符合标准时停止，这样做要快得多。

Get first row from IX_LastName where LastName <= 'Rob'

While found and LastName < 'Roc' 
  Fetch Clients.* where rowAddress = IX_LastName.rowAddress
  Write down FirstName, LastName
  Get next from IX_LastName

在这种情况下，在对第一个条目进行B树查找后，我们只读取满足标准的条目。一旦我们读到一个不符合标准的名字，我们就停止读取。

解决B树的扩展问题

通常情况下，当我们部署一个新的数据库时，会有一些填充的查询表和空的事务表。系统从一开始就运行得很顺利，特别是如果我们尊重数据库设计的良好做法，将表规范化；创建主键、外键和唯一键；用相应的索引支持外键。

几个月或几年后，当数据量大大增加了系统和数据库的复杂性，我们开始注意到性能下降。关于系统为什么会变慢，以及如何处理的问题，就会出现各种意见。

流行的观点通常是，数据库的大小是主要的罪魁祸首。解决办法似乎是删除我们每天不需要的历史数据，将其放在一个单独的数据库中，用于报告和分析。

让我们先来探讨一下主要的假设。

SQL范围查询。执行时间是否取决于表的大小？

考虑一个典型的来自单个表的范围查询。

SELECT Column1, …, ColumnN
FROM Table
WHERE Column BETWEEN X AND Y;

假设在Column ，有一个索引，最佳执行计划是

Get first row from IX_Column where Column between X and Y

While found and Column <= Y
  Fetch Table.* where rowAddress = IX_Column.rowAddress
  Write down Column1, …, ColumnN
  Get next row from IX_Column

让我们计算一下RDBMS为返回这些数据所要读取的块。

Get first row 部分是由我们在第二课中介绍的B树查询实现的。它所要读取的块的数量等于B树的深度。之后，我们从索引的叶层中读取后续的项目。

对于OLTP查询，通常所有的结果都会在一个索引块中找到（有时是两个，但很少有更多）。除此之外，对于每个索引条目，我们可以访问表中的一个块，根据其地址找到相应的行。有些表的行可能在我们已经加载的同一个表块内，但是为了简化估算，我们假设每次都加载一个新的表块。

所以公式是

B = D + 1 + R

B是读取的总块数，D是B树深度，R是查询返回的行数。

唯一取决于表中行数的参数是D，即B树的深度。

为了简化计算和说明问题，假设1000个索引条目适合于一个块。只要表中的行数少于1,000，D=1。对于持有商业交易的表来说，这可能是系统部署后的第一个工作日的情况。很快，B树的深度就会增加。只要表中的行数少于100万，索引将由两层组成。

如果我们被缓慢的数据库响应时间所困扰，并将此归咎于数据量，那么请注意，交易表往往只有几百万行的数据。因为只有100万行适合两级B树索引，所以深度必须至少是三。除非表内有超过10亿行，否则深度不会增长到4。现在我们有一个更精确的估计。

B = 4 + R

如果R很小，把B树的深度降回2，会大大加快查询的速度。当我们通过主键或唯一键值进行搜索时，系统将读取四个块而不是五个块，这是一个20%的改进。如果查询返回更多的行，改进可能就不明显了。问题是，对于许多应用来说，我们可能无法通过在数据库中保留少于100万个事务来支持必要的业务操作。

因此，结论似乎是，表的大小并不重要；换句话说，移动历史数据是在浪费时间和资源。

但不是这么快。让我们来了解一下B树索引的结构以及数据变化对它的影响。

B-树索引的实现细节

在第二课对B树索引的介绍中，我们看到平衡树的所有层次都是按关键列值（物理上）排序的。然而，当我们想插入、更新或删除一个项目时，往往要移动大量的数据来保持顺序。

假设我们在一个区块的中间插入，而这个区块刚好是满的。我们必须分割这个块，重新排列数据，有时甚至要更新另一个B树上的数据，指向当前的B树。

为了使这种情况更有效率，每个索引项都包含指向上一行和下一行的指针，使其成为双链接的。对于一般的插入，这意味着我们只需将新的项目尽可能地写到前一个项目上，并纠正指针。

当我们需要分割一个区块时，我们必须在前一个B树层写一个新的项目。这只是纠正几个指针的问题，不需要重写树的大部分内容。分割后，两个数据块大约都是半满的。根据磁盘上的空闲空间，"相邻 "的数据块在物理上可能是相当遥远的。

一段时间后，索引碎片会增加，查询执行的速度会变得很明显。在RDBMS以我们描述的方式执行查询时，对项目的顺序和邻近性的假设变得越来越不正确，导致了更多的读取。在最坏的情况下，由于所有的数据块都是半空的，系统必须要读取两倍的数据块。

B树的索引维护

解决这个问题的方法是索引碎片整理（或 "重新索引"）。每个RDBMS都提供了一个重新创建整个索引的功能；在重新索引之后，索引再次被物理排序。

重新索引是一个相当快的操作，尽管它读取和写入了大量的数据。现代RDBMS通常提供两种重索引模式，其中较快的模式需要在处理过程中锁定表。无论哪种方式，最好都是在非高峰期进行重索引。否则，处理可能会减慢数据库的性能。

删除历史数据

当我们有几十亿甚至上亿行的表时，在非高峰时段完成重新索引操作可能是不可行的。

为了避免这种情况，将历史数据从OLTP数据库中移出可能是一种解决办法。然而，如果我们只是简单地删除超过特定阈值的行，就会使索引变得更加零散，我们就需要更频繁地重新索引。

SQL 分区来拯救？

有一种方法可以避免因删除历史数据而造成的碎片化，在生产数据库中只保留 "活跃 "的事务。所有主要的RDBMS实现的想法是将一个表分成更小的块（称为分区），并提供添加、删除、甚至在表之间切换的能力（例如，从一个活动表到一个结构相同的历史表）。

让我们看看这个电子表格中的Reservations 表的分区情况。该表是按月划分的，分区名称被映射到日期段和其他电子表格。为了了解对分区表的查询是如何执行的，我们将做几个练习。

练习4：SQL中的分区查询

从上面链接的电子表格中，尝试提取以下查询所要求的数据--不使用任何索引。

SELECT HotelID, ReservationID, ClientID, DateFrom, DateTo
FROM Reservations
WHERE DateFrom BETWEEN TO_DATE('2021-03-01','YYYY-MM-DD') AND TO_DATE('2021-03-03');

你可能发现，你必须首先查看分区映射表，找到包含2021年3月的预订的分区。之后，你打开相应的分区，按顺序读取数据，并过滤掉不符合条件的行。

虽然直截了当，但你可能不喜欢在读取这么多行后保留这么少的行。读取三月的分区比读取整个预订表要好，但仍不理想。那么索引呢？

全局索引

RDBMS允许我们创建一个全局索引来覆盖一个分区表的所有分区。但是，全局索引和普通索引的工作方式在下面没有什么区别。全局索引并不具有分区意识。因此，使用全局索引的CRUD查询并不涉及其表的分区图。

我们只需要在放弃整个分区时更新分区图。然后，我们必须从索引中删除指向被删除的分区的任何行。这意味着整个全局索引需要重建。

一个停工窗口仍然是必要的，因为在过时的项目被删除之前，索引不能被使用。如果我们能够定期放弃分区，限制活动分区的数量，那么重建索引的操作就可能适合于停工窗口。因此，使用分区确实有助于解决原来的问题，因为它缩短了维护任务的时间，包括维护全局索引。

但是，如果我们仍然无法承受停工的后果呢？

全局性的分区索引

这个策略解决了这个问题：我们只需按照分割表的方式来分割索引。在分区电子表格链接的电子表格中，每个分区都包含Reservations 表的部分和一个叫做IX_DateFrom的索引表，两者都被DateFrom 。

为了执行练习4中的查询，RDBMS首先会查看一个索引分区图，并确定哪些分区包含范围内的日期。(在我们的例子中，这只是一个索引分区。)然后，它将使用B树查找，循环到叶层，最后使用相应的行地址访问表。

当我们从一个表中删除一个分区时，只需从索引中删除相应的分区即可。没有必要停机。

本地索引

全局分区索引的主要缺点是，我们必须注意同时丢弃表和相应的索引分区。从索引分区图中读取和维护索引分区图本身只需要一点额外的成本。

本地索引涉及一个类似但略有不同的方法。我们在每个表的分区中创建一个本地索引，而不是划分一个全局索引。这样一来，本地索引就分享了全局分区索引的主要优势--即没有停机时间--同时避免了它们的缺点。

这似乎是一个完美的解决方案。但是在我们庆祝之前，让我们研究一下几个查询的可能执行计划。

练习5:局部分割的索引

试着再次运行查询，这次使用DateFrom 上的本地分区索引。

你可能使用了这个执行计划。

For all partitions where [StartDateFrom, StartDateTo)
              intersects ['2021-03-01', '2021-03-03']
  Get first row from IX_DateFrom where DateFrom between '2021-03-01' and '2021-03-03'
  While found and DateFrom < '2021-03-04'
    Fetch Reservations.* where RowAddress = IX_DateFrom.RowAddress
    Write down HotelID, ReservationID, ClientID, DateFrom, DateTo
    Get next row from IX_DateFrom 

我们很幸运，所有的日期都属于一个分区，所以我们只需要遍历一个本地索引。如果这个时期是六个月，我们就必须要读取六个本地索引。

练习6:形成鲜明对比

你的任务是再次使用预订分区图，这次要列出客户124访问酒店1的时间段。

SELECT DateFrom, DateTo
FROM Reservations
WHERE ClientID = 124
  AND HotelID = 1;

这里我们可以看到本地索引的主要缺点。我们不得不从Reservations 表的每个分区中读取本地索引表IX_HotelID_CientID。

For all partitions
  Get first row from IX_HotelID_ClientID where ClientID = 124 and HotelID = 1
  While found and ClientID = 124 and HotelID = 1
    Fetch Reservations.* where RowAddress = IX_HotelID_ClientID.RowAddress
    Write down DateFrom, DateTo
    Get next row from IX_HotelID_ClientID

这种执行方式显然会比表不分区时读取更多的块，花费更多的时间。

因此，虽然我们确实找到了在非高峰期保持索引健康的方法，但这个策略也使我们的一些查询变得更慢。

如果我们的业务模式允许我们保留少量的分区，或者至少最频繁的查询包含允许RDBMS只读取一个或两个分区的标准，这个解决方案可能是我们需要的。否则，我们最好避免分区，并努力改善数据模型、索引和查询，以及加强数据库服务器。

SQL中的索引-接下来要学习什么

这就是我们旅程的终点。在《SQL索引详解》中，我重点介绍了所有现代RDBMS所共有的索引实现。我还关注了应用程序开发人员感兴趣的话题，而忽略了那些通常与数据库管理员有关的话题。后者最好研究一下填充因子对索引碎片的影响，但这两种角色的人都可能会发现进一步阅读相关内容是很有帮助的。

• 数据和索引缓存
• 非B树索引结构，如哈希、GiST、位图和列库索引
• 集群索引（在Oracle中被称为索引组织的表）
• 功能性索引
• 局部索引

我们讨论的分区方法是范围分区。这是最常用的分区类型，但是还有其他的分区，比如哈希分区和列表分区。另外，一些RDBMS提供了多级分区的选择。