PostgreSQL 索引大全

1. 创建索引的基本语法

单列索引

-- 基本B-tree索引（最常用）
CREATE INDEX index_name ON table_name (column_name);

-- 示例
CREATE INDEX idx_users_email ON users (email);

多列复合索引

CREATE INDEX index_name ON table_name (col1, col2, col3);

-- 示例
CREATE INDEX idx_users_name_email ON users (last_name, first_name, email);

2. 不同类型的索引

B-tree 索引（默认）

-- 默认就是B-tree索引
CREATE INDEX idx_products_price ON products (price);

-- 显式指定
CREATE INDEX idx_products_price ON products USING btree (price);

哈希索引

-- 适用于等值查询
CREATE INDEX idx_users_email_hash ON users USING hash (email);

GiST 索引（空间数据）

-- 用于地理空间数据
CREATE INDEX idx_locations_geom ON locations USING gist (geom);

GIN 索引（数组、JSON、全文搜索）

-- 用于数组类型
CREATE INDEX idx_products_tags ON products USING gin (tags);

-- 用于JSONB
CREATE INDEX idx_users_profile ON users USING gin (profile);

-- 用于全文搜索
CREATE INDEX idx_documents_content ON documents USING gin (to_tsvector('english', content));

BRIN 索引（大数据量）

-- 适用于按时间顺序排列的大表
CREATE INDEX idx_logs_timestamp ON logs USING brin (timestamp);

3. 唯一索引

-- 确保列值的唯一性
CREATE UNIQUE INDEX idx_users_username_unique ON users (username);

-- 多列唯一索引
CREATE UNIQUE INDEX idx_user_emails_unique ON user_emails (user_id, email);

4. 部分索引（条件索引）

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

-- 只为非空值创建索引
CREATE INDEX idx_users_phone_not_null ON users (phone) WHERE phone IS NOT NULL;

5. 表达式索引

-- 基于表达式的索引
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 日期部分索引
CREATE INDEX idx_orders_order_date ON orders (EXTRACT(YEAR FROM order_date));

6. 并发创建索引（不锁表）

-- 在生产环境中使用，避免锁表
CREATE INDEX CONCURRENTLY idx_products_name ON products (name);

7. 查看索引信息

-- 查看表的索引
SELECT * FROM pg_indexes WHERE tablename = 'your_table';

-- 查看索引大小和统计信息
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes 
WHERE tablename = 'your_table';

8. 删除索引

-- 删除索引
DROP INDEX index_name;

-- 示例
DROP INDEX idx_users_email;

9. 索引最佳实践

选择合适的列创建索引

-- 为经常在WHERE、JOIN、ORDER BY中使用的列创建索引
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

避免过度索引

        不要为每个列都创建索引，索引会占用空间并影响写性能

使用复合索引

-- 多个查询条件使用复合索引更高效
CREATE INDEX idx_users_search ON users (last_name, first_name, city);

监控索引使用情况

-- 查看未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

10. 完整示例

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true
);

-- 创建各种索引
-- 主键自动创建索引
CREATE UNIQUE INDEX idx_users_username ON users (username);
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_name ON users (last_name, first_name);
CREATE INDEX idx_users_active ON users (is_active) WHERE is_active = true;
CREATE INDEX idx_users_created_desc ON users (created_at DESC);
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 查看所有索引
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

注意事项

1. 写性能影响：索引会降低INSERT、UPDATE、DELETE的速度

2. 存储空间：索引需要额外的磁盘空间

3. 维护成本：需要定期监控和优化索引

4. 查询优化器：PostgreSQL会自动选择最合适的索引

5. 统计信息：确保ANALYZE定期运行以更新统计信息

通过合理使用索引，可以显著提高PostgreSQL数据库的查询性能。