移動(dòng)客戶端中高效使用 SQLite

轉(zhuǎn)發(fā)自 2016-08-18趙豐騰訊Bugly

導(dǎo)語

iOS 程序能從網(wǎng)絡(luò)獲取數(shù)據(jù)。少量的 KV 類型數(shù)據(jù)可以直接寫文件保存在 Disk 上，App 內(nèi)部通過讀寫接口獲取數(shù)據(jù)涵妥。稍微復(fù)雜一點(diǎn)的數(shù)據(jù)類型壳鹤，也可以將數(shù)據(jù)格式化成 JSON 或 XML 方便保存董习，這些通用類型的增刪查改方法也很容易獲取和使用哮奇。這些解決方案在數(shù)據(jù)量在數(shù)百這一量級(jí)有著不錯(cuò)的表現(xiàn)，但對(duì)于大數(shù)據(jù)應(yīng)用的支持則在穩(wěn)定性遗增、性能、可擴(kuò)展性方面都有所欠缺款青。在更大一個(gè)量級(jí)上做修，移動(dòng)客戶端需要用到更專業(yè)的桌面數(shù)據(jù)庫(kù) SQLite。

這篇文章主要從 SQLite 數(shù)據(jù)庫(kù)的使用入手，介紹如何合理饰及、高效蔗坯、便捷的將這個(gè)桌面數(shù)據(jù)庫(kù)和 App 全面結(jié)合。避免 App 開發(fā)過程中可能遇到的坑燎含，也提供一些在開發(fā)過程中通過大量實(shí)踐和數(shù)據(jù)對(duì)比后總結(jié)出的一些參數(shù)設(shè)置宾濒。整篇文章將以一個(gè)個(gè)具體的技術(shù)點(diǎn)作為講解單元，從 SQLite 數(shù)據(jù)庫(kù)生命周期起始講解到其終結(jié)屏箍。希望無論是從微觀還是從宏觀都能給工程師以幫助绘梦。

一、SQLite 初始化

在寫提綱的時(shí)候發(fā)現(xiàn)赴魁，原來 SQLite 初始化竟然是技術(shù)點(diǎn)一點(diǎn)也不少卸奉。

1. 設(shè)置合理的page_size和cache_size

PRAGMA schema.page_size = bytes;

PRAGMA schema.cache_size = pages;

網(wǎng)上有很多的文章提到了，在內(nèi)存允許的情況下增加 page_size 和 cache_size 能夠獲得更快的查詢速度颖御。但過大的 page_size 也會(huì)造成 B-Tree 查詢退化到二分查找榄棵、CPU 占用增加以及 OS 級(jí) cache 命中率的下降的問題。

通過反復(fù)比較測(cè)試不同組合的 page_size郎嫁、cache_size秉继、table_size、存儲(chǔ)的數(shù)據(jù)類型以及各種可能的增刪查改比例泽铛，我們發(fā)現(xiàn)后三者都是引起 page_size 和 cache_size 性能波動(dòng)的因素尚辑。也就是說對(duì)于不同的數(shù)據(jù)庫(kù)并不存在普遍適用的 page_size 和 cache_size 能一勞永逸的幫我們解決問題。

并且在對(duì)比測(cè)試中我們發(fā)現(xiàn) page_size 的選取往往會(huì)出現(xiàn)一個(gè)拐點(diǎn)盔腔。拐點(diǎn)以前隨著 page_size 增加各種性能指標(biāo)都會(huì)持續(xù)改善杠茬。但一旦過了拐點(diǎn)，性能將沒有明顯的改變弛随，各個(gè)指標(biāo)將圍繞拐點(diǎn)時(shí)的數(shù)據(jù)值小范圍波動(dòng)瓢喉。

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

上一點(diǎn)我們已經(jīng)提到了可能影響到 page_size 和 cache_size 最優(yōu)值選取的三個(gè)因素：

table_size

存儲(chǔ)的數(shù)據(jù)類型

增刪查改比例

我們簡(jiǎn)單的分析一下看看為什么這三個(gè)變量會(huì)共同作用于 page_size 和 cache_size舀透。

SQLite 數(shù)據(jù)庫(kù)把其所存儲(chǔ)的數(shù)據(jù)以 page 為最小單位進(jìn)行存儲(chǔ)栓票。cache_size 的含義為當(dāng)進(jìn)行查詢操作時(shí)，用多少個(gè) page 來緩存查詢結(jié)果愕够，加快后續(xù)查詢相同索引時(shí)方便從緩存中尋找結(jié)果的速度走贪。

了解了兩者的含義，我們可以發(fā)現(xiàn)惑芭。SQLite 存儲(chǔ)等長(zhǎng)的 int int64 BOOL 等數(shù)據(jù)時(shí)坠狡，page 可以優(yōu)化對(duì)齊地址存儲(chǔ)更多的數(shù)據(jù)。而在存儲(chǔ)變長(zhǎng)的 varchar blob 等數(shù)據(jù)時(shí)遂跟，一則 page 因?yàn)閿?shù)據(jù)變長(zhǎng)的影響無法提前計(jì)算存儲(chǔ)地址逃沿，二則變長(zhǎng)的數(shù)據(jù)往往會(huì)造成 page 空洞婴渡，空間利用率也有下降。

下表是設(shè)置不同的 page_size 和 cache_size 時(shí)凯亮，數(shù)據(jù)庫(kù)操作中最耗時(shí)的增查改三種操作分別與不同數(shù)據(jù)類型边臼，表列數(shù)不同的表之間共同作用的一組測(cè)試數(shù)據(jù)。

其中各列數(shù)據(jù)含義如下触幼，時(shí)間單位為毫秒

從上表我們看到硼瓣，放大 page_size 和 cache_size 并不能不斷的獲得性能的提升，在拐點(diǎn)以后提升帶來的優(yōu)化不明顯甚至是副作用了置谦。這一點(diǎn)甚至體現(xiàn)到了數(shù)據(jù)庫(kù)大小這方面堂鲤。從 G 列可以看到，page_size 的增加對(duì)于數(shù)據(jù)庫(kù)查詢的優(yōu)化明顯優(yōu)于插入操作的優(yōu)化媒峡。從05瘟栖、06行可以發(fā)現(xiàn)，增加 cache_size 對(duì)于數(shù)據(jù)庫(kù)性能提升并不明顯谅阿。從 J 列可以看到半哟，當(dāng)插入操作的數(shù)據(jù)量比較小的時(shí)候，反而是小的 page_size 和 cache_size 更有優(yōu)勢(shì)签餐。但 App DB 耗時(shí)更多的體現(xiàn)在大量數(shù)據(jù)增刪查改時(shí)的性能寓涨，所以選取合適的、稍微大點(diǎn)的 page_size 是合理的氯檐。

所以通過表格分析以后戒良，我們傾向于選擇 DB 線程總耗時(shí)以及線程內(nèi)部耗時(shí)最多的三個(gè)方法，作為衡量 page_size 優(yōu)劣的參考標(biāo)準(zhǔn)冠摄。

page_size 有兩種設(shè)置方法糯崎。一是在創(chuàng)建 DB 的時(shí)候進(jìn)行設(shè)置。二是在初始化時(shí)設(shè)置新的 page_size 后河泳，需要調(diào)用vacuum對(duì)數(shù)據(jù)表對(duì)應(yīng)的節(jié)點(diǎn)重新計(jì)算分配大小沃呢。這里可參考 pragma_page_size 官方文檔

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

2. 通過 timer 控制數(shù)據(jù)庫(kù)事務(wù)定時(shí)提交

Transaction 是任何一個(gè)數(shù)據(jù)庫(kù)中最核心的功能，但其對(duì) Server 端和客戶端的意義卻不盡相同拆挥。對(duì) Server 而言薄霜，一個(gè) Transaction 是主備容災(zāi)分片的最小單位(當(dāng)然還有其他意義)。對(duì)客戶端而言纸兔，一個(gè) Transaction 能夠大大的提升其內(nèi)部的增刪查改操作的速度惰瓜。SQLite 官方文檔以及工程實(shí)測(cè)的數(shù)據(jù)都顯示，事務(wù)的引入能提升性能兩個(gè)數(shù)量級(jí)以上食拜。

實(shí)現(xiàn)方案其實(shí)非常簡(jiǎn)單。程序初始化完畢以后副编，啟動(dòng)一個(gè)事務(wù)负甸，并創(chuàng)建一個(gè) repeated 的 Timer

在 Timer 的回調(diào)函數(shù) RenewTransaction 中，提交事務(wù)，并新啟動(dòng)一個(gè)事務(wù)

這樣就能實(shí)現(xiàn)自動(dòng)化的事務(wù)管理呻待，將優(yōu)化的實(shí)現(xiàn)黑盒化打月。邏輯使用方能將更多精力集中在邏輯實(shí)現(xiàn)方面，不用關(guān)心性能優(yōu)化蚕捉、數(shù)據(jù)丟失方面的問題奏篙。

從手動(dòng)事務(wù)管理到自動(dòng)事務(wù)管理會(huì)引發(fā)一個(gè)問題：

當(dāng)兩份數(shù)據(jù)必須擁有相同的生命周期，同時(shí)寫入 DB迫淹、同時(shí)從 DB 刪除秘通、同時(shí)被修改時(shí)，通過時(shí)間作為提交事務(wù)的唯一標(biāo)準(zhǔn)敛熬，就有可能引發(fā)兩份數(shù)據(jù)的操作進(jìn)入了不同的事務(wù)肺稀。而第二個(gè)事務(wù)如果不能正確的提交，就會(huì)造成數(shù)據(jù)丟失或錯(cuò)誤应民。

解決這個(gè)問題话原，可以利用 SQLite 的事務(wù)嵌套功能，設(shè)計(jì)一組開啟事務(wù)和關(guān)閉提交事務(wù)的接口诲锹，供邏輯使用者按照其需求調(diào)用事務(wù)的開始繁仁、提交和關(guān)閉。讓內(nèi)層事務(wù)保證兩(多)份數(shù)據(jù)的完整性归园。

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

和其他很多編程語言一樣黄虱，數(shù)據(jù)庫(kù)使用的 SQL 語句也需要經(jīng)過編譯后才能被執(zhí)行使用。SQL 語句的編譯結(jié)果如果能夠被緩存下來蔓倍，第二次及以后再被使用時(shí)就能直接利用緩存結(jié)果悬钳，大大減少整個(gè)操作的執(zhí)行時(shí)間。與此同理的還有 Java 數(shù)學(xué)庫(kù)優(yōu)化偶翅，通過把極其復(fù)雜的 Java 數(shù)學(xué)庫(kù)實(shí)現(xiàn)翻譯成 byte code默勾，在調(diào)用處直接執(zhí)行機(jī)器碼，能大大優(yōu)化 Java 數(shù)學(xué)庫(kù)的執(zhí)行速度和 C++ 持平甚至優(yōu)于其聚谁。而對(duì) SQLite 而言母剥，一次 compile 的時(shí)間根據(jù)語句復(fù)雜程度從幾毫秒到十幾毫秒不等，對(duì)于批量操作性能優(yōu)化是極其明顯的形导。

其實(shí)在上面的第2點(diǎn)中环疼，已經(jīng)是用一個(gè)專門的類將編譯結(jié)果保存下來。每次根據(jù)文件名稱和行號(hào)為索引朵耕，獲得對(duì)應(yīng)位置的 SQL 語句編譯結(jié)果炫隶。為了便于大家理解，我在注釋中也將 SQLIite 內(nèi)部最底層的方法寫出來供大家參考和對(duì)比性能數(shù)據(jù)阎曹。

4. 數(shù)據(jù)庫(kù)完整性校驗(yàn)

移動(dòng)客戶端中的數(shù)據(jù)庫(kù)運(yùn)行環(huán)境要遠(yuǎn)復(fù)雜于桌面平臺(tái)和服務(wù)器伪阶。掉電煞檩、后臺(tái)被掛起、進(jìn)程被 kill栅贴、磁盤空間不足等原因都有可能造成數(shù)據(jù)庫(kù)的損壞斟湃。SQLite 提供了檢查數(shù)據(jù)庫(kù)完整性的命令

PRAGMA integrity_check

該 SQL 語句的執(zhí)行結(jié)果如果不為 OK ，則意味著數(shù)據(jù)庫(kù)損壞檐薯。程序可以通過 ROLLBACK 到一個(gè)稍老的版本等方法來解決數(shù)據(jù)庫(kù)損壞帶來的不穩(wěn)定性凝赛。

5. 數(shù)據(jù)庫(kù)升級(jí)邏輯

代碼管理可以用 git、svn坛缕，數(shù)據(jù)庫(kù)如果要做升級(jí)邏輯相對(duì)來說會(huì)復(fù)雜很多墓猎。好在我們可以利用 SQLite，在內(nèi)部用一張 meta 表專門用于記錄數(shù)據(jù)庫(kù)的當(dāng)前版本號(hào)祷膳、最低兼容版本號(hào)等信息陶衅。用好了這張表，我們就可以對(duì)數(shù)據(jù)庫(kù)是否需要升級(jí)直晨、升級(jí)的路徑進(jìn)行規(guī)范搀军。

我們代入一個(gè)簡(jiǎn)單銀行客戶的例子來說明如何進(jìn)行數(shù)據(jù)庫(kù)的升級(jí)。

a.V1 版本對(duì)數(shù)據(jù)庫(kù)的要求非常簡(jiǎn)單勇皇，保存客戶的賬號(hào)罩句、姓、名敛摘、出生日期门烂、年齡、信用這6列兄淫。以及對(duì)應(yīng)的增刪查改屯远，對(duì)應(yīng)的SQL語句如下

并且在 meta 表中保存當(dāng)前數(shù)據(jù)庫(kù)的版本號(hào)為1，向前兼容的版本為1捕虽，代碼如下

b.V2 版本時(shí)需要在數(shù)據(jù)庫(kù)中增加客戶在銀行中的存款和欠款兩列慨丐。首先我們需要從 meta 表中讀取用戶的數(shù)據(jù)庫(kù)版本號(hào)。增加了兩列后創(chuàng)建 table 和增刪查改的 SQL 語句都要做出適當(dāng)?shù)男薷男顾健４a如下

很顯然 V2 版本的 SQL 語句很多都和 V1 是不兼容的房揭。V1 的數(shù)據(jù)使用 V2 的 SQL 進(jìn)行操作會(huì)引發(fā)異常產(chǎn)生。所以在 SQLite 封裝層晌端，我們需要根據(jù)當(dāng)前數(shù)據(jù)庫(kù)版本分別進(jìn)行處理捅暴。V1 版本的數(shù)據(jù)庫(kù)需要通過 ALTER 操作增加兩列后使用。記得升級(jí)完畢后要更新數(shù)據(jù)庫(kù)的版本咧纠。代碼如下

c.V3 版本發(fā)現(xiàn)出生日期與年齡兩個(gè)字段有重復(fù)蓬痒，冗余的數(shù)據(jù)會(huì)帶來數(shù)據(jù)庫(kù)體積的增加。希望 V3 數(shù)據(jù)庫(kù)能夠只保留出生日期字段漆羔。我們依然從 meta 讀取數(shù)據(jù)庫(kù)版本號(hào)信息梧奢。不過這次需要注意的是直到 SQLite 3.9.10 版本并沒有刪掉一列的操作瞪讼。不過這并不影響新版本創(chuàng)建的 TABLE 會(huì)去掉這一列，而老版本的DB也可以和新的 SQL 語句一起配合工作不會(huì)引發(fā)異常粹断。代碼如下

注意 last_compatible_version 這里可以填2也可以填3，主要根據(jù)業(yè)務(wù)邏輯合理選擇

d.除了數(shù)據(jù)庫(kù)結(jié)構(gòu)發(fā)生變化時(shí)可以用上述的方法升級(jí)嫡霞。當(dāng)發(fā)現(xiàn)老版本的邏輯引發(fā)了數(shù)據(jù)錯(cuò)誤瓶埋，也可以用類似的方法重新計(jì)算正確結(jié)果，刷新數(shù)據(jù)庫(kù)诊沪。

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

這個(gè)部分將以 App 開發(fā)中經(jīng)常面對(duì)的場(chǎng)景作為樣例進(jìn)行對(duì)比分析。

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

或許很多開發(fā)都知道端姚，當(dāng)用某列或某些列作為查詢條件時(shí)晕粪，給這些列增加索引是能大大提升查詢速度的。

但真的如此的簡(jiǎn)單嗎渐裸？

要回答這個(gè)問題巫湘，我們需要借助 SQLite 提供的 explain query 工具。

顧名思義昏鹃，它是用來向開發(fā)人員解釋在數(shù)據(jù)庫(kù)內(nèi)部一條查詢語句是如何進(jìn)行的尚氛。在 SQLite 數(shù)據(jù)庫(kù)內(nèi)部，一條查詢語句可能的執(zhí)行方式是多種多樣的洞渤。它有可能會(huì)掃描整張數(shù)據(jù)表阅嘶，也可能會(huì)掃描主鍵子表、索引子表载迄，或者是這些方式的組合讯柔。具體的關(guān)于 SQLite 查詢的方式可以參看官方文檔 Query Planning

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

簡(jiǎn)單的說，SQLite 對(duì)主鍵會(huì)按照平衡多叉樹理論對(duì)其建樹护昧，使其搜索速度降低到 Log(N)魂迄。

針對(duì)某列建立索引，就是將這列以及主鍵所有數(shù)據(jù)取出捏卓。以索引列為主鍵按照升序极祸，原表主鍵為第二列，重新創(chuàng)建一張新的表怠晴。需要特別注意的是遥金，針對(duì)多列建立索引的內(nèi)部實(shí)現(xiàn)方案是，索引第一列作為主鍵按照升序蒜田，第一列排序完畢后索引第二列按照升序稿械，以此類推，最后以原表主鍵作為最后一列冲粤。這樣就能保證每一行的數(shù)據(jù)都不完全相同美莫，這種多列建索引的方式也叫 COVERING INDEX页眯。所以對(duì)多列進(jìn)行索引，只有第一列的搜索速度理論上能到 Log(N)厢呵。

更重要的是窝撵，SQLite 這種建索引的方式確實(shí)可以帶來搜索性能的提升，但對(duì)于數(shù)據(jù)庫(kù)初始化的性能有著非常大的負(fù)面影響襟铭。這里先點(diǎn)到為止碌奉，下文會(huì)專門論述如何進(jìn)行優(yōu)化。這里以 SQLite 官方的一個(gè)例子來說明寒砖，在邏輯上 SQLite 是如何建立索引的赐劣。

實(shí)際上 SQLite 建立索引的方式并不是下列圖看起來的聚集索引，而是采用了非聚集索引哩都。因?yàn)榉蔷奂饕男阅懿⒉槐染奂饕涂妫臻g開銷卻會(huì)小很多。SQLite 官方圖片只是示意漠嵌，請(qǐng)一定注意

一列行號(hào)外加三列數(shù)據(jù) fruit state price

當(dāng)我們用CREATE INDEX Idx1 ON fruitsforsale(fruit)為 fruit 列創(chuàng)建索引后咐汞，SQLite 在內(nèi)部會(huì)創(chuàng)建一張新的索引表，并以 fruit 為主鍵儒鹿。如上圖所示

而當(dāng)我們繼續(xù)用CREATE INDEX Idx3 ON FruitsForSale(fruit, state)創(chuàng)建了 COVERING IDNEX 時(shí)碉考，SQLite 在內(nèi)部并不會(huì)為所有列單獨(dú)創(chuàng)建索引表。而是以第一列作為主鍵挺身，其他列升序侯谁，行號(hào)最后來創(chuàng)建一張表。如上圖所示

我們接下來要做的就是利用 explain query 來分析不同的索引方式對(duì)于查詢方式的影響章钾，以及性能對(duì)比墙贱。

不加索引的時(shí)候，查詢將會(huì)掃描整個(gè)數(shù)據(jù)表

針對(duì) WHERE CLAUSE 中的列加了索引以后的情況贱傀。SQLite 在進(jìn)行搜索的時(shí)候會(huì)先根據(jù)索引表i1找到對(duì)應(yīng)的行惨撇，再根據(jù) rowid 去原表中獲取 b 列對(duì)應(yīng)的數(shù)據(jù)「可能有些工程師已經(jīng)發(fā)現(xiàn)了魁衙，這里可以優(yōu)化啊，沒必要找到一行數(shù)據(jù)后還要去原表找一次株搔。剛才不是說了嘛剖淀，對(duì)多列建索引的時(shí)候，是把這些列的數(shù)據(jù)都放入一個(gè)新的表纤房。那我們?cè)囋嚳础?/p>

果然纵隔，同樣的搜索語句，不同的建索引的方式，SQLite 的查詢方式也是不同的捌刮。這次 SQLite 選擇了索引 i2 而非索引 i1碰煌，因?yàn)?a、b 列數(shù)據(jù)都在同一張表中绅作，減少了一次根據(jù)行號(hào)去原表查詢數(shù)據(jù)的操作芦圾。

看到這里不知道大家有沒有產(chǎn)生這樣的一個(gè)疑問，如果我們用 COVERING INDEX i2 的非第一列去搜索是不是并沒有索引的效果俄认？

WTF堕扶，果然，看起來我們?yōu)?b 列創(chuàng)建了索引 i2梭依，但用 EXPLAIN QUERY PLAN 一分析發(fā)現(xiàn) SQLite 內(nèi)部依然是掃描整張數(shù)據(jù)表。這點(diǎn)也和上面分析的對(duì) COVERING INDEX 建索引表的理論一致典尾，不過情況依然沒這么簡(jiǎn)單役拴，我們看看下面三個(gè)搜索

WTF，搜索的時(shí)候用 AND 和 OR 的效果是不一樣的钾埂。其實(shí)多想想 COVERING INDEX 的實(shí)現(xiàn)原理也就想通了河闰。對(duì)于沒有建索引的列進(jìn)行搜索那不就是掃描整張數(shù)據(jù)表。所以如果 App 對(duì)于兩列或以上有搜索需求時(shí)褥紫，就需要了解一個(gè)概念“前導(dǎo)列”姜性。所謂前導(dǎo)列，就是在創(chuàng)建 COVERING INDEX 語句的第一列或者連續(xù)的多列髓考。比如通過：CREATE INDEX covering_idx ON table1(a, b, c)創(chuàng)建索引部念，那么 a, ab, abc 都是前導(dǎo)列，而 bc氨菇，b儡炼，c 這樣的就不是。在 WHERE CLAUSE 中查蓉，前導(dǎo)列必須使用等于或者 in 操作乌询，最右邊的列可以使用不等式，這樣索引才可以完全生效豌研。如果確實(shí)要用到等于類的操作妹田，需要像上面最后一個(gè)例子一樣為右邊的、不等于類操作的列單獨(dú)建索引鹃共。

很多時(shí)候鬼佣，我們對(duì)于搜索結(jié)果有排序的要求。如果對(duì)于排序列沒有建索引霜浴，可以想象 SQLite 內(nèi)部會(huì)對(duì)結(jié)果進(jìn)行一次排序沮趣。實(shí)際上如果對(duì)沒有建索引，SQLite 會(huì)建一棵臨時(shí) B Tree 來進(jìn)行排序坷随。

所以我們建索引的時(shí)候別忘了對(duì) ORDER BY 的列進(jìn)行索引

講了這么多關(guān)于 SQLite 建索引房铭，其實(shí)也不過官方文檔的萬一驻龟。但是了解了 SQLite 建索引的理論和實(shí)際方案，掌握了通過 EXPLAIN QUERY PLAN 去分析自己的每一條 WHERE CLAUSE和ORDER BY缸匪。我們就可以分析出性能到底還有沒有可以優(yōu)化的空間翁狐。盡量減少掃描數(shù)據(jù)表的次數(shù)、盡量掃描索引表而非原始表凌蔬，做好與數(shù)據(jù)庫(kù)體積的平衡露懒。讓好的索引加快你程序的運(yùn)行。

2. 先建原始數(shù)據(jù)表砂心，再創(chuàng)建索引 - insert first then index

是的懈词，當(dāng)我第一眼看見這個(gè)結(jié)論時(shí)，我甚至覺得這是搞笑的辩诞。當(dāng)我去翻閱 SQLite 官方文檔時(shí)坎弯，并沒有對(duì)此相關(guān)的說明文檔∫朐荩看著 StackOverflow 上面華麗麗的 insert first then index VS insert and index together 的對(duì)比數(shù)據(jù)抠忘，當(dāng)我真的將建索引挪到了數(shù)據(jù)初始化插入后，奇跡就這樣發(fā)生了外永。XCode Instrument 統(tǒng)計(jì)的十萬條數(shù)據(jù)的插入CPU耗時(shí)崎脉，降低了20%(StackOverflow 那篇介紹文章做的對(duì)比測(cè)試下降還要更多達(dá)30%)。

究其原因伯顶，索引表在 SQLite 內(nèi)部是以 B-Tree 的形式進(jìn)行組織的囚灼，一個(gè)樹節(jié)點(diǎn)一般對(duì)應(yīng)一個(gè) page。我們可以看到數(shù)據(jù)庫(kù)要寫入祭衩、讀取啦撮、查詢索引表其實(shí)都需要用到公共的一個(gè)操作是搜索找到對(duì)應(yīng)的樹節(jié)點(diǎn)。從外存讀取索引表的一個(gè)節(jié)點(diǎn)到內(nèi)存汪厨，再在內(nèi)存判斷這個(gè)節(jié)點(diǎn)是否有對(duì)應(yīng)的 key(或者判斷節(jié)點(diǎn)是否需要合并或分裂)赃春。而統(tǒng)計(jì)研究表明，外存中獲取下一個(gè)節(jié)點(diǎn)的耗時(shí)比內(nèi)存中各項(xiàng)操作的耗時(shí)多好幾個(gè)數(shù)量級(jí)劫乱。也就是說织中，對(duì)索引表的各項(xiàng)操作，增刪查改的耗時(shí)取決于外存獲取節(jié)點(diǎn)的時(shí)間(SQLite 用 B-Tree 而非 STL 中采用的 RB-Tree 或平衡二叉樹衷戈，正是為了盡可能降低樹的高度狭吼，減少外存讀取次數(shù))。一邊插入原始表的數(shù)據(jù)殖妇，一邊插入索引表數(shù)據(jù)刁笙，有可能造成索引表節(jié)點(diǎn)被頻繁換到外存又從外存讀取。而同一時(shí)間只進(jìn)行建索引的操作，OS 緩存節(jié)點(diǎn)的量將增加疲吸，命中率提高以后速度自然得到了一定的提升座每。

SQLite 的索引采用了 B-Tree，樹上的一個(gè) Node 一般占用一個(gè) page_size摘悴。

B-Tree 的搜索節(jié)點(diǎn)復(fù)雜度如上峭梳。我們可以看到公式中的 m 就是 B-Tree 的階數(shù)也就是節(jié)點(diǎn)中最大可存放關(guān)鍵字?jǐn)?shù)+1。也就是說蹂喻，m 是和 page_size 成正比和復(fù)雜度成反比和樹的高度成反比和讀取外存次數(shù)成反比和耗時(shí)成反比葱椭。所以 page_size 越大確實(shí)可以減少 SQLite 含有查詢類的操作。但無限制的增加 page_size 會(huì)使得節(jié)點(diǎn)內(nèi)數(shù)據(jù)過多口四，節(jié)點(diǎn)內(nèi)數(shù)據(jù)查詢退化成線性二分查詢孵运，復(fù)雜度反而有些許上升。

所以在這里還是想強(qiáng)調(diào)一下蔓彩，page_size 的選擇沒有普適標(biāo)準(zhǔn)治笨，一定要根據(jù)性能工具的實(shí)際分析結(jié)果來確定

3. SELECT then INSERT VS INSERT OR REPLACE INTO

有過 SQLite 開發(fā)經(jīng)驗(yàn)的工程師都知道，INSERT 插入數(shù)據(jù)時(shí)如果主鍵已經(jīng)存在是會(huì)引發(fā)異常的粪小。而這時(shí)往往邏輯會(huì)要求用新的數(shù)據(jù)代替數(shù)據(jù)庫(kù)已存在的老數(shù)據(jù)。曾經(jīng)老版本的 SQLite 只能通過先 SELECT 查詢插入數(shù)據(jù)主鍵對(duì)應(yīng)的行是否存在抡句，不存在才能 INSERT探膊，否則只能調(diào)用 UPDATE。而3.x版本起待榔，SQLite 引入了 INSERT OR REPLACE INTO逞壁，用一行 SQL 語句就把原來的三行 SQL 封裝替代了。

不過需要注意的是锐锣，SQLite 在實(shí)現(xiàn) INSERT OR REPLACE INTO 時(shí)腌闯，實(shí)現(xiàn)的方案也是先查詢主鍵對(duì)應(yīng)行是否存在，如果存在則刪除這一行雕憔，最后插入這行的數(shù)據(jù)姿骏。從其實(shí)現(xiàn)過程來看，當(dāng)數(shù)據(jù)存在時(shí)原來只需要刷新這一行斤彼，現(xiàn)在則是刪掉老的插入新的分瘦，理論速度上會(huì)變慢。這種寫法僅僅是對(duì)數(shù)據(jù)庫(kù)封裝開發(fā)提供了便利琉苇，對(duì)性能還是有些許影響的嘲玫。不過對(duì)于數(shù)據(jù)量比較少不足1000行的情況，用這種方法對(duì)性能的損耗還是細(xì)微的并扇，且這樣寫確實(shí)方便了很多去团。但對(duì)于更多的數(shù)據(jù)，插入的時(shí)候還是推薦雖然寫起來很麻煩，但是性能更好的土陪，先 SELECT 再選擇 INSERT OR UPDATE 的方法昼汗。

4. Full Text Search(FTS)

INTEGER 類的數(shù)據(jù)能夠很方便的建索引，但對(duì)于 VARCHAR 類的數(shù)據(jù)旺坠，如果不建索引則只能使用 LIKE 去進(jìn)行字符串匹配乔遮。如果 App 對(duì)于字符串搜索有要求，那么基本上 LIKE 是滿足不了要求的取刃。

FTS 是 SQLite 為加快字符串搜索而創(chuàng)建的虛擬表蹋肮。FTS 不僅能通過分詞大大加快英文類字符串的搜索，對(duì)于中文字符串 FTS 配合 ICU 也能對(duì)中文等其他語言進(jìn)行分詞璧疗、分字處理坯辩，加快這些語言的搜索速度。下面這個(gè)是 SQLite 官方文檔對(duì)兩者搜索速度的一個(gè)對(duì)比。

上面創(chuàng)建 FTS 虛擬表的方式只能對(duì)英文搜索起作用，對(duì)其他語言的支持是通過 ICU 模塊支持來實(shí)現(xiàn)的确徙。所以工程是需要編譯創(chuàng)建 ICU 的靜態(tài)庫(kù)诽表，編譯 SQLite 時(shí)需要指定鏈接ICU庫(kù)。

其實(shí)無論創(chuàng)建數(shù)據(jù)表的時(shí)候是否創(chuàng)建了行號(hào)(rowid)列村怪，SQLite 都會(huì)為每個(gè)數(shù)據(jù)表創(chuàng)建行號(hào)列。想想上面的 fruitsforsale，當(dāng)數(shù)據(jù)表沒有任何列建了索引的時(shí)候阿纤，行號(hào)就是數(shù)據(jù)表的唯一索引。FTS 表略微不同的是夷陋，它的行號(hào)叫 docid欠拾，并且是可以用 SQL 語句訪問的。我們一般會(huì)用字符串在原始表中的行號(hào)作為這里的 docid骗绕。

如果你仔細(xì)看搜索語句你會(huì)發(fā)現(xiàn)和官方文檔不太一樣的是藐窄，對(duì)于 MATCH 的結(jié)果我們會(huì)再用 LIKE 過濾一次。

在回答這個(gè)問題前酬土，我們需要知道 SQLite 默認(rèn)對(duì)英文是按單詞(空格為分隔符)進(jìn)行分詞荆忍，對(duì)中文則是按照字進(jìn)行拆分。當(dāng)中文是按字進(jìn)行拆分時(shí)撤缴，SQLite 會(huì)對(duì)關(guān)鍵字也按字進(jìn)行拆分后進(jìn)行搜索东揣。這會(huì)帶來一個(gè) bug，當(dāng)關(guān)鍵字是疊詞時(shí)腹泌，比如“天天”嘶卧，除了可以把正確的如“天天向上”搜索出來，還能把“今天天氣不錯(cuò)凉袱，挺風(fēng)和日麗的”給搜索出來芥吟。就是因?yàn)殛P(guān)鍵詞“天天”也被按字拆分了侦铜。如果我們把 SQLite 內(nèi)英文搜索設(shè)置成按字母拆分，一樣會(huì)產(chǎn)生相同的問題钟鸵。所以我們需要把結(jié)果再 LIKE 一次钉稍，因?yàn)樵谝粋€(gè)小范圍內(nèi) LIKE 且不用加%通配符，這里的速度也是很快的棺耍。

如果希望對(duì)英文也按字母拆分贡未，使得輸入關(guān)鍵字 “cent”，就能匹配上 “Tencent” 也非常簡(jiǎn)單蒙袍。只需要找到俊卤，SQLite 實(shí)現(xiàn)的 icuOpen 方法。

其實(shí)只需要改變讀取 ICU 的方式害幅，就能支持英文按字母拆分了消恍。

4. 不固定個(gè)數(shù)的元素集合不要分表

在設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)，我們會(huì)把一個(gè)對(duì)象的屬性分成不同的列按行存儲(chǔ)以现。如果屬性是個(gè)數(shù)量不定的數(shù)組狠怨，切忌不要把這個(gè)數(shù)組屬性放到一個(gè)新表里面。上面我們提到過數(shù)據(jù)操作最耗時(shí)的其實(shí)是訪問外存上面的數(shù)據(jù)邑遏。當(dāng)數(shù)據(jù)量很大時(shí)佣赖，多張表的外存訪問是非常慢的。這里的做法是講數(shù)組數(shù)據(jù)用 JSON 序列化后记盒，已 VARCHAR 或者 BLOB 的形式存成一列憎蛤，和其他的數(shù)據(jù)放在同一個(gè)數(shù)據(jù)表當(dāng)中。

5. 用 protobuf 作為數(shù)據(jù)庫(kù)的輸入輸出參數(shù)

先說結(jié)論孽鸡，這樣做是數(shù)據(jù)庫(kù) Model 跨 iOS蹂午、Android 平臺(tái)的解決方案栏豺。兩個(gè)平臺(tái)用同一份 proto 文件分別生成各自的實(shí)現(xiàn)文件彬碱。需要跨平臺(tái)時(shí)將數(shù)據(jù)序列化后，以傳遞內(nèi)存的方式通過 JNI 接口將數(shù)據(jù)傳遞給對(duì)方平臺(tái)奥洼。對(duì)方平臺(tái)有相應(yīng)的方式進(jìn)行反序列化巷疼。JNI 封裝層的工作也大大降低了。這樣做還有個(gè)好處是灵奖，后臺(tái)返回 protobuf 的結(jié)果嚼沿，網(wǎng)絡(luò)只需要拷貝在內(nèi)存一份數(shù)據(jù)(實(shí)際上如果 UI、DB 是不同的線程瓷患，有可能會(huì)需要兩份)就能讓數(shù)據(jù)庫(kù)進(jìn)行使用骡尽，減少了不必要的內(nèi)存開銷。

6. 千萬不要編譯使用 SQLite 多線程實(shí)現(xiàn)

標(biāo)題已經(jīng)勝過千言萬語了擅编。多線程版的 SQLite 可是對(duì)每行操作加鎖的攀细，性能是比較差的箫踩，同樣的操作耗時(shí)是單線程版本的2倍。

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

1. 利用 Lambda 表達(dá)式簡(jiǎn)化從UI線程異步調(diào)用數(shù)據(jù)庫(kù)接口

好的 App 架構(gòu)境钟，一定會(huì)為數(shù)據(jù)庫(kù)單獨(dú)安排一個(gè)線程。在多線程環(huán)境下俭识，UI 線程發(fā)起了數(shù)據(jù)庫(kù)接口請(qǐng)求后慨削，一定要保證接口是異步返回?cái)?shù)據(jù)才能保證整個(gè) UI 操作的流暢性。但是異步接口開發(fā)最大的麻煩在于調(diào)用在 A 處套媚，還要實(shí)現(xiàn)一個(gè) B 方法來處理異步返回的結(jié)果缚态。這里推薦使用 C++ 11的 lambda 表達(dá)式加模板函數(shù) base::Bind 來實(shí)現(xiàn)像 JavaScript 語言一樣，能夠?qū)惒交卣{(diào)方法作為輸入?yún)?shù)傳遞給執(zhí)行方凑阶，待執(zhí)行完成操作后進(jìn)行異步回調(diào)猿规。用異步化接口編程，大大降低開發(fā)難度和實(shí)現(xiàn)量宙橱，并帶來了流暢的界面體驗(yàn)姨俩。

C++ 要實(shí)現(xiàn)將回調(diào)函數(shù)作為輸入?yún)?shù)傳遞給函數(shù)執(zhí)行者，并在執(zhí)行者完成預(yù)定邏輯獲得返回結(jié)果時(shí)調(diào)用回調(diào)函數(shù)傳遞回結(jié)果师郑，有兩個(gè)難點(diǎn)需要克服环葵。

如何將函數(shù)變成一個(gè)局部變量(C++11 lambda 表達(dá)式)

如何將一個(gè)函數(shù)匿名化(C++11 auto decltype 聯(lián)合推導(dǎo) lambda 表達(dá)式的類型)

2. 加密數(shù)據(jù)庫(kù)

有些時(shí)候，出于某種考慮宝冕，我們需要加密數(shù)據(jù)庫(kù)张遭。SQLite 數(shù)據(jù)庫(kù)加密對(duì)性能的損耗按照官方文檔的評(píng)測(cè)大約在3%的 CPU 時(shí)間。實(shí)現(xiàn)加密一種方案是購(gòu)買 SQLite 的加密版本地梨，大約是3000刀菊卷。還有一種就是自己實(shí)現(xiàn)數(shù)據(jù)庫(kù)的加密模塊。網(wǎng)上有很多介紹如何實(shí)現(xiàn) SQLite 免費(fèi)版中空實(shí)現(xiàn)的加密方法宝剖。