一、为什么我的PostgreSQL查询突然变慢了？
最近有个做电商的朋友跑来跟我吐槽，说他们平台的商品搜索功能越来越慢，特别是在促销活动期间，用户投诉特别多。我让他把查询语句发给我看看，结果发现是个典型的"全表扫描"问题。他们那张商品表已经积累了上百万条数据，但查询时居然没有走索引。
-- 这是他们正在使用的查询（技术栈：PostgreSQL 14）
EXPLAIN ANALYZE
SELECT * FROM products
WHERE category_id = 5 AND price BETWEEN 100 AND 500;
执行计划显示进行了全表扫描，耗时达到了惊人的1200ms。这让我意识到，很多开发者在使用PostgreSQL时，都忽略了默认索引策略的重要性。
二、PostgreSQL的默认索引策略揭秘
PostgreSQL安装后会默认创建一个名为"public"的模式，但不会为任何表自动创建索引。这与某些数据库系统不同，比如MySQL的InnoDB会为主键自动创建聚簇索引。
PostgreSQL的默认索引类型是B-tree，它适合大多数常见查询。但很多人不知道的是，即使你创建了索引，查询优化器也不一定会使用它。这取决于以下几个关键因素：
- 表的数据量大小
- 索引的选择性
- 统计信息的准确性
- 查询条件的写法
-- 创建一个简单的B-tree索引
CREATE INDEX idx_products_category ON products(category_id);
-- 但这样的查询可能还是不会走索引
SELECT * FROM products WHERE category_id = 5 OR price > 100;
三、如何正确设计PostgreSQL索引
针对我朋友的案例，我建议他们创建复合索引：
-- 创建复合索引（技术栈：PostgreSQL 14）
CREATE INDEX idx_products_category_price ON products(category_id, price);
-- 然后我们再次分析查询
EXPLAIN ANALYZE
SELECT * FROM products
WHERE category_id = 5 AND price BETWEEN 100 AND 500;
这次查询时间从1200ms降到了25ms！但索引设计远不止这么简单，还需要考虑更多场景：
- 部分索引：只为表中部分数据创建索引
- 表达式索引：对计算后的值建立索引
- 覆盖索引：包含查询所需的所有字段
- 并行索引扫描：大表查询优化
-- 部分索引示例：只索引活跃商品
CREATE INDEX idx_active_products ON products(id)
WHERE status = 'active';
-- 表达式索引示例：对商品名称的小写形式建立索引
CREATE INDEX idx_product_name_lower ON products(LOWER(name));
-- 覆盖索引示例：包含查询需要的额外字段
CREATE INDEX idx_covering ON products(category_id, price)
INCLUDE (name, image_url);
四、常见陷阱与高级优化技巧
即使创建了合适的索引，仍然可能遇到性能问题。以下是几个常见陷阱及解决方案：
- 统计信息过期：使用ANALYZE命令更新
- 索引膨胀：定期REINDEX
- 错误的参数设置：调整work_mem等参数
- 查询写法问题：避免在索引列上使用函数
-- 更新统计信息
ANALYZE products;
-- 重建索引
REINDEX INDEX idx_products_category_price;
-- 查看索引使用情况（技术栈：PostgreSQL 14）
SELECT * FROM pg_stat_user_indexes
WHERE relname = 'products';
-- 调整内存参数（需要在postgresql.conf中设置）
-- work_mem = 16MB
-- maintenance_work_mem = 256MB
对于特别大的表，还可以考虑表分区和并行查询：
-- 按范围分区示例
CREATE TABLE products (
id SERIAL,
name VARCHAR(100),
category_id INT,
price DECIMAL(10,2),
created_at TIMESTAMP
) PARTITION BY RANGE (category_id);
-- 创建分区表
CREATE TABLE products_1_5 PARTITION OF products
FOR VALUES FROM (1) TO (5);
CREATE TABLE products_6_10 PARTITION OF products
FOR VALUES FROM (6) TO (10);
-- 启用并行查询
SET max_parallel_workers_per_gather = 4;
五、实战：从慢查询到闪电查询
让我们看一个完整的优化案例。假设有一个订单查询接口变慢，原始查询如下：
-- 原始慢查询（技术栈：PostgreSQL 14）
EXPLAIN ANALYZE
SELECT o.*, u.username, p.name as product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'
AND o.created_at BETWEEN '2023-01-01' AND '2023-06-30'
ORDER BY o.created_at DESC
LIMIT 100;
这个查询耗时1800ms，执行计划显示进行了多次全表扫描。我们采取以下优化措施：
- 为orders表创建复合索引
- 为关联查询创建索引
- 使用覆盖索引减少回表
- 调整查询写法
-- 创建优化后的索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC)
INCLUDE (user_id, product_id);
CREATE INDEX idx_users_id ON users(id) INCLUDE (username);
CREATE INDEX idx_products_id ON products(id) INCLUDE (name);
-- 优化后的查询
EXPLAIN ANALYZE
SELECT o.*, u.username, p.name as product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'
AND o.created_at >= '2023-01-01'
AND o.created_at < '2023-07-01'
ORDER BY o.created_at DESC
LIMIT 100;
优化后查询时间降至85ms，性能提升了20倍！
六、总结与最佳实践
通过这个案例，我们可以总结出PostgreSQL索引优化的几个最佳实践：
- 理解你的查询模式：索引应该为查询服务
- 复合索引的顺序很重要：高选择性列在前
- 定期维护索引：REINDEX和ANALYZE
- 监控索引使用情况：移除无用索引
- 考虑部分索引和表达式索引的特殊场景
- 合理设置数据库参数
记住，索引不是越多越好。每个索引都会增加写操作的开销。一个好的经验法则是：只为那些被频繁查询且选择性高的列创建索引。
最后，PostgreSQL提供了丰富的工具来帮助诊断性能问题，如pg_stat_statements、EXPLAIN ANALYZE等。善用这些工具，结合对业务查询的理解，你就能设计出高效的索引策略。
评论