インデックス貼って終わりじゃない！MySQL/PostgreSQLで極める効く設計のコツ！

1. はじめに

RDB のテーブルに 「なんとなくインデックス」 を貼っていませんか？

・WHERE に使ってるから入れとくか…
・JOIN だし、多分必要なんだよね…
・貼っとけば速くなるって聞いたし…

しかし、インデックス設計を誤ると、

• ❌ アプリが急に重くなる
• ❌ INSERT / UPDATE / DELETE が遅くなる
• ❌ 「インデックスがあるのにフルスキャン」が増える

といった問題につながります😢

「どのクエリに対して、どのインデックスが効くのか」を理解した設計は、最小限のコストで最大の性能向上を得るための強力なチューニング手段です。

---

2. 最近のトレンド：ORM・FW の進化で SQL チューニングはどう変わったか

近年、アプリケーションフレームワークや ORM はかなり高機能になり、「素の SQL を最適化する」以外のアプローチでもボトルネックを減らせるようになってきました。

🚀 ORM / FW / クラウドが助けてくれる最適化

代表的な例として、以下のような機能があります。

• N+1 クエリの検出
  • Rails Bullet / Laravel Debugbar / Django Debug Toolbar など
• Eager / Lazy ローディングの制御と分析
• Bulk Insert / Batch Update の API 提供（明示的に使うことでまとめて更新しやすくなる）
• MySQL 8.0以降/8.4 LTS / PostgreSQL のクエリプランナの進化
  • 統計情報や拡張統計の改善により、より妥当なクエリ計画が選ばれやすくなっている
• クラウドによるインデックス推奨（AI/ML）
  • AWS Performance Insights / DevOps Guru, Azure SQL Database Advisor などが、不足しているインデックスを自動提案してくれる時代になりました。

「何も考えずに SQL を書く」よりは、ORM や FW、クラウド機能を適切に使うことで、性能はある程度までは自動的に底上げされます。

⚠️ それでもインデックス設計は必須

ただし、どれだけフレームワークや ORM が賢くなっても、「どこにどんなインデックスを貼るか」は自動では最適化されません。

ORM が「0 → 60 点」くらいの最適化を担うとすれば、
「60 → 100 点」に持ち上げるのは、依然としてインデックス設計とクエリ設計です。

---

3. インデックスが効く理由：B-Tree をざっくり理解する

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

多くの RDBMS でデフォルトのインデックスタイプになっているのが、B-Tree（平衡木）インデックスです。[4][1]

• 値がソートされた状態で保持される「順序付きリスト」
• 各値から、該当するテーブルの行（レコード）へのポインタを持つ

イメージとしては、「電話帳」や「書籍の索引」に近いものです。

「ユーザーIDが 123 の行はどこ？」
→ 木構造をたどることで、テーブル全体をなめることなく、必要な範囲だけにアクセスできる

インデックスあり vs なし

インデックスなし → 原則として全件スキャン（フルテーブルスキャン）
インデックスあり → インデックス経由で狭い範囲だけをスキャン

テーブルが小さいうちは差が出にくいですが、行数が増えるほど差は大きくなります。
100 万件クラスのテーブルでは、クエリ内容や環境によっては、インデックスの有無で 1 桁～2 桁レベルの実行時間差が出ることも少なくありません。

⚠️ インデックスの副作用

一方で、インデックスには次のようなコストもあります。

• INSERT / UPDATE / DELETE のたびに、インデックスも更新が必要
• インデックス分のストレージ消費
• 不要なインデックスが多いほど、オプティマイザのプラン選択が複雑になり、逆に性能低下を招くこともある

「とりあえず全部に貼る」ではなく、「どのクエリに対して、どのインデックスが効いているのか」を意識することが重要です。

💡 コラム：InnoDB のインデックス構造（クラスタ化インデックス）

MySQL の標準エンジン InnoDB は、クラスタ化インデックス（Clustered Index） という構造を採用しています。これはテーブル設計において非常に重要な特性です。

• 主キー（Primary Key）＝ テーブルそのもの

  • InnoDB では、主キーの B-Tree のリーフノードに「行データそのもの」が格納されています。
  • つまり、主キーで検索すると、インデックスを辿った先にデータがあるため、非常に高速です。

• セカンダリインデックス（その他のインデックス）

  • セカンダリインデックスのリーフノードには、「その行の主キーの値」が格納されています。
  • そのため、セカンダリインデックス経由でデータを取得する場合、「① セカンダリインデックスで主キーを探す」→「② 主キーでデータを探す」 という 2 段階のアクセスが発生します。

設計への影響

1. 主キーは小さく: 主キーが大きい（長い文字列など）と、すべてのセカンダリインデックスのサイズも肥大化します。
2. ランダム主キーは避ける: UUID v4 のようなランダム値を主キーにすると、データの物理的な挿入位置が飛び飛びになり、書き込み性能が劇的に低下します（UUID v7 推奨の理由）。

上級：ページ分割（Page Split）とフィルファクター

B-Tree インデックスは、内部的に「ページ」という固定サイズのブロック（InnoDB では 16KB）でデータを管理しています。

• ページ分割: ページが満杯の状態で新しいエントリを挿入すると、ページを 2 つに分割する処理が発生します。これは I/O コストが高く、インデックスの断片化を招きます。
• シーケンシャル INSERT: AUTO_INCREMENT や UUID v7 のように順序立った値を挿入すると、常にページ末尾への追記となり、ページ分割が最小化されます。
• ランダム INSERT: UUID v4 のようなランダム値では、ページ分割が頻発し、書き込み性能が 2〜10 倍悪化することもあります。

PostgreSQL の fillfactor

PostgreSQL では、テーブルやインデックス作成時に fillfactor を指定することで、ページの充填率を制御できます。

-- 更新が多いテーブルでは fillfactor を下げておく（デフォルト 100）
CREATE INDEX idx_orders_status ON orders (status) WITH (fillfactor = 90);

これにより、ページ内に余裕を持たせ、HOT（Heap Only Tuple）更新を促進してインデックス更新を減らせます。

---

4. どこに貼るべきか：判断基準

✔ 貼るべきカラム 4 大ポイント

インデックス候補になるのは、主に次のようなカラムです。[6][4]

1. WHERE で頻繁に絞り込みに使うカラム
2. JOIN の結合キーとして頻出するカラム
3. ORDER BY の先頭に来ることが多いカラム
4. 高い選択性（値のバリエーションが多く、一度にヒットする行が少ない）を持つカラム

ここで重要なのが「4. 選択性」です。
選択性が低い（例：true/false 程度しかない）カラムは、単独インデックスとしてはあまり役に立たないケースが多く、フルスキャンが選ばれる場合も少なくありません。[7]

選択性の定量評価（PostgreSQL 例）

PostgreSQL では、統計情報ビュー pg_stats から選択性の目安を確認できます。[7]

SELECT attname, n_distinct, null_frac
FROM pg_stats
WHERE tablename = 'orders';

• n_distinct が大きい → 値のバリエーションが多く、その列で絞り込みやすい傾向
• n_distinct が非常に小さい → 単独インデックスでは、フルスキャンの方が有利と判断されるケースが多い

実際には、複合インデックスや部分インデックスなどと組み合わせることで意味を持つ場合もあるため、統計情報と EXPLAIN をセットで見るのがおすすめです。

---

5. 複合インデックスの本質「左から順」

複数カラムをまとめた B-Tree 複合インデックスには、「左から順（leftmost prefix）」のルールがあります。[2][1][4]

CREATE INDEX idx_orders_user_status
  ON orders (user_id, status);

◎ 効率よく効く例

WHERE user_id = 12345 AND status = 'PAID'
WHERE user_id = 12345

• user_id が左端のカラムなので、インデックスの順序を活かして効率的に絞り込めます。

△ 効率が落ちる / 効きにくい例

WHERE status = 'PAID'

status 単独での条件では、「(user_id, status) 複合インデックスをフルに活かす」ことはできません。
オプティマイザがインデックスを一部スキャンしてフィルタするプランを選ぶこともありますが、一般に「左端カラムを使わない複合インデックスの使い方」は効率が悪くなりがちです。[5][1]

実務的には、
「複合インデックスは左端のカラムから順に条件が付くクエリで最大限効く」
と覚えておくと設計しやすくなります。

応用：ORDER BY + LIMIT をインデックスでさばく

CREATE INDEX idx_user_created
  ON orders (user_id, created_at DESC);

このインデックスがあると、例えば次のようなクエリが効率的になります。[5]

SELECT *
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

• user_id で絞り込み
• その中で created_at DESC 順に並ぶインデックスを上から 20 件だけ読む

という形で、インデックスだけでソート＋LIMIT まで処理できるため、ファイルソートや余計なテーブルアクセスを大きく減らせます。

上級：Covering Index（カバリングインデックス）

セカンダリインデックス経由のアクセスでは、通常「インデックス → 主キー → データ」という 2 段階のアクセスが発生します。しかし、クエリで必要なカラムがすべてインデックスに含まれている 場合、テーブル本体へのアクセス（ランダム I/O）を完全にスキップできます。

-- user_id, status, created_at だけを取得するクエリ
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 12345;

-- このインデックスがあれば、テーブルアクセス不要（Covering）
CREATE INDEX idx_covering
  ON orders (user_id, status, created_at);

EXPLAIN で Using index（MySQL）や Index Only Scan（PostgreSQL）が表示されれば、カバリングインデックスが効いています。

設計のポイント

• SELECT するカラムが少なく固定的なクエリに有効
• ただし、インデックスサイズが肥大化するため、更新コストとのトレードオフを考慮
• SELECT * を避け、必要なカラムだけを取得する習慣が前提

上級：Index Skip Scan（MySQL 8.0.13+）

MySQL 8.0.13 以降では、複合インデックスの 左端カラムを使わないクエリ でも、オプティマイザが「Index Skip Scan」を選択することがあります。

-- (gender, age) の複合インデックスがある場合
CREATE INDEX idx_gender_age ON users (gender, age);

-- 従来は効かなかったクエリ
SELECT * FROM users WHERE age = 25;

Index Skip Scan では、左端カラム（gender）の各値ごとにインデックスを「スキップ」しながらスキャンします。

注意点

• 左端カラムのカーディナリティ（値の種類）が 少ない 場合に有効
• カーディナリティが高いと、スキップ回数が増えて逆に遅くなる
• EXPLAIN で Using index for skip scan が表示されれば適用されている

---

6. EXPLAIN / EXPLAIN ANALYZE の読み解き

インデックス設計を「雰囲気」から「根拠あり」に変えるための必須ツールが EXPLAIN です。

MySQL

EXPLAIN
SELECT * FROM orders WHERE user_id = 12345;

よく見るポイントは次のあたりです。[8][4]

• type
  • アクセス方法（ALL = フルスキャン, range, ref など）
• rows
  • オプティマイザが見積もる行数（ここが大きすぎたり小さすぎたりするとプラン選択を誤る）
• Extra
  • Using index / Using filesort など、インデックス利用やソート状況のヒント

💡 MySQL 8.0以降なら EXPLAIN ANALYZE が使える
従来は「見積もり」しか見れませんでしたが、MySQL 8.0.18 以降では実際にクエリを実行して計測する EXPLAIN ANALYZE が使えます。

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;

これにより、「実際の実行時間」や「スキャン行数」がツリー形式で表示され、PostgreSQLに近い詳細な分析が可能になりました。

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 12345;

PostgreSQL では、実測値込みで確認できるのが強みです。[9][10]

• Node Type / Index Scan using idx_xxx
  • どのインデックスをどのように使ったか
• actual rows / loops
  • 実際に何行を何回読み込んだか
• Buffers
  • キャッシュ・ディスクからの読み取り状況

「見積もり（rows）と実測（actual rows）がどれだけずれているか」「どこでフルスキャンになっているか」を見ることで、インデックス追加や統計情報のメンテナンス方針を定量的に決められます。

上級：ヒストグラム統計で精度を上げる

デフォルトの統計情報（n_distinct など）だけでは、データの 偏り（スキュー） を正しく把握できないことがあります。例えば「status = 'PENDING'」が全体の 1% なのか 90% なのかで、最適なプランは大きく異なります。

MySQL 8.0+

-- ヒストグラムの作成
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- ヒストグラムの確認
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'orders';

PostgreSQL

PostgreSQL では ALTER TABLE ... ALTER COLUMN ... SET STATISTICS で統計サンプル数を増やすか、拡張統計（CREATE STATISTICS）を使用します。

-- 統計サンプル数を増やす
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 複数カラムの相関を考慮した拡張統計（PostgreSQL 10+）
CREATE STATISTICS orders_user_status (dependencies)
  ON user_id, status FROM orders;

上級：インデックスヒント（最終手段）

オプティマイザが誤ったプランを選択し続ける場合、インデックスヒント で強制的に使用するインデックスを指定できます。ただし、これは 最終手段 であり、統計情報の更新やクエリの書き換えで解決できないか先に検討すべきです。

MySQL

-- 推奨（ヒントを与えるが、最終判断はオプティマイザに委ねる）
SELECT * FROM orders USE INDEX (idx_user_status)
WHERE user_id = 12345;

-- 強制（オプティマイザの判断を無視）
SELECT * FROM orders FORCE INDEX (idx_user_status)
WHERE user_id = 12345;

PostgreSQL

PostgreSQL には直接的なインデックスヒントはありませんが、enable_seqscan = off などのプランナ設定で間接的に制御できます（本番での常用は非推奨）。

なぜ最終手段なのか？

• データ分布が変わると、ヒントが逆効果になる
• 保守性が下がる（なぜこのヒントが必要なのかを将来のメンテナが理解しにくい）
• 根本原因（統計情報の問題、クエリ設計の問題）を隠蔽してしまう

---

7. 実務でよくあるインデックス事故

LIKE '%keyword%' でインデックスが効かない

B-Tree インデックスは、前方一致には強いですが、先頭にワイルドカードがあると使えません。

• OK：WHERE name LIKE 'abc%'
• NG：WHERE name LIKE '%abc'

後者は、全文検索向けインデックス（MySQL の FULLTEXT、PostgreSQL の GIN + text search）などの検討が必要です。[11][9]

選択性 0 に近いカラムへの単独インデックス

例：is_deleted, is_active, flag などの boolean / 小さい enum

こうしたカラムは、単独インデックスでは「ほとんど全行ヒット」になりやすく、フルスキャンと大差ないため、オプティマイザがインデックスを使わないことが多いです。[7]
他の高選択性カラムとの複合インデックスで活かす方が現実的です。

PostgreSQLなら「部分インデックス」も検討
論理削除フラグ（is_deleted = false）のような場合、必要な行だけにインデックスを貼る「部分インデックス」が有効です。

CREATE INDEX idx_orders_active ON orders (user_id) WHERE is_deleted = false;

これにより、インデックスサイズを劇的に小さくしつつ、有効なデータへのアクセスを高速化できます。

更新頻度の高いカラムへのインデックス

更新が多いカラムをインデックスに含めると、

• 行更新時にインデックスエントリの更新も走る
• 複合インデックスだと更新対象が増えがち

といった理由で、書き込み性能に悪影響が出ます。[4]
書き込み頻度と読み取り頻度のバランスを考えたうえで、「本当にそのカラムを含める必要があるか」を検討しましょう。

複合インデックスの乱立

使われていない・ほとんど使われない複合インデックスが大量にあると、

• ストレージ消費
• 更新時のコスト増
• オプティマイザがプラン選択に悩む（統計の収集・利用コスト増）

という形で「負債」になります。
スロークエリログや EXPLAIN 実績と照らし合わせて、「実際に効いているインデックスだけを残す」という方針が重要です。

⚠️ ランダムな UUID (v4) を主キーにしてしまう

主キー（Primary Key）は通常、クラスタ化インデックス（テーブルデータの物理的な並び順）として扱われます（特に MySQL/InnoDB）。
ここにランダムな値である UUID v4 を採用すると、INSERT のたびにディスク上のランダムな位置への書き込みが発生し、インデックスの断片化（フラグメンテーション）と著しい性能低下を招きます。

✅ 2025年の解決策：UUID v7 を使う
2024年に標準化された UUID v7 は、先頭にタイムスタンプ情報を含んでいるため、時系列順にソート可能です。
これにより、B-Tree インデックスへの追加が「追記型（シーケンシャル）」に近い挙動となり、UUID v4 のパフォーマンス問題を解決しつつ、一意性を確保できます。
多くの言語・フレームワークで UUID v7 対応が進んでいるため、新規設計では v7 の採用を強く推奨します。

---

8. オンラインインデックス作成

本番環境で既存テーブルにインデックスを追加する際は、「オンラインでのスキーマ変更」をサポートするツールを使うと安全性と可用性を両立しやすくなります。

⚠️ まずは標準機能（Online DDL）を確認

MySQL 5.6以降、多くのインデックス追加操作は ALGORITHM=INPLACE, LOCK=NONE によって、参照・更新をブロックせずに実行可能です。数百万件程度であれば、標準の ALTER TABLE で十分なケースも多いです。

それでも以下のツールが必要になるのは、「サーバー負荷を細かく制御したい」「レプリケーション遅延を絶対に許容できない」「テーブルサイズが巨大」といったケースです。

🔧 pt-online-schema-change（Percona Toolkit）

• 既存テーブルを元に「影テーブル」を作成し、データコピーとトリガでの差分同期を行いつつ、最後に atomic rename で切り替える方式
• 多くの環境で実績があり、古い MySQL バージョンも含め幅広く対応しているツールです。[12][13]

🔧 gh-ost（GitHub 製）

• バイナリログを利用した非同期レプリケーション方式で差分を取り込み、トリガを使わないのが特徴
• 高トラフィック環境での負荷制御や再開性に優れ、MySQL 5.7/8.0/8.4+ を中心に使われることが多いです。[3][12]

どちらも「ダウンタイムを極小化したオンラインスキーマ変更」を実現するツールですが、特性は異なるため、自分の環境（バージョン・トラフィック・運用方針）に合わせて選択するのが重要です。[12][3]

---

9. 高度インデックスを使いこなす（PostgreSQL）

PostgreSQL では、B-Tree 以外にも用途特化型のインデックスが用意されています。[14][9]

🔹 GIN Index（JSONB / 全文検索など）

CREATE INDEX idx_data_gin
  ON items USING gin (json_data);

• JSONB / 配列 / 全文検索（to_tsvector）など、「1 行に複数の要素」が乗るケースに有効
• 書き込みコストは比較的高くなるため、更新頻度と読み取り頻度のバランスを考えて適用するのがポイントです。

🔹 BRIN Index（巨大テーブル向け）

CREATE INDEX idx_login_at_brin
  ON access_logs USING brin (login_at);

• 物理的に「近い行ほど似た値を持つ」パターン（時系列ログなど）で、非常に少ないストレージで範囲検索を高速化できるインデックス
• B-Tree と比べて「精度は低いが軽い」インデックスなので、巨大テーブルに対して有効です。

🔹 GiST（空間 / 範囲検索）

• GIS データ（PostGIS）、範囲型（int4range, tsrange など）の検索向け
• 幾何学的な距離や包含関係など、B-Tree では扱いづらい条件を効率よく処理できます。

---

10. パーティショニング × インデックスでスケールさせる

数千万～億件クラスのテーブルになってくると、単純なインデックス追加だけでは限界が見えてきます。
この規模では、テーブルを論理的に分割する「パーティショニング」とインデックスを組み合わせるアプローチが有効です。[9]

効果的なユースケース

• 月ごとのログテーブルやレンジパーティション（例：2025-01 / 2025-02 ...）
• IoT の時系列データ
• 古いデータをまとまって削除したいテーブル

得られる効果

• クエリごとにアクセスするパーティションが限定されることで、インデックスサイズ・スキャン範囲が小さくなる
• 古いパーティションを DROP するだけで大量データの削除が一瞬で終わる

具体的な構文や制約は RDBMS ごとに異なるため、MySQL / PostgreSQL の公式ドキュメントを参照しながら設計するのがおすすめです。

---

11. ORM を使った場合の注意点

ORM を使うと、N+1 や冗長なクエリをある程度防げますが、「どのインデックスが必要か」までは自動では分かりません。

• includes / select_related などで N+1 を解消しても、WHERE 条件や JOIN 条件が複雑になると、インデックスポリシーの見直しが必要になる
• Batch Insert / Bulk Update の API を使うと、「更新回数」は減らせるが、「1 回あたりのインデックス更新負荷」は増える可能性がある

ORM が「クエリの発行パターン」を整えてくれる一方で、
そのクエリを支えるインデックス設計は依然としてアプリケーションエンジニアの仕事 です。

---

12. インデックス設計の PDCA 循環

インデックス設計は一度作って終わりではなく、観測 → 分析 → 設計 → 適用 → 評価 のサイクルを回すことが重要です。
ここでは PostgreSQL 15+ や MySQL 8.0+ を例に、具体的な手順を示します。

---

1️⃣ 遅いクエリの特定（Observe）

まずはスロークエリや性能問題の原因となるクエリを抽出します。

PostgreSQL の場合

-- スロークエリログの確認（設定例）
SHOW log_min_duration_statement;

-- 遅いクエリを抽出
SELECT *
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

• pg_stat_statements 拡張を有効にしておくと、クエリごとの統計が取得可能
• total_exec_time / calls / rows を見て、頻度と時間の両面でボトルネックを把握

MySQL の場合

-- スロークエリログの有効化
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- 0.5秒以上のクエリを記録

-- スロークエリログ確認
SHOW VARIABLES LIKE 'slow_query_log_file';

---

2️⃣ クエリプランの確認（Analyze）

対象クエリの実行プランを確認して、どこでフルスキャンや非効率アクセスが起きているかを把握します。

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 12345 AND status = 'PAID';

• Index Scan / Seq Scan の有無
• actual rows と rows（推定行数）の差
• Buffers（キャッシュ・ディスク読み込み）の状況

実測値と推定値の差が大きい場合は統計情報更新 (ANALYZE orders;) を検討

MySQL

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 12345 AND status = 'PAID';

• type = アクセス方法
• rows = 推定行数
• Extra = インデックス利用やソートの状況

---

3️⃣ インデックス案の設計（Plan）

観測結果をもとに、どのカラムに単独・複合インデックスを作るか検討します。

ポイント

1. WHERE / JOIN / ORDER BY に頻繁に使われるカラムを優先
2. 選択性が高いカラムを左端に置いた複合インデックスを検討
3. 更新頻度が高いカラムは単独インデックスより複合インデックスの一部に組み込む方が現実的
4. ORDER BY + LIMIT を効かせる場合は、インデックス順序を意識

例：複合インデックス設計

-- よくある注文テーブルのケース
CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

• user_id → LEFTMOST
• status → WHERE 条件
• created_at DESC → ORDER BY + LIMIT に効かせる

PostgreSQL 15+

-- 同時アクセスが多い場合
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

• CONCURRENTLY で既存トランザクションを阻害せず作成可能
• 注意：CONCURRENTLY はトランザクション内で使えない
• 失敗時の注意：作成中に失敗すると INVALID なインデックスが残ります。必ず DROP INDEX して再実行が必要です。

MySQL

• pt-online-schema-change または gh-ost を活用してダウンタイムなしで作成

---

4️⃣ 効果の確認（Check）

インデックス作成後に、再度クエリ実行プランを確認します。

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 12345 AND status = 'PAID';

• Index Scan が使われているか
• 実行時間が改善しているか
• キャッシュ / ディスク読み込み量はどう変わったか

実行結果が思わしくない場合は、インデックスのカラム順や複合構成を見直す

---

5️⃣ 統計情報とメンテナンス

• PostgreSQL では ANALYZE / VACUUM で統計を最新化
• 定期的にスロークエリログや pg_stat_statements を確認し、不要なインデックスを削除

ANALYZE orders;
VACUUM (ANALYZE) orders;

• 過去に作ったインデックスが無駄になっていないか、定期的にチェックすることも重要

---

6️⃣ 次の PDCA サイクルへ

1. 遅いクエリを観測
2. 実行プランを確認
3. インデックス案を設計
4. 本番環境で安全に適用
5. 効果を確認
6. 統計更新・不要インデックス削除
7. 新たなボトルネックがあればサイクルを再実施

この循環を回すことで、アプリケーションの成長やデータ増加に応じたインデックス最適化が可能になります。

---

💡 ポイントまとめ

• EXPLAIN / EXPLAIN ANALYZE で 「なぜ効かないか」 を定量的に把握
• LEFTMOST 原則・選択性・更新頻度を意識した設計
• 本番環境ではオンライン作成 (CONCURRENTLY / pt-osc / gh-ost)
• 過去のインデックスも定期チェックして整理
• PDCA を繰り返すことで、性能劣化を未然に防ぐ（大事！）

---

AWS RDS / Aurora 環境でのインデックス設計のポイント

AWS が提供する RDS および Aurora は、マネージドデータベースサービスとして MySQL や PostgreSQL をクラウド上で手軽に利用できる環境です。これらの環境でも、基本的なインデックス設計方針はオンプレミスと同じですが、クラウド特有の特性も考慮する必要があります。

• MySQL の InnoDB や PostgreSQL の B-Tree インデックスの構造や基本動作は変わらず、左端優先ルールや複合インデックス設計の原則もそのまま適用できます。
• Aurora は分散ストレージを採用しており、リードレプリカを使った読み取り負荷分散に強みがあります。一方で、書き込みはプライマリに集中するため、INSERT / UPDATE / DELETE の負荷やインデックス更新コストには注意が必要です。
• ストレージの I/O 性能やクラウドのコストモデルを踏まえ、無駄なインデックスを多数貼ることは避けましょう。過剰なインデックスはストレージ容量の増加やバックアップ時間にも影響します。
• オンラインスキーマ変更に有効なツール（pt-online-schema-change や gh-ost）は、RDS / Aurora 環境においても利用可能で、実稼働環境でのインデックス追加や変更を安全に実施できます。
• AWS の監視ツール（CloudWatch、Performance Insights）を活用し、インデックスの使用状況やパフォーマンスを定期的にモニタリングすると効果的です。

これらをふまえて、AWS 環境においてもオンプレミス同様に「合理的なインデックス設計」と「運用による継続的な改善」に取り組むことが、パフォーマンスとコストのバランスを保つために重要です。

13. まとめ

• インデックスは「索引」としての役割を理解し、B-Tree の性質をざっくり把握する
• LEFTMOST 原則（leftmost prefix）と選択性を意識して、複合インデックスのカラム順を決める
• ORDER BY / LIMIT をインデックスでさばくことで、ソートコストを大きく抑えられる
• UUID v7 の採用や EXPLAIN ANALYZE の活用など、最新のベストプラクティスを取り入れる
• ORM / FW に任せられる部分と、インデックス設計が不可欠な部分を切り分ける
• 高度インデックス（GIN / BRIN / GiST）やパーティショニングは、規模やデータ特性に応じて段階的に導入する
• 本番環境への適用は、標準の Online DDL や外部ツールを活用し、必ず検証してから進める

このあたりまで押さえておくと、「なんとなくインデックスを貼る」から一歩進んで、「なぜそれが効くのか／効かないのか」を説明しながら設計・レビューできるようになります！

この記事が少しでも皆さんの役に立てば幸いです😼

---

参考文献・引用元

MySQLの複合インデックスの仕組みとleftmost prefixルール解説[1]

MySQL複合インデックスがB+ツリー内部でどのように構成されているか（InnoDB事例）[2]

MySQLオンラインスキーマ変更ツールの特徴比較（pt-online-schema-change vs gh-ost）[3]

Alibaba CloudによるMySQLのインデックス設計と運用ベストプラクティス解説[4]

RedgateによるMySQL B-Treeおよび複合インデックスの技術説明[5]

複合インデックス活用による時系列データ高速化事例[6]

PostgreSQL選択性（n_distinct）と統計情報の理解[7]

MySQLがインデックスを使用しない原因の代表例と対処法[8]

PostgreSQLにおけるインデックスの種類・特徴と最適化実践[9]

PostgreSQL公式ドキュメント：行数推定例とインデックス効果の測定[10]

PostgreSQL全文検索・GINインデックスの公式仕様[11]

Perconaによるオンラインスキーマチェンジツール性能比較

PlanetScale公式：オンラインスキーマ変更ツールの紹介

PostgreSQLにおける4億件テーブルのスキャン問題と対策

PostgreSQL複合インデックスの構造と最適化原則解説

PostgreSQLマルチカラムインデックスの活用例