DEV Community

Cover image for [🗄️DataBase] N+1 Query Problem
Mercy
Mercy

Posted on

[🗄️DataBase] N+1 Query Problem

#webdev #learning #database #backend

在做後端開發時，一定遇過這種情況：
👉 API 很單純
👉 查的資料也不多
👉 但效能就是「莫名其妙很慢」

認真查 log 才發現，SQL 被打了幾十次、幾百次，但你明明只寫了一個查詢
這種問題，通常不是你寫錯邏輯，而是踩到了N+1 Query Problem

所以什麼是 N+1 Query Problem 呢?

指的就是
為了取得一組資料，系統額外又發出了 N 次資料庫查詢

準確一點來說是
我們為了取得一組資料，會做兩件事

  1. 先執行 1 次查詢取得主資料
  2. 然後對每一筆資料，再各自發出 1 次查詢

以生活化的例子來看，想像你要查全班同學的成績：

  1. 你先去教務處問：「請問 301 班有哪些學生？」→ 拿到 30 個名字
  2. 然後你對每一個同學，都跑去教務處問一次：「XXX 的成績是多少？」

第 1 次：問全班名單
第 2 次：問 小明 的成績
第 3 次：問 小華 的成績
第 4 次：問 小美 的成績
...
第 31 次：問第 30 個同學 的成績
總共 1 + 30 = 31 次 跑教務處

這就是 N+1 —— N=30 個同學，多出 30 次額外查詢

比較聰明做法是
第 1 次就一次把全部同學的成績單拿回來，只要跑 1 趟

以程式碼來看

假設今天我們要做一個 API 回傳「商店列表 + 每家店的機器數量」
前端會拿到像這樣的資料：

{
  "total": 3,
  "items": [
    {
      "id": "A",
      "name": "Store A",
      "machineCount": 10
    },
    {
      "id": "B",
      "name": "Store B",
      "machineCount": 3
    },
    {
      "id": "C",
      "name": "Store C",
      "machineCount": 25
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

第 1 次查詢：取得所有商店

var (items, total) = await _repo.GetStoresAsync(...);    // → SELECT * FROM Stores
Enter fullscreen mode Exit fullscreen mode

第 2～N+1 次查詢：對每家店逐一問「你有幾台機器？」

foreach (var s in items)
{
    var count = await _repo.CountMachinesByStoreAsync(s.Id);  // → SELECT COUNT(*) FROM Machines WHERE StoreId = @p
}
Enter fullscreen mode Exit fullscreen mode

如果分頁回傳 20 家店，那就是：
1 + 20 = 21 次 SQL 查詢 (N=20 家店，每家多 1 次)

那應該怎麼解決呢?

第一種解決方式: JOIN

這種情境，其實可以用一條 SQL 就解決
先把 Stores 跟 Machines join 起來，並用 COUNT 算出每家店的機器數量

SELECT
    s.Id,
    s.Name,
    COUNT(m.Id) AS MachineCount
FROM Stores s
LEFT JOIN Machines m ON m.StoreId = s.Id
GROUP BY s.Id, s.Name;
Enter fullscreen mode Exit fullscreen mode

對應到 C#（EF / LINQ）
第 1 次：取得所有商店（不變）

var (items, total) = await _repo.GetStoresAsync(...);    // → SELECT * FROM Stores
Enter fullscreen mode Exit fullscreen mode

第 2 次：一次把所有商店的機器數量算完

var items = await query
    .OrderBy(s => s.Code)
    .Skip((page - 1) * limit)
    .Take(limit)
    .GroupJoin(
        _db.Machines,
        s => s.Id,
        m => m.StoreId,
        (s, machines) => new StoreDto
        {
            Id = s.Id,
            Name = s.Name,
            MachineCount = machines.Count()
        })
    .ToListAsync();
Enter fullscreen mode Exit fullscreen mode

第二種解決方式: batch query（批量查詢)

透過 GROUP+COUNT，一次把所有商店的機器數量算完

SELECT StoreId, COUNT(*)
FROM Machines
WHERE StoreId IN ('A', 'B', 'C', ...)
GROUP BY StoreId;
Enter fullscreen mode Exit fullscreen mode

對應到 C#
第 1 次：取得所有商店（不變）

var (items, total) = await _repo.GetStoresAsync(...);    // → SELECT * FROM Stores
Enter fullscreen mode Exit fullscreen mode

第 2 次：一次把所有商店的機器數量算完

var machineCounts = await _db.Machines
    .Where(m => storeIds.Contains(m.StoreId))    → WHERE "StoreId" IN (...)
    .GroupBy(m => m.StoreId)                    → GROUP BY "StoreId"
    .Select(g => new { StoreId = g.Key, Count = g.Count() })  → SELECT "StoreId", COUNT(*)
    .ToDictionaryAsync(x => x.StoreId, x => x.Count);
Enter fullscreen mode Exit fullscreen mode

不需要第 3 次了，直接用 Dictionary 查出答案

var count = machineCounts.GetValueOrDefault(someStoreId, 0);
Enter fullscreen mode Exit fullscreen mode

變成：1 + 1 = 2 次 SQL 查詢

等同於，用 SQL 來看：
原本（逐筆）：

SELECT COUNT(*) FROM Machines WHERE StoreId = 'A'   -- 第 1 次
SELECT COUNT(*) FROM Machines WHERE StoreId = 'B'   -- 第 2 次
SELECT COUNT(*) FROM Machines WHERE StoreId = 'C'   -- 第 3 次
...
Enter fullscreen mode Exit fullscreen mode

改 batch 後（一次問完）：

SELECT "StoreId", COUNT(*)
FROM Machines
WHERE "StoreId" IN ('A', 'B', 'C', ...)    ← 一次問全部
GROUP BY "StoreId"                         ← 分組各自算
Enter fullscreen mode Exit fullscreen mode

為什麼 JOIN 與 Batch 更快？

  • 網路只來回 1 次而不是 N 次
  • 每次資料庫查詢都有固定成本（連線、解析 SQL、交易開銷），N 次就把這些成本放大 N 倍
  • 資料庫對一次問很多筆有優化處理（索引、快取）

JOIN 與 Batch 的核心優化，其實都在做同一件事：
👉 把 N 次查詢，變成一次（或少數幾次）查詢

下次在撈資料前可以先想想：

  • 這段會不會產生 N 次 SQL？
  • 能不能在進迴圈前就一次查完？

Top comments (0)