MyBatis实现列表查询优化的实用技巧分享_Java

订单列表只显示20条数据。查订单的sql加了分页，客户表也有主键索引，日志里却连续出现20条查客户的语句。

问题往往藏在组装返回值的循环里：

var page = mapper.page(afterid);
var result = new arraylist<row>();
for (order order : page) {
    string name = mapper.customername(
        order.customerid());
    result.add(new row(
        order.id(), displayname(name)));
}

这段代码每拿到一个订单，就调用一次 customername。当这页订单对应20个不同客户时，一次列表读取会执行21次查询：先查订单，再查20次客户。这就是常见的n+1查询：1次主查询，后面跟着n次关联查询。

改法是先收齐这页的客户id，一次查回名称，再按订单顺序组装。 下面用mybatis 3.5.19实际跑这两种写法，普通页的查询次数从21次降到了2次，返回的20条订单完全一致。

分页保留，把查客户移到循环外

订单查询继续只取当前页，不需要为了批量补名称，把所有订单都读出来。

select id, customer_id
from orders
where id > #{afterid}
order by id
limit 20

拿到这一页后，收集客户id并去重。接下来按这些id查客户，用map保存“客户id → 客户名称”的对应关系。

服务层改成下面这样：

static list<row> batch(
        ordermapper mapper, int afterid) {
    list<order> page = mapper.page(afterid);
    // 空页直接返回，不生成 in ()。
    if (page.isempty()) return list.of();

    list<integer> ids = page.stream()
        .map(order::customerid).distinct().tolist();
    map<integer, string> names = new hashmap<>();
    for (customer customer : mapper.customers(ids)) {
        names.put(customer.id(), customer.name());
    }

    var result = new arraylist<row>();
    // 客户查询刻意倒序返回，仍按订单页的顺序组装。
    for (order order : page) {
        result.add(new row(order.id(),
            displayname(names.get(order.customerid()))));
    }
    return result;
}

mapper.customers(ids) 使用 foreach 生成 in 条件，查询部分如下：

select id, name from customers where id in
<foreach collection="ids" item="id"
         open="(" separator="," close=")">
    #{id}
</foreach>
order by id desc

mapper参数使用 @param("ids") 命名。#{id} 绑定预处理参数，不把id直接拼成sql字符串。注解mapper里的动态sql需要放在 <script> 中，文末完整代码已经包含它。mybatis动态sql文档有对应用法。

这里故意让客户按id倒序返回，订单仍按id正序。这样可以检验：组装代码有没有错误地把两个列表按下标配对。

查询返回的先后顺序不能用来判断客户属于哪个订单，客户id才可以。 map查找用的是id，最后的循环走的是原订单列表，所以客户查询顺序变化不会打乱页面。

少查19次，也不能少掉一条订单

我给程序准备了20条订单，其中第7条指向的客户资料不存在。两种方案都把它显示为“客户资料缺失”，保留这条订单。

实际输出的结果是：

普通页：逐条 21 次，批量 2 次
20 条订单的顺序与名称一致：true
第 7 条：row[orderid=7, customername=客户资料缺失]

如果批量查询后直接遍历客户列表生成页面行，这条订单就会消失。缺失关联数据的处理必须沿用原来的业务约定；这里采用占位名称，项目要求报错时就保留报错行为。

空页也检查了：订单查询没有结果，直接返回空列表，总共1次查询。不要继续向mapper传空集合，生成一条没有有效 in 条件的sql。

还有两个容易漏掉的条件。原查询如果带租户或数据权限限制，批量查询也要保留；如果页面要求订单与客户资料来自同一快照，需要按业务要求处理事务和数据库隔离，不能把拆成两次读取默认当作一致性保证。

本例一页只有20个id。把这个方法用到大批量数据时，需要根据数据库参数限制和执行计划决定是否分段，不能把整张表的id一次塞进 in。

开了一级缓存，为什么还值得改？

20条订单不一定每次都查21次。

我把第2条订单的客户改成第1条的客户，再跑逐条查询。同一个 sqlsession 内，第二次相同的客户查询命中了一级缓存，实际查询从21次变成了20次：

重复客户：逐条 20 次，批量 2 次

程序保留了 localcachescope=session，没有为了凑出21次而关闭缓存。两种方案各开一个新会话，避免前一组查询给后一组预热。

一级缓存能减少同一会话里的重复查询，但这页剩余的客户id仍然不同，缓存不能把它们自动合成一条批量sql。mybatis配置文档说明了会话缓存的范围。

所以排查时要看实际执行的sql，不能数一下mapper调用，就把它当成数据库查询次数。下面的程序在 statementhandler.query 上计数，缓存命中的查询不会经过这个位置；建表和准备数据的语句不计入结果。

这次改动收掉了循环里的逐条查询。至于线上接口能快多少，还要在相同数据和负载下测耗时，21次变2次不能直接换算成快了10倍。

能join，为什么还要分两次查？

如果只是订单关联唯一客户、取一个名称，left join 完全可以作为候选，未必需要先查订单再查客户。选哪种写法，要结合现有mapper职责、索引和执行计划。

这里保留两次查询，是为了演示如何改掉现有服务层里的循环查库。关联对象换成订单明细时，还要重新考虑分页：一笔订单有多条明细，连接后 limit 20 限制的是结果行数，未必还能得到20笔订单。先确定订单页，再取关联数据，可以保留按订单分页的含义。

下次遇到列表接口查询多，先找到循环中的数据库调用：这批id能否在进入循环前收齐？如果可以，通常就有机会把逐条读取合并起来。改完再用同一页数据核对查询次数、顺序和缺失记录，别只看日志变短了。

把代码跑一遍

下面是完整的maven示例，只需要jdk 21和maven，不需要另装数据库。我运行时用的是jdk 21.0.11、mybatis 3.5.19、h2 2.3.232；h2使用内存库，启动时创建上述20条订单。

新建目录，放入这两个文件：

mybatis-query-lab/
├── pom.xml
└── src/main/java/demo/querylab.java

pom.xml：

<project xmlns="http://maven.apache.org/pom/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"
         xsi:schemalocation="http://maven.apache.org/pom/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelversion>4.0.0</modelversion>
  <groupid>demo</groupid>
  <artifactid>mybatis-query-lab</artifactid>
  <version>1.0</version>
  <properties>
    <maven.compiler.release>21</maven.compiler.release>
    <project.build.sourceencoding>utf-8</project.build.sourceencoding>
  </properties>
  <dependencies>
    <dependency>
      <groupid>org.mybatis</groupid>
      <artifactid>mybatis</artifactid>
      <version>3.5.19</version>
    </dependency>
    <dependency>
      <groupid>com.h2database</groupid>
      <artifactid>h2</artifactid>
      <version>2.3.232</version>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupid>org.apache.maven.plugins</groupid>
        <artifactid>maven-compiler-plugin</artifactid>
        <version>3.13.0</version>
      </plugin>
      <plugin>
        <groupid>org.codehaus.mojo</groupid>
        <artifactid>exec-maven-plugin</artifactid>
        <version>3.5.0</version>
        <configuration>
          <mainclass>demo.querylab</mainclass>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

src/main/java/demo/querylab.java：

package demo;

import java.sql.statement;
import java.util.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.datasource.unpooled.unpooleddatasource;
import org.apache.ibatis.executor.statement.statementhandler;
import org.apache.ibatis.mapping.environment;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.*;
import org.apache.ibatis.transaction.jdbc.jdbctransactionfactory;

public class querylab {
    public record order(int id, int customerid) {}
    public record customer(int id, string name) {}
    public record row(int orderid, string customername) {}

    public interface ordermapper {
        @select("""
            select id, customer_id from orders
            where id > #{afterid} order by id limit 20
            """)
        @constructorargs({
            @arg(column = "id", javatype = int.class),
            @arg(column = "customer_id", javatype = int.class)
        })
        list<order> page(@param("afterid") int afterid);

        @select("select name from customers where id = #{id}")
        string customername(@param("id") int id);

        @select("""
            <script>
            select id, name from customers where id in
            <foreach collection="ids" item="id"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
            order by id desc
            </script>
            """)
        @constructorargs({
            @arg(column = "id", javatype = int.class),
            @arg(column = "name", javatype = string.class)
        })
        list<customer> customers(@param("ids") list<integer> ids);
    }

    // 本文程序单线程运行，只统计真正进入 statementhandler 的查询。
    @intercepts(@signature(type = statementhandler.class,
        method = "query", args = {statement.class, resulthandler.class}))
    public static class querycounter implements interceptor {
        final list<string> sql = new arraylist<>();
        public object intercept(invocation invocation) throws throwable {
            var handler = (statementhandler) invocation.gettarget();
            sql.add(handler.getboundsql().getsql()
                .replaceall("\\s+", " ").trim());
            return invocation.proceed();
        }
    }

    static list<row> onebyone(
            ordermapper mapper, int afterid) {
        var page = mapper.page(afterid);
        var result = new arraylist<row>();
        for (order order : page) {
            string name = mapper.customername(
                order.customerid());
            result.add(new row(
                order.id(), displayname(name)));
        }
        return result;
    }

    static list<row> batch(
            ordermapper mapper, int afterid) {
        list<order> page = mapper.page(afterid);
        // 空页直接返回，不生成 in ()。
        if (page.isempty()) return list.of();

        list<integer> ids = page.stream()
            .map(order::customerid).distinct().tolist();
        map<integer, string> names = new hashmap<>();
        for (customer customer : mapper.customers(ids)) {
            names.put(customer.id(), customer.name());
        }

        var result = new arraylist<row>();
        // 客户查询刻意倒序返回，仍按订单页的顺序组装。
        for (order order : page) {
            result.add(new row(order.id(),
                displayname(names.get(order.customerid()))));
        }
        return result;
    }

    static string displayname(string name) {
        return name == null ? "客户资料缺失" : name;
    }

    static list<row> run(sqlsessionfactory factory, querycounter counter,
                         boolean usebatch, int afterid) {
        counter.sql.clear();
        // 两种方案各用一个新会话，避免前一组预热后一组的缓存。
        try (sqlsession session = factory.opensession()) {
            var mapper = session.getmapper(ordermapper.class);
            return usebatch ? batch(mapper, afterid)
                            : onebyone(mapper, afterid);
        }
    }

    static void require(boolean ok, string message) {
        if (!ok) throw new assertionerror(message);
    }

    public static void main(string[] args) throws exception {
        var ds = new unpooleddatasource(
            "org.h2.driver", "jdbc:h2:mem:query_lab", "sa", "");
        // 保持此连接打开，让两个 sqlsession 使用同一份内存数据。
        try (var keeper = ds.getconnection();
             var setup = keeper.createstatement()) {
            setup.execute("create table orders "
                + "(id int primary key, customer_id int not null)");
            setup.execute("create table customers "
                + "(id int primary key, name varchar(60) not null)");
            for (int i = 1; i <= 20; i++) {
                setup.executeupdate("insert into orders values ("
                    + i + "," + (100 + i) + ")");
                // 故意缺少第 7 个客户，检查是否误删了订单。
                if (i != 7) setup.executeupdate(
                    "insert into customers values (" + (100 + i)
                    + ",'客户" + i + "')");
            }

            var env = new environment("lab", new jdbctransactionfactory(), ds);
            var config = new configuration(env);
            config.setlocalcachescope(localcachescope.session);
            var counter = new querycounter();
            config.addinterceptor(counter);
            config.addmapper(ordermapper.class);
            var factory = new sqlsessionfactorybuilder().build(config);

            var before = run(factory, counter, false, 0);
            int beforecount = counter.sql.size();
            var after = run(factory, counter, true, 0);
            int aftercount = counter.sql.size();
            require(beforecount == 21 && aftercount == 2, "查询数不符");
            require(before.equals(after) && after.size() == 20, "返回结果改变");
            for (int i = 0; i < after.size(); i++) {
                string expected = i == 6 ? "客户资料缺失" : "客户" + (i + 1);
                require(after.get(i).equals(new row(i + 1, expected)),
                    "订单顺序或客户名称不符");
            }
            system.out.printf("普通页：逐条 %d 次，批量 %d 次%n",
                beforecount, aftercount);
            system.out.println("20 条订单的顺序与名称一致：true");
            system.out.println("第 7 条：" + after.get(6));
            system.out.println("批量方案实际执行的 sql：");
            counter.sql.foreach(system.out::println);

            var empty = run(factory, counter, true, 20);
            require(empty.isempty() && counter.sql.size() == 1, "空页多查");
            system.out.println("空页：1 次查询，0 条订单");

            setup.executeupdate("update orders set customer_id = 101 where id = 2");
            var repeatedbefore = run(factory, counter, false, 0);
            int repeatedcount = counter.sql.size();
            var repeatedafter = run(factory, counter, true, 0);
            require(repeatedbefore.equals(repeatedafter)
                && repeatedafter.size() == 20
                && repeatedafter.get(1).customername().equals("客户1")
                && counter.sql.size() == 2 && repeatedcount == 20,
                "重复客户场景不符");
            system.out.printf("重复客户：逐条 %d 次，批量 %d 次%n",
                repeatedcount, counter.sql.size());
        }
    }
}

在 pom.xml 所在目录运行：