性能多次插入或多次插入 [英] Performance Multiple inserts or multiple values single insert

问题描述

明智的选择(时间和服务器负载)是最好具有多个插入或具有多个值的单个插入.

Performance wise (times and server load) is it better to have multiple inserts or single insert with multiple values.

我在这里发现stackoverflow上，每个插入项最多可以有1000个值集.

I've found here on stackoverflow we can have up to 1000 value sets per insert.

我说的是两种情况……

• 大约要插入1000-3000个值
• 有时我会在mySQL DB中插入20000-30000个值集.

推荐答案

是的，通常，批量插入一次比单个插入要快，因为它避免了每个插入语句都会发生的中间通信.

Yes, generally bulk insertion is faster than single insert at a time as it avoids intermediate communication which was occurring for every insert statement.

但是有时它会导致问题，因为插入/更新语句在表/行上获得排他锁，这意味着当时其他进程/连接都不能使用表.

But sometimes it leads to problems as insert/update statements acquire exclusive locks on tables/rows which means no other process/connection can use table at that time.

如果您有多个同时使用DB的进程，其中某些正在从表中读取而有些正在写入中，则在批量插入时整个操作将被锁定/停止

If you have multiple processes using DB at the same some of which are reading from table and some are writing then whole operations will be locked/stopped at the time of your bulk insertion

因此，大容量插入将表锁定的时间比单次插入的时间长，如果锁定时间更长且数据库未针对此时间进行调优，则可能导致其他进程出现问题.

so bulk insertion will lock the table for more time than single insert which can cause issues for other processes if lock time is more and DB is not tuned for it.

如果您只是不做其他操作就插入数据库，那么可以从文件中进行批量插入(这要快得多)，并且如果有其他进程可以考虑锁定，请调整批量插入频率.

If you are just inserting into the DB with no other operation, then go for bulk insert from a file (which is much faster) and if you have other processes in place adjust bulk insert frequency considering locking.

可能重复的是更新语句批量更新的速度比db2中有一次更新?

Possible duplicate is of update statements Is bulk update faster than single update in db2?

这篇关于性能多次插入或多次插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！