SQLite剖析(6)：臨時檔案和記憶體資料庫

1、7種臨時檔案
內容整理自http://sqlite.org/tempfiles.html。
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命令建立的臨時檔案不會比原有資料庫檔案大。
2、SQLITE_TEMP_STORE編譯時間參數和PRAGMA指令
復原日誌、主日誌和SQL語句記錄檔總是會被寫入磁碟，但其它類型的臨時檔案可能存放在記憶體中而不會寫入磁碟（這樣可以減少大量的IO操作），是寫入磁碟還是存放於記憶體中取決於SQLITE_TEMP_STORE編譯時間參數，temp_store pragma運行時指令，以及臨時檔案的大小。
SQLITE_TEMP_STORE編譯時間參數是原始碼中的宏定義（#define），其取值範圍是0到3（預設值為1），如下：
* 等於0時，臨時檔案總是儲存在磁碟上，而不會考慮temp_store pragma指令的設定。
* 等於1時，臨時檔案預設儲存在磁碟上，但是該值可以被temp_store pragma指令覆蓋。
* 等於2時，臨時檔案預設儲存在記憶體中，但是該值可以被temp_store pragma指令覆蓋。
* 等於3時，臨時檔案總是儲存在記憶體中，而不會考慮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頁。這個參數值不能在運行時修改。
4、記憶體資料庫
內容整理自http://sqlite.org/inmemorydb.html。
在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);
或者 TTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1;
以這種方式命名的記憶體資料庫，只會與名字精確相同的另一個串連共用它的緩衝。
5、空檔案名稱對應的臨時資料庫
在調用sqlite3_open()函數或執行ATTACH命令時，如果資料庫檔案參數傳的是Null 字元串，那麼一個新的臨時檔案將被建立以作為臨時資料庫的隱藏檔，如：
rc = sqlite3_open("", &db);
或 ATTACH DATABASE '' AS aux2;
每次都會建立不同的臨時檔案，和記憶體資料庫非常相似，兩個串連建立的臨時資料庫也是各自獨立的，在串連關閉後臨時資料庫將自動消失，其隱藏檔也將被自動刪除。
儘管磁碟檔案被建立用於儲存臨時資料庫中的資料資訊，但是實際上臨時資料庫也會和記憶體資料庫一樣通常駐留在記憶體中，唯一不同的是，當臨時資料庫中資料量過大時，SQLite為了保證有更多的記憶體可用於其它操作，因此會將臨時資料庫中的部分資料寫到磁碟檔案中，而記憶體資料庫則始終會將資料存放在記憶體中。