如何提高插入速度？

Question

我需要将数据从外部web服务导入到我的mySQL(5.7)数据库中。问题是，我需要将数据分成表。例如，我有桌子

CREATE TABLE a (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

CREATE TABLE b (
    id INT PRIMARY KEY AUTO_INCREMENT,
    a_id INT,
    name VARCHAR(100)
);

现在，我必须在表a (1:n)的表b中插入多行，因为在插入表a之前，我不知道表a的id，唯一的方法是在表a中插入一行，获取最后一个id，然后插入连接到表b的所有条目。

但是，当我逐行插入时，我的数据库非常慢。在表a中插入大约35000行，在表b中插入120000行需要超过1小时。如果我在表a上批量插入大约1000行(仅用于测试而不填充表b)，它会更快(不到3分钟)。

我想一定有办法可以加快我的进口速度。

谢谢你的帮忙

Answer 1

我猜想您正在使用一种驱动插入的编程语言。您需要能够对这个操作序列进行编程。

First，您需要使用这个序列将一行放入a，将依赖行放入b。它使用ID()来处理a_id。这比查询表以找到正确的id值更快，也更健壮。

INSERT INTO a (name) VALUES ('Claus');
SET @a_id = LAST_INSERT_ID();
INSERT INTO b (a_id, name) VALUES (@a_id, 'von');
INSERT INTO b (a_id, name) VALUES (@a_id, 'Bönnhoff');

诀窍是捕获会话变量@a_id中的@a_id值，然后对每个依赖的插入进行重用。(为了说明这一点，我把你变成贵族，对不起:-)

第二个，您应该记住这一点:插入很便宜，但是事务提交很昂贵。这是因为MySQL (InnoDB实际上)在提交之前实际上不会更新表。除非您显式地管理您的事务，否则DBMS使用一个称为“自动提交”的特性，其中它将立即提交每个插入(或更新或删除)。

事务越少，速度越快。因此，要提高大容量加载性能，需要将大约100个插入到单个事务中。(确切的数字并不重要。)你可以这样做：

START TRANSACTION;   /* start an insertion bundle */

INSERT INTO a (name) VALUES ('Claus');
SET @a_id = LAST_INSERT_ID();
INSERT INTO b (a_id, name) VALUES (@a_id, 'von');
INSERT INTO b (a_id, name) VALUES (@a_id, 'Bönnhoff');

INSERT INTO a (name) VALUES ('Oliver');
SET @a_id = LAST_INSERT_ID();
INSERT INTO b (a_id, name) VALUES (@a_id, 'Jones');

... more INSERT operations ...

INSERT INTO a (name) VALUES ('Jeff');
SET @a_id = LAST_INSERT_ID();
INSERT INTO b (a_id, name) VALUES (@a_id, 'Atwood');
COMMIT;               /* commit the bundle */

START TRANSACTION;    /* start the next bundle */

INSERT INTO a (name) VALUES ('Joel');
SET @a_id = LAST_INSERT_ID();
INSERT INTO b (a_id, name) VALUES (@a_id, 'Spolsky');

... more INSERT operations ...

COMMIT;               /* finish the bundle */

(除了LAST_INSERT_ID()之外，所有这些都适用于任何基于SQL的关系数据库管理系统。每个RDBMS都有自己的处理ID的方法。