在mysql中,提供了save ignore语法,用于在插入数据如果出现冲突时忽略信息的处理方式.在这种情况下,一般主键id是通过自动生成,ignore通过一些惟一索引进行控制.在程序中的期望即如果惟一索引不冲突就插入新的数据,如果冲突则不再插入. 但都希望能够在处理成功之后拿到这些数据的主键(不管是之前的还是新插入的),以便于后续进行处理.相应的简单业务逻辑如下所示.
List<T> personList = xxxList;
mysql.saveIgnore(personList);
//进行后续逻辑,如转账
personList.forEach(t-> {
mysql.addMoney(t.getId(), 100);
});
在标准的jdbc中,如果是save,或者是mysql的save values(value1) (value2) 这种语法,是能够通过 statement.getGeneratedKeys()返回自动生成的主键.但对于save ignore无效,mysql并不是返回由于冲突处理的之前的主键信息.
常规的作法就是在save ignore之后,再通过相应的惟一索引来进行查询. 类似如下的sql方式
select id from t where t.name in(name1,name2);
这种方式,对于如果惟一索引仅有一列是没有问题的.但如果是多列组成的,则生成如下的sql
select id from t where (t.name,t.code) in ((name1,code1),(name2,code2));
理论上,这种方式也没有问题,但是在mysql中,这种语法并不能命中相应的索引,会造成全局扫描(验证版本5.5,5.6)
修改in版本为如下sql
select id from t where (t.name = name1 and t.code = code1) or (t.name = name2 and t.code = code2);
这种方式可以命中索引,但是整个sql会变得很长,可以看出,相应的惟一索引列会出现多次.如果是5000个数据处理(批量处理肯定数据会很多才有意义),则整个sql会很长.
本文的方式是通过临时表,提前插入索引数据,再通过表关联来获取相应的数据.这样可以避免上面sql过长的问题,并且利用mysql内存表快速处理数据.整个原理可以理解为以下几个步骤
- 创建临时表,表数据仅为惟一索引要求列,并建立相应的惟一索引
- 插入待处理数据中指定属性到临时表当中
- 使用save ignore语法插入数据到实际表中
- 两表关联,使用临时表关联实际表,指定相应的惟一列进行关联条件,查询出主键信息
- 程序中将主键处理到相应的对象中
1 建立临时表
临时表采用mysql的temporary表方案,这样在事务处理完之后,表信息会自动清除.同时设置引擎为memory,数据不需要放入磁盘.因为数据量不多,因此不会对mysql的temp表空间有太多影响.
惟一需要考虑的是表的列信息,这些需要由程序来给出,实际在在插入单个表时,这个表的惟一是确定的.因为只需要程序中给出属性名,再通过属性映射即找出相应的字段以及字段类型信息.这些信息足以创建出临时表了.
注:此步对于程序要求,给要求给出惟一属性信息即可. 如T对象属性列{"name","code"}.这个属性可以定义为static属性,固化在程序中.
2 插入临时表数据
无特殊处理方式,遍列处理数据,提取惟一属性信息,直接插入即可.
3 插入数据至实际表
无特殊处理方式,使用原来的save ignore即可.
4 关联查询
这一步,因为两个表都已经有数据.因此直接关联查询即可.相应的语法类似以下处理.
select t.id,t.name,t.code from t inner join temp on t.name = temp.name and t.code = temp.code
上面的sql除查询id外,还查询出相应的惟一列name,code,这是方便第5步反向处理id.
5 程序反向设置id
在第4步,查询出惟一列以及id属性, 程序中通过map映射以惟一值为key,对象为value.这里的数据以惟一值为key,id为value作为参考map.两者进行比例处理即可.
总结
以上的方案,是一个简单化的处理方案,不需要程序中作额外的处理.整个处理逻辑可以通用化到公共方法中.整个临时表建立,处理,查询逻辑都可以自动化.在程序中只需要传递对象列表即可,惟一要增加的就是额外的惟一列信息.
实战篇
1 以上的整个处理方式,都需要mysql temp表空间参与,在之前的测试当中,批量的数据量尝试提至20000,但最后测试效果不好.具体原因没有细查.建议上限值为5000.
2 上面的处理逻辑当中,避免使用ddl语句,也不能使用如truncate类似的语法.这种语法会导致mysql自动提交当前事务,这是需要避免的.因此上面使用了临时表,以及使用了delete语法.不要创建实体表以及使用truncate,以避免出现事务自动被提交的问题
3 在实践当中,save ignore出现的原因在于并不知道当前处理的数据是否是已经在数据表中存在还是不存在,即这些数据是外部导入进来的.因此在后续的运行过程中,实际上save ignore很大程度可能并不会插入一条数据.这样在上面处理流程中的第3步就可以省略.而且发现,整个流程中也是第3步最花时间. 因此,可以通过配置变量控制相应的流程,即可以通过 Query-Save-Query的方式代替.如果直接查询的数据量就与待处理数据相同,则表示不需要再插入的.
4 在最前面提到此方案处理自增主键,实际上并不仅仅只支持自增主键,所有使用自然主键的地方都可以使用这种方式.当然,如果是业务主键,则不需要这种处理.
改进篇
1 临时表改进
在第1步中需要创建临时表,这个步骤可以优化.方式如通过提前创建好或者池化表来处理.采用池化表方案就只能是实体表,不过只需要小心处理不影响当前事务即可.
2 关联查询结构
在第4步查询中,可以看出查询结果列太多,可以通过序号优化的方式来处理,并不需要查询出索引列.如在临时表中增加一列,序号.此序号表示程序中数据的下标值,最终处理时,仅通过下标来定位数据即可.
转载请标明出处:i flym
本文地址:https://www.iflym.com/index.php/code/201705250001.html