by Hayato Matsuura.

最初のN行のみの選択


最初のN行のみ選択するクエリとは、結果を一定の行数だけに限定するクエリです。 これは、結果セットの中から最新あるいは「最高の」エントリを問い合わせる時に使います。 効率的な実行のために、このようなランキングは、 パイプライン化したorder byを使って実行されるべきです。

クエリの最初の数行のみを取り出す一番シンプルな方法は、 最初の数行を取り出した時点で文の実行を終了してしまう事です。しかし残念な事に、 実行計画を準備する時点では、オプティマイザはそれには気付いてくれません。 最適な実行計画を選択するためには、アプリケーションが最終的に全行を取得するのかどうかを、 オプティマイザは知っていなければなりません。パイプライン化されたorder byを使う方が、データベースが各行を別々にフェッチする必要があるとしても、 最初の10行だけを取り出すのなら良い選択でしょう。しかしこの場合は、 明示的なソート処理を行うフルテーブルスキャンが最適な方法という事になってしまいます。 つまり、オプティマイザが最適な実行計画を選択するには、 全行を取得する前に文の実行を止めるつもりかどうかを知っている必要があります。

ヒント

クエリの実行結果の全行が必要かどうかを、 データベースに伝えましょう。

SQL標準は、長らくこの必要条件を扱わないできました。 これに対応する拡張(fetch first)は、SQL:2008で登場したばかりで、 IBM DB2、PostgreSQL、SQL Server 2012、Oracle 12cでのみ使用できます。 これは、1つはこの機能がノンコアな拡張とされているためでもあり、また、 各データベースが長年にわたって独自の方法でこの機能を実現してきたからでもあります。

次の例は、最新の売上10エントリを問い合わせるクエリを通じて、この 拡張の使い方を示したものです。基本的な動きは全て同じで、 最新の売上を始点に全ての売上を取得しています。 それから、それぞれの最初のN行のみを選択するクエリの文法を使って、 10行取り出した後にクエリの実行の中断を指示しています。

DB2

DB2は、バージョン9(LUWとzOS)からSQL標準の fetch first文法をサポートしています。

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

プロプラエタリなlimitキーワードは、 DB2 LUW 9.7からサポートしています(db2set DB2_COMPATIBILITY_VECTOR=MYSが必要)。

MySQL

MySQLとPostgreSQLは、取り出す行数を制限するのにlimit句を使います。

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10
Oracle

Oracleでは、fetch first拡張が12cから使えるようになりました。 それより前のリリースでは、 結果セットの行数を自動的に制限する疑似列であるROWNUMを使う必要があります。この列をフィルタで使うには、 クエリを以下のようにラップする必要があります。

SELECT *
  FROM (
       SELECT *
         FROM sales
        ORDER BY sale_date DESC
       )
 WHERE rownum <= 10
PostgreSQL

PostgreSQLはfetch first拡張を バージョン8.4からサポートしています。それより前から使用できた limit句も、MySQLの例として挙げたのと同じように 使用可能です。

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY
SQL Server

SQL Serverは、取りだす行数を制限するのにtop句をサポートしています。

SELECT TOP 10 *
  FROM sales
 ORDER BY sale_date DESC

リリース 2012からは、fetch first拡張も サポートしています。

ここまでのSQL文は全て、最初のN行のみを選択することをデータベースが認識できるように なっている、特別なクエリです。

重要

データベースは、部分結果のみを取得する事を事前に知っている場合のみ、 部分結果向けにクエリを最適化できます。

オプティマイザが、実際には最初の10行しか必要ないと分かっている場合は、 パイプライン化されたorder byが使えるなら、 それを使った実行計画を選択してくれます。

DB2
Explain Plan
-----------------------------------------------------------------
ID | Operation                      |               Rows |   Cost
 1 | RETURN                         |                    |     24
 2 |  FETCH SALES                   |      10 of 1009326 | 458452
 3 |   IXSCAN (REVERSE) SALES_DT_PR | 1009326 of 1009326 |   2624

Predicate Information

DB2においては、最初のN件のみを選択するクエリの挙動は、 SORT処理がない場合は実行計画で直接見ることはできません (last_explainedビューSORT (TOP-N)のように括弧内に表示されます。 次のコード例に表示されています)。

この例の場合、他のフィルタ述語の条件が影響しているのでも ないはずなのに(Predicate Informationには何も書かれていません) 行数が減っているので、最初のN件のみを選択するクエリになっている はずだと推測できるでしょう。

Oracle
-------------------------------------------------------------
| Operation                     | Name        | Rows | Cost |
-------------------------------------------------------------
| SELECT STATEMENT              |             |   10 |    9 |
|  COUNT STOPKEY                |             |      |      |
|   VIEW                        |             |   10 |    9 |
|    TABLE ACCESS BY INDEX ROWID| SALES       | 1004K|    9 |
|     INDEX FULL SCAN DESCENDING| SALES_DT_PR |   10 |    3 |
-------------------------------------------------------------

Oracleの実行計画には、 COUNT STOPKEYを使った実行停止が計画されていると表示されています。 データベースは、最初のN行のみのクエリの文法を理解している事が分かります。

ヒント

付録, 「実行計画, では、DB2MySQL、​Oracle、​PostgreSQL、​SQL Serverの各データベースにおける 対応する処理の概要を説明しています。

正しい文法で書く事は、条件の1つでしかありません。効率的に実行を中断するには、 内部的な動作がパイプライン化されて実行される事が重要なのです。そのためには、 order by句がインデックスでカバーされている必要があります。 今回の例で言うと、SALE_DATEPRODUCT_IDに対する インデックスSALE_DT_PRです。このインデックスを使う事で、 データベースは明示的なソート処理を行わなくて良くなり、インデックスを読んだらすぐに アプリケーションに行を返せるようになります。10行を取り出した時点で処理は中断されるので、 それ以上の行を読み出す事はありません。

重要

パイプライン化された最初のN行のみを選択するクエリは、 結果セットの全てを読む必要も、並べ替える必要もありません。

パイプライン化されたorder byに適したインデックスが SALE_DATEにない場合は、データベースはテーブル全体を読み込み、 並べ替えを行う必要があります。結果の最初の行は、テーブルから最後の行を読み込んだ後にしか 出力されません。

この実行計画では、パイプライン化されたorder byには なっておらず、クライアント側から中断したのと同じぐらい実行は遅いでしょう。 それでも最初のN件のみを選択するクエリの方がましな理由としては、 データベースは全ての結果ではなく、最初の10件だけをマテリアライズすれば良いことです。 その方が、明らかにメモリの消費量が少なくなります。Oracleの実行計画では この最適化を表すのに、SORT ORDER BY処理に STOPKEY修飾語を付けています。

パイプライン化された最初のN件のみを選択するクエリの優位点は、 目前のパフォーマンス向上だけでなく、スケーラビリティも改善してくれるところにあります。 パイプライン化した実行でない場合、最初のN件のみを選択するクエリの応答時間は、 テーブルサイズに比例して長くなります。パイプライン化した実行の場合は、 選択する行数にのみ比例します。言い換えると、 パイプライン化した最初のN件のみを選択するクエリのレスポンスタイムは、 テーブルサイズとはほとんど関係なく、常に一定であると言う事です。 Bツリーの深さが深くなった時にだけ、少し遅くなります。

は、 それぞれのパターンでデータ量が増えていった時のスケーラビリティを 表した図です。パイプライン化されていないorder byでは、 実行時間が一定の割合で増加してしまっているのが明らかです。 一方で、パイプライン化された方の実行時間は、ほぼ一定です。

図最初のN件のみを選択するクエリのスケーラビリティ

パイプライン化された最初のN件のみを選択するクエリの応答時間は、 テーブルの大きさには比例しませんが、選択する行数には比例して長くなります。 行数が2倍になれば、応答時間も2倍になります。この点は、いわゆる 「ページング」を行うために多くの行を選択するクエリにおいて、 大きく影響してきます。ページングにおいては、前のページで表示したものも含めて 最初の行から選択し、次のページに必要なデータを取得したらそれ以前のものを 捨てるという動作をするためです。これに対しては、別の方法がありますので、 次の節で説明していきます。

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

著者について

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 | CC-BY-NC-ND 3.0 license