MySQL控制台导入大量SQL文件时变慢 [英] MySQL console slow on import of huge SQL files
问题描述
我通过MySQL控制台导入SQL的速度很慢，并且随着我们的SQL文件每天都在增加，我想知道是否有其他方法可以更快地导入SQL文件.
My import of SQL via the MySQL console is rather slow, and, as our SQL file is increasing every day, I would like to know if there are any alternatives on how to import an SQL file faster.
无法切换到Oracle或其他系统，配置必须保持不变.
Changing to Oracle or other systems is not an option, the configuration has to stay the same.
当前，SQL文件为:1.5 GB. 我正在使用Apache 2.2.14，PHP 5.2.11和MySQL 5.1.41的 WAMP .
Currently the SQL file is: 1.5 GB. I'm on WAMP with Apache 2.2.14, PHP 5.2.11 and MySQL 5.1.41.
也许问题出在这里，导入是通过一个简单的操作完成的:
Perhaps the issue is here, import is done by a simple:
mysql -u username -p dbname < sqlfilename.sql
有什么建议吗?
推荐答案
在导入过程中启用索引会使您的服务器减慢爬网速度. ALTER TABLE表名DISABLE KEYS;并在导入之前和之后使用..ENABLE KEYS可以提高导入速度，但是需要花费一些时间来重新创建索引，因此毕竟可能不会大大提高速度.
Having indexes enabled during import will slow your server down to a crawl. ALTER TABLEtablenameDISABLE KEYS; and using ..ENABLE KEYS prior to and after import, will improve import speed, but will take some time to re-create indexes, so it might not be a big speed gain after all.
另外，也许使用myisam表(与具有引用完整性选项的innodb相比)通常会提供更好的性能，因为不涉及引用完整性开销.
Also, perhaps using myisam tables (in contrast to innodb with referential integrity options) usually gives better performance, as there is no referential integrity overhead involved.
就我个人而言，我不使用mysql控制台中的import语句，而是使用mysql -uUSER -pPASS DBNAME < file.sql导入sql文件，并且对我来说效果很好.
Personally, I don't use import statement from mysql console, but import sql files using mysql -uUSER -pPASS DBNAME < file.sql, and it works well for me.
希望有帮助.
这篇关于MySQL控制台导入大量SQL文件时变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
