【入門編】 B-treeインデックスの最適化 – PostgreSQL

こんにちは！データベースの世界へようこそ。

普段、何気なくSQLを書いていると、「なぜか最近、検索が遅いな……」なんて悩むこと、ありますよね。そんな時、僕たちデータベースエンジニアが真っ先にチェックするのが「インデックス」です。

今日は、PostgreSQLの標準装備である「B-tree（ビーツリー）インデックス」について、難しい専門用語はなるべく抜きにして、お話ししてみようと思います。

—

データベースにインデックスを貼るというのは、「分厚い本の巻末に索引を作る」ことと全く同じです。

もし索引がない本から特定のキーワードを探そうと思ったら、最初から最後まで全ページをめくるしかありませんよね。これって、データベースでいうところの「全件スキャン（フルスキャン）」です。データが数件ならいいですが、数百万件あったら日が暮れてしまいます。

そこで登場するのが「B-treeインデックス」です。これは、僕たちが辞書を引くときのように、「大まかな範囲で絞り込んで、目的のページにたどり着く」ための賢い仕組みなんです。

B-treeインデックスのすごいところは、「どれだけデータが増えても、たどり着くまでの手間がほとんど変わらない」という点です。

これを「木の深さ」と呼びます。
例えば、100万件のデータがあっても、B-treeなら数回の枝分かれをたどるだけで、お目当てのデータに到達できます。イメージとしては、こんな感じです。

このように、階層を降りていくだけで、膨大なデータの中から一瞬で目的の場所にたどり着ける。これが、B-treeが「等価検索（＝で探す）」や「範囲検索（＞や＜で探す）」に抜群に強い理由なんです。

さて、ここからが今日一番伝えたい「プロのコツ」です。
複数の項目を組み合わせてインデックスを貼る（複合インデックス）とき、「順番」がめちゃくちゃ重要だって知っていましたか？

例えば、「苗字」と「名前」でインデックスを作るとします。
このとき、以下のどちらがいいと思いますか？

1. （苗字, 名前）の順で並べる
2. （名前, 苗字）の順で並べる

答えは、「左側に、より絞り込みが効くものを置く」のが正解です。

日常に例えるなら、住所を探すときに「番地」から探すよりも、「都道府県」→「市町村」→「町名」という順で絞り込むほうが圧倒的に早いのと同じです。

もし「苗字」で検索することが多いのに、インデックスを（名前, 苗字）で作ってしまうと、データベースは「名前」という大きな括りから探し始めないといけません。これだと、せっかく作った索引もあまり役に立たず、宝の持ち腐れになってしまうんです。

インデックスは、ただ作ればいいというものではありません。
むやみにたくさん作ると、今度はデータの更新（追加や削除）をするたびに索引を書き直さないといけないので、逆にシステムが重くなってしまいます。

「どんな検索が一番多いかな？」
「この検索には、どの列を左側に持ってきたら一番効率がいいかな？」

そんな風に、ユーザーがどう使うかを想像しながらインデックスを設計するのは、まるで庭木の手入れをするようで、実はとってもクリエイティブで楽しい作業なんですよ。

まずは皆さんのデータベースで、一番よく使う検索クエリを見つけてみてください。そして、そのクエリが「どの列を使って絞り込んでいるか」を意識することから始めてみましょう。

きっと、データベースの反応が劇的に変わるはずです！それでは、また次の記事でお会いしましょう。