SQL性能优化实战：从慢查询到高效执行

性能问题的根源

SQL性能问题通常源于以下几个方面：缺少合适的索引、查询写法低效、数据量增长超出预期设计范围，以及统计信息过时导致查询优化器做出错误决策。在开始优化之前，首先需要找到真正的瓶颈所在，而不是凭感觉猜测。

使用EXPLAIN ANALYZE定位问题：

-- 分析查询执行计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

重点关注执行计划中的以下信号：Seq Scan（全表扫描，通常需要优化）、Hash Join vs Nested Loop（大表连接时前者通常更好）、actual time 远大于 estimated time（统计信息过时）。

索引设计原则

索引是SQL优化最有效的工具，但错误的索引反而会拖慢写入性能。

单列索引 vs 复合索引：

-- 针对常见的过滤+排序组合创建复合索引
-- 列的顺序遵循"等值过滤列在前，范围过滤和排序列在后"
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at DESC);

-- 覆盖索引：查询所需列全部在索引中，无需回表
CREATE INDEX idx_orders_covering
ON orders (user_id, status)
INCLUDE (total_amount, created_at);
-- 查询时直接从索引获取数据，避免访问主表

部分索引：只对满足条件的行建立索引，节省空间并提高选择性：

-- 只对未完成订单建立索引（假设已完成订单占99%，不需要频繁查询）
CREATE INDEX idx_orders_pending
ON orders (user_id, created_at)
WHERE status IN ('pending', 'processing');

-- 只对活跃用户建立索引
CREATE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

常见慢查询优化

问题1：N+1查询

N+1是ORM中最常见的性能陷阱，在循环中为每条记录发出额外查询：

-- 低效：先查10个用户，再逐个查订单（11次查询）
SELECT * FROM users LIMIT 10;
-- 对每个用户执行：SELECT * FROM orders WHERE user_id = ?

-- 优化：使用JOIN一次性获取所有数据（1次查询）
SELECT u.id, u.name, u.email,
       json_agg(json_build_object(
         'id', o.id,
         'total', o.total_amount,
         'status', o.status
       )) AS orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = ANY($1::uuid[])  -- 批量传入用户ID数组
GROUP BY u.id, u.name, u.email;

问题2：在WHERE子句中对列使用函数

-- 低效：函数调用导致无法使用索引
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-01';

-- 优化：改写为范围条件，可使用索引
SELECT * FROM orders
WHERE created_at >= '2026-03-01'
  AND created_at <  '2026-03-02';

**问题3：过度使用SELECT ***

-- 低效：传输不必要的列，无法使用覆盖索引
SELECT * FROM products WHERE category_id = $1;

-- 优化：只选择需要的列
SELECT id, name, price, stock_quantity
FROM products WHERE category_id = $1;

分区表提升大数据量性能

当单表数据量超过数千万行时，表分区是保持查询性能的有效手段：

-- 按月创建范围分区
CREATE TABLE events (
  id          BIGSERIAL,
  user_id     UUID NOT NULL,
  event_type  VARCHAR(50),
  created_at  TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建月度分区
CREATE TABLE events_2026_01
PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02
PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- 查询时PostgreSQL会自动只扫描相关分区（分区裁剪）
SELECT * FROM events
WHERE created_at BETWEEN '2026-01-15' AND '2026-01-31'
  AND user_id = $1;