SQLite 3下用事务优化select [英] Optimizing select with transaction under SQLite 3

问题描述

我读到将大量 SELECT 包装到 BEGIN TRANSACTION/COMMIT 中是一个有趣的优化.

I read that wrapping a lot of SELECT into BEGIN TRANSACTION/COMMIT was an interesting optimization.

但是如果我之前使用PRAGMA journal_mode = OFF"，这些命令真的有必要吗?(如果我记得的话，这会禁用日志，显然也禁用事务系统.)

But are these commands really necessary if I use "PRAGMA journal_mode = OFF" before? (Which, if I remember, disables the log and obviously the transaction system too.)

推荐答案

使用事务——即使你只是在读取数据.这可能会产生几毫秒."

我不确定 Katashrophos.net 博客从何处获取此信息，但在事务中包装 SELECT 语句并没有任何作用.事务总是并且仅在对数据库进行更改时使用，并且事务不能被禁用.他们是一个要求.许多人不明白的是，除非您手动BEGIN 和COMMIT 一个事务，否则每个语句都会自动放入自己唯一的事务中.请务必阅读关于提高 sqlite 性能的事实问题.博客作者可能一直想说的是，如果你打算做一个INSERT，然后一个SELECT，然后另一个INSERT，然后将这些语句手动包装在单个事务中会提高性能.否则 sqlite 会自动将两个插入语句放在单独的唯一事务中.

I'm not sure where the Katashrophos.net blog is getting this information, but wrapping SELECT statements in transactions does nothing. Transactions are always and only used when making changes to the database, and transactions cannot be disabled. They are a requirement. What many don't understand is that unless you manually BEGIN and COMMIT a transaction, each statement will be automatically put in their own unique transaction. Be sure to read the de facto SO question on improving sqlite performance. What the author of the blog might have been trying to say, is that if you plan to do an INSERT, then a SELECT, then another INSERT, then it would increase performance to manually wrap these statements in a single transaction. Otherwise sqlite will automatically put the two insert statements in separate unique transactions.

根据 SQLite 理解的 SQL" 文档中有关事务的文档:
除非在事务中，否则不能对数据库进行任何更改.任何更改数据库的命令(基本上，除了 SELECT 之外的任何 SQL 命令)将自动启动一个事务，如果一个尚未启动实际上."

According to the "SQL as Understood by SQLite" documentation concerning transactions:
"No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect."

最后，通过 PRAGMA journal_mode = OFF 禁用日志不会禁用事务，只会禁用日志记录.但是禁用日志也是提高性能的好方法.通常在每次交易后，sqlite 会在日志中记录交易.当它不必这样做时，您会获得性能提升.

Lastly, disabling journaling via PRAGMA journal_mode = OFF does not disable transactions, only logging. But disabling the log is a good way to increase performance as well. Normally after each transaction, sqlite will document the transaction in the journal. When it doesn't have to do this, you get a performance boost.

更新:
因此，优雅的骰子"引起了我的注意，我上面引用的 SQLite 文档声明具有误导性.SELECT 语句实际上使用事务系统.这用于获取和释放数据库上的 SHARED 锁.因此，在单个事务中包装多个 SELECT 语句确实更有效.通过这样做，锁只被获取和释放一次，而不是针对每个单独的 SELECT 语句.这最终会稍微提高效率，同时还确保所有 SELECT 语句将访问相同版本的数据库，以防某些其他程序添加/删除某些内容.

UPDATE:
So it has been brought to my attention by "elegant dice" that the SQLite documentation statement I quote above is misleading. SELECT statements do in fact use the transaction system. This is used to acquire and release a SHARED lock on the database. As a result, it is indeed more efficient to wrap multiple SELECT statements in a single transaction. By doing so, the lock is only acquired and released once, rather than for each individual SELECT statement. This ends up being slightly more efficient while also assuring that all SELECT statements will access the same version of the database in case something has been added/deleted by some other program.

这篇关于SQLite 3下用事务优化select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！