PostgreSQL 索引原理

索引是什么

索引是数据的"目录"，用来快速定位数据，避免全表扫描。

-- 无索引：从头到尾一行行找
SELECT * FROM users WHERE email = 'test@example.com';  -- 顺序扫描

-- 有索引：直接定位
SELECT * FROM users WHERE email = 'test@example.com';  -- 索引扫描

PostgreSQL 索引类型

B-tree（默认）

原理：平衡多叉树，类似字典的拼音索引

适用场景：
- =, <, >, <=, >= 比较
- BETWEEN, IN
- LIKE 'abc%'（前缀匹配）
- IS NULL / IS NOT NULL

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);
CREATE INDEX idx_users_status_name ON users(status, name);

B-tree 结构：

        [15]
       /    \
    [5,10]  [20,25]
    / | \    / | \
  叶 子 节 点（数据页）

• 每个节点可以有多个子节点（度 d 的 B-tree）
• 所有叶子节点深度相同
• 插入/删除保持平衡

Hash

原理：把值哈希成一个数字，用数字作为目录

适用场景：
- = 比较
- 不支持范围查询

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Hash 结构：

Key: test@example.com
     ↓
Hash: 58784927
     ↓
Bucket[58784927 % 1024] → [指向数据行的指针]

GiST（通用搜索树）

原理：支持复杂数据类型的索引框架

适用场景：
- 几何数据（点、线、面、多边形）
- 全文搜索
- 范围查询
- 坐标数据

-- 几何类型索引
CREATE INDEX idx_locations ON locations USING GIST (position);

-- 范围类型索引
CREATE INDEX idx_reservations ON reservations USING GIST (daterange);

-- 全文搜索
CREATE INDEX idx_articles ON articles USING GIST (to_tsvector('zhcfg', content));

GIN（倒排索引）

原理：为每个值建立列表（倒排），适合多值列

适用场景：
- 数组包含查询
- JSONB 包含查询
- 全文搜索

-- 数组索引
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- JSONB 索引
CREATE INDEX idx_logs_data ON logs USING GIN (data);

-- 查询示例
SELECT * FROM products WHERE tags @> ARRAY['电子产品'];
SELECT * FROM logs WHERE data @> '{"level": "error"}';

BRIN（块范围索引）

原理：记录每个块范围的最小/最大值，适合物理顺序存储的数据

适用场景：
- 日志型数据（按时间顺序写入）
- 分区表
- 大表但查询通常是范围扫描

CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 比 B-tree 省 99% 空间
-- 扫描时跳过不包含目标值的块

BRIN vs B-tree：

特性	B-tree	BRIN
索引大小	较大	极小
插入性能	需要维护索引	几乎不影响
查询性能	O(log n)	取决于数据分布
适用场景	随机访问	顺序写入的大表

复合索引

多列索引

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

最左前缀原则：

-- 这个索引可以加速：
SELECT * FROM orders WHERE user_id = 1;                    -- ✓ 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid'; -- ✓ 使用索引

-- 这个索引无法使用：
SELECT * FROM orders WHERE status = 'paid';               -- ✗ 不使用索引

索引列顺序

考虑查询模式：

-- 如果经常这样查：
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

-- 索引顺序应该是 (user_id, status)
CREATE INDEX idx_orders ON orders(user_id, status);

-- 如果经常查 status：
SELECT * FROM orders WHERE status = 'paid';
CREATE INDEX idx_orders_status ON orders(status, user_id);

表达式索引

-- 在函数上建索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 使用
SELECT * FROM users WHERE LOWER(email) = LOWER('Test@Example.COM');

-- 在计算表达式上建索引
CREATE INDEX idx_orders_year ON orders(DATE_TRUNC('year', created_at));

部分索引

-- 只索引满足条件的行
CREATE INDEX idx_orders_pending ON orders(created_at) 
WHERE status = 'pending';

CREATE INDEX idx_users_active_email ON users(email) 
WHERE status = 'active' AND email IS NOT NULL;

优点：

• 索引更小
• 写入更快
• 可以建唯一索引（部分唯一）

唯一索引

CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 效果同 UNIQUE 约束

索引膨胀

原因

• 大量 UPDATE/DELETE 造成页面稀疏
• VACUUM 不能回收所有空间
• 索引页面碎片化

解决

-- 查看膨胀严重的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重建索引
REINDEX INDEX idx_name;

-- 或重建表的所有索引
REINDEX TABLE users;

索引创建建议

什么时候建索引

• WHERE 条件经常出现的列
• JOIN 连接的列
• ORDER BY 的列
• SELECT 中 DISTINCT 的列

什么时候不建

• 列基数很低（只有几个值，如性别）
• 表很小
• 查询需要返回大部分数据
• 频繁更新的列

查看索引使用情况

-- 查看查询是否使用索引
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 查看统计信息
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

索引类型对比

类型	适用场景	大小	查询性能	更新成本
B-tree	通用	中	O(log n)	中
Hash	等值查询	中	O(1)	中
GiST	几何/范围	大	较好	较大
GIN	数组/JSON	大	较好	大
BRIN	顺序大表	极小	取决于分布	无

多值列索引

数组列

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[]
);

CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 包含查询
SELECT * FROM products WHERE tags && ARRAY['电子产品', '手机'];

-- 包含精确匹配
SELECT * FROM products WHERE tags @> ARRAY['手机'];

-- 被包含查询
SELECT * FROM products WHERE tags <@ ARRAY['电子产品', '手机', '苹果'];

JSONB 列

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    data JSONB
);

CREATE INDEX idx_logs_data ON logs USING GIN (data);

-- 顶层键查询
SELECT * FROM logs WHERE data->>'level' = 'error';

-- 嵌套查询
SELECT * FROM logs WHERE data->'user'->>'name' = '张三';

-- 包含查询
SELECT * FROM logs WHERE data @> '{"level": "error"}';

-- 任意键存在
SELECT * FROM logs WHERE data ? 'trace_id';