使用 SQL 索引和分区解决瓶颈

已发表: 2022-03-11

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

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

我们还强调了在 SQL 中使用索引很有帮助的两个场景。当索引覆盖索引时，包含查询中的所有列——来自WHERE条件、 JOIN条件和SELECT列表——我们避免完全读取相应的表。或者，当索引将访问的数据块数量减少到表大小的一小部分时，它们会有所帮助。

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

SQL 范围查询

可以利用索引的查询通常包括显着减少一个或多个列可以采用的可能值范围的条件。范围查询根据“A 列的值必须在 X 和 Y 之间”之类的条件限制数据。

一个很好的例子是第二课练习 4 中的查询：

 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`在开头使用通配符

这次我们的侦探带着关于嫌疑人的模糊信息来到酒店：只是姓氏以“-son”结尾。侦探想要所有这些客人的名字和姓氏：

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

对于Clients表和LastName上的索引，我们将使用此电子表格。写下查询将返回的结果。想想你可以应用的不同方法。

表扫描方法

最简单的策略是读取表中的所有数据，并在客人的姓氏以“-son”结尾时记下他们的姓名：

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

在这里，我们必须按顺序读取整个表。

使用索引

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

事实证明，您必须阅读整个索引才能从表中找到所有 Andersons、Robinsons 和 Thompsons。这比使用表扫描更好吗？除了读取整个索引之外，您还必须使用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-”（罗伯茨）开头的第一个条目，读取后续行（罗伯茨和罗宾逊），并在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-tree 扩展问题

通常，当我们部署一个新数据库时，会有一些填充的查找表和空的事务表。系统从一开始就运行平稳，特别是如果我们通过规范化表来尊重数据库设计的良好实践；创建主键、外键和唯一键；并支持具有相应索引的外键。

几个月或几年后，当数据量显着增加系统和数据库的复杂性时，我们开始注意到性能下降。出现了关于为什么系统一直在变慢以及如何处理它的意见。

流行的观点通常认为数据库的大小是罪魁祸首。解决方案似乎是删除我们每天不需要的历史数据，并将其放在单独的数据库中进行报告和分析。

让我们首先探讨主要假设。

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-tree 查找实现。它必须读取的块数等于 B 树的深度。之后，我们从索引的叶级读取后续项。

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

所以公式是：

B = D + 1 + R

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

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

为了简化计算并说明问题，假设 1,000 个索引条目适合一个块。只要表中的行数少于 1,000，D = 1。对于保存业务事务的表，可能是系统部署后的第一个工作日。很快，B-tree 的深度就会增加。只要表中的行数少于 100 万，索引将由两个级别组成。

如果我们被缓慢的数据库响应时间所困扰并将其归咎于数据量，请注意事务表通常只有数百万行。由于只有 100 万行适合两级 B 树索引，因此深度必须至少为 3。除非表中的行数超过 10 亿，否则深度不会增长到四。现在我们有一个更精确的估计：

B = 4 + R

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

所以结论似乎是表大小无关紧要；换句话说，移动历史数据是浪费时间和资源。

但不是那么快：让我们更多地了解 B 树索引的结构以及数据更改如何影响它。

B-tree 索引实现细节

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

假设我们正在插入一个恰好已满的块的中间。我们必须拆分块，重新排列数据，有时甚至更新指向当前 B-tree 级别的数据。

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

当我们也需要拆分一个块时，我们必须在之前的 B-tree 级别中写入一个新项。这只是更正几个指针的问题——不需要重写树的大部分。拆分后，两个数据块都大约是半满的。根据磁盘上的可用空间位置，“相邻”块在物理上可能相距甚远。

一段时间后，索引碎片增加，查询执行速度变慢变得明显。随着 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 月以来预留的分区。然后，您打开相应的分区，顺序读取数据，并过滤掉不满足的行健康）状况。

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

全球索引

RDBMS 允许我们创建一个全局索引来覆盖分区表的所有分区。但是全局索引和常规索引在下面的工作方式没有区别：全局索引不支持分区。因此，使用全局索引的 CRUD 查询不涉及其表的分区映射。

我们只需要在删除整个分区时更新分区映射。然后，我们必须从索引中删除指向已删除分区的所有行。这意味着需要重建整个全局索引。

中断窗口仍然是必要的，因为在删除过时的项目之前无法使用索引。如果我们可以定期删除分区，限制活动分区的数量，那么重新索引操作可能适合中断窗口。因此，通过缩短维护任务（包括维护全局索引）所需的时间，使用分区确实有助于解决原始问题。

但是，如果我们仍然无法承受停电怎么办？

全局分区索引

这种策略解决了这个问题：我们只需像分区表一样对索引进行分区。在分区电子表格链接到的电子表格中，每个分区都包含它的Reservations表部分和一个名为 IX_DateFrom 的索引表，两者均由DateFrom分区。

要执行练习 4 中的查询，RDBMS 将首先查看索引分区映射并确定哪些分区包含范围内的日期。（在我们的例子中，它只是一个索引分区。）之后，它将使用 B-tree 查找，循环到叶级别，最后使用相应的行地址访问表。

当我们从表中删除一个分区时，从索引中删除相应的分区就足够了。无需停机。

本地索引

全局分区索引的主要缺点是我们必须同时删除表和相应的索引分区。读取和维护索引分区映射本身只需要一点额外成本。

本地索引涉及类似但略有不同的方法。我们不是对单个全局索引进行分区，而是在每个表分区内创建一个本地索引。这样做时，本地索引共享全局分区索引的主要优点——即没有停机时间——同时避免了它们的缺点。

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

练习 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：对比

您的任务是再次使用 Reservations 分区图，这次是列出客户 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 Indexes Explained 中，我重点介绍了所有现代 RDBMS 通用的索引实现。我还专注于应用程序开发人员感兴趣的主题，而忽略了通常与数据库管理员相关的主题。后者可以很好地研究填充因子对索引碎片的影响，但是两个角色的人可能会发现进一步阅读以下内容很有用：

数据和索引缓存
非 B 树索引结构，例如哈希、GiST、位图和列存储索引
聚集索引（在 Oracle 中称为索引组织表）
功能指标
部分索引

我们讨论的分区方法是范围分区。它是最常用的分区类型，但还有其他类型，例如散列分区和列表分区。此外，一些 RDBMS 提供多级分区选项。

最后，SQL 开发人员可以很好地探索围绕 RDBMS 查询执行的其他重要主题——首先是查询解析，然后是基于成本的执行计划编译、缓存和重用。

对于我使用过的四个 RDMBS，我推荐以下资源作为后续步骤：

甲骨文

优化器概述
索引和索引组织表
管理索引
分区概述
分区指南
问汤姆

PostgreSQL

查询处理
PostgreSQL 中的索引
PostgreSQL 中的索引（官方文档）
缓冲区管理
表分区
分区指南

微软 SQL 服务器

查询处理架构
索引
分区表和索引

MySQL/MariaDB

了解查询执行计划
优化和索引
分区 - 基础知识
分区 - 文档
MariaDB 文档：查询优化和索引