SQL 查询优化：10 个真正有效的技巧

SQL 查询优化：真正有效的技巧

SQL 性能问题遵循可预测的模式。在考虑引入缓存层或升级数据库之前，先尝试这些技巧——大多数慢查询只需几分钟就能修复，只要你知道该找什么。

1. 先运行 EXPLAIN ANALYZE

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

关注：Seq Scan（大表上的全表扫描）、高 "Rows Removed by Filter"、实际行数与预估行数不匹配。

2. 添加缺失的索引

-- 单列索引
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 复合索引（选择性最高的列在前）
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- 部分索引（仅相关行）
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

复合索引的列顺序很重要：(status, created_at) 有助于仅按 status 或同时按两者过滤的查询，但无法帮助仅按 created_at 过滤的查询。

3. 使用覆盖索引

包含查询所需的所有列——数据库无需访问主表行：

-- 查询：SELECT email, name FROM users WHERE active = true ORDER BY created_at
CREATE INDEX idx_users_active ON users(active, created_at) INCLUDE (email, name);

4. 避免使用 SELECT *

-- 慢：获取所有 20+ 列，包括大的 TEXT/JSON 字段
SELECT * FROM articles WHERE author_id = 42;

-- 快：仅需要的列，可以命中覆盖索引
SELECT id, title, published_at FROM articles WHERE author_id = 42;

5. 修复 N+1 问题

1 个查询获取 N 行，然后 N 个查询获取关联数据 = N+1 次往返。

-- 替代 N+1：单个 JOIN
SELECT o.id, o.amount, c.name, c.email
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

// ORM 预加载（Sequelize）
const orders = await Order.findAll({
  where: { status: 'pending' },
  include: [{ model: Customer }],  // 1 个查询而非 N+1
});

6. 子查询中使用 EXISTS 替代 IN

-- 较慢：IN 扫描整个子查询结果
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- 较快：EXISTS 在首次匹配时停止
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 1000);

7. 避免在索引列上使用函数

-- 慢：函数阻止索引使用
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- 快：范围条件使用索引
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

8. 使用键集分页替代 OFFSET

-- 慢：OFFSET 10000 扫描并丢弃 10000 行
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- 快：键集（基于游标）分页
SELECT * FROM orders WHERE id > 10020 ORDER BY id LIMIT 20;

9. 批量插入

-- 慢：1000 条单独的 INSERT 语句（1000 次往返）
-- 快：单条批量 INSERT
INSERT INTO logs (user_id, action) VALUES
  (1, 'login'), (2, 'purchase'), (3, 'view');
-- ... 每批最多 1000-5000 行

10. 启用慢查询日志

-- PostgreSQL：记录超过 100ms 的查询
SET log_min_duration_statement = 100;

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

快速诊断清单

1. 大表上出现 Seq Scan？→ 在 WHERE/JOIN 列上添加索引
2. 每个请求有多个查询？→ 使用 JOIN 或预加载修复 N+1
3. 使用了 SELECT *？→ 仅指定需要的列
4. WHERE 列上使用了函数？→ 重写为范围条件
5. 大量 OFFSET？→ 切换到键集分页
6. 批量写入？→ 使用批量 INSERT/UPDATE

→ 使用 SQL 格式化工具 格式化和美化 SQL 查询。