はじめに
あるタスクで検索画面のパフォーマンス改善を頼まれました。
ユーザーの検索条件によってDBから該当の情報を表示する、よくある検索画面です。ただ、この情報を表示するまでに数十分かかることがあり、それを改善するというタスクでした。
今回はその改善で学んだことをまとめます。
パフォーマンス改善の前提
パフォーマンスの問題は、だいたいDBがネックになっています。つまり、SQL文の実行に時間がかかっているケースがほとんどです。
また、検証する際は本番環境と同等のデータ量を用意する必要があります。100件のデータでは速くても、100万件になると急に遅くなることがあるためです。
パフォーマンスが悪化する要因
主な要因は以下の2つです。
データ件数の増加
システムを長く運用していると、データは増え続けます。リリース当初は問題なくても、数年後に遅くなるケースは珍しくありません。
監査証跡や各種ログ出力による負荷
誰がいつ何をしたかを記録する監査ログは、運用上必要なものです。ただ、これらの書き込み処理がDBに負荷をかけ、検索が遅くなることがあります。
主なチューニング手法
チューニングの方法はいくつかあります。
- インデックスの利用
- テーブル結合方式の変更
- SQL文の書き換え
今回はインデックスの利用によって改善できたので、そこにフォーカスします。
インデックスとは
インデックスは、本の「索引」と同じ仕組みです。
500ページの本から特定の単語を探すとき、索引がなければ1ページ目から順にめくる必要があります。索引があれば「○○ → 120ページ」とすぐにわかります。
DBも同じです。インデックスがなければ全件を1行ずつチェックします（これを全件スキャンと呼びます）。インデックスがあれば、目的のデータに直接アクセスできます。
たとえば400万件のテーブルで全件スキャンが走ると、それだけで数分〜数十分かかることがあります。インデックスを使えば、これが数秒で終わることもあります。
インデックスの作り方
例として、以下のような住民情報を管理するテーブルがあるとします。
CREATE TABLE residents (
id SERIAL PRIMARY KEY,
resident_id VARCHAR(20), -- 住民ID
name VARCHAR(100), -- 氏名
birth_date DATE, -- 生年月日
address TEXT, -- 住所
created_at TIMESTAMP, -- 登録日時
deleted_at TIMESTAMP -- 削除日時
);
このテーブルに対して「住民IDで検索する」処理が頻繁に行われるとします。
SELECT * FROM residents WHERE resident_id = '12345';
この検索を高速化するために、resident_idカラムにインデックスを作成します。
CREATE INDEX idx_resident_id ON residents (resident_id);
idx_resident_idはインデックスの名前です。idx_のようなプレフィックスを付けておくと、後から見たときにインデックスだと分かりやすくなります。
既存のインデックスを確認するには以下のSQLを使います。
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'residents';
不要になったインデックスはDROP INDEXで削除できます。
DROP INDEX idx_resident_id;
インデックスはディスク容量を消費し、INSERT/UPDATE時に更新コストがかかります。そのため、よく検索に使うカラムに絞って作成するのがポイントです。
おわりに
インデックスを適切に設定することで、検索速度は大きく改善します。
ただし、インデックスを作っただけでは効かないケースもあります。本当に使われているかは「実行計画」で確認できるので、作成後は必ず確認することをおすすめします。