データベース性能ことのはじめ

はじめに

データベースの性能を改善しようとした際に、何から手を付ければ良いのか分からないと言う初学者向けの内容になります。
初学者向けではありますが、ほとんどの場合これから解説する内容でチューニング可能です。
これ以上のチューニングとなると、データベースだけではない性能の検討が必要になります。

1. インデックスの適切な設計と活用

データベースのチューニングの手始めとして行うことが多いのが以下の対処になります。
インデックスには検索性能を上げる反面、過剰にインデックスを付けると登録性能が下がるという特徴があるため、バランス良く設定する必要があります。

そもそもインデックスとは

インデックスとは、データをすばやく検索する為の仕組みなのですが、メカニズムを説明してもピンとこないと思うので、一つ例を使って説明しようと思います。

B-Treeインデックスとは

特徴

• 階層構造（ツリー構造）を持つ
  • データが昇順または降順にソートされた状態で管理される。
  • インデックスのルートからリーフノードまでをたどることで、効率的に検索できる。
• 範囲検索やソートに強い
  • BETWEEN や ORDER BY のクエリが高速に処理できる。
• LIKE 'abc%' のような前方一致検索に対応
  • 例：WHERE name LIKE 'John%' のような検索が効率的に行える。

向いている用途

• 一般的な検索（=、<、>、BETWEEN など）
• 範囲検索
• ORDER BY を伴うクエリ
• 前方一致検索 (LIKE 'abc%')

デメリット

• インデックスのツリー構造を維持するため、データの更新（INSERT/UPDATE/DELETE）時のオーバーヘッドが発生する。
• LIKE '%abc%' のような部分一致検索は非効率。

ハッシュインデックスとは

特徴

• ハッシュ関数を使用してキーを変換し、インデックスを作成する
  • = 演算子による完全一致検索が非常に高速。
  • データはハッシュテーブル内のバケットに格納されるため、検索がO(1) の時間で可能（ほぼ一定時間で検索できる）。
• 範囲検索やソートには不向き
  • データが順序を持たないため、<、>、BETWEEN などの範囲検索ができない。
  • ORDER BY も効率的に処理できない。
• 部分一致（LIKE '%abc%'）にも対応できない
  • B-Tree のような順序構造を持たないため、前方一致や部分一致の検索には向かない。

向いている用途

• 完全一致検索（= のみ）が主な用途
• キー・バリュー型のデータ検索
• 非常に高速な検索が求められる場合

デメリット

• 範囲検索 (<, >, BETWEEN) に使えない
• LIKE 'abc%' などの前方一致検索ができない
• ハッシュ衝突が発生する可能性がある
  • 複数の異なる値が同じハッシュ値になると、検索性能が低下することがある。

カバリングインデックスとは

B-Treeインデックスの拡張的な位置づけで、以下の様な特徴をもつ。

特徴

1. テーブルアクセスを回避

通常のインデックスは、検索キーでデータの位置を特定した後に、実際のテーブルを参照する必要がありますが、カバリングインデックスはインデックス自体に必要なデータがすべて含まれているため、テーブルアクセス（I/O 負荷）を省略できます。

2. クエリのパフォーマンス向上

インデックスだけでデータを取得できるため、ディスク I/O の削減によってクエリの実行速度が向上します。特に SELECT 文のパフォーマンス向上 に効果的です。

3. B-Tree インデックスと組み合わせる

カバリングインデックスは B-Tree インデックスを拡張する形で実装 されることが多いです。例えば、通常の B-Tree インデックスは特定のカラムだけを持ちますが、カバリングインデックスは検索キーだけでなく、クエリで必要なすべてのカラムをインデックスに含めます。

4. インデックスサイズが大きくなりがち

必要なカラムをすべて含めるため、通常のインデックスよりもストレージを消費する 可能性があるので、頻繁に使うクエリに対してのみ適用するのが望ましいです。

インデックスの用途まとめ

ここまで解説したインデックスの用途などの特徴と、向き不向きの表になります。

特性	B-Tree インデックス	ハッシュ インデックス	カバリング インデックス
主な用途	一般的な検索（=、<、>、BETWEEN）	完全一致（=）検索	クエリのテーブルアクセス削減
範囲検索 (<, >, BETWEEN)	可能（速い）	不可能	可能（速い）
完全一致 (=) の検索	速い	非常に速い	速い
部分一致検索 (LIKE ‘%abc%’)	非効率	不可能	非効率
データ取得の効率	テーブル参照が必要	テーブル参照が必要	テーブル参照不要 （速い）
ストレージ使用量	小さい	小さい	大きくなりがち

過剰なインデックスの削除

データベースにおいて、インデックスは検索速度を向上させる一方で、データの更新（INSERT / UPDATE / DELETE）処理に負荷をかける要因にもなります。
そのため、不要なインデックスを削除することで、データの書き込み性能を向上させることができます。
これは登録性能を改善する必要がある際の施策です。

過剰なインデックスの例

以下のような表とインデックスを参考に、問題点と対応策を示したいと思います。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department VARCHAR(100),
    salary INT
);

CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);

問題点

1. idx_name, idx_department, idx_salary の3つのインデックスが作成されている
   • これにより、検索は速くなるが、INSERT, UPDATE, DELETE の際にすべてのインデックスを更新する必要がある。
2. department と salary の検索頻度が低い
   • department や salary に対する検索クエリがほとんどない場合、インデックスの維持コストが無駄になる。
3. name を検索するクエリがあるが、department の組み合わせが多い
   • name のインデックスがあれば、department のインデックスは不要かもしれない。

最適化（不要なインデックスを削除）

上記の問題点を、以下のインデックスを削除することで改善します。

DROP INDEX idx_department ON employees;
DROP INDEX idx_salary ON employees;

改善点

• 不要なインデックスを削除し、INSERT, UPDATE, DELETE のパフォーマンスを向上。
• もし department や salary をよく検索する場合、カバリングインデックス を作成するのも一案。

代替案：複合インデックスを使用

もし name と department を組み合わせた検索が多いなら、個別のインデックスを削除し、複合インデックスを作成することで、インデックスの負荷を減らせる。

CREATE INDEX idx_name_department ON employees(name, department);

このような改善をすることで、インデックス数を減らしつつ、検索性能を維持できます。

2. クエリ最適化とSQLチューニング

• EXPLAIN（MySQL）やEXPLAIN ANALYZE（PostgreSQL）を使用してクエリ実行計画を確認し、ボトルネックを特定する。
• SELECT * を避け、必要なカラムのみを指定する。
• JOINの順序やサブクエリの見直しを行い、効率的なクエリを実現する。
• バッチ処理を活用し、大量データの一括処理を最適化する。

3. データの適切なパーティショニングとシャーディング

• パーティショニング（Partitioning）
  • 大規模データを日付やID範囲でテーブル分割し、特定範囲のデータのみを効率的に検索できるようにする。
• シャーディング（Sharding）
  • ユーザーIDなどのキーを基にデータベースを分散し、負荷を分散させる。

4. キャッシュの活用と負荷分散

• アプリケーションレベルのキャッシュ（Memcached, Redis）を導入し、頻繁にアクセスされるデータをキャッシュする。
• データベースのクエリキャッシュ（MySQLのQuery Cache, PostgreSQLのpg_bouncerなど）を活用する。
• リードレプリカ（Read Replica）を活用し、読み取り専用クエリの負荷を分散する。