インデックスで速く探す

ある市役所の市民課では、申請のデータが30万件あります。

窓口で住民番号を入れると、画面が出るまで30秒かかります。後ろには列ができています。

システム担当の先輩が、ある列に「インデックス」を1つ作りました。すると、画面はすぐに出るようになりました。今日は、その仕組みを学びます。

分厚い業務マニュアルで「有給休暇」を探す場面を考えます。

巻末の索引がなければ、1ページ目から順にめくるしかありません。

データベースも同じです。インデックスがない列で探すと、全部の行を読みます。これをフルスキャンといいます。行が多いほど、時間がかかります。

インデックスは、列の値を順番に並べ、それぞれの行の場所を書いたものです。

並んでいるので、真ん中を見て「前か後ろか」を決められます。これをくり返すと、読む範囲が毎回半分になります。

30万件でも、およそ20回見るだけで目的の行に着きます。

EXPLAIN QUERY PLAN をクエリの前に付けると、データベースが探し方を教えてくれます。

SCAN と出たら、全部の行を読む予定です。SEARCH ... USING INDEX と出たら、インデックスを使う予定です。

遅いクエリを調べるとき、現場の担当者が最初に見るのがこの実行計画です。

-- 住民番号で申請を探します。
SELECT * FROM requests WHERE resident_no = 'R-10482';

-- データベースが、どう探すつもりかを聞きます。
-- SCAN は「全部の行を順に読む」という意味です。
EXPLAIN QUERY PLAN
SELECT * FROM requests WHERE resident_no = 'R-10482';

-- resident_no 列にインデックスを作ります。
CREATE INDEX idx_requests_resident ON requests (resident_no);

-- もう一度、探し方を聞きます。
-- SEARCH ... USING INDEX は「索引を使って探す」という意味です。
EXPLAIN QUERY PLAN
SELECT * FROM requests WHERE resident_no = 'R-10482';

-- WHERE の列を kind に変えると、探し方はどうなりますか。

インデックスは、ただではありません。

行を足したり変えたりするたびに、データベースはインデックスも直します。そのぶん、書き込みが遅くなります。保存する場所も増えます。

だから、よく探す列や、JOIN でつなぐ列にだけ作ります。例えば、住民番号や外部キーの列です。