不依賴成本模型的酷 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 優化。