SQLite教程（十一）：临时文件(2)-木庄网络博客

当前第2页返回上一页

在很多包含子查询的查询中，SQLite的执行器会将该查询语句拆分为多个独立的SQL语句，同时将子查询的结果持久化到临时文件中，之后在基于该临时文件中的数据与外部查询进行关联，因此我们可以称其为物化子查询。通常而言，SQLite的优化器会尽力避免子查询的物化行为，但是在有些时候该操作是无法避免的。该临时文件所占用的磁盘空间需要依赖子查询检索出的数据数量，在查询结束后，该文件将被自动删除。见如下示例：

代码如下:

SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);

在上面的查询语句中，子查询SELECT b FROM ex2的结果将会被持久化到临时文件中，外部查询在运行时将会为每一条记录去检查该临时文件，以判断当前记录是否出现在临时文件中，如果是则输出当前记录。显而易见的是，以上的行为将会产生大量的IO操作，从而显著的降低了查询的执行效率，为了避免临时文件的生成，我们可以将上面的查询语句改为：

代码如下:

SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);

对于如下查询语句，如果SQLite不做任何智能的rewrite操作，该查询中的子查询也将会被持久化到临时文件中，如：

代码如下:

SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;

在SQLite自动将其修改为下面的写法后，将不会再生成临时文件了，如：

代码如下:

    SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;

    6. 临时索引文件：
    当查询语句包含以下SQL从句时，SQLite为存储中间结果而创建了临时索引文件，如：
    1). ORDER BY或GROUP BY从句。
    2). 聚集查询中的DISTINCT关键字。
    3). 由UNION、EXCEPT和INTERSECT连接的多个SELECT查询语句。
    需要说明的是，如果在指定的字段上已经存在了索引，那么SQLite将不会再创建该临时索引文件，而是通过直接遍历索引来访问数据并提取有用信息。如果没有索引，则需要将排序的结果存储在临时索引文件中以供后用。该临时文件所占用的磁盘空间需要依赖排序数据的数量，在查询结束后，该文件被自动删除。

    7. VACUUM命令使用的临时数据库文件：
    VACUUM命令在工作时将会先创建一个临时文件，然后再将重建的整个数据库写入到该临时文件中。之后再将临时文件中的内容拷贝回原有的数据库文件中，最后删除该临时文件。
    该临时文件所占用的磁盘空间不会超过原有文件的尺寸。

三、相关的编译时参数和指令：

对于SQLite来说，回滚日志、主数据库日志和SQL语句日志文件在需要的时候SQLite都会将它们写入磁盘文件，但是对于其它类型的临时文件，SQLite是可以将它们存放在内存中以取代磁盘文件的，这样在执行的过程中就可以减少大量的IO操作了。要完成该优化主要依赖于以下三个因素：

1. 编译时参数SQLITE_TEMP_STORE：

    该参数是源代码中的宏定义(#define)，其取值范围是0到3(缺省值为1)，见如下说明：
    1). 等于0时，临时文件总是存储在磁盘上，而不会考虑temp_store pragma指令的设置。
    2). 等于1时，临时文件缺省存储在磁盘上，但是该值可以被temp_store pragma指令覆盖。
    3). 等于2时，临时文件缺省存储在内存中，但是该值可以被temp_store pragma指令覆盖。
    4). 等于3时，临时文件总是存储在内存中，而不会考虑temp_store pragma指令的设置。

    2. 运行时指令temp_store pragma：

    该指令的取值范围是0到2(缺省值为0)，在程序运行时该指令可以被动态的设置，见如下说明：
    1). 等于0时，临时文件的存储行为完全由SQLITE_TEMP_STORE编译期参数确定。
    2). 等于1时，如果编译期参数SQLITE_TEMP_STORE指定使用内存存储临时文件，那么该指令将覆盖这一行为，使用磁盘存储。
    2). 等于2时，如果编译期参数SQLITE_TEMP_STORE指定使用磁盘存储临时文件，那么该指令将覆盖这一行为，使用内存存储。

    3. 临时文件的大小：

    对于以上两个参数，都有参数值表示缺省情况是存储在内存中的，只有当临时文件的大小超过一定的阈值后才会根据一定的算法，将部分数据写入到磁盘中，以免临时文件占用过多的内存而影响其它程序的执行效率。

    最后在重新赘述一遍，SQLITE_TEMP_STORE编译期参数和temp_store pragma运行时指令只会影响除回滚日志和主数据库日志之外的其它临时文件的存储策略。换句话说，回滚日志和主数据库日志将总是将数据写入磁盘，而不会关注以上两个参数的值。

四、其它优化策略：

在SQLite中由于采用了Page Cache的缓冲优化机制，因此即便临时文件被指定存储在磁盘上，也只有当该文件的大小增长到一定的尺寸后才有可能被SQLite刷新到磁盘文件上，在此之前它们仍将驻留在内存中。这就意味着对于大多数场景，如果临时表和临时索引的数据量相对较少，那么它们是不会被写到磁盘中的，当然也就不会有IO事件发生。只有当它们增长到内存不能容纳的时候才会被刷新到磁盘文件中的。其中SQLITE_DEFAULT_TEMP_CACHE_SIZE编译期参数可以用于指定临时表和索引在占用多少Cache Page时才需要被刷新到磁盘文件，该参数的缺省值为500页。

标签：SQLite

返回前面的内容