Cron 作业中的 MySQL 事务

Question

我的 Ubuntu 服务器上有一个 PHP DAEMON，用于将大量数据插入到 InnoDB 中。该平台的用户也正在使用相同的表格。

DAEMON 未在 TRANSACTION 模式下运行时大约需要 60-70 秒来执行 100.000 次插入。在 TRANSACTION 模式下运行时，BEGIN .... COMMIT 需要 15-20 秒。

但是，TRANSACTION 模式会锁定表，并阻止用户在 DAEMON TRANSACTION 执行时使用平台进行插入吗？锁定用户正在操作的表超过 20 秒当然是不可取的:)

嗯，我正在批量插入 500 和 500 个 FOR 循环 INSERT INTO (col1, col2) VALUES (a,b) 等。这很好，并且运行顺利，但是我能够加快速度如果我在循环之前发出 BEGIN，并在循环之后发出 COMMIT，则该过程会显着，但这意味着 BEGIN/COMMIT 之间的时间超过 60 秒。但是，当系统执行数十万次插入时，使用该平台的人可以对同一个表进行插入。系统会为用户插入生成插入帐户，还是用户必须等待 XX 秒才能处理插入？

Answer 1

根据您的描述，您使用 innodb 并默认 autocommit模式启用，您可以在循环中一条一条地插入记录。自动提交模式意味着每个插入都被封装到自己的事务中，这很好，但非常慢，因为每个记录都单独持久化到磁盘中。

如果您包装在 begin - commit 语句中插入记录的循环，则所有插入都将在单个事务中运行，并且仅在以下情况下持久保存到磁盘一次:发出commit - 这就是您体验速度提升的原因。

无论你以哪种方式插入记录，innodb都会使用锁。然而，innodb only locks the record being inserted :

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

这意味着，打开一个仅插入记录的较长时间的事务不会干扰其他用户向同一个表中插入记录。

请注意，在循环中发出单个插入语句是将大量数据插入 MySQL 的效率最低的方法。

使用bulk insert (在循环中构建单个插入语句并在循环后执行，注意 max_allowed_packet 设置:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

或者使用load data infile陈述。

这两种方案可以显着加快数据插入速度，并且不会造成表锁。