在 Node.js PostgreSQL API 中消除 N+1 查询及相关子查询问题

端点响应缓慢往往被归咎于基础设施问题，但实际上很多情况下其慢速原因就隐藏在处理代码之中。本文将分析两个由 Postgres 支持的 Express 端点：一个可在 2.24 秒内返回用户的订单信息，另一个用于生成报告的端点则需要约 29 秒。解决这些问题的方法既不需要使用 Redis，也不需要额外的服务器或新的架构；仅仅通过纠正五种常见错误就能将两者的响应时间都控制在 200 毫秒以内。读完本文后，你应该能够在自己编写的代码中发现类似的模式，并知道该选择哪种查询方式。

如果您希望在修改任何代码之前找到性能瓶颈的通用方法，我们的关于如何找出慢速 Node.js 接口中的真正瓶颈指南涵盖了性能分析的相关内容。而本文则重点讨论具体的查询模式。

被深入分析的两个接口

第一个处理程序 GET /orders/:userId 会先加载用户的订单列表，然后遍历每个订单以获取其明细项，接着再遍历每个明细项以查询对应的产品。对于每一个明细项，它还会计算 SHA-256 哈希值，并将其作为 etag 字段附加上去。

// GET /orders/:userId
app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
  );
  const orders = ordersResult.rows;

  const enrichedOrders = [];

  for (const order of orders) {
    const itemsResult = await pool.query(
      `SELECT * FROM order_items WHERE order_id = ${order.id}`
    );
    const items = itemsResult.rows;

    const enrichedItems = [];
    for (const item of items) {
      const productResult = await pool.query(
        `SELECT * FROM products WHERE id = ${item.product_id}`
      );
      const product = productResult.rows[0];

      const etag = crypto
        .createHash('sha256')
        .update(JSON.stringify({ item, product }))
        .digest('hex');

      enrichedItems.push({ ...item, product, etag });
    }

    enrichedOrders.push({ ...order, items: enrichedItems });
  }

  res.json(enrichedOrders);
});

第二个处理程序用于生成订单状态汇总。它首先选取所有订单，同时通过子查询计算出每个用户的取消率，然后使用 JavaScript 遍历这些结果行，统计各状态的数量并存储每个用户的取消率。虽然该代码片段包含 SQL 语句，但处理程序本身是 JavaScript 编写的。

app.get('/reports/order-status-summary', async (req, res) => {
  const result = await pool.query(`
    SELECT
      o.id AS order_id,
      o.user_id,
      o.status,
      (
        SELECT round(
          count(*) FILTER (WHERE o2.status = 'cancelled')::numeric
            / NULLIF(count(*), 0),
          4
        )
        FROM orders o2
        WHERE o2.user_id = o.user_id
      ) AS cancellation_rate
    FROM orders o
  `);
  const rows = result.rows;

  const totalsByUser = {};
  const cancellationRateByUser = {};
  for (const row of rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] =
      (totalsByUser[row.user_id][row.status] || 0) + 1;
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

乍看之下两者都似乎合理，但只有在面对实际规模的数据量时问题才会显现。测试数据库包含：

• 用户：5,000 个
• 产品：2,000 种
• 订单：500,000 笔
• 订单明细：1,500,000 条

在如此大的数据量下，查询订单的接口需要 2.24 秒的时间，这对于一个简单的“我的订单”页面来说显然过长；而生成汇总信息的接口则需要 29.26 秒。

为何 GET /orders/:userId 需要 2.24 秒

用户点击“我的订单”后不应等待两秒之久。这种延迟源于三层相互叠加的冗余操作。

初始查询会扫描并排序整个表格

仅短短几行代码，最初的查询就已经存在多个问题。

const ordersResult = await pool.query(
  `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);

• orders.user_id上没有索引，因此Postgres必须逐行扫描全部500,000条记录才能找到大约102条属于该用户的记录。
• 同样也没有任何机制支持ORDER BY created_at DESC，所以在扫描完成后仍需在没有索引辅助的情况下对匹配的记录进行排序。
• userId是直接插入SQL字符串中的。由于该值直接来自URL，这存在SQL注入风险，同时也会使每个请求都变成内容各不相同的查询语句。

双重嵌套的N+1问题

大部分时间都耗费在循环处理上。

for (const order of orders) {
  const itemsResult = await pool.query(`SELECT * FROM order_items WHERE order_id = ${order.id}`);
  ...
  for (const item of items) {
    const productResult = await pool.query(`SELECT * FROM products WHERE id = ${item.product_id}`);

• 这是典型的N+1模式，且嵌套了两次。大约102个订单会生成102次商品查询，而约500件商品又会产生大约500次产品查询。由于每个await操作都会等待前一个完成，且每次都要承担完整的网络延迟，因此总共需要超过600次与Postgres的往返请求。实际上使用JOIN或WHERE id = ANY(...)只需1到3次查询就能获取相同的数据。
• order_items.order_id没有索引，因此102次商品查询中的每一次都不得不单独读取150万行数据。

毫无意义的哈希处理，阻塞事件循环

最后一个问题属于CPU运算而非I/O操作。

const etag = crypto.createHash('sha256').update(JSON.stringify({ item, product })).digest('hex');

每个项目都会同步计算哈希值，每次请求大约要执行500次。由于Node.js在单线程上运行JavaScript，每一次计算都会阻塞事件循环，进而延迟进程正在处理的其它请求。更糟糕的是，这些计算结果从未被用于缓存或条件请求，因此所有的努力都毫无意义。我们在关于process.nextTick如何阻塞事件循环的文章中解释了为何阻塞该线程会带来如此高昂的代价。

总体而言，延迟是由600多次往返操作、数次完整表扫描以及数百次哈希计算共同叠加而成的。

为何汇总报告需要大约29秒

如果用户打开订单摘要后等待近半分钟仍无法显示内容，很可能会认为页面出错了。问题的根源在于某一条 SQL 语句。

SELECT
  o.id AS order_id, o.user_id, o.status,
  (
    SELECT round(
      count(*) FILTER (WHERE o2.status = 'cancelled')::numeric / NULLIF(count(*), 0),
      4
    )
    FROM orders o2
    WHERE o2.user_id = o.user_id
  ) AS cancellation_rate
FROM orders o

1. 每个外部行都会触发一次相关子查询。外部查询会返回全部 500,000 笔订单，Postgres 会针对每一笔订单重新执行基于该行 user_id 过滤后的 orders o2 的子查询。由于平均每名用户有 100 笔订单，因此每名用户的相同取消率需要被重新计算约 100 次，总计约有 500,000 次子查询执行。
2. 没有索引来优化重复查询。由于 orders.user_id 上不存在索引，所有这些查询都需要进行全表扫描。虽然添加索引可以降低每次查询的成本，但无法避免重复计算相同结果所带来的资源浪费。

过大的数据量在 JavaScript 中被再次聚合。全部500,000行数据，每行都包含计算出的比率，会通过网络传输到Node端，然后Node会遍历这些数据来构建totalsByUser和cancellationRateByUser。而Postgres本可以通过GROUP BY一次性完成这种聚合操作。

计算成本与错误的数值成正比。本应与5,000名用户相关的处理工作实际上却要与500,000笔订单相关，效率大约低了100倍。这就是为什么该接口的响应时间会达到26到29秒，而非大约十分之一秒的原因。

第一步：添加与访问模式相匹配的索引

索引通常是成本最低且最有效的初步解决方案。此处需要创建两个索引：一个是在 orders (user_id, created_at DESC) 上的复合索引，以便通过该索引同时实现过滤和排序功能；另一个是在 order_items (order_id) 上的索引，用于查询订单项。下面的命令会在 Postgres 容器中创建这两个索引；IF NOT EXISTS 机制确保可以安全地重复执行该命令。

docker exec slow-api-postgres psql -U postgres -d shop -c "
CREATE INDEX IF NOT EXISTS idx_orders_user_id_created_at ON orders (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items (order_id);
"

复合索引中的列顺序很重要。将 user_id 放在首位可以让 Postgres 直接定位到某个用户的行，而且由于这些行已经在索引中按 created_at DESC 的顺序存储，因此完全无需再进行排序操作。在数据量较大的生产表上，建议使用 CREATE INDEX CONCURRENTLY，以避免索引创建过程阻塞写入操作。

有了这些索引之后，两个处理程序都可以被重写为批量获取数据，让数据库负责聚合操作。

使用批量查询重写 GET /orders/:userId

新版本仅执行三次查询：首先通过参数化查询获取用户的订单，收集这些订单的ID；接着使用 order_id = ANY($1) 获取所有相关商品信息；随后通过 Set 去重商品ID，最后再执行一次查询加载这些商品。所有结果会在内存中通过两次 Map 查找合并起来，同时针对没有订单的用户会提前返回。

app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  );
  const orders = ordersResult.rows;

  if (orders.length === 0) {
    return res.json([]);
  }

  const orderIds = orders.map((o) => o.id);
  const itemsResult = await pool.query(
    'SELECT * FROM order_items WHERE order_id = ANY($1)',
    [orderIds]
  );
  const items = itemsResult.rows;

  const productIds = [...new Set(items.map((i) => i.product_id))];
  const productsResult = await pool.query(
    'SELECT * FROM products WHERE id = ANY($1)',
    [productIds]
  );
  const productsById = new Map(productsResult.rows.map((p) => [p.id, p]));

  const itemsByOrderId = new Map();
  for (const item of items) {
    const enrichedItem = { ...item, product: productsById.get(item.product_id) };
    if (!itemsByOrderId.has(item.order_id)) {
      itemsByOrderId.set(item.order_id, []);
    }
    itemsByOrderId.get(item.order_id).push(enrichedItem);
  }

  const enrichedOrders = orders.map((order) => ({
    ...order,
    items: itemsByOrderId.get(order.id) || [],
  }));

  res.json(enrichedOrders);
});

具体改动如下：

1. 在 orders (user_id, created_at DESC) 上创建的复合索引使得 WHERE 和 ORDER BY 操作能够通过索引扫描完成，而无需对50万行数据进行顺序扫描和排序。

order_items (order_id) 的索引意味着查询项目时不再需要扫描150万行数据。

N+1查询模式被简化为三个查询：一个用于查询订单，一个用于查询所有订单中的项目，另一个用于查询所有不同的产品。原本需要600多次序列化往返的查询现在仅需三次。

$1这样的占位符取代了字符串插值方式，从而堵住了SQL注入的漏洞。需要注意的是，在node-postgres中每次执行仍会为未命名的参数化查询生成新的执行计划；若希望Postgres重复使用同一执行计划，应使用带名称的预处理语句。

不再需要为每个项目计算SHA-256哈希值，从而消除了事件循环中无意义的阻塞操作。

一个实际注意事项：删除etag字段会改变响应的结构。在应用此更改之前，请确认没有客户端依赖该字段。

使用GROUP BY重写报表

优化后的报表会执行两个针对性的聚合查询：一个按 user_id 和 status 分组以统计数量，另一个按 user_id 分组以计算取消率。JavaScript仅负责将已聚合的行重新整理为响应格式。与之前一样，处理逻辑仍是嵌入了SQL的JavaScript代码。

// GET /reports/order-status-summary (OPTIMIZED / "after")
app.get('/reports/order-status-summary-optimized', async (req, res) => {
  const statusResult = await pool.query(`
    SELECT user_id, status, count(*)::int AS count
    FROM orders
    GROUP BY user_id, status
  `);

  const rateResult = await pool.query(`
    SELECT
      user_id,
      round(
        count(*) FILTER (WHERE status = 'cancelled')::numeric
          / NULLIF(count(*), 0),
        4
      ) AS cancellation_rate
    FROM orders
    GROUP BY user_id
  `);

  const totalsByUser = {};
  for (const row of statusResult.rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] = row.count;
  }

  const cancellationRateByUser = {};
  for (const row of rateResult.rows) {
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

变化内容及其重要性：

1. 相关的子查询变成了 GROUP BY user_id。 Postgres会在一次聚合操作中为每个用户计算一次比率：从大约500,000次计算减少到5,000次。
2. 结果集从500,000行减少到大约5,000至20,000行。 Postgres会为每个 (user_id, status) 对返回一行用于统计数量，再为每个用户返回一行用于计算比率，且没有重复的计算列。

两个独立的查询取代了单行聚合查询。每个查询只需扫描一次orders表，而无需为每一行都使用嵌套查询。

节点不再进行重新聚合。JavaScript中不会运行任何计数循环；处理程序只需将分组后的行映射到对应的键值。

处理时间从大约26秒降至29秒，进一步缩短到约0.1至0.19秒，速度提升了150到260倍。经测试，新的接口为所有5,000名用户返回的totalsByUser和cancellationRateByUser数值与原有接口一致。这种等价性验证值得借鉴：每当为提升速度而重写查询时，应在替换之前将结果与旧版本进行比较。

这两种聚合方式在每次请求时都会读取整个orders表。在当前规模下这没有问题，但如果表格持续增长，使用FILTER子句、物化视图或定期刷新的汇总表进行单次查询就能更高效地生成报表。

关键要点

• 为用于过滤和排序的列建立索引，并使复合索引的顺序与查询逻辑一致：先是等值比较列，再是排序列。
• 将循环中的任何await db.query()操作视为危险信号，应改用JOIN或ANY($1)等批量查询方式。
• 对于来自请求的参数，始终使用参数化查询。
• 注意那些会为每一行重新计算本应针对每个实体仅计算一次的结果的相关子查询。
• 让数据库负责聚合操作，只返回响应实际需要的行数据。

移除没有调用方的同步 CPU 任务，并在切换之前确认优化后的接口能返回相同的结果。