by Hayato Matsuura.

ASCDESCNULLS FIRST/LAST


データベースは、どちらの方向に向かってもインデックスを読むことができます。つまり、パイプライン化されたorder byは、スキャンされるインデックスの範囲がorder by句と全く反対の時にも可能になるということです。order by句の ASCDESC修飾語がパイプライン化された処理の 邪魔をしそうに思うかもしれませんが、多くのデータベースではインデックスの順序を変える方法を用意しており、パイプライン化されたorder byにインデックスを使えるようにできます。

次の例では、インデックスを逆順で使用し、日付の逆順および PRODUCT_IDの逆順に並べて、昨日までの売上を取り出します。

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date DESC, product_id DESC

実行計画にも、データベースはインデックスを降順に読んでいると出ています。

DB2
Explain Plan
---------------------------------------------------------------------
ID | Operation                      |                     Rows | Cost
 1 | RETURN                         |                          |  688
 2 |  FETCH SALES                   |     394 of 394 (100.00%) |  688
 3 |   IXSCAN (REVERSE) SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
 3 - STOP ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)

DB2では、インデックスの作成時にDISALLOW REVERSE SCAN句を使うことで、インデックスの逆順での使用を抑制できます。

Oracle
---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |  320 |  300 |
| 1 | TABLE ACCESS BY INDEX ROWID | SALES       |  320 |  300 |
|*2 |  INDEX RANGE SCAN DESCENDING| SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

この場合、データベースは最後に一致するエントリを探すのに、インデックスツリーを使います。にあるように、 「上に向って」リーフノードチェーンをたどるのです。これが、データベースがリーフノードチェーンを作るのに双方向連結リストを 使う理由です。

図逆順のインデックススキャン

もちろん、スキャンされるインデックスの範囲は、order by句と完全に逆順である必要があります。

重要

データベースは、インデックスをどちらの方向からも読むことができます。

次の例では、order by句にASCDESC修飾語が混在しているので、その条件を満たしていません。

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date ASC, product_id DESC

このクエリでは、PRODUCT_IDで降順に並んでいる 昨日の売上を返し、その後また、PRODUCT_IDで降順に並んでいる今日の売上を返さなくてはなりません。では、このプロセスを図示しています。 要求する順序で売上を返すには、データベースはインデックススキャン中に「ジャンプ」しなくてはなりません。

図不可能なパイプライン化されたorder by

インデックスは、昨日の売上で最小のPRODUCT_IDを持っている エントリから、今日の売上で最も大きいPRODUCT_IDを持っている エントリへのリンクは持っていません。そのためデータベースは、明示的なソート処理をしないためにインデックスを使うことはできません。

このような場合、多くのデータベースでは、order by句に インデックスの順序を合わせる簡単な方法が用意されています。具体的には、 インデックスの宣言時もASCDESCを使えます。

  DROP INDEX sales_dt_pr
CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC)

警告

バージョン8以前では、MySQLはインデックス定義内の ASCDESC修飾語を無視します

これで、インデックスの順序がorder byと一致したので、 データベースはソート処理をしなくてよくなりました。

DB2
Explain Plan
-----------------------------------------------------------
ID | Operation            |                     Rows | Cost
 1 | RETURN               |                          |  675
 2 |  FETCH SALES         |     387 of 387 (100.00%) |  675
 3 |   IXSCAN SALES_DT_PR | 387 of 1009326 (   .04%) |   24

Predicate Information
 3 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)
Oracle
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |  320 |  301 |
| 1 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  301 |
|*2 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

は、 新しいインデックスの順序の図です。2つ目の列に対するソートの方向が変わったので、前の図からは矢印の方向が変わっています。これにより、 最初の矢印は2つ目の矢印の始点を向くようになり、インデックスは希望の順序に並んでいることが分かります。

重要

order byASCDESCを 混在させる場合には、パイプライン化されたorder byを 使う場合と同じようにインデックスを定義する必要があります。

where句でのインデックスの使い方には影響はありません。

図順序が混在したインデックス

ASC/DESC付きのインデックスは、 各列をそれぞれ逆の方向にソートする場合にのみ必要になります。全ての列を逆順にする場合は、インデックスを逆にたどれるので、その必要はありません。 ただし、索引構成表セカンダリインデックスは例外です。 セカンダリインデックスは、ソート順序の指定があるかどうかに関係なくクラスタリングキーを インデックスに追加します。クラスタリングキーを逆順にソートする必要がある時、他の列も全て逆順に並べ替える以外の方法はありません。 データベースがインデックスを逆順に読み込むには、そうする他ないのです。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

ASCDESCに加えてSQL標準では、order by句に対して、あまり知られていないであろうNULLS FIRSTNULLS LAST修飾語を定義しています。 NULLのソートに対する明示的な制御は、「今のところ」 SQL:2003のオプション拡張として登場しています。 そのため、各データベースでのサポートは十分ではありません。標準ではNULLのソート順を明確に定義していないため、 この事は特に注意すべきと言えます。全てのNULLはソートしたらまとめて現われるべきであると書かれているだけで、 他のエントリの先に出てくるのか、後に出てくるのかは書かれていないのです。厳密に言うと、明確に定義された結果が欲しいなら、order by句でnullになり得る全ての列に対して、 NULLのソート順を定義してやる必要があります。

実際には、このオプショナルな拡張はSQL Server 2017でもMySQL 8.0でも 実装されていません。Oracleは標準にNULLSのソートが登場する前から これをサポートしていましたが、12cでは インデックス定義にこれを書く事はできません。Oracleでは、NULLS FIRSTでソートする場合には、パイプライン化されたorder byは使えません。PostgreSQL(8.3以降)だけは、order by句及びインデックス定義でのNULLS修飾語をサポートしています。

次の表は、この機能の各データベースでのサポート状況を示したものです。

この説明が気に入れば、きっとこの本も 気に入るはず。

図データベースごとの機能表

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy and GDPR | CC-BY-NC-ND 3.0 license