by Hayato Matsuura.

効率的なページネーションのための窓関数の使用


窓関数は、SQLでページネーションを実装するもう1つの方法です。柔軟で、ここまでに書いたものと同じく、標準に則っています。 しかし、パイプライン化された最初のN件のみを選択するクエリで使えるのはSQL ServerとOracleのみです。 MySQL0、 DB2、PostgreSQLでは、必要な行を取得した後にインデックススキャンを中止できないので、 クエリの実行が非効率になってしまいます。

次の例は、窓関数のROW_NUMBERをページネーションのクエリに使用する例です。

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

ROW_NUMBERは、over句で定義されているソート順に対応した行を列挙する関数です。外側のwhere句では 2ページ目で表示する結果(11行目から20行目)に限定するために、この列挙された値を使っています。

協力してください

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

Oracleでは、パイプライン化された最初のN件だけを選択するクエリを 実行するために実行中断の条件を認識でき、SALE_DATESALE_IDの インデックスを使えます。

DB2
Explain Plan
-------------------------------------------------------------
ID | Operation                   |               Rows |  Cost
 1 | RETURN                      |                    | 65658
 2 |  FILTER                     |  100933 of 1009326 | 65658
 3 |   FETCH SALES               | 1009326 of 1009326 | 65295
 4 |    IXSCAN (REVERSE) SL_DTID | 1009326 of 1009326 |  5679

Predicate Information
 2 - RESID (11 <= Q3.$C8)
     RESID (Q3.$C8 <= 20)

DB2 LUW 10.5では、これを最初のN件のみを選択するクエリとしては 実行しない事に注意しましょう。ソートの処理はしませんが、インデックスは全体を読んでしまいます。また、20行を読み込んだ後も 実行を中断しません。

20行の読み込み後に正しく実行を中断するには、実行中断のための条件で二重にクエリをラップし、 その後10行をフィルタする必要があります。

SELECT *
  FROM (SELECT *
          FROM (SELECT sales.*
                     , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                                 , sale_id   DESC) rn
                 FROM sales
               ) tmp
         WHERE rn <= 20
       ) tmp2
 WHERE rn > 10
 ORDER BY sale_date DESC, sale_id DESC;
Explain Plan
-----------------------------------------------------------------
ID | Operation                       |               Rows |  Cost
 1 | RETURN                          |                    |    21
 2 |  FILTER                         |            7 of 20 |    21
 3 |   FETCH SALES                   |      20 of 1009326 | 65352
 4 |    IXSCAN (REVERSE) SALES_DT_ID | 1009326 of 1009326 |  5736

Predicate Information
 2 - RESID (10 < Q3.$C8)

rn <= 20と言うフィルタはPredicate Informationに 表示されていませんが、行数の見積には反映されている点に注意しましょう。

Oracle
---------------------------------------------------------------
|Id | Operation                      | Name    | Rows |  Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT               |         | 1004K| 36877 |
|*1 |  VIEW                          |         | 1004K| 36877 |
|*2 |   WINDOW NOSORT STOPKEY        |         | 1004K| 36877 |
| 3 |    TABLE ACCESS BY INDEX ROWID | SALES   | 1004K| 36877 |
| 4 |     INDEX FULL SCAN DESCENDING | SL_DTID | 1004K|  2955 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=11 AND "RN"<=20)
2 - filter(ROW_NUMBER() OVER (
           ORDER BY "SALE_DATE" DESC, "SALE_ID" DESC )<=20)

WINDOW NOSORT STOPKEY処理の表示は、 ソート処理が行われず(NOSORT)、上限に達したらデータベースはクエリの実行を中断する事(STOPKEY)を表しています。中断された処理はパイプライン化されたものである事を考えれば、このクエリの実行は前の節で説明した オフセット法と同じぐらい効率的である事が分かるでしょう。

窓関数の強みはページネーションではなく、数値解析にあります。もしまだ窓関数を使った事がなければ、関係するドキュメントを数時間読んで 勉強する価値は、間違いなくあると言えます。

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

著者について

Markus Winandの写真

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

彼の本

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

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

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

Amazonで購入
(印刷版のみ)

注記

0

MySQLは、バージョン8.0から窓関数をサポートしています。

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license