データベースとパフォーマンス

概要

この記事は、達人に学ぶ DB 設計徹底指南書を読み学習した内容を個人学習用にまとめ直したものです。

この記事では主にデータベースのパフォーマンスを向上させるためのインデックス設計について記述しています。

データベースのパフォーマンスを決める要因

物理設計や非正規化以外に、データベースのパフォーマンス設計においては以下の重要なポイントがある。

インデックス
- (x, a) という形式の配列(x: キー、a: 実データあるいはそれへのポインタ)
- テーブルとは独立して保持されるオブジェクト
統計情報
- DBMS が SQL の最適なアクセスパスを見つけるための経路情報
- 最近では、エンジニアの選択ではなく、DBMS に経路方式を一任するアーキテクチャが主流(コストベース)

インデックス設計

インデックスが SQL のパフォーマンス改善において非常にポピュラーとなっているのは以下のような特徴から。

アプリケーション透過的
- インデックスを使用するかは、DBMS が自動判断するので、インデックス使用時は単純に DB 側にインデックスを作成するのみでよい(アプリケーションコードに影響を与えない)
データ透過的
- インデックスを作成することでテーブルに格納されているデータの中身が影響を受けることはなく、テーブルの構造も変化しない
大きな性能改善効果
- インデックス性能は、データ量に対して線形よりも緩くしか劣化しない

B-Tree インデックスとは？

インデックスにはいくつかの種類があるが、まず覚えるべきは B-Tree インデックスである。

通常、DBMS では何のオプションもつけずにインデックスを作成すると、暗黙に B-Tree インデックスが作成される。

さらに、以下のようにすべての評価基準において、平均以上のパフォーマンスを出せるのが特徴となっている。

B-Tree インデックス構造の簡易図:

【ルートノード（Root）】
※ 探索の入口。キーと子ノードへのポインタのみ
────────────────────────
              [ 50 ]
             /      \
            ▼        ▼

【内部ノード（Internal）】
※ キー + 下位ノードへのポインタ
────────────────────────
      [ 20 | 30 ]       [ 70 | 90 ]
       /   |    \        /    |    \
      ▼    ▼     ▼      ▼     ▼     ▼

【リーフノード（Leaf）】
※ ここが最下層。インデックスとしての「実体」
────────────────────────
 [10]  [25]  [35]   [60]  [80]  [100]
  │     │     │      │     │      │
  ▼     ▼     ▼      ▼     ▼      ▼
【テーブルの行（データ）】
（PK / ROWID / TID など）

均一性
- 各キー値の間で検索速度にバラツキが少ない
- B-Tree が平衡木構造を持つため、どんなキー値を使っても、常にリーフ(データ)までの距離が一定
- テーブルへの挿入、更新、削除の繰り返しによって、場合によっては構造が崩れて非平衡木構造となり、探索にかかる速度にもばらつきが出ることがある
持続性
- データ量の増加に比してパフォーマンス低下が少ない
- B-Tree の木の高さは平均的に 3~4 段程度の「背の低い・平べったい」平衡木構造であり、これはデータ量が増えても変わらず、パフォーマンス低下が少ない
処理汎用性
- 検索、挿入、更新、削除のいずれの処理もそこそこ早い
- 検索と更新処理(挿入、更新、削除)のどちらも同じぐらいの探索速度であるのは、他のインデックスにはない特徴
非等値性
- 等号(=)に限らず、不等号(<, >, <=, >=)を使ってもそこそこ速い
- B-Tree は構築される時に必ずキー値をソートするので、リーフを一つに絞れない場合でも、特定のノードよりも「右」か「左」かを判断できるので不等号の比較が容易
- 否定条件(<>,!=)の場合は、特定のノード以外の全てが該当してしまうので B-Tree による絞り込みは役に立たない
親ソート性
- GROUP BY, ORDER BY, COUNT / MAX / MIN などソートが必要な処理を高速化できる
- B-Tree は構築時にキー値をソートするので、B-Tree インデックスが設定された列を ORDER BY 句のキーとして指定した場合、ソート処理をスキップできる

B-Tree インデックスを「文字列（VARCHAR / TEXT）」カラムに作成した場合、
並び順（ソート順）は DB が採用している「照合順序（COLLATION）」に従う

照合順序は「バイト順」や「辞書順(ロケール依存)」など DB によって様々。

B-Tree インデックスの設計方針

B-Tree インデックスを作成するか否かを決める指針は以下となる。

大規模なテーブルに対して作成する
カーディナリティの高い列に作成する
SQL 文で WHERE 句の選択条件、または結合条件に使用されている列に作成する

B-Tree インデックスとテーブルの規模

データ量が少ない場合は、B-Tree インデックスを使うよりもフルスキャンの方が高速である。

最近のハードウェアを考えるなら、目安としてはレコード数が一万以下であればインデックスの効果はほとんどない。

B-Tree インデックスとカーディナリティ

特定の列の値が、どのくらいの種類の多さを持つか、という概念をカーディナリティと呼ぶ。

例えば、以下の「性別」を表す列は、以下のような値を持つことが考えられるので、カーディナリティは「３」となる。

男性
女性
不詳

B-Tree インデックスを作成する際には、カーディナリティが高い列に対して作成することが望ましい。

具体的には、特定のキーを指定したときに、全体のレコード数の 5%以下程度に絞り込めるだけのカーディナリティがあること。

例えば、「365 日の 1 日を指定する」SELECT 文の場合、0.3%に絞り込めるのでその日付データ列に B-Tree インデックスを作成する意味はあると判断できる。

カーディナリティの注意点

複合列に対してインデックスを作成する場合、カーディナリティは対象の複合列の組み合わせで考える

例:

(a, b, c) という複合列に対してインデックスを作成する。

列それぞれのカーディナリティはそれぞれ、a: 2, b: 10, c: 5 とする。

この場合、個別の列の絞り込み率は 5%より大きくなるが、(a, b, c) の組み合わせで見た場合のカーディナリティが 100 だとすれば、絞り込み率は 1%以下となるので、B-Tree インデックスは有効に機能する。

カーディナリティが高くても、特定の値にデータが集中しているような列は向いてない
- 例えば、1~100 までの値を取る列のカーディナリティは 100 だが、値の 99%が 100 で、1~99 の値は全体の 1%しかとらない場合、100 を指定した SELECT 文は非常に広範囲にヒットしてしまうが、残りの 1~99 の値を指定した場合はピンポイントにヒットするので検索性能が安定しなくなる
- カーディナリティが高い列ほどインデックスの効果が高いが、値が平均的に分散しているのがベスト

B-Tree インデックスと SQL

SQL で検索条件や結合条件として使用されない列にインデックスを作っても無意味であるが、インデックス列を作成した列に対してそのような条件の SQL を記述する場合にはいくつか気をつけるべきポイントがある。

以下で、SomeTable テーブルの col_1 列に対してインデックスを作成した場合の例を示す。

1. インデックス列に演算を行っている

SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100; // インデックス列に対して演算を行っている

インデックスを作成した列は SQL において、「裸」で用いるのが原則であり、演算を行ってはならない
B-Tree インデックスの中で保持されているのは「col_1」の値であり、「col_1 * 1.1」の値ではないため
以下のように式変形を行うことで回避できる

WHERE col_1 > 100 / 1.1;

2. 索引列に対して SQL 関数を適用している

SELECT *
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = 'a';

1 と同じ理由で、インデックスが存在するのは「col_1」の値であり、「SUBSTR(col_1, 1, 1)」の値ではないため

索引関数とは

SUBSTR(col_1, 1, 1);

SUBSTR(文字列, 開始位置, 文字数)
col_1 の 1 文字目から 1 文字分を取り出す(つまり col_1 の先頭 1 文字)

3. IS NULL 述語を使用している

SELECT *
FROM SomeTable
WHERE col_1 IS NULL;

B-Tree インデックスは一般的に NULL についてはデータの値とは見なさず保持しないので、IS NULL または NOT NULL 述語に対しては有効ではない
一部の DBMS では IS NULL を用いた場合にもインデックスが有効に機能するが、汎用性はない

4. 否定形を用いている

SELECT *
FROM SomeTable
WHERE col_1 <> 100;

否定形は、利用したとしても検索範囲が広すぎて役に立たないので、インデックスを利用できない

5. OR を用いている

SELECT *
FROM SomeTable
WHERE col_1 = 99 OR col_2 = 100;

OR を用いた場合はインデックスが利用されない
以下のように IN で書き換えるとインデックスが利用される

SELECT *
FROM SomeTable
WHERE col_1 IN (99, 100);

6. 後方一致、または中間一致の LIKE 述語を用いている

SELECT * FROM SomeTable WHERE col_1 LIKE '%a'; // 後方一致 ❌

SELECT * FROM SomeTable WHERE col_1 LIKE '%a%'; // 中間一致 ❌

SELECT * FROM SomeTable WHERE col_1 LIKE 'a%'; // 前方一致 ⭕️

LIKE 述語は、前方一致検索の場合のみインデックスが使用される

7. 暗黙の型変換を行っている

col_1 が文字列型である例を考える。

SELECT * FROM SomeTable WHERE col_1 = 100; // 数値型で比較 ❌

SELECT * FROM SomeTable WHERE col_1 = '100'; // 文字列型で比較 ⭕️

SELECT * FROM SomeTable WHERE col_1 = CAST(100 AS CHAR(2)); // 文字列型に変換して比較 ⭕️

データ型の異なる列値を SQL において選択条件や結合条件として利用する場合、型変換を行って型を統一する必要があるが、明示的に型変換を行わなくても暗黙的に型変換が行われ、SQL 文自体はエラーとならない
暗黙的な型変換を行う場合、インデックスは使用されなくなる
回避するためには、明示的に条件に使用する値のデータ型を列のデータ型に合わせて変換する必要がある

B-Tree インデックスに関する注意事項

主キーおよび一意制約の列には作成不要
- DBMS は主キー制約や一意制約を作成する際には内部的に B-Tree インデックスを作成しているため、主キー列や一意制約が存在する列に対しては二重にインデックスを作成する必要はない
B-Tree インデックスは更新性能を劣化させる
- インデックスが作成されている対象の列値が変化した場合、インデックス内に保持している値も変更する必要がある
- このため、B-Tree インデックスを作れば作るほど、当該テーブルに対しての更新性能は劣化していくので、極力無駄なインデックスは作成しないようにすることが望ましい
定期的なメンテナンスを行うことが望ましい
- インデックスは、テーブルのデータが更新されていくと、長期的には構造が崩れて性能が劣化していくため、定期的にインデックスの再構築を行うことが望ましい

インデックスの再編成

どの DBMS でも最もシンプルにインデックスを再編成するには一度インデックスを削除してから作り直すことである。

しかし、この方法はインデックス削除後に再作成した時にエラーが発生した場合、インデックスが利用不可となり深刻な性能問題を引き起こす。

そのため、各 DBMS はこの欠点を補うためのインデックス再編成コマンドを持つ。

PostgreSQLの再編成コマンド

REINDEX インデックス名; // オプションを用いて細かい制御が可能

一方、MySQL のみはインデックスの再編成を行うためにテーブルの再編成を行う必要がある。

MySQLの再編成コマンド

OPTIMIZE TABLE テーブル名; // テーブルの再編成コマンドをインデックス再編成時にも用いる

統計情報

統計情報はテーブルやインデックスなどの「メタデータ」であり、DBMS は統計情報を頼りに SQL のアクセスパスを決定する。

その具体的なプロセスには、ユーザーは基本的には関わらないため、SQL のアクセスパスの決定には、ユーザーは統計情報を通してのみ関与することとなる。

SQL の実行計画作成プロセス

DBMS へ SQL を投げると、以下のような流れで処理が行われる。

👤 DBユーザー
    │
    │ SQL
    ▼
┌───────────┐     ┌─────────────────┐     ┌──────────────┐     ┌──────────────┐
│ ① パーサ    │ ──▶│ ② オプティマイザ │ ──▶│   テーブル     │ ──▶│  結果返却     │
└───────────┘     └────────▲────────┘     └──────────────┘     └──────────────┘
                              │
                              │
                              ▼
                    ┌──────────────────────────────┐
                    │     ③ カタログマネージャ        │
                    └──────────────────────────────┘

1. パーサ
- SQL 文を解析して、適切な構文であるかどうかをチェックする
2. オプティマイザ
- SQL のアクセスパス(実行計画)を決定する
- 実行計画を立てる際に統計情報が必要になるので、カタログマネージャへ統計情報を照会する
3. カタログマネージャ
- 統計情報を管理するモジュール

上記から、わかるようにSQL の実行計画は DBMS が「お任せ」で選ぶ。

統計情報の設計指針

前項で説明したように、SQL 実行計画は DBMS が「お任せ」で選ぶため、一般的にエンジニアが SQL の実行計画立案に関与するには統計情報を通した間接的な関わり方となる。

そのため、DB 設計時に考慮するポイントは「統計情報の収集をどのように行うか」というポイントに絞られ、大きく以下の二点を考える。

統計情報収集のタイミング
統計情報収集の対象(範囲)

統計情報収集のタイミング

データが大きく更新された後、なるべく早くが望ましい
レコードの増減だけでなく、データ値の分布や偏りが変わることもアクセスパスに影響するので、「更新」には INSERT/UPDATE/DELETE の全てが含まれる
統計情報収集は非常に重い処理のため、他の処理への影響が少ない夜間帯に実施するのが原則

統計情報収集の対象(範囲)

大きな更新のあったテーブル(およびインデックス)が対象
負荷が高い処理のため、データが変更されていないテーブルを対象にすると、収集にかかる時間が無駄に長くなる

統計情報の凍結について

現状のものから SQL 実行計画を変化させたくない場合、あえて統計情報の凍結を行うケースがある。

すなわち、現在使われている経路が将来に渡ってもデータへの最短ルートであり、現状維持が最適だとわかっている場合である。

このような凍結は、オプティマイザがデータ量の増加によって 100%最適な実行計画を選択することを完全には信じない悲観的な設計と言える。

ちなみに、凍結自体は統計情報の収集をストップするのみなので簡単だが、サービス終了時のデータに近いテストデータを開発時に用意する必要があるなど、実際に実施するには膨大な実作業を要する場合が多い。

参考文献

この記事は以下の情報を参考にして執筆しました。

達人に学ぶ DB 設計徹底指南書