根据引擎类型,您一定要选择适合的方法来优化MyISAM引擎或InnoDB引擎。
我们最近进行了基准测试,比较了不同的数据插入方式,并测量了自插入之前到所有索引完全恢复的时间。这是在一个空表上进行的,但我们使用了多达1000万行。
在我们的测试中,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
- MySQL的行是按插入顺序排序的,并且索引会被重建。通过进行批量插入并不会出现“额外的碎片化”。
如果我有错误,请随时评论。
更新:根据我们更近期和完整的测试结果,禁用/启用键的建议是错误的。
一位同事运行了多个不同的测试程序 - 在具有InnoDB / MyISAM预设和空表的情况下,使用LOAD DATA LOCAL
、INSERT INTO
、REPLACE INTO
和UPDATE
进行选择和插入速度,在“密集”和“碎片化”的表格上进行测试(我不太确定如何进行,我想它是沿着DELETE FROM ... ORDER BY RAND() LIMIT ...
进行的,带有一个固定的种子,以便仍然可以进行比较),同时启用和禁用索引。
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.