创建索引（`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这种不能重复的标识符上。

创建唯一索引的语法

唯一索引的语法和普通索引差不多，只是多了个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参数创建索引

想象一下，你在生产环境有个超大表，大家不停地往里插数据。用普通模式（CREATE INDEX）建索引时，这个表会被锁住，其他操作（插入、更新、删除）都得等着。这对线上系统来说简直灾难。为了避免这种情况，可以用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
);

建索引时被锁表。 如果你在生产库用普通方式建索引，用户可能会遇到延迟甚至报错。用CONCURRENTLY参数就能避免这个问题。

想象一下，你在公司负责优化一个有几百万行的数据库。你可以用索引找出瓶颈，让用户体验飞起来。比如加对了索引，查询时间能从10秒缩到几毫秒。是不是很酷？