PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：差分理解 － Day6 インデックス基礎

SQL PostgreSQL
スポンサーリンク

Day6 後半のゴール

「“インデックスを張るかどうか”を、自分で判断できる感覚を持つ」

前半で、B-treeインデックスを「ソートされた木構造」としてイメージできました。
後半では、もう一歩踏み込んで「どんなクエリで効くのか」「内部で何をしているのか」「どこで逆効果になるのか」を、実務寄りの例で固めていきます。

ここでのゴールはこうです。
どのカラムにインデックスを張ると効果的か、典型パターンで説明できる。
B-treeインデックスが検索時に“どんなステップ”で行を見つけているか、ざっくり言語化できる。
インデックスが増えると“読み取りは速くなるが書き込みは重くなる”というトレードオフを理解できる。

どのカラムにインデックスを張るべきか

例題：ユーザー・注文テーブルで考える

まずは、よくある2つのテーブルを想像してみます。

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  amount     INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);
SQL

ここで、どこにインデックスを張ると効きそうかを考えます。

users.email
orders.user_id
orders.created_at

この3つは、現実のアプリでよく検索条件に使われるカラムです。

メールアドレスでユーザーを引くクエリ：

SELECT * FROM users WHERE email = 'a@example.com';
SQL

特定ユーザーの注文一覧を取るクエリ：

SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;
SQL

最近の注文を取るクエリ：

SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';
SQL

こういう「WHERE句に頻繁に出てくるカラム」にインデックスを張ると、B-treeが本領を発揮します。

例えば、orders.user_id にインデックスを張るならこうです。

CREATE INDEX idx_orders_user_id ON orders(user_id);
SQL

orders.created_at にも張るならこう。

CREATE INDEX idx_orders_created_at ON orders(created_at);
SQL

ここでの重要ポイントは、「インデックスは“よく検索条件に使うカラム”に張る」というシンプルな基準です。
逆に、「ほとんどWHEREに出てこないカラム」にインデックスを張っても、ほぼ意味がありません。

B-treeインデックスの内部動作を“ステップ”でイメージする

email検索を頭の中で再生してみる

前半では「20の質問ゲーム」でイメージしましたが、ここではもう少し具体的に、B-treeがどう動くかをステップで追ってみます。

users.email にインデックスがある状態で、次のクエリを投げるとします。

SELECT * FROM users WHERE email = 'a@example.com';
SQL

PostgreSQLの中では、ざっくりこんな流れになります。

まず、B-treeインデックスの「根っこ（ルートノード）」を読む。
ルートノードには、「どの範囲の値がどの枝にあるか」という情報が入っている。
'a@example.com' が属する範囲の枝を選び、その枝（子ノード）を読む。
子ノードでも同じように、「どの範囲か」を見て、さらに下のノードへ降りていく。
葉ノードにたどり着くと、そこには「emailの値」と「その行がテーブル本体のどこにあるか（ポインタ）」が並んでいる。
目的のemailに一致するエントリを見つけたら、そのポインタを使ってテーブル本体から行を読み出す。

この「上から下へ降りていく」ステップが、木構造の探索です。
データが100万行あっても、木の高さはせいぜい数段なので、数回のディスクアクセスで目的の場所にたどり着けます。

ここでの深掘りポイントは、「インデックスは“テーブルのコピー”ではなく、“キーと行へのポインタのソート済み一覧”」だということです。
インデックスをたどった後、最終的にはテーブル本体を読みに行く、という二段構えになっています。

範囲検索でのB-treeの動き

「先頭の位置に飛んでから、必要な範囲だけなめる」

次に、範囲検索のときの動きをイメージしてみます。

orders.created_at にインデックスがある状態で、次のクエリを投げるとします。

SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
SQL

このとき、B-treeはこう動きます。

まず、'2024-01-01' 以上の値が現れる「最初の位置」を、木構造をたどって探す。
その位置から、インデックスの葉ノードを順番に読み進めていく。
'2024-01-31' を超えたところで止める。
その間に見つかったエントリのポインタを使って、テーブル本体から行を読み出す。

つまり、「範囲の先頭にジャンプして、そこから必要な範囲だけスキャンする」という動きです。
これが、B-treeが範囲検索に強い理由です。

もしインデックスがなければ、「テーブル全体をなめながら、created_at が条件に合うかどうかを1行ずつチェックする」ことになります。
データが増えれば増えるほど、この差は大きくなります。

インデックスの“裏側のコスト”

「INSERT/UPDATE/DELETEのたびに、木を更新している」

インデックスは読み取りを速くしてくれますが、タダではありません。
書き込みのたびに、インデックス側も更新する必要があります。

例えば、orders に1行INSERTするとき、PostgreSQLはこうします。

テーブル本体に新しい行を書き込む。
user_id にインデックスがあれば、そのB-treeに「user_idの値＋行へのポインタ」を挿入する。
created_at にインデックスがあれば、そのB-treeにも同様に挿入する。

つまり、「インデックスの数だけ、木構造の更新が発生する」ということです。
UPDATEでインデックス対象のカラムが変わるときも同様で、「古い値をインデックスから削除し、新しい値を挿入する」処理が走ります。

ここでの重要ポイントは、「インデックスは読み取りを速くする代わりに、書き込みを重くする」というトレードオフです。
だからこそ、「本当に必要なカラムにだけ張る」「闇雲に増やさない」という判断が大事になります。

インデックスが“効かない”典型パターン

「条件がゆるすぎる／曖昧すぎる場合」

B-treeインデックスがあっても、PostgreSQLが「使わない」と判断するケースがあります。
代表的なのは、次のようなパターンです。

条件がゆるすぎて、ほとんど全件がヒットする場合。
ソート順や条件が、インデックスの構造と相性が悪い場合。

例えば、orders.created_at にインデックスがある状態で、こういうクエリを投げるとします。

SELECT * FROM orders WHERE amount >= 0;
SQL

amount にインデックスがなければ、当然フルスキャンです。
amount にインデックスがあったとしても、「ほぼ全件が条件にマッチする」なら、
PostgreSQLは「インデックス経由よりフルスキャンの方が速い」と判断することがあります。

また、B-treeは「前方一致のLIKE」には強いですが、「前にワイルドカードが付くLIKE」には弱いです。

-- インデックスが効きやすい
WHERE email LIKE 'a%'

-- インデックスが効きにくい
WHERE email LIKE '%example.com'
SQL

前者は「ソート順を活かして、’a’ で始まる範囲」に飛べますが、
後者は「どこにあるか分からない」ので、B-treeの強みを活かしにくいのです。

ここでの学びは、「インデックスは万能ではない」「条件式の書き方次第で効いたり効かなかったりする」ということです。
（このあたりは、後の回でEXPLAINを使って詳しく見ていきます。）

PostgreSQLでインデックスを“設計の一部”として扱う

「テーブル定義＋制約＋インデックス＝DB設計」

ここまで来ると、PostgreSQLのテーブル定義は、だんだんこう見えてきます。

カラム定義：どんなデータを持つか。
制約（NOT NULL / CHECK / UNIQUE / 外部キー）：どんなデータしか許さないか。
インデックス：どんなクエリを速くしたいか。

SQLiteやMySQLのときは、「とりあえずテーブル作って、必要になったらインデックスを足す」という感覚だったかもしれません。
PostgreSQLでは、「よく使うクエリを想像しながら、最初からインデックスも含めて設計する」スタイルが相性がいいです。

例えば、さっきのordersテーブルなら、こういう設計が“最初からの完成形”に近いです。

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  amount     INTEGER NOT NULL CHECK (amount >= 0),
  created_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
SQL

制約で「おかしなデータを入れない」。
インデックスで「よく使う検索を速くする」。

この2つをセットで考えると、「壊れにくくて速いDB設計」に近づいていきます。

Day6 後半のまとめ

B-treeインデックスは、「キーと行へのポインタをソート済みの木構造として持つ」仕組みで、WHERE email = '...' のような検索ではルートノードから枝をたどって目的の値の位置にジャンプし、WHERE created_at BETWEEN ... のような範囲検索では「範囲の先頭に飛んでから必要な範囲だけ葉ノードをなめる」ことで、大量データでも少ないステップで目的の行にたどり着く。
一方で、インデックスはINSERT/UPDATE/DELETEのたびに木構造を更新する必要があるため、「インデックスの数だけ書き込みコストが増える」という裏側の負担があり、amount >= 0 のようにほぼ全件ヒットする条件や LIKE '%xxx' のようにソート順を活かせない条件では、PostgreSQLが「インデックスを使わずフルスキャンした方が速い」と判断することもある。
だからこそ、「よくWHEREに出てくるカラム（email, user_id, created_atなど）にだけB-treeインデックスを張り、制約でデータの正しさを守りつつ、インデックスで“よく使うクエリ”を速くする」という発想で、テーブル定義＋制約＋インデックスをセットで設計していく――これが Day6 後半で身につけたいインデックス基礎の感覚になる。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました