SQLite3 - 数据库临时文件

SQLite Temporary Disk Files
***************************
SQLite在处理数据库的时候会用到很多临时文件。本文介绍SQLite用到的各种临时文件，用法，何时创建和删除，各个临时文件的重要性以及如何避免某些过多的创建临时文件

7种临时文件
*****************************
   1. Rollback journals
   2. Master journals
   3. Statement journals
   4. TEMP databases
   5. Materializations of views and subqueries
   6. Transient indices
   7. Transient databases used by VACUUM

1. Rollback journals 
******************************
此临时文件用于实现atomic commit和rollback功能。 Rollback journals文件与数据库文件处于同一目录, 同一文件名+"-journal". 在事务开始时创建，事务结束时删除。

(1)atomic commit的实现：
如果crash or power loss在事务中发生，rollback journal文件就会被写到磁盘。当下一次打开这个数据库文件时，SQLite会提示有被抛弃的rollback journal文件（称为hot journal），此数据库文件处于不一致状态，可以用此rollback journals文件将数据库恢复到中断的事务之前的状态。

(2) exclusive locking mode
PRAGMA locking_mode=EXCLUSIVE;
SQLite在第一个事务开始时创建一个rollback journals文件， 但是事务结束时不删除文件。 rollback journals文件会被截断或者header被置0。 知道退出exclusive access mode才删除rollback journals文件。

(3)journal_mode pragma
控制rollback journals文件的创建和删除行为
DELETE： 默认值，在事务结束时删除
PERSIST：不删除，但是header被置0。 这样减少了删文件带来的系统开销。与EXCLUSIVE mode的行为相同
OFF：不创建rollback journal文件， turn off atomic commit and rollback capabilities.



2. Master Journal Files
*************************************************************
当一个事务操作多个数据库(ATTACH DATABASE)时，Master Journal文件是atomic commit的一部分。
master journal file存放在main database同一个目录下。 multi-database transaction提交时，master journal文件被删除。


3. Statement Journal Files
*****************************************************
A statement journal file is used to rollback partial results of a single statement within a larger transaction. For example, suppose an UPDATE statement will attempt to modify 100 rows in the database. But after modifying the first 50 rows, the UPDATE hits a constraint violation which should block the entire statement. The statement journal is used to undo the first 50 row changes so that the database is restored to the state it was in at the start of the statement.

A statement journal is only created for an UPDATE or INSERT statement that might change multiple rows of a database and which might hit a constraint or a RAISE exception within a trigger and thus need to undo partial results. If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if there are no other active statements on the same database connection then no statement journal is created since the ordinary rollback journal can be used instead. The statement journal is also omitted if an alternative conflict resolution algorithm is used. For example:

    UPDATE OR FAIL ...
    UPDATE OR IGNORE ...
    UPDATE OR REPLACE ...
    INSERT OR FAIL ...
    INSERT OR IGNORE ...
    INSERT OR REPLACE ...
    REPLACE INTO ....

The statement journal is given a randomized name, not necessarily in the same directory as the main database, and is automatically deleted at the conclusion of the transaction. The size of the statement journal is proportional to the size of the change implemented by the UPDATE or INSERT statement that caused the statement journal to be created.


4. TEMP Databases
**************************
used if invoke "CREATE TEMP TABLE"



5. Materializations Of Views And Subqueries
*************************************************
store subqueries result



6. Transient Indices
**********************************************
此文件用于实现如下SQL特性：
An ORDER BY or GROUP BY clause
The DISTINCT keyword in an aggregate query
Compound SELECT statements joined by UNION, EXCEPT, or INTERSECT

7. Transient databases used by VACUUM
*****************************************************************
VACUUM命令：重建数据库
重建数据库的理由：
(1) 除非SQLite auto_vacuum=FULL, 否则，如果很多数据被删除，会留下很多空闲space。 vacuum用于重新组织文件中的数据,相当于磁盘整理。
(2) 频繁的inserts, updates, and deletes， 造成很多磁盘碎片

当用vacuum命令整理数据库文件时，会将整个数据库重新整理放到Transient databases文件中，然后又写回main database。vacuum命令结束，此文件就被删除。


临时文件是否写入磁盘
*****************************
Rollback journal, master journal, and statement journal files总是写到磁盘。其他临时文件不写磁盘，只在内存中。
除Rollback journal, master journal, and statement journal之外的临时文件是否写磁盘由SQLITE_TEMP_STORE编译选项, temp_store pragma和临时文件大小决定。

SQLITE_TEMP_STORE 的值可以为0~3， 默认值是1
   0. Temporary files are always stored on disk regardless of the setting of the temp_store pragma.
   1. Temporary files are stored on disk by default but this can be overridden by the temp_store pragma.
   2. Temporary files are stored in memory by default but this can be overridden by the temp_store pragma.
   3. Temporary files are always stored in memory regardless of the setting of the temp_store pragma.

temp_store pragma的值可以为0,1,2, 默认值是0
   0. Use either disk or memory storage for temporary files as determined by the SQLITE_TEMP_STORE.
   1. If the SQLITE_TEMP_STORE compile-time parameter specifies memory storage for temporary files, then override that decision and use disk storage instead. Otherwise follow the recommendation of the SQLITE_TEMP_STORE compile-time parameter.
   2. If the SQLITE_TEMP_STORE compile-time parameter specifies disk storage for temporary files, then override that decision and use memory storage instead. Otherwise follow the recommendation of the SQLITE_TEMP_STORE compile-time parameter.

 

参考:

http://www.sqlite.org/tempfiles.html