MySQL 禁用和启用密钥
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8210608/
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
MySQL disable & enable keys
提问by Mellon
In my MySQL database, there is a table which has 2,000,000 records. Now, I would like to insert another 6,000,000 new records into this table.
在我的 MySQL 数据库中，有一个包含 2,000,000 条记录的表。现在，我想在该表中再插入 6,000,000 条新记录。
To speed up the insertion, I though I should use disable/enable keys like following:
为了加快插入速度，我想我应该使用禁用/启用键，如下所示：
ALTER TABLE cars DISABLE KEYS;
INSERT INTO cars ...
...
...
INSERT INTO cars ...
ALTER TABLE search_all_values ENABLE KEYS;
OPTIMIZE TABLE cars;
But I somehow feel that, the disable/enable keys would make more sense to be used for empty table insertion.
但我不知何故觉得，禁用/启用键用于空表插入更有意义。
While in my case, I have already 2,000,000 records in the table, when ENABLE KEYS, mysql will re-create all the indexes (including the existing records and new added records) which probably won't produce a efficient data insertion as a whole in my case. As re-create all the indexes will take long timeand probably so does OPTIMIZE TABLE
虽然在我的情况下，我已经有 2,000,000 条记录在表中，当ENABLE KEYSmysql 将重新创建所有索引（包括现有记录和新添加的记录），这可能不会在我的情况下整体产生有效的数据插入. 由于重新创建所有索引将需要很长时间，可能也是如此OPTIMIZE TABLE
I would like to ask your opinion about am I right and how can I have a efficent data insertion in my case?
我想请问您对我是否正确以及如何在我的情况下有效插入数据的意见？
回答by Arne
You definitely have to pick your approach based on the engine type... optimizing for MyISAMor for InnoDB.
您绝对必须根据引擎类型选择您的方法……针对 MyISAM或InnoDB 进行优化。
We recently ran a benchmark comparing different ways to insert data and measured the time from before insertion and until all indices are fully restored. It was on an empty table, but we used up to 10 million rows.
我们最近运行了一个基准测试，比较不同的数据插入方式，并测量了从插入前到所有索引完全恢复的时间。它在一张空桌子上，但我们使用了多达 1000 万行。
MyISAM with LOAD DATA INFILEand ALTER TABLE ... ENABLE/DISABLE KEYSwon hands down in our test (on a Windows 7 system, MySQL 5.5.27 - now we're trying it on a Linux system).
MyISAM数据与LOAD DATA INFILE和ALTER TABLE ... ENABLE/DISABLE KEYS不战而胜在我们的测试（Windows 7系统上，MySQL的5.5.27 -现在我们正在尝试它在Linux系统上）。
ENABLE and DISABLE KEYS does not work for InnoDB, it's MyISAM only. For InnoDB, use SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;if you are sure your data doesn't contain duplicates (don't forget to set them to 1after the upload is complete).
ENABLE 和 DISABLE KEYS 不适用于 InnoDB，它仅适用于 MyISAM。对于 InnoDB，SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;如果您确定您的数据不包含重复项，请使用（不要忘记1在上传完成后将它们设置为）。
I don't think you need OPTIMIZE TABLEafter a bulk insert - MySQL rows are ordered by insertion and the index is rebuilt anyway. There's no "extra fragmentation" by doing a bulk insert.
我认为OPTIMIZE TABLE在批量插入后不需要- MySQL 行按插入排序，无论如何都会重建索引。进行批量插入不会产生“额外的碎片”。
Feel free to comment if I made factual errors.
如果我犯了事实错误，请随时发表评论。
UPDATE:According to our more recent and complete test results, the advice to DISABLE / ENABLE keys is wrong.
更新：根据我们最近和完整的测试结果，禁用/启用键的建议是错误的。
A coworker had a program run multiple different tests - a table with InnoDB / MyISAM prefilled and empty, selection and insertions speeds with LOAD DATA LOCAL, INSERT INTO, REPLACE INTOand UPDATE, on "dense" and "fragmented" tables (I'm not quite sure how, I think it was along the lines of DELETE FROM ... ORDER BY RAND() LIMIT ...with a fixed seed so it's still comparable) and enabled and diasabled indices.
与InnoDB的/ MyISAM数据表预填充并清空，选择和插入速度： -一个同事有一个程序运行多个不同的测试LOAD DATA LOCAL，INSERT INTO，REPLACE INTO并UPDATE在“密集”和“碎片化”的表，（我不太清楚怎么样，我觉得它沿的线条DELETE FROM ... ORDER BY RAND() LIMIT ...与固定的种子所以它仍然是相当的）并启用和diasabled指数。
We tested it with many different MySQL versions (5.0.27, 5.0.96, 5.1.something, 5.5.27, 5.6.2) on Windows and Linux (not the same versions on both OS, though). MyISAM only won when the table was empty. InnoDB was faster when data was present already and generally performed better (except for hdd-space - MyISAM is smaller on disk).
我们在 Windows 和 Linux 上使用许多不同的 MySQL 版本（5.0.27、5.0.96、5.1.something、5.5.27、5.6.2）对其进行了测试（不过，两个操作系统上的版本不同）。MyISAM 仅在表为空时获胜。当数据已经存在时，InnoDB 速度更快，并且通常性能更好（硬盘空间除外 - MyISAM 在磁盘上更小）。
Still, to really benefit from it, you have to test it yourself - with different versions, different configuration settings and a lot of patience - especially regarding weird inconsistencies (5.0.97 was a lot faster than 5.5.27 with the same config - we're still searching the cause). What we did find wasthat DISABLE KEYSand ENABLE KEYSare next to worthless and sometimes harmfull if you don't start with an empty table.
尽管如此，要真正从中受益，您必须自己测试 - 使用不同的版本，不同的配置设置和大量的耐心 - 特别是关于奇怪的不一致（5.0.97 比具有相同配置的 5.5.27 快得多 - 我们'仍在寻找原因）。我们确实发现了那个DISABLE KEYS和ENABLE KEYS旁边毫无价值，有时有害的：如果你不带一个空表开始。
回答by matthiasmullie
Indexing the new keys will take up some time. It's up to you to decide if you want it to be done all at once (disabling it first) or one at a time (by keeping it as-is and letting it index as each record is being added)
索引新键需要一些时间。由您决定是一次完成（首先禁用它）还是一次完成（通过保持原样并在添加每条记录时对其进行索引）
I'd go for the latter, not disabling your keys. If you fear stressing the server to much, you could try inserting in batches, e.g. only a certain amount of inserts per minute.
我会选择后者，而不是禁用您的密钥。如果您担心服务器压力过大，您可以尝试分批插入，例如每分钟仅插入一定数量。
