不依賴成本模型的酷 SQL 最佳化。第 4 部分

不依賴成本模型的酷 SQL 最佳化。第 1 部分 不依賴成本模型的 Cool SQL 最佳化。第 2 部分 不依賴成本模型的酷 SQL 最佳化。第三部分

8.檢查限制

哦，這是很酷的東西！我們的Sakila資料庫對FILM.RATING列有CHECK約束：

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

認真地說，使用CHECK約束來確保資料完整性。添加它們的成本非常低 - 遠低於其他限制，例如PRIMARY、UNIQUE或FOREIGN KEY，因為它們不需要索引即可工作，因此您幾乎「免費」獲得它們。但有一個與優化相關的有趣的細微差別！考慮以下查詢：

不可能謂詞

我們已經遇到過不可能的謂詞，甚至是NOT NULL約束（這實際上是一種特殊的CHECK約束），但這個更酷：

SELECT *
FROM film
WHERE rating = 'N/A';

不存在這樣的電影，也不可能存在，因為CHECK約束阻止其插入（或更新）。同樣，這應該翻譯為不執行任何操作的命令。這個請求怎麼辦？

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

由於上面的索引，只需快速掃描索引併計算所有評級= 'NC-17' 的電影就足夠了，因為這是唯一剩下的評級。所以查詢應該要這樣重寫：

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

無論索引如何，情況都應該如此，因為將一列與一個值進行比較比與 4 個值進行比較要快。那麼，哪些資料庫可以做到這一點呢？

資料庫2

不可能的謂詞（評級 = 'N/A'） 酷！

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Predicate Information
 2 - RESID (1 = 0)

反向謂詞（評級=“NC-17”） 不...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

儘管 ID=3 步驟使用索引，且基數正確，但仍會發生完整掃描，因為該計劃沒有範圍謂詞，只有「SARG」謂詞。有關詳細信息，請參閱Marcus Wynand 的評論。您也可以透過手動反轉謂詞並取得來示範這一點：

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

現在我們有了所需的範圍謂詞。

MySQL

MySQL 支援CHECK約束語法，但由於某種原因不強制執行它們。嘗試這個：

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

你會得到：

A
-
0

MySQL 零分（真的，為什麼不只支援CHECK限制？）

甲骨文

不可能的謂詞（評級=“N/A”）

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

同樣，一個非常奇怪的過濾器NULL IS NOT NULL，切斷了FULL TABLE SCAN，它可以輕鬆地從計劃中完全刪除。但至少它有效！ 反向謂詞（評級 = 'NC-17'） 哎呀：

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

謂詞不能反轉，基數評估非常蹩腳，此外我們得到INDEX FAST FULL SCAN而不是INDEX RANGE SCAN，並且得到過濾謂詞而不是訪問謂詞。但這是我們應該得到的，例如，透過手動反轉謂詞：

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

真糟糕！

PostgreSQL

請注意，Sakila資料庫的PostgreSQL版本在RATING列上使用ENUM類型而不是CHECK約束。我使用CHECK約束來複製該表。 不可能的謂詞（評級 = 'N/A'） 不起作用：

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

反向謂詞（評級 = 'NC-17'） 也不起作用：

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

很抱歉！ 注意：正如David Rowley 在評論中向我們指出的那樣，可以透過設定參數來啟用此功能：

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

反向謂詞（評級 = 'NC-17'） 也是！

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

概括

9. 不必要的反射性連結。

隨著您的查詢變得更加複雜，您很可能最終需要根據表的主鍵對錶執行反射聯接。相信我，在建立複雜視圖並將它們相互連接時，這是一種非常常見的做法，因此確保資料庫專注於這一點是優化複雜 SQL 程式碼的關鍵部分。我不會示範一個複雜的例子，一個簡單的例子就足夠了，例如：

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

這可以看作是JOIN 消除的特殊情況，因為我們實際上不需要連接到A2，我們可以只用表A1來完成我們需要的一切。接下來，INNER JOIN 消除僅在存在 FOREIGN KEY 時才能正常工作，而我們這裡沒有。但是由於ACTOR_ID的主鍵，我們可以證明實際上A1 = A2。從某種意義上來說，這又是傳遞閉包。您可以更進一步，使用表A1和A2中的欄位：

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

在JOIN消除 的經典情況下，將不再可能消除它，因為兩個表都已投影。但由於我們已經證明A1 = A2，那麼它們是可以互換的，因此我們可以預期查詢會轉換為：

SELECT first_name, last_name
FROM actor;

什麼 DBMS 可以做到這一點？

資料庫2

僅表 A1 的投影 是：

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

表 A1 和 A2 的投影 ...也可以：

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

僅 表 A1 的投影

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

表 A1 和 A2 的投影 ...也沒有

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

徹底失望了…

甲骨文

僅表 A1 的投影 是

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

表 A1 和 A2 的投影 再次是

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

僅表 A1 的投影 否：

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

表 A1 和 A2 的投影 又否：

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

SQL伺服器

僅表 A1 的投影 奇怪的是，沒有！（但請記住，我使用的是 SQL Server 2014，較新的版本可能已修復此問題。我絕對可以使用升級！）

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

表 A1 和 A2 的投影 不再是這樣，計劃甚至變得更糟：

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

概括

坦白說，我預計這種優化會在所有資料庫上進行，但遺憾的是我大錯特錯了。除了消除 JOIN之外，這是最重要的最佳化之一，它允許您從視圖和表格函數等可重複使用部分建立大量 SQL 查詢。不幸的是，5 個最常見的資料庫中有 3 個不支援它。