MySQL控制台导入大量SQL文件时变慢

本文介绍了MySQL控制台导入大量SQL文件时变慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我通过My​​SQL控制台导入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.

希望有帮助.