SQL 查询优化的常用模式：避免 N+1、合理分页与去重

SQL 查询优化的常用模式避免 N1、合理分页与去重一、识别 N1 查询问题N1 查询是 ORM 使用中最常见、影响最大的性能问题。场景:你需要查询 10 个用户及其各自的订单数量。代码可能是这样:// 第 1 个查询: 获取 10 个用户 const users await db.query(SELECT * FROM users LIMIT 10); // N 个查询对每个用户再查一次他们的订单 for (const user of users) { user.orders await db.query( SELECT * FROM orders WHERE user_id ?, [user.id] ); }总共执行了 11 次查询 (1 10)。如果有 100 个用户就是 101 次查询。如果每次查询耗时 5ms通常不会注意到但如果数据库部署在云端每次查询的网络往返 (RTT) 可能是 50ms101 次查询就是 5 秒——用户体验从「瞬间加载」变成了「明显卡顿」。这种性能瓶颈的核心在于查询结构的耗时分布。在 N1 查询模式下系统需要执行 1 次获取主记录的查询耗时约 5ms以及 N 次逐条获取关联记录的查询耗时约 N × 50ms导致总耗时随数据量线性增长5 50N ms。相比之下批量查询优化只需 1 次获取主记录5ms和 1 次批量获取所有关联记录耗时约 50ms总耗时固定为 55ms完全不受用户数量影响。解决方案:方案一JOIN 或子查询。用一条 SQL 完成所有查询:SELECT u.*, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id LIMIT 10。这样 1 次查询完成原来 1N 次查询的工作。方案二IN 批量查询。先查询所有用户的 ID 列表再用WHERE user_id IN (1,2,3,...,10)一次批量查询所有用户的订单在应用层按 user_id 分组。方案三ORM 的 eager loading。大多数 ORM 支持预加载关联数据。Prisma 中的include,Sequelize 中的include,ActiveRecord 中的includes——在查询主记录时就告诉 ORM「顺便把关联数据一起查了」。二、深分页的性能问题分页查询是独立产品中最常见的列表功能。用LIMIT 20 OFFSET 10000来获取第 10001 到 10020 条记录——这种「深分页」在某些数据库中是性能杀手,因为它需要扫描前 10000 条记录然后丢弃,只返回最后 20 条。解决方案:方案一:游标分页(Cursor-based Pagination)。用WHERE id last_id ORDER BY id LIMIT 20替代OFFSET。第一次查询返回前 20 条,记录最后一条的 ID,下次查询WHERE id 20 LIMIT 20。这种方式「只扫描需要的记录」,性能与页码无关。缺点:不能跳页(如直接从第 1 页跳到第 10 页)。方案二:延迟关联(Deferred Join)。先查 ID(索引扫描,快),再根据 ID 查完整数据。SELECT * FROM posts WHERE id IN (SELECT id FROM posts ORDER BY id LIMIT 20 OFFSET 10000)。内层查询只扫描索引,速度远快于全表扫描;外层查询用 IN 批量获取 20 条完整记录。方案三:对于小型数据量( 10 万条),OFFSET 分页完全够用。深分页优化只在数据量达到一定规模(百万级以上)时才需要。不要过早优化。三、重复数据的处理数据去重是独立产品中常见的数据质量需求。方案一:DISTINCT。最简单的去重方式:SELECT DISTINCT category FROM posts。DISTINCT 适合「对单列去重」或「取唯一组合」,但 DISTINCT 要求返回的所有字段的组合唯一。方案二:GROUP BY 聚合函数。与 DISTINCT 类似,但可以在去重的同时做聚合:SELECT category, COUNT(*) FROM posts GROUP BY category。方案三:ROW_NUMBER() 窗口函数。当需要「保留每组中的一条记录」时,使用ROW_NUMBER():为每个分组中的记录编号,取出每组编号为 1 的记录。WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC ) as rn FROM user_logs ) SELECT * FROM ranked WHERE rn 1; 这样能获取每个用户的最新一条日志,且一次查询完成,不需要循环遍历。 ## 四、查询计划分析与验证 优化 SQL 查询后,如何验证「优化确实有效」最重要的工具还是 EXPLAIN(或 EXPLAIN ANALYZE)。 优化前运行 EXPLAIN ANALYZE,记录扫描行数和实际执行时间。优化后再次运行,对比两个指标的变化。不要只凭「感觉」判断优化效果——用数据说话。具体验证流程如下 1. 发现慢查询后先通过 EXPLAIN ANALYZE 记录优化前的扫描行数和执行时间 2. 执行优化操作比如用 JOIN 替代 N1 查询、用游标替代 OFFSET 深分页等 3. 再次运行 EXPLAIN ANALYZE对比优化前后的扫描行数和执行时间 4. 若扫描行数显著减少比如从百万级降到几十行说明优化有效可上线若扫描行数无明显变化说明优化方向有误需继续分析调整。 ## 五、总结 SQL 查询优化的常用模式,核心是三个「避免」:**避免循环中的逐条查询(N1)→ 用 JOIN 或批量 IN 替代**;**避免扫描大量不需要的记录(OFFSET 深分页)→ 用游标或延迟关联**;**避免应用层处理重复数据(逐条过滤)→ 用窗口函数在数据库层完成**。 对于独立产品:在查询数据量不大( 10 万条)的阶段,用 ORM 的 eager loading(预加载)解决 N1 问题;数据增长到百万级后,逐步用游标分页和延迟关联优化;去重用窗口函数(数据库层)比应用层遍历高效得多。 SQL 优化的效果,用 EXPLAIN 前后的扫描行数变化来验证。扫描行数减少 90% 的优化,是值得做的;扫描行数几乎不变的优化,可能是方向上弄错了。