挿入


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

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

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

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

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

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

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

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


注記

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

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

Tweet this tip

ヒント

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

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

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

考えてみよう

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

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

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