SQL 优化模式

通过系统优化、合理索引和查询计划分析，将慢速数据库查询转变为闪电般的快速操作。

何时使用此技能

• - 调试运行缓慢的查询
• 设计高性能数据库模式
• 优化应用程序响应时间
• 降低数据库负载和成本
• 提升数据集的扩展性
• 分析 EXPLAIN 查询计划
• 实现高效索引
• 解决 N+1 查询问题

核心概念

1. 查询执行计划 (EXPLAIN)

理解 EXPLAIN 输出是优化的基础。

PostgreSQL EXPLAIN:

sql
-- 基本解释
EXPLAIN SELECT * FROM users WHERE email = user@example.com;

-- 包含实际执行统计
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = user@example.com;

-- 更详细的输出
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL 30 days;

需要关注的关键指标：

• - Seq Scan：全表扫描（对于大表通常较慢）
• Index Scan：使用索引（良好）
• Index Only Scan：仅使用索引而不访问表（最佳）
• Nested Loop：连接方法（适用于小数据集）
• Hash Join：连接方法（适用于较大数据集）
• Merge Join：连接方法（适用于已排序数据）
• Cost：估算查询成本（越低越好）
• Rows：估算返回的行数
• Actual Time：实际执行时间

2. 索引策略

索引是最强大的优化工具。

索引类型：

• - B-Tree：默认，适用于等值和范围查询
• Hash：仅用于等值（=）比较
• GIN：全文搜索、数组查询、JSONB
• GiST：几何数据、全文搜索
• BRIN：适用于具有相关性的超大表的块范围索引

sql
-- 标准 B-Tree 索引
CREATE INDEX idxusersemail ON users(email);

-- 复合索引（顺序很重要！）
CREATE INDEX idxordersuserstatus ON orders(userid, status);

-- 部分索引（索引行的子集）
CREATE INDEX idxactiveusers ON users(email)
WHERE status = active;

-- 表达式索引
CREATE INDEX idxuserslower_email ON users(LOWER(email));

-- 覆盖索引（包含额外列）
CREATE INDEX idxusersemail_covering ON users(email)
INCLUDE (name, created_at);

-- 全文搜索索引
CREATE INDEX idxpostssearch ON posts
USING GIN(to_tsvector(english, title || || body));

-- JSONB 索引
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. 查询优化模式

避免 SELECT \*：

sql
-- 不好：获取不必要的列
SELECT * FROM users WHERE id = 123;

-- 好：只获取所需内容
SELECT id, email, name FROM users WHERE id = 123;

高效使用 WHERE 子句：

sql
-- 不好：函数阻止索引使用
SELECT * FROM users WHERE LOWER(email) = user@example.com;

-- 好：创建函数索引或使用精确匹配
CREATE INDEX idxusersemail_lower ON users(LOWER(email));
-- 然后：
SELECT * FROM users WHERE LOWER(email) = user@example.com;

-- 或者存储规范化数据
SELECT * FROM users WHERE email = user@example.com;

优化 JOIN：

sql
-- 不好：笛卡尔积然后过滤
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.userid AND u.createdat > 2024-01-01;

-- 好：先过滤再连接
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > 2024-01-01;

-- 更好：过滤两个表
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > 2024-01-01) u
JOIN orders o ON u.id = o.user_id;

优化模式

模式 1：消除 N+1 查询

问题：N+1 查询反模式

python

不好：执行 N+1 个查询

users = db.query(SELECT * FROM users LIMIT 10)
for user in users:
orders = db.query(SELECT * FROM orders WHERE user_id = ?, user.id)
# 处理订单

解决方案：使用 JOIN 或批量加载

sql
-- 解决方案 1：JOIN
SELECT
u.id, u.name,
o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

-- 解决方案 2：批量查询
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5);

python

好：使用 JOIN 或批量加载的单个查询

使用 JOIN

results = db.query(
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5)
)

或批量加载

users = db.query(SELECT * FROM users LIMIT 10) user_ids = [u.id for u in users] orders = db.query( SELECT * FROM orders WHERE user_id IN (?), user_ids )

按 user_id 分组订单

ordersbyuser = {} for order in orders: ordersbyuser.setdefault(order.user_id, []).append(order)

模式 2：优化分页

不好：在大表上使用 OFFSET

sql
-- 大偏移量时很慢
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000; -- 非常慢！

好：基于游标的分页

sql
-- 快得多：使用游标（最后看到的 ID）
SELECT * FROM users
WHERE created_at < 2024-01-15 10:30:00 -- 最后一个游标
ORDER BY created_at DESC
LIMIT 20;

-- 复合排序
SELECT * FROM users
WHERE (created_at, id) < (2024-01-15 10:30:00, 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 需要索引
CREATE INDEX idxuserscursor ON users(created_at DESC, id DESC);

模式 3：高效聚合

优化 COUNT 查询：

sql
-- 不好：计数所有行
SELECT COUNT(*) FROM orders; -- 在大表上很慢

-- 好：使用估算值进行近似计数
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = orders;

-- 好：先过滤再计数
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL 7 days;

-- 更好：使用仅索引扫描
CREATE INDEX idxorderscreated ON orders(created_at);
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL 7 days;

优化 GROUP BY：

sql
-- 不好：先分组再过滤
SELECT userid, COUNT(*) as ordercount
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10;

-- 更好：先过滤再分组（如果可能）
SELECT userid, COUNT(*) as ordercount
FROM orders
WHERE status = completed
GROUP BY user_id
HAVING COUNT(*) > 10;

-- 最佳：使用覆盖索引
CREATE INDEX idxordersuserstatus ON orders(userid, status);

模式 4：子查询优化

转换相关子查询：

sql
-- 不好：相关子查询（对每一行执行）
SELECT u.name, u.email,
(SELECT COUNT(*) FROM orders o WHERE o.userid = u.id) as ordercount
FROM users u;

-- 好：使用聚合的 JOIN
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name, u.email;

-- 更好：使用窗口函数
SELECT DISTINCT ON (u.id)
u.name, u.email,
COUNT(o.id) OVER (PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

使用 CTE 提高清晰度：

sql
-- 使用公用表表达式
WITH recent_users AS (
SELECT id, name, email
FROM users
WHERE created_at > NOW() - INTERVAL 30 days
),
userordercounts AS (
SELECT user_id