MySQL 如何在innodb中禁用索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9524938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to disable index in innodb
提问by fanchyna
I'm trying to speed up bulk insert in an InnoDB table by temporary disabling its indexes:
我试图通过临时禁用索引来加速 InnoDB 表中的批量插入：
ALTER TABLE mytable DISABLE KEYS;
But it gives a warning:
但它发出警告：
+-------+------+-------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------+
| Note | 1031 | Table storage engine for 'mytable' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)
How can we disable the indexes?
我们如何禁用索引？
What alternatives are there to avoid using the index when doing bulk inserts?
在进行批量插入时，有哪些替代方法可以避免使用索引？
How can we speed up the process?
我们怎样才能加快这个过程？
回答by lurkerbelow
Have you tried the following?
您是否尝试过以下方法？
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
From the MySQL References https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
来自 MySQL 参考https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
See Section "Bulk Data Loading Tips"
请参阅“批量数据加载提示”部分
回答by RolandoMySQLDBA
There is a very good reason why you cannot execute DISABLE KEYSon an InnoDB table; InnoDB is not designed to use it, and MyISAM is.
不能DISABLE KEYS在 InnoDB 表上执行有一个很好的理由；InnoDB 不是为了使用它而设计的，而 MyISAM 是。
In fact, here is what happens when you reload a mysqldump:
实际上，当您重新加载 mysqldump 时会发生以下情况：
You will see a CREATE TABLEfor a MyISAM table following by a write lock.
您将看到CREATE TABLEMyISAM 表后面跟着一个写锁。
Before all the bulk inserts are run, a call to ALTER TABLE ... DISABLE KEYSis done.
在运行所有批量插入之前，会调用ALTER TABLE ... DISABLE KEYS。
What this does is turn off secondary indexes in the MyISAM table.
这样做是关闭 MyISAM 表中的二级索引。
Then, bulk inserts are done. While this is being done, the PRIMARY KEY and all UNIQUE KEYS in the MyISAM table are disabled. Before the UNLOCK TABLEs, a call ALTER TABLE ... ENABLE KEYSis done in order to rebuild all non-unique indexes linearly.
然后，完成批量插入。执行此操作时，MyISAM 表中的 PRIMARY KEY 和所有 UNIQUE KEY 都被禁用。在 之前UNLOCK TABLEs，调用ALTER TABLE ... ENABLE KEYS是为了线性重建所有非唯一索引。
IMHO this operation was not coded into the InnoDB Storage Engine because all keys in a non-unique index come with the primary key entry from gen_clust_index(aka Clustered Index). That would be a very expensive operation since building a non-unique index would require O(n log n) running time to retrieve each unique key to attach to a non-unique key.
恕我直言，此操作未编码到 InnoDB 存储引擎中，因为非唯一索引中的所有键都带有来自gen_clust_index（又名聚集索引）的主键条目。这将是一项非常昂贵的操作，因为构建非唯一索引需要 O(n log n) 运行时间来检索每个唯一键以附加到非唯一键。
In light of this, posting a warning about trying to DISABLE KEYS/ENABLE KEYSon an InnoDB table is far easier than coding exceptions to the mysqldump for any special cases involving non-MyISAM storage engines.
鉴于此，DISABLE KEYS/ENABLE KEYS对于涉及非 MyISAM 存储引擎的任何特殊情况，在 InnoDB 表上发布有关尝试的警告比将异常编码到 mysqldump 容易得多。
回答by staabm
to reduce the costs for re-calculating the indexes you should insert the data either using DATA INFILE or using Mysql Multi Row Inserts, like
为了减少重新计算索引的成本，您应该使用 DATA INFILE 或使用 Mysql Multi Row Inserts 插入数据，例如
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
插入 tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
-> so inserting several rows with one statement.
-> 所以用一个语句插入多行。
How many rows one can insert with one statement depends on the max_allowed_packetmysql setting.
一个语句可以插入多少行取决于max_allowed_packetmysql 设置。
回答by KciNicK
A little late but... whatever... forget all the answers here, don't disable the indexes, there's no way, just drop them ALTER TABLE tablename DROP INDEX whatever, bulk insert the data, then ALTER TABLE tablename ADD INDEX whatever(whatever); the amount of time recreating the indexes is 1% of the bulk insert with indexes on it, like 400000 rows took 10 minutes with indexes and like 2 seconds without them..., cheers...
有点晚了但是......无论如何......忘记这里的所有答案，不要禁用索引，没有办法，只需删除它们 ALTER TABLE tablename DROP INDEX whatever，批量插入数据，然后 ALTER TABLE tablename ADD INDEX whatever( whatever) ; 重新创建索引的时间是带有索引的大容量插入的 1%，比如 400000 行有索引需要 10 分钟，没有索引需要 2 秒......，欢呼......
