動機

オフセット法とシーク法についてはこちらで紹介していますが(offsetでページネーションは遅い。これからはシーク法だ！)、主要なリレーショナルデータベース毎に、オフセット法によるページネーションsqlと比較してどれだけ早いのか確認してみました。

100万件のデータから最後の10行を取得するオフセット法sqlとシーク法sqlを比較してます。
前提合わせしてなかったり、計測回数も1回なので、真面目に計測していないですが、
オフセット法とシーク法の違いは感じられると思います。

試したデータベース	オフセット法	シーク法
MySQL	4.40秒	0.00秒
PostgreSQL	15.146秒	2.425秒
Oracle	8.95秒	0.04秒
SQLServer	6.557秒	0秒
DB2	8.287903秒	0.009107秒
sqlite	12.043秒	0.000秒

また、上記を調べる中で、各種リレーショナルデータベースでテーブル作成やインデックスの作成、大量件数のランダムなデータ生成、統計情報の取得や実行計画の取得までの一連の作業を後で見返すよう用に書いています。
途中黒三角▼の行がありますが、ここはsqlなど縦に長いので折りたたんでいます。参照する場合は、その行をクリックしてください。

何をやるか？

他えば、受注管理システムの受注明細画面のバックエンドで実行するページネーション用のsqlを作っていることをイメージしてください。
ざっくりした要件では、最近受注した明細を見たいというものです。
最終的に決まった外部設計は、受注日付の降順、製品IDの昇順、受注明細IDの昇順でソートした受注明細を10件毎にページネーションするというものです。

ページネーションを試すテーブルの作成(受注明細テーブルの作成)。
これに100万件のランダムなテストデータを登録。
この受注明細を、受注日付の降順、製品IDの昇順、受注明細IDの昇順でソートしたものをページネーションで10件毎切り出すsql(オフセット法とシーク法)を用意。
それぞれのsqlの実行計画の比較と処理時間を比較。
またインデックスの有無で実行計画の比較と処理時間を比較。

ページネーションを試すテーブルの作成(受注明細テーブルの作成)

受注明細テーブルの作成

何故そうなのか的な違いの発見。create table文を流そうとしたのですが、

SQLServerの場合:sql文を流した後にgoと入力して[Enter]キーを押下しないと実行されません。

DB2の場合:Oracleで言うところのCUIツールのsqlplusに該当するdb2CUIツール。コピペでsql文を貼り付けようとすると、コマンドラインのオプションに-tをつけずに実行すると、改行文字がsql文実行になってしまい、意図した挙動をしてくれません。

  drop table t_order_item cascade constraint;

  create table t_order_item (
    order_item_id decimal(7,0) not null
    , product_id decimal(7,0)
    , order_qty decimal(7,0)
    , unit_prc decimal(7,3)
    , shipping_address_desc char(10)
    , order_date date
    , constraint pk_t_order_item primary key ( order_item_id )
  )
  ;

シーク法sqlに効果のあるインデックスの作成

order by句に指定されているカラムでインデックスを作成します。

ここで発見。インデックスの削除の仕方がリレーショナルデータベースで違いがありました。

インデックスの削除

MySQL,SQLServer

  drop index idx1_t_order_item on t_order_item;

DB2,Oracle,PostgreSQL,sqlite3

  drop index idx1_t_order_item;

インデックスの作成

  create index idx1_t_order_item on t_order_item (order_date, product_id, order_item_id);

100万件のランダムなテストデータを登録

0-9の数字を格納したt_digitsテーブルを作り、これを直積で件数を増やして100万件にするv_millionビューを作ります。
v_millionビューをもとに乱数でカラムの値を作って、受注明細テーブルt_order_itemに登録します。

0から9の数字テーブルの作成と初期データ登録

  drop table t_digits cascade constraint;

  create table t_digits (
    digit_num decimal(1,0) not null
    , constraint pk_t_digits primary key (digit_num)
  );

  insert into t_digits (digit_num) values (0);
  insert into t_digits (digit_num) values (1);
  insert into t_digits (digit_num) values (2);
  insert into t_digits (digit_num) values (3);
  insert into t_digits (digit_num) values (4);
  insert into t_digits (digit_num) values (5);
  insert into t_digits (digit_num) values (6);
  insert into t_digits (digit_num) values (7);
  insert into t_digits (digit_num) values (8);
  insert into t_digits (digit_num) values (9);

  commit;

0から99万9,999の数字(100万件)ビューの作成

  drop view v_million;

  create view v_million (
    digit_num
  ) as
  select
    d1.digit_num
    + ( d2.digit_num * 10 )
    + ( d3.digit_num * 100 )
    + ( d4.digit_num * 1000 )
    + ( d5.digit_num * 10000 )
    + ( d6.digit_num * 100000 ) as seq
  from
    t_digits d1
    , t_digits d2
    , t_digits d3
    , t_digits d4
    , t_digits d5
    , t_digits d6
    ;

上記の受注明細テーブルt_order_itemにテストデータとして、100万件のデータを登録します。
カラムにセットする値は乱数により生成します。
下記insert文で、特定の日付範囲をランダムに設定している箇所がありますが(2019-01-01から2019-05-31)、
分かりにくいので、その考え方を記します。

考え方： from_日付 + mod(ランダムな正の整数, (to_日付 - from_日付))
modは剰余を求めています。

以下にデータベース毎のテストデータ登録と統計情報取得について記します。

MySQLのテストデータ登録と統計情報の取得

テスト用データベースを作成して、そこに接続します。

create database testdb;
use testdb;

覚書:ダミー表の使用例。というか、特に指定しなくてよい。

ダミー表の使用例

  select 
  substring(md5(cast(rand() as char)), 1, 10) as shipping_address_desc;

100万件のテストデータinsert。

100万件のテストデータinsert

  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + rand() * 5) as product_id
    , floor(1 + rand() * 1000) as order_qty
    , round(cast( (rand() * 1000000) as decimal(10,3) ) % 10000, 3) as unit_prc
    , substring(md5(cast(rand() as char)), 1, 10) as shipping_address_desc
    , date_add(
        cast('2019-01-01' as date)
        , interval (
          cast(floor(1 + rand() * 365) as decimal(3,0))
          % cast(datediff(cast('2019-05-31' as date), cast('2019-01-01' as date)) as decimal(3,0))
        )
        day
      ) as order_date
  from
    v_million
  ;
  commit;

初期データを登録した後に、統計情報を取得します。

統計情報の取得

  analyze table t_order_item;

PostgreSQLのテストデータ登録と統計情報の取得

テスト用データベースを作成して、そこに接続します。

create database testdb;
\c testdb;

覚書:ダミー表の使用例。

ダミー表の使用例

  select 
  substring(md5(cast(random() as varchar)), 1, 10) as shipping_address_desc;

100万件のテストデータinsert。

100万件のテストデータinsert

  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + random() * 5) as product_id
    , floor(1 + random() * 1000) as order_qty
    , round(cast( (random() * 1000000) as decimal(10,3) ) % 10000, 3) as unit_prc
    , substring(md5(cast(random() as varchar)), 1, 10) as shipping_address_desc
    , cast('2019-01-01' as date)
      + cast(floor(1 + random() * 365) as integer) % cast(cast('2019-05-31' as date) - cast('2019-01-01' as date) as integer)
  from
    v_million
  ;
  commit;

初期データを登録した後に、統計情報を取得します。

統計情報の取得

  analyze t_order_item;

Oracleのテストデータ登録と統計情報の取得

SYSTEMユーザでテストを実施しますが、SYSTEMユーザには、乱数生成に使用するDBMS_CRYPTOパッケージの権限が付与されていないので付与します。

権限付与

  sqlplus /nolog
  connect / as sysdba
  grant execute on dbms_crypto to system;

日付文字列をdateにcastする度に日付書式を指定するのが面倒なので、この後で使うsql文のデフォルト書式を設定します。

日付書式の設定

  alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

覚書:ダミー表の使用例。

ダミー表の使用例

  select 
  substr(dbms_crypto.hash(utl_i18n.string_to_raw (dbms_crypto.randomnumber, 'AL32UTF8'),2 ), 1, 10) as shipping_address_desc
  from dual;

100万件のテストデータinsert。

100万件のテストデータinsert

  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , mod( dbms_crypto.randomnumber, 5) + 1 as product_id
    , mod( dbms_crypto.randomnumber, 1000) + 1 as order_qty
    , (mod( dbms_crypto.randomnumber, 1000000) + 1) / 1000 as unit_prc
    , substr(dbms_crypto.hash(utl_i18n.string_to_raw (dbms_crypto.randomnumber, 'AL32UTF8'),2 ), 1, 10) as shipping_address_desc
    , cast('2019-01-01' as date)
      + mod(cast(mod( dbms_crypto.randomnumber, 365) as number) , cast(cast('2019-05-31' as date) - cast('2019-01-01' as date) as number))
  from
    v_million
  ;
  commit;

初期データを登録した後に、統計情報を取得します。

統計情報の取得

  begin
    dbms_stats.gather_table_stats(
      ownname       => 'system'
      ,tabname      => 't_order_item'
      ,method_opt   => 'for all indexed'
      ,cascade      => true
    );
  end;
  /

SQLServerのテストデータ登録と統計情報の取得

テスト用データベースを作成して、そこに接続します。

create database testdb;
go
use testdb;
go

覚書:ダミー表の使用例。

ダミー表の使用例

  select 
  convert(char(10), hashbytes('MD5', cast(rand(checksum(newid())) as char)), 2) as shipping_address_desc;

SQLServerの場合、rand()関数のseed値にnewid()関数でユニークな値を設定しないと乱数にならず100万件同じ値になってしまいます。

100万件のテストデータinsert。

100万件のテストデータinsert

  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + rand(checksum(newid())) * 5) as product_id
    , floor(1 + rand(checksum(newid())) * 1000) as order_qty
    , round(cast((rand(checksum(newid())) * 1000000) as decimal(10,3)) % 10000, 3) as unit_prc
    , convert(char(10), hashbytes('MD5', cast(rand(checksum(newid())) as char)), 2) as shipping_address_desc
    , dateadd(day
      , cast( floor(1 + rand( checksum( newid() ) ) * 365) as decimal(3,0)) % cast(datediff(day , '2019-01-01', '2019-05-31') as decimal(3,0))
      , cast('2019-01-01' as date) ) as shipping_address_desc
  from
    v_million
  ;
  commit;

初期データを登録した後に、統計情報を取得します。

統計情報の取得

  analyze t_order_item;

DB2のテストデータ登録と統計情報の取得

dockerにDB2コンテナを作った直後の状態で、下記の100万件insertをすると、トランザクション・ログがフルとなってしまい、更新ができなくなります。
そのため、DB2コンテナ内部で、ログのサイズを増やす必要があります。

参考URL:

[DB2 LUW] トランザクション・ログがフル (SQL0964C) になったときの対応 (IM-10-00W)

DB2の設定情報の内、トランザクションログに関する部分をgrepしています。

コンテナ内部

db2 get db cfg for testdb|grep LOG

Log file size (4KB)                         (LOGFILSIZ) = 1024
Number of primary log files                (LOGPRIMARY) = 16
Number of secondary log files               (LOGSECOND) = 22

セカンダリサイズは即時反映されるので、取りあえずMAXの4096を設定します。

コンテナ内部

db2 update db cfg for testdb using LOGSECOND 4096 IMMEDIATE

覚書:ダミー表の使用例。

ダミー表の使用例

  select 
  substring(hex(hash(cast(rand() as varchar), 2)), 1, 10) as shipping_address_desc
  from sysibm.sysdummy1;

100万件のテストデータinsert。

  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + rand() * 5) as product_id
    , floor(1 + rand() * 1000) as order_qty
    , round(cast( (rand() * 1000000) as decimal(10,3) ) % 10000, 3) as unit_prc
    , substring(hex(hash(cast(rand() as varchar), 2)), 1, 10) as shipping_address_desc
    , cast('2019-01-01' as date)
      + cast(floor(1 + rand() * 365) as integer)
      % cast(days(cast('2019-05-31' as date)) - days(cast('2019-01-01' as date)) as integer)
  from
    v_million
  ;
  commit;

初期データを登録した後に、統計情報を取得します。

統計情報の取得

  runstats on table t_order_item with distribution and detailed indexes all;

sqliteのテストデータ登録と統計情報の取得

覚書:ダミー表の使用例。

ダミー表の使用例

  select 
  abs(random()) % 5 + 1  as product_id;

  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , abs(random()) % 5 + 1  as product_id
    , abs(random()) % 1000 + 1 as order_qty
    , (abs(random()) % 10000000.0 + 1.0) / 1000.0 as unit_prc
    , substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1) as shipping_address_desc
    , date((strftime('%s', '2019-01-01') + ((abs(random()) % 365)*86400) % (strftime('%s', '2019-05-31') - strftime('%s', '2019-01-01'))), 'unixepoch') as order_date
  from
    v_million
  ;
  commit;

初期データを登録した後に、統計情報を取得します。

統計情報の取得

  analyze;

MySQL

処理時間をコンソール表示

他のデータベースでは、コマンドを入力する必要がありますが、MySQLでは特に入力せずとも処理時間が表示されます。

最初の10行を取得

実行計画を取得します。

最初の10行を取得

  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
    limit 10 offset 0
  ;

index有無に関係なく、type=ALLのテーブルフルスキャンです。

index有り

+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 856180 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+

index無し

+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002885 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+

処理時間を計測します。上記sqlのexplainを外して実行します。
1回しか計測していませんが、index有無は処理時間の差は出ませんでした。
(4回くらい計測して平均すればよいのでしょうが面倒だったので端折りました)

index有り:10 rows in set (2.95 sec)
index無し:10 rows in set (2.82 sec)

オフセット法による最後10行を取得

実行計画を取得します。

オフセット法による最後10行

  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
    limit 10 offset 999990
  ;

index有無に関係なく、type=ALLのテーブルフルスキャンです。

オフセット法による最後の10行(index有り)

+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 856180 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+

オフセット法による最後の10行(index無し)

+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002885 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+

index有無は処理時間の差はわずかです。最初の10行取得と比べると約2倍弱の処理時間がかかっています。

index有り:10 rows in set (4.40 sec)
index無し:10 rows in set (4.50 sec)

オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。

オフセット法による最後11行

  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;
+---------------+------------+-----------+----------+-----------------------+------------+
| order_item_id | product_id | order_qty | unit_prc | shipping_address_desc | order_date |
+---------------+------------+-----------+----------+-----------------------+------------+
|        990154 |          5 |       584 |  267.004 | bb2fafd1b2            | 2019-01-01 |＜＝ここをキーにする
|        990952 |          5 |        94 |  414.368 | 9120ac3d9e            | 2019-01-01 |
|        992846 |          5 |       431 | 2061.600 | 2a35c06ab9            | 2019-01-01 |
|        993338 |          5 |       759 | 8705.299 | 9dd01ddc97            | 2019-01-01 |
|        993348 |          5 |       762 | 3606.435 | 87934dd6b0            | 2019-01-01 |
|        994258 |          5 |       491 | 5982.529 | 78dbe3f5a8            | 2019-01-01 |
|        994789 |          5 |       730 | 4800.993 | 0b5b210492            | 2019-01-01 |
|        995875 |          5 |       230 | 2739.531 | 8bcd9f4035            | 2019-01-01 |
|        996055 |          5 |       922 | 7283.062 | a099aad579            | 2019-01-01 |
|        997247 |          5 |       926 | 2229.296 | 71eb6c9324            | 2019-01-01 |
|        999160 |          5 |        98 | 8096.207 | 9c6236cd50            | 2019-01-01 |
+---------------+------------+-----------+----------+-----------------------+------------+
11 rows in set (4.36 sec)

シーク法による最後の10行を取得

実行計画を取得します。

シーク法による最後の10行

  explain
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 990154 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;

index有り:type=rangeでインデックスの効果が出ています。idx1_t_order_itemインデックスです。
index無し:type=ALLのテーブルフルスキャンのように見えますが、Using whereとなっているのでwhere句で抽出対象が絞られる効果が出ています。

シーク法による最後の10行(index有り)

+----+-------------+--------------+------------+-------+---------------------------+-------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys             | key               | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+--------------+------------+-------+---------------------------+-------------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t_order_item | NULL       | range | PRIMARY,idx1_t_order_item | idx1_t_order_item | 13      | NULL |   12 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------------+------------+-------+---------------------------+-------------------+---------+------+------+----------+---------------------------------------+

シーク法による最後の10行(index無し)

+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 882635 |    35.77 | Using where; Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

オフセット法では最後の10行を取得するのに4.40 secの処理時間がかかっていますが、シーク法のindex有りは0.00 secです。index無しでも2.47 secです。

index有り:10 rows in set (0.00 sec)
index無し:10 rows in set (2.47 sec)

シーク法でのページジャンプ

各ページの最後の行がわかれば、それをキーにシーク法が仕掛けられます。下記SQLは10行でページングする場合の調べ方です。
考え方は、行数でMODするイメージ。 WINDOW関数が使える前提。

全てのページの境界を求める

実行計画を取得します。

全てのページの境界を求める

  explain
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        case mod(row_number() over(
          order by
            order_date desc, product_id, order_item_id
        ), 10)  -- 10行でページング
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
      order by
        order_date desc,
        product_id,
        order_item_id
    ) x
  where
    x.page_boundary = 1
  ;

index有無に関係なく、type=ALLのテーブルフルスキャンです。

index有り

+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |     10 |   100.00 | Using filesort |
|  2 | DERIVED     | t          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 856180 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+

index無し

+----+-------------+------------+------------+------+---------------+-------------+---------+-------+---------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows    | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+---------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |      10 |   100.00 | Using filesort |
|  2 | DERIVED     | t          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 1147185 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+---------+----------+----------------+

index有無は処理時間の差はわずかです。実際に10万行表示するのに20秒くらいかかりました。

index有り:100000 rows in set (21.17 sec)
index無し:100000 rows in set (20.77 sec)

PostgreSQL

処理時間をコンソールに表示

\timing

最初の10行を取得

実行計画を取得します。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

最初の10行を取得

  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 0
  ;

実行計画と処理時間

実行計画の見方がわからないです。index有無は関係ないようです。全件をソートして、Parallel Seq Scanなので、ワーカが2つあって、パラレルに検索しているのかもしれないですが、Seq Scanとなっているので、結局フルスキャンなんだと思います。

index有り

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=22504.71..22505.88 rows=10 width=37)
   ->  Gather Merge  (cost=22504.71..119733.80 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=21504.69..22546.36 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)

index無し

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=22504.71..22505.88 rows=10 width=37)
   ->  Gather Merge  (cost=22504.71..119733.80 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=21504.69..22546.36 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)

処理時間を計測します。上記sqlのexplainを外して実行します。
index有無で若干差が出ていますが、ざっくり1秒くらいです。

index有り:Time: 847.375 ms
idnex無し:Time: 1011.590 ms (00:01.012)

オフセット法による最後10行を取得

実行計画を取得します。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

オフセット法による最後10行を取得

オフセット法による最後10行

  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 999990
  ;

実行計画と処理時間

実行計画の見方がわからないです。index有無は関係ないようです。最初の10件と同じような実行計画ですが、さらにJITなるものが登場しています。

オフセット法による最後の10行(index有り)

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=161018.59..161018.71 rows=1 width=37)
   ->  Gather Merge  (cost=63789.50..161018.59 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=62789.48..63831.15 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true

index無し

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=161018.59..161018.71 rows=1 width=37)
   ->  Gather Merge  (cost=63789.50..161018.59 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=62789.48..63831.15 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true

処理時間を計測します。上記sqlのexplainを外して実行します。
index有無で若干差が出ていますが、ざっくり15秒くらいです。

index有り:Time: 15145.981 ms (00:15.146)
index無し:Time: 15016.168 ms (00:15.016)

オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

オフセット法による最後11行

  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;

 order_item_id | product_id | order_qty | unit_prc | shipping_address_desc | order_date
---------------+------------+-----------+----------+-----------------------+------------
        992619 |          5 |       648 |  231.046 | 7fce9ea93f            | 2019-01-01＜＝ここをキーにする
        992954 |          5 |       518 | 3058.612 | bddf40c90e            | 2019-01-01
        993407 |          5 |       202 | 1643.126 | ff13043c60            | 2019-01-01
        993878 |          5 |       305 | 5854.718 | e8590003f0            | 2019-01-01
        994495 |          5 |       973 | 8662.716 | 803e36b2be            | 2019-01-01
        994628 |          5 |       152 | 4002.453 | 24e4dfbcfc            | 2019-01-01
        995617 |          5 |       149 | 4843.632 | 12516bf6d8            | 2019-01-01
        996712 |          5 |       948 | 6764.766 | 224c8c3aaa            | 2019-01-01
        998088 |          5 |        23 | 4314.589 | 9def3af209            | 2019-01-01
        998210 |          5 |       682 |  146.102 | a032f90106            | 2019-01-01
        999235 |          5 |       549 | 7711.560 | 9875c20006            | 2019-01-01
(11 rows)

Time: 15068.021 ms (00:15.068)

シーク法による最後の10行

実行計画を取得します。
本体のsql文はMySQLと同じです。

シーク法による最後の10行

  explain
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 992619 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;

実行計画と処理時間

オフセット法と比較すると違いが顕著です。index有りの方は、Bitmap Heap ScanやBitmap Index Scanなどインデックス効果が出てそうなキーワードが出ています。
index無しの方はオフセット法にはなかったfilterというのが登場しています。恐らくwhere句で抽出対象が絞られる効果が出ていると思われます(MySQLと同じような挙動)。

シーク法による最後の10行(index有り)

                                                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=48.89..48.91 rows=9 width=37)
   ->  Sort  (cost=48.89..48.91 rows=9 width=37)
         Sort Key: order_date DESC, product_id, order_item_id
         ->  Bitmap Heap Scan on t_order_item  (cost=13.41..48.75 rows=9 width=37)
               Recheck Cond: (('2019-01-01'::date > order_date) OR (('2019-01-01'::date = order_date) AND ('5'::numeric < product_id)) OR (('2019-01-01'::date = order_date) AND '5'::numeric  product_id) AND ('992619'::numeric < order_item_id)))
               ->  BitmapOr  (cost=13.41..13.41 rows=9 width=0)
                     ->  Bitmap Index Scan on idx1_t_order_item  (cost=0.00..4.43 rows=1 width=0)
                           Index Cond: (order_date < '2019-01-01'::date)
                     ->  Bitmap Index Scan on idx1_t_order_item  (cost=0.00..4.43 rows=1 width=0)
                           Index Cond: ((order_date = '2019-01-01'::date) AND (product_id > '5'::numeric))
                     ->  Bitmap Index Scan on idx1_t_order_item  (cost=0.00..4.54 rows=9 width=0)
                           Index Cond: ((order_date = '2019-01-01'::date) AND (product_id = '5'::numeric) AND (order_item_id > '992619'::numeric))

index無し

                                                                                                                      QUERY PLAN                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=19751.25..19752.42 rows=10 width=37)
   ->  Gather Merge  (cost=19751.25..19757.32 rows=52 width=37)
         Workers Planned: 2
         ->  Sort  (cost=18751.23..18751.29 rows=26 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..18750.67 rows=26 width=37)
                     Filter: (('2019-01-01'::date > order_date) OR (('2019-01-01'::date = order_date) AND ('5'::numeric < product_id)) OR (('2019-01-01'::date = order_date) AND ('5'::numeric = product_id) AND ('992619'::numeric < order_item_id)))

オフセット法では最後の10行を取得するのに00:15.146秒の処理時間がかかっていますが、シーク法のindex有りは2.425 msです。index無しでも985.916 msです。段違いです。

index有り:Time: 2.425 ms
index無し:Time: 985.916 ms

シーク法でのページジャンプ

全てのページの境界を求める

PostgreSQLの場合、問い合わせ結果が一定行数になるとページャが働いてmoreとなって表示が止まります。一気に10万行表示したいので、ページャの機能をオフにします。

ページャオフ

\pset pager off

実行計画を取得します。

全てのページの境界を求める

  explain
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        case mod(row_number() over(
          order by
            order_date desc, product_id, order_item_id
        ), 10)  -- 10行でページング
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
      order by
        order_date desc,
        product_id,
        order_item_id
    ) x
  where
    x.page_boundary = 1
  ;

実行計画と処理時間

Seq Scanとなており、Parallelですらなくなっています。

index有り

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 WindowAgg  (cost=172682.84..212795.34 rows=5000 width=49)
   ->  Subquery Scan on x  (cost=172682.84..212682.84 rows=5000 width=41)
         Filter: (x.page_boundary = 1)
         ->  WindowAgg  (cost=172682.84..200182.84 rows=1000000 width=41)
               ->  Sort  (cost=172682.84..175182.84 rows=1000000 width=37)
                     Sort Key: t.order_date DESC, t.product_id, t.order_item_id
                     ->  Seq Scan on t_order_item t  (cost=0.00..18334.00 rows=1000000 width=37)
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true

index無し

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 WindowAgg  (cost=172682.84..212795.34 rows=5000 width=49)
   ->  Subquery Scan on x  (cost=172682.84..212682.84 rows=5000 width=41)
         Filter: (x.page_boundary = 1)
         ->  WindowAgg  (cost=172682.84..200182.84 rows=1000000 width=41)
               ->  Sort  (cost=172682.84..175182.84 rows=1000000 width=37)
                     Sort Key: t.order_date DESC, t.product_id, t.order_item_id
                     ->  Seq Scan on t_order_item t  (cost=0.00..18334.00 rows=1000000 width=37)
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true

index有無は処理時間の差はわずかです。実際に10万行表示するのに18秒くらいかかりました。

index有り:Time: 18301.758 ms (00:18.302)
index無し:Time: 17955.903 ms (00:17.956)

Oracle

処理時間をコンソールに表示

set timing on

1行当たりの文字数とページ行数の設定

set linesize 120
set pagesize 100

実行計画を格納するPLAN表の作成

@?/rdbms/admin/utlxpls.sql

最初の10行を取得

実行計画を取得します。
本体のsql文はMySQLやPostgreSQLとちょっと違います。

最初の10行を取得

最初の10行

  explain plan for
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 0 rows fetch first 10 rows only
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));

実行計画と処理時間

index有無に関係なく100万行のTABLE ACCESS FULLです。

index有り

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (0>=0) THEN 0
              ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN (0>=0)
              THEN 0 ELSE 0 END +10)

index無し

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (0>=0) THEN 0
              ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN (0>=0)
              THEN 0 ELSE 0 END +10)

処理時間を計測します。上記sqlのexplain plan forとselect plan_table_output from table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));を外して実行します。
index有無で若干差が出ていますが、ざっくり1秒かからないです。

index有り:Elapsed: 00:00:00.44
index無し:Elapsed: 00:00:00.48

オフセット法による最後10行

実行計画を取得します。
本体のsql文はMySQLやPostgreSQLとちょっと違います。

オフセット法による最後10行を取得

オフセット法による最後10行

  explain plan for
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 999990 rows fetch first 10 rows only
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));

実行計画と処理時間

index有無に関係なく100万行のTABLE ACCESS FULLです。

オフセット法による最後の10行(index有り)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (999990>=0) THEN
              999990 ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">999990)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN
              (999990>=0) THEN 999990 ELSE 0 END +10)

オフセット法による最後の10行(index無し)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (999990>=0) THEN
              999990 ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">999990)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN
              (999990>=0) THEN 999990 ELSE 0 END +10)

index有り:Elapsed: 00:00:08.95
index無し:Elapsed: 00:00:09.29

オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文はMySQLやPostgreSQLとちょっと違います。

オフセット法による最後11行

select
 *
from
 t_order_item
order by
 t_order_item.order_date desc,
 t_order_item.product_id,
 t_order_item.order_item_id
offset 999989 rows fetch first 11 rows only
;

ORDER_ITEM_ID PRODUCT_ID  ORDER_QTY   UNIT_PRC SHIPPING_A ORDER_DATE
------------- ---------- ---------- ---------- ---------- -------------------
       993345          5        771    715.027 8A754A27E2 2019-01-01 00:00:00＜＝ここをキーにする
       993625          5        460     84.191 C17AFC1ECE 2019-01-01 00:00:00
       994455          5        777     16.591 861B93AEA5 2019-01-01 00:00:00
       994525          5        692    490.703 25A11413D8 2019-01-01 00:00:00
       995956          5        159    442.299 D33F17230D 2019-01-01 00:00:00
       997695          5        953     44.002 2E51B69029 2019-01-01 00:00:00
       998152          5        614    638.381 6EF2FECF01 2019-01-01 00:00:00
       999040          5        111    331.362 0603022051 2019-01-01 00:00:00
       999143          5        343    453.721 D9B6FB3DD0 2019-01-01 00:00:00
       999408          5        342     142.84 577DD08BBB 2019-01-01 00:00:00
       999750          5        873    209.179 C5BE6F1053 2019-01-01 00:00:00

11 rows selected.

Elapsed: 00:00:09.29

シーク法による最後の10行

実行計画を取得します。
本体のsql文はMySQLやPostgreSQLとちょっと違います。

シーク法による最後の10行

  -- 日付書式を変えます。
  alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

  explain plan for
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 993345 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  fetch first 10 rows only
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));

実行計画と処理時間

オフセット法と比較すると違いが顕著です。index有りの方はINDEX RANGE SCANとなっており、インデックスが効果を出しています。
index無しの方はTABLE ACCESS FULLですが、ROWSを見ると、7355行なのでwhere句で抽出対象が絞られています。

シーク法による最後の10行(index有り)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2789792141

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |    10 |  1210 |   949   (1)| 00:00:01 |
|*  1 |  VIEW                                 |                   |    10 |  1210 |   949   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK             |                   |  7355 |   265K|   949   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORDER_ITEM      |  7355 |   265K|   948   (1)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS       |                   |       |       |            |          |
|   5 |      BITMAP OR                        |                   |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS   |                   |       |       |            |          |
|   7 |        SORT ORDER BY                  |                   |       |       |            |          |
|*  8 |         INDEX RANGE SCAN              | IDX1_T_ORDER_ITEM |       |       |    44   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS   |                   |       |       |            |          |
|  10 |        SORT ORDER BY                  |                   |       |       |            |          |
|* 11 |         INDEX RANGE SCAN              | IDX1_T_ORDER_ITEM |       |       |     7   (0)| 00:00:01 |
|  12 |       BITMAP CONVERSION FROM ROWIDS   |                   |       |       |            |          |
|  13 |        SORT ORDER BY                  |                   |       |       |            |          |
|* 14 |         INDEX RANGE SCAN              | IDX1_T_ORDER_ITEM |       |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC
              ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=10)
   8 - access("ORDER_DATE"<CAST('2019-01-01' AS date))
       filter("ORDER_DATE"<CAST('2019-01-01' AS date))
  11 - access("ORDER_DATE"=CAST('2019-01-01' AS date) AND "PRODUCT_ID">5)
       filter("PRODUCT_ID">5 AND "ORDER_DATE"=CAST('2019-01-01' AS date))
  14 - access("ORDER_DATE"=CAST('2019-01-01' AS date) AND "PRODUCT_ID"=5 AND
              "ORDER_ITEM_ID">993345)
       filter("ORDER_ITEM_ID">993345 AND "PRODUCT_ID"=5 AND "ORDER_DATE"=CAST('2019-01-01' AS
              date))

シーク法による最後の10行(index無し)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |    10 |  1210 |  1588   (2)| 00:00:01 |
|*  1 |  VIEW                    |              |    10 |  1210 |  1588   (2)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  7355 |   265K|  1588   (2)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  7355 |   265K|  1587   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC
              ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=10)
   3 - filter("ORDER_DATE"<CAST('2019-01-01' AS date) OR "PRODUCT_ID">5 AND
              "ORDER_DATE"=CAST('2019-01-01' AS date) OR "ORDER_ITEM_ID">993345 AND
              "PRODUCT_ID"=5 AND "ORDER_DATE"=CAST('2019-01-01' AS date))

オフセット法では最後の10行を取得するのに00:00:08.95秒の処理時間がかかっていますが、シーク法のindex有りは00:00:00.04です。index無しでも00:00:00.41です。段違いです。

index有り:Elapsed: 00:00:00.04
index無し:Elapsed: 00:00:00.41

シーク法でのページジャンプ

全てのページの境界を求める

実行計画を取得します。
Oracleは剰余の計算にmod関数を使います。

全てのページの境界を求める

  explain plan for
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        -- Oracleは剰余の計算にmod関数を使います。
        case mod(row_number() over(
          order by
            order_date desc, product_id, order_item_id
        ), 10)  -- 10行でページング
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
      order by
        order_date desc,
        product_id,
        order_item_id
    ) x
  where
    x.page_boundary = 1
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));

実行計画と処理時間

index有無に関係なく100万行のTABLE ACCESS FULLです。

index有り

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4158901137

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   1 |  WINDOW NOSORT       |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|*  2 |   VIEW               |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   3 |    WINDOW SORT       |              |  1000K|    35M|  1567   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T_ORDER_ITEM |  1000K|    35M|  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"."PAGE_BOUNDARY"=1)

index無し

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4158901137

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   1 |  WINDOW NOSORT       |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|*  2 |   VIEW               |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   3 |    WINDOW SORT       |              |  1000K|    35M|  1567   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T_ORDER_ITEM |  1000K|    35M|  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"."PAGE_BOUNDARY"=1)

index有無は処理時間の差はわずかです。実際に10万行表示するのに42秒くらいかかりました。

index有り:Elapsed: 00:00:43.80
index無し:Elapsed: 00:00:41.32

SQLServer

処理時間をコンソール表示

set statistics time on;
go

最初の10行を取得

実行計画を取得します。
本体のsql文はOracleと同じです。MySQLやPostgreSQLとちょっと違います。

最初の10行を取得

最初の10行

set showplan_text on;
go
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 0 rows fetch first 10 rows only
  ;
  go
set showplan_text off;
go

実行計画と処理時間

実行計画の見方がよくわからないです。フルスキャンになっているんだろうか？

index有り

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
       |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))

index無し

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
       |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))

処理時間を計測します。上記sqlのset showplan_text on;とset showplan_text off;を外して実行します。
ざっくり2秒です。

index有り:   CPU time = 1567 ms,  elapsed time = 1596 ms.
index無し:   CPU time = 1570 ms,  elapsed time = 1596 ms.

オフセット法による最後10行

実行計画を取得します。
本体のsql文はOracleと同じです。MySQLやPostgreSQLとちょっと違います。

オフセット法による最後10行を取得

オフセット法による最後10行

set showplan_text on;
go
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 999990 rows fetch first 10 rows only
  ;
  go
set showplan_text off;
go

実行計画と処理時間

実行計画の見方がよくわからないです。フルスキャンになっているんだろうか？

オフセット法による最後の10行(index有り)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(OFFSET EXPRESSION:((999990)),TOP EXPRESSION:((10)))                                                                                          
       |--Sort(TOP 1000000, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
            |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))

オフセット法による最後の10行(index無し)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(OFFSET EXPRESSION:((999990)),TOP EXPRESSION:((10)))                                                                                          
       |--Sort(TOP 1000000, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
            |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))

処理時間を計測します。上記sqlのset showplan_text on;とset showplan_text off;を外して実行します。
ざっくり7秒です。

index有り:   CPU time = 6175 ms,  elapsed time = 6557 ms.
index無し:   CPU time = 6217 ms,  elapsed time = 6719 ms.

オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文はOracleと同じです。MySQLやPostgreSQLとちょっと違います。

オフセット法による最後11行


  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 999989 rows fetch first 11 rows only
  ;
  go

order_item_id product_id order_qty unit_prc  shipping_address_desc order_date
------------- ---------- --------- --------- --------------------- ----------------
       990913          5       325   544.387 D9B563CB0D                  2019-01-01＜＝ここをキーにする
       991307          5       722  4875.844 9401D8E665                  2019-01-01
       992318          5       782  2300.518 EF135253B0                  2019-01-01
       992549          5       583  2651.842 6138B01A85                  2019-01-01
       993405          5       934  1726.319 41759C5AAC                  2019-01-01
       997085          5       204  7942.090 C13635D25A                  2019-01-01
       997471          5       428  5180.960 2630ED13D4                  2019-01-01
       997898          5       634  9250.947 C458DAE46D                  2019-01-01
       999406          5        80  1191.171 23B6092077                  2019-01-01
       999680          5       354  2146.997 74F0158945                  2019-01-01
       999961          5       551  7962.957 B5D3CE925C                  2019-01-01

(11 rows affected)

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 7000 ms.

シーク法による最後の10行

実行計画を取得します。
本体のsql文はOracleと同じです。MySQLやPostgreSQLとちょっと違います。

シーク法による最後の10行

set showplan_text on;
go
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 990913 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  offset 0 rows fetch first 10 rows only
  ;
  go
set showplan_text off;
go

実行計画と処理時間

オフセット法と比較すると違いが顕著です。index有りの方はIndex Seekと[idx1_t_order_item]となっています。たぶん、インデックスが効果を出しています。
index無しの方はよくわからないですが、Sort(TOP 10,となっているので効果がありそうです(オフセット法はSort(TOP 1000000,となっている)。

シーク法による最後の10行(index有り)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))                                                                                                                                                                                                                                                                                  
       |--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[t_order_item].[order_item_id], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH)                                                                                                                                                                                                                                                                                                                
            |--Index Seek(OBJECT:([testdb].[dbo].[t_order_item].[idx1_t_order_item]), SEEK:([testdb].[dbo].[t_order_item].[order_date] < '2019-01-01' OR [testdb].[dbo].[t_order_item].[order_date]='2019-01-01' AND [testdb].[dbo].[t_order_item].[product_id]=(5.) AND [testdb].[dbo].[t_order_item].[order_item_id] > (990913.) OR [testdb].[dbo].[t_order_item].[order_date]='2019-01-01' AND [testdb].[dbo].[t_order_item].[product_id] > (5.)) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]), SEEK:([testdb].[dbo].[t_order_item].[order_item_id]=[testdb].[dbo].[t_order_item].[order_item_id]) LOOKUP ORDERED FORWARD)

シーク法による最後の10行(index無し)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))                                                                                                                            
       |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]), WHERE:([testdb].[dbo].[t_order_item].[order_date]<'2019-01-01' OR '2019-01-01'=[testdb].[dbo].[t_order_item].[order_date] AND [testdb].[dbo].[t_order_item].[product_id]>(5.) OR '2019-01-01'=[testdb].[dbo].[t_order_item].[order_date] AND (5.)=[testdb].[dbo].[t_order_item].[product_id] AND [testdb].[dbo].[t_order_item].[order_item_id]>(990913.)))

処理時間を計測します。上記sqlのset showplan_text on;とset showplan_text off;を外して実行します。
ざっくり1秒かかってないです。index有りはCPU timeも0 msとなかか高速です。

index有り:   CPU time = 0 ms,  elapsed time = 0 ms.
index無し:   CPU time = 678 ms,  elapsed time = 691 ms.

シーク法でのページジャンプ

全てのページの境界を求める

実行計画を取得します。

全てのページの境界を求める

set showplan_text on;
go
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        -- 10行でページング
        case ((row_number() over(order by order_date desc, product_id, order_item_id)) % 10)
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
      t_order_item t
    ) x
  where
    x.page_boundary = 1
  ;
  go
set showplan_text off;
go

実行計画と処理時間

実行計画がよくわからない。。。

index有り

-----------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]+(1)))
       |--Sequence Project(DEFINE:([Expr1003]=row_number))
            |--Segment
                 |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END))
                      |--Filter(WHERE:(CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END=(1)))
                           |--Sequence Project(DEFINE:([Expr1001]=row_number))
                                |--Segment
                                     |--Sort(ORDER BY:([t].[order_date] DESC, [t].[product_id] ASC, [t].[order_item_id] ASC))
                                          |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item] AS [t]))

index無し

-----------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]+(1)))
       |--Sequence Project(DEFINE:([Expr1003]=row_number))
            |--Segment
                 |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END))
                      |--Filter(WHERE:(CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END=(1)))
                           |--Sequence Project(DEFINE:([Expr1001]=row_number))
                                |--Segment
                                     |--Sort(ORDER BY:([t].[order_date] DESC, [t].[product_id] ASC, [t].[order_item_id] ASC))
                                          |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item] AS [t]))

index有無は処理時間の差はわずかです。elapsが約9秒ですが、実際に10万行表示は30秒以上かかってます(ストップウォッチで図るの面倒)。
恐らく標準出力に書き出し終わったのが9秒で、画面描画やりきるのに30秒かかっているのだと思います。
この辺、データベースの違いが出ていて面白いです。

index有り:   CPU time = 6943 ms,  elapsed time = 8406 ms.
index無し:   CPU time = 7091 ms,  elapsed time = 8559 ms.

DB2

実行計画取得の仕方を参考にしたURL:https://www.slideshare.net/simosako/db2-30672626

EXPLAIN表の作成

一度コネクトしてから抜けて作る。

connect to testdb;
quit;

コンテナ内部のbashコマンドプロンプトで次のコマンドを実行します。

コンテナ内部

db2 -tvf ~/sqllib/misc/EXPLAIN.DDL

DB2のCUIツールを起動するときは、-tオプションで起動すること。
こうしないと複数行に渡るsql文をコピペで貼り付けられないので。
(改行文字でsqlを実行してしまうため)

コンテナ内部

db2 -t

testdbに接続します。

connect to testdb;

最初の10行を取得

実行計画を取得します。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

最初の10行を取得

set current explain mode explain;
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 0
  ;
set current explain mode no;

実行計画と処理時間

DB2では、実行計画を見やすく整形するツールがあるので、これを利用します。
コンテナ内部のbashコマンドプロンプトでdb2exfmtコマンドを使います。

コンテナ内部

db2exfmt -1 -d testdb -o explain-first10-index.txt
db2exfmt -1 -d testdb -o explain-first10-noindex.txt

情報がありすぎなので抜粋しています。見方がわからないですが、この後のシーク法の実行計画と比べてCostが大きいのでフルスキャンなんだと思います。

index有り

Access Plan:
-----------
        Total Cost:             11466.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       10
     TBSCAN
     (   2)
     11466.4
      11242
       |
       10
     SORT
     (   3)
     11466.4
      11242
       |
      1e+06
     TBSCAN
     (   4)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

index無し

Access Plan:
-----------
        Total Cost:             11466.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       10
     TBSCAN
     (   2)
     11466.4
      11242
       |
       10
     SORT
     (   3)
     11466.4
      11242
       |
      1e+06
     TBSCAN
     (   4)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

処理時間を計測します。

DB2のCUIツールはelaps時間を表示してくれないので、db2batchコマンドでsqlを実行してelaps時間を取得します。
(メンドクサイ)
上記sqlの本体部分をtop10.sqlとして切り出し、db2batchコマンドで実行してelaps時間を取得します。

コンテナ内部

db2batch -d testdb -f top10.sql

index有無で若干差が出ていますが、ざっくり1秒くらいです。

index有り:* Elapsed Time is:       0.805196 seconds
index無し:* Elapsed Time is:       0.805790 seconds

オフセット法による最後10行を取得

実行計画を取得します。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

オフセット法による最後10行を取得

オフセット法による最後10行

set current explain mode explain;
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 999990
  ;
set current explain mode no;

実行計画と処理時間

実行計画を整形します。

コンテナ内部

db2exfmt -1 -d testdb -o explain-offset-last10-index.txt
db2exfmt -1 -d testdb -o explain-offset-last10-noindex.txt

実行計画の見方が。。。

オフセット法による最後の10行(index有り)


Access Plan:
-----------
        Total Cost:             15595.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
     333333
     FILTER
     (   2)
     15595.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14431.3
      11242
       |
      1e+06
     SORT
     (   4)
     14431.3
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

オフセット法による最後の10行(index無し)

Access Plan:
-----------
        Total Cost:             15595.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
     333333
     FILTER
     (   2)
     15595.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14431.3
      11242
       |
      1e+06
     SORT
     (   4)
     14431.3
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

処理時間を計測します。

コンテナ内部

db2batch -d testdb -f offset.sql

index有無で若干差が出ていますが、ざっくり9秒くらいです。

index有り:* Elapsed Time is:       8.287903 seconds
index無し:* Elapsed Time is:       8.510650 seconds

オフセット法による最後11行

  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;

ORDER_ITEM_ID PRODUCT_ID ORDER_QTY UNIT_PRC  SHIPPING_ADDRESS_DESC ORDER_DATE
------------- ---------- --------- --------- --------------------- ----------
      990156.         5.      567.  8170.409 174AC4F98F            01/01/2019＜＝ここをキーにする
      991238.         5.        1.  4067.441 C27DC23DDA            01/01/2019
      992174.         5.       22.  8059.180 B59D2EE94D            01/01/2019
      992914.         5.      914.  5385.903 76DF69D2FB            01/01/2019
      993365.         5.      579.  1337.713 FE4BAB3543            01/01/2019
      994680.         5.      381.  2501.721 CA253B2F37            01/01/2019
      995542.         5.      755.  5127.181 2BAA3FEC3A            01/01/2019
      996232.         5.      539.  7789.173 618712FFBE            01/01/2019
      996577.         5.      717.  7552.965 B1B09F9C58            01/01/2019
      996581.         5.      644.  9716.192 171F2F7460            01/01/2019
      996726.         5.      402.  9129.561 80BFC8A14B            01/01/2019

  11 record(s) selected.

シーク法による最後の10行

実行計画を取得します。
本体のsql文はMySQLと同じです。

シーク法による最後の10行

set current explain mode explain;
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 990156 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;
set current explain mode no;

実行計画と処理時間

実行計画を整形します。

コンテナ内部

db2exfmt -1 -d testdb -o explain-seek-last10-index.txt
db2exfmt -1 -d testdb -o explain-seek-last10-noindex.txt

オフセット法と比べると、Costが段違いに少ないです。IXSCANといったキーワードが出てるので、インデックスが効いてそう。

シーク法による最後の10行(index有り)

Access Plan:
-----------
        Total Cost:             198.045
        Query Degree:           1


             Rows
            RETURN
            (   1)
             Cost
              I/O
              |
              10
            TBSCAN
            (   2)
            198.045
            53.6559
              |
              10
            SORT
            (   3)
            198.044
            53.6559
              |
            14.734
            FETCH
            (   4)
            198.035
            53.6559
          /---+----\
      14.734        1e+06
      IXSCAN   TABLE: DB2INST1
      (   5)    T_ORDER_ITEM
      98.4011        Q1
      38.9466
        |
       1e+06
  INDEX: DB2INST1
 IDX1_T_ORDER_ITEM
        Q1

シーク法による最後の10行(index無し)

Access Plan:
-----------
        Total Cost:             12883.9
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       10
     TBSCAN
     (   2)
     12883.9
      11242
       |
       10
     SORT
     (   3)
     12883.9
      11242
       |
     13.9128
     TBSCAN
     (   4)
     12883.9
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

処理時間を計測します。

コンテナ内部

db2batch -d testdb -f seek.sql

index有無に関係なくオフセット法と比べるとかなり高速です。

index有り:* Elapsed Time is:       0.009107 seconds
index無し:* Elapsed Time is:       0.619869 seconds

シーク法でのページジャンプ

全てのページの境界を求める

実行計画を取得します。

全てのページの境界を求める

set current explain mode explain;
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
          -- 10行でページング
          case ((row_number() over(order by order_date desc, product_id, order_item_id)) % 10)
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
    ) x
  where
    x.page_boundary = 1
  ;
set current explain mode no;

実行計画と処理時間

実行計画を整形します。

コンテナ内部

db2exfmt -1 -d testdb -o explain-boundary-index.txt
db2exfmt -1 -d testdb -o explain-boundary-noindex.txt

index有り

Access Plan:
-----------
        Total Cost:             16227.2
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
      40000
     FILTER
     (   2)
     16191.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14972.4
      11242
       |
      1e+06
     SORT
     (   4)
     14819.9
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

index無し

Access Plan:
-----------
        Total Cost:             16227.2
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
      40000
     FILTER
     (   2)
     16191.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14972.4
      11242
       |
      1e+06
     SORT
     (   4)
     14819.9
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

処理時間を取得します。

コンテナ内部

db2batch -d testdb -f boundary.sql

10万行表示し終わるのに40秒くらいかかってます。

index有り:* Elapsed Time is:      39.321630 seconds
index無し:* Elapsed Time is:      38.948654 seconds

sqlite

処理時間をコンソールに表示

.headers onはクエリ結果にカラム名のヘッダーをつけるため。

.timer on
.headers on

最初の10行を取得

実行計画を取得します。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

最初の10行を取得

  explain query plan
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 0
  ;

実行計画と処理時間

index有り

QUERY PLAN
|--SCAN TABLE t_order_item USING INDEX idx1_t_order_item
`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

index無し

QUERY PLAN
|--SCAN TABLE t_order_item
`--USE TEMP B-TREE FOR ORDER BY

処理時間を計測します。上記sqlのexplain query planを外して実行します。
index有無で若干差が出ていますが、ざっくり1秒くらいです。

index有り:Run Time: real 0.053 user 0.028097 sys 0.024213
idnex無し:Run Time: real 0.813 user 0.769735 sys 0.041614

オフセット法による最後10行を取得

実行計画を取得します。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。

オフセット法による最後10行を取得

オフセット法による最後10行

  explain query plan
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 999990
  ;

実行計画と処理時間

実行計画の見方が分からない。

sql: オフセット法による最後10行(index有り) QUERY PLAN |--SCAN TABLE t_order_item USING INDEX idx1_t_order_item `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

sql: オフセット法による最後10行(index無し) QUERY PLAN |--SCAN TABLE t_order_item `--USE TEMP B-TREE FOR ORDER BY

処理時間を計測します。
index有無で若干差が出ていますが、ざっくり15秒くらいです。

index有り:Run Time: real 12.043 user 8.530403 sys 3.503906
index無し:Run Time: real 19.638 user 10.585848 sys 9.024575

オフセット法による最後11行

  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;

order_item_id|product_id|order_qty|unit_prc|shipping_address_desc|order_date
993272|5|611|7099.066|HELDNF1O87|2019-01-01＜＝ここをキーにする
993288|5|718|4004.409|P60UWIVTPP|2019-01-01
994711|5|146|524.474|P8V2EJMK8N|2019-01-01
995546|5|912|59.209|GVCUXA1RKT|2019-01-01
997147|5|225|8210.519|6RBXR4R5PE|2019-01-01
997653|5|775|181.531|MRMVXY1SEC|2019-01-01
998014|5|593|9775.705|EPNZFR6SOI|2019-01-01
998552|5|288|6715.191|UC27EGLKII|2019-01-01
998808|5|850|2812.261|47K965649X|2019-01-01
999126|5|709|2253.525|WTHR81JDMX|2019-01-01
999748|5|693|7628.87|RKBHOBHJ94|2019-01-01
Run Time: real 13.039 user 9.328710 sys 3.700142

シーク法による最後の10行

実行計画を取得します。
本体のsql文はMySQLと同じです。

シーク法による最後の10行

  explain query plan
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( date('2019-01-01') > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( date('2019-01-01') = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( date('2019-01-01') = order_date and 5 = product_id and 993272 < order_item_id)
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;

実行計画と処理時間

オフセット法と比較すると違いが顕著です。USING INDEX idx1_t_order_itemとインデックス効果が出てそうなキーワードが出ています。

シーク法による最後の10行(index有り)

QUERY PLAN
|--MULTI-INDEX OR
|  |--INDEX 1
|  |  `--SEARCH TABLE t_order_item USING INDEX idx1_t_order_item (order_date<?)
|  |--INDEX 2
|  |  `--SEARCH TABLE t_order_item USING INDEX idx1_t_order_item (order_date=? AND product_id>?)
|  `--INDEX 3
|     `--SEARCH TABLE t_order_item USING INDEX idx1_t_order_item (order_date=? AND product_id=? AND order_item_id>?)
`--USE TEMP B-TREE FOR ORDER BY

シーク法による最後の10行(index無し)

QUERY PLAN
|--SCAN TABLE t_order_item
`--USE TEMP B-TREE FOR ORDER BY

オフセット法が15秒くらいに対して、1秒くらいなので、高速です。

index有り:Run Time: real 0.000 user 0.000422 sys 0.000105
index無し:Run Time: real 1.883 user 1.854988 sys 0.026341

シーク法でのページジャンプ

全てのページの境界を求める

実行計画を取得します。

全てのページの境界を求める

  explain query plan
  select
  x.*,
  row_number() over(
    order by
      order_date desc, product_id, order_item_id
  ) + 1 page_number
  from
  ( select
      case (row_number() over(
        order by
          order_date desc, product_id, order_item_id
      ) % 10)  -- 10行でページング
        when 0
        then 1
        else 0
      end page_boundary,
      t.*
    from
      t_order_item t
    order by
      order_date desc,
      product_id,
      order_item_id
  ) x
  where
  x.page_boundary = 1
  ;

実行計画と処理時間

index有り

QUERY PLAN
|--CO-ROUTINE 3
|  |--CO-ROUTINE 1
|  |  |--CO-ROUTINE 4
|  |  |  |--SCAN TABLE t_order_item AS t USING INDEX idx1_t_order_item
|  |  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  |  `--SCAN SUBQUERY 4
|  |--SCAN SUBQUERY 1 AS x
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 3

index無し

QUERY PLAN
|--CO-ROUTINE 3
|  |--CO-ROUTINE 1
|  |  |--CO-ROUTINE 4
|  |  |  |--SCAN TABLE t_order_item AS t
|  |  |  `--USE TEMP B-TREE FOR ORDER BY
|  |  `--SCAN SUBQUERY 4
|  |--SCAN SUBQUERY 1 AS x
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 3

index有無は処理時間の差はわずかです。実際に10万行表示するのに42秒くらいかかりました。

index有り:Run Time: real 42.422 user 14.213993 sys 3.987254
index無し:Run Time: real 41.138 user 16.528704 sys 1.048000

で、オフセット法に比べてシーク法のページネーションはどれだけ早いの？RDB毎に。

動機

何をやるか？

ページネーションを試すテーブルの作成(受注明細テーブルの作成)

インデックスの削除

インデックスの作成

100万件のランダムなテストデータを登録

MySQL

処理時間をコンソール表示

最初の10行を取得

オフセット法による最後10行を取得

オフセット法による最後11行

シーク法による最後の10行を取得

シーク法でのページジャンプ

全てのページの境界を求める

PostgreSQL

処理時間をコンソールに表示

最初の10行を取得

オフセット法による最後10行を取得

オフセット法による最後11行

シーク法による最後の10行

シーク法でのページジャンプ

全てのページの境界を求める

Oracle

処理時間をコンソールに表示

最初の10行を取得

オフセット法による最後10行

オフセット法による最後11行

シーク法による最後の10行

シーク法でのページジャンプ

全てのページの境界を求める

SQLServer

処理時間をコンソール表示

最初の10行を取得

オフセット法による最後10行

オフセット法による最後11行

シーク法による最後の10行

シーク法でのページジャンプ

全てのページの境界を求める

DB2

EXPLAIN表の作成

最初の10行を取得

オフセット法による最後10行を取得

オフセット法による最後11行

シーク法による最後の10行

シーク法でのページジャンプ

全てのページの境界を求める

sqlite

処理時間をコンソールに表示

最初の10行を取得

オフセット法による最後10行を取得

オフセット法による最後11行

シーク法による最後の10行

シーク法でのページジャンプ

全てのページの境界を求める