恢复 InnoDB 表：非常非常慢？
我曾经处理Myisam表，但是对于外部连接我改为Innodb表（转换并添加外键）。我经常从服务器下载数据库，并将其填充到本地主机。当它是 myisam 表时，它曾经快得要命。但现在需要30多分钟...我知道它必须在插入记录之前进行检查，但我禁用了外键检查。其中一些行：
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` ( `ID` int(10) unsigned NOT NULL auto_increment, `NAME` varchar(255) NOT NULL default '', PRIMARY KEY (`ID`), UNIQUE KEY `Index_2` (`NAME`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
TRUNCATE TABLE t1;
删除/创建/截断所有表。然后INSERT，INSERT...如何让它更快？
I used to deal with Myisam tables, but for foreign connections I changed to Innodb tables (converted, and added foreign keys). I often download the database from the server, and fill the localhost with it. When it was myisam tables, it used to be fast as hell. But now it requires more than 30 minutes... I know it must do checks before insert records, but I disabled the foreign key checking. Some line of it:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` ( `ID` int(10) unsigned NOT NULL auto_increment, `NAME` varchar(255) NOT NULL default '', PRIMARY KEY (`ID`), UNIQUE KEY `Index_2` (`NAME`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
TRUNCATE TABLE t1;
drop/create/truncate all tables. Then INSERT, INSERT... how to make it faster?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
假设您使用
mysqldump，请使用--extended-insert。这会将多行的插入合并到一个语句中。另请尝试
--disable-keys选项。这不仅会禁用外键检查，还会禁用索引更新，直到插入所有行。一次更新所有索引通常要快得多。如果您不使用
mysqldump，则可以在转储之前执行ALTER TABLE table_name DISABLE KEYS;，并在转储之后执行ALTER TABLE table_name ENABLE KEYS;每个表的转储。但是，您可能会发现特定于数据库引擎的备份工具会更快。尝试 XtraBackup。
Assuming you use
mysqldump, use--extended-insert. This will combine the inserts of multiple rows into one statement.Also try the
--disable-keysoption. Not only will this disable foreign key checks, it will also disable updating of indices until all rows have been inserted. It's typically much faster to update the index all at once.If you're not using
mysqldump, you can executeALTER TABLE table_name DISABLE KEYS;before the dump andALTER TABLE table_name ENABLE KEYS;after the dump for each table.However, you'll likely find that a database-engine specific backup tool will be faster. Try XtraBackup.