PostgreSQL 索引详解

一、索引基础概念

1.1 什么是索引

索引是数据库中用于加速数据检索的数据结构。它类似于书籍的目录，通过索引可以快速定位到目标数据，而不需要扫描整个表。

1.2 PostgreSQL 索引类型概览

PostgreSQL 提供了六种内置索引类型，每种都有其特定的使用场景：

| 索引类型 | 适用场景 | 支持的操作符 |
| ----------- | ---------------------------- | -------------------------------------------- | ---- |
| B-Tree | 范围查询、排序、等值查询 | <, <=, =, >=, >, BETWEEN, IN, LIKE 'prefix%' |
| Hash | 等值查询 | = |
| GiST | 几何数据、全文搜索、范围类型 | @>, <@, &&, @@ |
| SP-GiST | 非平衡数据结构 | <<, >>, ~= |
| GIN | 数组、JSONB、全文搜索 | @>, ?, ?&, ? | , @@ |
| BRIN | 大表、物理有序数据 | <, <=, =, >=, > |

1.3 索引的基本操作

-- 创建索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_users_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- 创建部分索引（条件索引）
CREATE INDEX idx_users_active ON users(email) WHERE status = 1;

-- 创建表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 并发创建索引（不阻塞写入）
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

-- 查看索引
\di
\di+ users

-- 查看索引定义
SELECT indexdef FROM pg_indexes WHERE tablename = 'users';

-- 删除索引
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- 不阻塞

-- 重建索引
REINDEX INDEX idx_users_email;
REINDEX TABLE users;
REINDEX DATABASE mydb;

二、B-Tree 索引

2.1 B-Tree 原理

B-Tree（平衡树）是 PostgreSQL 的默认索引类型，也是最常用的索引类型。

2.2 B-Tree 适用场景

-- 等值查询
SELECT * FROM users WHERE id = 100;
SELECT * FROM users WHERE email = 'test@example.com';

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE created_at >= '2024-01-01';
SELECT * FROM users WHERE salary > 10000;

-- 排序
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 前缀匹配
SELECT * FROM users WHERE name LIKE 'John%';  -- 可以使用索引
SELECT * FROM users WHERE name LIKE '%John';  -- 无法使用索引

-- MIN/MAX 优化
SELECT MIN(created_at) FROM users;  -- 直接取索引边界值
SELECT MAX(id) FROM users;

-- NULL 值处理
SELECT * FROM users WHERE deleted_at IS NULL;  -- 可以使用索引
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;

2.3 复合索引

-- 复合索引（多列索引）
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- 最左前缀原则
-- 以下查询可以使用该索引：
SELECT * FROM users WHERE status = 1;  -- ✓
SELECT * FROM users WHERE status = 1 AND created_at > '2024-01-01';  -- ✓
SELECT * FROM users WHERE status = 1 ORDER BY created_at;  -- ✓

-- 以下查询无法使用该索引：
SELECT * FROM users WHERE created_at > '2024-01-01';  -- ✗ 没有 status
SELECT * FROM users ORDER BY created_at;  -- ✗ 没有 status

-- 索引列顺序的影响
-- 原则：高选择性的列放在前面
-- 如果 status 只有 3 个值，email 有 100 万个不同值
CREATE INDEX idx_bad ON users(status, email);   -- 不好
CREATE INDEX idx_good ON users(email, status);  -- 更好

-- 覆盖索引（Index-Only Scan）
-- 索引包含查询所需的所有列，无需回表
CREATE INDEX idx_users_covering ON users(status, created_at, email);

SELECT email FROM users WHERE status = 1 ORDER BY created_at LIMIT 10;
-- 可能触发 Index-Only Scan

2.4 索引选择性

-- 选择性 = 不同值的数量 / 总行数
-- 选择性越高，索引效果越好

-- 查看列的选择性
SELECT
    attname,
    n_distinct,
    CASE
        WHEN n_distinct > 0 THEN n_distinct
        ELSE n_distinct * -1 * reltuples
    END AS distinct_values,
    reltuples AS total_rows
FROM pg_stats
JOIN pg_class ON tablename = relname
WHERE tablename = 'users'
AND schemaname = 'public';

-- 高选择性列（适合建索引）
-- email（唯一）、id（唯一）、phone（唯一）

-- 低选择性列（可能不适合单独建索引）
-- status（只有几个值）、gender（只有2个值）、is_active（布尔）

-- 但低选择性列可以作为部分索引的条件
CREATE INDEX idx_users_active ON users(email) WHERE status = 1;

三、Hash 索引

3.1 Hash 索引原理

Hash 索引使用哈希函数将键值映射到桶中，只支持等值查询。

3.2 Hash 索引使用

-- 创建 Hash 索引
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- 适用场景
SELECT * FROM users WHERE email = 'test@example.com';  -- ✓

-- 不适用场景
SELECT * FROM users WHERE email LIKE 'test%';  -- ✗
SELECT * FROM users WHERE email > 'a';  -- ✗
SELECT * FROM users ORDER BY email;  -- ✗

-- Hash vs B-Tree 性能对比
-- 对于等值查询，Hash 理论上更快（O(1) vs O(log n)）
-- 但实际差距不大，B-Tree 更通用

-- 什么时候用 Hash 索引？
-- 1. 只有等值查询
-- 2. 列值很长（如长文本、UUID）
-- 3. 数据分布均匀

四、GiST 索引

4.1 GiST 索引原理

GiST（Generalized Search Tree，通用搜索树）是一个索引框架，支持多种数据类型和查询操作。

4.2 GiST 索引使用场景

-- 1. 几何数据查询
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    position POINT
);

CREATE INDEX idx_locations_position ON locations USING GIST (position);

-- 查找某点附近的位置
SELECT * FROM locations
WHERE position <-> POINT(116.4, 39.9) < 0.1
ORDER BY position <-> POINT(116.4, 39.9);

-- 2. 范围类型查询
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    during TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- 查找重叠的预订
SELECT * FROM reservations
WHERE during && '[2024-01-15 14:00, 2024-01-15 16:00)';

-- 3. 全文搜索
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    tsv TSVECTOR
);

CREATE INDEX idx_articles_tsv ON articles USING GIST (tsv);

-- 全文搜索（GIN 索引通常更好，但 GiST 支持更多操作）
SELECT * FROM articles WHERE tsv @@ to_tsquery('postgresql & index');

-- 4. 网络地址查询
CREATE TABLE ip_ranges (
    id SERIAL PRIMARY KEY,
    network INET
);

CREATE INDEX idx_ip_network ON ip_ranges USING GIST (network inet_ops);

SELECT * FROM ip_ranges WHERE network >> '192.168.1.100';

五、GIN 索引

5.1 GIN 索引原理

GIN（Generalized Inverted Index，通用倒排索引）特别适合包含多个元素的值（数组、JSONB、全文搜索）。

5.2 GIN 索引使用场景

-- 1. 数组类型
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 包含查询
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
-- 重叠查询
SELECT * FROM articles WHERE tags && ARRAY['java', 'python'];

-- 2. JSONB 类型
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 包含查询
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
-- 键存在查询
SELECT * FROM products WHERE attributes ? 'price';
SELECT * FROM products WHERE attributes ?& ARRAY['color', 'size'];

-- 针对特定路径的 GIN 索引
CREATE INDEX idx_products_tags ON products USING GIN ((attributes->'tags'));

-- 3. 全文搜索
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    tsv TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(body, '')), 'B')
    ) STORED
);

CREATE INDEX idx_documents_tsv ON documents USING GIN (tsv);

-- 全文搜索
SELECT * FROM documents
WHERE tsv @@ to_tsquery('english', 'postgresql & performance');

-- 4. 三元组（pg_trgm 扩展）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- 模糊查询（支持 LIKE '%keyword%'）
SELECT * FROM users WHERE name LIKE '%john%';
SELECT * FROM users WHERE name ILIKE '%john%';
SELECT * FROM users WHERE name % 'john';  -- 相似度匹配

5.3 GIN vs GiST 选择

特性	GIN	GiST
构建速度	慢	快
查询速度	快	较慢
更新速度	慢	快
索引大小	大	小
适用场景	静态数据、高查询	动态数据、写多读少

-- GIN 优化参数
-- 延迟更新（提高写入性能）
ALTER INDEX idx_products_attrs SET (fastupdate = on);
-- 挂起列表大小
ALTER INDEX idx_products_attrs SET (gin_pending_list_limit = 4096);

六、BRIN 索引

6.1 BRIN 索引原理

BRIN（Block Range Index，块范围索引）是一种轻量级索引，适用于物理有序的大表。

6.2 BRIN 索引使用

-- 创建 BRIN 索引
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 指定每个范围包含的页数（默认 128）
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at)
    WITH (pages_per_range = 64);

-- 适用场景
-- 1. 日志表（按时间顺序插入）
CREATE TABLE access_logs (
    id BIGSERIAL,
    ip INET,
    path TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_access_logs_created ON access_logs USING BRIN (created_at);

-- 2. 传感器数据
CREATE TABLE sensor_data (
    id BIGSERIAL,
    sensor_id INTEGER,
    value NUMERIC,
    recorded_at TIMESTAMPTZ
);

CREATE INDEX idx_sensor_recorded ON sensor_data USING BRIN (recorded_at);

-- 3. 分区表的每个分区
CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE INDEX idx_logs_2024_01_created ON logs_2024_01 USING BRIN (created_at);

-- 查询时间范围
SELECT COUNT(*) FROM access_logs
WHERE created_at BETWEEN '2024-01-15' AND '2024-01-16';

-- BRIN vs B-Tree 对比
-- 假设 1 亿条日志记录：
-- B-Tree 索引大小：约 2-3 GB
-- BRIN 索引大小：约 100-200 KB

6.3 BRIN 索引维护

-- 查看 BRIN 索引摘要
CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT * FROM brin_page_items(
    get_raw_page('idx_logs_created', 2),
    'idx_logs_created'
);

-- 手动更新摘要（新插入的数据）
SELECT brin_summarize_new_values('idx_logs_created');

-- 去除无效范围
SELECT brin_desummarize_range('idx_logs_created', 128);
SELECT brin_summarize_range('idx_logs_created', 128);

七、SP-GiST 索引

7.1 SP-GiST 索引原理

SP-GiST（Space-Partitioned Generalized Search Tree）适用于非平衡数据结构，如四叉树、kd-树、基数树。

-- 适用场景：地理数据、IP 地址、文本前缀

-- 1. 地理数据（点）
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    location POINT
);

CREATE INDEX idx_points_location ON points USING SPGIST (location);

-- 2. 范围类型
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    during INT4RANGE
);

CREATE INDEX idx_reservations_during ON reservations USING SPGIST (during);

-- 3. 文本前缀（电话号码、URL）
CREATE TABLE phones (
    id SERIAL PRIMARY KEY,
    number TEXT
);

CREATE INDEX idx_phones_number ON phones USING SPGIST (number);

-- 前缀查询
SELECT * FROM phones WHERE number ^@ '138';  -- 以 138 开头

八、索引优化策略

8.1 判断索引是否被使用

-- 查看查询执行计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

-- 查看索引使用统计
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,           -- 索引扫描次数
    idx_tup_read,       -- 通过索引读取的行数
    idx_tup_fetch       -- 通过索引获取的行数
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY idx_scan DESC;

-- 查看未使用的索引
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
    AND indisunique IS FALSE
ORDER BY pg_relation_size(i.indexrelid) DESC;

8.2 索引失效场景

-- 1. 对索引列使用函数
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';  -- 无法使用索引
-- 解决：创建表达式索引
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 2. 隐式类型转换
SELECT * FROM users WHERE id = '100';  -- id 是 INTEGER，但输入是字符串
-- 解决：使用正确的类型
SELECT * FROM users WHERE id = 100;

-- 3. LIKE 以通配符开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 无法使用 B-Tree 索引
-- 解决：使用 pg_trgm + GIN 索引
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);

-- 4. 否定条件
SELECT * FROM users WHERE status != 1;  -- 可能不使用索引
SELECT * FROM users WHERE status NOT IN (1, 2);  -- 可能不使用索引

-- 5. OR 条件
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '13800000000';
-- 解决：确保两列都有索引，或使用 UNION
SELECT * FROM users WHERE email = 'a@b.com'
UNION
SELECT * FROM users WHERE phone = '13800000000';

-- 6. 数据分布不均
-- 如果 99% 的数据 status = 1，查询 status = 1 可能全表扫描更快
SELECT * FROM users WHERE status = 1;  -- 优化器可能选择全表扫描

8.3 索引设计原则

-- 1. 为高频查询创建索引
-- 分析慢查询日志，找出高频 WHERE 条件

-- 2. 考虑复合索引的列顺序
-- 高选择性列在前，范围条件列在后
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at);

-- 3. 使用部分索引减小索引大小
-- 只索引需要查询的数据子集
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- 4. 使用覆盖索引避免回表
-- 包含查询需要的所有列
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name, status);

-- 5. 定期维护索引
-- 重建膨胀的索引
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 查看索引膨胀
SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

8.4 索引监控与维护

-- 创建索引监控视图
CREATE VIEW v_index_stats AS
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    CASE WHEN idx_scan > 0
        THEN round(idx_tup_read::numeric / idx_scan, 2)
        ELSE 0
    END AS avg_tuples_per_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 定期重建索引（低峰期执行）
DO $$
DECLARE
    idx RECORD;
BEGIN
    FOR idx IN
        SELECT indexrelid::regclass AS index_name
        FROM pg_stat_user_indexes
        WHERE schemaname = 'public'
    LOOP
        EXECUTE 'REINDEX INDEX CONCURRENTLY ' || idx.index_name;
    END LOOP;
END $$;

-- 自动更新统计信息
ANALYZE users;
ANALYZE VERBOSE users;

九、总结

索引类型选择指南

场景	推荐索引
等值/范围查询	B-Tree（默认）
纯等值查询、长键值	Hash
数组包含查询	GIN
JSONB 查询	GIN
全文搜索	GIN（查询多）/ GiST（更新多）
几何/地理数据	GiST / SP-GiST
范围类型	GiST
时序大表	BRIN
前缀匹配	SP-GiST

索引优化要点

1. 选择合适的索引类型：根据数据类型和查询模式选择
2. 设计复合索引：遵循最左前缀原则，高选择性列在前
3. 使用部分索引：只索引常用数据子集
4. 避免过度索引：索引会降低写入性能
5. 定期维护：REINDEX 重建膨胀索引，ANALYZE 更新统计信息
6. 监控索引使用：删除未使用的索引