コスト モデルに依存しない優れた SQL 最適化。パート 4

コスト モデルに依存しない優れた SQL 最適化。パート 1 コスト モデルに依存しない優れた SQL 最適化。パート 2 コスト モデルに依存しない優れた SQL 最適化。パート 3

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';

列を 1 つの値と比較する方が、4 つの値と比較するよりも高速であるため、これはインデックスに関係なく当てはまります。では、どのようなデータベースがこれを実現できるのでしょうか?

DB2

不可能な述語 (評価 = '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 RANGE SCANの代わりにINDEX FAST FULL 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

PostgreSQLバージョンのSakelaデータベースでは、 RATING列に対してCHECK制約の代わりにENUMタイプが使用されることに注意してください。代わりに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;

これは、実際にはA2への結合が必要ないため、テーブルA1だけで必要なすべてを実行できるため、 JOIN 除去の 特殊なケースとして見ることができます。次に、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 は何ですか?

DB2

テーブル 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のみの投影 No.

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 の削除と同様に最も重要な最適化の 1 つであり、ビューやテーブル関数などの再利用可能な部分から巨大な SQL クエリを構築できるようになります。残念ながら、最も一般的な 5 つのデータベースのうち 3 つではサポートされていません。