最近、めっきり物覚えが悪くなった。それどころか、昔勉強したことも忘れる始末である。これが歳をとったということか・・・！ SE定年35年説は正しかったのかなと思うところである。いや、実際はアラフォーなんですけどね。アハハハ・・・。
コホン。
そんなわけで、こりゃヤバイという危機感を元に、昔勉強したことを再度復習しながらブログにしてみましたというやつである。また忘れてもブログ見ればいいやというのが主目的ではあるが。
と、いうわけで、今回はデータベースの話。応答を高速化したい場合には、部分範囲処理という考え方が有効ですよという話を前に本で読んだのでそれをまとめてみた。
部分範囲処理とはどういうものなのか？
部分範囲処理とは、SQLのWHERE節で与えられた条件を満たす範囲全体を処理しないで、先に運搬単位（Array Size）の量だけを処理し、その結果を抽出した後に次の作業を続けるという処理方式のことである。要するに条件を満たすデータが一定量見つかったらその時点で取得処理を一時停止して応答を返すような処理である。
この反対が、すべての検索結果を取得してから返す処理である、全体範囲処理である。
部分範囲処理は、データが一定量取得出来た時点で応答が返るので、応答時間が早くなる。全体範囲処理の場合、すべての処理が終わらないと応答が返らないので応答時間は遅くなる。
部分範囲処理を行うための前提条件
部分範囲処理を検討する前に次の前提条件が満たしているか確認する必要がある。
- インデックスが適切に設定されていること
- データベースが部分範囲処理に対応していること
インデックスが設定されていない場合、検索条件に合致するレコードが存在するかを調べるためには、テーブルを最後まで検索しないと結果を確定出来ない。そのため、途中で停止することは出来ない。
また、データベースが部分範囲処理に対応していないと無意味なので、SELECTの応答仕様を確認する。具体的には、一定量を満たしたら応答が返るか、すべて取れないと返せないかを確認する。
簡単な確認方法としては、次の方法がある。
まずは、数万件以上のレコードを持つテーブルを用意する。次に、このテーブルに対し、WHERE節なしでSELECTしてみる。この時、結果がすぐ出力され始める場合は、部分範囲処理に対応していると言える。しばらく待ってからの場合は全体範囲処理である。ただし、一定時間応答が出力されないのは使ったSQLクライアントの仕様の可能性もある（すべての結果を取得した後ではないと出力しないなど）のでそれも確認しておく。
部分範囲処理に向く処理
一つは、検索対象となるテーブルのデータ量は大量にあるが、求める結果は少なくてよいケースである。主なケースとしては、Webシステムの検索などが該当する。ブラウザ上に表示するデータはヒットする件数がたとえ多かったとしても、すべて表示するのではなく、一部のデータを表示すればいいケースがほとんどである。
もう一つは、レコードそのものが習得の目的ではなく、チェックが目的のケースである。特定の条件に合致するレコードが1件でもあればいい場合などが該当する。
また、部分範囲処理は、一般的にアクセス主管列の抽出範囲が狭く、その他の列のチェック条件の範囲が広い程、一定量のデータを集めやすいため応答時間は良くなる。
また、アクセス主管列の処理範囲が広かったとしても、他の列のチェック条件の範囲も同じく広ければ、同様に一定量のデータを集めやすいため応答時間は良くなる。
部分範囲処理に向かない処理、出来ない処理
一つは、SQLの実行計画にSORTが登場する場合である。order byによるソートを行う場合、全体を取得してからでないとソート出来ないため、部分範囲処理にはならない。
distinctを用いた場合はSORT(UNIQUE)になるがこれも同様に部分範囲処理にはならない。
ただし、order byの場合、ソート項目がインデックス化されている場合はソート処理が発生しないため、部分範囲処理にすることが可能である。
二つ目は、実行計画にVIEWが登場する場合である。VIEWが登場する場合、取得結果をテンポラリの保存空間に書き込みを行ったということなので、その部分は全体範囲処理となる。
三つ目は、グループ関数を使用している場合である。SUM等の集計処理は全体範囲処理をしないと集計できないためである。
最後に、出来ないというよりも向かないという処理であるが、それはバッチ系の処理である。Webシステム等のオンライン系は、応答時間が重要になるが、バッチ処理はスループット（単位時間あたりに何件処理できるか）が重要になるためである。無理に部分範囲処理を採用した場合、かえってスループットは低下する可能性がある（例えば全レコードを必要とする場合、インデックス経由でアクセスするよりはテーブル自体にアクセスしてしまった方が早い）。
部分範囲処理の誘導方法
インデックスを使用し、order byを取り除く
インデックスが存在する場合、ソート済なので、order byは不要となる。検索対象のデータは大量にあるが、必要なデータ数は少なくてよい場合は、ソート目的でインデックスを設定すると部分範囲処理へと導くことができる。ただし、必要なデータ量も多くなる場合は、かえって処理効率は落ちるので注意すること。
EXISTSを活用する
ある条件のレコードが存在することを確認する時に、count(*)を使用した場合は、全体範囲処理になるが、EXISTSを利用した場合は、部分範囲処理となる。EXISTSを利用すると、レコードが存在した時点で処理を辞めれるためである。条件に一致するレコードが多くなるほど、EXISTSの方が有利となる。
例
適用前
SELECT COUNT(*) FROM TABLE_A WHERE COLUMN_A = X
適用後
SELECT 1 FROM DUAL
WHERE EXISTS (
SELECT COLUMN_A FROM TABLE_A
WHERE COLUMN_A = X
)
※厳密に言うと、適用後のものは結果がなければ応答結果が0ではなくNULLになる
MAX,MINを活用する
MAX,MINを適用する列に対してインデックスを設定している場合、アクセスはインデックスの先頭、または末尾にのみアクセスすればよいので部分範囲処理となる。
応用として、アクセスを主管とする列とMAXを求めようとする列のみの結合インデックスにすると、他の追加条件は影響を受けないのでアクセスが安定する。インデックス順にアクセスしていけば、条件に一致するMAX値を求めるのは簡単なためである。
ROWNUMを活用する
取得件数を制限したり、数件のみ必要な場合は、ROWNUMを活用することで部分範囲処理へと導ける。ただし、ORDER BYが入っている場合、ソート処理を実行してからROWNUMの制限が入るため、全体範囲処理となるので注意すること。
例
SELECT * FROM (
SELECT * FROM TABLE_B ORDER BY REGIST_DATE )
WHERE ROWNUM <= 10
上記の例の場合、REGIST_DATEにインデックスを設定すれば、ソート済となるため部分範囲処理へと導ける。
インラインビューを活用する
ある箇所が全体範囲処理になってしまい、それが応答時間の悪化につながっている場合、その部分を切り出してインラインビューにしてみると部分範囲処理へと導けることがある。
例
【適用前】
SELECT
a.product_cd, product_name, avg_stock
FROM PRODUCT a,
( SELECT
product_cd, SUM(stock_qty) / (:b2 -:b1 ) avg_stock
FROM PROD_STOCK
WHERE
stock_date between :b1 and :b2
GROUP BY product_cd ) b
WHERE
b.product_cd = a.product_cd
AND a.category_cd = ’20’;
【適用後】
SELECT
a.product_cd, product_name,
(
SELECT
SUM(stock_qty) / (:b2 -:b1 )
FROM PROD_STOCK b
WHERE
stock_date between :b1 and :b2
AND b.product_cd = a.product_cd
)as avg_stock
FROM PRODUCT a,
WHERE a.category_cd = ’20’;
SQLは、category_cdが20であるproduct_cdに対して、与えられた期間内の平均在庫数量を求めるものである。
適用前のSQLは、GROUP BY句が存在するため、全体範囲処理となる。故に集計がすべて完了するまでは応答が返らない。category_cdが20であるレコードが多数存在する場合、レスポンスが悪くなる。
これに対し、適用後のSQLは以下の順で実行される。
-
category_cdが20であるレコードを一行PRODUCTテーブルから読む
- 取得したproduct_cdを元にPROD_STOCKから平均在庫を求める
- 一定量レコードを取得するまで、①～②を繰り返す。満たしたら、その時点で一度レスポンスを返し処理を中断する。
適用後のSQLは、処理全体の時間自体は変わらないが、結果を早期に受け取れ、取得処理を中断することが可能となる。
おまけ データベースの部分範囲処理対応状況
Oracleの場合
対応している。参考文献の本は基本的にはOracleベースの解説なので当然といえば当然か。
運搬単位(ARRAYSIZE)は、SQL*PLusでは SHOW ARRAYSIZEで確認可能だ。また、Pro*Cの場合は、たぶん、SQLBLOCKMAXで調整する(未確認)。
取得処理の一時停止は、SQLDeveloperは対応している(テーブルアクセス時、一気に取得するわけではなく、スクロール毎に取得する)。SQL*PLusはSET PAUSEで停止可能。
また、オプティマイザのモードによっては、部分範囲処理にならないこともある。モードがALL_ROWSの場合、ソート目的でインデックスを設定していても、使用されずにFULL SCANになることもある。FIRST_ROWSの場合は、想定通りの動きをする。
オプティマイザのモードは、show parameter optimizer_mode(sysdbaで実行)で確認出来る。
なお、特定のSQLの場合のみFIRST_ROWSで取得したい場合は、ヒント句で/*+ FIRST_ROWS */を指定すればよい。
PostgreSQLの場合
一部対応している？ 確認したバージョンは9.3
SELECT*で大量レコードが存在するテーブルを検索しても、即座に応答は返らず、一定時間経過後なので部分範囲処理には対応していないように見える。
しかし、limitを指定した場合は応答は早い。実行計画を見る限り、全件取得してから件数制限をするのではなく、指定した件数の取得が出来たらその時点で処理を終えている模様。そのため、部分範囲処理に対応していると言えそうだ。
ただし、Oracleとは異なり部分範囲処理の取得処理は中断ではなく、終了となる。継続して読み込みたい場合は、再度SQLの実行が必要となる。
運搬単位という概念は存在しない模様。
取得処理も、クライアント(pgAdmin)では一括取得のみ対応っぽい。limitで制限すれば早く取得は出来るが、継続して取得は出来ない。大量レコードのテーブル表示に時間がかかるのも結果をすべて受け取ってから表示しているからかもしれない。
オプティマイザのモードは特になく(基本的にはALL_ROWS？)、またヒント句の指定も標準では出来ない。追加パッケージの導入で可能となる模様。
MySQLの場合
対応していない(ように見える)。確認したバージョンは、5.6
大量レコードが存在するテーブルに対するSELECT*はPostgreSQLと同様、一定時間経過後に出力された。また、limitを指定した場合でも、実行計画を見るとFetch件数は全レコード件数分あったので、部分範囲処理をしていない。
limitは実行計画には登場しないので、いまいち判断がつかないが、どうも取得した後にlimitが適用されているようである。インデックスが存在した場合も、条件に一致したレコードをすべて取り出してからlimitが適用されていた(ある条件で抽出できる件数が40件あったとして、limit 10としても、実行計画上の件数は40件となっていた。部分範囲処理に対応しているのならば10件となるはず)。
運搬単位という概念も存在しない模様。
取得処理も一括取得のみ。
オプティマイザのモードも特になく、基本的にはALL_ROWSで動作する。
参考文献
