インデックスが検索を速くしてくれて、DBが全部を総なめしなくて済むって話は何度かしてきたよね。じゃあ実際どうやって作るのか、CREATE INDEXコマンドにどんなパラメータがあるのか、UNIQUEやCONCURRENTLYみたいなオプションはどんな時に使うべきか、ここでしっかり押さえておこう。インデックスを「使うだけ」じゃなくて、「ちゃんと管理」したいなら大事なポイントだよ。
CREATE INDEXの構文
インデックスはCREATE INDEXコマンドで作れる。基本の構文はこんな感じ：
CREATE INDEX index_name
ON table_name (column_name);
index_name— インデックスの名前。できればインデックスの用途が分かる名前にしよう。例えばusersテーブルのemailカラム用ならidx_users_emailとか。table_name— インデックスを作るテーブルの名前。column_name— インデックスを貼るカラム。
簡単な例を出すね。例えばusersテーブルがあるとする：
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
age INT
);
emailでユーザー検索を速くしたいとき、インデックスを作る：
CREATE INDEX idx_users_email
ON users (email);
これで、例えばこんなクエリ：
SELECT * FROM users WHERE email = 'example@example.com';
PostgreSQLはidx_users_emailインデックスを使って、サクッと該当行を見つけてくれるよ。
ユニークインデックス（UNIQUE）
ユニークインデックスは、指定したカラム（またはカラムの組み合わせ）の値が必ずユニーク（重複なし）になることを保証してくれる。もし重複した値を入れようとしたら、PostgreSQLがエラーを出して止めてくれるよ。
ユニークインデックスは、emailやusernameみたいな、絶対に被っちゃいけない識別子によく使う。
ユニークインデックス作成の構文
ユニークインデックスの作り方は普通のインデックスとほぼ同じだけど、UNIQUEキーワードを追加するだけ：
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
例えば、usersテーブルのemailは絶対にユニークにしたい場合、こうする：
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);
これで、例えば：
INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);
みたいに同じemailでINSERTしようとすると、PostgreSQLがエラーを投げてくれる。
CONCURRENTLYパラメータ付きインデックス作成
例えば、めっちゃでかいテーブルが本番環境にあって、常にINSERTやUPDATEが走ってるとする。普通にCREATE INDEXすると、そのテーブルがロックされて、他のクエリがINSERT/UPDATE/DELETEできなくなっちゃう。これ、本番だとかなりヤバいよね。そんな時は、CONCURRENTLYパラメータを使って「非同期」でインデックスを作れる。
構文
CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);
CONCURRENTLYキーワードを付けると、PostgreSQLはテーブルをロックせずに並行してインデックスを作ってくれる。
例えば、ordersテーブルがあって、何百万件もデータがあって、どんどん新しい注文が追加されてるとする：
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_number VARCHAR(50) NOT NULL,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
order_dateで検索を速くしたいけど、テーブルをロックしたくない場合：
CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);
これでテーブルをロックせずにインデックスが作られて、ユーザーは何も気づかずに使い続けられる。
CONCURRENTLYの注意点：
- 普通に作るよりちょっと遅い。PostgreSQLが何段階かに分けて作業するから。
- もしエラー（例えば重複データとか）があったら、手動でインデックスを消して作り直す必要がある。
その他のインデックス作成パラメータ
PostgreSQLでは、インデックス作成時に他にもいろんなパラメータを追加できる。例えば、複数カラムを同時にインデックス化することも可能。これは、複数カラムでよく検索する場合に便利だよ。
CREATE INDEX idx_users_name_email
ON users (name, email);
これで、WHERE name = 'John' AND email = 'john@example.com'みたいなクエリが速くなる。
1カラムずつインデックスを作るのと、複数カラムで1つのインデックスを作るのは全然違う！複数カラムのインデックスは、WHEREでその全部のカラムを使う検索が速くなるんだ。
エラー例とその対処法
インデックス作成時にはいろんなエラーに出くわすことがある。よくあるやつを紹介するね：
ユニークインデックス作成時の重複エラー。 もしテーブルにすでに重複行があったら、PostgreSQLはユニークインデックスを作れない。この場合、まず重複を消すか修正しよう。
DELETE FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(email) > 1
);
インデックス作成時のロックエラー。 普通のインデックス作成を本番DBでやると、クライアントが遅延やエラーに遭遇することがある。CONCURRENTLYパラメータを使って回避しよう。
例えば、君が会社で何百万件もあるDBの最適化を任されたとする。インデックスを使えば、ボトルネックを見つけてユーザー体験を爆速にできる。正しいインデックスを追加するだけで、クエリの実行時間が10秒から数ミリ秒に短縮できることも。これ、めっちゃカッコよくない？
GO TO FULL VERSION