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

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

10. 述語のプッシュ

この最適化は、コスト モデルにまったく基づいていないとは言えないため、ここでは完全に適切ではありません。しかし、オプティマイザーが述語を派生テーブルにプッシュすべきではない理由が 1 つも思いつかないため、コスト以外の残りの最適化とともに、ここにその理由をリストします。次のリクエストを考えてみましょう。

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;

つまり、ACTOR.LAST_NAME列とCUSTOMER.LAST_NAME列の両方にインデックスがあるため、述語を派生テーブルにプッシュし、そこから 2 つのUNION ALLサブクエリにプッシュします。繰り返しになりますが、この変換はおそらくほとんどのデータベースのコスト見積もりに基づいていると思われますが、どのアルゴリズムでも、処理されるタプルの数をできるだけ早く減らす方がほとんどの場合良いため、これは簡単なことだと私は考えています。このような変換が間違ったアイデアであることが判明したケースをご存知の場合は、ぜひコメントをお待ちしています。とても興味があります。では、これを実行できるデータベースはどれでしょうか? (そして、これはとてもシンプルでとても重要なことです。答えは「すべて」とさせてください)

DB2

単純な派生テーブル はい

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「View」は、派生テーブルがまだ存在していることを示します。この場合、大きな問題はありませんが、おそらくわずかな追加コストがかかるだけです。

PostgreSQL

単純な派生テーブル はい、機能します:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

ただし、PostgreSQL は単一行の検索に主キーさえ使用せず、代わりにテーブル全体をスキャンする場合があることに注意してください。この場合、200 行 × 1 行あたり 25 バイト (「幅」) が 1 つのブロックに収まります。このような小さなテーブルにアクセスするために不必要な 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テーブルの方がはるかに大きい ため、 CUSTOMER.LAST_NAME列のインデックスが使用されます。

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 ユーザーを大いに失望させているのは、これら 2 つの人気のあるオープン ソース データベースは、商業的な競合他社である DB2、Oracle、SQL Server と比較すると、まだ長い道のりがあり、そのうち DB2 が最も優れたパフォーマンスを示しており、Oracle と SQL Server がほぼ次点となっています。首、少し後ろ。