这个问题转载自堆栈溢出根据评论中的建议，对于重复表示歉意。
问题
问题 1：随着数据库表的大小越来越大，我该如何调整 MySQL 来提高 LOAD DATA INFILE 调用的速度？
问题 2：使用一组计算机来加载不同的 csv 文件，会提高性能还是会毁掉它？（这是我明天使用加载数据和批量插入进行的基准测试任务）
目标
我们正在尝试使用不同的特征检测器和聚类参数组合来进行图像搜索，因此我们需要能够及时构建大型数据库。
机器信息
该机器有 256GB 的 RAM，并且有另外 2 台具有相同数量 RAM 的机器可用，如果可以通过分发数据库来改善创建时间？
表架构
表格模式如下
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| match_index | int(10) unsigned | NO | PRI | NULL | |
| cluster_index | int(10) unsigned | NO | PRI | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| tfidf | float | NO | | 0 | |
+---------------+------------------+------+-----+---------+----------------+创建
CREATE TABLE test
(
match_index INT UNSIGNED NOT NULL,
cluster_index INT UNSIGNED NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
tfidf FLOAT NOT NULL DEFAULT 0,
UNIQUE KEY (id),
PRIMARY KEY(cluster_index,match_index,id)
)engine=innodb;迄今为止的基准测试
第一步是比较批量插入和从二进制文件加载到空表中。
It took: 0:09:12.394571 to do 4,000 inserts with 5,000 rows per insert
It took: 0:03:11.368320 seconds to load 20,000,000 rows from a csv file考虑到从二进制 csv 文件加载数据时的性能差异，首先我使用以下调用加载包含 100K、1M、20M、200M 行的二进制文件。
LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test;2 小时后，我终止了 200M 行二进制文件（~3GB csv 文件）的加载。
所以我运行了一个脚本来创建表，并从二进制文件中插入不同数量的行，然后删除该表，见下图。
从二进制文件插入 1M 行大约需要 7 秒。接下来，我决定对每次插入 1M 行进行基准测试，看看特定数据库大小是否会造成瓶颈。一旦数据库达到约 59M 行，平均插入时间就会下降到约 5,000/秒
设置全局 key_buffer_size = 4294967296 可以稍微提高插入较小二进制文件的速度。下图显示了不同行数的速度
然而，插入 1M 行并没有提高性能。
行数：1,000,000 时间：0:04:13.761428 插入/秒：3,940
vs 对于空数据库
行数：1,000,000 时间：0:00:6.339295 插入/秒：315,492
更新
使用以下顺序执行加载数据，而不是仅使用加载数据命令
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
LOAD DATA INFILE '/mnt/imagesearch/tests/eggs.csv' INTO TABLE test_ClusterMatches;
SET foreign_key_checks=1;
SET unique_checks=1;
COMMIT;
因此，就正在生成的数据库大小而言，这看起来非常有希望，但其他设置似乎不会影响加载数据文件调用的性能。
然后，我尝试从不同的机器加载多个文件，但由于文件太大，导致其他机器超时，因此 load data infile 命令锁定了表。
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction增加二进制文件中的行数
rows: 10,000,000 seconds rows: 0:01:36.545094 inserts/sec: 103578.541236
rows: 20,000,000 seconds rows: 0:03:14.230782 inserts/sec: 102970.29026
rows: 30,000,000 seconds rows: 0:05:07.792266 inserts/sec: 97468.3359978
rows: 40,000,000 seconds rows: 0:06:53.465898 inserts/sec: 96743.1659866
rows: 50,000,000 seconds rows: 0:08:48.721011 inserts/sec: 94567.8324859
rows: 60,000,000 seconds rows: 0:10:32.888930 inserts/sec: 94803.3646283解决方案：在 MySQL 外部预先计算 id，而不是使用自动增量
使用以下方式构建表格
CREATE TABLE test (
match_index INT UNSIGNED NOT NULL,
cluster_index INT UNSIGNED NOT NULL,
id INT NOT NULL ,
tfidf FLOAT NOT NULL DEFAULT 0,
PRIMARY KEY(cluster_index,match_index,id)
)engine=innodb;使用 SQL
LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"
使用脚本预先计算索引似乎已经消除了数据库规模增长带来的性能损失。
更新 2-使用内存表
大约快 3 倍，不考虑将内存表移动到基于磁盘的表的成本。
rows: 0 seconds rows: 0:00:26.661321 inserts/sec: 375075.18851
rows: 10000000 time: 0:00:32.765095 inserts/sec: 305202.83857
rows: 20000000 time: 0:00:38.937946 inserts/sec: 256818.888187
rows: 30000000 time: 0:00:35.170084 inserts/sec: 284332.559456
rows: 40000000 time: 0:00:33.371274 inserts/sec: 299658.922222
rows: 50000000 time: 0:00:39.396904 inserts/sec: 253827.051994
rows: 60000000 time: 0:00:37.719409 inserts/sec: 265115.500617
rows: 70000000 time: 0:00:32.993904 inserts/sec: 303086.291334
rows: 80000000 time: 0:00:33.818471 inserts/sec: 295696.396209
rows: 90000000 time: 0:00:33.534934 inserts/sec: 298196.501594
将数据加载到基于内存的表中，然后将其复制到基于磁盘的表中分成几块使用该查询复制 107,356,741 行，耗时 10 分 59.71 秒
insert into test Select * from test2;
这使得加载 1 亿行大约需要 15 分钟，这与直接将其插入基于磁盘的表大致相同。
答案1
好问题，解释得很好。
如何调整 MySQL 来提高 LOAD DATA INFILE 调用的速度？
您已经对密钥缓冲区进行了较高的设置 - 但这足够吗？我假设这是 64 位安装（如果不是，那么您需要做的第一件事就是升级）并且不在 MSNT 上运行。运行几个测试后，查看 mysqltuner.pl 的输出。
为了最大限度地利用缓存，您可能会发现批量/预排序输入数据的好处（“sort”命令的最新版本具有许多用于对大型数据集进行排序的功能）。此外，如果您在 MySQL 之外生成 ID 号，那么它可能会更有效率。
将使用一组计算机来加载不同的 csv 文件
假设（再次）您希望将输出集表现为单个表，那么您将获得的唯一好处是通过分配排序和生成 ID 的工作 - 您不需要更多数据库。另一方面，使用数据库集群，您会遇到争用问题（除了性能问题之外，您不应该看到它）。
如果您可以对数据进行分片并独立处理生成的数据集，那么是的，您将获得性能优势 - 但这并不会消除调整每个节点的需要。
检查 sort_buffer_size 是否至少有 4 Gb。
除此之外，性能的限制因素全在于磁盘 I/O。有很多方法可以解决这个问题 - 但您可能应该考虑在 SSD 上设置一组镜像条带数据集以获得最佳性能。
答案2
- 考虑一下你的限制因素。几乎可以肯定是单线程 CPU 处理。
- 您已经确定这
load data...比插入更快，因此使用它。 - 您已经确定非常大的文件（按行数计算）会使速度减慢很多；您想将它们分成几部分。
- 使用不重叠的主键，排队至少 N*CPU 集，使用不超过一百万行......可能更少（基准）。
- 在每个文件中使用连续的主键块。
如果您想要真正做到出色，您可以创建一个多线程程序，将单个文件提供给命名管道集合并管理插入实例。
总而言之，您不需要针对这一点对 MySQL 进行过多的调整，因为您只需调整 MySQL 的工作负载即可。
答案3
我不记得确切的语法，但如果它是 inno db，您可以关闭外键检查。
您还可以在导入后创建索引，这可以真正提高性能。