不依賴成本模型的酷 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 大致不相上下。脖子，靠後一點。