记一次存储过程和prepareStatement执行速度的比较

这两天在存储过程和prepareStatement的选择上栽了个跟头，记录一下。

需求是终端通过网关把数据放入redis中，综合考虑决定使用redis的发布订阅模式来接收数据入库，考虑到获取数量的不确定和多线程的安全性，最终决定了使用linkedblockingqueue来进行二次接受redis数据，防止直接接收redis订阅消息处理不及导致的数据丢失，为了提高数据的写入速度，创建线程池并行执行入库操作。

在决定使用这种方式前其实测试过直接接收数据通过存储过程入库的方式，通过比较插入数据的数据量和实际入库的数据量，在3秒极限情况下产生数据1w条时，数据丢失率达到50%，也就是一次丢失一半数据，随着总数据量增加，损失率依次增高。猜想是线程数受到CPU核心数影响，其次是硬盘的读写速率瓶颈。

两种情况依次说明，先来存储过程：

废话不说上代码：

 

    @Override
    public void onMessage(Message message, byte[] pattern) {
        HrRecordDO hrRecordDO1 = JSON.parseObject(new String(message.getBody()), HrRecordDO.class);

        try {
            linkedBlockingQueue.put(hrRecordDO1);
        } catch (InterruptedException e) {
            log.error("数据存放异常");
        }
            new ThreadPoolExecutor(4, 1000, 2, TimeUnit.SECONDS, new LinkedBlockingQueue<Runnable>(50000000)).execute(() -> {
               Connection conn = null;
                CallableStatement call = null;
                try {
                    conn = JdbcUtils.getConnection();
                    call = conn.prepareCall("{call Pro_record(?,?,?,?,?,?,?,?)}");
                    HrRecordDO hrRecordDO = null;
                    try {
                        hrRecordDO = concurrentLinkedDeque.take();
                    } catch (InterruptedException e) {

                    }
                    //赋值
                    call.setInt(1, hrRecordDO.getHubno());
                    call.setInt(2, hrRecordDO.getDevno());
                    call.setInt(3, hrRecordDO.getHr());
                    call.setDouble(4, hrRecordDO.getCal());
                    call.setString(5, hrRecordDO.getLessonId());
                    call.setString(6, hrRecordDO.getBeginId());
                    call.setInt(7, hrRecordDO.getSteps());
                    call.setObject(8, new java.sql.Date(hrRecordDO.getCreateTime().getTime()));
                    call.execute();
                } catch (SQLException e) {
                    log.debug("入库异常...");
                } finally {
                    JdbcUtils.close(call);
                    JdbcUtils.close(conn);
                }
            });
    }

存储过程：

CREATE DEFINER = `histar`@`%` PROCEDURE `Pro_record` (
	IN `hubno` INT (11),
	IN `devno` INT (11),
	IN `hr` INT (11),
	IN `cal` DOUBLE (16, 1),
	IN `lessonId` VARCHAR (36),
	IN `beginId` VARCHAR (36),
	IN `steps` INT (11),
	IN `createTime` datetime
)
BEGIN
	INSERT INTO hr_record (
		hub_no,
		dev_no,
		hr,
		cal,
		lesson_id,
		begin_id,
		steps,
		create_time
	)
VALUES
	(
		hubno,
		devno,
		hr,
		cal,
		lessonId,
		beginId,
		steps,
		createTime
	);


END

创建并运行生产端后，消费端开始接收数据进行处理，虽然保证了数据的不丢失，及其令人尴尬的是执行速度慢到爆，测试生产了了1w条数据后，客户端还在慢慢悠悠的入库，最终生产入库时间比是1:3，这肯定不行，1w数据出来后，客户端应该同时入库完毕，这是最低要求。

后来网上看了一些存储过程和预编译的相关文章，才总结发现自己选的执行方案有问题，除去批量操作的情况，最忌讳连接开开关关以及分担过多压力给数据库服务器，因为数据库可能要同时执行多种事务。因此换成了prepareStatement预编译语句，把数据库压力给项目服务器。这里不贴代码了，其实就是使用prepareStatement同时把存储过程换成insert into table_name (field1field2，field3...) values （?,?,? ...）这种预编译语句。

 

完事后测试，使用2w数据进行发布订阅入库测试，结果发布入库时间比为1：1，而且是零数据损失。

除此之外，还做了一些其他尝试：

       1.使用MySQL连接池，很奇怪一编译就报错，提示可能会出现数据库资源耗尽的问题，于是作罢。

       2.使用递归获取数据放入List中，但是项目跑着跑着就出现了java.lang.StackOverflowError错误，由于修改一些项目参数可能会导致其他问题出现，权衡一下还是稳妥为先

       3.使用RedisTemplate的boundListOps存放值，循环获取并执行批量操作，这是可行的，不过如果同时要对实时入库的数据做些处理的话，可能就不太友好了。

以上的方式多次的循环接收值并暂时存储到List中，接着循环遍历赋值入库，入库后要把List清空接收后续的数据，不过这个过程中就已经出现了数据丢失的情况，而且在数据不断的转手过程中消耗的资源也是很可观的。因此在使用redis做发布订阅的情况下都不是最佳选择。

小提示：在生产上做实时数据处理过程中，尽量不使用日志输出，就纯粹的代码运行即可，日志对参数的解析、输出打印控制台和log持久化对执行效率有一定的影响。

看来，针对实际简单的应用场景，还是用简单的方法解决问题最好。