MySQL控制台在导入巨大的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.

我通过MySQL控制台导入SQL的速度相当慢,而且,随着我们的SQL文件每天都在增加,我想知道是否有更快的方法可以更快地导入SQL文件。

Changing to Oracle or other systems is not an option, the configuration has to stay the same.

不能选择更改为Oracle或其他系统,配置必须保持不变。

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.

目前SQL文件是:1.5 GB。我正在使用Apache 2.2.14,PHP 5.2.11和MySQL 5.1.41进行WAMP。

Perhaps the issue is here, import is done by a simple:

也许问题在这里,导入是通过一个简单的:

mysql -u username -p dbname < sqlfilename.sql

Any suggestions?

4 个解决方案

#1

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.

在导入期间启用索引会使服务器速度降低到爬行速度。 ALTER TABLEtablenameDISABLE KEYS;并且在导入之前和之后使用..ENABLE KEYS将提高导入速度,但是需要一些时间来重新创建索引,因此它可能不会是一个很大的速度增益。

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.

此外,也许使用myisam表(与具有参照完整性选项的innodb相比)通常会提供更好的性能,因为不涉及参考完整性开销。

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控制台的import语句,而是使用mysql -uUSER -pPASS DBNAME 导入sql文件,对我来说效果很好。

Hope it helps.

希望能帮助到你。

#2

Try these http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

试试这些http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

It's maybe an autocommit issue, turn that off then see what happends.

这可能是一个自动提交问题,关闭它然后看看发生了什么。

SET autocommit=0 ; source <your dump file> ; COMMIT ;

#3

Switching autocommit off is the first of a series of recommendations given in Bulk Data Loading for InnoDB Tables to speed up restore operations for MySQL.

切换自动提交是InnoDB表的批量数据加载中给出的一系列建议中的第一个,以加速MySQL的恢复操作。

Instead of switching autocommit off manually at restore time you can already dump your MySQL data in a way that includes all necessary statements right into your SQL file.

您可以在恢复时手动关闭自动提交,而不是以包含所有必要语句的方式将MySQL数据转储到SQL文件中。

The command line parameter for mysqldump is --no-autocommit. You might also consider to add --opt which sets a combination of other parameters to speed up restore operations.

mysqldump的命令行参数是--no-autocommit。您还可以考虑添加--opt,它设置其他参数的组合以加速恢复操作。

Here is an example for a complete mysqldump command line as I use it, containing --no-autocommit and --opt:

以下是我使用它时完整的mysqldump命令行的示例,其中包含--no-autocommit和--opt:

mysqldump -hlocalhost -uMyUser -p'MyPassword' --no-autocommit --opt --default-character-set=utf8 --quote-names  MyDbName  >  dump.sql

For details of these parameters see the reference of mysqldump

有关这些参数的详细信息,请参阅mysqldump的参考

#4

I'm not sure if this will solve your problem, becuase I'm not sure where the bottleneck is... However, I've have been really impressed with the free Toad for MySQL tool as a replacement MySQL management console. It's got great import tools, and is generally miles better IMO than the standard MySQL management console.

我不确定这是否能解决您的问题,因为我不确定瓶颈在哪里......但是,我对使用免费的Toad for MySQL工具作为替代MySQL管理控制台感到非常满意。它有很棒的导入工具,通常比标准的MySQL管理控制台更好的IMO。

#1