How to Quickly Insert Data Into MariaDB

本文介绍了向MariaDB中快速插入数据的几种不同技术。

背景

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

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

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

1.禁用索引(key)

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

ALTER TABLE table_name DISABLE KEYS;
BEGIN;
... inserting data with INSERT or LOAD DATA ....
COMMIT;
ALTER TABLE table_name ENABLE KEYS;

多数存储引擎(至少MyISAM和Aria是如此)的ENABLE KEYS会扫描表中的行并收集索引键值，然后对它们排序，最后创建索引块。因此，先禁用KEY，插入数据后启用KEY的整体速度比每行都更新一次索引的速度至少要快一个数量级，并且所需要的buffer也更少。

当插入海量数据时，花在完整性检查上的时间也会很长。可以通过设置系统变量unique_checks和foreign_key_checks来禁用UNIQUE索引以及foreign keys约束对数据的检查：

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

对于XtraDB/InnoDB表，可以临时将AUTO_INCREMENT lock mode设置为2，如下：

SET @@global.innodb_autoinc_lock_mode = 2;

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

2.Loading text files

向MariaDB中插入数据最快的方式是使用LOAD DATA INFILE命令。

该命令最简单的格式为：

LOAD DATA INFILE 'file_name' INTO TABLE table_name;

可以使用下面的语句来读取客户端所在机器上的文件并插入到MariaDB服务器上：

LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name;

从客户端上读取文件的速度比直接在服务端上读取文件的速度要慢一些，但差距不会很大。

LOAD DATA INFILE之所以很快，是因为：

1. 不需要解析SQL语句。
2. 读取数据时可以一次读取很多个数据块。
3. 如果load data之前表是空的，则所有的非唯一索引都会先禁用，插入数据成功后才启用。
4. 它会告诉存储引擎先缓存一些行的数据，达到一定数量后再一次性插入到表中(至少MyISAM和Aria存储引擎支持该行为)。
5. 对于空表，某些事务性引擎(如Aria)不会记录数据插入的事务日志，因为我们可以随时通过truncate操作来回滚。

由于以上速度优势，当你需要插入大量数据的时候，你可以先创建一个本地数据文件，然后使用LOAD DATA INFILE来插入它们，这比直接INSERT要快。

在MariaDB 5.3中，你还能看到LOAD DATA INFILE的进度报告progress reporting。

2.1 mysqlimport

你可以使用mysqlimport并行导入多个文件。例如：

mysqlimport --use-threads=10 database text-file-name [text-file-name...]

mysqlimport内部会使用LOAD DATA INFILE来读取数据并插入数据，因此速度也非常快。

3.使用INSERT语句插入数据

3.1 使用大事务(Using big transactions)

当需要使用单行insert语句(一次插入一行)插入数据时，可以将它们放进BEGIN / END中，从而避免每行一次事务(意味着每行都需要将数据sync到磁盘)。例如，每个begin/end中包含1000次insert，这至少提升1000倍插入效率。

BEGIN;
INSERT ...
INSERT ...
END;
BEGIN;
INSERT ...
INSERT ...
END;
...

之所以上面使用了多个BEGIN/END语句而不是一个BEGIN/END的原因是前者会占用更少的事务日志空间。

3.2 insert语句的多值插入

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

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

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

4.同时向多个表中插入数据(Inserting data into several tables at once)

如果你想一次性向多个表中插入数据，最好的方法是使用多行语句并且一次性发送给服务端：

INSERT INTO table_name_1 (auto_increment_key, data) VALUES (NULL,"row 1");
INSERT INTO table_name_2 (auto_increment, reference, data) values (NULL, LAST_INSERT_ID(), "row 2");

LAST_INSERT_ID()函数回返回auto_increment的最后一个值。

默认情况下，客户端mysql命令会将上面的语句按一个整体发送给服务端。

可以通过下面的方式来测试mysql的行为：

delimiter ;;
select 1; select 2;;
delimiter ;

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

完整的服务变量，可参见mysqld options。