问题:excel数据导入,保存到数据库中,为了优化查询效率和其他一些业务需求,需要将数据的一列属性切分后保存到redis中,插入数据库前要保证其中一个属性不重复,另外一个属性已经在数据库中。
为了将问题描述简单些,我们假设excel中有两列数据A和B,其中数据A要保证数据库中不重复,数据B保证数据库中已经存在,并且要将A切分为前缀保存到redis中。
原本的代码导入两千条数据要10几分钟的时间,比较明显的可以优化的地方有两点:
1:原本代码中判断数据A是否重复,用遍历数据A,去数据库中查询是否已经存在
2:原本代码中判断数据B是否已经存在,也是遍历数据B,去数据库中查询是否存在。
我们知道,每次数据库连接都有耗时,2000条数据,遍历查询就需要2000次数据库连接,我们可以采用in查询,并且如果in查询的条件有索引,也是会利用索引的。
为了证明,我建了一个临时表,只有一个字段,查询一百次,分别用in和循环查询,循环查询100次时间可以见:
而用in查询可以看到时间
相差很大。
将查询优化为in查询后,速度依旧很慢,2000条数据导入也需要十分钟左右的时间,因为上面说过数据库建立连接极为耗时,那么插入数据时批量插入一定也会比循环插入效率高,下一步我将插入语句由原来的循环插入改为批量插入,为了验证效率差异,我们分别做俩种操作,插入100条数据,循环插入时间:
批量插入时间:
可以看到时间效率相差很大。
我将插入语句优化后,整体导入时间有了很大提升,2000条数据需要几分钟的时间,在数据库方面优化已经足够,那么问题点出现在哪里?
首先想到的一次性读入两千条数据,会不会导致full gc。因为创建一个大对象时,会将对象直接分配到老年代,如果老年代连续空间不够,会导致full gc。所以我们应该避免创建寿命短的大对象。根据jvm参数将此原因排除。
那么只有保存redis耗时了,原redis保存方式是for循环插入,2000条数据,A字段平均20个字符,那么就需要循环调用4万次redis的zset操作。此时redis的pipeline就派上了用场,首先我们知道redis是单线程操作,所有的操作必须按顺序执行,执行完一个操作返回一次结果, 因为每一次client链接redis server都是一次tcp链接,那么4万次,就需要8万次tcp报文传输,大部分时间消耗在传输耗时中了,那么pipeline就是客户端一次发送多个命令,无需等待前一个命令返回结果,等到server处理完所有操作,统一返回报文。那么2万次操作只需要两次报文传输,不过redis推荐每次操作不超过1万个(经过实践,一次传送10万指令没有问题),那么我将其划分为几次,分批操作。
优化后2000条数据只需要十几秒钟的时间就可以导入完成。
以上优化比较简单,都是一些基础知识。
还有一些不足的地方,一是一次性读取整个excel数据有待优化,因为一次性创建大对象,会直接分配到老年代,而老年代的回收需要full gc。我们的导入对象又寿命很短,所以会触发full gc。
二是redis的pipeline不会保证事务性,即有的操作失败后不会影响其他操作,不过我们可以获取返回值后判断哪些失败,重发请求即可。
细节:在redis中还保存了数据A的缓存,其实查询A是否存在有两种方案选择
1:单个查询,可以利用到redis缓存(我们会对A属性在redis做key value缓存)
2:in查询,不会利用redis缓存
那么根据业务需求,导入的数据绝大部分都是数据库中不存在的,只有极个别的录错数据才会存在数据库中,那么redis命中率极低,会穿透查询数据库,所以我采用了in查询,不利用redis缓存。