在没有TRANSACTION的批量INSERT上重建MySQL暂停索引 [英] MySQL pause index rebuild on bulk INSERT without TRANSACTION

问题描述

我有很多数据要将LOW_PRIORITY插入到表中。每次插入行时都会重建索引，这需要很长时间。我知道我可以使用交易，但是如果只有一行失败，我不希望整套都失败。

I have a lot of data to INSERT LOW_PRIORITY into a table. As the index is rebuilt every time a row is inserted, this takes a long time. I know I could use transactions, but this is a case where I don't want the whole set to fail if just one row fails.

有没有让MySQL停止在特定表上重建索引的方法，直到我告诉它可以恢复？

理想情况下，我想插入1,000行或所以，设置索引做其事，然后插入接下来的1000行。

Ideally, I would like to insert 1,000 rows or so, set the index do its thing, and then insert the next 1,000 rows.

我不能使用INSERT DELAYED，因为我的表类型是InnoDB。否则，INSERT DELAYED对我来说是完美的。

I cannot use INSERT DELAYED as my table type is InnoDB. Otherwise, INSERT DELAYED would be perfect for me.

不是很重要，但我使用PHP / PDO来访问MySQL。您可以给予任何建议，我们将不胜感激。谢谢！

Not that it matters, but I am using PHP/PDO to access MySQL. Any advice you could give would be appreciated. Thanks!

推荐答案

AFAIK，对于那些使用InnoDB表的人，如果你不想在每个<$之后重建索引c $ c> INSERT ，您必须使用事务。

AFAIK, for those that use InnoDB tables, if you don't want indexes to be rebuilt after each INSERT, you must use transactions.

例如，要插入一批1000行，请使用以下SQL： / p>

For example, for inserting a batch of 1000 rows, use the following SQL:

SET autocommit=0;
//Insert the rows one after the other, or using multi values inserts
COMMIT;

通过禁用自动提交，将在第一个 INSERT启动一个事务。然后，一个接一个地插入行，最后，提交事务并重建索引。

By disabling autocommit, a transaction will be started at the first INSERT. Then, the rows are inserted one after the other and at the end, the transaction is committed and the indexes are rebuilt.

如果在执行其中一个行程期间发生错误 INSERT ，事务未回滚但向客户报告错误，该客户可以选择回滚或继续。因此，如果您不希望在 INSERT 失败时回滚整个批次，则可以记录 INSERT s失败并继续插入行，最后在最后提交事务。

If an error occurs during execution of one of the INSERT, the transaction is not rolled back but an error is reported to the client which has the choice of rolling back or continuing. Therefore, if you don't want the entire batch to be rolled back if one INSERT fails, you can log the INSERTs that failed and continue inserting the rows, and finally commit the transaction at the end.

但是，考虑到包装 INSERT <事务中的/ code> s意味着在提交事务之前您将无法看到插入的行。可以将 SELECT 的事务隔离级别设置为 READ_UNCOMMITTED ，但正如我测试过的那样，行当 SELECT 非常接近 INSERT 时，不可见。请参阅我的发布。

However, take into account that wrapping the INSERTs in a transaction means you will not be able to see the inserted rows until the transaction is committed. It is possible to set the transaction isolation level for the SELECT to READ_UNCOMMITTED but as I've tested it, the rows are not visible when the SELECT happens very close to the INSERT. See my post.

这篇关于在没有TRANSACTION的批量INSERT上重建MySQL暂停索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！