動機
オフセット法とシーク法についてはこちらで紹介していますが(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。
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行を取得
最初の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行
オフセット法による最後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行
シーク法による最後の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表の作成
実行計画を格納する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)
処理時間を計測します。上記sqlのexplain plan forとselect plan_table_output from table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));を外して実行します。
index有無で若干差が出ていますが、ざっくり9秒くらいです。
index有り:Elapsed: 00:00:08.95
index無し:Elapsed: 00:00:09.29
オフセット法による最後11行
この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文はMySQLやPostgreSQLとちょっと違います。
オフセット法による最後11行
オフセット法による最後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行
シーク法による最後の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行
オフセット法による最後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行
シーク法による最後の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行を取得
最初の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行
この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。
オフセット法による最後11行
オフセット法による最後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行
シーク法による最後の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行を取得
最初の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
;
実行計画と処理時間
実行計画の見方が分からない。
オフセット法による最後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
オフセット法による最後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行
この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文はMySQLと同じです。limitとoffsetの組み合わせです。
オフセット法による最後11行
オフセット法による最後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行
シーク法による最後の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