by Hayato Matsuura.

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


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

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

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

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

図パフォーマンスの比較

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

重要

スケーラビリティは、パフォーマンスがデータ量のような要素に依存することを示しています。

パフォーマンス値は、スケーラビリティの図におけるある時点の値でしかありません。

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

図データ量に対するスケーラビリティ

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

ヒント

付録, 「Example Schema では、OraclePostgreSQLSQL Serverでこのテストを再現する ためのスクリプトを紹介しています。

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

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

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

DB2

-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  208
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  208
 3 |   IXSCAN SCALE_SLOW | 4456 of 135449700 (   .00%) |  208
Explain Plan
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  296
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  296
 3 |   IXSCAN SCALE_FAST | 4456 of 135449700 (   .00%) |  296

MySQL

+------+------------+---------+-------+------+-----------------------+
| type | key        | key_len | ref   | rows | Extra                 |
+------+------------+---------+-------+------+-----------------------+
| ref  | scale_slow | 6       | const |    1 | Using index condition |
+------+------------+---------+-------+------+-----------------------+
+------+------------+---------+-------------+------+-------+
| type | key        | key_len | ref         | rows | Extra |
+------+------------+---------+-------------+------+-------+
| ref  | scale_fast | 12      | const,const |    1 |       |
+------+------------+---------+-------------+------+-------+

Oracle

------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |  972 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  3000 |  972 |
------------------------------------------------------
------------------------------------------------------
| Id   Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |   13 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_FAST |  3000 |   13 |
------------------------------------------------------

SQL Server

上の実行計画ではscale_slowを使い、 一方で下の実行計画ではscale_fastを使っています。どちらもIndex Seekを使っていることに注意してください。つまり、なぜ一方のクエリが他方よりも遅いのかのヒントは与えてくれません。

STATISTICS PROFILE ONを使うと、違いがはっきりします。

|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:scale_slow),
         SEEK:(scale_data.section=2),
         WHERE:(scale_data.id2=1234) ORDERED FORWARD)
|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:(scale_data.scale_fast),
         SEEK:(scale_data.section=1)
          AND  scale_data.id2=1234) ORDERED FORWARD)

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

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

協力してください

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

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

ヒント

述語情報に注意しましょう。

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

DB2

Explain Plan
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  208
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  208
 3 |   IXSCAN SCALE_SLOW | 4456 of 135449700 (   .00%) |  208

Predicate Information
 3 - START (Q1.SECTION = ?)
      STOP (Q1.SECTION = ?)
      SARG (Q1.ID2 = ?)
Explain Plan
-------------------------------------------------------------
ID | Operation           |                        Rows | Cost
 1 | RETURN              |                             |  296
 2 |  GRPBY (COMPLETE)   |         1 of 4456 (   .02%) |  296
 3 |   IXSCAN SCALE_FAST | 4456 of 135449700 (   .00%) |  296

Predicate Information
 3 - START (Q1.SECTION = ?)
     START (Q1.ID2 = ?)
      STOP (Q1.SECTION = ?)
      STOP (Q1.ID2 = ?)

コスト値にも注意しましょう。2番目のインデックスはより効率がよいですが、 1番目のインデックスの方がコスト値が低いので、両方のインデックスが存在すると オプティマイザは効率の悪い方を選んでしまいます。

MySQL

+------+------------+---------+-------+------+-----------------------+
| type | key        | key_len | ref   | rows | Extra                 |
+------+------------+---------+-------+------+-----------------------+
| ref  | scale_slow | 6       | const |    1 | Using index condition |
+------+------------+---------+-------+------+-----------------------+
+------+------------+---------+-------------+------+-------+
| type | key        | key_len | ref         | rows | Extra |
+------+------------+---------+-------------+------+-------+
| ref  | scale_fast | 12      | const,const |    1 |       |
+------+------------+---------+-------------+------+-------+

Oracle

------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |  972 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  3000 |  972 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=TO_NUMBER(:A))
       filter("ID2"=TO_NUMBER(:B))
------------------------------------------------------
| Id   Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 |   13 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_FAST |  3000 |   13 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=TO_NUMBER(:A) AND "ID2"=TO_NUMBER(:B))

SQL Server

グラフィカルな実行計画の違いを確認するには、マウスをIndex Seekの上に移動し、"Predicate"と "Seek Perdicates"を比べます。

|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:scale_slow),
         SEEK:(scale_data.section=2),
         WHERE:(scale_data.id2=1234) ORDERED FORWARD)
|--Compute Scalar
   |--Stream Aggregate(Count(*))
      |--Index Seek(OBJECT:(scale_data.scale_fast),
         SEEK:(scale_data.section=1)
          AND  scale_data.id2=1234) ORDERED FORWARD)

最初の実行計画のWHERE述語には、 インデックスでフィルターするとマークされています。これはつまり、 スキャンするインデックスの範囲を狭めないということです。2つ目の実行計画では どちらの述語もSEEKの配下にあり、これはSQL Serverにおいては アクセス述語としてはたらきます。

注記

実行計画は、明確化のために単純化されています。アクセス述語とフィルタ述語の見分け方では、Oracleの実行計画の「Predicate Information」セクションを詳細に解説しています。

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

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

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

著者について

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 | CC-BY-NC-ND 3.0 license