MySQL禁用&启用键 [英] MySQL disable & enable keys

问题描述

在我的MySQL数据库中，有一个有2,000,000条记录的表。现在，我想在这个表中插入另外6,000,000个新记录。

为了加快插入，我虽然应该使用如下的禁用/启用键： p>

  ALTER TABLE汽车DISABLE KEYS; 
 
 INSERT INTO汽车... 
 ... 
 ... 
 INSERT INTO汽车... 
 
 ALTER TABLE search_all_values ENABLE KEYS; 
 
优化台车; 
  

但我不知怎的感觉，禁用/启用键会更有意义的用于空表插入。

在我的例子中，我已经有2,000,000条记录，当 ENABLE KEYS 创建所有索引（包括现有记录和新添加的记录），这在我的情况下可能不会产生一个有效的数据插入作为一个整体。 重新创建所有索引需要很长时间，可能还会 OPTIMIZE TABLE

解决方案

我想要问您对我的意见是否正确，如何在我的案例中插入效率数据？ >您必须根据引擎类型...优化选择您的方法...优化为MyISAM 或为InnoDB < a>。

我们最近运行了一个基准，比较了插入数据的不同方法，并测量从插入之前到所有索引完全恢复的时间。

MyISAM与 LOAD DATA INFILE 和 ALTER TABLE ... ENABLE / DISABLE KEYS 在我们的测试中胜出（在Windows 7系统上，MySQL 5.5.27 - 现在我们在Linux系统上尝试）。

ENABLE和DISABLE KEYS不适用于InnoDB，它仅适用于MyISAM。对于InnoDB，使用 SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; 如果您确定您的数据不包含重复的（不要忘记在上传后将它们设置为 1 完成）。

在批量插入后我不认为你需要 OPTIMIZE TABLE 插入和索引重建无论如何。通过批量插入没有额外的碎片。

如果我犯了实际错误，请随意评论。

UPDATE：根据我们最近的完整测试结果，对DISABLE / ENABLE键的建议是错误的。

程序运行多个不同的测试 - 一个包含InnoDB / MyISAM的表，预填充和空，选择和插入速度为 LOAD DATA LOCAL ， INSERT INTO ， REPLACE INTO 和 UPDATE 确定如何，我认为这是沿着 DELETE FROM ... ORDER BY RAND（）LIMIT ... 与固定种子的行，所以它仍然可比）和启用和

我们在Windows上测试了许多不同的MySQL版本（5.0.27,5.0.96,5.1.something，5.5.27,5.6.2） Linux（不是两个操作系统上的相同版本，虽然）。 MyISAM只有在桌子是空的时候赢了。 InnoDB更快，当数据已经存在，一般表现更好（除了hdd空间 - MyISAM在磁盘上较小）。

仍然，为了真正受益，必须自己测试 - 使用不同的版本，不同的配置设置和很多耐心 - 特别是关于奇怪的不一致（5.0.97是一个很快比5.5.27与相同的配置 - 我们仍在搜索原因）。 我们发现 c> c> c> c>是无价值的harmfull如果你不从一个空表开始。

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.

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 time and probably so does 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?

解决方案

You definitely have to pick your approach based on the engine type... optimizing for MyISAM or for 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.

MyISAM with LOAD DATA INFILE and ALTER TABLE ... ENABLE/DISABLE KEYS won hands down in our test (on a Windows 7 system, MySQL 5.5.27 - now we're trying it on a Linux system).

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 1 after the upload is complete).

I don't think you need OPTIMIZE TABLE after 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.

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 INTO and 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.

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).

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 was that DISABLE KEYS and ENABLE KEYS are next to worthless and sometimes harmfull if you don't start with an empty table.

这篇关于MySQL禁用&amp;启用键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！