我试图优化我的代码插入数据到MySQL的一部分。我应该链插入,使一个巨大的多行插入或多个单独的插入更快?
当前回答
https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
插入一行所需的时间由以下因素决定,其中数字表示大致的比例: 连接:(3) 向服务器发送查询:(2) 解析查询:(2) 插入行:(1 ×行大小) 插入索引:(1 ×索引个数) 关闭:(1)
由此可见,发送一个大语句将为每个插入语句节省7的开销,进一步阅读文本还说:
如果同时插入来自同一客户端的多行,请使用带有多个VALUES列表的INSERT语句一次插入几行。这比使用单独的单行INSERT语句快得多(在某些情况下快很多倍)。
其他回答
多次插入更快,但它有线程。另一个危险是禁用约束检查临时使插入快得多。你的桌子有没有它并不重要。例如,测试禁用外键,享受速度:
SET FOREIGN_KEY_CHECKS=0;
当然,你应该在插入后重新打开它:
SET FOREIGN_KEY_CHECKS=1;
这是插入大数据的常用方法。 数据完整性可能会被破坏,所以在禁用外键检查之前应该注意这一点。
一个主要因素是您是否使用事务引擎以及是否开启了自动提交。
自动提交是默认开启的,你可能想让它保持开启状态;因此,您执行的每个插入操作都会执行自己的事务。这意味着如果您对每行执行一次插入,那么您将为每行提交一个事务。
假设只有一个线程,这意味着服务器需要为每一行同步一些数据到磁盘。它需要等待数据到达一个持久存储位置(最好是RAID控制器中有电池支持的ram)。这本身就相当缓慢,可能会成为这些情况下的限制因素。
当然,我假设你正在使用事务性引擎(通常是innodb),并且你没有调整设置来降低持久性。
我还假设您使用单个线程来进行这些插入。使用多线程会让事情变得有点混乱,因为一些版本的MySQL在innodb中有工作组提交功能——这意味着多个线程可以共享一次写入事务日志的操作,这很好,因为这意味着更少的同步到持久存储。
另一方面,结果是,您确实希望使用多行插入。
它会产生反效果,但在大多数情况下至少是10,000行。因此,如果将它们批量处理到1,000行,则可能是安全的。
如果你在使用MyISAM,还有很多其他的东西,但我不会用这些来烦你。和平。
我只是做了一个小的基准测试,似乎对于很多行它不是更快。这里是我插入28000行的结果:
比10000:164.96秒 到5000分37秒 到1000时:12.56秒 到600秒:12.59秒 比500:13.81秒 到250秒:17.96秒 到400时:14.75秒 差100分27秒
看来1000 * 1000是最好的选择。
下面是我做的一个PHP小测试的结果:
我试图在3种不同的方式插入3000条记录,使用PHP 8.0, MySQL 8.1 (mysqli)
多个插入查询,包含多个事务:
$start = microtime(true);
for($i = 0; $i < 3000; $i++)
{
mysqli_query($res, "insert into app__debuglog VALUE (null,now(), 'msg : $i','callstack','user','debug_speed','vars')");
}
$end = microtime(true);
echo "Took " . ($end - $start) . " s\n";
完成5次,平均11.132秒(+/- 0.6秒)
多个插入查询,一个事务:
$start = microtime(true);
mysqli_begin_transaction($res, MYSQLI_TRANS_START_READ_WRITE);
for($i = 0; $i < 3000; $i++)
{
mysqli_query($res, "insert into app__debuglog VALUE (null,now(), 'msg : $i','callstack','user','debug_speed','vars')");
}
mysqli_commit($res);
$end = microtime(true);
echo "Took " . ($end - $start) . " ms\n";
5次测试的结果:0.48s (+/- 0.04s)
单个聚合插入查询
$start = microtime(true);
$values = "";
for($i = 0; $i < 3000; $i++)
{
$values .= "(null,now(), 'msg : $i','callstack','user','debug_speed','vars')";
if($i !== 2999)
$values .= ",";
}
mysqli_query($res, "insert into app__debuglog VALUES $values");
$end = microtime(true);
echo "Took " . ($end - $start) . " ms\n";
5次测试的结果:0.085s (+/- 0.05s)
因此,对于3000行插入,看起来像:
在单个写事务中使用多个查询比在每个插入中使用多个事务进行多个查询快22倍。 使用单个聚合插入语句仍然比使用单个写事务的多个查询快6倍
在同一时间通过导线发送尽可能多的插入。实际的插入速度应该是相同的,但是您将从网络开销的减少中看到性能的提高。