PostgreSQL / 系统课时
避免常见 SQL 性能问题
避免常见 SQL 性能问题
本课解决“SQL 能运行但浪费大量资源”的问题。你将从类型、条件、访问次数和返回数据量四个方面识别低效写法,并把它们改成更容易使用索引、减少往返的查询。
学习目标
- 避免列上的类型转换和函数计算
- 用范围条件替代不利于索引的日期表达式
- 减少 N+1 查询、重复查询和无用返回列
保持参数与列类型一致
PostgreSQL 对类型匹配较严格。即使通过显式转换让 SQL 能执行,把转换写在索引列上也可能使普通索引失效。应优先转换参数,而不是转换列。下面脚本同时演示订单号精确匹配和日期范围过滤。
CREATE TEMP TABLE customer_orders (
id bigint PRIMARY KEY,
order_no text NOT NULL,
customer_id bigint NOT NULL,
status text NOT NULL,
created_at timestamp NOT NULL
);
INSERT INTO customer_orders VALUES
(1, '10001', 501, 'PAID', '2026-07-30 09:00:00'),
(2, '10002', 501, 'PAID', '2026-07-30 23:30:00'),
(3, '10003', 502, 'PENDING', '2026-07-31 00:10:00');
CREATE INDEX idx_customer_orders_order_no
ON customer_orders (order_no);
CREATE INDEX idx_customer_orders_created_at
ON customer_orders (created_at);
SELECT id, order_no
FROM customer_orders
WHERE order_no = '10001';
SELECT id, order_no
FROM customer_orders
WHERE created_at >= TIMESTAMP '2026-07-30 00:00:00'
AND created_at < TIMESTAMP '2026-07-31 00:00:00'
ORDER BY id;预期结果
订单号查询:
1 | 10001
日期范围查询:
1 | 10001
2 | 10002反例是把列写成 order_no::bigint = 10001,或写成 created_at::date = DATE '2026-07-30'。它们要求逐行计算,通常不利于直接使用原列上的 B-tree 索引。
删除无效和重复条件
恒真条件、重复条件和互相矛盾的条件会增加阅读和维护成本,也可能干扰优化判断。例如动态 SQL 中常见的 WHERE 1 = 1 本身影响通常不大,但若生成器重复追加同一筛选项,应在应用层去重。
使用 OR 连接大量不同列时,应检查是否能拆成更清晰的 UNION ALL,但只有确认结果不会重复或已经正确去重时才能这样改。不要为了“看起来走索引”而改变业务语义。
避免 N+1 与重复往返
先查 100 个客户,再为每个客户单独查订单,会产生 101 次数据库往返。应使用连接、批量 IN、聚合或一次性加载完成同类需求。
同一请求内重复执行完全相同的查询,也应通过变量复用、请求级缓存或调整服务调用链消除。数据库优化不仅是单条 SQL 更快,还包括减少 SQL 次数。
只返回需要的数据
SELECT * 会读取无用列,宽表中的大文本或二进制字段尤其昂贵。列表查询应显式选择展示字段,详情查询再按需补充。
同时限制结果集大小。缺少过滤或 LIMIT 的管理查询可能把大量数据传到应用层,导致数据库、网络和 JVM 内存同时承压。接口层还应设置合理的最大分页大小。
用执行计划验证而非猜测
使用 EXPLAIN (ANALYZE, BUFFERS) 比较修改前后实际行数、循环次数、扫描方式和缓冲区读取。若估算行数与实际差异很大,应检查统计信息是否过期,并执行 ANALYZE。
优化后还要验证结果集、排序和边界条件未改变。性能提升不能以漏数据、重复数据或类型语义变化为代价。