一、为什么需要索引？
想象一下，你有一本厚厚的电话簿，里面记录了上百万条联系人信息。如果没有目录，每次找人都要从第一页开始翻，这得多费劲啊！数据库也是一样，当表中数据量很大时，如果没有索引，查询就得全表扫描，效率极低。
PostgreSQL的索引就像书的目录，它能帮数据库快速定位到特定数据。举个例子，假设我们有个用户表，里面有1000万条记录：
-- 技术栈：PostgreSQL
-- 创建测试表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
created_at TIMESTAMP
);
-- 插入1000万条测试数据（示例用函数生成）
INSERT INTO users (name, email, created_at)
SELECT
'user_' || i,
'user_' || i || '@example.com',
NOW() - (random() * 365 * 10 || ' days')::INTERVAL
FROM generate_series(1, 10000000) AS i;
如果没有索引，执行SELECT * FROM users WHERE email = 'user_5000000@example.com'可能需要几秒钟。但加上索引后：
-- 创建B-tree索引
CREATE INDEX idx_users_email ON users(email);
同样的查询瞬间完成，这就是索引的魔力。
二、PostgreSQL的索引类型
PostgreSQL提供了多种索引类型，每种适合不同场景：
1. B-tree索引
最常用的索引，适合等值查询和范围查询。比如：
-- 范围查询示例
SELECT * FROM users WHERE created_at BETWEEN '2022-01-01' AND '2022-12-31';
-- 创建B-tree索引
CREATE INDEX idx_users_created_at ON users(created_at);
2. Hash索引
只支持等值查询，但比B-tree更快。不过它不支持范围查询，也不适合频繁更新的列：
-- 创建Hash索引
CREATE INDEX idx_users_email_hash ON users USING HASH(email);
3. GIN索引
适合复合值（如数组、JSON）。比如查询JSON字段中的值：
-- 添加JSON列
ALTER TABLE users ADD COLUMN profile JSONB;
-- 更新示例数据
UPDATE users SET profile = '{"skills": ["PostgreSQL", "Python"], "age": 30}' WHERE id = 1;
-- 创建GIN索引
CREATE INDEX idx_users_profile ON users USING GIN(profile);
-- 查询JSON字段
SELECT * FROM users WHERE profile @> '{"skills": ["PostgreSQL"]}';
4. GiST索引
适合地理空间数据和全文搜索。比如：
-- 添加地理位置列
ALTER TABLE users ADD COLUMN location POINT;
-- 创建GiST索引
CREATE INDEX idx_users_location ON users USING GIST(location);
-- 查询附近用户
SELECT * FROM users
WHERE location <-> POINT(0, 0) < 10;
三、如何选择合适的索引
不是所有列都适合建索引，选择不当反而会降低性能。以下是几个原则：
- 高选择性列优先：比如邮箱比性别更适合建索引，因为邮箱唯一性更高。
- 常用查询条件：WHERE子句频繁使用的列应该建索引。
- 组合索引技巧：多列查询时考虑组合索引，但要注意顺序：
-- 组合索引示例
CREATE INDEX idx_users_name_email ON users(name, email);
-- 这样查询能用上索引
SELECT * FROM users WHERE name = 'Alice' AND email LIKE '%@example.com';
-- 但这样不行（顺序不匹配）
SELECT * FROM users WHERE email LIKE '%@example.com';
- 避免过度索引：每个索引都会增加写入开销，通常不要超过5-6个索引。
四、索引优化实战案例
案例1：分页查询优化
常见的分页查询：
-- 性能差的写法（偏移量大时）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 1000000;
优化方案：
-- 改用游标分页
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;
案例2：模糊查询优化
LIKE查询通常无法利用索引：
-- 无法使用索引
SELECT * FROM users WHERE email LIKE '%example%';
但可以改用特殊索引：
-- 创建pg_trgm扩展
CREATE EXTENSION pg_trgm;
-- 创建GIN索引支持模糊查询
CREATE INDEX idx_users_email_trgm ON users USING GIN(email gin_trgm_ops);
-- 现在这个查询能用索引了
SELECT * FROM users WHERE email LIKE '%example%';
案例3：部分索引
只为部分数据建索引，节省空间：
-- 只为活跃用户建索引
CREATE INDEX idx_users_active ON users(email) WHERE created_at > NOW() - INTERVAL '1 year';
五、常见陷阱与注意事项
索引失效场景：
- 使用函数操作：
WHERE LOWER(email) = 'test@example.com' - 隐式类型转换：
WHERE email = 123（email是字符串类型） - NULL值判断：
WHERE email IS NULL（除非创建时指定NULLS NOT DISTINCT）
- 使用函数操作：
维护成本：
索引会降低INSERT/UPDATE/DELETE速度，频繁写入的表要谨慎。统计信息：
定期运行ANALYZE更新统计信息，否则优化器可能选错索引。
六、总结
索引是PostgreSQL性能优化的利器，但要用对地方。记住：
- B-tree适合大多数场景
- 组合索引要注意列顺序
- 特殊查询（JSON、地理空间）用专用索引
- 定期维护统计信息
最后分享一个查看索引使用情况的技巧：
-- 查看索引使用统计
SELECT * FROM pg_stat_user_indexes;
评论