使用事务提升sqlite insert的性能

最新推荐文章于 2025-08-06 12:42:31 发布

原创最新推荐文章于 2025-08-06 12:42:31 发布 · 3k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#ios #sqlite #事务 #insert performance #插入慢

ios 专栏收录该内容

182 篇文章

订阅专栏

本文探讨了SQLite数据库在插入大量数据时的性能瓶颈，并分享了一个实际案例，通过合理利用事务处理，将插入速度从每秒几条提升到数千条，同时介绍了如何处理主键冲突的情况。

昨天发现sqlite插入性能很低，搜索了一下发现，其实sqlite的插入可以做到每秒50000条，但是处理事务的速度慢：

(19) INSERT is really slow - I can only do few dozen INSERTs per second

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.
Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

sqlite FAQ#19

我原本的代码没有使用事务，所以每条insert语句都默认为一个事务。解决的办法是加上事务，执行SQL的时间就从10秒缩短到了0.07秒

发现了这个以后，我就尝试把可能的地方都加上事务，但是原本程序有一处逻辑，是执行一大堆insert，如果主键冲突就自然无视。但是如果把这堆sql变成事务，就会影响正确数据的插入，所以又把insert语句改成insert or ignore：

insert or ignore into test (id, key) values (20001, 'kyfxbl');

然后再放到一个事务里，效率大大提升