- 上次遇到一个插入效率的问题,同一个事务下,插入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的一个项目
![e272613bff231213beb0237d164d11d4.png](https://i-blog.csdnimg.cn/blog_migrate/4ab19afa8006f0a815446df636aee1d6.jpeg)
![94bf9eb7046e80878f98245ee0e046c4.png](https://i-blog.csdnimg.cn/blog_migrate/a1bf97ef6fe9a3078bcebc4d2818ae89.png)
![5e04c5d3deea009d0f3c4db707f0032d.png](https://i-blog.csdnimg.cn/blog_migrate/6fcfcf271101729a35c77f59a4db1c34.jpeg)
## 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:**
![41aa084382fd4412f911fe28071085a6.png](https://i-blog.csdnimg.cn/blog_migrate/ec6ba944e7ce8f6b439d1f43cb0c3071.png)
**执行test1,大家注意这个是注释的:**
![c9b8e772b10e381c46b40b561003e63b.png](https://i-blog.csdnimg.cn/blog_migrate/a17a3312125ed1cb899fb8c52198da66.png)
![e55003979b13236e3b25d1959b3858aa.png](https://i-blog.csdnimg.cn/blog_migrate/55ba40f8bea18ff993b1a26b089f4923.png)
Tue Oct 15 15:36:31 CST 2019
168
Tue Oct 15 15:36:31 CST 2019
**- 执行test2:**
![7f33765d7d63c842536f18b125533eec.png](https://i-blog.csdnimg.cn/blog_migrate/d6ba64b2055a34ac7a5e52ded2a34462.jpeg)
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"
![d717a7a293296fb08ca97ce817bfbf8c.png](https://i-blog.csdnimg.cn/blog_migrate/53686ada7287a608f2b4f232d08f948e.png)
![e4e529f467bfdc8f3bf4a40eac83f1d4.png](https://i-blog.csdnimg.cn/blog_migrate/f7a17f78a4eeb9314f919640fd9627cc.png)
Tue Oct 15 15:35:29 CST 2019
126
Tue Oct 15 15:35:29 CST 2019
- 结果很明显,一条一条插入使用了168ms,而批量插入用了126ms。