一般的に、インデックスを作りすぎるとINSERTやUPDATEのパフォーマンスに影響を与えるため、よろしくないとされている。今回は実際にはどういう現象を発生させるのか、を見てみる。

やること

100列のテーブルを作り、INSERTとUPDATEの速度を、インデックス数を1,10,50,100と変化させて計測する。インデックスの個数は変化させるが、INSERTやUPDATEは同一のものを使用する。これにより、インデックスの作りすぎがどのような現象を生むか、を考えるための材料とする。

環境

ソースコード

準備

INTEGER型の列を100個持つテーブルを作る。なお、SQLが長いので途中は省略している。

DROP TABLE many_indexes_practice PURGE;
CREATE TABLE MANY_INDEXES_PRACTICE
  (
    COLUMN001 INTEGER ,
    COLUMN002 INTEGER ,
    COLUMN003 INTEGER ,
    COLUMN004 INTEGER ,
    --（中略）
    COLUMN097 INTEGER ,
    COLUMN098 INTEGER ,
    COLUMN099 INTEGER ,
    COLUMN100 INTEGER
  );

インデックスを作る。実行するcreate index ...の個数は適宜変更する。

create index IDX_MANY_INDEXES_001 on many_indexes_practice (COLUMN001);
create index IDX_MANY_INDEXES_002 on many_indexes_practice (COLUMN002);
create index IDX_MANY_INDEXES_003 on many_indexes_practice (COLUMN003);
create index IDX_MANY_INDEXES_004 on many_indexes_practice (COLUMN004);
create index IDX_MANY_INDEXES_005 on many_indexes_practice (COLUMN005);
create index IDX_MANY_INDEXES_006 on many_indexes_practice (COLUMN006);
create index IDX_MANY_INDEXES_007 on many_indexes_practice (COLUMN007);
--（中略）
create index IDX_MANY_INDEXES_096 on many_indexes_practice (COLUMN096);
create index IDX_MANY_INDEXES_097 on many_indexes_practice (COLUMN097);
create index IDX_MANY_INDEXES_098 on many_indexes_practice (COLUMN098);
create index IDX_MANY_INDEXES_099 on many_indexes_practice (COLUMN099);
create index IDX_MANY_INDEXES_100 on many_indexes_practice (COLUMN100);

各INSERT,UPDATEの前にバッファキャッシュをクリアする。

ALTER SYSTEM FLUSH BUFFER_CACHE;

計測対象のSQL

INSERT

ダイレクトパスインサートで1万行追加する。

INSERT
  /*+ APPEND */
INTO MANY_INDEXES_PRACTICE
  (
    COLUMN001 ,
    COLUMN002 ,
    COLUMN003 ,
  --（中略）
    COLUMN099 ,
    COLUMN100
  )
SELECT ROWNUM ,
  ROWNUM ,
  ROWNUM ,
  ROWNUM ,
  --（中略）
  ROWNUM ,
  ROWNUM
FROM
  (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000
  ),
  (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 10
  ) ;
COMMIT;

UPDATE

全行UPDATEで各行各列にプラス100,000をする。

UPDATE MANY_INDEXES_PRACTICE 
SET
COLUMN001 = COLUMN001 + 100000
,COLUMN002 = COLUMN002 + 100000
,COLUMN003 = COLUMN003 + 100000
  --（中略）
,COLUMN099 = COLUMN099 + 100000
,COLUMN100 = COLUMN100 + 100000
;
COMMIT;

計測結果

インデックスの数を、1,10,50,100と変更し、INSERTとUPDATEをそれぞれ3回実行した結果が以下の通り。

	1	2	3	AVG
INSERT 1	0.820	0.805	0.829	0.818
INSERT 10	1.195	1.212	1.230	1.212
INSERT 50	3.667	3.211	3.297	3.392
INSERT 100	11.716	11.669	13.780	12.388
UPDATE 1	1.531	1.968	1.492	1.664
UPDATE 10	5.055	5.097	5.638	5.263
UPDATE 50	37.714	30.232	28.886	32.277
UPDATE 100	102.055	98.456	106.305	102.272

AVGの値でテキトーな折れ線グラフを作るとこんな感じ。等間隔にデータが無いのでちっと見た目がビミョウだが……

f:id:kagamihoge:20150201180649p:plain

感想とか

グラフだけを見ると、INSERTはまぁなんとなく遅くなっており、UPDATEはまぁそれなりにガツンと遅くなっている、ように見える。細かいことはともかく、インデックスが増えれば増えるほどINSERTやUPDATEは遅くなる、ということに間違いは無い。

しかし、これは計測用のSQLにかなり依存した結果なことに注意が必要である。

INSERTはダイレクトパスインサートで元から早い。さらに、インデックスの末尾にただ単純にデータを入れていくだけなので、インデックスのメンテナンスコストも低い。逆順にデータを追加したり、ランダムにデータを追加したりすると、また違った結果になると思われる。*1

UPDATEは、グラフを見ると指数関数的とまでは言わないものの、インデックスの個数に正比例して遅くなるわけではないらしい。今回は速度計測だけ見るのが目的で、statspackレポート取得して分析する気は無かったから、詳細はわからんのだけど。コチラの場合、インデックスは更新対象行に対応するノードをDELETしてINSERTするので、比較的負荷の高い処理となる。UPDATE実行前と後でインデックスのブロック数を確認してみる。

SELECT SEGMENT_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS
  WHERE 
    OWNER = 'KAGAMIHOGE'
    AND SEGMENT_TYPE = 'INDEX';

	BYTES	BLOCKS	EXTENTS
（前）	196608	24	3
（後）	393216	48	6

計測対象のUPDATEは全行に対してDELETE-INSERTをするので、論理的な行数は変わらなくとも、インデックスのサイズは2倍になる。INSERTに比べてUPDATEが圧倒的に遅いのは、OracleのUPDATEは元々重い処理なうえに、インデックスのサイズがでかくなるような処理を行う辺りにある、と考えられる。とはいえ、これもstatspackレポート取るとかして数値データに基づいて考えないと、ハッキリしたことはいえない。

まとめとか

というわけで、statspackレポート取得しなかったから割とふんわりした内容になってしまったが……インデックスの数が増えれば増えるほどINSERTやUPDATEは遅くなる傾向にあることだけ確かである。ただし、どのように遅くなるかはINSERTやUPDATEがインデックスにどういう影響を与えるか、に依存すると思われる。

あとこれは完全に余談なんだけど。インデックス作ると遅くなるからつって、マッタクインデックス作らないのも考えモンである。しかし逆に、考えなしにインデックスを乱発するいわゆる「インデックスショットガン」もまた考えモンである。じゃあ何が正解かというと、環境に依存する要素が多いんで、計測してアレコレ試行錯誤するしかないらしい。

インデックスの効果を最大限に高めるために必要な情報はベンダーによって異なるため、使用しているデータベース製品の詳しい知識が必要です。（中略）最も重要なルールは、推測のみに基づいて、闇雲にインデックスをつけてはならないということです。

SQLアンチパターン第12章インデックスショットガン（闇雲インデックス）12.5 解決策「MENTOR」の原則に基づいて効果的なインデックス管理を行う　より抜粋