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

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

4. 「意味のない」述語の削除

同様に無意味なのは、（ほぼ）常に真となる述語です。ご想像のとおり、次のように尋ねるとします。

SELECT * FROM actor WHERE 1 = 1;

...この場合、データベースは実際にはそれを実行せず、単に無視します。以前 Stack Overflow でこれに関する質問に回答したことがあり、この記事を書くことにしました。これを演習としてテストするのは読者に任せますが、述語がもう少し「無意味」であればどうなるでしょうか? 例えば：

SELECT * FROM film WHERE release_year = release_year;

本当に各行の値をそれ自体と比較する必要がありますか? いいえ、この述語がFALSEとなる値はありません。しかし、まだ確認する必要があります。述語をFALSEに等しくすることはできませんが、やはり 3 値ロジックにより、どこでもNULLに等しくなる可能性があります。RELEASE_YEAR列はNULL 可能で、いずれかの行にRELEASE_YEAR IS NULLがある場合、NULL = NULLの結果はNULLとなり、その行は削除する必要があります。したがって、リクエストは次のようになります。

SELECT * FROM film WHERE release_year IS NOT NULL;

どのデータベースがこれを行うのでしょうか?

DB2

はい！

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

ご覧のとおり、2 つのクエリは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

これは述語にまったく対応しないのでしょうか? あるいは少なくともそうあるべきです。しかし、そうですか？

DB2

はい！

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

WHERE 句がまったくないかのように、EXTRA列が空になっている ことに注目してください。

オラクル

はい！

----------------------------------------------------
| 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述語は、SELECT *を記述した場合でも、単一ACTOR_ID列の外部キー インデックスを使用して実行できます(このクエリに必要なのはすべてです) 。

まとめ

幸いなことに、私たちのデータベースはすべてEXISTSサブクエリから射影を削除します。 パート 3では、その他の優れた SQL 最適化について説明します。