CREATE INDEXの使い方
インデックスが効く仕組みは分かっても、どこに作るかを決めないと手が動きません。全部の列に作るのがいちばんやってはいけない選択で、遅いクエリを 1 本決めて、それに合わせて 1 本作るのが基本の進め方です。
遅いクエリを 1 本選ぶところから始める
インデックスは「あると良さそうだから」では作りません。実行が遅いクエリのうち、呼ばれる回数が多いものから手を付けます。1 回 3 秒でも月に 1 度しか動かない集計より、1 回 0.3 秒でも 1 日 10 万回動く一覧のほうが、直したときの効き目は大きくなります。
対象が決まったら、その WHERE と ORDER BY を見ます。
SQL クエリ
SELECT id, ordered_at FROM orders
WHERE user_id = 42
ORDER BY ordered_at DESC
LIMIT 20;絞り込みに使っているのは user_id、並べ替えに使っているのは ordered_at です。この 2 つがそのままインデックスの材料になります。
書くのは、名前とテーブルと列だけ
構文は 1 行です。
SQL クエリ
CREATE INDEX idx_orders_user_id ON orders (user_id);名前は自由に付けられますが、実行計画やトラブル時のログにそのまま出てくるので、どのテーブルのどの列かが読める形にしておきます。列は複数並べられます。
SQL クエリ
CREATE INDEX idx_orders_user_ordered ON orders (user_id, ordered_at DESC);並べる順番は、等価条件で絞る列を先、範囲指定や並べ替えに使う列を後ろにします。先ほどのクエリなら user_id が先、ordered_at が後ろです。重複そのものを禁止したいなら UNIQUE を付けます。制約とインデックスを 1 回で用意できます。
SQL クエリ
CREATE UNIQUE INDEX uq_users_email ON users (email);本番では、作っている間テーブルが止まる
ここが実務でいちばん怖いところです。大きなテーブルにインデックスを作ると、作成中は書き込みがロックされます。行数によっては数分から数時間かかり、その間ユーザーは注文も登録もできません。
PostgreSQL には CONCURRENTLY があり、ロックを取らずに作れます。代わりに完了までの時間は普通より長くなります。
SQL クエリ
CREATE INDEX CONCURRENTLY idx_orders_ordered_at ON orders (ordered_at);
-- 要らなくなったら消す
DROP INDEX idx_orders_ordered_at;どちらを選ぶにしても、本番と同じ行数を用意した検証環境で所要時間を計ってから当てます。
コマンド自体は 1 行でも、本番に当てる判断には行数の確認と時間の計測が要ります。ここを飛ばして深夜に書き込みを止めてしまうのが、いちばんよくある事故です。
テーブル構造
CREATE TABLE query_log (
id INT PRIMARY KEY,
query_pattern VARCHAR(200) NOT NULL,
exec_count INT NOT NULL,
avg_ms INT NOT NULL
);
INSERT INTO query_log VALUES
(1, 'SELECT * FROM orders WHERE user_id = ? ORDER BY ordered_at DESC', 50000, 250),
(2, 'SELECT * FROM products WHERE category_id = ?', 80000, 30),
(3, 'SELECT * FROM users WHERE email = ?', 100000, 5),
(4, 'SELECT COUNT(*) FROM orders WHERE status = ?', 200, 1200),
(5, 'SELECT * FROM reviews WHERE product_id = ? AND rating >= ?', 30000, 180);期待される出力
| id | query_pattern | total_load |
|---|---|---|
| 1 | SELECT * FROM orders WHERE user_id = ? ORDER BY ordered_at DESC | 12500000 |
| 5 | SELECT * FROM reviews WHERE product_id = ? AND rating >= ? | 5400000 |
| 2 | SELECT * FROM products WHERE category_id = ? | 2400000 |