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

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

4. 消除“无意义”谓词

同样毫无意义的是（几乎）总是为真的谓词。正如你可以想象的，如果你问：

SELECT * FROM actor WHERE 1 = 1;

...那么数据库将不会实际执行它，而只是忽略它。我曾经在 Stack Overflow 上回答过一个有关此问题的问题，这就是我决定写这篇文章的原因。我将把这个测试作为练习留给读者，但是如果谓词稍微“无意义”一点，会发生什么？例如：

SELECT * FROM film WHERE release_year = release_year;

您真的需要将每一行的值与其自身进行比较吗？不，没有任何值会使该谓词为FALSE，对吧？但我们仍然需要检查一下。尽管谓词不能等于FALSE ，但由于三值逻辑，它很可能在任何地方都等于NULL 。RELEASE_YEAR列可为空，如果任何行具有RELEASE_YEAR IS NULL，则NULL = NULL会导致NULL，并且必须消除该行。于是请求就变成了下面这样：

SELECT * FROM film WHERE release_year IS NOT NULL;

哪些数据库可以做到这一点？

数据库2

是的！

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

遗憾的是，MySQL 并没有将谓词映射到执行计划中，因此弄清楚 MySQL 是否实现了这种特定的优化有点棘手。您可以执行性能评估并查看是否正在进行任何大规模比较。或者您可以添加索引：

CREATE INDEX i_release_year ON film (release_year);

并获得以下请求的计划作为回报：

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

如果优化有效，那么两个查询的计划应该大致相同。但在本例中，情况并非如此：

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

正如您所看到的，我们的两个查询在POSSIBLE_KEYS和FILTERED列 的值上存在显着差异。所以我大胆猜测 MySQL 没有对此进行优化。

甲骨文

是的！

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

很不幸的是，不行！

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

计划和成本各不相同。也就是说，看看基数的评估，这是绝对不行的，而这个谓词：

SELECT * FROM film WHERE release_year IS NOT NULL;

给出更好的结果：

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

真糟糕！

SQL服务器

奇怪的是，SQL Server 似乎也不这样做：

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

然而，根据计划的外观，基数评估是正确的，成本也是如此。但根据我使用 SQL Server 的经验，我会说在这种情况下不会发生优化，因为 SQL Server 会在计划中显示实际执行的谓词（要了解原因，请查看下面的CHECK约束示例）。NOT NULL列上的“无意义”谓词怎么样？上述转换是必要的，因为RELEASE_YEAR可能是未定义的。例如，如果您对FILM_ID列运行相同的无意义查询，会发生什么情况？

SELECT * FROM film WHERE film_id = film_id

现在它是否完全对应于任何谓词？或者至少应该是这样。但真的是这样吗？

数据库2

是的！

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

根本不应用任何谓词，我们选择所有电影。

MySQL

是的！（再次，有根据的猜测）

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

请注意，EXTRA列现在为空，就好像我们根本没有WHERE 子句一样！

甲骨文

是的！

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

同样，没有谓词适用。

PostgreSQL

哇，又没有了！

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

应用过滤器后，基数分数仍然是 5。真糟糕！

SQL服务器

再说一遍不！

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

概括

这看起来是一个简单的优化，但并不是所有的 DBMS 都使用它；特别是，奇怪的是，它没有在 SQL Server 中使用！

5. EXISTS 子查询中的投影

有趣的是，在我的大师班上，我总是被问到这些问题，我捍卫这样的观点：SELECT *通常不会带来任何好处。问题是：是否可以在EXISTS子查询中使用SELECT *？例如，如果我们需要找到在电影中扮演的演员......

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

答案是……是的。能。星号不会影响请求。你怎么能确定这一点？考虑以下查询：

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

所有这些数据库都报告除零错误。注意一个有趣的事实：在 MySQL 中，当我们除以零时，我们得到NULL而不是错误，所以我们必须做一些不允许的事情。现在，如果我们执行以下查询而不是上面的查询，会发生什么？

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

现在没有一个数据库返回错误。它们都返回TRUE或1。这意味着我们的数据库都没有实际评估EXISTS子查询的投影（即SELECT子句）。例如，SQL Server 显示了以下计划：

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

正如您所看到的，CASE表达式已转换为常量，并且子查询已被消除。其他数据库将子查询存储在计划中，并且没有提及任何有关投影的内容，因此我们再看一下 Oracle 中的原始查询计划：

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

上面的查询计划如下所示：

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

我们观察有关Id=3 处投影的信息。事实上，我们甚至不访问FILM_ACTOR表，因为我们不需要。EXISTS谓词可以使用单个ACTOR_ID列上的外键索引来执行- 该查询所需的一切 - 即使我们编写了SELECT *。

概括

幸运的是，我们所有的数据库都从EXISTS子查询中删除了投影： 请继续关注第 3 部分，我们将讨论其他很酷的 SQL 优化。