by Hayato Matsuura.

挿入


テーブルあたりのインデックスの数は、insertの パフォーマンスに対して最も影響を与える要素です。テーブルにたくさんのインデックスがあれば、実行もそれだけ遅くなります。insert文は、where句を持たないことから、 インデックスの利点を直接受けられない唯一の処理です。

新しい行をテーブルに追加するのは、いくつかのステップからなっています。まず最初に、データベースは行を追加する場所を探します。 一般的なヒープテーブルでは、行の順序が決まっていないため、データベースは空き容量があるテーブルブロックであればどれでも使用可能です。 これは非常にシンプルで高速な処理で、ほとんどはメインメモリ上で行われます。その後にデータベースが行うのは、それぞれのブロックに新しいエントリを 追加するだけです。

テーブルにインデックスがある場合、データベースはインデックスを使って新しいエントリを見つけられるようにしなければなりません。そのため、 新しいエントリはテーブルの全てのインデックスにも追加されます。従って、インデックスの数はinsert文の実行コストの乗数になります。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

それに加えて、データベースはインデックスの順序とツリーのバランスを保たねばならない事から、インデックスに新しいエントリを追加するのは ヒープ構造にエントリを追加するよりもかなり重い処理になってしまいます。インデックスに対する新しいエントリは、どこに書き込んでもいいわけではなく、 どれか特定のリーフノードに 属する事になるのです。また、データベースが自分でリーフノードを探す際もインデックスツリーを使うので、 ツリーの走査のためにインデックスのブロックへのアクセスも発生します。

目当てのリーフノードが見つかったら、データベースはそのノードに十分な空き容量があるかを確認します。もし空き容量がなければ、 データベースはリーフノードを分割し、エントリを新旧のノード間で分散します。このプロセスにより、 同じように複製しなければならない、対応するブランチノードからの参照にも 影響があります。言わずもがなですが、ブランチノードの空き容量が不足していれば、こちらも同じように分割しなければなりません。最悪のケースでは、 データベースはルートノードにぶら下がる全てのノードを分割しなくてはならないのです。ただしこれは、層を増やしてツリーの階層が深くなる場合に 限ります。

つまり、インデックスのメンテナンスはinsert操作に おいて最も重い処理であると言えるでしょう。それは、図, 「インデックスの数による挿入のパフォーマンス変化を見ても明白です。テーブルに全くインデックスが ない時は、実行時間はほとんど分からないくらい短くなります。しかし、1つインデックスを増やしただけで実行時間は百倍近くも増えてしまいます。 さらにインデックスを増やすと、実行時間はどんどん長くなっていきます。

図インデックスの数による挿入のパフォーマンス変化

注記

1つインデックスを作るだけで、非常に大きな違いが生まれます。

insertのパフォーマンスを最適化するには、 インデックスの数を小さく保つ事が非常に重要です。

ヒント

インデックスは注意深くかつ慎重に使い、 かつ、可能な限り冗長なインデックスは使わないようにしましょう。これは、delete文やupdate文を使う際にも同じ事が言えます。

insert文の事だけを考えるならば、 インデックスは全く使わなければ最高のパフォーマンスを得られる事になります。しかし、インデックスのないテーブルは、現実的なアプリケーションでは あり得ません。保存したデータは通常参照する事になるでしょうから、クエリの速度を上げるためにインデックスが必要になるでしょう。 書き込み専用のログテーブルですら、プライマリキーや個別のインデックスを持つ事があります。

ただし、巨大なデータをロードする際に一時的に全てのインデックスを削除するのは、非常に良いやり方です。そういった場合には、他のSQL文は 実行されずインデックスは不要なためです。これにより、グラフからも明らかに分かる程の劇的なスピードアップが可能で、実際にデータウェアハウスの 現場ではよく使われる手法です。

考えてみよう

索引構成表クラスタ化インデックスを使った場合、はどのように変わるでしょうか?

insert文が、インデックスの利点を間接的に 受けられる場面は存在するでしょうか? つまり、インデックスがinsert文の実行を速くする事はあるでしょうか?

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy and GDPR | CC-BY-NC-ND 3.0 license