MySQL插入大量数据最好用批量插入

插入大量数据到MySQL数据库时，执行单条插入语句的开销相对较大，包括网络延迟、每次插入都要进行事务处理和日志记录等。批量插入可以将多条插入语句合并为一条，从而减少网络往返次数和事务开销，提高整体数据插入效率。

理解批量插入

减少网络往返次数

每次执行SQL语句，客户端和数据库服务器之间都需要进行一次网络通信。如果对每条记录执行单独的插入语句，当插入大量数据时，网络通信的次数会非常多，导致大量时间花费在网络往返上。批量插入通过一次网络请求发送多条记录的数据，大幅减少了网络请求的次数。

降低事务开销

在InnoDB存储引擎中，每条插入语句默认被视为一个事务。事务处理包括日志记录和事务提交，这些都需要时间。如果使用批量插入，多条记录可以在一个事务内完成，从而减少了事务处理的次数和开销。

具体场景和数据

假设我们有一个电商平台，在大型促销活动（如“双十一”）后，我们需要将数百万条订单数据插入到orders表中。表的结构如下：

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  order_date DATETIME,
  total_amount DECIMAL(10, 2),
  status VARCHAR(15)
);

单条插入

如果我们使用单条插入，那么对于每个订单，都需要执行一条如下的插入语句：

INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES (1234, '2023-04-01 10:00:00', 299.99, 'completed'); 

执行这个操作数百万次将会非常慢，因为每次都涉及到网络通信和事务处理。

批量插入

相反，如果我们使用批量插入，我们可以将多个订单的数据合并到一个插入语句中：

INSERT INTO orders (customer_id, order_date, total_amount, status) 
VALUES 
(1234, '2023-04-01 10:00:00', 299.99, 'completed'),
(5678, '2023-04-01 10:05:00', 159.99, 'completed'),
(9101, '2023-04-01 10:10:00', 499.99, 'completed'),
...; // 更多的记录

这样，我们可以一次性插入几百或几千条记录，而不是每次只插入一条。批量插入大大减少了网络往返次数和每次插入的事务开销。

性能优化提示

• 合理大小的批次：批量插入的数据量不应该过大，以免造成服务器资源过度消耗或超过最大允许的包大小。通常，开发者需要根据实际情况调整批量插入的大小。
• 关闭自动提交：在开始批量插入之前，可以关闭自动提交事务（autocommit），然后在所有数据插入完成后手动提交事务，以减少事务开销。
• 调整索引策略：在批量插入数据之前，可以考虑暂时移除不必要的索引，待数据插入完成后再重建索引，这可以进一步提高插入效率。

通过这些方法，批量插入可以显著提高大量数据插入到MySQL。