PostgreSQL 索引

1. 什么是索引？为什么要用索引？

索引 = 加速 WHERE、JOIN、ORDER BY、GROUP BY 的结构

本质是一个数据结构（通常是 B-Tree），可以通过二分查找快速定位行。

有索引 -> 查少量行（logN）
没索引 -> 扫描整张表（O(N)）

2. PostgreSQL 支持哪些索引？

类型	适用场景	是否常用
B-Tree（默认）	查询、排序、范围查询（=、>、<、BETWEEN、LIKE ‘abc%’）	⭐⭐⭐⭐⭐ 最常用
Hash	等值查询 (=)	⭐（一般用 B-Tree 替代）
GIN	JSONB、全文检索、数组 @>	⭐⭐⭐⭐
GiST	地理位置、全文检索、模糊匹配	⭐⭐⭐
BRIN	大型按时间顺序的表（亿级数据）	⭐⭐⭐⭐⭐ 大库神器
SP-GiST	T-Tree、QuadTree 等特殊场景	⭐

3. B-Tree 索引（最常用）

创建普通索引

CREATE INDEX idx_users_email ON users(email);

查询使用 B-Tree 索引的情况

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

排序也会使用索引

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

范围查询使用索引

SELECT * FROM orders WHERE price > 100;

4. B-Tree 支持的 LIKE

⭐ 前缀匹配支持（能走索引）：

SELECT * FROM user WHERE name LIKE 'abc%';

❌ 包含匹配（不能走索引）

LIKE '%abc%'

可使用 trigram 扩展（见后面）。

5. 多列索引（注意顺序！！）

CREATE INDEX idx_user_status_created ON users(status, created_at);

✅ 合适查询：

WHERE status = 1
WHERE status = 1 AND created_at > now() - interval '1 day'

❌ 不合适查询（不会走索引）：

WHERE created_at > now()        -- status 没用到，索引失效

多列索引必须按顺序使用。

6. 覆盖索引（Index Only Scan）

索引包含查询需要的所有字段 -> 不需要访问表 -> 提升性能 10 倍。

CREATE INDEX idx_users_email_only ON users(email);
SELECT email FROM users WHERE email = 'a@b.com';

7. 唯一索引（Unique Index）

自动确保唯一性 + 快速查找。

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

等同于：

email TEXT UNIQUE

8. 表达式索引（非常强）

例如你的需求：TRIM(name) 去空格查询

CREATE INDEX idx_trim_name ON ashare (trim(name));

查询：

SELECT * FROM ashare WHERE trim(name) = '平安银行';

表达式必须一模一样，索引才会生效。

9. 局部索引（Partial Index）

适合大表，只给常查询的数据建索引（减少空间 + 加速）。

示例：只索引未删除的数据

CREATE INDEX idx_active_user ON users(email) WHERE deleted = false;

查询：

WHERE deleted = false AND email = 'xxx'

10. JSONB 索引（GIN）

大 JSON 表，超强索引。

JSONB 包含查询

CREATE INDEX idx_json_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> '["python"]';

JSONB 键查询

CREATE INDEX idx_json_data ON users USING GIN (data);
SELECT * FROM users WHERE data->>'city' = 'Beijing';

11. Trigram（模糊搜索索引）

支持 LIKE ‘%xxx%’ 效率巨大提升。

安装扩展：

CREATE EXTENSION pg_trgm;

创建索引：

CREATE INDEX idx_trgm_name ON ashare USING GIN (name gin_trgm_ops);

查询：

SELECT * FROM ashare WHERE name LIKE '%银行%';

性能提升 100 倍。

12. BRIN 索引（海量数据用）

适用于：

时间序列
自增 id
上亿条数据

示例：

CREATE INDEX idx_brin_log_created ON logs USING BRIN(created_at);

优点：

占用极小（通常只有 KB 级）
查询范围连续的数据非常快
插入速度接近无索引

新闻、时间序列分析等场景非常适用。

13. 索引什么时候不会生效？

非常重要！

❌ WHERE 不符合索引规则
❌ 类型不一致
❌ 使用函数但无表达式索引
❌ 进行计算（例如 price * 2 = 100）
❌ 在 OR 两边都没有索引
❌ LIKE ‘%xxx%’（未使用 trigram）

14. 如何查看执行计划（非常重要）

EXPLAIN ANALYZE
SELECT * FROM users WHERE id = 10;

重点关注：

Index Scan（走索引）
Seq Scan（全表扫描）
Index Only Scan（最优）
Bitmap Heap Scan（中间状态）

15. 什么时候需要索引？（实战建议）

✅ 业务常用的字段
✅ WHERE 中出现的字段
✅ JOIN 关联字段
✅ ORDER BY 排序字段
✅ GROUP BY 字段
✅ 唯一性约束字段
✅ 高频查询的 JSONB 字段
❌ 不常用字段不要建索引
❌ DML 高表不要随便增加过多索引（影响写入）

16. 实战：你的场景索引推荐

你当前的数据模型（ashare, plate_data, news 等），推荐：

1. 股票 code 是主键 -> 必须索引

CREATE UNIQUE INDEX idx_ashare_code ON ashare(code);

2. 按日期查询

CREATE INDEX idx_up_resaon_date ON up_resaon(date);

3. code + date 查询

CREATE INDEX idx_up_resaon_code_date ON up_resaon(code, date);

4. 前缀 name 查询

如果要支持模糊搜索：

CREATE INDEX idx_trgm_ashare_name ON ashare USING GIN (name gin_trgm_ops);