SQLite 剖析之临时文件、内存数据库
一、7 种临时文件
SQLite 中,一个数据库由单个磁盘文件构成,简化了 SQLite 的使用,因为移动或备份数据库只要拷贝单个文件即可。这也使得 SQLite 适合用作应用程序文件格式。但是,当在单个文件中存储一个数据库时,SQLite 会在处理数据库的过程中使用许多临时文件。
SQLite 目前使用 7 种不同类型的临时文件:
* 回滚日志(Rollback journals)
* 主日志(Master journals)
* SQL 语句日志(Statement journals)
* 临时数据库(TEMP databases)
* 视图和子查询的持久化(Materializations of views and subqueries)
* 临时索引(Transient indices)
* VACUUM 使用的临时数据库(Transient databases used by VACUUM)
(1)回滚日志
回滚日志是一个临时文件,用来实现原子提交和回滚功能。回滚日志总是位于与数据库文件相同的目录下,文件名为数据库文件名后加 "-journal"。回滚日志通常在一个事务首次开始时创建,在一个事务提交或回滚时删除。如果没有回滚日志,SQLite 将不能回滚一个未完成的事务,并且在事务执行的中间某时刻若发生系统崩溃或断电,数据库也会被损坏。回滚日志通常在事务的起点和终点创建和销毁,但也会有一些例外规则。
如果崩溃或断电发生在事务的中间某时刻,则在硬盘上会留有回滚日志。在下次另外一个应用程序尝试打开数据库文件时,它会通知存在回滚日志 ("热日志"),并使用日志中的信息来把数据库恢复到未完成事务开始之前的状态。这就是 SQLite 实现原子提交的基本原理。
如果应用程序使用指令 "PRAGMA locking_mode=EXCLUSIVE;" 把 SQLite 置于排斥锁模式下,则 SQLite 在带排斥锁模式会话的事务开始时创建一个新的回滚日志,在事务结束不会删除回滚日志。回滚日志可能会被缩小,或者它的头部可能会被清零(取决于你使用的 SQLite 版本),但文件不会被删除,直到排斥访问模式退出时回滚日志才会被删除。
回滚日志的创建和删除也可以用日志模式 PRAGMA 指令来更改。默认的日志模式是 DELETE,即在每个事务结束时删除回滚日志。PERSIST 日志模式则放弃删除日志文件,而是把日志文件的头部清零,以防止其他进程回滚日志,因此这与删除日志文件有同样的效果,虽然实际上并没有从磁盘上删除日志文件。也就是说,日志模式 PERSIST 展示的行为与 EXCLUSIVE 锁模式相同。OFF 日志模式让 SQLite 放弃在开始时创建回滚日志,它会禁用 SQLite 的原子提交和回滚功能,让 ROLLBACK 命令不可用。如果使用 OFF 日志模式的事务在中间某时刻发生崩溃或断电,则数据库文件不能恢复,可能会被损坏。
(2)主日志文件
主日志文件用于多数据库操作的原子提交过程中,即一个事务修改多个数据库,这些数据库通过 ATTACH 命令被关联在一个数据库连接上。主日志文件总是位于与主数据库文件 (主数据库文件是在调用 sqlite3_open()、sqlite3_open16() 或 sqlite3_open_v2()创建数据库连接时使用的数据库)相同的目录下,后跟一个随机的后缀。主日志文件中包含所有关联的辅助数据库名称。多数据库事务提交时主日志文件就会被删除。
主日志文件只会在这样的情况下创建:一个数据连接与通过 ATTACH 关联的两个或多个数据库进行会话,并且一个事务修改多个数据库文件。如果没有主日志文件,多数据库事务对每个单独数据库的提交是原子性的,但对整个多数据库一起则不是原子性的。也就是说,如果提交在中间某时刻因为崩溃或断电而中断,则可能对一个数据库的更改完成,而对另一个数据库的更改被回滚。主日志文件确保所有数据库的所有更改要么一起回滚,要么一起提交。
(3)SQL 语句日志文件
SQL 语句日志文件用于回滚大型事务中一个单独 SQL 语句的部分结果。例如,假设一条 UPDATE 语句尝试修改数据库中的 100 行,但在修改完 50 行后,因为意外情况而终止。SQL 语句日志用来撤消这 50 行的更改,以便数据库恢复到语句执行前的状态。
SQL 语句日志只会在一条 UPDATE 或 INSERT 语句修改数据库的多行,且意外终止或在触发器中抛出异常因而需要撤消部分结果的情况下创建。如果 UPDATE 或 INSERT 没有包含在 BEGIN...COMMIT 中,且在同一数据库连接上没有其他活动的 SQL 语句,则无需创建语句日志,因为可以使用原来的回滚日志。如果使用了可靠的冲突解决算法,则语句日志也会被忽略,例如:
- UPDATE OR FAIL ...UPDATE OR IGNORE ...UPDATE OR REPLACE ...INSERT OR FAIL ...INSERT OR IGNORE ...INSERT OR REPLACE ...REPLACE INTO ....
SQL 语句日志文件使用随机的文件名,不一定要在与主数据库相同的目录下,在事务结束时自动删除。SQL 语句日志的空间大小只是 UPDATE 或 INSERT 语句完成的更改部分的比例大小。
(4)临时数据库
使用 "CREATE TEMP TABLE" 命令创建的表格只在执行这条命令的数据库连接上可见。这些 TEMP 表格,以及任何关联的索引、触发器和视图,一起存放在一个单独的临时数据库文件中,这个临时数据库在首次遇到 "CREATE TEMP TABLE" 命令时创建。这个单独的临时数据库文件也有一个关联的回滚日志。用来存储 TEMP 表格的临时数据库会在使用 sqlite3_close() 关闭数据库连接时自动删除。
临时数据数据库文件与通过 ATTACH 命令添加的辅助数据库文件非常类似,不过带有一些特殊属性。临时数据库文件总是在数据库连接关闭时自动删除。临时数据库总是使用 synchronous=OFF 和 journal_mode=PERSIST 这两条 PRAGMA 指令设置。并且,临时数据库不能使用 DETACH,别的进程也不能通过 ATTACH 关联临时数据库。临时数据库文件和它的回滚日志只有在应用程序使用 "CREATE TEMP TABLE" 命令时才会被创建。
(5)视图和子查询的持久化
包含子查询的查询命令必须在某个时刻单独执行子查询并把结果存储在一个临时表格中,然后使用临时表格中的内容来执行外部查询。我们称之为 "持久化" 子查询。SQLite 的查询优化器会尝试避免持久化,但有时候这是不可避免的。持久化过程创建的每个临时表格存储在它们自己单独的临时文件中,在查询结束时自动删除。这些临时表格的大小取决于子查询实体的数据数量。
位于 IN 操作符右边的子查询通常必须被持久化,例如:
- SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
在上面的查询命令中,子查询 "SELECT b FROM ex2" 的执行结果被存储在一个临时表格中(实际为一个临时索引),它通过二进制搜索的方式来确定是否存在一个值 ex2.b。一旦这个临时表格被创建,就运行外部查询,对每个预期的结果行检查 ex1.a 是否包含在临时表中,如果为 true,则输出这个结果行。
为了避免创建临时表格,查询可以重写为以下形式:
- SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);
如果在列 ex2.b 上有索引,则 3.5.4 及以后版本的 SQLite 会自动做这样的重写。
如果 IN 操作符的右边部分是值列表,像下面这样:
- SELECT * FROM ex1 WHERE a IN (1,2,3);
位于 IN 右边的值列表被认为是一个子查询,必须要持久化,也就是说此查询行为相当于下面这样:
- SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3);
当 IN 右边是一个值列表时,会用一个临时索引来持有这些值。
当子查询出现在 SELECT 命令的 FROM 子句中时也会进行持久化,例如:
- SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
根据查询,SQLite 可能需要持久化 "(SELECT b FROM ex2)" 子查询到一个临时表格中,然后在 ex1 和临时表格之间执行连接。查询优化器会尝试 "扁平化(flattening)" 这个查询来避免子查询的持久化。在这个例子中,查询可以被扁平化,SQLite 将自动把这个查询转换成:
- SELECT ex1. * ,
- ex2.b FROM ex1 JOIN ex2 ON ex2.b = ex1.a;
更复杂的查询可能会,也可能不会进行扁平化处理以避免临时表格。是否扁平化处理取决于子查询或外部查询是否包含聚合函数、ORDER BY 子句、GROUP BY 子句或 LIMIT 子句等等。
(6)临时索引
SQLite 使用临时索引来实现很多 SQL 语言特性,包括:
* ORDER BY 或 GROUP BY 子句
* 聚合查询中的 DISTINCT 关键字
* 复合式 SELECT 语句,即有 UNION, EXCEPT 或 INTERSECT 等连接子句
每个临时索引存放在它自己的临时文件中,在 SQL 语句执行结束时被自动删除。
SQLite 会尝试使用已存在的索引来实现 ORDER BY 子句。如果在指定的字段上已存在索引,SQLite 将遍历该索引(而不是创建临时索引)来提取需要的信息,并且以指定的排序输出结果行。如果 SQLite 没有找到合适的索引,则执行查询并把每行存储在一个临时索引中,索引的关键字为 ORDER BY 指定的字段。然后 SQLite 返回并从头到尾遍历临时索引,以指定的排序输出每行。
对于 GROUP BY 子句,SQLite 根据指定字段对输出行进行排序。每个输出行与先前行进行比较,看它是否属于新的组。GROUP BY 字段的排序与 ORDER BY 字段的排序是相同的。如果有存在的索引就使用它,如果没有已存在的索引,则创建临时索引。
聚合查询上的 DISTINCT 关键字会在一个临时文件中创建临时索引,并把每行结果存储到索引中。对新的结果行,如果在临时索引中已存在,则忽略它。
复合查询的 UNION 运算符会在一个临时文件创建临时索引,并把左边和右边子查询结果存储到索引中,忽略重复的行。当两个子查询执行完后,从头到尾遍历临时索引来产生最后的输出。
复合查询的 EXCEPT 运算符会在一个临时文件创建临时索引,并把左边子查询结果存储到临时索引中,然后从索引中移除右边子查询的结果,最后从头到尾遍历临时索引以得到最后的输出。
复合查询的 EXCEPT 运算符会创建两个独立的临时索引,它们位于两个独立的临时文件中。左边和右边子查询被执行并存放到各自的临时索引中。然后一起遍历两个索引,输出同时存在于两个索引中的结果。
注意复合查询的 UNION ALL 运算符自己并不使用临时索引,当然 UNION ALL 左边和右边的子查询可能会单独使用临时索引,这取决于它们是怎么复合的。
(7)VACUUM 命令使用的临时数据库
VACUUM 命令会先创建一个临时文件,然后重建整个数据库并写入到该临时文件中。之后将临时文件中的内容拷贝回原有的数据库文件中,最后删除该临时文件。VACUUM 命令创建的临时文件不会比原有数据库文件大。
二、SQLITE_TEMP_STORE 编译时参数和 PRAGMA 指令
回滚日志、主日志和 SQL 语句日志文件总是会被写入磁盘,但其它类型的临时文件可能存放在内存中而不会写入磁盘(这样可以减少大量的 IO 操作),是写入磁盘还是存放于内存中取决于 SQLITE_TEMP_STORE 编译时参数,temp_store pragma 运行时指令,以及临时文件的大小。对于 SQLite 来说,回滚日志、主数据库日志和 SQL 语句日志文件在需要的时候 SQLite 都会将它们写入磁盘文件,但是对于其它类型的临时文件,SQLite 是可以将它们存放在内存中以取代磁盘文件的,这样在执行的过程中就可以减少大量的 IO 操作了。要完成该优化主要依赖于以下三个因素:
1. 编译时参数 SQLITE_TEMP_STORE
SQLITE_TEMP_STORE 编译时参数是源代码中的宏定义(#define),其取值范围是 0 到 3(缺省值为 1),如下:
* 等于 0 时,临时文件总是存储在磁盘上,而不会考虑 temp_store pragma 指令的设置。
* 等于 1 时,临时文件缺省存储在磁盘上,但是该值可以被 temp_store pragma 指令覆盖。
* 等于 2 时,临时文件缺省存储在内存中,但是该值可以被 temp_store pragma 指令覆盖。
* 等于 3 时,临时文件总是存储在内存中,而不会考虑 temp_store pragma 指令的设置。
2. 运行时指令 temp_store pragma
temp_store pragma 指令的取值范围是 0 到 2(缺省值为 0),在程序运行时该指令可以被动态的设置,如下:
* 等于 0 时,临时文件的存储行为完全由 SQLITE_TEMP_STORE 编译期参数确定。
* 等于 1 时,如果编译期参数 SQLITE_TEMP_STORE 指定使用内存存储临时文件,那么该指令将覆盖这一行为,使用磁盘存储。否则直接使用 SQLITE_TEMP_STORE 的行为。
* 等于 2 时,如果编译期参数 SQLITE_TEMP_STORE 指定使用磁盘存储临时文件,那么该指令将覆盖这一行为,使用内存存储。否则直接使用 SQLITE_TEMP_STORE 的行为。
重申一下,SQLITE_TEMP_STORE 编译时参数 temp_store pragma 指令只影响除回滚日志和主日志之外的其它临时文件的存储策略。换句话说,回滚日志和主数据库日志将总是将数据写入磁盘,而不会关注以上两个参数的值。
3. 临时文件的大小
对于以上两个参数,都有参数值表示缺省情况是存储在内存中的,只有当临时文件的大小超过一定的阈值后才会根据一定的算法,将部分数据写入到磁盘中,以免临时文件占用过多的内存而影响其它程序的执行效率。
三、其他临时文件优化策略
SQLite 对当前读写的数据库页面采用了 Page Cache 的缓冲优化机制,因此即便临时文件被指定存储在磁盘上,也只有当该文件的大小增长到一定的尺寸后(导致页面缓存填满)才有可能被 SQLite 刷新到磁盘文件上,在此之前它们仍将驻留在内存中。这就意味着对于大多数场景,如果临时表和临时索引的数据量相对较少(页面缓存足够存放它们),那么它们是不会被写到磁盘中的,当然也就不会有磁盘 IO 发生。只有当它们增长到内存不能容纳的时候才会被刷新到磁盘文件中的。
每个临时表格和索引都有自己的页缓存,它们能存放最大多少个数据库页面由 SQLITE_DEFAULT_TEMP_CACHE_SIZE 编译期参数来确定,这个参数指定了临时表和索引在占用多少 Page Cache 时才需要被刷新到磁盘文件,该参数的缺省值为 500 页。这个参数值不能在运行时修改。
四、内存数据库
在 SQLite 中,数据库通常存储在磁盘文件中。然而在有些情况下,我们可以让数据库始终驻留在内存中。最常用的一种方式是在调用 sqlite3_open()、sqlite3_open16() 或 sqlite3_open_v2() 时,数据库文件名参数指定为":memory:",如:
- rc = sqlite3_open(":memory:", &db);
在调用完以上函数后,不会有任何磁盘文件被生成,取而代之的是,一个新的数据库在纯内存中被成功创建了。由于没有持久化,该数据库在当前数据库连接被关闭后就会立刻消失。需要注意的是,每个: memory: 数据库是不同的数据库,也就是说,用文件名 ":memory:" 打开两个数据库连接将创建两个独立的内存数据库。
文件名 ":memory:" 可以用在任何允许使用数据库文件名的地方。例如,它可以用于 ATTACH 命令中,让内存数据库像其他普通数据库一样,附加到当前的连接中,如:
- ATTACH DATABASE ':memory:' AS aux1;
注意在创建内存数据库时,只能用文件名 ":memory:",不能包含其他文本,例如 "./:memory:",这样会创建一个基于磁盘文件的数据库。在使用 URI 格式的文件名时,也可以使用 ":memory:",例如:
- rc = sqlite3_open("file::memory:", &db);
或者
- ATTACH DATABASE 'file::memory:' AS aux1;
如果内存数据库使用 URI 文件名打开,则它可以使用共享缓存。如果通过未修饰的 ":memory" 名来指定内存数据库,则这个数据库总是有一个私有的对其他连接不可见的缓存。如果使用 URI 文件名,则同样的内存数据库可以被两个或多个数据库连接打开,例如:
- rc = sqlite3_open("file::memory:?cache=shared", &db);
或者
- ATTACH DATABASE 'file::memory:?cache=shared' AS aux1;
这使得多个数据库连接可以共享同一个内存数据库。当然,共享一个内存数据库的这些连接需要在同一个进程中。当最后一个数据库连接关闭时,内存数据库自动被删除。
如果需要在一个进程中使用多个不同的但可共享的内存数据库,可以在 URI 文件名中附加 mode=memory 查询参数来创建一个命名的内存数据库:
- rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);
或者
- ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1;
以这种方式命名的内存数据库,只会与名字精确相同的另一个连接共享它的缓存。
五、(空文件名对应的) 临时数据库
在调用 sqlite3_open() 函数或执行 ATTACH 命令时,如果数据库文件参数传的是空字符串,那么一个新的临时文件将被创建以作为临时数据库的存储文件,如:
- rc = sqlite3_open("", &db);
或者
- ATTACH DATABASE '' AS aux2;
每次都会创建不同的临时文件,和内存数据库非常相似,两个连接创建的临时数据库也是各自独立的,在连接关闭后临时数据库将自动消失,其存储文件也将被自动删除。
尽管磁盘文件被创建用于存储临时数据库中的数据信息,但是实际上临时数据库也会和内存数据库一样,通常驻留在内存中,唯一不同的是,当临时数据库中数据量过大时,SQLite 为了保证有更多的内存可用于其它操作,因此会将临时数据库中的部分数据写到磁盘文件中,而内存数据库则始终会将数据存放在内存中
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: http://www.92to.com/bangong/2017/03-11/18486433.html