インデックス設計の注意点
遅い一覧を直すためにインデックスを 4 本足したら、今度は注文の登録が遅くなった。よくある展開です。インデックスは読み取りを速くする代わりに、書き込みを必ず遅くします。付ければ速くなる道具ではなく、読み取りと書き込みを交換する道具です。
1 行入れるたびに、5 か所へ書いている
インデックスは本体とは別に持っている見出しの一覧です。本体に 1 行増えたら、見出しの側にも並び順を保ったまま 1 件足す必要があります。
SQL クエリ
-- orders に 4 本のインデックスがあると、
-- この 1 行の INSERT で本体 1 回 + インデックス 4 回、合わせて 5 か所を書く
INSERT INTO orders (user_id, status, ordered_at) VALUES (42, 'paid', NOW());UPDATE と DELETE も同じです。ストレージも増えます。インデックスは元テーブルの 1 割から 3 割ほどの大きさになるので、5 本張ればテーブル全体は 1.5 倍から 2.5 倍に膨らみ、バックアップにかかる時間もそのぶん延びます。
読み取りが速くなる効果は測らないと分かりませんが、書き込みの遅さとディスクの消費は毎日確実に払い続けます。
性別のような列に付けても、読み取りすら速くならない
値の種類が少ない列は、見出しを引いても候補がほとんど絞れません。
SQL クエリ
CREATE INDEX idx_users_is_active ON users (is_active);
SELECT id, name FROM users WHERE is_active = TRUE;is_active が真の行が全体の 9 割を占めるなら、見出しを引いてから本体を 90 万行読むより、最初から全件読むほうが速くなります。データベースはそう判断してインデックスを使いません。作った本数だけ書き込みが遅くなって、見返りはゼロです。
性別、都道府県、ステータスのように取りうる値が数個しかない列は、単体では作らないのが原則です。使うなら、値の種類が多い列を先頭に置いた複合インデックスの一部として入れます。
列の順番を間違えると、まるごと無駄になる
複合インデックスは前の列から順にしか使えません。(user_id, ordered_at) は user_id だけの検索にも効きますが、ordered_at だけの検索には効きません。順番を入れ替えると、効く相手も入れ替わります。作ったのに一度も使われないインデックスは、たいていこの読み違いから生まれます。
この性質は、要らない 1 本を見つけるのにも使えます。
SQL クエリ
-- 同じテーブルに、この 3 本が残っている
-- idx_orders_user (user_id)
-- idx_orders_user_status (user_id, status)
-- idx_orders_status_user (status, user_id)
DROP INDEX idx_orders_user;1 本目は 2 本目の先頭部分と同じなので、消しても検索は変わりません。書き込みの負担だけが減ります。3 本目は先頭が違うので別物ですが、status の値が数種類しかないなら、これも消して困らない可能性が高い 1 本です。
半年ほど運用したら、どのインデックスが何回使われたかを確認して、使われていないものを落とします。作るときの判断より、消すときの判断のほうが根拠を集めやすくなります。
テーブル構造
CREATE TABLE index_usage_stats (
schema_name VARCHAR(50) NOT NULL,
table_name VARCHAR(50) NOT NULL,
index_name VARCHAR(50) NOT NULL,
use_count BIGINT NOT NULL,
size_mb DECIMAL(10, 2) NOT NULL
);
INSERT INTO index_usage_stats VALUES
('app', 'orders', 'idx_orders_user_ordered', 1500000, 120.50),
('app', 'orders', 'idx_orders_user_only', 0, 80.20),
('app', 'orders', 'idx_orders_status_only', 5, 60.10),
('app', 'users', 'uq_users_email', 800000, 30.00),
('app', 'users', 'idx_users_is_active', 0, 25.50),
('app', 'products', 'idx_products_category', 200000, 15.00),
('app', 'reviews', 'idx_reviews_old', 0, 45.80);期待される出力
| table_name | index_name | use_count | size_mb |
|---|---|---|---|
| orders | idx_orders_user_only | 0 | 80.20 |
| orders | idx_orders_status_only | 5 | 60.10 |
| reviews | idx_reviews_old | 0 | 45.80 |
| users | idx_users_is_active | 0 | 25.50 |