N+1 query: kẻ giết hiệu năng âm thầm của ORM và cách dập tắt — hoatq.dev

cat blog/.md

N+1 query: kẻ giết hiệu năng âm thầm của ORM và cách dập tắt

date: tags: backend, database, orm, sqlalchemy, performance

Có một bug tôi từng debug suốt cả buổi chiều. Endpoint GET /orders của một service trả về danh sách 50 đơn hàng gần nhất, kèm thông tin buyer và mỗi đơn hàng có vài line items. Trên local mọi thứ chạy nhanh, nhưng lên staging p95 lên 4.5s. Mở pg_stat_statements ra xem, không có query nào nặng. Mở APM thì thấy endpoint đó bắn 217 query cho một request.

50 đơn → 50 query lấy buyer → 50 query đếm line items → vài cái phụ. Tôi mới hiểu mình đang nhìn thẳng vào N+1 — kẻ giết hiệu năng kinh điển nhất của ORM mà ai dùng ORM rồi cũng gặp ít nhất một lần.

Bài này là những gì tôi học sau lần đó.

Vì sao gọi là N+1

Tên gọi đến từ pattern truy vấn: 1 query lấy danh sách N entity, rồi với mỗi entity lại thêm 1 query con để lazy-load quan hệ. Tổng cộng 1 + N query.

Code SQLAlchemy ngây thơ hay viết kiểu này:

async def list_orders(session: AsyncSession, limit: int = 50):
    stmt = select(Order).order_by(Order.created_at.desc()).limit(limit)
    result = await session.execute(stmt)
    orders = result.scalars().all()

    return [
        {
            "id": o.id,
            "buyer_name": o.buyer.full_name,  # lazy load
            "item_count": len(o.line_items),  # lazy load
        }
        for o in orders
    ]

Trông rất gọn. Nhưng o.buyer và o.line_items là relationship, và mặc định SQLAlchemy load lazy — tức là chỉ query khi bạn động đến nó. Vòng for chạm 50 lần, mỗi entity 2 lần lazy, ra 100 query phụ. Cộng query gốc thành 101.

Vấn đề tệ hơn ba lần so với một query JOIN tương đương:

  • Round-trip network — 100 lần đi-về với DB. Mỗi round-trip 1-2ms, cộng dồn ra 200ms chỉ riêng network.
  • Plan parsing — DB phải parse, plan, execute cho từng query nhỏ. Cache plan giúp chút nhưng không bù lại được.
  • Connection holding — connection bị giữ lâu hơn nhiều, pool nhanh cạn dưới tải.

Trên local thì không thấy vì latency DB ~0.1ms. Lên staging hoặc prod (DB ở vùng khác, hoặc qua PgBouncer transaction mode), 1ms latency × 200 query = trễ cả nửa giây cho một request.

Phát hiện N+1 trước khi nó lên prod

Đợi APM báo là quá muộn. Tôi quen vài cách bắt N+1 ngay từ dev:

1. Bật echo SQL trong test environment

engine = create_async_engine(DATABASE_URL, echo=True)

Chạy test integration, đếm log SQL. Nếu một test gọi 1 endpoint mà log ra >5 query thì phải xem xét.

2. Assert query count trong test

SQLAlchemy có thể đếm query qua event listener. Tôi hay viết helper:

from contextlib import contextmanager
from sqlalchemy import event

@contextmanager
def assert_query_count(engine, expected: int):
    count = 0
    def before_cursor(conn, cursor, statement, *args):
        nonlocal count
        count += 1
    event.listen(engine.sync_engine, "before_cursor_execute", before_cursor)
    try:
        yield
    finally:
        event.remove(engine.sync_engine, "before_cursor_execute", before_cursor)
        assert count == expected, f"Expected {expected} queries, got {count}"

Dùng:

async def test_list_orders_query_count(session):
    with assert_query_count(engine, 2):  # 1 cho orders, 1 cho relationships
        await list_orders(session, limit=50)

Test này fail ngay khi ai đó thêm field lazy mới — chặn N+1 ngay tại PR review.

3. Tooling chuyên dụng

  • sqlalchemy.dialects.postgresql.psycopg2 log với SQLALCHEMY_WARN_20=1
  • Django có django-silk hoặc django-debug-toolbar hiển thị query count theo request
  • APM (Datadog, New Relic) flag span có >N query xuống cùng DB

Cách 1: selectinload — chọn mặc định

Với SQLAlchemy 2.x async, selectinload là vũ khí hay dùng nhất:

from sqlalchemy.orm import selectinload

stmt = (
    select(Order)
    .options(
        selectinload(Order.buyer),
        selectinload(Order.line_items),
    )
    .order_by(Order.created_at.desc())
    .limit(50)
)
orders = (await session.execute(stmt)).scalars().all()

Cách này chạy đúng 2 query bổ sung:

-- query 1: orders
SELECT ... FROM orders ORDER BY created_at DESC LIMIT 50;

-- query 2: buyers (chỉ 1 query với IN)
SELECT ... FROM buyers WHERE id IN (1, 2, 3, ..., 50);

-- query 3: line items
SELECT ... FROM line_items WHERE order_id IN (1, 2, 3, ..., 50);

Từ 217 query xuống còn 3. Latency rớt từ 4.5s về 180ms — đúng cái mà tôi fix lần đó.

Vì sao selectinload thường tốt hơn joinedload? Vì nó không phình row khi quan hệ một-nhiều. Một order có 10 line items, nếu JOIN sẽ ra 10 row trùng order data, network và parsing tăng. selectinload tách thành query riêng với IN (...), gọn và đều.

Cách 2: joinedload — khi cần đúng 1 query

joinedload hợp với quan hệ một-một hoặc nhiều-một (mỗi order chỉ có 1 buyer):

from sqlalchemy.orm import joinedload

stmt = (
    select(Order)
    .options(joinedload(Order.buyer))
    .order_by(Order.created_at.desc())
    .limit(50)
)

SQL ra:

SELECT orders.*, buyers.*
FROM orders
LEFT JOIN buyers ON buyers.id = orders.buyer_id
ORDER BY orders.created_at DESC
LIMIT 50;

Đúng 1 query. Hợp khi bạn cần ít row, quan hệ tỷ lệ 1:1, và muốn tối thiểu round-trip.

Cảnh báo: dùng joinedload cho one-to-many, nhớ .unique() ở result, nếu không SQLAlchemy ném lỗi vì duplicate row. Và nhớ LIMIT không hoạt động đúng nữa — pagination kiểu này phải gói subquery, dễ ra lỗi tế nhị. Cá nhân tôi mặc định selectinload và chỉ dùng joinedload khi đo được nó nhanh hơn.

Cách 3: DataLoader pattern cho GraphQL / nested API

Nếu API là GraphQL hoặc REST resolver lồng nhau, eager-load không lúc nào cũng biết trước cần gì. Lúc này pattern DataLoader (do Facebook đặt tên) giải quyết tốt: gom các yêu cầu lẻ trong một event loop tick, gửi đi như một query IN.

Trong Python async, một implementation ngắn:

from collections import defaultdict
import asyncio

class BuyerLoader:
    def __init__(self, session):
        self.session = session
        self._queue: list[tuple[int, asyncio.Future]] = []
        self._scheduled = False

    async def load(self, buyer_id: int):
        fut = asyncio.get_event_loop().create_future()
        self._queue.append((buyer_id, fut))
        if not self._scheduled:
            self._scheduled = True
            asyncio.get_event_loop().call_soon(self._flush)
        return await fut

    def _flush(self):
        asyncio.create_task(self._do_flush())

    async def _do_flush(self):
        batch = self._queue
        self._queue = []
        self._scheduled = False

        ids = [b[0] for b in batch]
        stmt = select(Buyer).where(Buyer.id.in_(ids))
        rows = (await self.session.execute(stmt)).scalars().all()
        by_id = {b.id: b for b in rows}

        for buyer_id, fut in batch:
            fut.set_result(by_id.get(buyer_id))

Khi resolver order.buyer được gọi N lần trong cùng tick, tất cả gom thành 1 query duy nhất với IN (...). Đây là cách Apollo Server và Strawberry Python giải N+1 mà không cần code resolver biết trước.

Cách 4: denormalize có chủ ý

Cuối cùng — không phải N+1 nào cũng đáng chiến đấu. Nếu bạn cần item_count chứ không cần data line items, đừng load relationship làm gì:

# Thêm cột counter, update bằng trigger hoặc trong service layer
class Order(Base):
    item_count: Mapped[int] = mapped_column(default=0)

Hoặc tính bằng subquery 1 lần:

item_count_subq = (
    select(LineItem.order_id, func.count().label("cnt"))
    .group_by(LineItem.order_id)
    .subquery()
)

stmt = (
    select(Order, item_count_subq.c.cnt)
    .outerjoin(item_count_subq, item_count_subq.c.order_id == Order.id)
    .order_by(Order.created_at.desc())
    .limit(50)
)

Một query, chính xác cái cần. Denormalize counter chấp nhận trade-off storage + complexity ghi, đổi lấy đơn giản và nhanh khi đọc. Với endpoint list view truy cập nhiều, đáng làm.

Bẫy thường gặp

Vài thứ tôi từng vấp:

  • Lazy load trong serializer — Pydantic / marshmallow truy xuất relationship khi serialize, gây N+1 ngầm. Phải eager-load trước khi rời session, không phải khi response trả về.
  • session.expire_on_commit=True default — sau commit, lần truy xuất tiếp theo lazy-refresh từng object. Trong vòng for ra hàng trăm query. Tắt nó nếu không cần.
  • Refresh sau write — await session.refresh(obj) re-query để có giá trị mới. Trong vòng for là chết. Dùng RETURNING của PostgreSQL hoặc batch refresh.
  • __repr__ truy xuất relationship — debug log gọi repr(order), nếu repr in cả buyer.name, log một cái là một query phụ. Tôi học bài đắt vụ này khi bật DEBUG log trong production.

Kết

N+1 không phải vấn đề ORM ngu — nó là cái giá của tiện nghi. ORM cho phép viết order.buyer.full_name như đọc thuộc tính object thường, và phải đánh đổi bằng việc developer cần ý thức lúc nào dữ liệu thật sự được fetch.

Quy tắc tôi tự đặt cho team:

  1. Bất kỳ endpoint nào trả list — bắt buộc khai báo selectinload hoặc joinedload cho relationship được truy xuất.
  2. Test integration assert query count cho endpoint quan trọng.
  3. Production bật slow query log + APM span count; có cảnh báo khi 1 request bắn >10 query.
  4. Code review: nhìn thấy for x in items: x.foo mà foo là relationship, hỏi ngay “đã eager load chưa?”.

Bốn cái trên không phòng được mọi case, nhưng đủ chặn 90% N+1 thoát ra prod. Lần debug 4.5s xuống 180ms không phải là khoảnh khắc tự hào — đó là khoảnh khắc tôi nhận ra mình đáng lẽ chặn từ PR.

// reactions

lsrelated/

Connection pool sizing: vì sao tăng pool size lại làm app chậm hơn
backend, database, postgresql
UUID v7: ID có thứ tự thời gian, cứu B-tree index khỏi fragmentation
database, postgresql, performance
Cursor pagination: vì sao OFFSET càng ngày càng chậm và cách fix
backend, postgresql, performance

cat comments.log

← cd ../blog EOF
hoatq@dev : ~/blog $