建立索引（`CREATE INDEX`）與索引參數（`UNIQUE`、`CONCURRENTLY`）

我們之前已經講過好幾次索引怎麼加速查詢，讓資料庫不用每次都全表掃描。現在就來搞懂索引到底怎麼建、CREATE INDEX 這個指令有什麼參數，還有什麼時候該用像 UNIQUE 或 CONCURRENTLY 這種選項。這些都很重要，因為你不只是要用索引，還要會聰明地管理它們。

CREATE INDEX 的語法

要建索引就用 CREATE INDEX 這個指令。基本語法長這樣：

CREATE INDEX index_name
ON table_name (column_name);

• index_name — 索引的名字。最好讓它能看出索引的用途，比如 idx_users_email 就是給 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 這種不能重複的 key 或其他識別欄位。

建立唯一索引的語法

唯一索引的語法跟一般索引差不多，只是多了 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);

PostgreSQL 會直接報錯，因為 email 必須唯一。

用 CONCURRENTLY 參數建立索引

想像一下你在 production 上有個超大的表，大家一直在寫入新資料。如果你用一般模式（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 有幾個特點：

1. 建立索引會比一般模式慢，因為 PostgreSQL 會分好幾個階段來做。
2. 如果建立索引時出錯（比如有重複資料），你得手動刪掉索引再重建。

其他索引參數

PostgreSQL 建索引時還可以加其他參數。比如你可以一次對多個欄位建索引。這很適合你常常用多個欄位一起查詢的時候。

CREATE INDEX idx_users_name_email
ON users (name, email);

這樣查詢 WHERE name = 'John' AND email = 'john@example.com' 就會快很多。

兩個單欄位索引跟一個多欄位索引是不一樣的！多欄位索引就是要加速你在 WHERE 裡同時查多個欄位的情境。

常見錯誤與解法

建立索引時有時會遇到一些錯誤。這裡列幾個最常見的：

建立唯一索引時插入重複值的錯誤。 如果表裡已經有重複的資料，PostgreSQL 就沒辦法建立唯一索引。這時你要先刪掉或修正重複的資料。

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

建立索引時的鎖表錯誤。 如果你在 production 用一般方式建索引，使用者可能會遇到延遲或錯誤。這時就該用 CONCURRENTLY 參數來避免這個問題。

想像你在公司負責優化一個有幾百萬筆資料的資料庫。你可以用索引找出瓶頸，讓使用者體驗大升級。比如加對索引，查詢時間從 10 秒變成幾毫秒，是不是超爽？