在 PostgreSQL 中添加索引是提升查询性能的常用手段。下面是创建索引的完整指南，涵盖最常见的场景和语法。

---

基础语法

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

示例：

-- 在 employees 表的 last_name 列上创建索引
CREATE INDEX idx_employees_last_name ON employees (last_name);

---

常用索引类型

1. 普通 B-tree 索引（默认）

适用于等值查询和范围查询（=、>、<、BETWEEN、LIKE（非前导通配符））：

CREATE INDEX idx_employees_salary ON employees (salary);

2. 唯一索引

确保列值唯一，同时提升查询性能：

CREATE UNIQUE INDEX idx_employees_email ON employees (email);

3. 多列索引（复合索引）

针对多个列的查询条件：

CREATE INDEX idx_employees_dept_salary ON employees (department_id, salary);

注意： 多列索引中，列的顺序很重要。通常将最常用于过滤的列放在前面。

4. 部分索引（条件索引）

只对表中满足条件的部分数据创建索引，节省空间：

-- 只为活跃员工（status = 'active'）创建索引
CREATE INDEX idx_employees_active ON employees (last_name)
WHERE status = 'active';

5. 表达式索引

对列的函数计算结果创建索引：

-- 对邮箱域名部分创建索引（忽略大小写）
CREATE INDEX idx_employees_email_domain ON employees (LOWER(SPLIT_PART(email, '@', 2)));

6. 哈希索引

仅适用于等值查询（=），比 B-tree 稍快，但功能受限：

CREATE INDEX idx_employees_status ON employees USING HASH (status);

---

高级选项

并发创建索引（不阻塞写入）

默认情况下，创建索引会锁表，阻止写入。使用 CONCURRENTLY 可以在创建索引时允许其他操作继续：

CREATE INDEX CONCURRENTLY idx_employees_name ON employees (first_name, last_name);

注意： 并发创建索引耗时更长，且可能会失败（需要清理后重试）。

指定索引表空间

将索引存储在不同的物理位置：

CREATE INDEX idx_employees_name ON employees (last_name) TABLESPACE fast_space;

设置填充因子

为索引页预留空间，减少页分裂（适用于频繁更新的列）：

CREATE INDEX idx_employees_name ON employees (last_name) WITH (FILLFACTOR = 90);

---

实际场景示例

场景1：为经常查询的列添加索引

-- 假设经常按 email 查询用户
CREATE INDEX idx_users_email ON users (email);

场景2：优化多条件查询

-- 查询：SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

场景3：提升排序性能

-- 经常按创建时间倒序查询
CREATE INDEX idx_orders_created_at_desc ON orders (created_at DESC);

场景4：加速全文搜索

-- 对文本列创建 GIN 索引，支持全文检索
CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));

场景5：JSONB 数据查询优化

-- 假设 data 是 JSONB 列，经常查询 data->>'name'
CREATE INDEX idx_customers_data_name ON customers ((data->>'name'));

---

查看和验证索引

查看表的所有索引

-- 在 psql 命令行中
\d 表名

-- 或查询系统表
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

检查查询是否使用索引

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

---

索引维护

删除索引

DROP INDEX idx_employees_name;

重命名索引

ALTER INDEX idx_old_name RENAME TO idx_new_name;

重建索引

-- 普通重建（会锁表）
REINDEX INDEX idx_employees_name;

-- 并发重建（不阻塞读写）
REINDEX INDEX CONCURRENTLY idx_employees_name;

---

索引设计原则

1. 不要过度索引：每个索引都会增加写操作的开销
2. 为高频查询创建索引：分析慢查询日志，找到真正的瓶颈
3. 考虑列的区分度：区分度高的列（如邮箱）适合索引，区分度低的列（如性别）通常不适合
4. 测试验证：在生产环境前，先在测试环境用真实数据量验证索引效果

---

快速参考表

索引类型	适用场景	示例
B-tree（默认）	大多数场景，=、>、<、BETWEEN	CREATE INDEX ... ON table (col);
UNIQUE	需要唯一约束的列	CREATE UNIQUE INDEX ... ON table (col);
多列索引	多条件查询	CREATE INDEX ... ON table (col1, col2);
部分索引	只索引部分行	CREATE INDEX ... ON table (col) WHERE condition;
表达式索引	对函数结果查询	CREATE INDEX ... ON table (LOWER(col));
HASH	仅等值查询	CREATE INDEX ... ON table USING HASH (col);
GIN	全文搜索、数组、JSONB	CREATE INDEX ... ON table USING GIN (col);
GiST	地理空间、全文搜索	CREATE INDEX ... ON table USING GiST (col);

如果你有具体的表结构和查询需求，可以告诉我，我可以帮你设计最优的索引方案。