mybatis批量查询效率对比

背景：

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

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

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

1、普通程序：

for循环嵌套{
    selectByPrimaryKey();
	select();  
}

2、中级程序：

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

3、中级+程序：

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

4、高级程序

PreparedStatement 手动拼接sql执行executeQuery。

5、说明：

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

6、实战耗时对比

由于是demo逻辑都写在了controller。
数据库windows版。
表只有三列。
只对比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;
}

6.1.2、dao层

List<User> queryForeach(User user);

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>

6.1.4、输出结果

10次平均耗时：3072ms

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;
}

6.2.2、dao层

List<User> queryForeach1(User user1);

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>

6.2.4、输出结果

10次平均耗时：2519ms

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;
}

6.3.2、输出结果

10次平均耗时：596ms

7、总结：

查询条数	foreach耗时	${}耗时	pstm耗时	最快	最慢
80000	3072	2519	596	pstm	foreach
40000	1597	1099	330	pstm	foreach
20000	860	639	136	pstm	foreach
10000	389	315	101	pstm	foreach
5000	227	181	41	pstm	foreach
2000	53	56	16	pstm	foreach

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

8、参考资料：

| 16 | pstm | foreach |

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

8、参考资料：

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