0Pricing
SQL Interview Prep · レッスン

インデックスが逆効果になる場合：書き込みと選択性

書き込み増幅と、選択性の低い列へのインデックスが役に立たない理由を学びます。

「インデックスが逆効果になる場合：書き込みと選択性」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

質問の裏にある質問

インデックスが役立つ理由を3つのレッスンで学んだ後、面接官は反対の質問をします。「すべての列にインデックスを作ればよいのでは？」優れた候補者は、インデックスには書き込みやキャッシュとストレージに関する実際のコストがあり、プランナーがまったく使用しないインデックスもあると説明できます。

このレッスンでは、インデックスが害になる大きな2つの理由、書き込み増幅と選択性の低さを扱います。

すべてのインデックスは書き込みを遅くする

インデックスはテーブルと同期していなければなりません。すべてのINSERT、すべてのDELETE、そしてインデックス対象の列に対するすべてのUPDATEで、インデックス構造も更新する必要があります。これが書き込み増幅です。1回の行変更が、テーブルへの1回の書き込みに加え、影響を受けるインデックスごとの書き込みになります。

8つのインデックスを持つテーブルでは、インデックスのないテーブルの約9倍の書き込み処理が必要になります。書き込み主体または高スループットのテーブルでは、これは大きな負担です。

実例：書き込みコスト

1秒あたり数千行を取り込むイベントテーブルを想像してください。インデックスを1つ追加するたびに、すべての挿入処理でページ分割、リーフの更新、キャッシュの競合が発生し、より多くの処理が必要になります。

追記専用で書き込み主体のテーブルでは、主キー以外のインデックスをほとんど、またはまったく作らず、代わりにレプリカやデータウェアハウスで重い読み取りを行うのが適切な答えになることがよくあります。

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

選択性とは

選択性とは、列が行をどれだけ区別できるか、つまり一般的な値に一致する行の割合です。選択性が高い場合、1つの値に一致する行は少なくなります（メールアドレスやUUIDなど）。選択性が低い場合、1つの値に一致する行は多くなります（ブール値や3種類の選択肢しかないステータスなど）。

インデックスは、検索によってほとんどの行を除外できる選択性の高い列で効果を発揮します。選択性の低い列では、効果を発揮しないことがよくあります。

選択性の低いインデックスが役に立たない理由

is_activeがユーザーの90%でtrueだとします。インデックス検索ではテーブルの90%が返されることになり、その数の行に対してはエンジンが1行ごとにヒープフェッチを行うため、テーブルを1回順にスキャンするより遅くなります。

そのため、プランナーはインデックスを正しく無視して、シーケンシャルスキャンを実行します。結果として、そのインデックスは書き込みのオーバーヘッドとストレージだけを消費し、読み取りにはまったくメリットをもたらしません。

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

おおよその境界

口頭で説明する際に役立つ経験則があります。述語がテーブルの5～20%を超える行に一致する場合、通常はインデックススキャンよりシーケンシャルスキャンの方が優れています。ランダムなヒープフェッチは、ページを順に読み取るよりコストが高いためです。

正確な境界は、行のサイズ、キャッシュ、ストレージの速度によって異なります。そのため、プランナーは固定値ではなく統計情報を使って判断します。

部分インデックスによる解決

偏りのある列について、値の少ないものだけを常にクエリするのであれば、部分インデックス（Postgres）を使ってその行だけにインデックスを作成できます。小さく、選択性が高く、保守コストも低くなります。

注文の1%がpendingで、その注文を頻繁にクエリするとします。その場合は、その注文だけにインデックスを作成します。インデックスは小さいまま保たれ、プランナーも喜んで使用します。

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

古い統計情報がプランナーを誤らせる

オプティマイザは、列の統計情報をもとにインデックススキャンとシーケンシャルスキャンのどちらを使うか決定します。バルクロードや大規模な更新の後などに統計情報が古くなっていると、選択性を誤って判断し、間違った実行計画を選ぶことがあります。

面接官から「インデックスは存在するのに使われない」と言われた場合は、インデックス自体を責める前に、ANALYZEで統計情報を更新するという回答を含めるとよいでしょう。

ANALYZE orders;  -- refresh planner statistics

インデックスが招くその他の問題

あまり知られていないコストも加えて、回答を完成させましょう。

  • ストレージとキャッシュ：インデックスはディスクを占有し、メモリを取り合うため、有用なデータページを追い出します。
  • 冗長または重複するインデックス：保守されるものの、選択されることはありません。
  • 肥大化：大量の更新によってB-Treeが断片化し、REINDEXが必要になります。
  • オプティマイザの混乱：似たインデックスが多すぎると、プランニングが遅くなり、予測しにくくなります。

未使用インデックスを見つける

実際の環境での整理を正当化するには、Postgresがインデックスの使用状況を追跡していることに触れましょう。idx_scan = 0のインデックスは削除候補です。読み取りに一度も使われないまま、書き込みと領域のコストだけが発生しているためです。

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

面接での答え方

完全でバランスの取れたまとめは、次のようになります。

「インデックスには書き込み増幅のコストがあり、INSERT、UPDATE、DELETEのたびに保守が必要になるほか、ストレージとキャッシュにも負荷がかかります。インデックスが効果を発揮するのは選択性の高い述語に対してだけです。ほとんどの行が一致する列では、プランナーは正しくシーケンシャルスキャンを選ぶため、そのインデックスは純粋なオーバーヘッドになります。偏りのある列には部分インデックスを使い、ANALYZEで統計情報を最新に保ち、未使用のインデックスを削除します。」

クイックチェック

コストに見合う可能性が最も低いインデックスを判断してください。

振り返り：インデックスが問題になる場合

重要なポイント：

  • すべてのインデックスは書き込み増幅に加え、ストレージとキャッシュのコストを生みます。
  • インデックスは選択性の高い列で役立ちます。選択性の低い列では、プランナーはシーケンシャルスキャンを選びます。
  • 一致する行が全体の5～20%を超える場合、通常はスキャンが優先されます。
  • 偏りのある列について、値の少ないものだけをクエリする場合は部分インデックスを使用します。
  • ANALYZEで統計情報を最新に保ち、未使用のインデックス（idx_scan = 0）を削除します。

これでインデックス戦略のコースは完了です。インデックスが効果を発揮する場所に作成し、実行計画でその効果を証明してください。

よくある質問

「インデックスが逆効果になる場合：書き込みと選択性」レッスンは無料ですか？

はい。「インデックスが逆効果になる場合：書き込みと選択性」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「インデックスが逆効果になる場合：書き込みと選択性」で何を学びますか？

書き込み増幅と、選択性の低い列へのインデックスが役に立たない理由を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「インデックスが逆効果になる場合：書き込みと選択性」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. B-Treeインデックスとその効果
  2. 複合インデックスの列順
  3. カバリングインデックスとIndex-Only Scan
  4. インデックスが逆効果になる場合：書き込みと選択性
← SQL Interview Prepに戻る