不依赖于成本模型的酷 SQL 优化。第五部分

不依赖于成本模型的酷 SQL 优化。第 1 部分 不依赖于成本模型的 Cool SQL 优化。第 2 部分 不依赖于成本模型的酷 SQL 优化。第 3 部分 不依赖于成本模型的 Cool SQL 优化。第 4 部分

10. 推送谓词

这种优化在这里并不完全合适，因为不能说它完全不基于成本模型。但由于我想不出优化器不应将谓词推入派生表的任何原因，因此我将在此处列出它以及其余的非成本优化。考虑请求：

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

此查询中的派生表没有任何意义，应通过减少查询嵌套级别数来消除。但现在我们先忽略这一点。您可以期望数据库执行以下查询而不是上面的查询：

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

然后，也许再次消除外部请求。使用UNION获得了一个更复杂的示例：

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

该查询的结果：

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

现在，如果数据库优化器能够运行如下查询，那就太好了：

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

也就是说，它将谓词推入派生表，并从那里推入两个UNION ALL子查询，因为毕竟我们在ACTOR.LAST_NAME列和CUSTOMER.LAST_NAME列上都有索引。同样，这种转换可能基于大多数数据库中的成本估计，但我仍然认为这是理所当然的，因为对于任何算法，尽早减少处理的元组数量几乎总是更好。如果您知道这样的转变被证明是一个坏主意的案例，我将很高兴听到您的评论！我会很感兴趣。那么我们的哪个数据库可以做到这一点？（拜托，这是如此简单又如此重要，让答案是：一切）

数据库2

简单派生表 是

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

与 UNION 的派生表 也可以：

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

此外，在这两种情况下，派生表（视图）都被排除在计划之外，因为实际上并不需要它。

MySQL

简单派生表 是

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

使用通常通过常量值访问的主键。 带有 UNION 的派生表 哎呀，没有。

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

计划中的手动转换结果：

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

在MySQL中使用复杂的嵌套查询时这是一个严重的问题！

甲骨文

简单的派生表 是的，它有效。

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

并且嵌套层数也减少了。 使用 UNION 的派生表 也适用：

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

然而，并没有减少嵌套层数。id=1“查看”显示派生表还在。在这种情况下，这不是一个大问题，可能只是一个小的额外成本。

PostgreSQL

简单的派生表 是的，它有效：

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

但请注意，PostgreSQL 有时甚至不使用主键来查找单行，而是扫描整个表。在这种情况下，200 行 × 每行 25 字节（“宽度”）适合一个块，那么除了生成不必要的 I/O 操作来访问这么小的表之外，还有什么必要费心索引读取呢？ 使用 UNION 的派生表 是的，它也有效：

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

同样，不使用ACTOR.LAST_NAME列 上的索引，但使用CUSTOMER.LAST_NAME列上的索引，因为CUSTOMER表要大得多。

SQL服务器

简单的派生表 是的，它有效

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

使用 UNION 的派生表 也适用。

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

概括

我的希望没有实现。MySQL 8.0.2 尚未完全支持这种简单的优化。然而，其他人都支持。

结论

这里列出的列表还远未完成。即使在成本优化器介入之前，还有许多其他简单的 SQL 转换对于数据库来说实现起来并不困难（或不应该）。它们消除了[数据库]不必要的额外工作（而不是我已经写过的不必要的、必需的工作）。 这些是重要的工具：

1. 愚蠢的[开发人员]错误对性能没有影响。错误是不可避免的，随着项目的增长和 SQL 查询变得更加复杂，这些错误可能会累积，但愿不会产生任何影响。


2. 提供重用复杂块（例如视图和表函数）的能力，这些块可以嵌入到父 SQL 查询中、进行转换、部分删除或重写。

这些能力对于第 2 点至关重要。如果没有它们，基于可重用 SQL 组件库创建具有正常性能的 4000 行 SQL 查询将非常困难。令 PostgreSQL 和 MySQL 用户失望的是，这两个流行的开源数据库与商业竞争对手 DB2、Oracle 和 SQL Server 相比还有很长的路要走，其中 DB2 的表现最好，Oracle 和 SQL Server 大致不相上下。脖子，靠后一点。