如何在innodb中禁用索引 [英] How to disable index in innodb

问题描述

我正试图通过临时禁用其索引来加速InnoDB表中的批量插入：

I'm trying to speed up bulk insert in an InnoDB table by temporary disabling its indexes:

ALTER TABLE mytable DISABLE KEYS;

但它提供警告：

+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytable' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

我们如何禁用索引？

有哪些替代方案可供选择避免在批量插入时使用索引？

What alternatives are there to avoid using the index when doing bulk inserts?

我们如何加快这个过程？

How can we speed up the process?

推荐答案

您是否尝试过以下操作？

Have you tried the following?

    SET autocommit=0; 
    SET unique_checks=0; 
    SET foreign_key_checks=0;

来自MySQL参考资料 https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

From the MySQL References https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

请参阅批量数据加载提示部分

这篇关于如何在innodb中禁用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！