MySQL更快的INSERT [英] Mysql faster INSERT
问题描述
好吧，我有大约175k INSERT语句，相当大的INSERT语句，例如:
Ok, I've got about 175k INSERT statements, pretty big INSERT statements, example:
INSERT INTO `gast` (`ID`,`Identiteitskaartnummer`,`Naam`,`Voornaam`,`Adres`,`Postcode`,`Stad`,`Land`,`Tel`,`Gsm`,`Fax`,`Email`,`Creditcardnummer`) VALUES ('100001','5121-1131-6328-9416','Cameron','Rhoda','Ap #192-1541 Velit Rd.','54398','Catskill','Bermuda','1-321-643-8255','(120) 502-0360','1 48 428 3971-3704','tempor@justo.org','8378-3645-3748-8446');
(忽略这是荷兰语的事实).所有数据都是完全随机的.
(Disregard the fact this is in Dutch). All the data is completely random.
我必须做大约3到4次.在我的PC上查询10万条INSERT语句大约需要一个小时.有什么办法可以在不更改INSERT语句的情况下更快地做到这一点?我正在使用MySQL Workbench.谢谢.
I've got to do this about 3 to 4 times. Querying 100k INSERT statements takes about an hour on my PC. Is there any way to do this faster without altering the INSERT statements? I'm using MySQL Workbench. Thanks.
编辑
到目前为止，我已经尝试了所有建议的方法.仅使用一个INSERT但使用多个VALUES会给我一个错误，即INSERT语句太大.插入前禁用索引也不会提高性能.
I've tried everything suggested so far. Using only one INSERT but multiple VALUES gives me an error that the INSERT statement is too big. Disabling the indexes before inserting doesn't improve performance either.
我也尝试过通过命令行加载sql文件，但这也无济于事...
I've also tried loading an sql file through command line, but that doesn't do anything either...
每个INSERT的插入性能从0.015s到0.032s不等.
Insert performance varies from 0.015s to 0.032s per INSERT.
推荐答案
将所有数据插入一个表(您将使用该表重新设置数据库的表)中，然后发出INSERT SELECT语句，因为它将随后执行批量处理，而不是175K个不同的语句.
INSERT all of the data into one table - the table you're going to reseed your database with - and then issue an INSERT SELECT statement because it will then execute it as a batch instead of 175K different statements.
此外，当您使用INSERT SELECT语句重新设置数据库的种子时，请关闭目标表ALTER TABLE yourtablename DISABLE KEYS上的约束，然后在ALTER TABLE yourtablename ENABLE KEYS之后将其重新打开.
Also, when you reseed your database with the INSERT SELECT statement, turn off contraints on the target table ALTER TABLE yourtablename DISABLE KEYS and then turn them back on afterwards ALTER TABLE yourtablename ENABLE KEYS.
我还将亲自创建一个覆盖索引放在种子数据表上，因为这样就不必读取数据页.
I would also personally build a covering index on the seed data table because then it wouldn't have to read a data page.
这篇关于MySQL更快的INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
