索引

索引是数据库中用于加速查询的重要结构。合理的索引设计可以大幅提升查询性能，但索引也会占用存储空间并降低写入速度。本章将详细介绍PostgreSQL中的索引类型和使用方法。

索引概述

什么是索引

索引类似于书籍的目录，可以帮助数据库快速定位数据，而不必扫描整个表。

sql

-- 创建示例表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(10, 2),
    stock INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO products (product_code, name, category, price, stock)
SELECT 
    'P' || LPAD(i::TEXT, 4, '0'),
    '产品' || i,
    CASE (i % 5)
        WHEN 0 THEN '电子产品'
        WHEN 1 THEN '服装'
        WHEN 2 THEN '食品'
        WHEN 3 THEN '家居'
        ELSE '其他'
    END,
    (RANDOM() * 10000)::NUMERIC(10, 2),
    (RANDOM() * 1000)::INTEGER
FROM generate_series(1, 10000) AS i;

没有索引的查询

sql

-- 查看没有索引时的查询计划
EXPLAIN ANALYZE
SELECT * FROM products WHERE product_code = 'P01234';

-- 结果会显示 Seq Scan（顺序扫描），即扫描整个表

创建索引

基本语法

sql

-- 创建基本索引
CREATE INDEX idx_products_product_code ON products(product_code);

-- 创建索引后再查看查询计划
EXPLAIN ANALYZE
SELECT * FROM products WHERE product_code = 'P01234';
-- 结果会显示 Index Scan（索引扫描）

-- 创建唯一索引
CREATE UNIQUE INDEX idx_products_unique_code ON products(product_code);

-- 创建索引（如果不存在）
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);

指定索引方法

sql

-- B-tree索引（默认，适用于等值、范围、排序查询）
CREATE INDEX idx_products_price_btree ON products USING btree(price);

-- Hash索引（只适用于等值查询，不常用）
CREATE INDEX idx_products_name_hash ON products USING hash(name);

-- GiST索引（适用于几何数据、全文搜索等）
CREATE INDEX idx_products_name_gist ON products USING gist(to_tsvector('simple', name));

-- GIN索引（适用于数组、JSON、全文搜索）
CREATE INDEX idx_products_category_gin ON products USING gin(to_tsvector('simple', category));

并发创建索引

sql

-- CONCURRENTLY：不阻塞表的读写操作创建索引
-- 适用于生产环境中的大表
CREATE INDEX CONCURRENTLY idx_products_created_at ON products(created_at);

-- 注意：CONCURRENTLY创建索引耗时更长，但不会锁表

索引类型

B-tree索引

B-tree是默认的索引类型，适用于大多数场景：

sql

-- B-tree索引支持的操作
-- 1. 等值查询：=, <, <=, >, >=
-- 2. 范围查询：BETWEEN, IN
-- 3. 排序：ORDER BY
-- 4. 前缀匹配：LIKE 'xxx%'

-- 创建B-tree索引
CREATE INDEX idx_products_price ON products(price);

-- 适用场景示例
SELECT * FROM products WHERE price = 100;           -- 等值
SELECT * FROM products WHERE price > 100;           -- 大于
SELECT * FROM products WHERE price BETWEEN 50 AND 100;  -- 范围
SELECT * FROM products ORDER BY price;              -- 排序
SELECT * FROM products WHERE product_code LIKE 'P01%';  -- 前缀匹配

Hash索引

Hash索引只支持等值查询：

sql

-- 创建Hash索引
CREATE INDEX idx_products_name_hash ON products USING hash(name);

-- 只适用于等值查询
SELECT * FROM products WHERE name = '产品100';

-- 不支持范围查询和排序
-- SELECT * FROM products WHERE name > '产品100';  -- 不会使用Hash索引

GiST索引

GiST（Generalized Search Tree）索引适用于复杂的数据类型：

sql

-- 创建包含几何数据的表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position POINT,
    area POLYGON
);

-- 创建GiST索引用于几何数据
CREATE INDEX idx_locations_position ON locations USING gist(position);
CREATE INDEX idx_locations_area ON locations USING gist(area);

-- 几何查询示例
SELECT * FROM locations 
WHERE position <@ circle '((0,0), 10)';  -- 在圆内的点

-- GiST也支持排除约束
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    during TSRANGE,
    EXCLUDE USING gist (
        room_id WITH =,
        during WITH &&
    )
);

GIN索引

GIN（Generalized Inverted Index）适用于包含多个元素的值：

sql

-- 创建包含数组的表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    tags TEXT[],
    metadata JSONB
);

-- 创建GIN索引用于数组
CREATE INDEX idx_articles_tags ON articles USING gin(tags);

-- 数组查询
SELECT * FROM articles WHERE tags @> ARRAY['技术', '数据库'];

-- 创建GIN索引用于JSONB
CREATE INDEX idx_articles_metadata ON articles USING gin(metadata);

-- JSONB查询
SELECT * FROM articles WHERE metadata @> '{"author": "张三"}';

-- 创建全文搜索索引
CREATE INDEX idx_articles_content_fts ON articles USING gin(to_tsvector('chinese', content));

-- 全文搜索
SELECT * FROM articles 
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '数据库 & 技术');

BRIN索引

BRIN（Block Range Index）适用于大型有序表：

sql

-- BRIN索引存储每块数据的摘要信息
-- 适用于按插入顺序存储的大表，如日志表

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    level VARCHAR(10),
    message TEXT
);

-- 创建BRIN索引（非常小的索引）
CREATE INDEX idx_logs_time_brin ON logs USING brin(log_time);

-- BRIN索引适用于范围查询
SELECT * FROM logs WHERE log_time BETWEEN '2024-01-01' AND '2024-01-31';

-- 指定每块的页数
CREATE INDEX idx_logs_time_brin_pages ON logs USING brin(log_time) WITH (pages_per_range = 128);

复合索引

复合索引是在多个列上创建的索引：

sql

-- 创建复合索引
CREATE INDEX idx_products_category_price ON products(category, price);

-- 复合索引的使用规则（最左前缀原则）
-- 会使用索引
SELECT * FROM products WHERE category = '电子产品';
SELECT * FROM products WHERE category = '电子产品' AND price > 100;
SELECT * FROM products WHERE category = '电子产品' ORDER BY price;

-- 不会使用索引（缺少最左列）
SELECT * FROM products WHERE price > 100;

-- 部分使用索引
SELECT * FROM products WHERE category = '电子产品' AND name = '产品100';
-- 只会使用索引的category部分

-- 创建包含更多列的复合索引
CREATE INDEX idx_products_cat_price_stock ON products(category, price DESC, stock);

部分索引

部分索引只对满足条件的行建立索引：

sql

-- 只对活跃产品创建索引
CREATE INDEX idx_products_active ON products(price) 
WHERE stock > 0;

-- 只对高价产品创建索引
CREATE INDEX idx_products_high_value ON products(product_code) 
WHERE price > 5000;

-- 查询时自动使用部分索引
SELECT * FROM products WHERE price > 5000 AND product_code = 'P01234';

-- 部分索引可以减小索引大小，提高写入性能

表达式索引

表达式索引对计算结果建立索引：

sql

-- 对函数结果创建索引
CREATE INDEX idx_products_name_lower ON products(LOWER(name));

-- 使用时必须使用相同的表达式
SELECT * FROM products WHERE LOWER(name) = '产品100';

-- 对日期部分创建索引
CREATE INDEX idx_products_created_year ON products(EXTRACT(YEAR FROM created_at));

-- 查询时使用
SELECT * FROM products WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- 对JSONB字段创建表达式索引
CREATE INDEX idx_products_metadata_author ON products((metadata->>'author'));

-- 使用
SELECT * FROM products WHERE metadata->>'author' = '张三';

覆盖索引

覆盖索引包含查询所需的所有列，避免回表：

sql

-- 创建包含额外列的索引
CREATE INDEX idx_products_category_covering ON products(category) INCLUDE (name, price);

-- 这个查询可以直接从索引获取所有数据
SELECT category, name, price 
FROM products 
WHERE category = '电子产品';

-- 查看是否使用了仅索引扫描
EXPLAIN ANALYZE
SELECT category, name, price 
FROM products 
WHERE category = '电子产品';
-- 结果应显示 "Index Only Scan"

管理索引

查看索引

sql

-- 查看表的所有索引
\di products

-- 查看索引详情
\d+ products

-- 使用SQL查询索引信息
SELECT 
    indexname AS 索引名称,
    indexdef AS 索引定义
FROM pg_indexes
WHERE tablename = 'products';

-- 查看索引大小
SELECT 
    indexrelname AS 索引名称,
    pg_size_pretty(pg_relation_size(indexrelid)) AS 索引大小
FROM pg_stat_user_indexes
WHERE relname = 'products';

-- 查看索引使用统计
SELECT 
    indexrelname AS 索引名称,
    idx_scan AS 扫描次数,
    idx_tup_read AS 读取元组数,
    idx_tup_fetch AS 获取元组数
FROM pg_stat_user_indexes
WHERE relname = 'products'
ORDER BY idx_scan DESC;

重建索引

sql

-- 重建单个索引
REINDEX INDEX idx_products_price;

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

-- 重建数据库的所有索引
REINDEX DATABASE mydb;

-- 并发重建索引（不阻塞读写）
REINDEX INDEX CONCURRENTLY idx_products_price;
REINDEX TABLE CONCURRENTLY products;

删除索引

sql

-- 删除索引
DROP INDEX idx_products_price;

-- 如果存在则删除
DROP INDEX IF EXISTS idx_products_price;

-- 同时删除多个索引
DROP INDEX idx_products_price, idx_products_category;

索引与查询优化

使用EXPLAIN分析

sql

-- 查看查询计划
EXPLAIN SELECT * FROM products WHERE category = '电子产品';

-- 查看实际执行统计
EXPLAIN ANALYZE SELECT * FROM products WHERE category = '电子产品';

-- 查看详细统计
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT * FROM products WHERE category = '电子产品';

-- 强制使用索引
SET enable_seqscan = OFF;
EXPLAIN SELECT * FROM products WHERE category = '电子产品';
SET enable_seqscan = ON;  -- 恢复默认

-- 强制不使用索引
SET enable_indexscan = OFF;
EXPLAIN SELECT * FROM products WHERE category = '电子产品';
SET enable_indexscan = ON;  -- 恢复默认

索引失效的场景

sql

-- 1. 对索引列使用函数
-- 不会使用索引
SELECT * FROM products WHERE UPPER(name) = '产品100';
-- 解决方案：创建表达式索引
CREATE INDEX idx_products_name_upper ON products(UPPER(name));

-- 2. 隐式类型转换
-- 不会使用索引（product_code是VARCHAR）
SELECT * FROM products WHERE product_code = 123;
-- 正确写法
SELECT * FROM products WHERE product_code = '123';

-- 3. 使用OR连接不同列
-- 可能不会使用索引
SELECT * FROM products WHERE category = '电子产品' OR price > 1000;
-- 解决方案：使用UNION
SELECT * FROM products WHERE category = '电子产品'
UNION
SELECT * FROM products WHERE price > 1000;

-- 4. 使用NOT、!=、<>操作符
-- 可能不使用索引
SELECT * FROM products WHERE category != '电子产品';
-- 可以改写为
SELECT * FROM products WHERE category IN ('服装', '食品', '家居', '其他');

-- 5. LIKE以通配符开头
-- 不会使用索引
SELECT * FROM products WHERE name LIKE '%产品%';
-- 可以使用全文搜索或pg_trgm扩展

-- 安装pg_trgm扩展
CREATE EXTENSION pg_trgm;
-- 创建pg_trgm索引
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
-- 现在可以使用索引
SELECT * FROM products WHERE name LIKE '%产品%';

索引使用建议

sql

-- 1. 为经常查询的列创建索引
-- 查看哪些查询频繁执行
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- 2. 为外键创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 3. 避免过度索引
-- 查看未使用的索引
SELECT 
    schemaname || '.' || relname AS 表,
    indexrelname AS 索引,
    pg_size_pretty(pg_relation_size(indexrelid)) AS 大小,
    idx_scan AS 使用次数
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. 定期分析表统计信息
ANALYZE products;

-- 5. 考虑索引的维护成本
-- 索引会降低INSERT、UPDATE、DELETE的速度
-- 查看表的写操作统计
SELECT 
    relname AS 表名,
    n_tup_ins AS 插入次数,
    n_tup_upd AS 更新次数,
    n_tup_del AS 删除次数
FROM pg_stat_user_tables
ORDER BY n_tup_ins + n_tup_upd + n_tup_del DESC;

索引最佳实践

选择合适的索引类型

数据类型/场景	推荐索引类型
等值、范围、排序查询	B-tree
仅等值查询	Hash（较少使用）
几何数据、范围重叠	GiST
数组、JSONB、全文搜索	GIN
大型有序表的时间序列	BRIN

索引设计原则

sql

-- 1. 选择性高的列更适合索引
-- 选择性 = 不同值的数量 / 总行数
SELECT 
    category,
    COUNT(DISTINCT category)::FLOAT / COUNT(*) AS selectivity
FROM products
GROUP BY category;

-- 2. 复合索引的列顺序
-- 将选择性高的列放在前面
-- 将等值查询的列放在范围查询的列前面
CREATE INDEX idx_products_cat_price ON products(category, price);
-- category用于等值查询，price用于范围查询

-- 3. 使用部分索引减少索引大小
CREATE INDEX idx_products_in_stock ON products(product_code) 
WHERE stock > 0;

-- 4. 使用INCLUDE避免回表
CREATE INDEX idx_products_cat_name_price ON products(category) 
INCLUDE (name, price);

-- 5. 监控索引使用情况
SELECT 
    schemaname,
    relname AS 表名,
    indexrelname AS 索引名,
    idx_scan AS 使用次数,
    pg_size_pretty(pg_relation_size(indexrelid)) AS 索引大小
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

小结

本章介绍了PostgreSQL索引的主要内容：

内容	说明
B-tree索引	默认索引，适用于大多数场景
Hash索引	仅适用于等值查询
GiST索引	适用于几何数据、全文搜索
GIN索引	适用于数组、JSONB、全文搜索
BRIN索引	适用于大型有序表
复合索引	多列索引，遵循最左前缀原则
部分索引	只对部分行建立索引
表达式索引	对计算结果建立索引
覆盖索引	包含查询所需的所有列

合理使用索引是数据库性能优化的关键，下一章我们将学习视图的使用。

索引 ​

索引概述 ​

什么是索引 ​

没有索引的查询 ​

创建索引 ​

基本语法 ​

指定索引方法 ​

并发创建索引 ​

索引类型 ​

B-tree索引 ​

Hash索引 ​

GiST索引 ​

GIN索引 ​

BRIN索引 ​

复合索引 ​

部分索引 ​

表达式索引 ​

覆盖索引 ​

管理索引 ​

查看索引 ​

重建索引 ​

删除索引 ​

索引与查询优化 ​

使用EXPLAIN分析 ​

索引失效的场景 ​

索引使用建议 ​

索引最佳实践 ​

选择合适的索引类型 ​

索引设计原则 ​

小结 ​

索引