一、簡介：

    盡管SQLite的數據庫是由單一文件構成，然而事實上在SQLite運行時卻存在著一些隱含的臨時文件，這些臨時文件是出于不同的目的而存在的，對于開發者而言，它們是透明的，因此在開發的過程中我們并不需要關注它們的存在。盡管如此，如果能對這些臨時文件的產生機制和應用場景有著很好的理解，那么對我們今后應用程序的優化和維護都是極有幫助的。在SQLite中主要產生以下七種臨時文件，如：

    1). 回滾日志。
    2). 主數據庫日志。
    3). SQL語句日志。
    4). 臨時數據庫文件。
    5). 視圖和子查詢的臨時持久化文件。
    6). 臨時索引文件。
    7). VACUUM命令使用的臨時數據庫文件。
   
二、具體說明：

    1. 回滾日志：

    SQLite為了保證事物的原子性提交和回滾，在事物開始時創建了該臨時文件。此文件始終位于和數據庫文件相同的目錄下，其文件名格式為: 數據庫文件名 + "-journal"。換句話說，如果沒有該臨時文件的存在，當程序運行的系統出現任何故障時，SQLite將無法保證事物的完整性，以及數據狀態的一致性。該文件在事物提交或回滾后將被立刻刪除。

    在事物運行期間，如果當前主機因電源故障而宕機，而此時由于回滾日志文件已經保存在磁盤上，那么當下一次程序啟動時，SQLite在打開數據庫文件的過程中將會發現該臨時文件的存在，我們稱這種日志文件為"Hot Journal"。SQLite會在成功打開數據庫之前先基于該文件完成數據庫的恢復工作，以保證數據庫的數據回復到上一個事物開始之前的狀態。

    在SQLite中我們可以通過修改journal_mode pragma，而使SQLite對維護該文件采用不同的策略。缺省情況下該值為DELETE，即在事物結束后刪除日志文件。而PERSIST選項值將不會刪除日志文件，而是將回滾日志文件的頭部清零，從而避免了文件刪除所帶的磁盤開銷。再有就是OFF選項值，該值將指示SQLite在開始事物時不產生回滾日志文件，這樣一旦出現系統故障，SQLite也無法再保障數據庫數據的一致性。

    2. 主數據庫日志：

    在SQLite中，如果事物的操作作用于多個數據庫，即通過ATTACH命令附加到當前連接中的數據庫，那么SQLite將生成主數據庫日志文件以保證事物產生的改變在多個數據庫之間保持原子性。和回滾日志文件一樣，主數據庫日志文件也位于當前連接中主數據庫文件所處的目錄內，其文件名格式為：主數據庫文件名 + 隨機的后綴。在該文件中，將包含所有當前事物將會改變的Attached數據庫的名字。在事物被提交之后，此文件亦被SQLite隨之刪除。

    主數據庫日志文件只有在某一事物同時操作多個數據庫時(主數據庫和Attached數據庫)才有可能被創建。通過該文件，SQLite可以實現跨多個數據庫的事物原子性，否則，只能簡單的保證每個單一的數據庫內的狀態一致性。換句話說，如果該事物在執行的過程中出現系統崩潰或主機宕機的現象，在進行數據恢復時，若沒有該文件的存在，將會導致部分SQLite數據庫處于提交狀態，而另外一部分則處于回滾狀態，因此該事物的一致性將被打破。

    3. SQL語句日志：

    在一個較大的事物中，SQLite為了保證部分數據在出現錯誤時可以被正常回滾，所以在事物開始時創建了SQL語句日志文件。比如，update語句修改了前50條數據，然而在修改第51條數據時發現該操作將會破壞某字段的唯一性約束，最終SQLite將不得不通過該日志文件回滾已經修改的前50條數據。

    SQL語句日志文件只有在INSERT或UPDATE語句修改多行記錄時才有可能被創建，與此同時，這些操作還極有可能會打破某些約束并引發異常。但是如果INSERT或UPDATE語句沒有被包含在BEGIN...COMMIT中，同時也沒有任何其它的SQL語句正在當前的連接上運行，在這種情況下，SQLite將不會創建SQL語句日志文件，而是簡單的通過回滾日志來完成部分數據的UNDO操作。

    和上面兩種臨時文件不同的是，SQL語句日志文件并不一定要存儲在和數據庫文件相同的目錄下，其文件名也是隨機生成。該文件所占用的磁盤空間需要視UPDATE或INSERT語句將要修改的記錄數量而定。在事物結束后，該文件將被自動刪除。

    4. 臨時數據庫文件：

    當使用"CREATE TEMP TABLE"語法創建臨時數據表時，該數據表僅在當前連接內可見，在當前連接被關閉后，臨時表也隨之消失。然而在生命期內，臨時表將連同其相關的索引和視圖均會被存儲在一個臨時的數據庫文件之內。該臨時文件是在第一次執行"CREATE TEMP TABLE"時即被創建的，在當前連接被關閉后，該文件亦將被自動刪除。最后需要說明的是，臨時數據庫不能被執行DETACH命令，同時也不能被其它進程執行ATTACH命令。
   
    5. 視圖和子查詢的臨時持久化文件：

    在很多包含子查詢的查詢中，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頁。