哪些MySQL设置会影响LOAD DATA INFILE的速度？

Question

让我先介绍一下情况。我们正在尝试将数量相对较高的行（大约每天10-20M）插入到一个MyISAM表中，该表的宽度适中：

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| blah1        | varchar(255) | NO   | PRI |         |       | 
| blah2        | varchar(255) | NO   | PRI |         |       | 
| blah3        | varchar(5)   | NO   | PRI |         |       | 
| blah4        | varchar(5)   | NO   | PRI |         |       | 
| blah5        | varchar(2)   | NO   | PRI |         |       | 
| blah6        | varchar(2)   | NO   | PRI |         |       | 
| blah7        | date         | NO   | PRI |         |       | 
| blah8        | smallint(6)  | NO   | PRI |         |       | 
| blah9        | varchar(255) | NO   | PRI |         |       | 
| blah10       | bigint(20)   | YES  |     | NULL    |       | 
+--------------+--------------+------+-----+---------+-------+

除了那个巨大的主键之外，唯一的索引是在blah7日期字段上。我们正在使用LOAD DATA INFILE并且看到非常糟糕的性能，加载数据需要大约2个小时。我曾经认为LOAD DATA INFILE比这快得多。

有趣的是，我们还有一些不那么庞大的表（5-6个字段），我们也使用LOAD DATA INFILE批量处理数据，并且在这些表上看到更好的性能。记录数要小得多，这让我想到当我们加载大表时可能会遇到缓冲区大小限制，并且必须去读取磁盘（实际上，除了读取磁盘，还有什么能解释如此缓慢的加载时间呢？）。

这就带来了我的问题。在处理LOAD DATA INFILE命令时，哪些my.cnf设置最重要？

- Jason

Answer 1

一般情况下，插入索引会导致性能下降。在插入数据之前，您最好先删除索引，然后再重新索引。据 http://forum.percona.com/s/m/983/ 的说法，MySQL通常很快地加载MyISAM表中的数据，但也有例外情况，即无法通过排序重建索引，而是逐行构建它们。这可能是由于错误的配置（即myisam_max_sort_file_size或myisam_max_extra_sort_file_size太小）或缺乏优化所致，尤其是如果你有大型（不适合内存）的PRIMARY或UNIQUE索引。此外，还请查看http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/和http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/insert-speed.html。

- mattkemp

Answer 2

我不了解具体的设置，但我猜测你的性能问题可能与复合主键有关。

- Jordan S. Jones

Answer 3

如果您的表是MyISAM，并且正在向非空表添加数据，则bulk_insert_buffer_size很重要。

MyISAM使用一种特殊的树状缓存，使得在向非空表添加数据时，通过LOAD DATA INFILE进行批量插入更快。BULK_INSERT_BUFFER_SIZE变量限制了每个线程中缓存树的大小（以字节为单位）。将其设置为0会禁用此优化。默认值为8MB。最大值为4GB。

如果正在向非空表添加数据，则调整bulk_insert_buffer_size变量可以使数据插入更快。通常情况下，当要插入的数据超过10k行时，它会显示出改进效果。但很难说什么是正确的值，因此请尝试使用增量缓冲区大小值进行测试。

此外，如果还设置了以下变量，则性能提升将更加明显：

• MYISAM_SORT_BUFFER_SIZE
• KEY_BUFFER_SIZE

在使用以下alter table命令加载数据之前，您还应考虑禁用索引：

alter table t disable keys;

- Deepu S Nath