読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

インデックスがタクサンあるときのINSERTとUPDATEの速度

一般的に、インデックスを作りすぎると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」の原則に基づいて効果的なインデックス管理を行う より抜粋

参考文献

SQLアンチパターン

SQLアンチパターン