postgresql怎样建索引

在 PostgreSQL 中，建立索引主要使用 CREATE INDEX 语句。下面按常见场景给你梳理一下。

一、基本语法

CREATE INDEX 索引名
ON 表名 (列名);

示例：

CREATE INDEX idx_user_name
ON users (name);

二、常见索引类型

1️⃣ B-tree 索引（默认，最常用）

适合 =, <, >, BETWEEN, ORDER BY

CREATE INDEX idx_users_age ON users (age);

2️⃣ 唯一索引（UNIQUE）

保证列值不重复

CREATE UNIQUE INDEX idx_user_email
ON users (email);

或在建表时：

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text UNIQUE
);

3️⃣ 多列联合索引

CREATE INDEX idx_users_name_age
ON users (name, age);

⚠️ 注意最左前缀原则：

• 能用到：(name)、(name, age)
• 用不到：(age)

4️⃣ 部分索引（Partial Index）

只对部分数据建索引，节省空间、提升性能

CREATE INDEX idx_active_users
ON users (name)
WHERE status = 'active';

5️⃣ 表达式索引

对函数或表达式建索引

CREATE INDEX idx_lower_email
ON users (lower(email));

查询时需匹配：

SELECT * FROM users WHERE lower(email) = 'a@b.com';

6️⃣ 全文检索索引

CREATE INDEX idx_article_content
ON articles
USING gin (to_tsvector('english', content));

7️⃣ GIN / GiST（适合数组、JSON、全文）

CREATE INDEX idx_tags
ON posts USING gin (tags);

三、并发建索引（不锁表）

生产环境推荐：

CREATE INDEX CONCURRENTLY idx_users_name
ON users (name);

⚠️ 注意：

• 不能在事务块中使用
• 失败会留下无效索引，需 DROP 后重建

四、查看 & 删除索引

查看索引：

\d 表名
-- 或
SELECT * FROM pg_indexes WHERE tablename='users';

删除索引：

DROP INDEX idx_users_name;

五、建索引建议

✅ 常用于 WHERE、JOIN、ORDER BY 的列
✅ 大表优先用 CONCURRENTLY
❌ 不要盲目建索引（影响写入性能）

如果你有具体表结构或查询 SQL，我可以帮你设计最合适的索引方案。