一、运行环境
Mysql版本：mysql-8.0.21-winx64
数据库连接池：druid
二、批量 Insert 业务背景
大批量数据同步、加工。
同步效率较低时，会存在数据更新延迟、CPU、IO资源长时间被占用等问题。
如：从合同管理系统中同步合同数据存库，并在本系统计算合同统计信息，如：合同超期数量等。
三、测试准备
建表语句：
CREATE TABLE tab_user
(
id bigint(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
name varchar(64) DEFAULT NULL COMMENT '姓名',
sex varchar(32) DEFAULT NULL COMMENT '性别',
age int(11) DEFAULT NULL COMMENT '年龄',
create_time datetime DEFAULT NULL COMMENT '创建时间',
update_time datetime DEFAULT NULL COMMENT '更新时间',
status tinyint(1) DEFAULT NULL COMMENT '是否删除 1删除 0未删除',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
策略
1、 批量写
2、 多线程写
数据量：10w、100w、1000w、1m
每次批量写的数据条数：10、100、1000、2000、5000
线程池配置
spring.task.scheduling.thread-name-prefix=SysAsyncExecutor_
spring.task.scheduling.pool.size=10
spring.task.execution.thread-name-prefix=SysAsyncExecutor_
spring.task.execution.pool.core-size=6
spring.task.execution.pool.max-size=200
spring.task.execution.pool.queue-capacity=10
spring.task.execution.pool.keep-alive=60
spring.task.execution.pool.allow-core-thread-timeout=true
四、测试结果
批量插入数据对效率的影响
| 批量10 | 批量100 | 批量1000 | 批量2000 | 批量5000 | |
|---|---|---|---|---|---|
| 10w | 27966 | 6134 | 3268 | 3137 | 3031 |
说明： 插入10w条数据，每条sql语句分别批量insert数据10条、100条、1000条、2000条、5000条。
观察结论：
1、 批量写数据比逐条写数据耗时更短。
2、 随着每次批量写入的数据增多，总耗时变短，但有效率瓶颈。每次批量写1000条数据，效率较高，再增加pageSize不能显著缩短耗时。
多线程写对效率的影响
| （单位：毫秒） | 批量1000 | 批量1000, 多线程 |
|---|---|---|
| 10w | 3294 | 1160 |
| 100w | 31685 | 15556 |
| 1000w | 333115 | 91439 |
| 1m | 6737160 | 899613 |
说明： 插入10w、100w、1000w、1m数据，比较单线程批量插入和多线程批量插入的耗时。
观察结论： 在数据库连接资源充足的情况下，使用线程池进行多线程操作可以提升操作效率。
单线程和多线程CPU和IO耗费对比
说明：
a) 单表新增1m数据，批量1000条单线程操作。时间：17:46:50.783——19:39:07.944
b) 单表新增1m数据，批量1000条多线程操作。 时间：19:48:05.053——20:03:04.666
监控两个时间段windows系统的CPU和IO消耗。（监控工具：windows自带perfrom工具）
踩坑记录： 在并发新增1m数据后，执行 delete from tab_user;时，报错并出现数据库假死现象（无法进行对该表的任何操作，如select、drop table）
观察结论：大数据量和高并发对机器的CPU和IO有较大压力，并且会对数据库操作性能有影响。
CPU：
DISK：
带索引和不带索引insert效率对比
| （单位：毫秒） | 批量1000，表不带索引 | 批量1000，表带索引 |
|---|---|---|
| 10w | 3294 | 15528 |
| 100w | 31685 | 178754 |
| 1000w | 333115 | 402082 |
说明： 批量insert数据，每条sql插入1000条数据，比较数据库带索引和不带索引时的数据新增耗时。
观察结论： 添加索引后，新增数据会增加耗时。另外，在执行 delete from tab_user 删除数据时，耗时显著增加。
分库分表情形下大数据量insert
使用shardingJdbc框架实现分库分表，新增1000w条数据，耗时1164088。分库分表额外的改写SQL和路由操作增加了耗时。
查询效率
按姓名查询某人的信息
select * from tab_user where name ='覃志强'
| 1000w | |
|---|---|
| 普通表非索引 | 3.44s |
| 普通表带索引 | 0.6s |
| 分库分表带索引 | 0.5s |
如果可以通过加索引、SQL优化的方式提高查询效率，则先不考虑分库分表。
查询性能相差不大的原因分析：
1、 数据量不够大
2、 并发操作压力较小
3、 查询语句简单
4、 分库分表均在本机操作，没有多台机器集群。
