1. 什么是索引？

索引（Index）是 PostgreSQL 加速查询性能 的一种数据结构，它类似于书的目录，可以 快速定位数据，减少 SELECT 语句的查询时间。

索引的优点：

• 提高查询性能：减少扫描的数据量，加速 SELECT 查询。
• 加快 ORDER BY 和 GROUP BY：索引可以优化排序和分组操作。
• 加速 JOIN：在多个表连接时，索引可以提高匹配效率。

索引的缺点：

• 占用额外存储空间：索引存储在磁盘上，可能会占用较大空间。
• 影响写操作（INSERT/UPDATE/DELETE）：索引需要维护，导致写操作变慢。

---

2. 创建索引

PostgreSQL 提供了多种索引类型，最常见的是 B-Tree 索引。

2.1 基本语法

CREATE INDEX index_name ON table_name (column_name);

• index_name：索引的名称
• table_name：要创建索引的表
• column_name：要索引的列

2.2 示例

CREATE INDEX idx_employee_name ON employees (first_name);

这样，在 employees 表的 first_name 列上创建了一个索引，提高 WHERE first_name = 'Alice' 的查询速度。

---

3. PostgreSQL 索引类型

PostgreSQL 支持多种索引类型，适用于不同的查询场景。

3.1 B-Tree 索引（默认）

• 适用于 等值查询 (=)、范围查询 (>, <, BETWEEN)。
• 适用于 ORDER BY 排序。

CREATE INDEX idx_salary ON employees (salary);

示例查询优化：

EXPLAIN ANALYZE
SELECT * FROM employees WHERE salary > 5000;

适用场景：

• 查询 WHERE column = value
• 查询 WHERE column > value
• 排序 ORDER BY column

---

3.2 哈希索引（Hash Index）

• 适用于 等值查询（=），比 B-Tree 索引更快，但不支持范围查询和排序。

CREATE INDEX idx_employee_id_hash ON employees USING HASH (emp_id);

示例查询优化：

SELECT * FROM employees WHERE emp_id = 10;

适用场景：

• 仅适用于 = 查询，不能用于范围查询 (>, <) 或 ORDER BY。

---

3.3 GIN 索引（全文搜索、JSONB、数组）

• 适用于 全文搜索（tsvector）、JSONB 查询、数组查询。

CREATE INDEX idx_description ON employees USING GIN (to_tsvector('english', description));

示例查询优化：

SELECT * FROM employees WHERE to_tsvector('english', description) @@ to_tsquery('developer');

适用场景：

• 全文搜索（tsvector 字段）
• JSONB 字段的快速查找
• 数组类型字段的高效查找

---

3.4 GiST 索引（地理空间、全文搜索）

• 适用于 地理空间数据（PostGIS）、全文搜索、范围查询。

CREATE INDEX idx_location ON locations USING GIST (coordinates);

示例查询优化：

SELECT * FROM locations WHERE coordinates <@ ST_MakeEnvelope(-75, 40, -74, 41, 4326);

适用场景：

• GIS 地理位置数据（PostGIS）
• 全文搜索
• 范围查询

---

3.5 BRIN 索引（适用于大表范围查询）

• 适用于 数据有序的大型表（如日志表、时间序列数据）。
• 存储空间小，但查询可能不如 B-Tree 精确。

CREATE INDEX idx_log_time ON logs USING BRIN (created_at);

示例查询优化：

SELECT * FROM logs WHERE created_at BETWEEN '2025-03-01' AND '2025-03-10';

适用场景：

• 时间戳排序的日志表
• 连续递增的 ID

---

4. 复合索引（多个列）

创建索引时可以指定多个列，适用于多列查询。

CREATE INDEX idx_employee_name_salary ON employees (last_name, first_name, salary);

示例查询优化：

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'Alice';

注意：

• 复合索引的 第一个列必须出现在查询条件中，否则索引可能不会生效。

---

5. 唯一索引

唯一索引（UNIQUE INDEX）确保列的值不重复。

CREATE UNIQUE INDEX idx_unique_email ON employees (email);

等价于：

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

示例查询优化：

SELECT * FROM employees WHERE email = 'alice@example.com';

适用场景：

• 电子邮件、用户名等需要 唯一性 的字段。

---

6. 部分索引

部分索引（Partial Index）仅对 符合特定条件的数据 建立索引，减少存储开销。

CREATE INDEX idx_high_salary ON employees (salary) WHERE salary > 10000;

示例查询优化：

SELECT * FROM employees WHERE salary > 10000;

适用场景：

• 仅索引 查询频率高的记录，减少索引体积。

---

7. 索引管理

7.1 查看表上的索引

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

7.2 删除索引

DROP INDEX idx_employee_name;

7.3 重建索引

REINDEX INDEX idx_employee_name;

7.4 分析索引（优化查询）

ANALYZE VERBOSE employees;

---

8. 索引优化建议

✅ 不要对小表建索引：PostgreSQL 可能会使用 顺序扫描（Seq Scan），反而更快。
✅ 避免过多索引：索引会影响 INSERT/UPDATE/DELETE 速度，尽量只建立 必要索引。
✅ 考虑索引选择：

• 等值查询 (=) → B-Tree/Hash
• 范围查询 (>、<) → B-Tree
• 全文搜索 → GIN
• 时间序列大表 → BRIN
• 地理数据 → GiST
  ✅ 使用 EXPLAIN ANALYZE 分析查询，确保索引被正确使用。

---

更多详细内容请关注其他相关文章！