面试:谈谈PostgreSQL数据库SQL改写技巧？

以下是 PostgreSQL 中典型的低效 SQL 写法及其优化方案，这些写法会导致全表扫描、索引失效、资源过载等问题，附详细示例和优化方法：

---

一、索引失效类问题

1. 对索引列使用函数或表达式

-- 原写法（索引失效）：
SELECT * FROM orders WHERE EXTRACT(year FROM create_time) = 2023;

-- 优化方案：
SELECT * FROM orders 
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

-- 支持函数索引（特殊场景）：
CREATE INDEX idx_orders_year ON orders (EXTRACT(year FROM create_time));

2. 隐式类型转换

-- user_id 是 VARCHAR 类型
SELECT * FROM users WHERE user_id = 10086; -- 触发隐式转换

-- 优化方案：
SELECT * FROM users WHERE user_id = '10086'; -- 保持类型一致

3. 前导通配符查询

-- 原写法（全表扫描）：
SELECT * FROM products WHERE name LIKE '%Pro Max%';

-- 优化方案：
SELECT * FROM products WHERE name LIKE 'Apple%'; -- 仅用后通配符

-- 全文索引方案：
ALTER TABLE products ADD COLUMN search_vec tsvector;
UPDATE products SET search_vec = to_tsvector('english', name);
CREATE INDEX idx_product_search ON products USING GIN(search_vec);

SELECT * FROM products 
WHERE search_vec @@ to_tsquery('english', 'Pro & Max');

---

二、查询结构问题

**4. 滥用 SELECT ***

-- 原写法（传输冗余数据）：
SELECT * FROM employees WHERE dept_id = 5;

-- 优化方案：
SELECT emp_id, name, position -- 仅查必要字段
FROM employees WHERE dept_id = 5;

5. 低效分页查询

-- 原写法（扫描前 100000 行）：
SELECT * FROM logs ORDER BY id OFFSET 100000 LIMIT 20;

-- 优化方案（Keyset 分页）：
SELECT * FROM logs 
WHERE id > (SELECT id FROM logs ORDER BY id OFFSET 100000 LIMIT 1)
ORDER BY id LIMIT 20;

6. 滥用 CTE (WITH 子句)

-- 原写法（CTE 被多次执行）：
WITH cte AS (SELECT * FROM large_table WHERE category = 'A')
SELECT * FROM cte WHERE value > 100
UNION ALL
SELECT * FROM cte WHERE value < 50;

-- 优化方案（物化 CTE）：
WITH cte AS MATERIALIZED (SELECT * FROM large_table WHERE category = 'A')
SELECT * FROM cte WHERE value > 100
UNION ALL
SELECT * FROM cte WHERE value < 50;

---

三、连接与子查询问题

7. NOT IN 子查询

-- 原写法（低效）：
SELECT * FROM orders 
WHERE user_id NOT IN (SELECT user_id FROM payments);

-- 优化方案（改用 NOT EXISTS）：
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM payments p 
  WHERE p.user_id = o.user_id
);

-- 或使用 LEFT JOIN：
SELECT o.* 
FROM orders o
LEFT JOIN payments p ON o.user_id = p.user_id
WHERE p.user_id IS NULL;

8. 笛卡尔积连接

-- 原写法（产生 M×N 条数据）：
SELECT * FROM users, departments;

-- 优化方案：
SELECT * FROM users CROSS JOIN departments; -- 显式声明
-- 或添加 JOIN 条件

---

四、聚合与排序问题

9. 非索引列排序

-- 原写法（文件排序）：
SELECT * FROM products ORDER BY price DESC;

-- 优化方案：
CREATE INDEX idx_products_price ON products(price DESC);
SELECT * FROM products ORDER BY price DESC; -- 使用索引排序

10. DISTINCT 滥用

-- 原写法（高开销去重）：
SELECT DISTINCT product_id FROM orders;

-- 优化方案（GROUP BY 更高效）：
SELECT product_id FROM orders GROUP BY product_id;

---

五、数据操作问题

11. 未分批的大数据删除

-- 原写法（长事务锁表）：
DELETE FROM user_logs WHERE create_time < '2020-01-01';

-- 优化方案（分批删除）：
DO $$
DECLARE
  batch_size INT := 1000;
BEGIN
  LOOP
    DELETE FROM user_logs 
    WHERE create_time < '2020-01-01'
    LIMIT batch_size;
    EXIT WHEN NOT FOUND;
    COMMIT;
    PERFORM pg_sleep(0.1); -- 减少锁冲突
  END LOOP;
END $$;

12. 未使用 RETURNING 子句

-- 原写法（额外查询）：
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 100;
SELECT * FROM orders WHERE order_id = 100; -- 额外查询

-- 优化方案：
UPDATE orders SET status = 'SHIPPED' 
WHERE order_id = 100
RETURNING *; -- 单次操作

---

六、高级优化技巧

13. 覆盖索引优化

-- 原写法（回表查询）：
SELECT user_id, status FROM orders WHERE status = 'PAID';

-- 优化方案：
CREATE INDEX idx_orders_status_userid ON orders(status) INCLUDE (user_id);

14. 强制索引扫描

-- 优化器错误选择索引时：
SET enable_seqscan = off; -- 临时禁用全表扫描
SELECT * FROM orders WHERE create_time > '2023-01-01';

15. 分区表查询优化

-- 原写法（扫描所有分区）：
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 优化方案（按分区键查询）：
SELECT * FROM sales 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
AND sale_date >= '2023-01-01' AND sale_date <= '2023-12-31'; -- 明确分区边界

---

七、执行计划分析与验证

EXPLAIN 分析示例

EXPLAIN (ANALYZE, BUFFERS, COSTS)
SELECT p.product_id, p.name, COUNT(o.order_id)
FROM products p
JOIN orders o ON p.product_id = o.product_id
WHERE p.category = 'Electronics'
GROUP BY p.product_id
HAVING COUNT(o.order_id) > 10;

关键指标解读：

指标	健康状态	问题表现
Scan Type	Index Scan	Seq Scan（全表扫描）
Index Cond	有效索引条件	Filter（内存过滤）
Heap Fetches	低（覆盖索引为0）	高（频繁回表）
Buffers	shared hit > 90%	shared read 过高
Planning Time	< 10ms	> 100ms（统计信息过旧）

---

性能问题速查表

问题类型	危险写法特征	优化策略
索引失效	WHERE lower(name)='cjc'	函数索引/表达式改写
隐式类型转换	WHERE varchar_col = 123	统一数据类型
低效分页	OFFSET 100000 LIMIT 20	Keyset 分页
CTE 性能陷阱	多层嵌套 CTE	物化 CTE (MATERIALIZED)
分区表全扫描	未使用分区键过滤	显式添加分区键条件
冗余查询	未用 RETURNING	单次操作返回数据
大事务锁冲突	一次性操作百万行	分批提交
全字段返回	SELECT *	仅选择必要字段

---

优化前后性能对比示例

问题 SQL：

SELECT user_id, COUNT(*) 
FROM orders
WHERE EXTRACT(month FROM order_time) = 12
GROUP BY user_id;

优化步骤：

1. 创建函数索引：

   CREATE INDEX idx_orders_month ON orders (EXTRACT(month FROM order_time));
   

2. 改写查询：

   SELECT user_id, COUNT(*)
   FROM orders
   WHERE order_time BETWEEN '2023-12-01' AND '2023-12-31'
   GROUP BY user_id;
   

性能提升：

指标	优化前	优化后
执行时间	1200 ms	45 ms
扫描行数	1,000,000	85,000
Buffers 读取	15,000	420

通过针对性改写和索引优化，查询性能提升 26 倍。

重要提示：所有优化后务必使用 EXPLAIN ANALYZE 验证执行计划，并定期执行 ANALYZE table_name 更新统计信息。

欢迎关注我的公众号《IT小Chen》