- 上次遇到一个插入效率的问题，同一个事务下，插入1000条数据，主键为递增序列，下面演示：
1、新增两张表
- 记录序列表
CREATE TABLE `sequence` (
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`current_value` int(11) NOT NULL,
`increment_value` int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
- 测试插入记录表
CREATE TABLE `test_batch` (
`ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`GMT_CREATE` timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
- 自定义MySql的主键增长，通过函数来写：
CREATE DEFINER=`goms`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
DECLARE VALUE INTEGER;
SET VALUE = 0;
SELECT current_value INTO VALUE
FROM sequence
WHERE NAME = seq_name;
RETURN VALUE;
END
CREATE DEFINER=`goms`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
UPDATE sequence
SET current_value = CASE current_value WHEN 999999999 THEN 100000000 ELSE current_value + increment_value END
WHERE NAME = seq_name;
RETURN currval(seq_name);
END
2、搭建测试项目
- 在此不多说，这是我平时写demo的一个项目
image.png
image.png
image.png
3、基础代码
public interface GomsSequenceMapper {
long getSeq4ID();
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.tools.dao.test.GomsSequenceMapper">
<!-- flushCache="true" useCache="false" -->
<select id="getSeq4ID" resultType="java.lang.Long" >
SELECT nextval('SEQ_ID');
</select>
</mapper>
public interface TestBatchMapper {
int insertSelective(TestBatch record);
void insertBatch(List<TestBatch> list);
}
public class TestBatch {
private String id;
private String name;
private Date gmtCreate;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id == null ? null : id.trim();
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public Date getGmtCreate() {
return gmtCreate;
}
public void setGmtCreate(Date gmtCreate) {
this.gmtCreate = gmtCreate;
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.tools.dao.test.TestBatchMapper" >
<resultMap id="BaseResultMap" type="org.tools.domain.order.dto.TestBatch" >
<id column="ID" property="id" jdbcType="VARCHAR" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
<result column="GMT_CREATE" property="gmtCreate" jdbcType="TIMESTAMP" />
</resultMap>
<insert id="insertSelective" parameterType="org.tools.domain.order.dto.TestBatch" >
insert into test_batch
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
ID,
</if>
<if test="name != null" >
NAME,
</if>
<if test="gmtCreate != null" >
GMT_CREATE,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=VARCHAR},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="gmtCreate != null" >
#{gmtCreate,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<!-- 批量插入数据 -->
<insert id="insertBatch" parameterType="java.util.List" >
insert into test_batch (ID, NAME, GMT_CREATE)
values
<foreach collection="list" item="item" index="" separator=",">
(#{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, NOW())
</foreach>
</insert>
</mapper>
4、进入主题
@Service
public class TestBatchInsert {
@Autowired
private GomsSequenceMapper gomsSequenceDAO;
@Autowired
private TestBatchMapper testBatchMapper;
final class Interval{
public long start;
public long end;
public long interval;
}
@Transactional
public void test1() {
try {
Interval timeout = new Interval();
timeout.start = System.currentTimeMillis();
System.err.println(new Date(timeout.start));
for (int i = 0 ; i < 100; i++) {
long id = gomsSequenceDAO.getSeq4ID();
TestBatch test = new TestBatch();
test.setId(id+"");
test.setName(id+"----");
testBatchMapper.insertSelective(test);
}
timeout.end = System.currentTimeMillis();
timeout.interval = timeout.end-timeout.start;
System.err.println(String.valueOf(timeout.interval));
System.err.println(new Date(timeout.end));
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
}
@Transactional
public void test2() {
try {
Interval timeout = new Interval();
timeout.start = System.currentTimeMillis();
System.err.println(new Date(timeout.start));
List<TestBatch> lists = new ArrayList<TestBatch>();
for (int i = 0 ; i < 100; i++) {
long id = gomsSequenceDAO.getSeq4ID();
TestBatch test = new TestBatch();
test.setId(id+"");
test.setName(id+"----");
lists.add(test);
}
testBatchMapper.insertBatch(lists);
timeout.end = System.currentTimeMillis();
timeout.interval = timeout.end-timeout.start;
System.err.println(String.valueOf(timeout.interval));
System.err.println(new Date(timeout.end));
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
}
}
- 执行junittest：
image.png
执行test1，大家注意这个是注释的：
image.png
image.png
Tue Oct 15 15:36:31 CST 2019
168
Tue Oct 15 15:36:31 CST 2019
- 执行test2：
image.png
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException就是违反数据库完整性约束
- 在MyBatis中有flushCache、useCache这两个配置属性，分为下面几种情况：
（1）当为select语句时：
flushCache默认为false，表示任何时候语句被调用，都不会去清空本地缓存和二级缓存。
useCache默认为true，表示会将本条语句的结果进行二级缓存。
（2）当为insert、update、delete语句时：
flushCache默认为true，表示任何时候语句被调用，都会导致本地缓存和二级缓存被清空。
useCache属性在该情况下没有。 -
我们来改下xml，不缓存，flushCache="true", useCache="false"
image.png
image.png
Tue Oct 15 15:35:29 CST 2019
126
Tue Oct 15 15:35:29 CST 2019
- 结果很明显，一条一条插入使用了168ms，而批量插入用了126ms。
-
关注公众号"双城人"，搬砖过程遇到的问题，大家一起探讨，资源共享
image.png
