📖

データベースインデックスについて

shimadama

2025/05/08に公開

Rails

tech

インデックスの基本原理と内部構造

B-Treeインデックスの仕組み - データベース検索の効率化メカニズム

多くのリレーショナルデータベース（RDBMS）で標準的に使用されるインデックス構造が「B-Tree（B木）」インデックスです。B-Treeはバランスの取れた木構造（Balanced Tree）であり、データへの高速なアクセス経路を提供します。この構造により、データ量がどれだけ増えても、検索、挿入、削除のパフォーマンスが対数的にしか低下しない（O(log n)）という優れた特性を持ちます。

B-Treeの内部構造

B-Treeの内部構造について、より詳細に説明します：

ノード構造: B-Treeは複数のノードから構成され、各ノードは複数のキーとポインタを持ちます
ページサイズ: InnoDBのデフォルトページサイズは16KBです。これは一つのノードに収まるキーの数に影響します
フィルファクター: 通常、ノードは50-70%程度の充填率で管理され、これにより分割コストを抑えています
再バランシング: データの挿入・削除時に自動的にツリーの高さが均等に保たれます

-- InnoDBページサイズの確認
SHOW VARIABLES LIKE 'innodb_page_size';

-- インデックスの高さと推定行数の確認
SELECT 
  s.index_name,
  s.stat_value AS n_leaf_pages,
  ROUND(LOG(s.stat_value) / LOG(FANOUT)) + 1 AS btree_height
FROM mysql.innodb_index_stats s
WHERE s.database_name = 'your_database'
  AND s.table_name = 'your_table'
  AND s.stat_name = 'n_leaf_pages';

イメージとしては次のような階層構造になっています：

オプティマイザの動作原理

データベースオプティマイザは、インデックスを使用するかどうかを以下の基準で判断します：

インデックス使用の判断基準:

カーディナリティ: インデックスのカーディナリティが高いほど使用される可能性が高い
データ取得割合: 一般的に、テーブルの5-10%以上のデータを読む場合、フルスキャンが選択される
統計情報: ANALYZE TABLEやANALYZEコマンドで更新される統計情報に基づいて判断
コストベース: ディスクI/O、CPU使用率、メモリ使用量などを総合的に評価

-- MySQLでオプティマイザのコスト見積もりを確認
EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE email = 'example@example.com';

-- PostgreSQLでコスト見積もりを確認
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'example@example.com';

MySQLの主要なストレージエンジンであるInnoDBでは、B-Treeを発展させた「B+Tree」インデックスがデフォルトで採用されています。B+Treeでは、すべての実データへのポインタ（またはクラスタ化インデックスの場合は実データそのもの）はリーフノードにのみ格納され、リーフノード同士が双方向リストでリンクされているため、範囲検索が特に効率的です。

B-Tree (B+Tree) インデックスの性能特性:

検索時間複雑性: O(log n) — データ量が10倍になっても、検索時間は約2倍程度にしかなりません
挿入/更新/削除: O(log n) — データの変更も比較的高速
ストレージオーバーヘッド: インデックスサイズはデータサイズの約10-15%（カラム型とカーディナリティに依存）

分かりやすく言えば、B-Treeインデックスは非常に効率的な「多段式の住所録」のようなもので、「都道府県」→「市区町村」→「番地」と絞り込んでいくように、目的のデータへ素早く到達できます。

B-Treeインデックスのメリット:

検索の高速化: 等価条件（=）、範囲条件（>、<、BETWEEN）、前方一致のLIKE（例: LIKE 'prefix%'）での検索が大幅に高速化されます。
ソート操作の効率化: ORDER BY句による並べ替えが、インデックスを利用することで高速になる場合があります（ファイルソートを回避）。
結合操作の高速化: 外部キーや結合条件に使われるカラムにインデックスがあれば、JOIN処理が効率化されます。
ストレージエンジン最適化: InnoDB に最適化されており、クラスタインデックスと連携
自動メンテナンス: データベースが自動的にインデックス統計を更新
データの一意性保証: ユニークインデックスは、カラム（またはカラム群）の値が一意であることを保証します。

B-Treeインデックスのデメリット:

書き込み処理のオーバーヘッド: テーブルへのINSERT、UPDATE、DELETE時には、データだけでなくインデックス構造も更新する必要があるため、インデックスがない場合と比較して処理時間が長くなります。インデックスが多ければ多いほど、このオーバーヘッドは増大します。
ストレージ消費: 各インデックスはディスク容量を消費します。複数のインデックスを作成すると、テーブル本体のサイズを超えるストレージが必要になることもあります。
メンテナンスコスト: インデックスの断片化が進むとパフォーマンスが低下することがあり、定期的なメンテナンス（MySQLのOPTIMIZE TABLEやPostgreSQLのREINDEX、VACUUM FULLなど）が必要になる場合があります。また、統計情報が古くなるとオプティマイザが最適なインデックスを選択できなくなるため、統計情報の更新（MySQLのANALYZE TABLE、PostgreSQLのANALYZEや自動バキュームによる更新）も重要です。
過剰なインデックスのリスク: インデックスが多すぎると、クエリオプティマイザが最適な実行計画を選択する際の複雑性が増し、かえってパフォーマンスを低下させることがあります。また、前述の書き込みオーバーヘッドやストレージ消費も増大します。

詳細な解説: B-Treeの仕組みと限界

B-Tree構造は1970年代に開発された技術ですが、その汎用性と効率性から、今なお多くのデータベースシステムで中心的な役割を担っています。

詳細はUse The Index, Luke: The Tree (B-Treeの構造)をご参照ください。

カーディナリティとセレクティビティ - インデックス選択の重要要素

インデックスをどのカラムに作成すべきか判断する上で、非常に重要な概念が「カーディナリティ（cardinality）」と「セレクティビティ（selectivity）」です。

カーディナリティ: カラム内に存在するユニークな値の数。値の種類が多ければ「カーディナリティが高い」、少なければ「カーディナリティが低い」と言います。
セレクティビティ: 全レコード数に対するユニークな値の比率（カーディナリティ / 総レコード数）、または特定の値がどれだけレコードを絞り込めるかの度合い。セレクティビティが高いほど、そのカラムの値で検索した際に結果が少数に絞り込まれやすいため、インデックスの効果が高まります。

たとえば、100万件のユーザーレコードを持つユーザーテーブルで考えてみましょう：

emailカラム：ほぼすべてのレコードで値が異なるため、カーディナリティはほぼ100万（非常に高い）。
genderカラム：「男性」「女性」「その他」「無回答」など、数種類の値しかないため、カーディナリティは非常に低い（例: 4）。
statusカラム：「アクティブ」「停止中」「未認証」など、これも比較的カーディナリティが低い（例: 3）。

原則として、カーディナリティが高い（つまりセレクティビティが高い）カラムほど、インデックスを作成する価値が高まります。 なぜなら、そのようなカラムで検索すると、インデックスによって調査対象のレコード数を大幅に減らせるからです。

-- MySQLでカーディナリティを確認する (概算値)
SHOW INDEX FROM users;
-- 出力結果の Cardinality カラムに注目

-- PostgreSQLでカーディナリティを確認する (より正確な統計情報から)
SELECT attname AS column_name, n_distinct
FROM pg_stats
WHERE tablename = 'users';
-- n_distinct が正の値ならユニークな値の数、負の値なら総行数に対する割合の絶対値*(-1)

db/migrate/add_basic_indexes.rb

# Railsでの基本的なインデックス追加例
class AddBasicIndexes < ActiveRecord::Migration[7.1]
  def change
    # usersテーブルのemailカラム (高カーディナリティが期待される)
    add_index :users, :email, unique: true # emailは通常ユニークなのでunique制約も付加

    # usersテーブルのstatusカラム (低カーディナリティが予想される)
    # add_index :users, :status # 効果が限定的な場合があるため、慎重に検討
    # もしstatusが頻繁な検索条件で、かつ特定の値が少数派（例: '停止中'ユーザーのみ検索）なら有効な場合もある
  end
end

これは日常生活では「氏名での電話帳検索」と「血液型での電話帳検索」の違いのようなものです。氏名（高カーディナリティ）で探せばほぼ個人を特定できますが、血液型（低カーディナリティ）で探しても多くの人が該当してしまいます。

参考:

MySQL 8.0 Reference Manual: SHOW INDEX Statement

主要なインデックスタイプとその使い方

単一カラムインデックス (Single-Column Index) - 基本となる検索の高速化

最も基本的なインデックスタイプは、単一のカラムに対して作成されるインデックスです。WHERE句の条件で頻繁に使用されるカラムや、カーディナリティが高いカラムに対して作成すると効果的です。

単一カラムインデックスのメリット:

検索性能の向上: WHERE句でそのカラムが使用されるクエリの検索速度が向上します。
実装の容易さ: 作成が最も簡単で、基本的なインデックス戦略の第一歩です。
ソート操作の最適化: ORDER BY句でそのカラムが指定された場合、ソート処理が効率化されることがあります。
外部キーの参照整合性と性能向上: 外部キー制約を持つカラムには、通常インデックスが自動または手動で作成され、関連テーブルの結合や参照整合性のチェックが高速化されます。

単一カラムインデックスのデメリット:

限定的な最適化: 複数のカラムを組み合わせた検索条件（例: WHERE col1 = 'A' AND col2 = 'B'）に対しては、単一カラムインデックスだけでは効果が限定的です。オプティマイザはどちらか一方のインデックスを使うか、あるいは使わない判断をします。
インデックス数の増加リスク: 多くのカラムに個別に単一カラムインデックスを作成すると、インデックスの総数が増え、管理の複雑化や書き込みオーバーヘッドの増大に繋がります。
ストレージオーバーヘッド: 不必要な単一カラムインデックスもストレージを消費します。

db/migrate/add_basic_indexes_to_users.rb

class AddBasicIndexesToUsers < ActiveRecord::Migration[7.1]
  def change
    # email: 高カーディナリティでWHERE句で頻用される (既にunique:trueで作成済みと仮定)
    # add_index :users, :email, unique: true

    # organization_id: 外部キー。Railsのreferences型はデフォルトでインデックスを作成しますが、
    # 明示的に追加する場合や、オプションを指定したい場合。
    # (Rails 5以降では foreign_key: true を指定すると自動でインデックスが作成されます)
    add_index :users, :organization_id unless index_exists?(:users, :organization_id)

    # created_at: 範囲検索やソートでよく使われる。
    add_index :users, :created_at
  end
end

単一カラムインデックスで最適化されるクエリの例：

# emailで検索（効率的）
User.find_by(email: 'example@example.com')

# 外部キーで参照（効率的）
User.where(organization_id: 5)

# 日付範囲での検索（効率的）
User.where(created_at: 1.month.ago..Time.current)

クラスタ化インデックスの詳細

MySQL (InnoDB) では、主キー（通常はid）は自動的にクラスタ化インデックスになります。これは重要な概念なので、詳しく説明します：

InnoDBのクラスタ化インデックス

-- InnoDBでは主キーがクラスタ化インデックスになる
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- これがクラスタ化インデックス
  email VARCHAR(255),
  name VARCHAR(100),
  INDEX idx_email (email)  -- これはセカンダリインデックス
);

クラスタ化インデックスの特徴:

データの物理配置: 行データが主キーの順序で物理的にディスクに格納される
セカンダリインデックスとの関係: セカンダリインデックスのリーフノードには主キーの値が含まれる
ダブルルックアップ: セカンダリインデックスで検索後、主キーで再度検索することがある

-- 実行計画でクラスタ化インデックスの影響を確認
EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';
-- セカンダリインデックス → 主キーでの再検索が発生する可能性

大きな主キーの影響:

-- 悪い例：大きな主キー
CREATE TABLE bad_example (
  uuid VARCHAR(36) PRIMARY KEY,  -- 36バイト
  email VARCHAR(255),
  INDEX idx_email (email)
);

-- 良い例：小さな主キー
CREATE TABLE good_example (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 8バイト
  uuid VARCHAR(36) UNIQUE,
  email VARCHAR(255),
  INDEX idx_email (email)
);

PostgreSQLには、MySQLのInnoDBのような厳密な意味でのクラスタ化インデックスはありません。CLUSTERコマンドで一時的にテーブルを特定のインデックス順に並べ替えることはできますが、その後の挿入・更新で順序は維持されません。主キーも通常のB-Treeインデックスとして作成されます。

参考: Rails Guides: Migrations - Adding Indexes

複合インデックス (Compound/Composite Index) - 複数条件での検索を最適化

複合インデックス（マルチカラムインデックスとも呼ばれます）は、複数のカラムを組み合わせて一つのインデックスを作成します。これにより、複数のカラムにまたがるWHERE句の条件や、複数カラムでのソートを効率化できます。複合インデックスでは、カラムの順序が非常に重要です。

複合インデックスのメリット:

複数条件検索の最適化: 複数のカラムを含むWHERE句の検索が大幅に高速化されます（例: WHERE user_id = ? AND status = ?）。
カバリングインデックスの実現: クエリが必要とするすべてのカラムがインデックスに含まれている場合、テーブル本体へのアクセスが不要になり（インデックススキャンのみで完結）、非常に高速になります。
ストレージ効率の改善: 複数の単一カラムインデックスを作成するよりも、適切に設計された複合インデックス一つの方がストレージ効率が良い場合があります。
ソート操作の効率化: 複数カラムでのORDER BY操作（例: ORDER BY user_id, created_at DESC）がインデックスでカバーできる場合があります。
左端プレフィックスの活用: 複合インデックス (col1, col2, col3) は、col1のみ、またはcol1とcol2のみを使用するクエリにも利用できます。

複合インデックスのデメリット:

インデックスサイズの増大: 複数のカラムを含むため、単一カラムインデックスよりもサイズが大きくなる傾向があります。
更新コストの増加: インデックス対象のカラムが多いほど、データ更新時のオーバーヘッドが大きくなります。
カラム順序の重要性: 最適なカラム順序で設計しないと、効果が薄れたり、意図したクエリで使われなかったりします。
柔軟性の制限: 左端プレフィックスのルールにより、インデックスの途中や末尾のカラムだけを指定した検索では効果を発揮しません（例: (col1, col2, col3)インデックスはWHERE col2 = ?のようなクエリには通常使われません）。

db/migrate/add_compound_indexes_to_orders.rb

class AddCompoundIndexesToOrders < ActiveRecord::Migration[7.1]
  def change
    # 複合インデックスの例
    # user_id, status, created_at の順で複合インデックスを作成
    add_index :orders, [:user_id, :status, :created_at], name: 'idx_orders_on_user_status_created'
  end
end

このインデックス idx_orders_on_user_status_created により、以下のクエリが最適化されます：

# 最適化されるクエリの例
Order.where(user_id: 123, status: 'pending').order(created_at: :desc)
Order.where(user_id: 123, status: 'pending')
Order.where(user_id: 123) # 左端プレフィックスの原則

# 最適化されない、または効果が薄いクエリの例
# Order.where(status: 'pending') # user_idが指定されていないため
# Order.where(created_at: 1.day.ago..Time.current) # user_id, statusが指定されていないため

複合インデックスの重要なルール：左端のプレフィックス (Leftmost Prefix)

データベースオプティマイザは、複合インデックスをその定義の先頭（左端）から順番にしか効率的に利用できません。上記の例 [:user_id, :status, :created_at] では：

WHERE user_id = ? のクエリはインデックスを利用できます。
WHERE user_id = ? AND status = ? のクエリも利用できます。
WHERE user_id = ? AND status = ? AND created_at > ? のクエリも利用できます。
しかし、WHERE status = ? や WHERE created_at > ? のように、左端のカラム（この場合は user_id）を条件に含めないクエリでは、この複合インデックスは通常、効率的に利用されません。

カラム順序の最適化のコツ:

等価比較（= や IN）で使用されるカラムを最初に配置します。これらの条件は最も絞り込み効果が高いためです。
カーディナリティが高いカラムを優先的に前に置きます。より多くのレコードを絞り込めるためです。
範囲条件（>、<、BETWEEN、前方一致LIKE）で使用されるカラムは、等価比較のカラムの後に配置します。範囲条件が使われると、それ以降のインデックスカラムは範囲検索には使えても、更なる絞り込みやソートには使えなくなることがあります。
ソート（ORDER BY）で使用するカラムは、WHERE句の条件カラムの後に含めます。インデックスの順序とソート順（ASC/DESC）が一致していれば、ファイルソートを回避できる可能性があります。

参考: Percona Blog: A Rule of Thumb for Choosing Column Order in Indexes

複合インデックスの使い所:

ユーザーごとのデータ検索（ユーザーID + 作成日など）
状態別のデータフィルタリング（ステータス + 日付範囲など）
複数の属性による検索（カテゴリ + ブランド + 価格範囲など）

MySQL Performance Blog: 複合インデックスの効果的な使い方

ユニークインデックス (Unique Index) - データ整合性とパフォーマンス向上の両立

ユニークインデックスは、指定されたカラム（またはカラム群）の値が一意であることをデータベースレベルで保証します。これによりデータの整合性が保たれるだけでなく、通常のインデックスと同様に検索パフォーマンスも向上させます。

ユニークインデックスのメリット:

データ整合性の保証: 重複データの挿入を防ぎ、データの品質を維持します。
検索性能の向上: 一意性が保証されているため、オプティマイザは最初に一致するレコードを見つけた時点で検索を終了でき、非常に効率的です（constやeq_refアクセスタイプになることが多い）。
レースコンディションの防止: アプリケーションレベルの重複チェック（例: Railsのvalidates_uniqueness_of）だけでは、同時リクエストによる重複挿入（レースコンディション）を防ぎきれない場合があります。データベースレベルのユニークインデックスがあれば、これを確実に防げます。
アプリケーションロジックの簡素化: データベースが一意性を保証するため、アプリケーション側での複雑な重複チェックロジックが不要になる場合があります。
外部キー制約との連携: 参照整合性と組み合わせた高度なデータ制約が可能

ユニークインデックスのデメリット:

挿入性能への影響: データ挿入・更新時に一意性チェックが行われるため、通常のインデックスよりも若干オーバーヘッドが大きくなることがあります。
ロック競合の可能性: 高負荷環境では一意性チェックによるロック競合が発生する可能性があります
NULL値の扱い:
- MySQL: ユニークインデックスにおいて、NULL値は複数許可されます（NULLは特定の値ではないとみなされるため）。
- PostgreSQL: 標準SQLの定義に従い、ユニークインデックスにおいてNULL値は複数許可されます。
- (注意: 過去のDBや一部のDBでは挙動が異なる場合がありましたが、現在の主要なRDBMSでは概ねこの挙動です。)
一部更新の複雑さ: 複合ユニークインデックスの一部を更新する場合、一時的に制約違反が発生しないよう注意が必要です

db/migrate/add_unique_indexes.rb

class AddUniqueIndexes < ActiveRecord::Migration[7.1]
  def change
    # emailカラムにユニークインデックスを追加 (既に作成済みの場合も考慮)
    add_index :users, :email, unique: true unless index_exists?(:users, :email, unique: true)

    # product_id と category_id の組み合わせで一意性を保証する複合ユニークインデックス
    add_index :product_categories, [:product_id, :category_id], unique: true, name: 'idx_uniq_product_categories_on_product_and_category'
  end
end

ユニークインデックスの活用例：

# 新規ユーザー作成（一意性が即座に検証される）
User.create(email: 'new@example.com') # 重複があればエラー

# 関連付けの一意性確保
product.categories << new_category # 重複した関連付けは防止される

Rails Guide: モデル検証とデータベース制約

部分インデックス (Partial Index) - 特定条件のレコードのみを効率的にインデックス化

部分インデックスは、テーブル内の一部の行、つまり特定の条件（WHERE句で指定）を満たす行だけをインデックスの対象とする機能です。これにより、インデックスのサイズを小さく保ち、メンテナンスコストや書き込み時のオーバーヘッドを削減できます。

サポート状況:

PostgreSQL: 全てのバージョンでサポートされています。
MySQL: 8.0.13+で代替的な実装が可能（式インデックスや生成カラムを使用）

部分インデックスのメリット:

インデックスサイズの最適化: 条件に一致するレコードのみをインデックス化するため、インデックスサイズが大幅に小さくなります。
書き込みオーバーヘッドの削減: インデックス対象外のレコードの更新では、部分インデックスの更新は発生しません。
特定クエリの高速化: よく使われるWHERE句の条件を部分インデックスの条件として指定することで、そのクエリをピンポイントで高速化できます。
高セレクティビティの実現: テーブル全体ではカーディナリティが低いカラムでも、部分インデックスの条件で絞り込むことで、インデックス対象内では高セレクティビティとなり効果を発揮する場合があります。
メンテナンスコストの削減: インデックスサイズが小さいため再構築が高速

部分インデックスのデメリット:

クエリの制限: インデックスが使用されるためには、クエリのWHERE句が部分インデックスの条件を包含している（または互換性がある）必要があります。そうでないとインデックスが使用されません
データベース互換性: MySQLでは直接的なサポートがなく、代替実装が必要
メンテナンスの複雑さ: 複数の部分インデックスを管理する場合、どのインデックスがどのクエリに適用されるか追跡が難しくなります
オプティマイザの混乱: 複雑な条件の部分インデックスは、クエリオプティマイザが適切に使用しない場合があります

PostgreSQLでの部分インデックス

db/migrate/add_partial_indexes.rb

class AddPartialIndexes < ActiveRecord::Migration[7.1]
  def change
    # PostgreSQLでの部分インデックス例: 未発送 (shipped_at IS NULL) の注文のみを対象
    if connection.adapter_name == 'PostgreSQL'
      add_index :orders, :customer_id,
        name: 'idx_orders_on_customer_id_for_unshipped',
        where: 'shipped_at IS NULL'
    end
  end
end

MySQL 8.0での代替実装

db/migrate/add_partial_indexes_mysql.rb

class AddPartialIndexesMysql < ActiveRecord::Migration[7.1]
  def change
    if connection.adapter_name.downcase.starts_with?('mysql')
      # 代替策1: 生成カラム + 通常のインデックス
      add_column :users, :is_active, :boolean,
        as: "(status = 'active')",
        stored: true
      add_index :users, :is_active, name: 'idx_users_on_is_active'

      # 代替策2: 式インデックス (MySQL 8.0.13+)
      if Gem::Version.new(connection.database_version) >= Gem::Version.new('8.0.13')
        execute <<~SQL
          CREATE INDEX idx_active_users 
          ON users ((CASE WHEN status = 'active' THEN 1 ELSE NULL END));
        SQL
      end
    end
  end
end

実際のユースケース：

# アクティブなユーザーの中からメールアドレスで検索する
User.where(status: 'active').find_by(email: params[:email])

# 未発送の注文を顧客IDで検索する
Order.where(shipped_at: nil, customer_id: current_user.id)

特に「論理削除 (deleted_at IS NULL) されたレコードを除外する」といったケースで部分インデックスは非常に有効です。

参考:

PostgreSQL Documentation: Partial Indexes

関数インデックス (Functional Index) / 式インデックス (Expression Index) - 関数や式の結果にインデックスを作成

関数インデックス（MySQLの用語）または式インデックス（PostgreSQLの用語）は、カラムの値そのものではなく、カラムに関数や式を適用した結果に対してインデックスを作成する機能です。これにより、WHERE句で関数や式を使用した検索条件を効率化できます。

サポート状況:

PostgreSQL: 全てのバージョンで強力にサポートされています（式インデックス）。
MySQL: バージョン8.0.13からサポートされています（関数インデックス）。

関数/式インデックスのメリット:

関数を使用した条件の最適化: WHERE LOWER(email) = ? や WHERE DATE(created_at) = ? のようなクエリが高速化されます。
大文字小文字を区別しない検索の効率化: LOWER() や UPPER() 関数を使ったインデックスで実現できます。
計算値に基づく検索の最適化: 日付関数や数値計算の結果に基づく検索が効率化
データ型変換や計算結果に基づく検索の最適化: JSON内の特定要素の抽出、日付の一部抽出などが効率化されます。
インデックス使用条件の拡張: 通常のインデックスでは困難な検索パターンへの対応

関数インデックスのデメリット:

WHERE句の完全一致: インデックスが使用されるためには、クエリのWHERE句で使用されている関数/式と、インデックス定義の関数/式が完全に一致する必要があります。
更新時の計算オーバーヘッド: データ更新時には関数/式の計算も行われるため、通常のインデックスより更新コストが高くなることがあります。
バージョン依存性: MySQL 8.0.13より前のバージョンではサポートされていません
オプティマイザの限界: 複雑な関数を使用したインデックスは、オプティマイザが適切に使用できない場合があります
関数変更の影響: データベース関数のバージョンアップなどで動作が変わると、インデックスの効果が変わる可能性があります

db/migrate/add_functional_indexes.rb

class AddFunctionalIndexes < ActiveRecord::Migration[7.1]
  def change
    # PostgreSQLでの式インデックス (emailを小文字にしてインデックス)
    if connection.adapter_name == 'PostgreSQL'
      add_index :users, 'LOWER(email)', name: 'idx_users_on_lower_email_pg'
    end

    # MySQL 8.0.13以降での関数インデックス (emailを小文字にしてインデックス)
    if connection.adapter_name.downcase.starts_with?('mysql') && 
       connection.try(:database_version) >= '8.0.13'
      add_index :users, 'LOWER(email)', name: 'idx_users_on_lower_email_mysql'
    end
  end
end

関数インデックスの代表的なユースケース：

# 大文字小文字を区別しないemail検索
User.where("LOWER(email) = ?", params[:email].downcase)

# 登録日の年だけで検索
Order.where("EXTRACT(YEAR FROM created_at) = ?", 2025)

これらのクエリは、通常のカラムインデックスでは最適化できませんが、対応する関数/式インデックスがあれば効率的に処理できます。

参考:

MySQL 8.0 Reference Manual: Functional Key Parts

PostgreSQL Documentation: Indexes on Expressions

MySQL特有の高度なインデックス戦略

プレフィックスインデックス (Prefix Index) - 長いテキストデータの効率化

MySQLでは、VARCHAR、TEXT、BLOBといった長い文字列やバイナリ型のカラムに対して、カラム値の先頭部分（プレフィックス）だけをインデックス化することができます。これにより、インデックスサイズを削減し、パフォーマンスを維持しつつストレージ効率を高めることができます。

プレフィックスインデックスのメリット:

ストレージ使用量の削減: 長いテキストカラム全体をインデックス化する代わりに、先頭のN文字/バイトのみをインデックス化するため、ディスク容量を大幅に節約できます。
更新性能の向上: インデックスサイズが小さいため、データ更新時のインデックス更新オーバーヘッドも軽減されます。
前方一致検索の最適化: LIKE 'prefix%' のような前方一致検索に有効です。
長いテキストフィールドの検索最適化: 前方一致検索（LIKE 'prefix%'）が高速化
インデックス効率の維持: 適切なプレフィックス長を選ぶことで、完全なカラム値に近い選択性を維持可能
複合インデックスでのスペース効率: 複合インデックス内の長いカラムに対して容量を節約

プレフィックスインデックスのデメリット:

完全な値の比較ができない: プレフィックス部分しかインデックス化されていないため、インデックスだけでは完全な値の一致やソートは行えません。オプティマイザはインデックスで候補を絞り込んだ後、実テーブルのデータで最終確認を行う必要があります。
ユニーク制約の不完全性: プレフィックスインデックスでは、カラム全体のユニーク性は保証できません（プレフィックスが同じで残りの部分が異なる値は許容されます）。
ORDER BY制限: プレフィックスインデックスはORDER BYを使用したソートで最適化効果が限定的です
後方一致・中間一致検索には無効: LIKE '%keyword' や LIKE '%key%word%' には効果がありません。
最適なプレフィックス長の選択が難しい: 短すぎるとセレクティビティが低くインデックス効果が薄れ、長すぎるとストレージ削減効果が薄れます。適切な長さを選択するには、データの分布を分析する必要があります。
PostgreSQLとの互換性: PostgreSQLではサポートされていないため、データベース移行時に注意が必要です。

db/migrate/add_prefix_indexes.rb

class AddPrefixIndexes < ActiveRecord::Migration[7.1]
  def change
    # descriptionカラムの最初の50文字だけをインデックス化
    execute "CREATE INDEX idx_products_on_desc_prefix ON products (description(50))"

    # 複合インデックスで、nameは全体、descriptionは最初の50文字をインデックス化
    execute "CREATE INDEX idx_products_on_name_and_desc_prefix ON products (name, description(50))"
  end
end

プレフィックス長の選択を支援するクエリ（セレクティビティを確認）：

SELECT
  COUNT(DISTINCT LEFT(description, 20)) / COUNT(*) AS selectivity_20,
  COUNT(DISTINCT LEFT(description, 50)) / COUNT(*) AS selectivity_50,
  COUNT(DISTINCT LEFT(description, 100)) / COUNT(*) AS selectivity_100,
  COUNT(DISTINCT description) / COUNT(*) AS selectivity_full
FROM products;

このクエリの結果で、完全なカラムの選択性に近い最小のプレフィックス長を選ぶと良いでしょう。例えば、50文字で0.98、100文字で0.99の選択性なら、50文字のプレフィックスで十分です。

MySQL Performance Blog: 最適なプレフィックス長の選択方法

カバリングインデックス (Covering Index) - テーブルアクセスを回避する効率化技術

カバリングインデックスとは、クエリが必要とする全てのカラム（SELECT句、WHERE句、ORDER BY句、GROUP BY句で参照されるカラム）がインデックス内に含まれている状態を指します。この場合、データベースはテーブル本体のデータブロックにアクセスする必要がなく、インデックスデータだけでクエリを完結させることができます。これにより、ディスクI/Oが大幅に削減され、クエリの実行速度が劇的に向上します。

カバリングインデックスの制限事項

PostgreSQLでのVisibility Mapの影響:

# PostgreSQLでのIndex Only Scanの制限
# Visibility Map (VM) が最新でないとヒープフェッチが発生する
explain_output = Product.select(:id, :name).where(category_id: 1).explain
# 出力に "Heap Fetches: 1" と表示される場合がある

# VACUUMを実行してVisibility Mapを更新
ActiveRecord::Base.connection.execute("VACUUM products")

カバリングインデックスのメリット:

テーブルアクセスの回避 (I/O削減): 最も大きなメリットです。ディスクI/Oはパフォーマンスの主要なボトルネックの一つであり、これを回避できる効果は絶大です。
複数のクエリパターンを網羅: 適切に設計すれば様々なクエリタイプを最適化可能
メモリキャッシュの効率化: インデックスのみで完結するため、キャッシュ効率が向上
クエリ実行時間の大幅削減: テーブルスキャンや追加アクセス不要で、レスポンスタイムが向上

カバリングインデックスのデメリット:

インデックスサイズの肥大化: 多くのカラムをインデックスに含める必要があるため、インデックスサイズが非常に大きくなる可能性があります。
更新コストの増加: 含まれるカラムが多いほど、INSERT/UPDATE時のオーバーヘッドが大きくなります
設計の複雑さ: どのクエリをカバーさせるか、カラムの順序をどうするかなど、設計が難しくなります。特定のクエリに特化しすぎると、他のクエリで使えなくなる可能性もあります。
PostgreSQLでの効果の違い: PostgreSQLではInnoDBのようなクラスタ化インデックスの概念がないため、カバリングインデックスの実装と効果がMySQLとは異なります。
SELECT * の問題: SELECT * のように全カラムを取得するクエリでは、カバリングインデックスの恩恵は受けられません（全カラムを含むインデックスは現実的ではないため）。

db/migrate/add_covering_indexes.rb

class AddCoveringIndexes < ActiveRecord::Migration[7.1]
  def change
    # category_id で絞り込み、name と price を取得し、nameでソートするクエリを想定
    add_index :products, [:category_id, :name, :price], name: 'idx_covering_for_category_name_price'
  end
end

このインデックスにより、以下のようなクエリがテーブルアクセスなしで（または最小限のアクセスで）実行可能になります：

# インデックスだけで完結する可能性が高いクエリ
Product.where(category_id: 5)
       .select(:name, :price)
       .order(:name)

MySQLのEXPLAIN結果で Extra カラムに Using index と表示されれば、カバリングインデックスが使用されていることを意味します。PostgreSQLでは Index Only Scan と表示されます（ただし、Visibility Mapの状態によってはヒープフェッチが発生することもあります）。

# カバリングインデックスの動作を確認
ActiveRecord::Base.connection.execute(
  "EXPLAIN #{Product.select(:name, :price).where(category_id: 5).to_sql}"
).to_a
# => "Using index" が表示されればカバリングインデックスが使用されている

カバリングインデックス活用のポイント:

よく使われるSELECTのカラムをすべてインデックスに含める
検索条件（WHERE句）のカラムを左端に配置
ソート条件（ORDER BY句）のカラムも含める
GROUP BY句のカラムも可能なら含める

MySQL Performance Blog: カバリングインデックスの効果

不可視インデックス (Invisible Index) - 安全なインデックステスト (MySQL 8.0以降)

MySQL 8.0から導入された不可視インデックスは、インデックスをデータベースに作成し維持しつつも、クエリオプティマイザからはデフォルトで使用されないようにする機能です。これにより、新しいインデックスの本番環境への影響（特に書き込み性能への影響や、他のクエリプランへの予期せぬ影響）を、実際に有効化する前に安全に評価できます。

不可視インデックスのメリット:

安全なテスト: 本番環境でインデックスを「オフライン」状態で作成・保持し、そのオーバーヘッド（ストレージ、更新コスト）を評価できます。
段階的なデプロイ: インデックスをまず不可視で作成し、問題がないことを確認した上で可視化（有効化）できます。
インデックス削除の安全性: 既存のインデックスを削除する前に不可視にすることで、もし問題が発生したらすぐに可視に戻す、という安全策が取れます。
クエリプランの制御: 特定のセッションでのみ optimizer_switch を設定して不可視インデックスを使用させ、その効果をテストできます。
メンテナンス作業の簡素化: インデックス再構築時の一時的な代替手段として利用可能。

不可視インデックスのデメリット:

ストレージ消費: 不可視であっても、インデックスはディスク容量を消費し続けます。
更新コスト: 不可視インデックスもデータ更新時には維持されるため、書き込み性能への影響は発生します。
MySQL 8.0以降限定: 古いバージョンではサポートされていません
手動管理の必要性: 不可視インデックスの状態管理は手動で行う必要があります
セッション変数による使用制御: 特定のセッションではSET SESSION optimizer_switch='use_invisible_indexes=on';で強制的に使用可能ですが、これによる予期せぬ振る舞いに注意が必要です

db/migrate/add_invisible_index.rb

class AddInvisibleIndex < ActiveRecord::Migration[7.1]
  def change
    if connection.adapter_name.downcase.starts_with?('mysql') && 
       connection.try(:database_version) >= '8.0'
      # 不可視インデックスの追加 (SQL直書き)
      execute "CREATE INDEX idx_products_on_brand_id_invisible ON products (brand_id) INVISIBLE"
    end
  end
end

テストセッションで不可視インデックスを試すには：

SET SESSION optimizer_switch='use_invisible_indexes=on';
EXPLAIN SELECT * FROM products WHERE brand_id = 123;
-- ここで idx_products_on_brand_id_invisible が使われるか確認
SET SESSION optimizer_switch='use_invisible_indexes=off';

インデックスが有効と判断したら、可視化します：

ALTER TABLE products ALTER INDEX idx_products_on_brand_id_invisible VISIBLE;

逆に問題があれば削除します：

DROP INDEX idx_products_on_brand_id_invisible ON products;

不可視インデックスはデータベースに存在しますが、オプティマイザはそれを使用しません。これにより、インデックス作成の影響（ストレージ使用量やINSERT/UPDATE時のオーバーヘッド）を評価できます。

この機能は特に大規模な本番データベースでのインデックス変更時に役立ちます。インデックスを作成→テスト→問題なければ可視化という安全な流れを実現できます。

MySQL 8.0: 不可視インデックス機能

PostgreSQL特有の高度なインデックス機能

GINインデックス (Generalized Inverted Index) - 複合データ型と全文検索の強力な味方

PostgreSQLのGIN（Generalized Inverted Index）インデックスは、配列、JSONB、テキスト全文検索（tsvector）など、要素が複数含まれる可能性のある複合データ型に対して非常に効率的な検索を提供します。特に「要素を含むか」「特定のパターンに一致するか」といった検索で威力を発揮します。

GINインデックスのメリット:

複合データ型の効率的な検索: 配列型 (tags_array @> ARRAY['ruby'])、JSONB型 (document -> 'tags' ? 'ruby')、全文検索 (text_vector @@ to_tsquery('ruby & rails')) などに最適です。
検索速度の大幅な向上: 通常のB-Treeでは効率的に処理できない「含む」検索 (LIKE '%keyword%' のような中間一致・後方一致も pg_trgm 拡張と組み合わせることで高速化可能) や、配列・JSONB内の要素検索が非常に高速になります。
柔軟なクエリ対応: 多数の専用演算子（@>、<@、?、?|、?&、@@など）をサポートし、多様な検索ニーズに応えます。
複雑なデータ構造の処理: 階層データや複合データに効果的
トライグラム検索との組み合わせ: あいまい検索に非常に効果的

GINインデックスのデメリット:

更新コスト: GINインデックスはB-Treeインデックスと比較して、更新コストが高い傾向にあります。ただし、PostgreSQLのfastupdate機能（デフォルトでオン）により、頻繁な更新に対するパフォーマンスは改善されています。gin_pending_list_limitパラメータで調整可能です。
インデックスサイズ: 特に要素の種類が多い大規模なテキストデータやJSONBデータに対して、GINインデックスはB-Treeよりも大きくなる可能性があります。
構築時間: 大量データに対するGINインデックスの初期構築には時間がかかることがあります。
メモリ消費: クエリ実行時のメモリ使用量が多くなる傾向があります
PostgreSQL限定: MySQL互換性がないため、データベース移行の際に考慮が必要です

db/migrate/add_gin_indexes_postgresql.rb

# PostgreSQL専用
class AddGinIndexesPostgresql < ActiveRecord::Migration[7.1]
  def change
    # PostgreSQLの拡張機能を有効化 (既に有効なら不要)
    enable_extension 'pg_trgm' unless extension_enabled?('pg_trgm') # あいまい検索用
    enable_extension 'btree_gin' unless extension_enabled?('btree_gin') # 一部データ型でGINを使う場合

    # nameカラムにあいまい検索用のGINインデックス (トライグラム)
    add_index :products, :name, using: :gin, opclass: :gin_trgm_ops, name: 'idx_gin_products_on_name_trgm'

    # properties (JSONB型と仮定) カラムにGINインデックス
    add_index :products, :properties, using: :gin, name: 'idx_gin_products_on_properties'

    # tags (配列型 text[] と仮定) カラムにGINインデックス
    # add_index :products, :tags, using: :gin, name: 'idx_gin_products_on_tags' # 配列型の場合
  end
end

これらのインデックスにより、以下のようなPostgreSQLのクエリが高速化されます：

# nameカラムでのあいまい検索 (pg_trgm GINインデックスが使用される)
Product.where("name % ?", "キーワード") # % 演算子はpg_trgmの類似度チェック
Product.where("name ILIKE ?", "%キーワード%") # ILIKEもトライグラムで高速化

# properties (JSONB) 内の特定キーの存在チェック
Product.where("properties ? :key", key: 'color')

# properties (JSONB) 内の特定キーと値のペアを含むか
Product.where("properties @> ?", {color: 'red'}.to_json)

# tags (配列) が特定の要素を含むか
# Product.where("tags @> ARRAY[?]", "ruby")

通常のB-Treeインデックスでは「含む」検索（LIKE '%keyword%'）は最適化できませんが、GINインデックスを使えば効率化できます。このようなワイルドカード検索は一般的なインデックスでは解決できない典型的な性能問題です。

参考:

PostgreSQL Documentation: GIN Indexes

PostgreSQL Documentation: pg_trgm (トライグラム検索でGINインデックスを活用)

BRINインデックス (Block Range Index) - 大規模データの効率的なインデックス

PostgreSQLのBRIN（Block Range Index）インデックスは、物理的にディスク上で近接して格納されているデータ（通常、挿入順や特定のキーでソートされているデータ）に対して非常に効果的なインデックスです。BRINインデックスは、テーブル内の連続したデータブロック群（ブロックレンジ）ごとに、その範囲内の最小値と最大値を記録します。これにより、B-Treeインデックスと比較して非常に小さなサイズで、大規模テーブルに対する範囲検索を効率化できます。

BRINインデックスのメリット:

超小型インデックスサイズ: B-Treeインデックスの数十分の一から数百分の一という非常に小さなサイズで済みます。これは大規模テーブルにおいてストレージとメモリの節約に大きく貢献します。
挿入・更新の高速性: インデックスサイズが小さく構造が単純なため、データの挿入・更新時のオーバーヘッドがB-Treeよりも大幅に低くなります。
大規模な順序データでの範囲検索に最適: created_atのような時系列データや、連続したIDなど、物理的な格納順序と値の順序が強く相関しているカラムの範囲検索（例: WHERE created_at BETWEEN ? AND ?）で特に効果を発揮します。
メモリ効率: サイズが小さいためメモリ消費も少ない
スケーラビリティ: テーブル成長に伴うインデックスサイズの増加が緩やか

BRINインデックスのデメリット:

検索精度の粗さ: ブロックレンジ単位で情報を保持するため、B-Treeインデックスほどピンポイントでデータを特定できません。クエリはまずBRINインデックスで対象ブロックレンジを絞り込み、その後それらのブロック内の行をスキャンして条件に一致するか確認します。データの相関が低いと、多くのブロックをスキャンする必要が出てしまい効果が薄れます。
データ分布への依存性: カラムの値の順序とデータの物理的な格納順序との間に強い相関がない場合（例: ランダムな値を持つカラムや、頻繁なUPDATEでデータが断片化した場合）、BRINインデックスの効果は著しく低下します。
ポイントクエリには不向き: 特定の一つの値を検索する等価検索（=）には、B-Treeほど適していません。
PostgreSQL固有機能: MySQL互換性がない
テーブル整列の必要性: 最大効果を得るにはテーブルのCLUSTERINGが望ましい
pages_per_range の調整: インデックス作成時に pages_per_range ストレージパラメータで1つのエントリがカバーするブロック数を調整できます。この値の最適化が性能に影響します。

db/migrate/add_brin_indexes_postgresql.rb

# PostgreSQL専用
class AddBrinIndexesPostgresql < ActiveRecord::Migration[7.1]
  def change
    # logsテーブルのcreated_atカラム (時系列データ) にBRINインデックスを追加
    # pages_per_range はデフォルト値 (128) を使用。データ特性に応じて調整可能。
    add_index :logs, :created_at, using: :brin, name: 'idx_brin_logs_on_created_at'
  end
end

BRINインデックスが有効なシナリオ：

大規模なログテーブルや履歴テーブル（数千万行～数億行以上）で、主に日付範囲で検索する。
時系列データで、created_atやevent_timestampのようなカラムの値の順序とデータの物理的な挿入順序がほぼ一致している。
テーブルが非常に大きく、B-Treeインデックスのサイズやメンテナンスコストが問題になっている。

利用例：

# 日付範囲での検索（BRINインデックスが効果的）
Log.where(created_at: 1.week.ago..Time.current)

実際のユースケース: あるメッセージングアプリで10億行のログテーブルに対するBRINインデックスを導入したところ、インデックスサイズはB-Treeの65分の1になり（8GB→120MB）、範囲検索のパフォーマンスは同等でした。

参考: PostgreSQL Documentation: BRIN Indexes
参考: Crunchy Data: The Secret Weapon of PostgreSQL: BRIN Indexes

インデックス診断とチューニングの実践手法

インデックス使用状況の監視と分析

作成したインデックスが実際にクエリで使われているか、また、不要なインデックスが存在しないかを確認することは、データベース最適化の重要なステップです。使用されていないインデックスは、ストレージを無駄に消費し、書き込みパフォーマンスを低下させるだけです。

インデックスのライフサイクル管理

-- PostgreSQL: 統計情報の自動更新設定
ALTER TABLE products SET (autovacuum_analyze_scale_factor = 0.05);

-- MySQL: インデックスの断片化確認
SELECT 
  s.table_name,
  s.index_name,
  ROUND(s.stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb,
  ROUND(100 * (1 - s.stat_value / 
    (SELECT stat_value FROM mysql.innodb_index_stats 
     WHERE table_name = s.table_name 
     AND index_name = s.index_name 
     AND stat_name = 'size')), 2) AS fragmentation_pct
FROM mysql.innodb_index_stats s
WHERE s.stat_name = 'n_leaf_pages'
  AND s.database_name = DATABASE();

MySQLでのインデックス使用状況確認 (MySQL 8.0以降、Performance Schemaが必要):

-- 使用されていないインデックスの候補 (Performance Schema利用)
SELECT
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME,
    s.ROWS_READ -- MySQL 8.0以前は sys.schema_unused_indexes を参照
FROM mysql.innodb_index_stats s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage iou
    ON s.TABLE_NAME = iou.OBJECT_NAME AND s.INDEX_NAME = iou.INDEX_NAME
WHERE iou.INDEX_NAME IS NULL AND s.INDEX_NAME != 'PRIMARY' -- PRIMARYキーは除く
ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME;

-- より直接的には、スロークエリログやEXPLAIN結果から判断することが多い
-- または Percona Toolkit の pt-index-usage を利用する

PostgreSQLでのインデックス使用状況確認:
pg_stat_user_indexesビュー（またはpg_stat_all_indexes）のidx_scanカラムを確認します。このカウンタが0または非常に低い場合、そのインデックスはほとんど（または全く）使用されていない可能性があります。

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- 使用回数が0回のインデックス
  AND schemaname NOT IN ('pg_catalog', 'information_schema') -- システムスキーマを除外
ORDER BY pg_relation_size(indexrelid) DESC; -- サイズが大きい順

このクエリはデータベース起動からの累積値なので、長期間稼働しているDBでは注意が必要です。pg_stat_reset()で統計情報をリセットすることもできますが、本番環境では慎重に。

この情報を定期的に確認することで、使用頻度の低いインデックスを特定し、必要に応じて削除を検討できます。不要なインデックスは、INSERTやUPDATEの速度に悪影響を与え、ストレージも消費するため注意が必要です。

インデックス効果の判定と計測

EXPLAINコマンドの正しい解釈法

クエリがインデックスを効果的に使用しているか、またどのように実行されているか（実行計画）を理解するためには、EXPLAIN（MySQL）またはEXPLAIN ANALYZE（PostgreSQL）コマンドが不可欠です。

本番環境での安全な実行計画確認

# 本番環境での安全なEXPLAIN実行
def safe_explain(query, analyze: false)
  return query.explain unless analyze
  
  # ANALYZE は実際にクエリを実行するため注意
  if Rails.env.production?
    raise "EXPLAIN ANALYZE is not allowed in production"
  end
  
  query.explain(analyze: true)
end

MySQL EXPLAINの解釈

MySQLでEXPLAINを実行する方法：

# Railsコンソールでクエリプラン確認
puts User.where(email: 'example@example.com').explain

# より詳細な情報を取得（MySQL 8.0では FORMAT=JSON が推奨されることが多い）
query = User.where(email: 'example@example.com').to_sql
result = ActiveRecord::Base.connection.execute("EXPLAIN FORMAT=JSON #{query}")
puts JSON.pretty_generate(JSON.parse(result.first[0]))

MySQL EXPLAINでの重要なフィールド:

type: アクセス方法。これが最も重要。

良い順: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system, const: ほぼ定数時間。非常に高速。
eq_ref: JOINで主キーまたはユニークインデックスが使われる場合。高速。
ref: JOINでユニークでないインデックスが使われるか、単一テーブルでユニークでないインデックスによる等価検索。良好。
range: インデックスを使った範囲検索（例: BETWEEN, >, <）。比較的良好。
index: インデックス全体をスキャン。Using index (カバリングインデックス) が伴えば良いが、そうでない場合はテーブルサイズに依存。
ALL: フルテーブルスキャン。インデックスが使われていない。大テーブルでは致命的。

possible_keys: 使用可能なインデックスのリスト。
key: 実際に使用されたインデックス。NULLの場合はインデックス不使用。
key_len: 使用されたインデックスのキーの長さ。複合インデックスの一部だけが使われているかなどを判断する材料。
rows: 処理対象として見積もられた行数。小さいほど良い。
filtered: (MySQL 5.1以降) テーブルから読み取られた行のうち、WHERE句の条件で実際に何%が残ったかの見積もり。この値が低い場合、インデックスの選択性が低い可能性があります。
Extra: 追加情報。

Using index: カバリングインデックス。非常に良い。
Using where: インデックスで絞り込んだ後、さらにテーブルデータでフィルタリング。一般的。
Using temporary: 一時テーブルを使用。ORDER BY や GROUP BY がインデックスで処理できない場合に発生。パフォーマンス低下の原因。
Using filesort: メモリまたはディスク上でソート処理。インデックスでソートできない場合に発生。パフォーマンス低下の原因。

良い実行計画の例:

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | index_on_email| index_on_email | 767     | const |    1 | Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

上記は理想的なクエリプラン:

type: ref - インデックスを使用した効率的なルックアップ
rows: 1 - 1行のみを読み取り
Extra: Using index - テーブルアクセスなしでインデックスのみを使用

悪い実行計画の例:

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

上記は問題のあるクエリプラン:

type: ALL - 全テーブルスキャン（最も遅いアクセス方法）
key: NULL - インデックスが使用されていない
rows: 100000 - 大量の行を検査

問題のある実行計画を改善する方法:

type: ALL（全テーブルスキャン）

WHERE句で使用されるカラムにインデックスを追加
複合条件の場合は複合インデックスを検討

Using filesort（ソート）

ORDER BY句のカラムをインデックスに含める（複合インデックスの最後）
インデックスの順序をORDER BYの順序に合わせる

Using temporary（一時テーブル）

GROUP BY句のカラムをインデックスに含める
複雑なJOINの場合は結合条件を見直す

PostgreSQL EXPLAINの解釈

PostgreSQLでEXPLAINを実行する方法：

# Railsコンソールでクエリプラン確認 (基本的な実行計画)
puts User.where(email: 'example@example.com').explain

# 実際の実行情報を含めた詳細な分析 (ANALYZE)
# (注意: ANALYZEは実際にクエリを実行するため、更新系クエリでは使用しない)
puts ActiveRecord::Base.connection.explain("SELECT * FROM users WHERE email = 'example@example.com'", analyze: true)
# Rails 6.1+
# User.where(email: 'example@example.com').explain(analyze: true, buffers: true) のような形式も可

# バッファ使用状況や詳細オプションを含める (SQL直書き)
query = User.where(email: 'example@example.com').to_sql
result = ActiveRecord::Base.connection.execute("EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE, TIMING, SUMMARY) #{query}")
result.each { |row| puts row['QUERY PLAN'] }

PostgreSQL EXPLAIN (ANALYZE) での重要な項目:

ノードタイプ: 実行される操作。

Seq Scan (Sequential Scan): フルテーブルスキャン。🔴 通常は避けるべき（ただし小テーブルや多くの行を返すクエリでは合理的）。
Index Scan: インデックスを使ってテーブルから行を取得。🟢 良い。
Index Only Scan: インデックスに必要なデータが全て含まれており、テーブルアクセスが不要（カバリングインデックスに相当）。🟢 最良。ただし、Visibility Map (VM) が最新でないとヒープフェッチが発生することもある。
Bitmap Heap Scan + Bitmap Index Scan: 複数のインデックス条件を組み合わせたり、インデックススキャンで多数の行がヒットする場合に効率的。🟡
Sort: ソート操作。actual timeが大きい場合は注意。
Hash Join, Merge Join, Nested Loop: JOINの種類。Nested Loopは片方が小さい場合に効率的。

cost=startup_cost..total_cost: オプティマイザによるコスト見積もり。startup_costは最初の行を返すまでのコスト、total_costは全行を返すまでのコスト。小さいほど良い。
rows=planned_rows: 見積もられた行数。
width=avg_row_width: 平均行幅（バイト）。
(actual time=startup_time..total_time rows=actual_rows loops=num_loops): ANALYZEオプション使用時の実際の実行情報。

actual time: 実際の実行時間（ミリ秒）。非常に重要。
rows: 実際に返された行数。planned_rowsと大きく乖離している場合、統計情報が古い可能性がある (ANALYZE table_name; の実行を検討)。
loops: そのノードが実行された回数。

Buffers: shared hit=N read=M written=L: BUFFERSオプション使用時のバッファアクセス情報。

shared hit: PostgreSQLの共有バッファキャッシュから見つかったブロック数。
shared read: ディスクから読み込まれたブロック数。この値が大きいほどディスクI/Oが多い。
temp read/written: 一時ファイルへの読み書きブロック数（ソートやハッシュ処理でメモリが不足した場合）。

Filter: (condition): Seq Scan や Index Scan の後に追加で適用されたフィルタ条件。
Rows Removed by Filter: フィルタによって除外された行数。この値が大きい場合、より効率的なインデックスがある可能性。
Heap Fetches: Index Only Scan時に、Visibility Mapが古いためにテーブルからフェッチされた行数。この値が大きい場合、VACUUMの頻度や設定を見直す必要があるかもしれません。

良い実行計画の例:

Index Only Scan using index_users_on_email on users
  (cost=0.29..8.30 rows=1 width=36) (actual time=0.026..0.027 rows=1 loops=1)
  Index Cond: (email = 'example@example.com'::text)

上記は理想的なクエリプラン:

Index Only Scan - テーブルにアクセスせずインデックスのみを使用
rows=1 - 1行のみを読み取り
actual time=0.026..0.027 - 非常に高速（0.027ミリ秒）

悪い実行計画の例:

Seq Scan on users
  (cost=0.00..458.00 rows=1 width=36) (actual time=10.432..15.173 rows=1 loops=1)
  Filter: (email = 'example@example.com'::text)
  Rows Removed by Filter: 99999

上記は問題のあるクエリプラン:

Seq Scan - 全テーブルスキャン
Rows Removed by Filter: 99999 - フィルタリングに多くの行を検査
actual time=10.432..15.173 - 遅い実行時間（15ミリ秒）

問題のある実行計画を改善する方法:

Seq Scan（シーケンシャルスキャン）

WHERE句で使用されるカラムにインデックスを追加
クエリが多くの行（テーブルの20%以上）を返す場合、オプティマイザがあえてSeq Scanを選択することもある

統計情報の不一致

ANALYZE コマンドを実行して統計情報を更新

インデックススキャン後のソート

インデックスの順序をORDER BYに一致させる

参考:

MySQL 8.0 Reference Manual: Obtaining Execution Plan Information

PostgreSQL Documentation: Using EXPLAIN

効果的なパフォーマンス計測方法

インデックスの最適化は、推測ではなく計測に基づいて行うべきです。ここでは、Ruby/Rails環境でのパフォーマンス計測方法と、一般的な注意点を紹介します。

Ruby/Railsでの計測方法

単純な時間計測 (Benchmark.realtime):
単一のクエリやコードブロックの実行時間を簡単に計測できます。

benchmark_example.rb

require 'benchmark'

# 特定のクエリの実行時間を計測
time_ms = Benchmark.realtime do
  User.where(email: 'example@example.com').first
end * 1000 # ミリ秒に変換
puts "Query took #{time_ms.round(2)} ms"

# 複数の処理を比較
Benchmark.bm(20) do |x| # ラベル幅を20に設定
  x.report("With specific index:") { 100.times { User.where(email: 'example@example.com').first } }
  x.report("Without specific index (simulated):") { 100.times { User.where("id > 0 AND email LIKE ?", '%example@example.com%').first } } # あえて非効率なクエリで比較
end

より精度の高い繰り返し計測 (benchmark-ips gem):
benchmark-ips は、より統計的に信頼性の高いベンチマークを行うためのgemです。一定時間内に処理を繰り返し実行し、1秒あたりの実行回数（iterations per second, ips）を計測・比較します。ウォームアップ機能もあり、JITコンパイルなどの影響を軽減できます。

benchmark_ips_example.rb

# Gemfile に追加: gem 'benchmark-ips'
# bundle install を実行

require 'benchmark/ips'

Benchmark.ips do |x|
  x.config(time: 5, warmup: 2) # 5秒間測定、2秒間ウォームアップ

  x.report("Query with index") do
    User.where(email: 'example@example.com').first
  end

  x.report("Query potentially without index") do
    # 例: LOWER関数を使い、関数インデックスがない場合をシミュレート
    User.where("LOWER(email) = ?", 'example@example.com'.downcase).first
  end

  x.compare! # 各測定結果を比較して表示
end

キャッシュの影響を考慮した計測

# キャッシュを考慮した詳細な計測
def measure_query_performance(query, iterations: 100)
  # キャッシュをクリア
  ActiveRecord::Base.connection.clear_query_cache
  
  # ウォームアップ
  5.times { query.call }
  
  # 実測
  times = iterations.times.map do
    ActiveRecord::Base.uncached do
      Benchmark.realtime { query.call }
    end
  end
  
  {
    min: times.min * 1000,
    max: times.max * 1000,
    avg: (times.sum / times.size) * 1000,
    p95: times.sort[(iterations * 0.95).to_i] * 1000
  }
end

# 使用例
query = -> { User.where(email: 'example@example.com').first }
results = measure_query_performance(query)
puts "Performance: #{results}"

計測における注意点:

データベースキャッシュの影響:
データベースは一度実行したクエリの結果やデータブロックをキャッシュします。2回目以降の実行はキャッシュが効いて非常に高速になることがあるため、純粋なインデックスの効果を測定する際は注意が必要です。

MySQL: RESET QUERY CACHE (クエリキャッシュはMySQL 8.0で廃止。主にバッファプールの影響を考慮)。
PostgreSQL: OSのファイルキャッシュやPostgreSQLの共有バッファの影響を考慮。
Railsレベルのクエリキャッシュ: ActiveRecord::Base.uncached { ... } ブロック内で実行するか、各クエリの前に ActiveRecord::Base.connection.clear_query_cache を呼び出す。

統計的に意味のある測定:

一度だけの計測ではなく、十分な回数（例: benchmark-ipsでは自動）実行し、その平均や分布を見ます。
ウォームアップ期間を設けることで、初回実行時のオーバーヘッド（JITコンパイル、キャッシュのコールドスタートなど）の影響を排除します。

実データに近い環境でのテスト:

開発環境の少量データではなく、本番環境に近いデータ量・データ分布を持つステージング環境などでテストするのが理想です。パフォーマンスの問題は、データ量が大きくなって初めて顕在化することが多いためです。
本番環境で直接テストする場合は、読み取り専用のクエリに限定し、影響を最小限に抑えるか、負荷の低い時間帯を選びます。

パフォーマンスモニタリング

継続的なパフォーマンス監視には、APM (Application Performance Monitoring) ツールやデータベース固有の監視ツールが役立ちます。

Railsアプリケーション向けAPMツール:
これらのツールは、リクエストごとの処理時間の内訳（DBクエリ、Viewレンダリングなど）を可視化し、遅いクエリやN+1問題を特定するのに役立ちます。

Scout APM: 詳細なトランザクショントレース、N+1問題の自動検出、GitHub連携など。
New Relic APM: 幅広い言語とフレームワークに対応。詳細なパフォーマンス分析機能。
Datadog APM: ログ、メトリクス、トレースを統合的に監視。
rack-mini-profiler: 開発環境で手軽に各ページのSQL実行時間や処理時間をブラウザ上に表示。

データベース固有の監視ツール:

MySQL:

スロークエリログ: 設定した実行時間 (long_query_time) を超えたクエリをログに出力。mysqldumpslow コマンドで集計・分析可能。

-- my.cnf (または my.ini) に設定
-- slow_query_log = ON
-- slow_query_log_file = /var/log/mysql/mysql-slow.log
-- long_query_time = 1.0 -- 1秒以上のクエリを記録
-- log_queries_not_using_indexes = ON -- インデックス未使用クエリも記録 (オプション)

Performance Schema: MySQL 5.5以降で導入された詳細なパフォーマンス情報収集機能。sys スキーマを通じて分かりやすくアクセス可能。

-- Performance Schema を利用した実行回数の多いクエリ (MySQL 8.0)
SELECT digest_text, count_star, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Percona Toolkit: pt-query-digest はスロークエリログなどを分析する強力なツール。

PostgreSQL:

スロークエリログ: log_min_duration_statement パラメータで設定した実行時間を超えたクエリをログに出力。

-- postgresql.conf に設定 (または ALTER SYSTEM SET)
-- log_min_duration_statement = 1000  -- 1000ミリ秒 (1秒) 以上のクエリを記録
-- log_statement = 'none' -- or 'ddl', 'mod', 'all'
-- log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

pg_stat_statements モジュール: postgresql.conf の shared_preload_libraries に追加し、CREATE EXTENSION pg_stat_statements; を実行。実行回数、総実行時間、平均実行時間などの統計情報をSQLで確認可能。
```
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
```
auto_explain モジュール: 遅いクエリの実行計画を自動的にログに出力。
pgBadger: PostgreSQLのログファイルを詳細に分析し、HTMLレポートを生成するツール。

これらのツールや機能を活用し、最適化の効果を定量的に把握し、継続的な改善につなげましょう。

参考資料

インデックス設計の実践例

ここでは、ECサイトの商品テーブル (products) を例に、MySQLとPostgreSQLそれぞれでのインデックス設計のサンプルを示します。

ECサイト向けの商品テーブルとインデックス設計：MySQL 8

db/migrate/create_products_table_mysql.rb

class CreateProductsTableMysql < ActiveRecord::Migration[7.1]
  def change
    create_table :products do |t|
      t.string :name, null: false
      t.text :description
      t.decimal :price, precision: 10, scale: 2, null: false
      t.references :category, foreign_key: true, index: true # カテゴリID
      t.references :brand, foreign_key: true, index: true    # ブランドID
      t.integer :stock_count, default: 0
      t.boolean :is_published, default: false, null: false
      t.datetime :published_at
      t.json :properties # 色、サイズなどの追加情報 (MySQL 5.7.8+)
      t.timestamps
    end

    # === 基本的な検索用インデックス ===
    add_index :products, :name # 商品名検索 (前方一致LIKEなど)
    add_index :products, :price # 価格でのソートや範囲検索
    add_index :products, :is_published # 公開状態での絞り込み

    # === 複合条件での検索用インデックス ===
    # カテゴリ別 & 公開状態で絞り込み、価格でソート
    add_index :products, [:category_id, :is_published, :price], name: 'idx_products_on_cat_pub_price'
    # ブランド別 & 公開状態で絞り込み、新着順 (created_at)
    add_index :products, [:brand_id, :is_published, :created_at], name: 'idx_products_on_brand_pub_created'

    # === カバリングインデックスの例 ===
    # 公開されている商品をカテゴリで絞り込み、商品名、価格、在庫数をリスト表示するケースを想定
    # category_id で絞り込み、nameでソートする場合
    add_index :products, [:category_id, :is_published, :name, :price, :stock_count], name: 'idx_covering_cat_pub_name_price_stock'

    # === 全文検索用 (InnoDBはMySQL 5.6+, MyISAMは以前から) ===
    # execute "CREATE FULLTEXT INDEX ft_idx_products_on_name_desc ON products(name, description)"

    # === 部分インデックス (MySQL 8.0+) ===
    # 高額商品 (price > 10000) かつ公開中のもののみ
    # execute "CREATE INDEX idx_partial_premium_published_products ON products (price) WHERE price > 10000 AND is_published = TRUE"
    # Rails 7.0+ for MySQL 8.0.13+
    add_index :products, :price, name: 'idx_partial_premium_published_products', where: 'price > 10000 AND is_published = TRUE'

    # === 関数インデックス (MySQL 8.0.13+) ===
    # JSONプロパティ内の特定の値 (例: 'color') で検索する場合
    # execute "CREATE INDEX idx_func_products_properties_color ON products ((JSON_UNQUOTE(JSON_EXTRACT(properties, '$.color'))))"
    # Rails 7.0+
    add_index :products, "(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.color')))", name: 'idx_func_products_properties_color'

    # === プレフィックスインデックス ===
    # description の先頭100文字
    add_index :products, :description, length: 100, name: 'idx_prefix_products_description_100'
  end
end

ECサイト向けの商品テーブルとインデックス設計：PostgreSQL

db/migrate/create_products_table_postgresql.rb

class CreateProductsTablePostgresql < ActiveRecord::Migration[7.1]
  def up
    # PostgreSQLの拡張機能を有効化 (既に有効なら不要)
    enable_extension 'pg_trgm' unless extension_enabled?('pg_trgm')

    create_table :products do |t|
      t.string :name, null: false
      t.text :description
      t.decimal :price, precision: 10, scale: 2, null: false
      t.references :category, foreign_key: true, index: true
      t.references :brand, foreign_key: true, index: true
      t.integer :stock_count, default: 0
      t.boolean :is_published, default: false, null: false
      t.datetime :published_at
      t.jsonb :properties # PostgreSQLではJSONB型が推奨される
      t.string :tags, array: true, default: [] # タグを配列で保持
      t.timestamps
    end

    # === 基本的な検索用インデックス (B-Tree) ===
    add_index :products, :name # (必要なら text_pattern_ops も検討)
    add_index :products, :price
    add_index :products, :is_published

    # === 複合条件での検索用インデックス (B-Tree) ===
    add_index :products, [:category_id, :is_published, :price], name: 'idx_products_on_cat_pub_price_pg'
    add_index :products, [:brand_id, :is_published, :created_at], name: 'idx_products_on_brand_pub_created_pg'

    # === カバリングインデックス (Index Only Scan を狙う) ===
    # INCLUDE句を使ったより明示的なカバリングインデックス (PostgreSQL 11+)
    # execute "CREATE INDEX idx_covering_cat_pub_name_price_stock_pg ON products (category_id, is_published, name) INCLUDE (price, stock_count)"
    # Railsでは add_index で INCLUDE を直接指定できないため SQL 直書き
    # シンプルな複合インデックスでも Index Only Scan は発生しうる
    add_index :products, [:category_id, :is_published, :name, :price, :stock_count], name: 'idx_covering_cat_pub_name_price_stock_pg'

    # === 全文検索用 (GIN or GiST) ===
    # to_tsvector を使った全文検索インデックス
    # execute "ALTER TABLE products ADD COLUMN tsv tsvector"
    # execute "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON products FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(tsv, 'pg_catalog.japanese', name, description)" # 日本語の場合
    # add_index :products, :tsv, using: :gin, name: 'idx_gin_products_tsv'

    # === あいまい検索用のトライグラムインデックス (GIN or GiST) ===
    add_index :products, :name, using: :gin, opclass: :gin_trgm_ops, name: 'idx_gin_products_name_trgm'
    add_index :products, :description, using: :gin, opclass: :gin_trgm_ops, name: 'idx_gin_products_desc_trgm'

    # === JSONBプロパティへのインデックス (GIN) ===
    add_index :products, :properties, using: :gin, name: 'idx_gin_products_properties' # 全体
    # 特定のキー (例: properties->'color') への式インデックス
    add_index :products, "((properties->>'color'))", name: 'idx_expr_products_properties_color'
    # 特定のキーが特定の値を持つレコードを対象とした部分GINインデックス
    # add_index :products, :properties, using: :gin, name: 'idx_gin_partial_products_prop_specific', where: "(properties->'status' = 'available')"

    # === 配列型(tags)へのインデックス (GIN) ===
    add_index :products, :tags, using: :gin, name: 'idx_gin_products_tags'

    # === 部分インデックス (B-Tree) ===
    add_index :products, :price, name: 'idx_partial_premium_published_products_pg', where: 'price > 10000 AND is_published = TRUE'

    # === 式インデックス (B-Tree) ===
    # name を小文字にしてインデックス
    add_index :products, 'LOWER(name)', name: 'idx_expr_products_lower_name'

    # === BRINインデックス (大規模な順序データ向け) ===
    # 例えば、大量のログのようなテーブルで created_at に適用
    # add_index :products, :created_at, using: :brin, name: 'idx_brin_products_created_at' # このECサイトの商品テーブルには不向きかもしれない
  end

  def down
    # drop_table など、インデックス削除も記述
  end
end

インデックス設計によるパフォーマンス改善のイメージ:

適切なインデックス設計は、特にECサイトのような検索多用アプリケーションにおいて、ユーザー体験と売上に直結する重要な要素です。

参考: Rails Guides: Active Record Query Interface

ソート操作 (`ORDER BY`) の最適化

ORDER BY句によるソートも、インデックスを適切に設計することで大幅に高速化できます。データベースがインデックス順にデータを読み取れる場合、追加のソート処理（ファイルソート）を回避できるためです。

db/migrate/add_sort_indexes.rb

class AddSortIndexes < ActiveRecord::Migration[7.1]
  def change
    # created_at でのソートを高速化 (昇順・降順ともに利用可能)
    add_index :products, :created_at, name: 'idx_products_on_created_at_for_sort'

    # 複合インデックスで、WHERE句の条件とORDER BY句の条件を組み合わせる
    # category_id で絞り込み、price で昇順ソートする場合
    add_index :products, [:category_id, :price], name: 'idx_products_on_category_price_for_sort'
    # MySQLではインデックスのソート順を指定できないが、オプティマイザが双方向にスキャン可能
    # PostgreSQLではインデックス作成時に ASC/DESC を指定可能
    # add_index :products, [:category_id, :price], order: { price: :desc }, name: 'idx_pg_cat_price_desc' # PGのみ
  end
end

これにより、以下のようなクエリが最適化されます：

# created_at順のソート (最新順)
Product.order(created_at: :desc)

# カテゴリ内で価格が安い順にソート
Product.where(category_id: 5).order(price: :asc)

ソート最適化のポイント:

インデックススキャンとの両立: WHERE句の条件でインデックスが使われ、かつそのインデックスの順序がORDER BY句の順序と一致（または逆順）する場合に最も効率的です。
複合インデックスの順序: 複合インデックス (colA, colB) は ORDER BY colA, colB や ORDER BY colA を最適化できます。ORDER BY colB だけでは通常最適化されません（左端プレフィックスの原則）。
ソート方向 (ASC/DESC):

MySQL: インデックスは昇順で格納されますが、オプティマイザは前方スキャン（ASC）と後方スキャン（DESC）の両方を行えるため、ASCでもDESCでもインデックスを利用できます。
PostgreSQL: インデックス作成時にカラムごとに ASC / DESC / NULLS FIRST / NULLS LAST を指定でき、クエリのソート順と完全に一致する場合に最も効率的です。

EXPLAINでの確認: Extra に Using filesort (MySQL) や、EXPLAIN ANALYZE で Sort ノードの actual time が大きい (PostgreSQL) 場合は、インデックスによるソート最適化が効いていない可能性があります。

参考:

MySQL 8.0 Reference Manual: ORDER BY Optimization

PostgreSQL Documentation: Indexes and ORDER BY

ページネーションの効率化：キーセットページネーション (Seek Method)

多くのアプリケーションで使われる OFFSET と LIMIT を組み合わせたページネーション（例: User.limit(20).offset(10000)）は、オフセット値が大きくなる（深いページに進む）ほどパフォーマンスが著しく低下します。これは、データベースがオフセット分の行を実際にスキップする必要があるためです。

この問題の解決策として、「キーセットページネーション」（シークメソッド、カーソルベースページネーションとも呼ばれます）があります。これは、前回のページの最後の行の特定のキー（通常はソート順に使われるユニークなカラム、またはカラムの組み合わせ）を基準に、次のページのデータを取得する方法です。

キーセットページネーションのメリット:

一貫した高速レスポンス: ページ番号に関わらず、常に一定の高速なレスポンスが期待できます。
大規模データセットへの適応性: 何百万行、何億行のデータセットでも効率的に動作します。
データ変動への耐性: ページをめくっている間に新しいデータが挿入/削除されても、ページ内容の重複や欠落が起きにくいです（OFFSET方式では発生しやすい）。
リソース効率: DBサーバーの負荷とメモリ使用量を抑える
並行スクロール対応: 異なるユーザーが同時に別々の位置をスクロールしても効率的

キーセットページネーションのデメリット:

特定のページへの直接ジャンプが困難: 「5ページ目に飛ぶ」といった操作は直接的にはできません。「次へ」「前へ」のナビゲーションが基本です。
実装の複雑性: OFFSET方式よりロジックがやや複雑になります。特に複数のソート順や動的なソートに対応する場合は、キーの管理が煩雑になることがあります。
UIの制約: ページ番号ベースのUIとは相性が悪く、無限スクロールや「もっと読み込む」形式のUIに適しています。
ソート順変更の複雑さ: ソート順が変更可能なUIでは、ソート基準ごとに異なるキー項目が必要になり実装が複雑化します。

app/models/product.rb

class Product < ApplicationRecord
  # キーセットページネーションの例 (IDで昇順ソートされている場合)
  def self.paginate_after_id(last_id: nil, limit: 20)
    scope = order(id: :asc).limit(limit)
    scope = scope.where('id > ?', last_id) if last_id
    scope
  end

  # キーセットページネーションの例 (created_atで降順、IDで昇順ソートの場合 - Tie Breaker)
  # last_created_at と last_id の両方が必要
  def self.paginate_by_creation_desc(last_created_at: nil, last_id: nil, limit: 20)
    scope = order(created_at: :desc, id: :desc).limit(limit) # ソート順を明確に
    if last_created_at && last_id
      # (created_at < ?) OR (created_at = ? AND id < ?) の形
      scope = scope.where(
        '(created_at < :last_created_at) OR (created_at = :last_created_at AND id < :last_id)',
        last_created_at: last_created_at,
        last_id: last_id
      )
    end
    scope
  end
end

コントローラでの使用例 (paginate_by_creation_desc):

app/controllers/products_controller.rb

def index
  @limit = 20
  @products = Product.paginate_by_creation_desc(
    last_created_at: params[:last_created_at],
    last_id: params[:last_id],
    limit: @limit
  )

  if @products.size == @limit
    last_product = @products.last
    @next_page_params = { last_created_at: last_product.created_at.iso8601(6), last_id: last_product.id }
  end
end

ビューでの実装：

app/views/products/index.html.erb

<%# 商品リスト表示 %>
<% @products.each do |product| %>
  <p><%= product.name %> - <%= product.created_at %></p>
<% end %>

<%# 次のページへのリンク %>
<% if @next_page_params %>
  <%= link_to "Next Page", products_path(@next_page_params) %>
<% end %>

このためには、(created_at, id) のような複合インデックスが効果的です。

参考: Use The Index, Luke!: Paging Through Results (OFFSET is a SCAM)

パフォーマンス回帰を防ぐベンチマーク

インデックスの変更やコードの修正がパフォーマンスに与える影響を定量的に測定し、意図しない性能低下（回帰）を防ぐために、ベンチマークをCI/CDプロセスに組み込むことも有効です。

lib/tasks/performance_benchmark.rake

require 'benchmark/ips'

namespace :benchmark do
  desc "Benchmark critical product queries"
  task product_queries: :environment do
    # テストデータの準備 (十分な量を推奨)
    unless Product.count > 10000
      puts "Seeding benchmark data for Products..."
      # (ここに大量データ生成ロジックを記述。例: 5万件の商品データ)
      # FactoryBot.create_list(:product, 50000) など
      puts "Benchmark data seeded."
    end

    category_id_sample = Product.pluck(:category_id).sample || 1
    last_product_sample = Product.order(created_at: :desc, id: :desc).first

    Benchmark.ips do |x|
      x.config(time: 10, warmup: 3) # 時間を長めに取り、安定した結果を得る

      x.report("Filter by category, published, order by price") do
        Product.where(category_id: category_id_sample, is_published: true).order(price: :asc).limit(20).load
      end

      x.report("Keyset pagination (creation_desc)") do
        Product.paginate_by_creation_desc(
          last_created_at: last_product_sample&.created_at,
          last_id: last_product_sample&.id,
          limit: 20
        ).load
      end

      # 他の重要なクエリもここに追加
      x.compare!
    end
  end
end

実行方法: bundle exec rails benchmark:product_queries

このようなベンチマークを定期的に実行し、結果を記録・比較することで、パフォーマンスのトレンドを把握し、問題の早期発見に繋げられます。

参考: GitHub - evanphx/benchmark-ips: Provides iteration per second benchmarking for Ruby

インデックス活用方法

理論を理解し、ツールを使えるようになったら、次は実際のアプリケーションでどのようにインデックス戦略を立て、活用していくかです。

主要なアクセスパターンに基づくインデックス設計

最も効果的なインデックス設計は、アプリケーションが実際にどのようにデータにアクセスしているか（アクセスパターン）を理解することから始まります。

スロークエリログの分析:
前述の通り、MySQLのslow_query_logやPostgreSQLのlog_min_duration_statementで記録された遅いクエリを定期的にレビューします。pt-query-digest (Percona Toolkit) や pgBadger といったツールを使うと、これらのログを効率的に集計・分析できます。実行頻度が高く、かつ実行時間が長いクエリが最適化の主要なターゲットです。
APMツールの活用:
New Relic, Scout APM, DatadogなどのAPMツールは、どのリクエスト（コントローラのアクション）が遅く、その中でどのDBクエリが時間を消費しているかを特定するのに役立ちます。
ビジネス要件の理解:
アプリケーションのコア機能や、ユーザーが最も頻繁に利用する検索・フィルタリング機能を特定します。これらの機能に関連するクエリは、パフォーマンス要件が厳しいことが多いです。
EXPLAINによるボトルネック特定:
特定された遅いクエリに対してEXPLAINを実行し、フルテーブルスキャン、不適切なJOIN、ファイルソートなど、パフォーマンス上のボトルネックを具体的に特定します。

これらの分析に基づいて、最もインパクトの大きい箇所からインデックスの追加・修正を検討します。

lib/tasks/db_analysis.rake

# Rakeタスク例: pt-query-digest や pgBadger のような専用ツールの方が高機能ですが、
# Railsから簡易的にスロークエリログを処理するイメージ
namespace :db do
  desc "Analyze slow queries from a given log file (conceptual)"
  task :analyze_slow_queries, [:log_file] => :environment do |t, args|
    log_file_path = args[:log_file]
    unless log_file_path && File.exist?(log_file_path)
      puts "Usage: rails db:analyze_slow_queries[<path_to_log_file>]"
      next
    end

    puts "Analyzing slow queries from: #{log_file_path}..."
    # ここにログファイルをパースし、頻度や実行時間で集計するロジックを記述
    # (例: 正規表現でクエリパターン、実行時間、検査行数などを抽出)
    #
    # 実際の分析には pt-query-digest (MySQL) や pgBadger (PostgreSQL) の利用を強く推奨します。
    # 例: pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt
    # 例: pgbadger -f /var/log/postgresql/postgresql-Mon.log -o slow_query_report.html

    puts "Analysis complete (conceptual). Use dedicated tools for actual analysis."
  end
end

参考:

Percona Toolkit: pt-query-digest

pgBadger - PostgreSQL Log Analyzer

データ容量に応じたインデックス戦略

テーブルのデータ量や特性に応じて、最適なインデックス戦略は変わります。

小規模データ（～数万レコード）:
- 基本的なB-Treeインデックス（主キー、外部キー、頻繁な検索条件のカラム）で十分な場合が多いです。
- フルスキャンでも許容範囲の速度であることがあり、インデックスのオーバーヘッド（書き込み、ストレージ）を考慮すると、過剰なインデックスは避けるべきです。
中規模データ（数十万～数百万レコード）:
- 複合インデックス、カバリングインデックスの活用が重要になります。
- EXPLAINを詳細に確認し、不要なファイルソートや一時テーブルの使用を避ける設計を心がけます。
- カーディナリティの低いカラムへのインデックスは慎重に検討します。
大規模データ（数百万～数億レコード以上）:
- 部分インデックス: 特定の条件で絞り込まれる頻繁なクエリに対して、インデックス対象を限定することでサイズと効率を両立します。
- PostgreSQLのBRINインデックス: 巨大な時系列データなど、物理的順序と値の相関が高い場合に劇的な効果を発揮します。
- MySQLのプレフィックスインデックス: 長大なテキストカラムの検索で、ストレージと性能のバランスを取ります。
- データパーティショニング: テーブル自体を分割することで、クエリ対象のデータ量を物理的に減らすアプローチも検討されます（インデックス戦略と合わせて考慮）。
- 読み取り専用レプリカへの負荷分散や、より専門的なデータベースソリューション（データウェアハウスなど）の検討も視野に入ることがあります。

以下は、大規模なログテーブルを想定したインデックス戦略のマイグレーション例です。

大規模ログテーブルのインデックス戦略：MySQL 8

db/migrate/optimize_large_log_table_mysql.rb

class OptimizeLargeLogTableMysql < ActiveRecord::Migration[7.1]
  def change
    # logsテーブル (user_id, event_type, message (TEXT), created_at, client_ipなど) を想定

    # 1. 頻繁な検索パターン: 特定 user_id の直近のログを event_type で絞り込む
    add_index :logs, [:user_id, :created_at, :event_type], name: 'idx_logs_on_user_created_event'
    # created_at を範囲検索で使うため、event_type の前に置く方が良い場合も。クエリによる。
    # add_index :logs, [:user_id, :event_type, :created_at], name: 'idx_logs_on_user_event_created'

    # 2. 部分インデックス: エラーログ (event_type = 'error') のみを対象に、created_at で検索
    # Rails 7.0+ for MySQL 8.0.13+
    add_index :logs, :created_at, name: 'idx_partial_error_logs_on_created', where: "event_type = 'error'"

    # 3. プレフィックスインデックス: message (TEXT型) の全文検索は重いので、
    #    先頭部分でのキーワード検索を補助 (MySQLのFULLTEXT INDEXも検討)
    add_index :logs, :message, length: 100, name: 'idx_prefix_logs_on_message_100'

    # 4. IPアドレス検索: client_ip (VARCHAR(45) IPv6対応)
    add_index :logs, :client_ip, name: 'idx_logs_on_client_ip'

    # 注意: 既存の巨大テーブルにインデックスを追加する場合、長時間テーブルロックが発生する可能性があります。
    # MySQL 5.6+ のオンラインDDLや、pt-online-schema-change (Percona Toolkit) のようなツールを検討。
    # Rails 6.0+ では add_index に algorithm: :concurrently オプションがMySQLでもある程度サポート(ALGORITHM=INPLACE, LOCK=NONEを試みる)
    # add_index :logs, :column_name, algorithm: :concurrently # (利用可能か確認)
  end

  private

  # Railsマイグレーション内で本番環境でのみ、かつ大規模テーブルにのみ適用するガード例
  def safe_to_apply_large_table_migration?(table_name, row_threshold = 1_000_000)
    return false unless Rails.env.production? # 本番以外では常に実行しないなど

    count = 0
    begin
      # INFORMATION_SCHEMA.TABLES の TABLE_ROWS はInnoDBでは概算値
      # より正確には SELECT COUNT(*)だが、巨大テーブルには実行できない
      # ここでは簡易的なチェック
      count = ActiveRecord::Base.connection.select_value(
        "SELECT table_rows FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = '#{table_name}'"
      ).to_i
    rescue ActiveRecord::StatementInvalid => e
      puts "Warning: Could not get row count for #{table_name}: #{e.message}"
      return false # 不明な場合は安全側に倒す
    end
    count > row_threshold
  end
end

大規模ログテーブルのインデックス戦略：PostgreSQL

db/migrate/optimize_large_log_table_postgresql.rb

class OptimizeLargeLogTablePostgresql < ActiveRecord::Migration[7.1]
  disable_ddl_transaction! # CONCURRENTLY を使う場合はトランザクション外で実行

  def change
    # logsテーブル (user_id, event_type, message (TEXT), payload (JSONB), created_at, client_ipなど) を想定

    # 1. 頻繁な検索パターン: 特定 user_id の直近のログを event_type で絞り込む
    # (CONCURRENTLY はRails 5.0+でサポート)
    add_index :logs, [:user_id, :created_at, :event_type], name: 'idx_logs_on_user_created_event_pg', algorithm: :concurrently

    # 2. 部分インデックス: エラーログ (event_type = 'error') のみを対象に、created_at で検索
    add_index :logs, :created_at, name: 'idx_partial_error_logs_on_created_pg', where: "event_type = 'error'", algorithm: :concurrently

    # 3. JSONB (payload) 内の特定キーで検索
    # 例: payload->>'error_code' で検索
    add_index :logs, "((payload->>'error_code'))", name: 'idx_expr_logs_payload_error_code', algorithm: :concurrently
    # GINインデックスで payload 全体を検索可能にする
    add_index :logs, :payload, using: :gin, name: 'idx_gin_logs_payload', algorithm: :concurrently

    # 4. BRINインデックス: created_at (時系列データ) に非常に効果的
    # テーブルがcreated_at順にほぼソートされて格納されている場合に最適
    add_index :logs, :created_at, using: :brin, name: 'idx_brin_logs_on_created_at'
    # (BRINインデックスはCONCURRENTLY非対応のため、メンテナンスウィンドウで実行するか、
    #  作成時の負荷を許容する必要がある。ただし作成は比較的速い)

    # 5. message (TEXT型) のあいまい検索 (GIN + pg_trgm)
    # enable_extension 'pg_trgm' が必要
    add_index :logs, :message, using: :gin, opclass: :gin_trgm_ops, name: 'idx_gin_logs_message_trgm', algorithm: :concurrently
  end
end

レプリケーション遅延の考慮

大規模インデックス作成時は、レプリケーション遅延を考慮する必要があります：

class AddLargeIndex < ActiveRecord::Migration[7.1]
  def change
    # レプリケーション遅延を監視
    if mysql_replication_lag > 10.seconds
      raise "Replication lag too high"
    end
    
    # 分割実行
    add_index :huge_table, :column, algorithm: :inplace, lock: :none
  end
  
  private
  
  def mysql_replication_lag
    result = ActiveRecord::Base.connection.execute("SHOW SLAVE STATUS")
    lag = result.first['Seconds_Behind_Master']
    lag ? lag.to_i.seconds : 0.seconds
  end
end

注意: algorithm: :concurrently (PostgreSQL) やMySQLのオンラインDDL機能を使っても、完全に負荷がないわけではありません。特に巨大テーブルへの操作は、慎重な計画とテストが必要です。

ソートJOINとN+1問題の解決

Railsアプリケーションで頻繁に遭遇するパフォーマンス問題の一つが「N+1クエリ問題」です。これは、親オブジェクトを1クエリで取得した後、各親オブジェクトに関連する子オブジェクトを個別のクエリで取得してしまう（N回の追加クエリが発生する）問題です。

N+1問題解決のメリット:

クエリ数の劇的削減: 何百ものクエリが数個のクエリに集約されます。
レスポンス時間の大幅短縮: 特にネットワークレイテンシやDB接続オーバーヘッドが大きい場合に効果的。
データベース負荷の軽減: DBサーバーへの問い合わせ回数が減り、リソース消費を抑えます。

N+1問題解決のデメリット（eager loadingの注意点）:

メモリ使用量の増加: 一度に多くの関連データをロードするため、アプリケーションサーバーのメモリ消費が増えることがあります。特に、不要な関連データまで大量に読み込むと問題になります。
複雑なクエリの発行: includes や eager_load は内部的にJOINや複数のIN句を使ったクエリを発行します。これが過度に複雑になると、DBオプティマイザが最適化しきれず、逆に遅くなることも稀にあります。
インデックス依存: JOINを効率的に行うためには、外部キーや結合条件のカラムに適切なインデックスが不可欠です。

Railsでは、includes, preload, eager_load といったメソッドでN+1問題を解決（eager loading, 事前読み込み）できます。

# N+1問題が発生するコード
blogs = Blog.limit(10)
blogs.each do |blog|
  puts blog.posts.size # 各blogごとにpostsテーブルへのクエリが発行される
end

# includes を使った最適化 (通常は LEFT OUTER JOIN または別クエリ(IN句)で解決)
blogs_with_posts = Blog.includes(:posts).limit(10)
blogs_with_posts.each do |blog|
  puts blog.posts.size # 追加クエリなし
end

# preload を使った最適化 (常に別クエリ(IN句)で解決)
blogs_with_preloaded_posts = Blog.preload(:posts).limit(10)
# (挙動は includes と似るが、クエリ発行戦略が異なる)

# eager_load を使った最適化 (常に LEFT OUTER JOIN で解決)
blogs_with_eager_loaded_posts = Blog.eager_load(:posts).limit(10)

必要なインデックス:
関連付けに使用される外部キーカラムには、必ずインデックスを作成しましょう。

db/migrate/add_association_indexes.rb

class AddAssociationIndexes < ActiveRecord::Migration[7.1]
  def change
    # Postモデルが Blogモデルに belongs_to :blog している場合
    add_index :posts, :blog_id unless index_exists?(:posts, :blog_id)

    # Commentモデルが Postモデルに belongs_to :post している場合
    add_index :comments, :post_id unless index_exists?(:comments, :post_id)

    # ポリモーフィック関連の場合 (例: commentable)
    # add_index :comments, [:commentable_type, :commentable_id]
  end
end

N+1問題の検出:

bullet gem: N+1クエリや未使用のeager loadingを開発中に自動検出して通知してくれます。

参考:

Rails Guides: Eager Loading Associations

GitHub - flyerhzm/bullet: help to kill N+1 queries and unused eager loading

JSON/JSONB インデックス最適化

多くのモダンなRailsアプリケーションでは、スキーマレスなデータを扱うためにJSONまたはJSONB型のカラムが利用されます。これらのカラム内のデータに対する検索を効率化するためには、特別なインデックス戦略が必要です。

MySQL 8 での JSON インデックス

MySQL 5.7.8以降でJSON型がサポートされ、8.0.13以降では関数インデックスによりJSON内の特定の値へのインデックス作成が容易になりました。また、生成カラム(Generated Column)と通常のインデックスを組み合わせる方法も有効です。

JSON型インデックスのメリット (MySQL):

特定JSONパスの値へのインデックス: JSON_EXTRACT や ->> 演算子で抽出した値に関数インデックスを作成できます。
生成カラムによる最適化: JSON内の頻繁に検索する値を仮想生成カラムまたはストアード生成カラムとして定義し、そのカラムに通常のB-Treeインデックスを作成できます。これは関数インデックスよりもクエリがシンプルになる場合があります。

JSON型インデックスのデメリット (MySQL):

更新パフォーマンス: JSONドキュメントの一部が変更されただけでも、関連するインデックスの更新が必要になります。
複雑なクエリ: JSONパスの指定や型変換がクエリを複雑にする可能性があります。
インデックスタイプの制限: GINインデックスのような、JSON構造全体に対する柔軟な検索（キーの存在確認、複数キー条件など）はPostgreSQLほど得意ではありません。

db/migrate/add_json_indexes_to_mysql_products.rb

class AddJsonIndexesToMysqlProducts < ActiveRecord::Migration[7.1]
  def change
    # 前提: productsテーブルに properties JSON カラムが存在

    # 関数インデックス: properties内の 'color' というキーの値にインデックス
    # (MySQL 8.0.13+)
    add_index :products, "(JSON_UNQUOTE(JSON_EXTRACT(properties, '$.color')))", name: 'idx_func_products_properties_color_mysql'

    # 生成カラムとインデックス: 'brand'キーの値を別カラムとして定義しインデックス
    # (MySQL 5.7+)
    execute "ALTER TABLE products ADD COLUMN brand_name VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(properties, '$.brand'))) STORED"
    add_index :products, :brand_name, name: 'idx_generated_products_brand_name'

    # MySQL 8.0.17+ では Multi-Valued Indexes がJSON配列に対して利用可能
    execute "CREATE INDEX idx_multi_valued_tags ON products ((CAST(properties->'$.tags' AS CHAR(50) ARRAY)))"
    # これにより JSON_CONTAINS, JSON_OVERLAPS, MEMBER OF() といった演算子が高速化される
  end
end

MySQL 8.0でのJSON検索クエリ例：

# 関数インデックスが利用される例 ($.color のインデックスがある場合)
Product.where("JSON_UNQUOTE(JSON_EXTRACT(properties, '$.color')) = ?", "red")

# 生成カラム (brand_name) のインデックスが利用される例
Product.where(brand_name: "Acme")

# Multi-Valued Index が利用される例 ($.tags のインデックスがある場合)
Product.where("JSON_CONTAINS(properties->'$.tags', JSON_QUOTE('organic'))")

PostgreSQL での JSONB インデックス

PostgreSQLでは、JSONB型（バイナリ形式のJSON）が推奨され、これに対して非常に強力で柔軟なインデックス機能（特にGINインデックス）が提供されています。

JSONB型インデックスのメリット (PostgreSQL):

GINインデックスによる強力な検索:
- キーの存在確認 (?, ?|, ?& 演算子)。
- 特定の値を含むか (@> 演算子)。
- JSONBドキュメント全体に対するインデックスが可能。
式インデックス: JSONB内の特定の値やパスに対してB-Treeインデックスを作成し、等価検索や範囲検索を高速化。
パフォーマンス: テキスト形式のJSONよりもJSONBの方が一般的に高速。

db/migrate/add_jsonb_indexes_to_postgresql_products.rb

class AddJsonbIndexesToPostgresqlProducts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction! # CONCURRENTLY を使う場合

  def change
    # 前提: productsテーブルに properties JSONB カラムが存在

    # GINインデックス: properties カラム全体に作成。多様な検索に対応。
    add_index :products, :properties, using: :gin, name: 'idx_gin_products_properties_pg', algorithm: :concurrently

    # GINインデックス (特定の演算子クラス): jsonb_path_ops を使うと @> 演算子に特化
    # add_index :products, :properties, using: :gin, opclass: :jsonb_path_ops, name: 'idx_gin_products_properties_path_ops', algorithm: :concurrently

    # 式インデックス (B-Tree): properties内の 'price' (数値と仮定) にインデックス
    add_index :products, "((properties->>'price')::numeric)", name: 'idx_expr_products_properties_price_pg', algorithm: :concurrently

    # 式インデックス (B-Tree): properties内のネストしたキー 'metadata'->'version' (整数と仮定)
    add_index :products, "((properties#>>'{metadata,version}')::integer)", name: 'idx_expr_products_meta_version_pg', algorithm: :concurrently
  end
end

PostgreSQLでのJSONB検索クエリ例：

# GINインデックスが利用される例
# 'color'キーが存在するか
Product.where("properties ? ?", 'color')
# 'color'が'red'であるか (jsonb_path_ops GINインデックスまたは式B-Treeインデックスが有効)
Product.where("properties @> ?", {color: 'red'}.to_json)
# 'tags'配列に'organic'が含まれるか (tagsがproperties内にある場合)
Product.where("properties->'tags' @> ?", ['organic'].to_json)

# 式インデックス (B-Tree) が利用される例
# properties内のpriceが1000未満
Product.where("((properties->>'price')::numeric) < ?", 1000)
# properties内のmetadata->versionが2より大きい
Product.where("((properties#>>'{metadata,version}')::integer) > ?", 2)

参考:

MySQL 8.0 Reference Manual: Indexing JSON Documents

MySQL 8.0 Reference Manual: Generated Columns

PostgreSQL Documentation: JSON Types - Indexing

セキュリティとインデックス

機密データのインデックス

暗号化されたカラムにはインデックスが効果的に動作しません：

# 暗号化カラムへのインデックスは効果がない
class User < ApplicationRecord
  encrypts :ssn  # 暗号化される
  
  # これは動作しない
  # add_index :users, :ssn
  
  # 代わりにハッシュインデックスを使用
  before_save :set_ssn_hash
  
  private
  def set_ssn_hash
    self.ssn_hash = Digest::SHA256.hexdigest(ssn) if ssn_changed?
  end
end

# マイグレーション
class AddSecureIndexes < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :ssn_hash, :string
    add_index :users, :ssn_hash, unique: true
  end
end

まとめ

適切なデータベースインデックス戦略は、Railsアプリケーションのパフォーマンスを劇的に向上させる最も効果的な手段の一つです。この記事では、インデックスの基本原理から、MySQLとPostgreSQLそれぞれの高度なインデックスタイプ、診断・チューニング手法、そして具体的な設計例までを解説しました。

インデックス戦略を成功させるための鍵:

クエリパターンの徹底分析: 実際のアクセスパターンを把握し、最も効果の高い箇所にインデックスを設計します。推測ではなく計測に基づきましょう。
カーディナリティとセレクティビティの理解: 絞り込み効果の高いカラムにインデックスを作成するのが基本です。
複合インデックスのカラム順序の最適化: 左端プレフィックスのルールを理解し、WHERE句、ORDER BY句の使われ方を考慮して順序を決定します。
カバリングインデックスの活用: 頻繁なクエリでテーブルアクセスを回避できれば、大きなパフォーマンス向上が期待できます。
部分インデックスと式インデックスの戦略的利用: 特定条件下でのみインデックスを有効にしたり、加工された値に対してインデックスを作成することで、よりピンポイントな最適化が可能です。
データベース固有機能の活用: MySQLのプレフィックスインデックスや不可視インデックス、PostgreSQLのGIN/BRINインデックスや豊富な式インデックスなど、利用しているDBの強みを最大限に活かしましょう。
インデックスのコスト意識: インデックスは検索を高速化する一方で、ストレージを消費し、書き込み（INSERT/UPDATE/DELETE）パフォーマンスを低下させます。不要なインデックスや効果の薄いインデックスは定期的に見直し、削除することも重要です。
継続的な監視とメンテナンス: インデックスの使用状況、統計情報、断片化などを定期的に監視し、必要に応じてANALYZE、REINDEX、OPTIMIZE TABLEなどのメンテナンスを行います。
テストとベンチマーク: インデックス戦略の変更が期待通りの効果をもたらすか、また意図しない性能劣化を引き起こさないかを、テストとベンチマークで検証します。

MySQL 8 と PostgreSQL のインデックス戦略における特徴的な違い

特徴	MySQL (主にInnoDB)	PostgreSQL	備考
主キーの扱い	クラスタ化インデックス (物理的にデータが主キー順に格納)	通常のB-Treeインデックス (`CLUSTER`で一時的に並べ替え可能)	InnoDBのPK検索は非常に高速。セカンダリインデックスはPKを含む。
テキスト検索	FULLTEXTインデックス (InnoDBは5.6+), プレフィックスインデックス	GIN/GiSTインデックス + `tsvector` (高機能), `pg_trgm` + GIN/GiST (あいまい検索)	PGの柔軟性と機能性が高い。
JSON/複合型検索	JSON型。関数インデックス、生成カラム+インデックス、Multi-Valued Index (8.0.17+)	JSONB型 (推奨)。GINインデックスによる強力な検索 (`@>`, `?`等)、式インデックス。配列型にもGIN。	PGのJSONBとGINの組み合わせが非常に強力。
部分インデックス	代替実装が必要（式インデックスや生成カラム）	全バージョンで強力にサポート。柔軟なWHERE句。	PGの方が歴史が長く、より柔軟。
式/関数インデックス	8.0.13+ で関数インデックスをサポート。	式インデックスとして強力にサポート。多様な関数や式が利用可能。	PGの方が柔軟性が高い。
大規模順序データ	特化したインデックスはなし。パーティショニング等で対応。	BRINインデックス (超小型、高速更新)。	PGのBRINは特定条件下で非常に有効。
インデックステスト支援	不可視インデックス (8.0+)。	なし (ただし `EXPLAIN` や `pg_stat_statements` で分析)。	MySQLの不可視インデックスは安全な導入に便利。
オンラインDDL	`ALGORITHM=INPLACE, LOCK=NONE` (5.6+), `pt-online-schema-change` 等。	`CREATE INDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY`。	どちらも高負荷な本番環境でのインデックス操作を支援する機能がある。

どちらを選ぶか？
単純な比較は難しいですが、一般的な傾向として：

MySQL: シンプルな構成やリードレプリカによるスケールアウトが比較的容易で、広範な実績があります。InnoDBのクラスタ化インデックスは特定のアクセスパターンで非常に強力です。
PostgreSQL: データ型の豊富さ、インデックス種類の多様性（GIN, BRIN, GiSTなど）、SQL標準への準拠度、拡張性（PostGIS, TimescaleDBなど）に優れています。複雑なクエリやデータ構造を扱う場合に強みを発揮します。

最終的には、アプリケーションの具体的な要件、チームの習熟度、利用可能なインフラなどを総合的に考慮して選択することになります。

インデックス最適化は一度行ったら終わりではなく、アプリケーションの成長やデータ量の変化、アクセスパターンの変化に応じて継続的に見直し、改善していく反復的なプロセスです。

参考: Percona Blog: Benchmarking PostgreSQL: The Hidden Cost of Over-Indexing (過剰なインデックスのコストについて)

株式会社GroovesPublication

Grooves所属メンバーの個人発信をまとめたPublicationです。記事内容はあくまでも個人の見解であり、会社としてレビュー等はしておりません。あらかじめご了承ください。