mybatis批量查询效率对比

背景：

随着项目的运行，数据越来越多，系统部分功能运行越来越慢，优化势在必行。

快速释放系统数据库等资源占有，避免造成旱涝不均等。

这里主要举例说明下查询数据的处理思路，以及如何取舍。

1、普通程序：

for循环嵌套{
    selectByPrimaryKey();
	select();  
}
1
2
3
4

2、中级程序：

foreach拼接。
在MySql文档中也提到，如果要优化速度时，可以将许多小型操作组合到一个大型操作中。
1
2

3、中级+程序：

${}在程序中拼接好sql参数。
1

4、高级程序

PreparedStatement 手动拼接sql执行executeQuery。
1

5、说明：

上面四个模式是个人总结并非完全适用，要根据具体业务确定选用，编码复杂度依次增加。

6、实战耗时对比

由于是demo逻辑都写在了controller。
数据库windows版。
表只有三列。
只对比2、3、4的情况。
1
2
3
4

6.1、foreach拼接

6.1.1、controller层

@GetMapping("/queryForeach")
public List<User> queryForeach(User user){
    user.setLimit(80000);
    List<Long> idsList = userMapper.query(user)
        .stream().map(User::getId).collect(Collectors.toList());
    User user1 = new User();
    user1.setIdsList(idsList);
    long sum = 0L;
    for (int i = 0; i < 10; i++) {
        long s1 = System.currentTimeMillis();
        userMapper.queryForeach(user1);
        System.out.println("foreach拼接SQL>>>"+(System.currentTimeMillis()-s1));
        sum +=System.currentTimeMillis()-s1;
    }
    System.out.println("10次平均值>>>:"+sum/10);
    return null;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

6.1.2、dao层

List<User> queryForeach(User user);
1

6.1.3、xml

<select id="queryForeach" resultType="com.example.dto.User">
    select * from user
    <where>
        <if test=" idsList !=null and idsList.size !=0 ">
            and id in
            <foreach collection="idsList" item="item" separator="," open="(" close=")">
                #{item}
            foreach>
        if>
    where>
select>
1
2
3
4
5
6
7
8
9
10
11

6.1.4、输出结果

10次平均耗时：3072ms
1

6.2、${}拼接查询参数

6.2.1、controller层

@GetMapping("/queryForeach1")
public List<User> queryForeach1(User user){
    user.setLimit(80000);
    String ids = userMapper.query(user)
        .stream().map(item -> String.valueOf(item.getId()))
        .collect(Collectors.joining(",","(",")"));
    User user1 = new User();
    user1.setIds(ids);
    long sum = 0L;
    for (int i = 0; i < 10; i++) {
        long s1 = System.currentTimeMillis();
        userMapper.queryForeach1(user1);
        System.out.println("$拼接SQL>>>"+(System.currentTimeMillis()-s1));
        sum += System.currentTimeMillis()-s1;
    }
    System.out.println("10次平均值>>>:"+sum/10);
    return null;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

6.2.2、dao层

List<User> queryForeach1(User user1);
1

6.2.3、xml

<select id="queryForeach1" resultType="com.example.dto.User">
    select * from user
    <where>
        <if test=" ids !=null and ids !='' ">
            and id in ${ids}
        if>
    where>
select>
1
2
3
4
5
6
7
8

6.2.4、输出结果

10次平均耗时：2519ms
1

6.3、PreparedStatement手动操作

6.3.1、controller层

@Value("${spring.datasource.url}")
private String mysqlUrl;
@Value("${spring.datasource.username}")
private String mysqlUsername;
@Value("${spring.datasource.password}")
private String mysqlPassword;

@GetMapping("/queryForeach2")
public List<User> queryForeach2(User user){
    user.setLimit(80000);
    List<User> query = userMapper.query(user);
    long sum = 0L;
    for (int j = 0; j < 10; j++) {
        long s1 = System.currentTimeMillis();
        Connection connection = null;
        try {
            connection = DriverManager
                .getConnection(mysqlUrl, mysqlUsername, mysqlPassword);
            StringBuffer sql =
                new StringBuffer("select * from user where id in (");
            for (int i = 0; i < query.size(); i++) {
                sql.append("?,");
            }
            sql.deleteCharAt(sql.length()-1);
            sql.append(")");
            PreparedStatement ps =
                connection.prepareStatement(sql.toString());
            for (int i = 0; i < query.size(); i++) {
                ps.setLong(i+1,query.get(i).getId());
            }
            ResultSet resultSet = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("pstm拼接SQL>>>"+(System.currentTimeMillis()-s1));
        sum += System.currentTimeMillis()-s1;
    }
    System.out.println("pstm10次平均值>>>:"+sum/10);
    return null;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

6.3.2、输出结果

10次平均耗时：596ms
1

7、总结：

耗时：foreach>${}>PreparedStatement
编码复杂度:PreparedStatement>${}>foreach
抉择：依据业务场景按照实际情况选择，不可盲目选PreparedStatement。
数据量小于2000选foreach编码简单，时间相差无几。
数据量大于40000选pstm效率最高，时间相差5倍。
1
2
3
4
5

8、参考资料：

| 16 | pstm | foreach |

耗时：foreach>${}>PreparedStatement
编码复杂度:PreparedStatement>${}>foreach
抉择：依据业务场景按照实际情况选择，不可盲目选PreparedStatement。
数据量小于2000选foreach编码简单，时间相差无几。
数据量大于40000选pstm效率最高，时间相差5倍。
1
2
3
4
5

8、参考资料：

https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html