一般に、DB運用のアドバイスとして使われないインデックスは削除すべき、とある。ディスクのムダはもとより、インデックスのメンテナンスコストの増大は性能劣化に繋がるからである。
というわけで、今回はインデックスを大量に付与したとき何が起こるかを見てみる。ぶっちゃけありえない状況ではあるとは思うのだが、単一のテーブルにインデックスを100とか200とか作って、INSERT,UPDATE,DELETEを実行し、実行時間の変化を見る。
準備
実験対象のテーブルを作る。
create table hoge (hoge_id integer not null);
データを1万件入れる。
insert into hoge select rownum from (select rownum from all_catalog where rownum <= 1000), (select rownum from all_catalog where rownum <= 10); commit;
実は今回のエントリ書くまで、同一列に対するインデックスは複数作れないとは知らなかった。まぁ当たり前なんだけども…… 列を大量に持つテーブル作って、複合インデックスの組み合わせ量産しようかとも考えた。この方がより実運用でありえるし。が、組み合わせを作るのめんどくさいのでやめた。よって、ファンクションインデックス大量に作ることにした。
要するに、下記のようなインデックスを作りまくる。よってこのエントリでの、単一のテーブルに対する大量のインデックス、とは下記のようなインデックス作成の集まりとなる。
create index ind_hoge1 on hoge(hoge_id || 1); create index ind_hoge2 on hoge(hoge_id || 2); create index ind_hoge3 on hoge(hoge_id || 3);
連番のふられたcreate indexのSQL文は下記のようなSQLで作成する。
select 'create index ind_hoge' || rownum || ' on hoge(hoge_id || ' || rownum || ');' from (select rownum from all_catalog where rownum <= 1000);
ここから、速度計測対象のSQL文。
各INSERT, UPDATE, DELETEを発行する前に、バッファキャッシュをクリアしておく。実行時間にクリアの時間は含めない。
alter system flush buffer_cache;
INSERT
なお、実行時間はINSERT/UPDATE/DELETEとCOMMITの合計。
insert into hoge(hoge_id) values (10001); commit;
UPDATE
INSERTで入れた10001を10002にUPDATEする。
update hoge set hoge_id = hoge_id + 1 where hoge_id = 10001; commit;
DELETE
UPDATEで修正した10002をDELETEする。
delete from hoge where hoge_id = 10002; commit;
INSERT -> UPDATE -> DELETE の順に3セット実行し、実行時間を取る。インデックスの数を0,100,200,300,400,500で試行。
3セットの計測が終了したらテーブルを削除し、テーブル＆データ＆インデックス（さっきの試行からプラス100）を作りなおす。オマケでDROP TABLEにかかった時間も取る。
drop table hoge purge;
実行結果
下記は、3回実行したものの平均値を取ったもの。
| 0 | 100 | 200 | 300 | 400 | 500 | |
|---|---|---|---|---|---|---|
| INSERT | 0.031 | 0.062 | 0.083 | 0.115 | 0.162 | 0.183 |
| UPDATE | 0.026 | 0.072 | 0.146 | 0.203 | 0.255 | 0.344 |
| DELETE | 0.037 | 0.062 | 0.094 | 0.135 | 0.182 | 0.219 |
オマケのDROP TABLEの実行時間。インデックスつけまくるとDROPするのもそれに比例して時間がかかるようになる。
| 0 | 100 | 200 | 300 | 400 | 500 | |
|---|---|---|---|---|---|---|
| DROP | 0.078 | 6.625 | 12.125 | 18.578 | 24.484 | 31.391 |
感想とか
インデックス500個とか正気の沙汰ではないので、こんなことの実行時間にどれほどの意味があるのか疑問の余地は多いにあるのだけど。
まず、INSERT,UPDATE,DELETE時のインデックスのメンテナンス処理の概要について。
新しい行の追加 リーフブロックへ索引エントリを追加する
行の削除 リーフブロックから索引エントリを削除する
索引列の列値の更新 リーフブロックから古い索引エントリを削除し、新しい列値に対応したリーフブロックに索引エントリを追加するプロとしてのOracleアーキテクチャ入門 Oracle現場主義 CHAPTER 10 索引の仕組み Bツリー索引 Bツリー索引のメンテナンス p.167 表10-02 を抜粋
実行時間のかかりかたは、おおよそ上記引用に沿った形になっている。UPDATEが一番酷くて、DELETE,INSERTの順。SQLの正味にかかる時間―インデックスの数ゼロのもの―を、引いてDELETEプラスINSERTすると、まぁなんとなくUPDATEの実行時間になるような雰囲気。インデックスに更新がかかるような処理はなるべく避けるべし、と言われるのが良くわかります。
とまぁそんなわけで、一般に言われているようにインデックスを作れば作るほどINSERT,UPDATE,DELETEは重くなるよ、あとOracleはUPDATE時はDELETE -> INSERTするので特にUPDATEは重くなるよ、ということが確認できたと思います。
