SoFunction
Updated on 2025-03-04

Solution to N+1 problem in MyBatis

The N+1 problem refers to when performing a one-to-many query, the application first executes a query statement to obtain the result set (i.e. +1), and then executes N additional query statements for each result to obtain the associated data. This problem usually occurs when dealing with associations in ORM frameworks such as MyBatis or Hibernate, especially one-to-many or many-to-many relationships.

Give an example:

Suppose there are two tablesUserandOrder, one of the users (User) There may be multiple orders (Order), this is a one-to-many relationship.

Table structure：

CREATE TABLE User (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
​
CREATE TABLE Order (
    id INT PRIMARY KEY,
    user_id INT,
    item VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES User(id)
);

Java Entity Class：

public class User {
    private int id;
    private String name;
    private List<Order> orders;
    // Getters and Setters
}
​
public class Order {
    private int id;
    private String item;
    private int userId;
    // Getters and Setters
}

Query requirements: We want to query all users and their corresponding order list.

N+1 problem manifestation:

first step:MyBatis First executes a query to get all users.

SELECT * FROM User;

This is the "+1" in the query.

Step 2: Then, for each user that is queryed, MyBatis executes another query to get the order list of this user:

SELECT * FROM Order WHERE user_id = ?;

If there are N users, N such queries will be executed.

The problem lies: This method will cause a large number of database queries when there are a large number of users (i.e., N is very large), seriously affecting performance.

How to solve the N+1 problem?

There are several ways to solve the N+1 problem in MyBatis, and here are several common solutions:

1. Use JOIN statement to perform a one-time query

The most direct solution is to use SQLJOINStatement, obtain all users and their corresponding orders at one time, avoiding multiple queries.

Example:

SQL Query：

SELECT  AS user_id,  AS user_name,  AS order_id,  AS order_item
FROM User u
LEFT JOIN Order o ON  = o.user_id;

MyBatis configuration：

<resultMap  type="User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="item" column="order_item"/>
    </collection>
</resultMap>
​
<select  resultMap="UserOrderResultMap">
    SELECT  AS user_id,  AS user_name,  AS order_id,  AS order_item
    FROM User u
    LEFT JOIN Order o ON  = o.user_id;
</select>

Effect: This code usesLEFT JOINGet all users and their corresponding orders at one time to avoid the N+1 problem.

2. Use collection for nested result mapping

In some cases, you may want to use nested result maps to handle one-to-many relationships. By MyBatis<collection>Tags, you can map query results to a collection to avoid N+1 problems.

Example:

<resultMap  type="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="id"/>
        <result property="item" column="item"/>
    </collection>
</resultMap>
​
<select  resultMap="UserResultMap">
    SELECT , ,  AS order_id, 
    FROM User u
    LEFT JOIN Order o ON  = o.user_id;
</select>

  • Effect:use<collection>Tags can map order information toUserThe object'sordersIn the collection attribute, avoid multiple queries.

3. Delay loading

MyBatis also supports Lazy Loading, that is, the associated data is loaded only when needed. This approach does not completely eliminate the N+1 problem, but can improve performance in some scenarios, especially when you don't always need to load all associated data.

Configuration example:

Enable lazy loading in the MyBatis configuration file:

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

  • Effect: Load associated data only when needed to reduce unnecessary queries. However, when accessing a large amount of associated data, N+1 issues still occur.

4. Use IN to query batches to get associated data

A common optimization strategy is to first obtain all user data at once and then useINQuery batch to obtain associated data. Although this method is not a one-time query, it is much more efficient than querying one by one.

Example:

First get all users：

SELECT * FROM User;

Then get orders from all users：

SELECT * FROM Order WHERE user_id IN (SELECT id FROM User);

  • Effect: This method reduces the number of queries to the database, but still requires manual processing of the association mapping of query results.

Summarize

  • N+1 Problem: In a one-to-many relationship query, the application first executes a query to obtain the main data, and then performs N additional queries for each record to obtain the associated data, resulting in a large number of database queries, affecting performance.

Solution:

useJOINSentences are searched at one time.

Using MyBatis<collection>Tags are used for nested result mapping.

Configuring Lazy Loading reduces unnecessary queries.

useINQuery batch to obtain associated data.

Through reasonable SQL design and MyBatis mapping configuration, N+1 problems can be effectively solved and the performance of the application can be optimized.

This is the end of this article about the solution to the N+1 problem in MyBatis. For more related content on MyBatis, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!