MySQL 在没有 TRANSACTION 的批量 INSERT 上暂停索引重建

Question

我有很多数据需要 INSERT LOW_PRIORITY 到表中。由于每次插入行时都会重建索引，因此需要很长时间。我知道我可以使用事务，但在这种情况下，如果只有一行失败，我不希望整个集合失败。

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

理想情况下，我想插入 1,000 行左右，设置索引做它的事情，然后插入接下来的 1,000 行。

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

这并不重要，但我正在使用 PHP/PDO 访问 MySQL。您能提供的任何建议将不胜感激。谢谢！

Answer 1

ALTER TABLE tableName DISABLE KEYS
// perform inserts
ALTER TABLE tableName ENABLE KEYS

这将禁用所有非唯一索引的更新。缺点是这些索引也不会用于选择查询。

但是，您可以使用多重插入（INSERT INTO table(...) VALUES(...),(...),(...)，这也将批量更新索引。

Answer 2

AFAIK，对于那些使用 InnoDB 表的人来说，如果您不希望在每次插入后重建索引，则必须使用事务。

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

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

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

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

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