"这条SQL怎么这么慢？"这大概是后端开发中最常见的问题之一。很多开发者遇到慢查询就本能地加索引，但索引不是万能药。真正的SQL性能优化需要从查询设计、索引策略、执行计划分析等多个维度入手。这篇文章总结了10个经过实战验证的优化技巧，每个都有具体的代码示例和效果对比。
技巧1：用EXPLAIN分析执行计划
优化SQL的第一步不是改代码，而是理解查询是怎么执行的。
-- 基础EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- 详细EXPLAIN（推荐）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- PostgreSQL格式化输出
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name;
关注重点：
-
Seq Scan（全表扫描）—— 通常需要优化 -
Index Scan（索引扫描）—— 理想情况 -
rows（预估行数）—— 与实际行数差距大说明统计信息过时 -
actual time（实际执行时间）—— 找出最耗时的部分
-- 如果统计信息过时，手动更新
ANALYZE orders;
ANALYZE users;
技巧2：避免SELECT *，只查需要的列
-- 慢：查询所有列（包括大文本字段）
SELECT * FROM articles WHERE author_id = 456;
-- 快：只查询需要的列
SELECT id, title, created_at FROM articles WHERE author_id = 456;
为什么SELECT *慢？三个原因：
- 网络传输：数据库到应用之间传输了大量不需要的数据
- 内存占用：数据库需要在内存中构造完整行
- 覆盖索引失效：无法利用覆盖索引（后面会讲）
技巧3：合理创建复合索引
单列索引不够用的时候，复合索引是关键。但顺序很重要。
-- 假设这个查询很频繁
SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- 创建复合索引（顺序很关键）
-- 最左前缀原则：等值条件在前，范围条件在后
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);
复合索引的顺序规则：
- 等值条件的列放前面（
user_id = 123,status = 'paid'） - 范围条件的列放后面（
created_at DESC） - 排序的列放最后
-- 这个查询能用到上面的索引
WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC
-- 这个查询只能用到部分索引（user_id部分）
WHERE user_id = 123 ORDER BY created_at DESC
-- 这个查询完全用不到索引
WHERE status = 'paid' ORDER BY created_at DESC
技巧4：利用覆盖索引避免回表
覆盖索引是指索引包含了查询所需的所有列，不需要回表查询数据行。
-- 假设 users 表有 (id, name, email, bio, avatar, created_at)
-- 查询1：需要回表（bio和avatar不在索引中）
SELECT name, email, bio FROM users WHERE id = 123;
-- 查询2：可以覆盖索引（只查name和email）
SELECT name, email FROM users WHERE id = 123;
-- 创建覆盖索引
CREATE INDEX idx_users_name_email ON users(id, name, email);
效果：覆盖索引可以把查询速度提升10-100倍，特别是在数据量大的时候。
技巧5：用JOIN替代子查询
很多数据库对JOIN的优化比子查询好得多。
-- 慢：相关子查询（每行执行一次子查询）
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;
-- 快：用JOIN替代
SELECT u.name, COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id;
-- 慢：WHERE中的子查询
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE region = 'Asia');
-- 快：用JOIN替代
SELECT DISTINCT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.region = 'Asia';
技巧6：分页优化——避免大偏移量
传统的OFFSET分页在大偏移量时性能急剧下降。
-- 慢：OFFSET越大越慢（数据库需要扫描并跳过前面的行）
SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- 快：使用游标分页（基于上一页最后一条记录）
SELECT * FROM articles
WHERE created_at < '2025-05-01 12:00:00' -- 上一页最后一条的时间
ORDER BY created_at DESC
LIMIT 20;
-- 或者使用WHERE id > last_id
SELECT * FROM articles
WHERE id > 500000 -- 上一页最后一条的ID
ORDER BY id ASC
LIMIT 20;
性能对比（100万行数据）：
-
OFFSET 0：约5ms -
OFFSET 10000：约50ms -
OFFSET 100000：约500ms -
OFFSET 500000：约2500ms - 游标分页：始终约5ms
技巧7：批量操作替代循环查询
N+1查询问题是性能杀手。
# 慢：N+1查询（在循环中执行SQL）
users = db.query("SELECT * FROM users LIMIT 100")
for user in users:
orders = db.query(f"SELECT * FROM orders WHERE user_id = {user['id']}")
user['orders'] = orders
# 执行了101次查询！
# 快：批量查询
users = db.query("SELECT * FROM users LIMIT 100")
user_ids = [u['id'] for u in users]
orders = db.query(
"SELECT * FROM orders WHERE user_id IN (%s)" %
','.join(map(str, user_ids))
)
# 构建user_id到orders的映射
from collections import defaultdict
orders_map = defaultdict(list)
for order in orders:
orders_map[order['user_id']].append(order)
for user in users:
user['orders'] = orders_map.get(user['id'], [])
# 只执行了2次查询！
技巧8：避免在索引列上使用函数
在索引列上使用函数会导致索引失效，触发全表扫描。
sql
-- 慢：函数导致索引失效
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
SELECT * FROM orders WHERE DATE(created_at) = '2025-06-01';
SELECT * FROM products WHERE name LIKE '%phone%';
---
*本文首发于[我的技术博客](https://wdsega.github.io)，欢迎访问获取更多技术文章。*
*如果你是内容创作者或自由职业者，推荐看看我整理的[Creator Pro Bundle](https://segauser.gumroad.com/l/rrhmbb)工具包，包含AI提示词系统、内容创作工具、副业指南和自动化脚本，源码全开放。*
Top comments (0)