MariaDB快速批量插入数据的几种办法

时间：2022-06-01 17:08:30

前言

当要向MariaDB中插入新的数据时，以下过程会影响插入所消耗的时间：(按时间消耗长短降序排序)

  • 将数据sync到磁盘上(它是事务结束的一部分)
  • 添加新的键值。索引越大，更新键值所消耗的时间就越长。
  • 检查外键约束(如果存在)。
  • 将行添加到存储引擎中。
  • 将待插入数据发送给服务器。

下面介绍几种向表中快速插入数据的技术(按效率的提升程度降序排序)。

1. 禁用索引(key)

你可以临时禁用非唯一索引。特别是在表中数据很少甚至没有数据的时候，禁用非唯一索引可以极大提升插入速度。

多数存储引擎(至少MyISAM和Aria是如此)的ENABLE KEYS会扫描表中的行并收集索引键值，然后对它们排序，最后创建索引块。

因此，先禁用KEY，插入数据后启用KEY的整体速度比每行都更新一次索引的速度至少要快一个数量级，并且所需要的buffer也更少。

注意：当使用INSERT或 LOAD DATA向空表中插入数据时，MariaDB会自动先DISABLE KEYS，插入成功后再自动ENABLE KEYS。

当插入海量数据时，花在完整性检查上的时间也会很长。可以通过禁用UNIQUE索引以及删除主外键约束节省时间；

此外，如果表上有INSERT触发器，或者PERSISTENT字段，可以先删除它们，在数据插入完成之后再重建它们。

2. 使用INSERT语句插入数据

2.1 使用事务

当需要使用单行insert语句(一次插入一行)插入数据时，可以将它们放进事务中，从而避免每行一次事务(意味着每行都需要将数据sync到磁盘)。

例如，每个事务中包含1000次insert，这至少提升1000倍插入效率。

2.2 insert语句的多值插入

你可以使用insert语句一次性插入多行数据，例如：

INSERT INTO table_name values(1,"row 1"),(2, "row 2"),...;

系统变量max_allowed_packet控制了该语句允许的最大行数量。

3. 可以优化插入速度的服务器变量

innodb_buffer_pool_size：如果你的InnoDB/XtraDB表中有多个索引，可增大该值

key_buffer_size：如果你的MyISAM表中有多个索引，可增大该值

max_allowed_packet ： 增大该值以允许insert语句可以一次性插入更多的行(即增加insert语句中括号的个数)

相关文章