当前位置：首页 > java >正文

PostgreSQL 索引大全

java 2025/9/2 6:50:19

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';