はじめに
本記事ではSQLのインサート(INSERT)が遅い・もしくは遅くなる原因について、
実際の検証結果を交えながら動作とその対処法について解説していきます。
#目次
- 誰に向けたものか
- インサートが遅い原因について
- おわりに
#誰に向けたものか
SQLのインサートが遅いと感じている開発者、運用者に向けての記事になります。
尚、今回使用したDBは ORACLE 11g R2 となります。
#インサートが遅い原因について
インサートが遅い原因は大きく分けて3つあります。
第一に 1. 索引の数が多すぎないか？
第二に 2. シーケンスにCACHEの設定をしているか？
最後に 3. コミットのタイミングはいつなのか？
早速、原因についてみていきましょう。
##1.索引の数が多すぎないか?
こちらは皆さんも一度は耳にしたことがあるかもしれません。
索引の数が多ければ多いほどインサートが遅くなるといったものです。
こちらについて解説する前に先ず、そもそものインサートの動作に触れていきます。
インサートの動作としてテーブルに対してレコードを追加すると同時に、
索引の更新も行っています。また、索引の更新は表に紐づくすべての索引に対して行われます。
順を追って見てみると、
1.インサートを発行する。
2.テーブルにレコードが追加される。
3.テーブルに紐づく索引すべてが更新される。
3.の動作に注目していただきたいのですが、索引数が多ければ多いほど、
索引に対する更新も発生するので必然インサートも遅くなるというものです。
この動作について実機検証してきましたので、以下のグラフをご覧ください。
10万件インサートしたときの実行時間の推移となります。
縦軸が実行時間、横軸がインデックスの数です。
インデックスが1個の時は13秒で終わっている処理が、
インデックスが10個になると100秒ほどかかるといった結果になりました。
その差、なんと87秒。
思った以上に、実行時間に差があることが分かったのではないでしょうか。
この結果を念頭に置いた上で、以下の3点を意識するようにしてください。
・むやみやたらに索引を作成しない。
・索引数は5個程度に抑える。
・索引を新たに追加する場合はインサート処理が遅くならないかテストを行う。
難しいことではないので意識してもらえたらと思います。
##2. シーケンスにCACHEの設定をしているか？
では次に考えられるシーケンスの設定について解説していきます。
#本記事ではシーケンスについての説明は割愛させていただきます。
通常シーケンスをNEXTVALするとDB上で番号の発行を行う動作になりますが、
「とある設定」をすることでシーケンスを共有プールと呼ばれるメモリ上に
キャッシュしておくことができます。
これによってシーケンスの取得を高速化することができます。
「とある設定」というのが「CACHE」の設定です。
例えばCACHEの値を20に設定すると「1～20」までの番号が
メモリ上にキャッシュされる動作となります。
設定方法としては以下のようになります。
--シーケンスにCACHE 20を設定する。
--新規作成
CREATE SEQUENCE SQ_EMP_01
START WITH 1
INCREMENT BY 1
MAXVALUE 999999999
CACHE 20;
--変更
ALTER SEQUENCE SQ_EMP_01 CACHE 20;
/****************************
* 説明
* START WITH <Num> ：初期値
* INCREMENT BY <Num>：増分値
* MAXVALUE <Num>：最大値
* CACHE <Num>：CACHEしないときは NOCACHE
****************************/
CACHEの設定の仕方も分かったので設定した場合と設定しなかった場合で、
どれだけインサートのパフォーマンスに影響を及ぼすのかを見ていきましょう。
以下のグラフは20万件インサートしたときの実行時間の推移となります。
縦軸が実行時間、横軸がCACHE値です。
グラフから見て分かるとおりCACHE 0(NOCACHE)の場合だと107秒、
CACHE 1000の場合だと91秒。その差は16秒。
いずれの結果もNOCACHEに比べCACHEを設定した方が速いということが分かりました。
そのためシーケンスを利用する場合は基本的にCACHEを設定するようにしましょう。
どれくらいに設定すればよいか分からないという方は、とりあえずCACHE 20を設定して、
経過観察していただければよいかなと思います。
但し、注意点がいくつかあります。
メモリ上にシーケンスを乗せておくので、DBを再起動する場合・共有プールをフラッシュ場合には
メモリに乗っていたシーケンスがフラッシュされます。
これによってシーケンスに欠番が発生します。
欠番を許容しない設計のテーブルでは注意して設定を行ってください。
##3. コミットのタイミングはいつなのか？
では最後の原因、コミット(commit)のタイミングについて解説していきます。
皆さんはコミットのタイミングを意識されていますでしょうか。
コミットを行うとオーバヘッドが発生します。
1件単位と1000件単位でコミットを行う場合を比較すると
1件単位でコミットを行うほうがコミットの発行回数も増え、
その分オーバヘッドによる遅延が発生します。
これはインサートに限った話ではないですが、1例として検証してきました。
このグラフも20万件インサートしたときの実行時間の推移となります。
縦軸が実行時間、横軸がコミットの単位です。
1件毎にコミットするよりも複数件ずつコミットした方が速いといった結果になりました。
最大で19秒。
こちらの結果を参考にコミットのタイミングについて見直してみては如何でしょうか。
但し、コミットについても注意点があるので説明しておきます。
先ず、インサートされたデータはコミットを実行するまで更新前のデータが
UNDO表領域に保持されます。
コミットの感覚を長くすればするほどUNDO表領域を使用することになり、
ORA-01555などのORACLEエラーを発生させてしまう可能性が高くなります。
また、ロールバック(rollback)する際にUNDO表領域から戻すデータ量が大きくなりすぎて、
戻す時間が想定以上にかかることがあります。
なので大量データをインサートする時は大体1万件単位でコミットすることを目安としてください。
#おわりに
今回は基本的な3点を解説させてもらいました。
これらの点を意識するだけで大きなパフォーマンス改善が見込めることがありますので、
是非活用してください。
ここまでご覧いただきありがとうございました。