时间戳

首页 > 解决方案 > 如何提高插入速度？

mysql - 如何提高插入速度？

问题描述

我需要将数据从外部 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的id，唯一的方法是在表a中插入一行，获取最后一个id 然后将所有连接的条目插入到表 b 中。

但是，当我逐行插入时，我的数据库非常慢。在表a中插入大约35000行，在表b中插入120000行需要1h多。如果我在表 a 上批量插入大约 1000 行（仅用于在不填充表 b 的情况下进行测试），它的速度非常快（不到 3 分钟）

我想一定有一个解决方案可以加快我的导入速度。

谢谢你的帮助

标签： mysqlquery-optimizationetlbulk-load

解决方案

我假设您正在使用一种编程语言来驱动您的插入。您需要能够对这一系列操作进行编程。

首先，您需要使用此序列将行放入a和从属行放入b。它使用LAST_INSERT_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，然后为每个依赖的 INSERT 重用它。（我把你变成贵族来说明这一点，对不起:-)

其次，您应该记住这一点：INSERT 很便宜，但事务 COMMIT很昂贵。这是因为 MySQL（实际上是 InnoDB）直到 COMMIT 才真正更新表。除非您明确管理事务，否则 DBMS 使用称为“自动提交”的功能，它会立即提交每个 INSERT（或 UPDATE 或 DELETE）。

更少的交易让您获得更快的速度。因此，为了提高批量加载性能，您希望将 100 个左右的 INSERT 捆绑到一个事务中。（确切的数字并不重要。）您可以执行以下操作：

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。每个 RDBMS 都有自己的处理 ID 的方式。（

推荐阅读