SQLite教程（十一）：臨時檔案，sqlite教程

SQLite教程（十一）：臨時檔案，sqlite教程

一、簡介：

    儘管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頁。