一、運行環(huán)境

image.png

image.png

Mysql版本：mysql-8.0.21-winx64

數據庫連接池：druid

二、批量 Insert 業(yè)務背景

大批量數據同步、加工。
同步效率較低時，會存在數據更新延遲、CPU、IO資源長時間被占用等問題。

如：從合同管理系統(tǒng)中同步合同數據存庫，并在本系統(tǒng)計算合同統(tǒng)計信息，如：合同超期數量等。

三、測試準備

建表語句：

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 '創(chuàng)建時間',
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

四、測試結果

批量插入數據對效率的影響

說明： 插入10w條數據，每條sql語句分別批量insert數據10條、100條、1000條、2000條、5000條。

觀察結論：
1、 批量寫數據比逐條寫數據耗時更短。
2、 隨著每次批量寫入的數據增多，總耗時變短，但有效率瓶頸。每次批量寫1000條數據，效率較高，再增加pageSize不能顯著縮短耗時。

image.png

多線程寫對效率的影響

說明： 插入10w、100w、1000w、1m數據，比較單線程批量插入和多線程批量插入的耗時。

觀察結論： 在數據庫連接資源充足的情況下，使用線程池進行多線程操作可以提升操作效率。

image.png

單線程和多線程CPU和IO耗費對比

說明：
a) 單表新增1m數據，批量1000條單線程操作。時間：17:46:50.783——19:39:07.944
b) 單表新增1m數據，批量1000條多線程操作。 時間：19:48:05.053——20:03:04.666

監(jiān)控兩個時間段windows系統(tǒng)的CPU和IO消耗。（監(jiān)控工具：windows自帶perfrom工具）

踩坑記錄： 在并發(fā)新增1m數據后，執(zhí)行 delete from tab_user;時，報錯并出現數據庫假死現象（無法進行對該表的任何操作，如select、drop table）

image.png

觀察結論：大數據量和高并發(fā)對機器的CPU和IO有較大壓力，并且會對數據庫操作性能有影響。

CPU：

image.png

DISK：

image.png

帶索引和不帶索引insert效率對比

說明： 批量insert數據，每條sql插入1000條數據，比較數據庫帶索引和不帶索引時的數據新增耗時。

觀察結論： 添加索引后，新增數據會增加耗時。另外，在執(zhí)行 delete from tab_user 刪除數據時，耗時顯著增加。

分庫分表情形下大數據量insert

使用shardingJdbc框架實現分庫分表，新增1000w條數據，耗時1164088。分庫分表額外的改寫SQL和路由操作增加了耗時。

查詢效率

按姓名查詢某人的信息
select * from tab_user where name ='覃志強'

如果可以通過加索引、SQL優(yōu)化的方式提高查詢效率，則先不考慮分庫分表。

查詢性能相差不大的原因分析：
1、 數據量不夠大
2、 并發(fā)操作壓力較小
3、 查詢語句簡單
4、 分庫分表均在本機操作，沒有多臺機器集群。