In the first lesson 解释了SQL索引，我们了解到 SELECT 当数据已经按特定列的值排序时，查询速度会更快.

In the second lesson, 我们学习了b树索引的基本结构，以及如何使用它们来减少我们在执行查询时访问的数据量. 我们还了解了如何实现连接多个表的查询，以及索引如何加快此类查询的速度.

我们还强调了在SQL中使用索引很有帮助的两个场景. 当索引覆盖索引时，包含来自查询的所有列 WHERE conditions, JOIN conditions, and the SELECT 列表—完全避免读取相应的表. Alternatively, 当索引将访问的数据块数量减少到表大小的一小部分时，索引可以提供帮助.

Otherwise, 扫描整个表要比从索引中读取并随机地前后跳转到相应的表行更有效.

SQL Range Queries

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

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

SELECT c.ClientName
来自预订部
JOIN Clients c ON r.ClientID = c.ClientID
WHERE r.DateFrom BETWEEN (
    To_date ('2020-08-13'， ' yyyy-mm-dd ')和
    TO_DATE (' 2020-08-14 ', ' YYYY-MM-DD ')
  ) AND
  r.HotelID = 3;

这里有两个范围. 第一个是日期范围，从2020年8月13日到2020年8月14日. 第二个是尽可能小的数值范围. 条件等于 r.旅馆号码3点到3点之间.

练习1:期间(日期及时间范围查询)

我们添加一个列 CheckInTime to the Reservations table. 您可以在 this spreadsheet. 注意到有一个索引覆盖了两者 CheckInTime and ClientId.

编写一个查询，返回在2020年8月15日登记入住的客户的姓名.

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

SELECT c.ClientName
来自预订部
JOIN Clients c ON r.ClientID = c.ClientID
WHERE TO_DATE(r.CheckInTime， 'YYYY-MM-DD') = '2020-08-15';

他们假设查询执行看起来像这样:

从IX_CheckInTime_ClientID中获取第一行
  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
  从IX_CheckInTime_ClientID获取下一行

问题是，在撰写本文时，没有一个RDBMS能够生成这样的执行计划. They see TO_DATE (Oracle语法)作为转换列值的函数 CheckInTime 变成没有索引的东西. 所以，它们生成的执行计划是这样的:

对于IX_CheckInTime_ClientID中的每一行
  如果TO_DATE(CheckInTime， 'YYYY-MM-DD') = '2020-08-15'，则
    Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID
    Write down Clients.ClientName

中读取所有行要快得多 Reservations 表，因为索引行比表行窄. 更小的行意味着需要从磁盘访问的块更少.

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

SELECT c.ClientName
来自预订部
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而不是由它衍生出来的东西属于定义明确的范围. 它生成的执行计划更像是:

从IX_CheckInTime_ClientID中获取第一行
  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
  从IX_CheckInTime_ClientID获取下一行

这就是我们真正想要的:不仅要利用索引本身，还要利用它已经排序的事实.

Exercise 2: LIKE 在开始时使用通配符

这一次，我们的侦探带着关于嫌疑犯的模糊信息来到酒店:只知道他的姓以“-儿子”结尾.“侦探要知道所有这些客人的姓和名。

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

For the Clients 表和索引 LastName, we’ll use this spreadsheet. 写下查询将返回的结果. 想想你可以采用的不同方法.

Table Scan Approach

最简单的策略是从表中读取所有数据，并写下以“-son”结尾的客人的名字:

对于来自客户机的每一行
  如果LastName像'%son'，那么写下FirstName, LastName

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

Using an Index

让我们试着利用索引 LastName column. 转到IX_LastName表, 用它来找到满足给定标准的所有客户, 写下他们的名字.

事实证明，你必须阅读整个索引才能找到所有的安德森家族, Robinsons, 和汤普森一起离开桌子. 这比使用表扫描更好吗? 除了读取整个索引, 你还必须找到, 对于每个匹配条目, 从表中获取相应的行 rowAddress 值，然后写下来 FirstName from there:

对于IX_LastName中的每一行
  如果LastName像'%son'那么
    Fetch Clients.* where RowAddress = IX_LastName.RowAddress
    写下名字，姓氏

对我们来说，按顺序读取表更简单、更快. 对于我们的RDBMS，它将取决于满足条件的行百分比. 如果有几个安德森的话, Robinsons, 汤普森坐在一张大桌子上, RDBMS将从更窄的索引项中读取更少的数据块，即使在找到匹配时必须从表中读取几个块. 否则，表扫描花费的时间更少.

对索引中的数据排序不会对此类查询提供任何帮助. 较小的索引行可能会有帮助，但只是有时.

Exercise 3: LIKE 以通配符结尾

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

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

尝试从同一电子表格中提取与查询匹配的数据.

如果使用表扫描方法，就错过了充分利用索引的机会 IX_LastName. 查找索引中以“Rob-”(Roberts)开头的第一个条目要快得多。, 读取后续行(Robertses和Robinsons), and stop when the LastName 不再符合标准的:

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

While found and LastName < 'Roc' 
  Fetch Clients.* where rowAddress = IX_LastName.rowAddress
  写下名字，姓氏
  从IX_LastName获取下一个

In this case, 在b树查找第一个条目之后, 我们只读取满足标准的条目. 一旦读取到不符合标准的名称，我们就停止读取.

解决b树扩展问题

通常在部署新数据库时, 有一些已填充的查找表和空的事务表. 这个系统从一开始就运行得很顺利, especially if we respected good practices of database design by normalizing tables; creating primary, foreign, and unique keys; and supporting foreign keys with corresponding indexes.

几个月或几年之后, 当数据量显著增加时，系统和数据库的复杂性也随之增加, 我们开始注意到性能下降. 关于系统运行速度放缓的原因以及该如何应对的意见层出不穷.

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

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

SQL范围查询:执行时间取决于表大小吗?

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

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

假设有一个索引 Column时，最优执行计划为:

从IX_Column中获取第一行，其中Column位于X和Y之间

While found and Column <= Y
  Fetch Table.* where rowAddress = IX_Column.rowAddress
  写下列1，…，列n
  从IX_Column获取下一行

让我们计算一下RDBMS必须读取哪些块才能返回这些数据.

The Get first row 部分由b树查找实现 introduced 在第二课中. 它需要读取的数据块数量等于b树的深度. 之后，我们从索引的叶级读取后续项.

With OLTP queries, 通常所有结果都在一个索引块中找到(有时是两个，但很少有更多)。. In addition to that, 对于每个索引项, 我们可以访问表中的块，根据它的地址找到相应的行. 有些表行可能在我们已经加载的同一个表块中, 但是为了简化估计, 让我们假设每次都加载一个新块.

So the formula is:

B = D + 1 + R

B是读取的块总数, D是b树深度, R是查询返回的行数.

唯一依赖于表中行数的参数是D，即b树深度.

为了简化计算和说明问题，假设一个块中包含1,000个索引项. D = 1，只要表中的行数少于1000行. 对于保存业务事务的表, 系统部署后的第一个工作日可能就是这种情况. 很快，b树的深度就会增加. 只要表中的行数少于100万，索引将由两个级别组成.

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

B = 4 + R

如果R很小，将b树深度降低回2将显著加快查询速度. 当我们搜索主键或唯一键值时, 系统将读取四个块而不是五个, 哪一个是20%的改进. 如果查询返回更多行，则改进可能不明显. The problem is, 对于许多应用程序, 如果在数据库中保留少于100万个事务，我们可能无法支持必要的业务操作.

So the conclusion seems to be that table size does not matter; in other words, 移动历史数据是对时间和资源的浪费.

但不要这么快:让我们更多地了解b树索引的结构以及数据更改如何影响它.

B-tree索引实现细节

在我们第二课对b树索引的介绍中, 我们看到平衡树的所有级别都是按键列值(物理上)排序的. However, 当我们想插入的时候, update, or delete an item, 通常我们必须移动大量数据来保持顺序.

假设我们要插入一个刚好满了的块的中间. 我们得把街区分开, rearrange the data, 有时甚至更新指向当前b树的另一个b树级别的数据.

使这类案件更有效率, 每个索引项包含指向上一行和下一行的指针, 使其成为双链接. 一般的插入, 这意味着我们只需将新条目写得尽可能靠近前一个条目并更正指针.

当我们也需要拆分一个块时，我们必须在之前的b树级别中写入一个新项. 这只是更正几个指针的问题—不需要重写树的大部分. 在分割之后，两个数据块大约都是半满的. 这取决于磁盘上的可用空间, “相邻”的街区在物理上可能相当遥远.

一段时间后，索引碎片增加，查询执行速度明显减慢. RDBMS按照我们描述的方式执行查询, 对物品的顺序和接近程度的假设变得越来越不正确, 导致更多的阅读. In the worst case, 所有数据块都是半空的, 系统必须读取两倍的块.

b树索引维护

解决这个问题的方法是索引碎片整理(或“重新索引”)。. Every RDBMS provides a feature to recreate an entire index; after reindexing, 索引再次按物理顺序排列.

驯鹿索引是一个相当快的操作，即使它读取和写入大量的数据. 现代rdbms通常提供两种索引模式, 快一点的要求在处理过程中锁住表. 无论哪种方式，最好在非高峰时间重新索引. 否则，处理过程可能会降低数据库性能.

删除历史数据

当我们有数十亿甚至数亿行的表时, 在非高峰时段完成驯鹿索引操作可能不可行.

为了避免这种情况，移动历史数据 从OLTP数据库中取出 也许是解决办法. However, 如果我们简单地删除超过特定阈值的行, 我们使索引更加分散, 我们需要更频繁地重新索引它们.

SQL分区救援?

有一种方法可以避免删除历史数据造成的碎片, 在生产数据库中只保留“活动”事务. 所有主要的rdbms实现的思想都是将表分成更小的块(称为 partitions)，并提供添加它们的功能, removing them, 甚至在表格之间切换(e.g.(从活动表到具有相同结构的历史表).

让我们来看看 Reservations 被分区的表 this spreadsheet. 该表按月划分，分区名称映射到日期周期和其他电子表格. 为了了解如何执行分区表上的查询，我们将做一些练习.

练习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月以来的预订的分区. After that, 您打开了相应的分区, 按顺序读取数据, 然后过滤掉不满足条件的行.

虽然很简单，但您可能不喜欢在阅读了这么多行之后保留这么少的行. 读取March分区比读取整个预订表要好，但仍然不理想. What about indexes?

Global Indexes

RDBMSs 允许我们创建一个 global index 以覆盖分区表的所有分区. 但是全局索引和常规索引在底层的工作方式没有区别:全局索引不支持分区. 因此，使用全局索引的CRUD查询不涉及其表的分区映射.

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

停机窗口仍然是必要的，因为在删除过时的项之前不能使用索引. 如果我们能定期删除分区, 限制活动节点的数量, 然后重新索引操作可能适合中断窗口. 因此，通过缩短维护任务所需的时间，使用分区确实有助于解决最初的问题, 包括维护全局索引.

但如果我们还是负担不起停电的费用怎么办?

全局分区索引

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

执行练习4中的查询, RDBMS首先查看索引分区映射，并确定哪些分区包含范围内的日期. (在我们的例子中，它只是一个索引分区.) After that, 它将使用b树查找, 循环到叶片水平, 最后使用相应的行地址访问表.

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

Local Indexes

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

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

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

练习5:局部分区索引

尝试再次运行查询，这次使用本地分区索引 DateFrom.

您可能使用了以下执行计划:

对于所有包含[StartDateFrom, StartDateTo)的分区
              交叉点['2021-03-01'，'2021-03-03']
  获取IX_DateFrom在' 20121-03-01 '和' 20121-03-03 '之间的第一行
  While found and DateFrom < '2021-03-04'
    Fetch Reservations.* where RowAddress = IX_DateFrom.RowAddress
    写下HotelID, ReservationID, ClientID, DateFrom, DateTo
    从IX_DateFrom获取下一行 

幸运的是，所有日期都属于一个分区，因此我们只需要遍历一个本地索引. 如果周期为6个月，我们将不得不读取6个本地索引.

练习6:对比

您的任务是再次使用reservation分区映射, 这一次要列出客户124访问酒店1的时间段:

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

这里我们可以看到本地索引的主要缺点. 的每个分区中读取本地索引表IX_HotelID_CientID Reservations table:

For all partitions
  从IX_HotelID_ClientID获取第一行，其中ClientID = 124且HotelID = 1
  当找到并且ClientID = 124和HotelID = 1时
    Fetch Reservations.* where RowAddress = IX_HotelID_ClientID.RowAddress
    写下DateFrom, DateTo
    从IX_HotelID_ClientID获取下一行

显然，与表未分区相比，此执行将读取更多块并花费更多时间.

因此，虽然我们确实找到了一种在非高峰时期保持指数健康的方法, 该策略还使我们的一些查询变慢了.

如果我们的业务模型允许我们保留少量分区，或者至少最频繁的查询包含允许RDBMS读取一个或两个分区的条件, 这个解决方案可能就是我们需要的. Otherwise, 我们最好避免分区，并致力于改进数据模型, indexes, 查询和增强数据库服务器.

SQL中的索引:下一步该学什么

我们的旅程到此结束. 在《欧博体育app下载》中，我重点介绍了所有现代rdbms都通用的索引实现. 我还关注了一些话题 应用程序开发人员 对通常与数据库管理员有关的主题不感兴趣. 后者可以很好地研究填充因子对索引碎片化的影响, 但这两种角色的人可能会发现进一步阅读以下内容很有用:

• 数据和索引缓存
• 非b树索引结构，如散列、GiST、位图和columnstore索引
• 聚集索引(称为 index-organized表 in Oracle)
• Functional indexes
• Partial indexes

我们讨论的划分方法是 range partitioning. 这是最常用的分区类型, 但是还有其他的, 比如哈希分区和列表分区. 另外，一些rdbms还提供了多级分区的选项.

Lastly, SQL developers 探究RDBMS查询执行优先的其他重要主题会更好吗, query parsing, 然后编制基于成本的执行计划, caching, and reuse.

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

Oracle

• 优化器概述
• 索引和索引组织表
• Managing Indexes
• 分区概述
• Partitioning Guide
• Ask Tom

PostgreSQL

• Query Processing
• PostgreSQL中的索引
• PostgreSQL中的索引(官方文档)
• Buffer Management
• Table Partitioning
• Partitioning Guide

Microsoft SQL Server

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

MySQL/MariaDB

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