導(dǎo)語(yǔ)

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

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

一、SQLite 初始化

在寫(xiě)提綱的時(shí)候發(fā)現(xiàn),原來(lái) 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 能夠獲得更快的查詢速度.但過(guò)大的 page_size 也會(huì)造成 B-Tree 查詢退化到二分查找、CPU 占用增加以及 OS 級(jí) cache 命中率的下降的問(wèn)題.

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

并且在對(duì)比測(cè)試中我們發(fā)現(xiàn) page_size 的選取往往會(huì)出現(xiàn)一個(gè)拐點(diǎn).拐點(diǎn)以前隨著 page_size 增加各種性能指標(biāo)都會(huì)持續(xù)改善.但一旦過(guò)了拐點(diǎn),性能將沒(méi)有明顯的改變,各個(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è)因素：

1. table_size

2. 存儲(chǔ)的數(shù)據(jù)類(lèi)型

3. 增刪查改比例

我們簡(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 來(lái)緩存查詢結(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)的影響無(wú)法提前計(jì)算存儲(chǔ)地址,二則變長(zhǎng)的數(shù)據(jù)往往會(huì)造成 page 空洞,空間利用率也有下降.

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

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

A = page_size(bytes)

B = cache_size(個(gè))

C = DBThread

D = UIThread

E = Insert 60000 row to TABLE E(25 columns, 10 integer, 15 varchar, 1 blob)

F = Insert 80000 row to TABLE F(7 column, 6 integer, 1 varchar)

G = update 2000 row of TABLE E with WHERE clause, select 50 row from TABLE J, at last update 2000 row of TABLE E with WHERE clause again

H = insert 40000 row to TABLE H(9 columns, 8 integer, 1 varchar)

J = insert 50 row to TABLE J(15 columns, 11 integer, 2 varchar, 2 blob)

K = DB File size(MB)

L = memory usage peak size(MB)

從上表我們看到,放大 page_size 和 cache_size 并不能不斷的獲得性能的提升,在拐點(diǎn)以后提升帶來(lái)的優(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 是合理的.

所以通過(guò)表格分析以后,我們傾向于選擇 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. 通過(guò) 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

// sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);Statement begin(GetCachedStatement(SQL_FROM_HERE, "BEGIN TRANSACTION"));

begin.Run();

m_timer.reset(base::ForegroundTimer::Create());m_timer->Start(FROM_HERE, base::TimeDelta::FromSeconds(5), this, &RenewTransaction);

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

void RenewTransaction()

{

// sqlite3_exec(db, "COMMIT", NULL, NULL, &sErrMsg);

Statement commit(GetCachedStatement(SQL_FROM_HERE, "COMMIT"));

commit.Run();

// sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

Statement begin(GetCachedStatement(SQL_FROM_HERE, "BEGIN TRANSACTION"));

begin.Run();

}

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

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

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

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

3. 緩存被編譯后的 SQL 語(yǔ)句

和其他很多編程語(yǔ)言一樣,數(shù)據(jù)庫(kù)使用的 SQL 語(yǔ)句也需要經(jīng)過(guò)編譯后才能被執(zhí)行使用.SQL 語(yǔ)句的編譯結(jié)果如果能夠被緩存下來(lái),第二次及以后再被使用時(shí)就能直接利用緩存結(jié)果,大大減少整個(gè)操作的執(zhí)行時(shí)間.與此同理的還有 Java 數(shù)學(xué)庫(kù)優(yōu)化,通過(guò)把極其復(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ù)語(yǔ)句復(fù)雜程度從幾毫秒到十幾毫秒不等,對(duì)于批量操作性能優(yōu)化是極其明顯的.

sprintf(strSQL, "INSERT INTO TABLEA VALUES (NULL, __1, __2, __3 __4, __5, __6, __7)");

sqlite3_prepare_v2(db, strSQL, BUFFER_SIZE, &stmt, &tail);

// cache stmt for later use

sqlite3_bind_text(stmt, 1, str1, -1, SQLITE_TRANSIENT);...

其實(shí)在上面的第2點(diǎn)中,已經(jīng)是用一個(gè)專(zhuān)門(mén)的類(lèi)將編譯結(jié)果保存下來(lái).每次根據(jù)文件名稱(chēng)和行號(hào)為索引,獲得對(duì)應(yīng)位置的 SQL 語(yǔ)句編譯結(jié)果.為了便于大家理解,我在注釋中也將 SQLIite 內(nèi)部最底層的辦法寫(xiě)出來(lá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、磁盤(pán)空間不足等原因都有可能造成數(shù)據(jù)庫(kù)的損壞.SQLite 提供了檢查數(shù)據(jù)庫(kù)完整性的命令

PRAGMA integrity_check

該 SQL 語(yǔ)句的執(zhí)行結(jié)果如果不為 OK ,則意味著數(shù)據(jù)庫(kù)損壞.程序可以通過(guò) ROLLBACK 到一個(gè)稍老的版本等辦法來(lái)解決數(shù)據(jù)庫(kù)損壞帶來(lái)的不穩(wěn)定性.

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

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

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

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

CREATE TABLE IF NOT EXISTS USER (

localid INTEGER PRIMARY KEY,

firstname LONGVARCHAR DEFAULT '' NOT NULL,

lastname LONGVARCHAR DEFAULT '' NOT NULL,

birthdate LONGVARCHAR DEFAULT '' NOT NULL,

age INTEGER DEFAULT 0 NOT NULL,

credit INTEGER DEFAULT 0 NOT NULL)INSERT INTO USER (localid, firstname , lastname , birthdate , age, credit) VALUES (?, ?, ?, ?, ?, ?)SELECT localid, firstname, lastname, birthdate, age, credit FROM USER

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

CREATE TABLE meta (key LONGVARCHAR NOT NULL UNIQUE PRIMARY KEY, value LONGVARCHAR)INSERT OR REPLACE INTO meta (key,value) VALUES (version, 1)INSERT OR REPLACE INTO meta (key,value) VALUES (last_compatible_version, 1)

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

SELECT value FROM meta WHERE key=versionSELECT value FROM meta WHERE key=last_compatible_versionCREATE TABLE IF NOT EXISTS USER (

localid INTEGER PRIMARY KEY,

firstname LONGVARCHAR DEFAULT '' NOT NULL,

lastname LONGVARCHAR DEFAULT '' NOT NULL,

birthdate LONGVARCHAR DEFAULT '' NOT NULL,

age INTEGER DEFAULT 0 NOT NULL,

credit INTEGER DEFAULT 0 NOT NULL,

deposit INTEGER DEFAULT 0 NOT NULL,

debt INTEGER DEFAULT 0 NOT NULL)INSERT INTO USER (localid, firstname , lastname , birthdate, age, credit, deposit) VALUES (?, ?, ?, ?, ?, ?, ?, ?)SELECT localid, firstname, lastname, birthdate, age, credit, deposit, debt FROM USER

很顯然 V2 版本的 SQL 語(yǔ)句很多都和 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ù)需要通過(guò) ALTER 操作增加兩列后使用.記得升級(jí)完畢后要更新數(shù)據(jù)庫(kù)的版本.代碼如下

if (1 == currentVersion) { ALTER TABLE USER ADD COLUMN deposit INTEGER DEFAULT 0 NOT NULL

ALTER TABLE USER ADD COLUMN debt INTEGER DEFAULT 0 NOT NULL

++currentVersion

}INSERT OR REPLACE INTO meta (key,value) VALUES (version, currentVersion)INSERT OR REPLACE INTO meta (key,value) VALUES (last_compatible_version, currentVersion)

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

SELECT value FROM meta WHERE key=versionSELECT value FROM meta WHERE key=last_compatible_versionCREATE TABLE IF NOT EXISTS USER (

localid INTEGER PRIMARY KEY,

firstname LONGVARCHAR DEFAULT '' NOT NULL,

lastname LONGVARCHAR DEFAULT '' NOT NULL,

birthdate LONGVARCHAR DEFAULT '' NOT NULL,

credit INTEGER DEFAULT 0 NOT NULL,

deposit INTEGER DEFAULT 0 NOT NULL,

debt INTEGER DEFAULT 0 NOT NULL)INSERT INTO USER (localid, firstname , lastname , birthdate, credit, deposit) VALUES (?, ?, ?, ?, ?, ?, ?, ?)SELECT localid, firstname, lastname, birthdate, credit, deposit, debt FROM USERif (2 == currentVersion) {

// do Nothing

++currentVersion

}INSERT OR REPLACE INTO meta (key,value) VALUES (version, currentVersion)INSERT OR REPLACE INTO meta (key,value) VALUES (last_compatible_version, 2)

注意 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ò)誤,也可以用類(lèi)似的辦法重新計(jì)算正確結(jié)果,刷新數(shù)據(jù)庫(kù).

二、如何寫(xiě)出高效的 SQL 語(yǔ)句

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

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

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

但真的如此的簡(jiǎn)單嗎?

要回答這個(gè)問(wèn)題,我們需要借助 SQLite 提供的 explain query 工具.

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

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

簡(jiǎn)單的說(shuō),SQLite 對(duì)主鍵會(huì)依照平衡多叉樹(shù)理論對(duì)其建樹(shù),使其搜索速度降低到 Log(N).

針對(duì)某列建立索引,就是將這列以及主鍵所有數(shù)據(jù)取出.以索引列為主鍵依照升序,原表主鍵為第二列,重新創(chuàng)建一張新的表.需要特別注意的是,針對(duì)多列建立索引的內(nèi)部實(shí)現(xiàn)方案是,索引第一列作為主鍵依照升序,第一列排序完畢后索引第二列依照升序,以此類(lèi)推,最后以原表主鍵作為最后一列.這樣就能保證每一行的數(shù)據(jù)都不完全相同,這種多列建索引的方式也叫 COVERING INDEX.所以對(duì)多列進(jìn)行索引,只有第一列的搜索速度理論上能到 Log(N).

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

實(shí)際上 SQLite 建立索引的方式并不是下列圖看起來(lái)的聚集索引,而是采用了非聚集索引.因?yàn)榉蔷奂饕男阅懿⒉槐染奂饕?但空間開(kāi)銷(xiāo)卻會(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)最后來(lái)創(chuàng)建一張表.如上圖所示

我們接下來(lái)要做的就是利用 explain query 來(lái)分析不同的索引方式對(duì)于查詢方式的影響,以及性能對(duì)比.

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;

SCAN TABLE t1

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

sqlite> CREATE INDEX i1 ON t1(a);

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;

SEARCH TABLE t1 USING INDEX i1

針對(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)化啊,沒(méi)必要找到一行數(shù)據(jù)后還要去原表找一次.剛才不是說(shuō)了嘛,對(duì)多列建索引的時(shí)候,是把這些列的數(shù)據(jù)都放入一個(gè)新的表.那我們?cè)囋嚳?

sqlite> CREATE INDEX i2 ON t1(a, b);

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;

SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

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

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

sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=2;

SCAN TABLE t1

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

// ANDsqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 AND b=2SEARCH TABLE t1 USING INDEX i2 (a=? AND b=?)// ORsqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2SCAN TABLE t1// b index ORsqlite> CREATE INDEX i3 ON t1(b);

sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

SEARCH TABLE t1 USING INDEX i3 (b=?)

WTF,搜索的時(shí)候用 AND 和 OR 的效果是不一樣的.其實(shí)多想想 COVERING INDEX 的實(shí)現(xiàn)原理也就想通了.對(duì)于沒(méi)有建索引的列進(jìn)行搜索那不就是掃描整張數(shù)據(jù)表.所以如果 App 對(duì)于兩列或以上有搜索需求時(shí),就需要了解一個(gè)概念 “前導(dǎo)列” .所謂前導(dǎo)列,就是在創(chuàng)建 COVERING INDEX 語(yǔ)句的第一列或者連續(xù)的多列.比如通過(guò)：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í)要用到等于類(lèi)的操作,需要像上面最后一個(gè)例子一樣為右邊的、不等于類(lèi)操作的列單獨(dú)建索引.

很多時(shí)候,我們對(duì)于搜索結(jié)果有排序的要求.如果對(duì)于排序列沒(méi)有建索引,可以想象 SQLite 內(nèi)部會(huì)對(duì)結(jié)果進(jìn)行一次排序.實(shí)際上如果對(duì)沒(méi)有建索引,SQLite 會(huì)建一棵臨時(shí) B Tree 來(lái)進(jìn)行排序.

// NO index on csqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c

SCAN TABLE t2

USE TEMP B-TREE FOR ORDER BY// YES index on csqlite> CREATE INDEX i4 ON t2(c);

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c

SCAN TABLE t2 USING INDEX i4

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

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

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

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

究其原因,索引表在 SQLite 內(nèi)部是以 B-Tree 的形式進(jìn)行組織的,一個(gè)樹(shù)節(jié)點(diǎn)一般對(duì)應(yīng)一個(gè) page.我們可以看到數(shù)據(jù)庫(kù)要寫(xiě)入、讀取、查詢索引表其實(shí)都需要用到公共的一個(gè)操作是搜索找到對(duì)應(yīng)的樹(shù)節(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í).也就是說(shuō),對(duì)索引表的各項(xiàng)操作,增刪查改的耗時(shí)取決于外存獲取節(jié)點(diǎn)的時(shí)間(SQLite 用 B-Tree 而非 STL 中采用的 RB-Tree 或平衡二叉樹(shù),正是為了盡可能降低樹(shù)的高度,減少外存讀取次數(shù)).一邊插入原始表的數(shù)據(jù),一邊插入索引表數(shù)據(jù),有可能造成索引表節(jié)點(diǎn)被頻繁換到外存又從外存讀取.而同一時(shí)間只進(jìn)行建索引的操作,OS 緩存節(jié)點(diǎn)的量將增加,命中率提高以后速度自然得到了一定的提升.

SQLite 的索引采用了 B-Tree,樹(shù)上的一個(gè) Node 一般占用一個(gè) page_size.

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

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

3. SELECT then INSERT VS INSERT OR REPLACE INTO

有過(guò) SQLite 開(kāi)發(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 只能通過(guò)先 SELECT 查詢插入數(shù)據(jù)主鍵對(duì)應(yīng)的行是否存在,不存在才能 INSERT,否則只能調(diào)用 UPDATE.而3.x版本起,SQLite 引入了 INSERT OR REPLACE INTO,用一行 SQL 語(yǔ)句就把原來(lái)的三行 SQL 封裝替代了.

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

4. Full Text Search(FTS)

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

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

// For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both an FTS table and an ordinary SQLite table created using the following SQL scriptCREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */CREATE TABLE enrondata2(content TEXT); /* Ordinary table */SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

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

CREATE VIRTUAL TABLE VCONTENT USING fts3(TOKENIZE icu, content LONGVARCHAR DEFAULT '' NOT NULL)INSERT INTO VCONTENT (docid, content) VALUES (?, ?)SELECT docid FROM VCONTENT WHERE content MATCH ? GROUP BY docid HAVING content LIKE ?

其實(shí)無(wú)論創(chuàng)建數(shù)據(jù)表的時(shí)候是否創(chuàng)建了行號(hào)(rowid)列,SQLite 都會(huì)為每個(gè)數(shù)據(jù)表創(chuàng)建行號(hào)列.想想上面的 fruitsforsale,當(dāng)數(shù)據(jù)表沒(méi)有任何列建了索引的時(shí)候,行號(hào)就是數(shù)據(jù)表的唯一索引.FTS 表略微不同的是,它的行號(hào)叫 docid,并且是可以用 SQL 語(yǔ)句拜訪的.我們一般會(huì)用字符串在原始表中的行號(hào)作為這里的 docid.

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

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

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

static int icuOpen辦法(

sqlite3_tokenizer *pTokenizer, /* The tokenizer */const char *zInput, /* Input string */int nInput, /* Length of zInput in bytes */sqlite3_tokenizer_cursor **ppCursor /* OUT: Tokenization cursor */){

...//pCsr->pIter = ubrk_open(UBRK_WORD, p->zLocale, pCsr->aChar, iOut, &status); 按單詞分詞// 按字母進(jìn)行拆分pCsr->pIter = ubrk_open(UBRK_CHARACTER, p->zLocale, pCsr->aChar, iOut, &status);

...

}/** The possible types of text boundaries. @stable ICU 2.0 */typedef enum UBreakIteratorType {/** Character breaks @stable ICU 2.0 */UBRK_CHARACTER = 0,/** Word breaks @stable ICU 2.0 */UBRK_WORD = 1,/** Line breaks @stable ICU 2.0 */UBRK_LINE = 2,/** Sentence breaks @stable ICU 2.0 */UBRK_SENTENCE = 3,

UBRK_TITLE = 4,

UBRK_COUNT = 5} UBreakIteratorType;

其實(shí)只需要改變讀取 ICU 的方式,就能支持英文按字母拆分了.

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

在設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí),我們會(huì)把一個(gè)對(duì)象的屬性分成不同的列按行存儲(chǔ).如果屬性是個(gè)數(shù)量不定的數(shù)組,切忌不要把這個(gè)數(shù)組屬性放到一個(gè)新表里面.上面我們提到過(guò)數(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ù)

先說(shuō)結(jié)論,這樣做是數(shù)據(jù)庫(kù) Model 跨 iOS、Android 平臺(tái)的辦理方案.兩個(gè)平臺(tái)用同一份 proto 文件分別生成各自的實(shí)現(xiàn)文件.需要跨平臺(tái)時(shí)將數(shù)據(jù)序列化后,以傳遞內(nèi)存的方式通過(guò) 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)存開(kāi)銷(xiāo).

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

標(biāo)題已經(jīng)勝過(guò)千言萬(wàn)語(yǔ)了.多線程版的 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ù)接口哀求后,一定要保證接口是異步返回?cái)?shù)據(jù)才能保證整個(gè)UI操作的流暢性.但是異步接口開(kāi)發(fā)最大的麻煩在于調(diào)用在A處,還要實(shí)現(xiàn)一個(gè) B 方法來(lái)處理異步返回的結(jié)果.這里推薦使用 C++11的 lambda 表達(dá)式加模板函數(shù) base::Bind 來(lái)實(shí)現(xiàn)像 JavaScript 語(yǔ)言一樣,能夠?qū)惒交卣{(diào)方法作為輸入?yún)?shù)傳遞給執(zhí)行方,待執(zhí)行完成操作后進(jìn)行異步回調(diào).用異步化接口編程,大大降低開(kāi)發(fā)難度和實(shí)現(xiàn)量,并帶來(lái)了流暢的界面體驗(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)需要克服.

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

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

// base::Bind是chromium開(kāi)源庫(kù)中的基礎(chǔ)庫(kù)base project中的一個(gè)模板辦法.// base::Bind的邏輯是將一個(gè)辦法包裝在一個(gè)模板類(lèi)中,并重載operator()辦法來(lái)實(shí)現(xiàn)通過(guò)調(diào)用模板類(lèi)()來(lái)調(diào)用其包裝的函數(shù)// base::Bind的功效非常接近于boost::function,這里用boost::function來(lái)進(jìn)行替換亦可namespace base { template <typename Functor> auto BindLambda(const Functor& functor) -> decltype(base::Bind(&Functor::operator(), base::Owned(&functor))) { auto functor_on_heap = new Functor(functor); return base::Bind(&Functor::operator(), base::Owned(functor_on_heap));

}

}

// 像JS一樣把Callback函數(shù)作為輸入?yún)?shù)進(jìn)行了傳遞,這樣不需要多加一個(gè)函數(shù)就能把vector中能夠整除2的數(shù)據(jù)篩查出來(lái)std::vector<int> result = filter(source, base::BindLambda([=](int dividend) -> bool{ return dividend % 2 == 0;

}));

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

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

最后,希望本文能對(duì)大家有所贊助.