jdbc 性能优化之fetch & batch

缘起

朋友（下简称P）在公司经常955, 拿着三线城市8.5K/月的薪资, 让老板十分的不爽, 于是有一天, 老板交代给他一个任务, oracle11g 单表（未分表）5000w的数据. 其中一个字段是中文姓名, 然后老板希望他能新增一个字段为拼音字段, 并且这个字段的值为中文姓名的拼音. P觉得这还不简单，首先改表结构——新增一个varchar字段（下简称V）为拼音， 然后用原生jdbc拉取所有数据下来，然后遍历结果集, 然后逐条更新V字段. 逻辑异常的清晰和简单. 至于中文转拼音，既有成熟的API接口，也有丰富的本地jar包. 所以这就不是难事了. 写完开始跑, 一下子傻了眼, 大概是 12秒/1w条 的处理速度. 则这样算下来，约莫16小时才能搞定. 但是气人就气人在老板还安排一位17年毕业的小鲜肉也来做这个东西, 人家处理速度是 5秒/1w条. 老板很明显想diss一下我的这位朋友P. 于是他死扛了2天，来找我怎么扳回一局. 最后我通过使用fetch+batch参数将性能提高到了0.35秒/1w条. 着实帮他找回了面子.

分析

其实jdbc 要想优化, 要搞清楚2件事情，首先整个过程就是顺次下面2个过程

1. 通过resultset接口从数据库拉取数据，
2. 发送update的sql语句+commit语句到数据库服务器命令数据库服务器执行一系列的操作.

其中过程1的优化需要fetch. 过程2的优化需要batch.

​ fetch的作用在于一次从数据库通过resultset接口获取的记录条数并且缓存到内存中, oracle默认是一次拿10条. 对于本案例而言太少了, 我们需要适当将其调大, 因为一次取1w条数据到内存中处理比一次才取10条放入内存中处理要高效的多——最起码客户端的resultset接口不需要发送太多次的取数据指令到数据库服务器. 这里在DEMO【1】中我设置其为1w. 当然，不可以设置的太大，否则内存吃不消. 显然 原生jdbc 中 resultset.next() 方法滚动游标是在滚动内存中缓存来的数据. 其实, 这完全可以看出resultset的设计思想——它里面一定封装了网络请求的方法, 但是和数据库交互的时候只会读取一定量的记录，而不会将select语句要读取的所有数据一口气拿过来(这属于jdbc协议的范畴, 因为jdbc协议就是规定jdbc客户端和服务器之间交互的契约嘛~), 因为这样的话, 首先要募集所有的要查询的数据过程就很慢, 其次即便募集齐了， 但是这对网络带宽和客户端机器的内存也是一个挑战. 所以通过每次读取fetchsize数量的记录，实现一种流式读取. 这无疑是一种高明的解决办法和设计思想.

PS: 在实践的过程中我发现了一个现象. 就是到最后会卡顿一下. 其实这不难理解, 因为最后不够1w条（即我们每次要fetch的数量）了, 数据库服务器会迟疑一下才把不够1w条的数据也给我们.

​ batch的作用在于通过jdbc驱动包中的实现，将多条增删改的sql合并为一条sql发送给数据库服务器. 如果不用batch的话, 则就是一条一条增删改的sql的发送给数据库服务器让数据库服务器一条一条的执行. 效率显然是前者高. 如果这句话看不明白, 请参考【1】中的图片, 秒懂. 尊重版权，我就不盗图了. 经过试验，本案例中的性能瓶颈就在这个batch上. 当然，前提是你使用的jdbc驱动包实现了addBatch、executeBatch接口，当然大部分主流数据库厂商都实现了该接口.

PS: 很多厂商没有尚实现 executeLargeBatch接口

参考

【1】https://blog.csdn.net/zhangyadick18/article/details/50294265

DEMO

【1】https://github.com/yfsyfs/backend/tree/master/jdbc%20%E4%BC%98%E5%8C%96%E4%B9%8B%20fetch%20%E5%92%8C%20batch

后记

小P在公司运行了DEMO【1】中的程序, 发现还是很慢，我告诉它，是因为他们公司建表的时候没有加索引(这里必须要吐槽一下，什么鬼公司，表连主键都没有, 聚簇索引都不要了吗? 我也是呵呵). 然后你想想要在5000w条数据中定位到你要更新的那一条，不慢就有鬼了. 所以小P加上了二级索引之后，速度明显上升, 据他反馈，最终完成任务只用了29分钟. 足足比竞争小鲜肉快了10倍. 因此也得到了领导的刮目相看. 从而小P过长了继续955/8.5K的性福生活. 啊呸，是幸福生活.