mySql批量插入优化 Excel 里的 7 个高效图片操作技巧：批量插入、插入链接、批量导出...
mySql批量插入优化 Excel 里的 7 个高效图片操作技巧：批量插入、插入链接、批量导出...，
mySql批量插入优化
近日，项目中有一个耗时较长的Job存在CPU占用过高的问题，经排查发现，主要时间消耗在往MyBatis中批量插入数据。
mapper configuration是用foreach循环做的，差不多是这样。（由于项目保密，以下代码均为自己手写的demo代码）
insert into USER (id, name) values
(#{model.id}, #{model.name})
这个方法提升批量插入速度的原理是，将传统的：
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
转化为：
INSERT INTO `table1` (`field1`, `field2`)
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");
在MySql Docs中也提到过这个trick，如果要优化插入速度时，可以将许多小型操作组合到一个大型操作中。
理想情况下，这样可以在单个连接中一次性发送许多新行的数据，并将所有索引更新和一致性检查延迟到最后才进行。
乍看上去这个foreach没有问题，但是经过项目实践发现，当表的列数较多（20+），以及一次性插入的行数较多（5000+）时，整个插入的耗时十分漫长，达到了14分钟，这是不能忍的。
在资料中也提到了一句话：
Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. You shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.
它强调，当插入数量很多时，不能一次性全放在一条语句里。可是为什么不能放在同一条语句里呢？这条语句为什么会耗时这么久呢？
我查阅了资料发现：
Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:
some database such as Oracle here does not support.
in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insertstatement in a Java Foreach loop. The most important thing is the session Executor type.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.
从资料中可知，默认执行器类型为Simple，会为每个语句创建一个新的预处理语句，也就是创建一个PreparedStatement对象。
在我们的项目中，会不停地使用批量插入这个方法，而因为MyBatis对于含有
Internally, it still generates the same single insert statement with many placeholders as the JDBC code above.
MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it contains
And these steps are relatively costly process when the statement string is big and contains many placeholders.
[1] simply put, it is a mapping between placeholders and the parameters.
从上述资料可知，耗时就耗在，由于我foreach后有5000+个values，所以这个PreparedStatement特别长，包含了很多占位符，对于占位符和参数的映射尤其耗时。并且，查阅相关资料可知，values的增长与所需的解析时间，是呈指数型增长的。
所以，如果非要使用 foreach 的方式来进行批量插入的话，可以考虑减少一条 insert 语句中 values 的个数，最好能达到上面曲线的最底部的值，使速度最快。一般按经验来说，一次性插20~50行数量是比较合适的，时间消耗也能接受。
重点来了。上面讲的是，如果非要用
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
List
BatchInsert
.into(simpleTable)
.map(id).toProperty("id")
.map(firstName).toProperty("firstName")
.map(lastName).toProperty("lastName")
.map(birthDate).toProperty("birthDate")
.map(employed).toProperty("employed")
.map(occupation).toProperty("occupation")
.build()
.render(RenderingStrategy.MYBATIS3);
batchInsert.insertStatements().stream().forEach(mapper::insert);
session.commit();
} finally {
session.close();
}
即基本思想是将 MyBatis session 的 executor type 设为 Batch ，然后多次执行插入语句。就类似于JDBC的下面语句一样。
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(
"insert into tb_user (name) values(?)");
for (int i = 0; i < stuNum; i++) {
ps.setString(1,name);
ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();
经过试验，使用了 ExecutorType.BATCH 的插入方式，性能显著提升，不到 2s 便能全部插入完成。
总结一下，如果MyBatis需要进行批量插入，推荐使用 ExecutorType.BATCH 的插入方式，如果非要使用
参考资料
https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html
https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast
https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353
https://blog.csdn.net/wlwlwlwl015/article/details/50246717
http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html
https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/
https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow
http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html
Excel 里的 7 个高效图片操作技巧：批量插入、插入链接、批量导出...
原文标题：《Excel 里的 7 个图片操作技巧，倒数第二个 90% 的人都不知道……》
我是卫星酱~
Excel 中的图片，能让我们的数据表更直观，展示的内容也更清晰。
比如，在职员登记表中插入员工照片，把表格转图片放到报告里……
今天，卫某带来的就是关于 Excel 图片的 7 个高效小技巧，一起来看看吧！
1、批量插入
将文件夹中的图片重命名为员工姓名，排序方式选择【名称】。
将 Excel 中的员工姓名按升序排序，记得【扩展选定区域】。
点击【插入】-【图片】- 【此设备】-选择图片所在文件夹，全选图片后【插入】。
图片插入后，在全选状态下把第一张与单元格对齐并调整大小。
然后把最后一张拖到最后一个单元格，全选图片，在【图片格式】中选【对齐】-【左对齐】。
再【对齐】-【纵向分布】。
最后将序号升序排序，恢复表格。
如果是 Excel 365 用户，其实可以在插入图片时直接选择【放置在单元格中】，速度更快：
2、插入链接
有时，过多的图片会让表格变得臃肿、卡顿。
因此，用链接替代图片是个不错的方案。
批量插入很简单，用函数：
=HYPERLINK("G\任务相关\写作图片素材\员工照片\"&B2&"jpg")
然后拖动鼠标下拉填充公式：
3、批量导出
有了批量插入，当然还要看看导出~
更改 Excel 扩展名为.zip，再解压打开，就能看到 Excel 里的图片，都存放在该文件夹下啦！
4、嵌入单元格
如果觉得插入的图片，浮在表格上不利于操作，那就选中图片后按鼠标右键，找到【大小与属性】，然后勾选【随单元格改变位置和大小】：
这样图片就会随表格移动和变形了~
5、批量删除
按【Ctrl+G】 - 点击【定位条件】-【对象】-【确定】，或者直接选中一张图片后按【Ctrl+A】全选；
然后按【Delete】键批量删除。
6、打印忽略
想要打印时不打印图片，就全选图片，按鼠标右键，在【大小和属性】对话框中，取消勾选【打印对象】，完成！
7、表格转图片
选中表格并【Ctrl+C】复制，接着在【开始】-【粘贴】下选择【图片】：
是不是超简单~
本文来自微信公众号：秋叶 Excel （ID：excel100），作者：卫星酱
广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。[db:内容]?
