mysql入库大量数据_Mysql: 大量数据批入库耗时的问题，当数据量大时为何速度下滑很快？...

java项目用的mybatis+mysql

int batchAmount = 20000;

for (int startIndex = 0; startIndex < size; startIndex += batchAmount) {

int endIndex = startIndex + batchAmount;

endIndex = (endIndex > size) ? size : endIndex;

nInserted += userMapper.batchInsertIgnoreDuplicate(userList.subList(startIndex, endIndex));

}

其中batchInsertIgnoreDuplicate对应的mybatis语句

Table, Columns, Values就是响应的表，表列名和列值

INSERT IGNORE INTO

VALUES

我发现入库操作，如果数据行在百万以及以下，每秒入库大概2000-5000行左右

但是当数据量到了400万左右，每秒就在400-1000行了，速度下滑显著

我看gc回收，回收频率和回收时间没什么问题，400万入库操作时，观察gc如下，没啥问题，应该不是java或者jvm gc问题。那为何数据量大，入库速度下滑严重？

[root@localhost bin]# ./jstat -gcutil 10442 10000

S0 S1 E O M CCS YGC YGCT FGC FGCT GCT

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

24.04 0.00 63.84 49.04 97.65 95.86 580 11.725 6 0.351 12.076

0.00 23.32 71.10 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.10 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.10 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.10 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.10 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.11 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.11 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.11 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.11 49.04 97.65 95.86 581 11.731 6 0.351 12.082

0.00 23.32 71.11 49.04 97.65 95.86 581 11.731 6 0.351 12.082

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087

12.85 0.00 78.61 49.04 97.65 95.86 582 11.736 6 0.351 12.087