DBインデックス入門（単一index・複合index・index_falseの理由）

背景

複合indexについて理解していなかったので、「そもそもindexってなんだっけ？」まで立ち返って学び直したアウトプット

前提

DBのindexについてですが、Railsのマイグレーションファイルで説明をします

データベースのindexとは何か？

indexとは何か

indexとは、データを素早く見つけるための仕組みのことだ

本の巻末索引と同じで、索引がなければ全ページをめくって探すしかないが、索引があれば一発で目的のページに飛べる

DBも同様で、indexがない場合は条件に合う行を探すために全行を1件ずつ読む必要がある。indexがある場合は、別途用意したソート済みリストを辿って目的の行に直行できる

なぜindexをはるのか

大量のデータから欲しい情報を素早く取得するためだ

YouTubeやX、Instagramのようなサービスは、投稿・編集・削除よりも「データを表示する（読み取る）」操作の方が圧倒的に多い。読み取りが遅いとそれだけ使い勝手に直結するため、読み取りを速くするindexが重要になる

indexは2種類の操作を速くする

操作	indexの効果
絞り込み（WHERE）	合う行に直行できる
並べ替え（ORDER BY）	indexはソート済みなので、別途の並べ替えを省ける

ただしindexはタダではない。INSERT / UPDATE / DELETE のたびにソート済み状態を維持するコストがかかるため、書き込みが少し遅くなる。読みを速くするために、書きで前払いするトレードオフだ

今回の具体例

マルチテナントAI壁打ち機能の threads テーブルを題材にする

テーブルの関係

tenant（テナント）
└── threads（スレッド）
    └── thread_messages（スレッドのメッセージ）

各テナントのユーザーが作成したスレッドを管理するテーブルだ

一覧画面のイメージ

過去スレッドの一覧画面を「会話した順（更新日時の降順）」で表示する

（イメージ画像）

マイグレーション

create_table :threads do |t|
  t.references :tenant, null: false, foreign_key: true, index: false
  t.references :user,   null: false, foreign_key: true
  t.string :title, null: false
  t.timestamps
  t.index %i[tenant_id user_id updated_at]
end

スレッドの一覧画面を開くたびに、次のようなSQLが走るイメージだ

tenant.threads.where(user_id: user.id).order(updated_at: :desc)

SELECT * FROM threads
WHERE tenant_id = 1 AND user_id = 5
ORDER BY updated_at DESC;

単独indexについて

まず tenant_id だけの単独indexで仕組みを理解する

indexあり：どんな表が作られるのか？

単独indexを作ると、本体テーブルとは別に「ソート済みの近道リスト」が生成される

本体テーブル（threadsテーブル）

id	tenant_id	user_id	updated_at
1	2	10	2024-01-03
2	1	20	2024-01-01
3	1	10	2024-01-05
4	3	30	2024-01-02

tenant_id の単独index

tenant_id（ソート済み）	本体のid
1	2
1	3
2	1
3	4

WHERE tenant_id = 1 のクエリを実行すると、このindexの tenant_id = 1 の行に直行し、本体の id = 2, 3 を取得できる。全行を読む必要がない

indexなし：全行を1件ずつ参照する

indexがない場合、DBは本体の全行を1件ずつ読んで条件に合うかを確認する

id	tenant_id	確認結果
1	2	❌ tenant_id = 1 でない
2	1	✅ 合致
3	1	✅ 合致
4	3	❌ tenant_id = 1 でない

行数が増えるほど確認回数も増える。100万行あれば100万回確認することになる

indexありの場合の並び順

indexは作成時にソート済みの状態で保存され、INSERT / UPDATE / DELETE のたびに正しい位置を維持し続ける。そのため ORDER BY tenant_id のようなクエリは、このソート済みのindexをそのまま返すだけで済み、別途の並べ替えコストがかからない

効くSQL・効かないSQL（単独index）

-- ✅ 効く: tenant_idで絞り込む
SELECT * FROM threads WHERE tenant_id = 1;

-- ❌ 効かない: indexを張っていないuser_idだけで絞り込む
SELECT * FROM threads WHERE user_id = 5;

複合indexについて

2つのindexの場合

indexあり：どんな表が作られるのか？

(tenant_id, user_id) の複合indexを作ると、次のような表が生成される

複合index: (tenant_id, user_id)

tenant_id	user_id（テナント内でソート）	本体のid
1	10	3
1	20	2
2	10	1
3	30	4

「まず tenant_id 順、同じ tenant_id の中で user_id 順」に並ぶ。電話帳の「姓 → 名」の順と同じ構造だ

indexなし

indexがない場合は単独indexと同様、全行を1件ずつ確認するしかない

カラムの順番が重要な理由

複合indexの最重要ルールは、左端の列から順にしか効かないことだ

(tenant_id, user_id) の複合indexで効くクエリ・効かないクエリ:

-- ✅ 効く: 左端のtenant_idで絞り込む
SELECT * FROM threads WHERE tenant_id = 1;

-- ✅ 効く: 左端から順に2列で絞り込む
SELECT * FROM threads WHERE tenant_id = 1 AND user_id = 5;

-- ❌ 効かない: 左端を飛ばしてuser_idだけで絞り込む
SELECT * FROM threads WHERE user_id = 5;

user_id = 5 だけで絞り込む場合、indexの中で user_id = 5 の行は各テナントの中にバラバラに散らばっているため、直行できない

原則: = で絞り込む列を左に、ORDER BY する列を右に置く

先頭一致の原則（3つのindexでも同じ原理）

今回の (tenant_id, user_id, updated_at) は3カラムだが、「左端から順に効く」ルールは変わらない。先頭2列（tenant_id, user_id）で絞り込み、末尾の updated_at でソートも賄う構成だ

効くSQL・効かないSQL（複合index）

-- ✅ 効く: 先頭2列で絞り込み、末尾列でORDER BYも1本でカバー
SELECT * FROM threads
WHERE tenant_id = 1 AND user_id = 5
ORDER BY updated_at DESC;

-- ❌ 効かない: 先頭を飛ばしている
SELECT * FROM threads WHERE user_id = 5;

-- ❌ 効かない: updated_atだけで絞り込む
SELECT * FROM threads WHERE updated_at > '2024-01-01';

index: false を使う理由

自動生成されるindexとは

t.references :tenant と書くと、Railsは次の3つを自動で作る

要素	正体	index: false で消える？
tenant_id カラム	bigint の列	❌ 消えない（必須）
外部キー制約	「実在する親行を指す」というDB上のルール	❌ 消えない
tenant_id の単独index	デフォルトで付く近道リスト	✅ これだけ消える

index: false が消すのは単独indexだけで、カラムも外部キー制約も残る

不要なindexのデメリット

indexは多ければいいわけではない。1本追加するごとに INSERT / UPDATE / DELETE のたびにそのindexのソート済み状態を維持するコストがかかり、書き込みが遅くなる。indexの分だけディスクも消費する

だからindex: falseを明示する

複合index (tenant_id, user_id, updated_at) の先頭が tenant_id なので、「tenant_id だけで絞り込む」クエリには複合indexがそのまま効く（左端ルール）

つまり tenant_id の単独indexは複合indexと仕事が丸かぶりしている。残すと書き込みのたびに2本のindexを維持するコストがかかり、ディスクも無駄になる。そのため index: false を明示して単独indexを消している

# index: false → tenant_idの単独indexだけを消す
# カラムも外部キー制約も残る
t.references :tenant, null: false, foreign_key: true, index: false

まとめ

• indexとは「指定した列でソート済みの本体行への近道リスト」。ないと全行スキャン、あると直行できる
• 複合indexは左端の列から順にしか効かない（左端ルール）。= で絞る列を左、ORDER BY する列を右に置く
• t.references は単独indexを自動生成する。複合indexの先頭列と被る場合は index: false で消すのが正解。カラムと外部キー制約は消えない
• indexは書き込みコストとディスクとのトレードオフ。よく WHERE / ORDER BY / JOIN で使う列だけに付ける

感想

• なんとなくindexがいかに愚かなことなのか理解しました。あぶねぇ

参考

• データベースのindexについて（動画）

これめっちゃわかりやすかった