背景:
随着项目的运行,数据越来越多,系统部分功能运行越来越慢,优化势在必行。
快速释放系统数据库等资源占有,避免造成旱涝不均等。
这里主要举例说明下查询数据的处理思路,以及如何取舍。
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