SQLite一個(gè)文件憑什么成為世界上用得最多的數(shù)據(jù)庫如果我告訴你你口袋里的手機(jī)、你正在用的瀏覽器、電腦上的 Python 和 Node.js甚至每一臺(tái) Mac 和絕大多數(shù) Linux 系統(tǒng)里都內(nèi)置了一個(gè)完整的數(shù)據(jù)庫你可能會(huì)問這是哪個(gè)數(shù)據(jù)庫答案是 SQLite。它沒有獨(dú)立的服務(wù)器進(jìn)程沒有安裝向?qū)]有賬號(hào)密碼沒有my.cnf配置文件。所謂一個(gè)數(shù)據(jù)庫在 SQLite 的世界里就是一個(gè)普通的文件。備份它就是復(fù)制文件刪除它就是刪除文件遷移它就是把它拷到另一個(gè)目錄。這個(gè) 2000 年就誕生的項(xiàng)目如今是世界上部署量最大的數(shù)據(jù)庫——官方估計(jì)的安裝量以百億計(jì)比 MySQL 和 PostgreSQL 加起來還多幾個(gè)數(shù)量級(jí)。而它的源碼放在公有域里你可以隨隨便便把它嵌進(jìn)任何商業(yè)產(chǎn)品不用署名不用付費(fèi)。這篇文章想聊清楚三件事SQLite 到底是什么它內(nèi)部是怎么工作的以及什么時(shí)候該用它、什么時(shí)候不該用。數(shù)據(jù)庫住進(jìn)程序里要理解 SQLite最快的方式是先看看正常的數(shù)據(jù)庫長什么樣。用 MySQL 的時(shí)候你的程序和數(shù)據(jù)庫是兩個(gè)東西數(shù)據(jù)庫是一個(gè)獨(dú)立運(yùn)行的進(jìn)程守在網(wǎng)絡(luò)的某個(gè)端口上你的程序通過 TCP 連過去把 SQL 發(fā)過去再把結(jié)果讀回來。中間隔著一次網(wǎng)絡(luò)往返隔著序列化和反序列化隔著賬號(hào)權(quán)限校驗(yàn)。你要先安裝它啟動(dòng)它配置它運(yùn)維它。SQLite 把這套東西全部砍掉了。它就是一個(gè) C 語言寫的庫編譯進(jìn)你的程序和你的代碼跑在同一個(gè)進(jìn)程里。所謂執(zhí)行 SQL就是一次普通的函數(shù)調(diào)用。數(shù)據(jù)存在哪磁盤上一個(gè).db文件而已。MySQL 應(yīng)用程序 ──網(wǎng)絡(luò)── 數(shù)據(jù)庫服務(wù)器進(jìn)程 ── 磁盤 SQLite 應(yīng)用程序 ──函數(shù)調(diào)用── SQLite 引擎 ── 一個(gè) .db 文件 同一個(gè)進(jìn)程里這個(gè)設(shè)計(jì)帶來的好處是連鎖的零配置。不用安裝不用啟動(dòng)服務(wù)第一次調(diào)用就自動(dòng)建庫。零運(yùn)維。沒有守護(hù)進(jìn)程可以掛沒有端口可以被攻擊沒有配置可以調(diào)錯(cuò)。零網(wǎng)絡(luò)。查詢不走網(wǎng)絡(luò)不存在連接池耗盡、網(wǎng)絡(luò)抖動(dòng)這些問題。備份極簡。停寫拷文件完事。當(dāng)然代價(jià)也是同樣的直接數(shù)據(jù)庫和應(yīng)用綁死在一個(gè)進(jìn)程里你能用多強(qiáng)的機(jī)器它就有多大能耐。這是后話。一個(gè) .db 文件里裝了什么SQLite 官方文檔里有一句很有意思的話理解 SQLite 的最好方式是理解它的文件格式。因?yàn)檫@個(gè)文件里裝的不是一堆看不懂的二進(jìn)制黑盒而是一棵棵結(jié)構(gòu)清晰的 B-tree。打開一個(gè)數(shù)據(jù)庫文件它由固定大小的頁默認(rèn) 4096 字節(jié)串起來就像一本書的一頁頁紙。文件開頭 100 字節(jié)是頭部魔數(shù)、頁大小、編碼、版本號(hào)。剩下的頁里住著兩類東西表和索引它們?nèi)际?B-tree。對(duì)你沒看錯(cuò)——表就是一棵 B-tree。你建的每一張表是一棵樹每個(gè)索引是另一棵樹。表那棵樹的葉子節(jié)點(diǎn)上放著完整的行數(shù)據(jù)索引那棵樹的葉子節(jié)點(diǎn)上放著索引列 行指針。查詢走索引就是在索引那棵樹上定位拿到行指針再回到表那棵樹里把整行撈出來。和 MySQL 的 InnoDB 大同小異只不過這一切都濃縮在一個(gè)文件里。這里有個(gè) SQLite 特有的細(xì)節(jié)值得單獨(dú)說。每張表其實(shí)都有一個(gè)隱藏的 64 位整數(shù)主鍵叫rowid哪怕你建表時(shí)沒寫主鍵它也存在。而如果你把主鍵聲明成INTEGER PRIMARY KEY它就是rowid的別名——這意味著主鍵查詢等于直接在樹上定位一次到位是 SQLite 里最快的查找路徑。反過來如果你用一個(gè)字符串或者 UUID 當(dāng)主鍵所有查找都要繞道二級(jí)索引。所以 SQLite 社區(qū)有個(gè)約定俗成的建議能用整數(shù)自增主鍵就用INTEGER PRIMARY KEY。這不是老派是這個(gè)文件格式?jīng)Q定的最優(yōu)解。寫入為什么慢以及 WAL 的救場(chǎng)SQLite 有一個(gè)名聲寫入慢。這個(gè)名聲一半是真的一半是誤會(huì)。先說真的那一半。數(shù)據(jù)庫要保證事務(wù)提交了就一定在持久性就必須在改數(shù)據(jù)時(shí)確保落盤——也就是調(diào)用fsync等磁盤真的把字節(jié)寫進(jìn)去。在默認(rèn)的 journal 模式下SQLite 的一次寫入是這么干的先把要改的頁的舊內(nèi)容復(fù)制到日志文件fsync改主文件fsync刪掉日志文件。一次提交至少兩次fsync。機(jī)械硬盤上一次fsync是毫秒級(jí)也就是說每行數(shù)據(jù)單獨(dú)提交一個(gè)事務(wù)每秒撐死幾百次寫入。這是性能懸崖也是大多數(shù)人抱怨SQLite 寫入慢的真實(shí)原因。但誤會(huì)在于這根本不是 SQLite 的上限。你把一千行數(shù)據(jù)放進(jìn)一個(gè)事務(wù)里提交fsync還是那幾次吞吐量立刻能沖到每秒幾萬行。寫入的粒度比寫入的數(shù)量重要得多。再說 WAL 的救場(chǎng)。SQLite 有個(gè)叫 WALWrite-Ahead Log預(yù)寫日志的模式一條命令開啟PRAGMA journal_modeWAL;開啟之后寫入不再直接改主文件而是先追加寫進(jìn)一個(gè)叫-wal的日志文件后臺(tái)再慢慢合并回主文件。讀取的時(shí)候讀主文件 WAL 里還沒合并的部分得到一個(gè)一致的快照。這帶來兩個(gè)質(zhì)變第一讀寫不再互相阻塞。舊模式下寫的時(shí)候要獨(dú)占文件所有讀都得等WAL 模式下讀者讀快照寫者寫日志互不干擾。對(duì)讀多寫少的場(chǎng)景這是決定性的提升。第二寫者和寫者之間也更從容。雖然同一時(shí)刻仍然只允許一個(gè)寫事務(wù)這點(diǎn)下面細(xì)說但沖突的窗口小了很多。代價(jià)是目錄里多了兩個(gè)文件app.db-wal和app.db-shm。這里埋著兩個(gè)經(jīng)典坑備份時(shí)只拷主文件會(huì)丟掉 WAL 里沒合并的最新數(shù)據(jù)。正確姿勢(shì)是先執(zhí)行VACUUM INTO backup.db生成一個(gè)原子快照或者先跑PRAGMA wal_checkpoint(TRUNCATE)把 WAL 合并清空。WAL 依賴共享內(nèi)存mmap所以數(shù)據(jù)庫文件必須放在本地磁盤。放 NFS、放 Windows 網(wǎng)絡(luò)共享輕則報(bào)錯(cuò)重則直接損壞數(shù)據(jù)。云端容器部署時(shí)把這個(gè)路徑掛到網(wǎng)絡(luò)存儲(chǔ)上是真實(shí)發(fā)生過的事故。一把庫級(jí)鎖和單寫者的智慧如果說 WAL 解決了讀和寫打架那寫和寫打架的問題依然存在而且是 SQLite 最根本的架構(gòu)約束。SQLite 的鎖是庫級(jí)別的不管你的程序里開了多少個(gè)連接、多少個(gè)線程同一時(shí)刻整個(gè)數(shù)據(jù)庫只有一個(gè)寫事務(wù)在跑。寫和寫之間永遠(yuǎn)排隊(duì)。這跟 MySQL 的行級(jí)鎖完全不同——在 MySQL 里更新兩行不相干的數(shù)據(jù)可以并行在 SQLite 里不行先來后到。于是并發(fā)寫會(huì)撞上那個(gè)著名的錯(cuò)誤SQLITE_BUSY: database is locked。解法有三層一層比一層根本。第一層等。每個(gè)連接都設(shè)置PRAGMA busy_timeout 5000意思是拿不到鎖就原地重試 5 秒而不是立刻報(bào)錯(cuò)。光是這一條就能消掉九成的 BUSY 錯(cuò)誤。注意它是 per-connection 的連接池里每個(gè)新連接都得帶上。第二層聰明地等。事務(wù)別用默認(rèn)的BEGINdeferred第一條語句才去搶鎖沖突暴露得晚、處理起來最狼狽有寫操作就用BEGIN IMMEDIATE一開始就把寫鎖搶到手——沖突提前暴露應(yīng)用層捕獲SQLITE_BUSY做指數(shù)退避重試即可。第三層從架構(gòu)上消滅競爭單寫者模式。這是 SQLite 官方推薦的高并發(fā)寫姿勢(shì)——所有寫操作投進(jìn)一個(gè)隊(duì)列由單個(gè)連接串行消費(fèi)讀操作走連接池隨便并發(fā)。寫鎖永遠(yuǎn)不沖突因?yàn)閷懻吒局挥幸粋€(gè)吞吐量反而比一堆連接互相搶鎖高得多。讀連接池N 個(gè)──→ ┌────────────┐ ←── 寫隊(duì)列 → 單寫協(xié)程串行 │ SQLite 文件 │ └────────────┘想明白這一點(diǎn)SQLite 的并發(fā)問題就從bug變成了設(shè)計(jì)約束下的正常工作方式。它不是不能高并發(fā)它是不能多寫者并發(fā)——而多數(shù)應(yīng)用的寫入量一個(gè)串行寫者綽綽有余。它其實(shí)沒那么簡陋很多人對(duì) SQLite 的印象停留在存存配置的小數(shù)據(jù)庫這低估它了。它是一個(gè)通過了 SQL 標(biāo)準(zhǔn)大部分測(cè)試的完整關(guān)系數(shù)據(jù)庫事務(wù)、外鍵、視圖、觸發(fā)器、CTE、窗口函數(shù)一樣不缺。更驚喜的是那些白送的內(nèi)置擴(kuò)展。全文檢索。FTS5 虛擬表開箱即用倒排索引、分詞、詞干還原、BM25 排序、關(guān)鍵詞高亮一套齊全。寫個(gè)站內(nèi)搜索、日志檢索CREATE VIRTUAL TABLE ... USING fts5(...)加一句MATCH就完事不用引入 Elasticsearch。JSON 處理。json_extract()能像查列一樣查 JSON 字段配合表達(dá)式索引還能直接給 JSON 里的字段建索引CREATEINDEXidx_statusONorders(json_extract(data,$.status));某種意義上這讓 SQLite 變成了一個(gè)帶索引的文檔數(shù)據(jù)庫。空間索引。R*Tree 擴(kuò)展讓經(jīng)緯度范圍查詢變成一次索引掃描地圖 POI 檢索這種活它也接得住。文件即工具。.dump導(dǎo)出文本、VACUUM INTO在線熱備、dbstat虛擬表告訴你是誰把庫撐大了、generate_series生成數(shù)字序列……這些內(nèi)置能力加起來SQLite 經(jīng)常能在小場(chǎng)景里頂替掉一整套中間件。動(dòng)態(tài)類型一個(gè)溫柔的陷阱從 MySQL 切到 SQLite最容易被咬一口的是類型系統(tǒng)。SQLite 的列類型是建議而非規(guī)定官方術(shù)語叫 type affinity類型親和性。你寫age INTEGER它只是傾向于把值轉(zhuǎn)成整數(shù)轉(zhuǎn)不了也不會(huì)拒絕CREATETABLEt(aINTEGER);INSERTINTOtVALUES(123);-- 存成整數(shù) 123INSERTINTOtVALUES(abc);-- 也能存變成文本 abcINSERTINTOtVALUES(3.5);-- 浮點(diǎn)也收聽起來寬容用起來要命一列里混著整數(shù)和文本ORDER BY的結(jié)果就可能詭異SQLite 的類型排序是 NULL 數(shù)字 文本寫進(jìn) MySQL 時(shí)才會(huì)發(fā)現(xiàn)數(shù)據(jù)早臟了。應(yīng)對(duì)方式很樸素別指望列類型兜底。NOT NULL 該加就加應(yīng)用層做校驗(yàn)需要的話用CHECK (typeof(a) integer)顯式約束。另外時(shí)間字段建議統(tǒng)一存 ISO8601 文本或 Unix 時(shí)間戳別讓格式自由發(fā)揮。MySQL 和 SQLite 的語法差異還有不少——沒有TRUNCATE、沒有AUTO_INCREMENT、ON DUPLICATE KEY UPDATE要換成ON CONFLICT、字符串拼接是||不是CONCAT。寫之前記住一個(gè)原則SQLite 是方言不是 MySQL 的子集重要 SQL 讓EXPLAIN QUERY PLAN驗(yàn)一遍比背語法表管用。什么時(shí)候該用它什么時(shí)候別用聊了這么多回到最實(shí)際的問題什么時(shí)候選 SQLite它的甜蜜點(diǎn)很清晰——單機(jī)、嵌入、讀多寫少、不想運(yùn)維。桌面應(yīng)用和手機(jī) App 的本地存儲(chǔ)是它的主場(chǎng)iOS 的 Core Data、Android 的 Room 底層都可以是它CLI 工具、腳本、爬蟲的落地存儲(chǔ)它比寫個(gè) CSV省心比起個(gè) MySQL輕量微服務(wù)的本地緩存和元數(shù)據(jù)不值得為它開一個(gè)數(shù)據(jù)庫實(shí)例的場(chǎng)合用它正合適還有原型和測(cè)試環(huán)境——很多項(xiàng)目開發(fā)時(shí)用 SQLite幾行配置就能切到生產(chǎn)用的 MySQL。反過來這些場(chǎng)景請(qǐng)直接繞開多個(gè)服務(wù)實(shí)例要同時(shí)寫同一份數(shù)據(jù)。單寫者是進(jìn)程內(nèi)的紀(jì)律跨機(jī)器它管不著。要么上 LiteFS、rqlite 這類分布式 SQLite 方案要么老老實(shí)實(shí)用 MySQL。高頻并發(fā)寫。不是寫不進(jìn)去是排隊(duì)。寫入量大到一個(gè)串行寫者跟不上時(shí)換引擎比優(yōu)化更省時(shí)間。需要賬號(hào)權(quán)限、審計(jì)、行級(jí)安全。SQLite 沒有用戶概念它的安全模型就是文件權(quán)限——能讀文件的人能讀全部數(shù)據(jù)。數(shù)據(jù)庫必須放在網(wǎng)絡(luò)存儲(chǔ)上。WAL 依賴本地共享內(nèi)存這條是紅線。有個(gè)粗略但好用的判斷標(biāo)準(zhǔn)如果你在糾結(jié)要不要給這個(gè)數(shù)據(jù)庫寫運(yùn)維腳本說明它已經(jīng)不是 SQLite 的量級(jí)了。反過來如果你發(fā)現(xiàn)自己在寫腳本啟動(dòng)數(shù)據(jù)庫、檢查端口、清理連接池——那這些活 SQLite 天生就不需要。結(jié)語SQLite 不是玩具數(shù)據(jù)庫也不是 MySQL 的廉價(jià)替代品。它是一種截然不同的架構(gòu)選擇把數(shù)據(jù)庫從數(shù)據(jù)中心搬進(jìn)程序內(nèi)部用一個(gè)文件換掉了整個(gè)服務(wù)端。它用 B-tree 和頁組織數(shù)據(jù)用 journal 和 WAL 守住 ACID用一把庫級(jí)寫鎖換來實(shí)現(xiàn)的極致簡單與可靠——簡單到它的測(cè)試代碼比產(chǎn)品代碼多幾百倍穩(wěn)定到二十年前的數(shù)據(jù)庫文件今天照樣能打開。下一次當(dāng)你隨手pip install某個(gè)包、打開一個(gè) App、或者在代碼里寫下sql.Open(sqlite, app.db)的時(shí)候可以想起這件事你沒有啟動(dòng)任何服務(wù)器但你確實(shí)打開了一個(gè)完整的數(shù)據(jù)庫。它就在那個(gè)文件里。參考鏈接SQLite 官網(wǎng)https://www.sqlite.org官方文件格式文檔https://www.sqlite.org/fileformat.html官方 WikiHow To Corrupt An SQLite Database File反著讀學(xué)保命https://www.sqlite.org/howtocorrupt.htmlSQLite 在 WAL 下的并發(fā)https://www.sqlite.org/wal.html