这里有一个关于如何正确配置 mysql (myisam) 以快速执行批量插入(加载数据 infile)的问题。

有 6 Gb 的文本文件要导入，1500 万行，16 列(一些 int，一些 varchar(255)，一个 varchar(40)，一个 char(1) 一些日期时间，一个 mediumtext)。

相对 my.conf 设置:

key_buffer  = 800M
max_allowed_packet = 160M
thread_cache_size = 80
myisam_sort_buffer_size = 400M
bulk_insert_buffer_size = 400M
delay_key_write = ON
delayed_insert_limit = 10000

共有三个索引 - 一个主索引(autincrement int)、一个唯一的 int 和一个唯一的 varchar(40)。

问题是在执行 load data infile 命令后，前 3 gig 数据被快速导入(基于 table.myd 的增加大小 - 5-8 mb/s)，但很快就超过了 3020 Mb 的限制导入速度大大降低 - table.myd 的大小正在增长 0.5mb/s。我注意到，当 Key_blocks_unused 耗尽为零时，导入过程会变慢。这些是导入开始时 mysql> show status like '%key%'; 的输出:

mysql> show status like '%key%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Com_preload_keys       | 0       | 
| Com_show_keys          | 0       | 
| Handler_read_key       | 0       | 
| Key_blocks_not_flushed | 57664   | 
| Key_blocks_unused      | 669364  | 
| Key_blocks_used        | 57672   | 
| Key_read_requests      | 7865321 | 
| Key_reads              | 57672   | 
| Key_write_requests     | 2170158 | 
| Key_writes             | 4       | 
+------------------------+---------+
10 rows in set (0.00 sec)

这就是它在 3020Mb 限制之后的样子，即当 key_blocks_unused 降为零时，这就是批量插入过程变得非常慢的时候:

mysql> show status like '%key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Com_preload_keys       | 0         | 
| Com_show_keys          | 0         | 
| Handler_read_key       | 0         | 
| Key_blocks_not_flushed | 727031    | 
| Key_blocks_unused      | 0         | 
| Key_blocks_used        | 727036    | 
| Key_read_requests      | 171275179 | 
| Key_reads              | 1163091   | 
| Key_write_requests     | 41181024  | 
| Key_writes             | 436095    | 
+------------------------+-----------+
10 rows in set (0.00 sec)

问题很明显，根据我的理解 - 索引存储在缓存中，但是一旦缓存填满，索引就会一个一个地写入磁盘，这很慢，因此所有过程都会变慢。如果我禁用基于 varchar(40) 列的唯一索引，因此所有索引都适合 Key_blocks_used(我猜这是直接依赖于 key_buffer 的变量，是'it?), 全部批量导入成功。所以，我很好奇，如何让mysql一次把所有Key_blocks_used数据放到磁盘，并释放Key_blocks_used？。我知道它可能会即时进行一些排序，但我仍然认为它应该可以进行一些缓存的 RAM 磁盘同步，以便成功管理索引，即使它们不适合内存缓存.所以我的问题是“如何配置 mysql，以便批量插入避免在(几乎)每个索引上写入磁盘，即使所有索引都不适合缓存？”最后一点 - delay_key_write对于给定的表设置为 1，但与禁用时相比，它没有增加任何加速。

提前感谢您的任何想法、想法、解释和 RTM! (:

还有一个小问题 - 在 Key_blocks_unused 变为 0 之前，我如何计算有多少 varchar(40) 索引适合缓存？

附言使用 $myisamchk --keys-used=0 -rq/path/to/db/tbl_name 禁用索引，然后使用 $myisamchk -rq/path/to/db/重新启用它们tbl_name，如 Mysql docs 中所述是一个已知的解决方案，它有效，但只有在批量插入到一个空表中时才有效。当表中已经有一些数据时，索引唯一性检查是必要的，因此禁用索引不是解决方案。

最佳答案

当您使用“load data infile”导入数据时，我认为 mysql 会一个接一个地执行插入，并且在每次插入时，它也会尝试更新索引文件 .MYI，这可能会减慢您的导入速度，因为它会消耗 bot每个单独插入的 I/O 和 CPU 资源。

您可以做的是将 4 个文件添加到您的导入文件以禁用表的键并在插入语句的末尾启用它，您应该会看到不同之处。

LOCK TABLES tableName WRITE;
ALTER TABLE tableName DISABLE KEYS;
----
your insert statement from  go here..
----
ALTER TABLE tableName ENABLE KEYS
UNLOCK TABLES;

如果您不想编辑您的数据文件，请尝试使用 mysqldump 来获取正确的转储文件，并且您不应该在导入数据时遇到这种缓慢的情况。

##Dump the database
mysqldump databaseName > database.sql

##Import the database
mysql databaseName < database.sql

希望这对您有所帮助!

关于mysql - 当索引不适合 key_buffer 时快速 MySQL 批量加载，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1693614/