N+1 Query Problem & Fix Patterns

The N+1 problem occurs when you query the main table once (1 query), then for each of the N rows you query the related table (N queries), resulting in 1 + N queries total. In list or detail views this leads to many small queries, high latency, and database load. This article explains the cause, how to detect it, and fixes using JOIN, IN, or lazy loading with batch IN.

Overview

• Cause: For example, SELECT * FROM orders returns N orders, then in a loop you run SELECT * FROM order_item WHERE order_id = ? for each order id — 1 + N queries.
• Impact: High latency, many connections, inflated QPS. In MyBatis this often appears when mapping a parent entity with a collection (e.g. one-to-many Order -> items) and the collection is loaded on access without a single bulk load.
• Solutions: Either fetch everything in one SQL (JOIN + ResultMap or nested result), or fetch the parent once, then one IN query for all related rows, and assemble in memory. Avoid looping with one query per parent row.

Example

Example 1: Classic N+1

JavaCopy
List<Order> orders = orderMapper.selectAll();  // 1 query
for (Order o : orders) {
    List<OrderItem> items = orderItemMapper.selectByOrderId(o.getId());  // N queries
    o.setItems(items);
}
// Total: 1 + N queries

Example 2: Fix with JOIN + ResultMap (recommended)

XMLCopy
<resultMap id="OrderWithItems" type="Order" autoMapping="true">
  <id column="order_id" property="id"/>
  <collection property="items" ofType="OrderItem" autoMapping="true">
    <id column="item_id" property="id"/>
  </collection>
</resultMap>
<select id="selectOrderWithItems" resultMap="OrderWithItems">
  SELECT o.id AS order_id, o.user_id, o.amount,
         i.id AS item_id, i.order_id, i.product_id, i.qty
  FROM orders o
  LEFT JOIN order_item i ON i.order_id = o.id
  WHERE o.user_id = #{userId}
</select>

• One query returns orders and items. MyBatis groups rows by order_id and builds Order with List<OrderItem>. No N+1.

Example 3: Two queries + IN (when parent set is large)

XMLCopy
<select id="selectByIds" resultType="OrderItem">
  SELECT * FROM order_item WHERE order_id IN
  <foreach collection="orderIds" item="id" open="(" separator="," close=")">#{id}</foreach>
</select>

JavaCopy
List<Order> orders = orderMapper.selectAll();
List<Long> orderIds = orders.stream().map(Order::getId).toList();
List<OrderItem> allItems = orderItemMapper.selectByIds(orderIds);
Map<Long, List<OrderItem>> byOrderId = allItems.stream()
    .collect(Collectors.groupingBy(OrderItem::getOrderId));
orders.forEach(o -> o.setItems(byOrderId.getOrDefault(o.getId(), List.of())));

• 1 query for orders, 1 query for items by IN, then group in memory. Total: 2 queries.

Example 4: When to use which

Scenario	Approach	Notes
Small parent and child sets	JOIN + ResultMap	Simple, no N+1
Large parent, large child	Two queries + IN + in-memory assembly	Avoid huge JOIN result set
Lazy loading required	Lazy load + batch IN per request/page	Avoid N single-row queries when accessing collections

Core Mechanism / Behavior

• ResultMap collection: Rows from a JOIN are grouped by the parent id. All rows with the same order_id become one Order with a List<OrderItem>. Use distinct column aliases (e.g. order_id, item_id) to avoid collisions.
• IN query: WHERE order_id IN (?,?,?) fetches all related rows in one go. Group by foreign key in the application and attach to parents. Keep IN list size reasonable (e.g. < 1000); chunk if larger.

Key Rules

• For any "parent + collection" loading, prefer one bulk load (JOIN or IN) instead of looping over parent rows.
• With JOIN, use clear aliases and correct column mappings in the ResultMap.
• If using lazy loading, load collections in batch (e.g. IN for the current page) instead of one query per parent.

What's Next

See ResultMap vs ResultType for nested mapping. See Batch Insert/Update when writing related data.