联系方式
IBSurgeon
此页面为机器翻译。请阅读英文原文。 English

IBSurgeon 文库

Firebird 5.0.1 优化器改进

(c) D.Simonov, IBSurgeon, 21-Aug-2024

最近，Firebird 5.0 DBMS 发布了一个小版本更新 Firebird 5.0.1。除了修复错误之外，还新增了一个实验性的优化器功能，本文将对此进行讨论。

将子查询转换为半连接中的 ANY/SOME/IN/EXISTS

半连接是一种连接两个关系的操作，只返回其中一个关系的行，而不执行完整的连接。与其他连接运算符不同，没有显式语法来指定是否执行半连接。但是，您可以使用 ANY/SOME/IN/EXISTS 中的子查询来执行半连接。

传统上，Firebird 会将 ANY/SOME/IN 谓词中的子查询转换为 EXISTS 谓词中的相关子查询，并为外层查询的每条记录执行 EXISTS 中的子查询。在 EXISTS 谓词内执行子查询时，会使用 FIRST ROWS 策略，并且在返回第一条记录后立即停止执行。

从 Firebird 5.0.1 开始，ANY/SOME/IN/EXISTS 谓词中的子查询可以转换为半连接。此功能默认禁用，可以通过在 firebird.conf 或 database.conf 文件中将 SubQueryConversion 配置参数设置为 true 来启用。
此功能是实验性的，因此默认禁用。您可以启用它并测试带有 ANY/SOME/IN/EXISTS 谓词子查询的查询，如果性能更好，则保持启用状态，否则将 SubQueryConversion 参数恢复为默认值（false）。
SubQueryConversion 配置参数的默认值将来可能会更改，或者该参数可能会被完全移除。一旦新的执行方式在大多数情况下被证明更优，这种情况就会发生。

与直接在子查询上执行 ANY/SOME/IN/EXISTS（即作为相关子查询）不同，将它们作为半连接执行会提供更多的优化空间。半连接可以通过各种 Hash Join (semi) 或 Nested Loop Join (semi) 算法执行，而相关子查询总是为外层查询的每条记录执行。

让我们尝试通过在 firebird.conf 文件中将 SubQueryConversion 参数设置为 true 来启用此功能。现在让我们做一些实验。

让我们执行以下查询：

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2
  AND H.CODE_HORSE IN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  )
Code 
Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap And
                            -> Bitmap
                                -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                            -> Bitmap
                                -> Index "FK_HORSE_SEX" Range Scan (full match)
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
                                -> Bitmap
                                    -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552356752
Delta memory = 352
Max memory = 552567920
Elapsed time = 0.045 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 43984
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1516|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

在执行计划中，我们看到了一种新的连接方法 Hash Join (semi)。IN 中子查询的结果被缓冲了，这在计划中显示为 Record Buffer (record length: 41)。也就是说，在这种情况下，IN 中的子查询只执行了一次，其结果被保存在哈希表内存中，然后外层查询只是在这个哈希表中进行查找。

为了比较，让我们在禁用子查询到半连接转换的情况下运行相同的查询。

Code 
Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                    -> Bitmap
                        -> Index "FK_HORSE_SEX" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552046496
Delta memory = 352
Max memory = 552135600
Elapsed time = 0.395 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 186891
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |      297|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

执行计划显示，子查询为主查询的每条记录执行，但使用了额外的索引 FK_COVER_FATHER。这在执行统计中也很明显：Fetches 的数量多了 4 倍，执行时间几乎差了 4 倍。
读者可能会问：为什么哈希半连接显示 COVER 表的索引读取次数多了 5 倍，但其他方面却更好？事实是，统计中的索引读取显示的是使用索引读取的记录数，它们并不显示索引访问的总次数，其中一些访问根本没有检索到记录，但这些访问并不是免费的。

发生了什么？为了更好地理解子查询的转换，让我们引入一个虚构的半连接运算符 “SEMI JOIN”。正如我已经说过的，这种连接类型在 SQL 语言中没有表示。我们的带有 IN 运算符的查询被转换为一个等价形式，可以写成如下：

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2

现在更清楚了。对于使用 EXISTS 的子查询也会发生同样的情况。让我们看另一个例子：

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.CODE_MOTHER = H.CODE_MOTHER
  )

目前，无法使用 IN 来编写这样的 EXISTS。让我们看看在不转换为半连接的情况下它是如何实现的。

Code 
Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap And

-> 位图
                    -> 索引 "FK_COVER_MOTHER" 范围扫描（完全匹配）
                -> 位图
                    -> 索引 "FK_COVER_FATHER" 范围扫描（完全匹配）
选择表达式
    -> 聚合
        -> 过滤
            -> 表 "HORSE" 作为 "H" 按 ID 访问
                -> 位图
                    -> 索引 "FK_HORSE_DEPARTURE" 范围扫描（完全匹配）

                COUNT
=====================
                91908

当前内存 = 552240400
增量内存 = 352
最大内存 = 554680016
已用时间 = 19.083 秒
缓冲区 = 32768
读取 = 0
写入 = 0
获取 = 935679
每表统计：
--------------------------------+---------+---------+---------+---------+---------+
 表名                           | 自然    | 索引    | 插入    | 更新    | 删除    |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    91908|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

非常慢。现在让我们设置 SubQueryConversion = true 并重新运行查询。

Code 
选择表达式
    -> 聚合
        -> 过滤
            -> 哈希连接（半连接）
                -> 过滤
                    -> 表 "HORSE" 作为 "H" 按 ID 访问
                        -> 位图
                            -> 索引 "FK_HORSE_DEPARTURE" 范围扫描（完全匹配）
                -> 记录缓冲区（记录长度：49）
                    -> 过滤
                        -> 表 "COVER" 按 ID 访问
                            -> 位图
                                -> 索引 "FK_COVER_DEPARTURE" 范围扫描（完全匹配）

                COUNT
=====================
                91908

当前内存 = 552102000
增量内存 = 352
最大内存 = 561520736
已用时间 = 0.208 秒
缓冲区 = 32768
读取 = 0
写入 = 0
获取 = 248009
每表统计：
--------------------------------+---------+---------+---------+---------+---------+
 表名                           | 自然    | 索引    | 插入    | 更新    | 删除    |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |   140254|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

查询执行速度快了 100 倍！如果我们使用虚构的 SEMI JOIN 运算符重写它，查询将如下所示：

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT
      COVER.CODE_FATHER,
      COVER.CODE_MOTHER
    FROM COVER
  ) TMP ON TMP.CODE_FATHER = H.CODE_FATHER AND TMP.CODE_MOTHER = H.CODE_MOTHER
WHERE H.CODE_DEPARTURE = 1

IN/EXISTS 中的任何相关子查询都能转换为半连接吗？不，并非所有，例如，如果子查询包含 FETCH/FIRST/SKIP/ROWS 过滤条件，则该子查询无法转换为半连接，它将作为相关子查询执行。以下是此类查询的示例：

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_HORSE
    OFFSET 0 ROWS
  )

这里的短语 OFFSET 0 ROWS 不会改变查询的语义，其执行结果与没有它时相同。让我们看看此查询的计划和统计信息。

Code 
子查询
    -> 跳过 N 条记录
        -> 过滤
            -> 表 "COVER" 按 ID 访问
                -> 位图
                    -> 索引 "FK_COVER_FATHER" 范围扫描（完全匹配）
选择表达式
    -> 聚合
        -> 过滤
            -> 表 "HORSE" 作为 "H" 按 ID 访问
                -> 位图
                    -> 索引 "FK_HORSE_DEPARTURE" 范围扫描（完全匹配）

                COUNT
=====================
                10971

当前内存 = 551912944
增量内存 = 288
最大内存 = 552002112
已用时间 = 0.201 秒
缓冲区 = 32768
读取 = 0
写入 = 0
获取 = 408988
每表统计：
--------------------------------+---------+---------+---------+---------+---------+
 表名                           | 自然    | 索引    | 插入    | 更新    | 删除    |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

如您所见，未发生到半连接的转换。现在让我们移除 OFFSET 0 ROWS 并再次获取统计信息。

Code 
选择表达式
    -> 聚合
        -> 过滤
            -> 哈希连接（半连接）
                -> 过滤
                    -> 表 "HORSE" 作为 "H" 按 ID 访问
                        -> 位图
                            -> 索引 "FK_HORSE_DEPARTURE" 范围扫描（完全匹配）
                -> 记录缓冲区（记录长度：33）
                    -> 表 "COVER" 全扫描

                COUNT
=====================
                10971

当前内存 = 552112128
增量内存 = 288
最大内存 = 585044592
已用时间 = 0.405 秒
缓冲区 = 32768
读取 = 0
写入 = 0
获取 = 854841
每表统计：
--------------------------------+---------+---------+---------+---------+---------+
 表名                           | 自然    | 索引    | 插入    | 更新    | 删除    |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |   722465|         |         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

这里发生了到半连接的转换，正如我们所见，执行时间变得更差了。原因是目前优化器在 Hash Join (semi) 和使用索引的 Nested Loop Join (semi) 连接算法之间没有成本估算，因此规则是：如果连接条件仅包含相等比较，则选择 Hash Join (semi) 算法，否则 IN/EXISTS 子查询将照常执行。

现在让我们禁用半连接转换并查看执行统计信息。

Code 
子查询
    -> 过滤
        -> 表 "COVER" 按 ID 访问
            -> 位图
                -> 索引 "FK_COVER_FATHER" 范围扫描（完全匹配）
选择表达式
    -> 聚合
        -> 过滤
            -> 表 "HORSE" 作为 "H" 按 ID 访问
                -> 位图
                    -> 索引 "FK_HORSE_DEPARTURE" 范围扫描（完全匹配）

                COUNT
=====================
                10971

当前内存 = 551912752
增量内存 = 288
最大内存 = 552001920
已用时间 = 0.193 秒
缓冲区 = 32768
读取 = 0
写入 = 0
获取 = 408988
每表统计：
--------------------------------+---------+---------+---------+---------+---------+
 表名                           | 自然    | 索引    | 插入    | 更新    | 删除    |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

如您所见，Fetches 与子查询包含 OFFSET 0 ROWS 子句时的情况完全相同，执行时间在误差范围内。这意味着您可以使用 OFFSET 0 ROWS 子句作为提示来禁用半连接转换。

现在让我们看看在子查询中使用除相等和 IS NOT DISTINCT FROM 之外的任何相关条件的情况。

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.BYDATE > H.BIRTHDAY
  )
Code 
Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap
                -> Index "COVER_IDX_BYDATE" Range Scan (lower bound: 1/1)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

如上所述，没有发生到半连接的转换，子查询对主查询的每条记录都会执行。

让我们继续实验，编写一个使用等值和一个除等值之外的谓词的查询。

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
  )
Code 
Select Expression
    -> Aggregate
        -> Nested Loop Join (semi)
            -> Filter
                -> Table "HORSE" as "H" Access By ID
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
            -> Filter
                -> Filter
                    -> Table "COVER" Access By ID
                        -> Bitmap
                            -> Index "COVER_IDX_BYDATE" Range Scan (lower bound: 1/1)

在这里的计划中，我们第一次看到了 Nested Loop Join (semi) 连接方法的使用，但不幸的是这个计划很糟糕，因为没有使用 FK_COVER_FATHER 索引。这样的查询不会得到任何结果。这可以通过使用 OFFSET 0 ROWS 提示来修复。

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
    OFFSET 0 ROWS
  )
Code 
Sub-query
    -> Skip N Records
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                72199

Current memory = 554017824
Delta memory = 320
Max memory = 554284480
Elapsed time = 45.548 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 84145713
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         | 75894621|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

执行时间不是最佳，但至少在这种情况下我们得到了结果。

因此，将子查询转换为 ANY/SOME/IN/EXISTS 为半连接，在某些情况下可以显著加快查询执行速度，但目前该功能仍不完善，因此默认是禁用的。在 Firebird 6.0 中，他们将尝试为该功能添加成本估算，并修复其他一些缺陷。此外，Firebird 6.0 计划添加将 ALL/NOT IN/NOT EXISTS 子查询转换为反连接的功能。

在总结 IN/EXISTS 中子查询执行的回顾时，我想指出，如果你有一个如下形式的查询

Code 
SELECT ...
FROM T1
WHERE  IN (SELECT field FROM T2 ...)

或者

Code 
SELECT ...
FROM T1
WHERE EXISTS (SELECT ... FROM T2 WHERE T1. = T2.field)

那么这样的查询几乎总是更高效地执行为

Code 
SELECT ...
FROM
  T1
  JOIN (SELECT DISTINCT field FROM T2) tmp ON tmp.field = T1.

让我给你一个清晰的例子：

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_HORSE IN (
  SELECT
    CODE_FATHER
  FROM COVER
  WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
)

使用 Hash Join (semi) 的执行计划和统计信息

Code 
Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Table "HORSE" as "H" Full Scan
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap
                                -> Index "IDX_COVER_BYYEAR" Range Scan (full match)

                COUNT
=====================
                 1616

Current memory = 554176768
Delta memory = 288
Max memory = 555531328
Elapsed time = 0.229 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 569683
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

相当快，但 HORSE 表被全表读取。

使用经典子查询执行的执行计划和统计信息

Code 
Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Full Scan

                COUNT
=====================
                 1616

Current memory = 553472512
Delta memory = 288
Max memory = 553966592
Elapsed time = 6.862 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 2462726
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1616|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

非常慢。HORSE 表是全表扫描，子查询被多次执行 - 对 HORSE 表中的每条记录执行一次。

现在是一个使用 DISTINCT 的快速选项

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
  JOIN (
      SELECT
        DISTINCT
        CODE_FATHER
      FROM COVER
      WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE
Code 
Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Unique Sort (record length: 44, key length: 12)
                -> Filter
                    -> Table "COVER" as "TMP COVER" Access By ID
                        -> Bitmap
                            -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
            -> Filter
                -> Table "HORSE" as "H" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan

                COUNT
=====================
                 1616

Current memory = 554349728
Delta memory = 320
Max memory = 555531328
Elapsed time = 0.011 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 14954
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |

--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |         |     1616|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

无需多余的读取，查询执行得非常快。因此结论是–始终查看 IN/EXISTS/ANY/SOME 中子查询的执行计划，并检查编写查询的替代变体。