大数据量插入数据库

大数据量插入数据库问题：

批量插入：大量数据保存在List中然后进行批量插入，超出最大数据包限制了，可以通过调整max_allowed_packet限制来提高可以传输的内容，不过由于30万条数据超出太多

List<User> userList = new ArrayList<>();
for (int i = 1; i <= 300000; i++) {
   User user = new User();
   user.setId(i);
   user.setUsername("12131 " + i);
   user.setAge((int) (Math.random() * 100));
   userList.add(user);
}
session.insert("batchInsertUser", userList); // 最后插入剩余的数据
session.commit();

报错：Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 >yun 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

循环逐条插入：执行后可以发现磁盘IO占比飙升，一直处于高位，总共执行了14909367毫秒，换算出来是4小时多，太慢了

for (int i = 1; i <= 300000; i++) {
   User user = new User();
   user.setId(i);
   user.setUsername("12131 " + i);
   user.setAge((int) (Math.random() * 100));
   // 一条一条新增
   session.insert("insertUser", user);
   session.commit();
}

优化批处理方案：使用了 MyBatis 的批处理操作，将每 1000 条数据放在一个批次中插入，能够较为有效地提高插入速度，并等待10秒钟。这有助于控制内存占用，并确保插入操作平稳进行，五十分钟执行完毕，时间主要用在了等待上，CPU和磁盘占用很低。如果低谷时期执行，CPU和磁盘性能又足够的情况下，直接批处理不等待执行，24秒可以完成数据插入，短时CPU和磁盘占用会飙高，批处理的量再调大一些调到5000，只需13秒

List<User> userList = new ArrayList<>();
for (int i = 1; i <= 300000; i++) {
   User user = new User();
   user.setId(i);
   user.setUsername("12131" + i);
   user.setAge((int) (Math.random() * 100));
   userList.add(user);
   if (i % 1000 == 0) {
       session.insert("batchInsertUser", userList);
       // 每 1000 条数据提交一次事务
       session.commit();
       userList.clear();
       // 等待一段时间
       Thread.sleep(waitTime * 1000);
  }
}
// 最后插入剩余的数据
if(!CollectionUtils.isEmpty(userList)) {
   session.insert("batchInsertUser", userList);
   session.commit();
}

使用 MySQL 数据库提供的load指令进行插入：一次性需要插入大批量数据(比如: 几百万的记录)，使用 insert 语句插入性能较低，此时可以使用load指令进行插入

优化方案
- 主键顺序插入，性能高于乱序插入，在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表，主键顺序插入，第一个页没有满，继续往第一页插入，第一页写满之后，再写入第二个页，页与页之间会通过指针连接，第二页写满了，再往第三页写入，如果是主键乱序插入，如果第一页和第二页都存满了，此时新数据插入，应该插入的位置正好在第一页的最后位置，此时第一页已经写满了，不能存放数据，此时会新开一个第三页，然后把第一页的后一半数据移动到第三页，然后将新数据插入到第三页，这样就实现了新数据插入，此时页的顺序有问题，需要调整为主键顺序，重新设置链表指针，这个过程就发生了页分裂的问题，如果是频繁的页分裂，页会变得稀疏并且被不规则地填充，最终数据会有碎片，同时页分裂和页合并涉及大量数据移动和重组，频繁进行这些操作会增加数据库的消耗，影响数据库整体性能。
总结：
- 批处理，批量提交SQL语句(相当于合并了多条insert语句为一条)可以降低网络传输和处理开销，减少与数据库交互的次数。在Java中可以使用Statement或者PreparedStatement的addBatch()方法来添加多个SQL语句，然后一次性执行executeBatch()方法提交批处理的SQL语句
- 循环插入时带有适当的等待时间和批处理大小，从而避免内存占用过高
- 设置适当的批处理大小：批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小，插入操作的频率将很高，而如果批处理大小太大，可能会导致内存占用过高
- 采用适当的等待时间：等待时间指在批处理操作之间等待的时间量。等待时间过短可能会导致内存占用过高，而等待时间过长则可能会延迟插入操作的速度。
- 索引方面，在大量数据插入前暂时去掉索引，最后再打上，这样可以大大减少写入时候的更新索引的时间---->一般不用
- 使用数据库连接池可以减少数据库连接建立和关闭的开销，提高性能
- 增加MySQL数据库缓冲区大小、配置高性能的磁盘和I/O
- 关闭事务自动提交，手动控制事务，避免每一条insert提交一次事务，频繁提交事务对性能影响大