データ量がパフォーマンスに与える影響

データベースに保存されたデータの量は、そのパフォーマンスに大きな影響を与えます。データベースに追加のデータが加わることにより あるクエリが遅くなるのは、よくあることです。しかし、データ量が2倍になった時の影響はどの位でしょうか？また、その影響の比率を改善するには どうしたらよいでしょうか？これらは、データベースのスケーラビリティを考える上で重要な問いかけです。

例として、2つの異なるインデックスを使った時の以下のクエリの応答時間を調べてみましょう。インデックスの定義は、今のところは 伏せておきます。これ以降の解説の中で、明らかになるでしょう。

SELECT count(*)
  FROM scale_data
 WHERE section = ?
   AND id2 = ?

SECTION列はこのクエリにおいて、データ量を制御するという 特別な役割があります。SECTIONの数が増えると、選択される行も増えるのです。図3.1では、 SECTIONが小さい場合の応答時間を示しています。

2つのインデックスの種類によって、明らかなパフォーマンスの違いがあるのが分かります。どちらの応答時間も0.1秒以下なので、 多くの場合、遅いクエリの方でも十分な速さと言えるかもしれません。しかし、このパフォーマンスのグラフはたった1つのテストした点を 示しているに過ぎません。スケーラビリティを議論するということは、データ量のような環境パラメータが変化した時の、パフォーマンスへの 影響を見なくてはなりません。

図3.2は、 SECTIONの数、つまりデータ量の増加に応じた応答時間を示したものです。

チャートは、どちらのインデックスでも応答時間が増加することを示しています。図の右側を見ると、データ量が100倍になった時、速い方の クエリが元の2倍かかるようになった一方で、遅いクエリの応答時間は20倍増加し、1秒を超えてしまっています。

SQLクエリの応答時間は、多くの要因に依存します。データ量はその1つです。クエリが、あるテスト環境下では十分に高速でも、本番環境で 同様に十分高速とは言えないこともあります。これは、本番環境のデータのごく一部しか開発環境に存在しない場合は特にそうです。

とは言え、データ量が増えた時にクエリが遅くなってしまこと自体は驚くべきことではありません。しかし、2つのインデックス間のここまでの 際立った違いは、予想していなかったことです。この比率の違いの理由は何なのでしょうか？

両方の実行計画を比較してみれば、理由を見つけるのは簡単なはずです。

実行計画は、違うインデックスを使っていること以外はほとんど同じです。コスト値がスピードの違いに影響してはいますが、その理由は 実行計画からは分かりません。

これはどうやら、「遅い インデックス問題」にぶち当たってしまったようです。インデックスを使っているにもかかわらず、クエリが遅いのです。今となっては、 「壊れたインデックス」の 都市伝説も真実ではないと分かっているのにです。ここで、インデックスの走査を遅くする2つの原因を思い出してみましょう。それは、(1) テーブルへの アクセス、(2) 広い範囲のインデックスのスキャン、でした。

どちらの実行計画もTABLE ACCESS BY INDEX ROWIDの表示はないので、一方の実行計画ではもう一方よりも広い範囲のインデックスをスキャンしているに違いありません。 それでは、インデックスをスキャンする範囲は、実行計画のどこに表示されているのでしょうか？もちろん、述語情報のところにあります！

述語情報は、上の例のように省略して構わない不要な情報では決してありません。述語情報のない実行計画は、不完全なものだと 言えるでしょう。上の例のような実行計画では、パフォーマンスの違いが起きる理由を確かめることはできません。完全な実行計画を見ると、 その違いが分かります。

違いは明らかです。SCALE_SLOWインデックスを使った時は、 SECTIONが唯一のアクセス述語の条件になっています。データ ベースは、セクションの全ての行を読んでから、ID2の フィルタ述語に一致しない行を捨てています。そのため、セクションの行数に よって応答時間が増えているのです。SCALE_FAST インデックスでは、データベースは全ての条件をアクセス述語として使っています。応答時間は、選択される行数によって増えます。

パズルの最後のピースは、インデックスの定義です。実行計画からインデックス定義を再構築できるでしょうか？

SCALE_SLOWインデックスの定義は、SECTION列 から始まらなくてはなりません。でないと、アクセス述語としては使えません。 ID2に対する条件はアクセス述語ではないので、インデックスの 定義においてSECTIONの後にはないでしょう。つまり SCALE_SLOWインデックスは、最低でも3カラムを含み、 SECTIONが最初、ID2は2番目ではないというものに なっているはずです。以下が、このテストで使ったインデックスそのものです。

CREATE INDEX scale_slow ON scale_data (section, id1, id2)

ID1が2番目にあるので、データベースは ID2をアクセス述語として使うことはできません。

一方、SCALE_FASTインデックスの定義では、 SECTIONとID2がアクセス述語に使えるように、 これらが最初の2つでなければなりません。これ以上は順序については 言えることはありません。テストで使ったインデックスは、SECTION から始まり、3番目にID1が追加であるというものです。

CREATE INDEX scale_fast ON scale_data (section, id2, id1)

ID1が最後に追加されただけなので、インデックスのサイズは SCALE_SLOWと同じです。しかし、インデックスのサイズによって 違いが生まれたのではという印象を持ったのではないでしょうか。