【騰訊Bugly乾貨分享】移動客戶端中高效使用SQLite

這篇文章主要從 SQLite 資料庫的使用入手，介紹如何合理、高效、便捷的將這個桌面資料庫和 App 全面結合。避免 App 開發過程中可能遇到的坑，也提供一些在開發過程中通過大量實踐和數據對比後總結出的一些參數設置。整篇文章將以一個個具體的技術點作為講解單元，從 SQLite 資料庫生命周期起始講... ...

本文來自於騰訊bugly開發者社區，非經作者同意，請勿轉載，原文地址：http://dev.qq.com/topic/57b57f2a0703f7d31b9a3932

作者：趙豐

導語

iOS 程式能從網路獲取數據。少量的 KV 類型數據可以直接寫文件保存在 Disk 上，App 內部通過讀寫介面獲取數據。稍微複雜一點的數據類型，也可以將數據格式化成 JSON 或 XML 方便保存，這些通用類型的增刪查改方法也很容易獲取和使用。這些解決方案在數據量在數百這一量級有著不錯的表現，但對於大數據應用的支持則在穩定性、性能、可擴展性方面都有所欠缺。在更大一個量級上，移動客戶端需要用到更專業的桌面資料庫 SQLite。

這篇文章主要從 SQLite 資料庫的使用入手，介紹如何合理、高效、便捷的將這個桌面資料庫和 App 全面結合。避免 App 開發過程中可能遇到的坑，也提供一些在開發過程中通過大量實踐和數據對比後總結出的一些參數設置。整篇文章將以一個個具體的技術點作為講解單元，從 SQLite 資料庫生命周期起始講解到其終結。希望無論是從微觀還是從巨集觀都能給工程師以幫助。

一、SQLite 初始化

在寫提綱的時候發現，原來 SQLite 初始化竟然是技術點一點也不少。

1. 設置合理的 page_size 和 cache_size

網上有很多的文章提到了，在記憶體允許的情況下增加 page_size 和 cache_size 能夠獲得更快的查詢速度。但過大的 page_size 也會造成 B-Tree 查詢退化到二分查找、CPU 占用增加以及 OS 級 cache 命中率的下降的問題。

通過反覆比較測試不同組合的 page_size、cache_size、table_size、存儲的數據類型以及各種可能的增刪查改比例，我們發現後三者都是引起 page_size 和 cache_size 性能波動的因素。也就是說對於不同的資料庫並不存在普遍適用的 page_size 和 cache_size 能一勞永逸的幫我們解決問題。

並且在對比測試中我們發現 page_size 的選取往往會出現一個拐點。拐點以前隨著 page_size 增加各種性能指標都會持續改善。但一旦過了拐點，性能將沒有明顯的改變，各個指標將圍繞拐點時的數據值小範圍波動。

那麼如何選取合適的 page_size 和 cache_size 呢？

上一點我們已經提到了可能影響到 page_size 和 cache_size 最優值選取的三個因素：

table_size
存儲的數據類型
增刪查改比例

我們簡單的分析一下看看為什麼這三個變數會共同作用於 page_size 和 cache_size。

SQLite 資料庫把其所存儲的數據以 page 為最小單位進行存儲。cache_size 的含義為當進行查詢操作時，用多少個 page 來緩存查詢結果，加快後續查詢相同索引時方便從緩存中尋找結果的速度。

瞭解了兩者的含義，我們可以發現。SQLite 存儲等長的 int int64 BOOL 等數據時，page 可以優化對齊地址存儲更多的數據。而在存儲變長的 varchar blob 等數據時，一則 page 因為數據變長的影響無法提前計算存儲地址，二則變長的數據往往會造成 page 空洞，空間利用率也有下降。

下表是設置不同的 page_size 和 cache_size 時，資料庫操作中最耗時的增查改三種操作分別與不同數據類型，表列數不同的表之間共同作用的一組測試數據。

其中各列數據含義如下，時間單位為毫秒

從上表我們看到，放大 page_size 和 cache_size 並不能不斷的獲得性能的提升，在拐點以後提升帶來的優化不明顯甚至是副作用了。這一點甚至體現到了資料庫大小這方面。從 G 列可以看到，page_size 的增加對於資料庫查詢的優化明顯優於插入操作的優化。從05、06行可以發現，增加 cache_size 對於資料庫性能提升並不明顯。從 J 列可以看到，當插入操作的數據量比較小的時候，反而是小的 page_size 和 cache_size 更有優勢。但 App DB 耗時更多的體現在大量數據增刪查改時的性能，所以選取合適的、稍微大點的 page_size 是合理的。

所以通過表格分析以後，我們傾向於選擇 DB 線程總耗時以及線程內部耗時最多的三個方法，作為衡量 page_size 優劣的參考標準。

page_size 有兩種設置方法。一是在創建 DB 的時候進行設置。二是在初始化時設置新的 page_size 後，需要調用 vacuum 對數據表對應的節點重新計算分配大小。這裡可參考 pragma_page_size 官方文檔

https://www.sqlite.org/pragma.html#pragma_page_size

2. 通過 timer 控制資料庫事務定時提交

Transaction 是任何一個資料庫中最核心的功能，但其對 Server 端和客戶端的意義卻不盡相同。對 Server 而言，一個 Transaction 是主備容災分片的最小單位(當然還有其他意義)。對客戶端而言，一個 Transaction 能夠大大的提升其內部的增刪查改操作的速度。SQLite 官方文檔以及工程實測的數據都顯示，事務的引入能提升性能 兩個數量級 以上。

實現方案其實非常簡單。程式初始化完畢以後，啟動一個事務，並創建一個 repeated 的 Timer

在 Timer 的回調函數 RenewTransaction 中，提交事務，並新啟動一個事務

這樣就能實現自動化的事務管理，將優化的實現黑盒化。邏輯使用方能將更多精力集中在邏輯實現方面，不用關心性能優化、數據丟失方面的問題。

從手動事務管理到自動事務管理會引發一個問題：

當兩份數據必須擁有相同的生命周期，同時寫入 DB、同時從 DB 刪除、同時被修改時，通過時間作為提交事務的唯一標準，就有可能引發兩份數據的操作進入了不同的事務。而第二個事務如果不能正確的提交，就會造成數據丟失或錯誤。

解決這個問題，可以利用 SQLite 的事務嵌套功能，設計一組開啟事務和關閉提交事務的介面，供邏輯使用者按照其需求調用事務的開始、提交和關閉。讓內層事務保證兩(多)份數據的完整性。

3. 緩存被編譯後的 SQL 語句

和其他很多編程語言一樣，資料庫使用的 SQL 語句也需要經過編譯後才能被執行使用。SQL 語句的編譯結果如果能夠被緩存下來，第二次及以後再被使用時就能直接利用緩存結果，大大減少整個操作的執行時間。與此同理的還有 Java 數學庫優化，通過把極其複雜的 Java 數學庫實現翻譯成 byte code，在調用處直接執行機器碼，能大大優化 Java 數學庫的執行速度和 C++ 持平甚至優於其。而對 SQLite 而言，一次 compile 的時間根據語句複雜程度從幾毫秒到十幾毫秒不等，對於批量操作性能優化是極其明顯的。

其實在上面的第2點中，已經是用一個專門的類將編譯結果保存下來。每次根據文件名稱和行號為索引，獲得對應位置的 SQL 語句編譯結果。為了便於大家理解，我在註釋中也將 SQLIite 內部最底層的方法寫出來供大家參考和對比性能數據。

4. 資料庫完整性校驗

移動客戶端中的資料庫運行環境要遠複雜於桌面平臺和伺服器。掉電、後臺被掛起、進程被 kill、磁碟空間不足等原因都有可能造成資料庫的損壞。SQLite 提供了檢查資料庫完整性的命令

 PRAGMA integrity_check

該 SQL 語句的執行結果如果不為 OK ，則意味著資料庫損壞。程式可以通過 ROLLBACK 到一個稍老的版本等方法來解決資料庫損壞帶來的不穩定性。

5. 資料庫升級邏輯

代碼管理可以用 git、svn，資料庫如果要做升級邏輯相對來說會複雜很多。好在我們可以利用 SQLite，在內部用一張 meta 表專門用於記錄資料庫的當前版本號、最低相容版本號等信息。用好了這張表，我們就可以對資料庫是否需要升級、升級的路徑進行規範。
我們代入一個簡單銀行客戶的例子來說明如何進行資料庫的升級。

a. V1 版本對資料庫的要求非常簡單，保存客戶的賬號、姓、名、出生日期、年齡、信用這6列。以及對應的增刪查改，對應的SQL語句如下

並且在 meta 表中保存當前資料庫的版本號為1，向前相容的版本為1，代碼如下

b. V2 版本時需要在資料庫中增加客戶在銀行中的存款和欠款兩列。首先我們需要從 meta 表中讀取用戶的資料庫版本號。增加了兩列後創建 table 和增刪查改的 SQL 語句都要做出適當的修改。代碼如下

很顯然 V2 版本的 SQL 語句很多都和 V1 是不相容的。V1 的數據使用 V2 的 SQL 進行操作會引發異常產生。所以在 SQLite 封裝層，我們需要根據當前資料庫版本分別進行處理。V1 版本的資料庫需要通過 ALTER 操作增加兩列後使用。記得升級完畢後要更新資料庫的版本。代碼如下

c. V3 版本發現出生日期與年齡兩個欄位有重覆，冗餘的數據會帶來資料庫體積的增加。希望 V3 資料庫能夠只保留出生日期欄位。我們依然從 meta 讀取資料庫版本號信息。不過這次需要註意的是直到 SQLite 3.9.10 版本並沒有刪掉一列的操作。不過這並不影響新版本創建的 TABLE 會去掉這一列，而老版本的DB也可以和新的 SQL 語句一起配合工作不會引發異常。代碼如下

註意 last_compatible_version 這裡可以填2也可以填3，主要根據業務邏輯合理選擇

d. 除了資料庫結構發生變化時可以用上述的方法升級。當發現老版本的邏輯引發了數據錯誤，也可以用類似的方法重新計算正確結果，刷新資料庫。

二、如何寫出高效的 SQL 語句

這個部分將以 App 開發中經常面對的場景作為樣例進行對比分析。

1. 分類建索引(covering index & explain query)

或許很多開發都知道，當用某列或某些列作為查詢條件時，給這些列增加索引是能大大提升查詢速度的。

但真的如此的簡單嗎？

要回答這個問題，我們需要藉助 SQLite 提供的 explain query 工具。

顧名思義，它是用來向開發人員解釋在資料庫內部一條查詢語句是如何進行的。在 SQLite 資料庫內部，一條查詢語句可能的執行方式是多種多樣的。它有可能會掃描整張數據表，也可能會掃描主鍵子表、索引子表，或者是這些方式的組合。具體的關於 SQLite 查詢的方式可以參看官方文檔 Query Planning

https://www.sqlite.org/queryplanner.html#searching

簡單的說，SQLite 對主鍵會按照平衡多叉樹理論對其建樹，使其搜索速度降低到 Log(N)。

針對某列建立索引，就是將這列以及主鍵所有數據取出。以索引列為主鍵按照升序，原表主鍵為第二列，重新創建一張新的表。需要特別註意的是，針對多列建立索引的內部實現方案是，索引第一列作為主鍵按照升序，第一列排序完畢後索引第二列按照升序，以此類推，最後以原表主鍵作為最後一列。這樣就能保證每一行的數據都不完全相同，這種多列建索引的方式也叫 COVERING INDEX。所以對多列進行索引，只有第一列的搜索速度理論上能到 Log(N)。

更重要的是，SQLite 這種建索引的方式確實可以帶來搜索性能的提升，但對於資料庫初始化的性能有著非常大的負面影響。這裡先點到為止，下文會專門論述如何進行優化。這裡以 SQLite 官方的一個例子來說明，在邏輯上 SQLite 是如何建立索引的。

實際上 SQLite 建立索引的方式並不是下列圖看起來的聚集索引，而是採用了非聚集索引。因為非聚集索引的性能並不比聚集索引低，但空間開銷卻會小很多。SQLite 官方圖片只是示意，請一定註意

一列行號外加三列數據 fruit state price

當我們用 CREATE INDEX Idx1 ON fruitsforsale(fruit) 為 fruit 列創建索引後，SQLite 在內部會創建一張新的索引表，並以 fruit 為主鍵。如上圖所示

而當我們繼續用 CREATE INDEX Idx3 ON FruitsForSale(fruit, state) 創建了 COVERING IDNEX 時，SQLite 在內部並不會為所有列單獨創建索引表。而是以第一列作為主鍵，其他列升序，行號最後來創建一張表。如上圖所示

我們接下來要做的就是利用 explain query 來分析不同的索引方式對於查詢方式的影響，以及性能對比。

不加索引的時候，查詢將會掃描整個數據表

針對 WHERE CLAUSE 中的列加了索引以後的情況。SQLite 在進行搜索的時候會先根據索引表i1找到對應的行，再根據 rowid 去原表中獲取 b 列對應的數據。可能有些工程師已經發現了，這裡可以優化啊，沒必要找到一行數據後還要去原表找一次。剛纔不是說了嘛，對多列建索引的時候，是把這些列的數據都放入一個新的表。那我們試試看。

果然，同樣的搜索語句，不同的建索引的方式，SQLite 的查詢方式也是不同的。這次 SQLite 選擇了索引 i2 而非索引 i1，因為 a、b 列數據都在同一張表中，減少了一次根據行號去原表查詢數據的操作。

看到這裡不知道大家有沒有產生這樣的一個疑問，如果我們用 COVERING INDEX i2 的非第一列去搜索是不是並沒有索引的效果？

WTF，果然，看起來我們為 b 列創建了索引 i2，但用 EXPLAIN QUERY PLAN 一分析發現 SQLite 內部依然是掃描整張數據表。這點也和上面分析的對 COVERING INDEX 建索引表的理論一致，不過情況依然沒這麼簡單，我們看看下麵三個搜索

WTF，搜索的時候用 AND 和 OR 的效果是不一樣的。其實多想想 COVERING INDEX 的實現原理也就想通了。對於沒有建索引的列進行搜索那不就是掃描整張數據表。所以如果 App 對於兩列或以上有搜索需求時，就需要瞭解一個概念 “前導列” 。所謂前導列，就是在創建 COVERING INDEX 語句的第一列或者連續的多列。比如通過：CREATE INDEX covering_idx ON table1(a, b, c)創建索引，那麼 a, ab, abc 都是前導列，而 bc，b，c 這樣的就不是。在 WHERE CLAUSE 中，前導列必須使用等於或者 in 操作，最右邊的列可以使用不等式，這樣索引才可以完全生效。如果確實要用到等於類的操作，需要像上面最後一個例子一樣為右邊的、不等於類操作的列單獨建索引。

很多時候，我們對於搜索結果有排序的要求。如果對於排序列沒有建索引，可以想象 SQLite 內部會對結果進行一次排序。實際上如果對沒有建索引，SQLite 會建一棵臨時 B Tree 來進行排序。

所以我們建索引的時候別忘了對 ORDER BY 的列進行索引

講了這麼多關於 SQLite 建索引，其實也不過官方文檔的萬一。但是瞭解了 SQLite 建索引的理論和實際方案，掌握了通過 EXPLAIN QUERY PLAN 去分析自己的每一條 WHERE CLAUSE和ORDER BY。我們就可以分析出性能到底還有沒有可以優化的空間。儘量減少掃描數據表的次數、儘量掃描索引表而非原始表，做好與資料庫體積的平衡。讓好的索引加快你程式的運行。

2. 先建原始數據表，再創建索引 - insert first then index

是的，當我第一眼看見這個結論時，我甚至覺得這是搞笑的。當我去翻閱 SQLite 官方文檔時，並沒有對此相關的說明文檔。看著 StackOverflow 上面華麗麗的 insert first then index VS insert and index together 的對比數據，當我真的將建索引挪到了數據初始化插入後，奇跡就這樣發生了。XCode Instrument 統計的十萬條數據的插入CPU耗時，降低了20%(StackOverflow 那篇介紹文章做的對比測試下降還要更多達30%)。

究其原因，索引表在 SQLite 內部是以 B-Tree 的形式進行組織的，一個樹節點一般對應一個 page。我們可以看到資料庫要寫入、讀取、查詢索引表其實都需要用到公共的一個操作是搜索找到對應的樹節點。從外存讀取索引表的一個節點到記憶體，再在記憶體判斷這個節點是否有對應的 key(或者判斷節點是否需要合併或分裂)。而統計研究表明，外存中獲取下一個節點的耗時比記憶體中各項操作的耗時多好幾個數量級。也就是說，對索引表的各項操作，增刪查改的耗時取決於外存獲取節點的時間(SQLite 用 B-Tree 而非 STL 中採用的 RB-Tree 或平衡二叉樹，正是為了儘可能降低樹的高度，減少外存讀取次數)。一邊插入原始表的數據，一邊插入索引表數據，有可能造成索引表節點被頻繁換到外存又從外存讀取。而同一時間只進行建索引的操作，OS 緩存節點的量將增加，命中率提高以後速度自然得到了一定的提升。

SQLite 的索引採用了 B-Tree，樹上的一個 Node 一般占用一個 page_size。

B-Tree 的搜索節點複雜度如上。我們可以看到公式中的 m 就是 B-Tree 的階數也就是節點中最大可存放關鍵字數+1。也就是說，m 是和 page_size 成正比和複雜度成反比和樹的高度成反比和讀取外存次數成反比和耗時成反比。所以 page_size 越大確實可以減少 SQLite 含有查詢類的操作。但無限制的增加 page_size 會使得節點內數據過多，節點內數據查詢退化成線性二分查詢，複雜度反而有些許上升。

所以在這裡還是想強調一下，page_size 的選擇沒有普適標準，一定要根據性能工具的實際分析結果來確定

3. SELECT then INSERT VS INSERT OR REPLACE INTO

有過 SQLite 開發經驗的工程師都知道，INSERT 插入數據時如果主鍵已經存在是會引發異常的。而這時往往邏輯會要求用新的數據代替資料庫已存在的老數據。曾經老版本的 SQLite 只能通過先 SELECT 查詢插入數據主鍵對應的行是否存在，不存在才能 INSERT，否則只能調用 UPDATE。而3.x版本起，SQLite 引入了 INSERT OR REPLACE INTO，用一行 SQL 語句就把原來的三行 SQL 封裝替代了。

不過需要註意的是，SQLite 在實現 INSERT OR REPLACE INTO 時，實現的方案也是先查詢主鍵對應行是否存在，如果存在則刪除這一行，最後插入這行的數據。從其實現過程來看，當數據存在時原來只需要刷新這一行，現在則是刪掉老的插入新的，理論速度上會變慢。這種寫法僅僅是對資料庫封裝開發提供了便利，對性能還是有些許影響的。不過對於數據量比較少不足1000行的情況，用這種方法對性能的損耗還是細微的，且這樣寫確實方便了很多。但對於更多的數據，插入的時候還是推薦雖然寫起來很麻煩，但是性能更好的，先 SELECT 再選擇 INSERT OR UPDATE 的方法。

4. Full Text Search(FTS)

INTEGER 類的數據能夠很方便的建索引，但對於 VARCHAR 類的數據，如果不建索引則只能使用 LIKE 去進行字元串匹配。如果 App 對於字元串搜索有要求，那麼基本上 LIKE 是滿足不了要求的。

FTS 是 SQLite 為加快字元串搜索而創建的虛擬表。FTS 不僅能通過分詞大大加快英文類字元串的搜索，對於中文字元串 FTS 配合 ICU 也能對中文等其他語言進行分詞、分字處理，加快這些語言的搜索速度。下麵這個是 SQLite 官方文檔對兩者搜索速度的一個對比。

上面創建 FTS 虛擬表的方式只能對英文搜索起作用，對其他語言的支持是通過 ICU 模塊支持來實現的。所以工程是需要編譯創建 ICU 的靜態庫，編譯 SQLite 時需要指定鏈接ICU庫。

其實無論創建數據表的時候是否創建了行號(rowid)列，SQLite 都會為每個數據表創建行號列。想想上面的 fruitsforsale，當數據表沒有任何列建了索引的時候，行號就是數據表的唯一索引。FTS 表略微不同的是，它的行號叫 docid，並且是可以用 SQL 語句訪問的。我們一般會用字元串在原始表中的行號作為這裡的 docid。

如果你仔細看搜索語句你會發現和官方文檔不太一樣的是，對於 MATCH 的結果我們會再用 LIKE 過濾一次。

在回答這個問題前，我們需要知道 SQLite 預設對英文是按單詞(空格為分隔符)進行分詞，對中文則是按照字進行拆分。當中文是按字進行拆分時，SQLite 會對關鍵字也按字進行拆分後進行搜索。這會帶來一個 bug，當關鍵字是疊詞時，比如“天天”，除了可以把正確的如“天天向上”搜索出來，還能把“今天天氣不錯，挺風和日麗的”給搜索出來。就是因為關鍵詞“天天”也被按字拆分了。如果我們把 SQLite 內英文搜索設置成按字母拆分，一樣會產生相同的問題。所以我們需要把結果再 LIKE 一次，因為在一個小範圍內 LIKE 且不用加%通配符，這裡的速度也是很快的。

如果希望對英文也按字母拆分，使得輸入關鍵字 “cent”，就能匹配上 “Tencent” 也非常簡單。只需要找到，SQLite 實現的 icuOpen 方法。

其實只需要改變讀取 ICU 的方式，就能支持英文按字母拆分了。

4. 不固定個數的元素集合不要分表

在設計資料庫時，我們會把一個對象的屬性分成不同的列按行存儲。如果屬性是個數量不定的數組，切忌不要把這個數組屬性放到一個新表裡面。上面我們提到過數據操作最耗時的其實是訪問外存上面的數據。當數據量很大時，多張表的外存訪問是非常慢的。這裡的做法是講數組數據用 JSON 序列化後，已 VARCHAR 或者 BLOB 的形式存成一列，和其他的數據放在同一個數據表當中。

5. 用 protobuf 作為資料庫的輸入輸出參數

先說結論，這樣做是資料庫 Model 跨 iOS、Android 平臺的解決方案。兩個平臺用同一份 proto 文件分別生成各自的實現文件。需要跨平臺時將數據序列化後，以傳遞記憶體的方式通過 JNI 介面將數據傳遞給對方平臺。對方平臺有相應的方式進行反序列化。JNI 封裝層的工作也大大降低了。這樣做還有個好處是，後臺返回 protobuf 的結果，網路只需要拷貝在記憶體一份數據(實際上如果 UI、DB 是不同的線程，有可能會需要兩份)就能讓資料庫進行使用，減少了不必要的記憶體開銷。

6. 千萬不要編譯使用 SQLite 多線程實現

標題已經勝過千言萬語了。多線程版的 SQLite 可是對每行操作加鎖的，性能是比較差的，同樣的操作耗時是單線程版本的2倍。

三、一些可能有用的輔助模塊

1. 利用 Lambda 表達式簡化從 UI 線程非同步調用資料庫介面

好的 App 架構，一定會為資料庫單獨安排一個線程。在多線程環境下，UI 線程發起了資料庫介面請求後，一定要保證介面是非同步返回數據才能保證整個UI操作的流暢性。但是非同步介面開發最大的麻煩在於調用在A處，還要實現一個 B 方法來處理非同步返回的結果。這裡推薦使用 C++11的 lambda 表達式加模板函數 base::Bind 來實現像 JavaScript 語言一樣，能夠將非同步回調方法作為輸入參數傳遞給執行方，待執行完成操作後進行非同步回調。用非同步化介面編程，大大降低開發難度和實現量，並帶來了流暢的界面體驗。
C++要實現將回調函數作為輸入參數傳遞給函數執行者，併在執行者完成預定邏輯獲得返回結果時調用回調函數傳遞迴結果，有兩個難點需要剋服。