PostgreSQL 索引

索引是提升查询性能的关键技术。本章将详细介绍 PostgreSQL 中各种索引类型及其使用场景。

索引基础

什么是索引？

索引是一种特殊的数据结构，用于加速数据检索。就像书籍的目录，索引帮助数据库快速定位数据，而不需要扫描整个表。

创建测试表

-- 创建大量测试数据
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product_name VARCHAR(100),
    quantity INTEGER,
    price NUMERIC(10, 2),
    order_date DATE,
    status VARCHAR(20)
);

-- 插入 100 万条测试数据
INSERT INTO orders (customer_id, product_name, quantity, price, order_date, status)
SELECT 
    (random() * 10000)::INTEGER,
    'Product ' || (random() * 1000)::INTEGER,
    (random() * 10 + 1)::INTEGER,
    (random() * 1000 + 10)::NUMERIC(10,2),
    DATE '2023-01-01' + (random() * 365)::INTEGER,
    (ARRAY['pending', 'processing', 'shipped', 'delivered', 'cancelled'])[floor(random() * 5 + 1)::INTEGER]
FROM generate_series(1, 1000000);

索引类型

B-Tree 索引（默认）

B-Tree 是最常用的索引类型，适用于等值查询和范围查询：

-- 创建 B-Tree 索引（默认）
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);

-- 多列 B-Tree 索引
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 唯一索引
CREATE UNIQUE INDEX idx_orders_id_unique ON orders(id);

Hash 索引

Hash 索引适用于等值查询，但不支持范围查询：

-- 创建 Hash 索引
CREATE INDEX idx_orders_customer_hash ON orders USING HASH (customer_id);

GiST 索引

GiST 索引适用于地理空间数据、全文搜索等：

-- 安装 PostGIS 扩展（地理空间）
CREATE EXTENSION IF NOT EXISTS postgis;

-- 使用 GiST 索引进行地理空间查询
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position GEOGRAPHY(Point, 4326)
);

CREATE INDEX idx_locations_position ON locations USING GIST (position);

-- 范围查询
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_number VARCHAR(10),
    time_range TSRANGE
);

CREATE INDEX idx_reservations_time ON reservations USING GIST (time_range);

GIN 索引

GIN 索引适用于全文搜索、数组、JSONB 等：

-- 创建 GIN 索引用于全文搜索
ALTER TABLE orders ADD COLUMN full_text tsvector;

UPDATE orders SET full_text = 
    to_tsvector('english', product_name || ' ' || status);

CREATE INDEX idx_orders_fulltext ON orders USING GIN (full_text);

-- 数组索引
CREATE INDEX idx_orders_product_array ON orders USING GIN (product_name);

-- JSONB 索引
ALTER TABLE orders ADD COLUMN metadata JSONB;

CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata);

-- JSONB 路径索引
CREATE INDEX idx_orders_metadata_path ON orders USING GIN (metadata);

BRIN 索引

BRIN（Block Range Index）适用于按顺序存储的大表：

-- 创建 BRIN 索引（按数据块范围）
CREATE INDEX idx_orders_date_brin ON orders USING BRIN (order_date);

-- 按页面范围大小
CREATE INDEX idx_orders_date_brin ON orders USING BRIN (order_date) 
WITH (pages_per_range = 128);

索引选择指南

索引类型	使用场景
B-Tree	默认选择：等值查询、范围查询、排序
Hash	简单的等值查询
GiST	地理空间数据、范围类型、全文搜索
GIN	数组、JSONB、全文搜索
BRIN	按插入顺序存储的大表、时序数据

索引操作

查看索引

-- 查看表的所有索引
\d+ orders

-- 使用系统表查询
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- 查看索引大小
SELECT 
    pg_size_pretty(pg_relation_size('idx_orders_customer')) AS size;

删除索引

-- 删除索引
DROP INDEX idx_orders_customer;

-- 删除多个索引
DROP INDEX idx_orders_date, idx_orders_status;

-- CASCADE：删除索引及其依赖对象
DROP INDEX idx_orders_composite CASCADE;

重建索引

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

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

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

-- 并行重建索引（PostgreSQL 13+）
REINDEX INDEX CONCURRENTLY idx_orders_customer;

复合索引

多列索引规则

-- 创建复合索引
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

-- 索引列顺序很重要！

-- 这个查询能使用索引
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 10000;

-- 这个查询只能使用部分索引
SELECT * FROM employees 
WHERE salary > 10000;

-- 这个查询不能使用索引
SELECT * FROM employees 
WHERE department = '技术部'
ORDER BY salary;

覆盖索引

-- 创建覆盖索引（包含查询需要的列）
CREATE INDEX idx_orders_cover ON orders(customer_id, order_date) 
INCLUDE (product_name, price);

-- 使用覆盖索引，避免回表查询
SELECT customer_id, order_date, product_name, price
FROM orders
WHERE customer_id = 100
AND order_date > '2023-01-01';

部分索引

-- 只索引满足条件的行
CREATE INDEX idx_orders_active ON orders(customer_id) 
WHERE status = 'active';

CREATE INDEX idx_orders_high_value ON orders(customer_id) 
WHERE price > 1000;

-- 使用部分索引
SELECT * FROM orders WHERE status = 'active' AND customer_id = 100;

表达式索引

-- 基于表达式的索引
CREATE INDEX idx_orders_lower_product ON orders(LOWER(product_name));

CREATE INDEX idx_orders_date_year ON orders(EXTRACT(YEAR FROM order_date));

CREATE INDEX idx_orders_price_range ON orders((price * quantity));

-- 使用表达式索引
SELECT * FROM orders WHERE LOWER(product_name) = 'product 100';

索引使用分析

EXPLAIN 分析

-- 查看查询计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

-- 详细分析（实际执行）
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

-- 格式化输出
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE customer_id = 100;

索引命中率

-- 查看索引使用统计
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- 查看表访问统计
SELECT 
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE relname = 'orders';

识别未使用的索引

-- 查找未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
)
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找重复索引
SELECT 
    schemaname,
    tablename,
    indexname1,
    indexname2
FROM pg_indexes
WHERE (schemaname, tablename, indexdef) IN (
    SELECT schemaname, tablename, indexdef
    FROM pg_indexes
    GROUP BY schemaname, tablename, indexdef
    HAVING COUNT(*) > 1
);

索引性能优化

创建索引的最佳实践

-- 1. 为 WHERE 子句中的列创建索引
-- 经常用于过滤的列
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

-- 2. 为 ORDER BY 子句中的列创建索引
CREATE INDEX idx_orders_date ON orders(order_date);

-- 3. 为 JOIN 条件创建索引
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_products_id ON products(id);

-- 4. 考虑列的选择性
-- 高选择性：customer_id（唯一值多）
-- 低选择性：status（只有几个值）-- 不建议单独索引

-- 5. 使用复合索引的正确顺序
-- 经常一起查询的列放在一起
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

避免的索引误区

-- 1. 避免创建过多索引
-- 每个索引都会增加写操作的开销

-- 2. 避免在低选择性列上创建索引
-- 如：性别、状态等

-- 3. 注意索引列的数据类型
-- 确保数据类型匹配

-- 4. 考虑使用部分索引
CREATE INDEX idx_orders_recent ON orders(customer_id, order_date)
WHERE order_date > '2023-01-01';

小结

本章我们学习了 PostgreSQL 索引的各个方面：

1. 索引基础：理解索引的工作原理
2. 索引类型：B-Tree、Hash、GiST、GIN、BRIN
3. 索引操作：创建、查看、删除、重建
4. 复合索引：多列索引、覆盖索引、部分索引、表达式索引
5. 索引分析：使用 EXPLAIN 分析索引效果
6. 性能优化：索引最佳实践和常见误区

练习

1. 为订单表创建合适的索引
2. 分析查询计划，比较有无索引的性能差异
3. 创建部分索引，只索引活跃订单
4. 创建表达式索引，索引小写产品名称
5. 识别并删除未使用的索引