本文由雲+社區發表 SQLite 在移動端開發中廣泛使用，其使用質量直接影響到產品的體驗。 常見的 SQLite 質量監控一般都是依賴上線後反饋的機制，比如耗時監控或者用戶反饋。這種方式問題是： 事後發現，負面影響已經發生。 關註的只是沒這麼差。eg. 監控閾值為 500ms ，那麼一條可優化為 2 ...
本文由雲+社區發表
SQLite 在移動端開發中廣泛使用，其使用質量直接影響到產品的體驗。
常見的 SQLite 質量監控一般都是依賴上線後反饋的機制，比如耗時監控或者用戶反饋。這種方式問題是：
- 事後發現，負面影響已經發生。
- 關註的只是沒這麼差。eg. 監控閾值為 500ms ，那麼一條可優化為 20ms 而平均耗時只有 490ms 的 sql 就被忽略了。
能否在上線前就進行SQLite使用質量的監控？於是我們嘗試開發了一個工具: SQLiteLint 。雖然名帶 “lint ” ，但並不是代碼的靜態檢查，而是在 APP 運行時對 sql 語句、執行序列、表信息等進行分析檢測。而和 “lint” 有點類似的是：在開發階段就介入，並運用一些最佳實踐的規則來檢測，從而發現潛在的、可疑的 SQLite 使用問題。
本文會介紹 SQLiteLint 的思路，也算是 SQLite 使用經驗的分享，希望對大家有所幫助。
簡述
SQLiteLint 在 APP 運行時進行檢測，而且大部分檢測演算法與數據量無關即不依賴線上的數據狀態。只要你觸發了某條 sql 語句的執行，SQLiteLint 就會幫助你 review 這條語句是否寫得有問題。而這在開發、測試或者灰度階段就可以進行。
檢測流程十分簡單：
\1. 收集 APP 運行時的 sql 執行信息 包括執行語句、創建的表信息等。其中表相關信息可以通過 pragma 命令得到。對於執行語句，有兩種情況： a）DB 框架提供了回調介面。比如微信使用的是 WCDB ，很容易就可以通過MMDataBase.setSQLiteTrace 註冊回調拿到這些信息。 b） 若使用 Android 預設的 DB 框架，SQLiteLint 提供了一種無侵入的獲取到執行的sql語句及耗時等信息的方式。通過hook的技巧，向 SQLite3 C 層的 api sqlite3_profile 方法註冊回調，也能拿到分析所需的信息，從而無需開發者額外的打點統計代碼。
\2. 預處理 包括生成對應的 sql 語法樹，生成不帶實參的 sql ，判斷是否 select* 語句等，為後面的分析做準備。預處理和後面的演算法調度都在一個單獨的處理線程。
\3. 調度具體檢測演算法執行 checker 就是各種檢測演算法，也支持擴展。並且檢測演算法都是以 C++ 實現，方便支持多平臺。而調度的時機包括：最近未分析 sql 語句調度，抽樣調度，初始化調度，每條 sql 語句調度。
\4. 發佈問題 上報問題或者彈框提示。
可以看到重點在第 3 步，下麵具體討論下 SQLiteLint 目前所關註的質量問題檢測。
檢測問題簡介
一、檢測索引使用問題
索引的使用問題是資料庫最常見的問題，也是最直接影響性能的問題。SQLiteLint 的分析主要基於 SQLite3 的 "explain query plan" ，即 sql 的查詢計劃。先簡單說下查詢計劃的最常見的幾個關鍵字：
SCAN TABLE: 全表掃描，遍曆數據表查找結果集，複雜度 O(n) SEARCH TABLE: 利用索引查找，一般除了 without rowid 表或覆蓋索引等，會對索引樹先一次 Binary Search 找到 rowid ，然後根據得到 rowid 去數據表做一次 Binary Search 得到目標結果集，複雜度為 O(logn) USE TEMP B-TREE: 對結果集臨時建樹排序，額外需要空間和時間。比如有 Order By 關鍵字，就有可能出現這樣查詢計劃
通過分析查詢計劃，SQLiteLint 目前主要檢查以下幾個索引問題：
1. 未建索引導致的全表掃描（對應查詢計劃的 SCAN TABLE... ）
雖然建立索引是最基本優化技巧，但實際開發中，很多同學因為意識不夠或者需求太緊急，而疏漏了建立合適的索引，SQLiteLint 幫助提醒這種疏漏。問題雖小，解決也簡單，但最普遍存在。 這裡也順帶討論下一般不適合建立索引的情況：寫多讀少以及表行數很小。但對於客戶端而言，寫多讀少的表應該不常見。而表行數很小的情況，建索引是有可能導致查詢更慢的（因為索引的載入需要的時間可能大過全表掃描了），但是這個差別是微乎其微的。所以這裡認為一般情況下，客戶端的查詢還是儘量使用索引優化，如果確定預估表數量很小或者寫多讀少，也可以將這個表加到不檢測的白名單。
解決這類問題，當然是建立對應的索引。
2. 索引未生效導致的全表掃描（對應查詢計劃的 SCAN TABLE... ）
有些情況即便建立了索引，但依然可能不生效，而這種情況有時候是可以通過優化 sql 語句去用上索引的。舉個例子：
以上看到，即便已建立了索引，但實際沒有使用索引來查詢。 如對於這個 case ，可以把 like 變成不等式的比較：
這裡看到已經是使用索引來 SEARCH TABLE ，避免了全表掃描。但值得註意的是並不是所有 like 的情況都可以這樣優化，如 like '%lo' 或 like '%lo%' ，不等式就做不到了。
再看個位操作導致索引不生效的例子：
位操作是最常見的導致索引不生效的語句之一。但有些時候也是有些技巧的利用上索引的，假如這個 case 里 flag 的業務取值只有 0x1，0x2，0x4，0x8 ，那麼這條語句就可以通過窮舉值的方式等效：
以上看到，把位操作轉成 in 窮舉就能利用索引了。
解決這類索引未生效導致的全表掃描 的問題，需要結合實際業務好好優化sql語句，甚至使用一些比較trick的技巧。也有可能沒辦法優化，這時需要添加到白名單。
3. 不必要的臨時建樹排序（對應查詢計劃的 USE TEMP B-TREE... )。
比如sql語句中 order by 、distinct 、group by 等就有可能引起對結果集臨時額外建樹排序，當然很多情況都是可以通過建立恰當的索引去優化的。舉個例子：
以上看到，即便id和mark都分別建立了索引，即便只需要一行結果，依然會引起重新建樹排序（ USE TEMP B-TREE FOR ORDER BY ）。當然這個case非常簡單，不過如果對 SQLite 的索引不熟悉或者開發時鬆懈了，確實很容易發生這樣的問題。同樣這個問題也很容易優化:
這樣就避免了重新建樹排序，這對於數據量大的表查詢，優化效果是立竿見影的好。
解決這類問題，一般就是建立合適的索引。
4. 不足夠的索引組合
這個主要指已經建立了索引，但索引組合的列並沒有覆蓋足夠 where 子句的條件式中的列。SQLiteLint 檢測出這種問題，建議先關註該 sql 語句是否有性能問題，再決定是否建立一個更長的索引。舉個例子：
以上看到，確實是利用了索引 genderIndex 來查詢，但看到where子句里還有一個 mark=60 的條件，所以還有一次遍歷判斷操作才能得到最終需要的結果集。尤其對於這個 case，gender 也就是性別，那麼最多 3 種情況，這個時候單獨的 gender 索引的優化效果的已經不明顯了。而同樣，優化也是很容易的：
解決這類問題，一般就是建立一個更大的組合索引。
5. 怎麼降低誤報
現在看到 SQLiteLint 主要根據查詢計劃的某些關鍵字去發現這些問題，但SQLite支持的查詢語法是非常複雜的，而對應的查詢計劃也是無窮變化的。所以對查詢計劃自動且正確的分析，不是一件容易的事。SQLiteLint 很大的功夫也在這件事情上
所以對查詢計劃自動且正確的分析，不是一件容易的事。SQLiteLint 很大的功夫也在這件事情上。SQLiteLint 這裡主要對輸出的查詢計劃重新構建了一棵有一定的特點的分析樹，並結合sql語句的語法樹，依據一定的演算法及規則進行分析檢測。建分析樹的過程會使用到每條查詢計劃前面如 "0|1|0" 的數字，這裡不具體展開了。 舉個例子：是不是所有帶有 "SCAN TABLE" 首碼的查詢計劃，都認為是需要優化的呢？明顯不是。具體看個 case ：
這是一個聯表查詢，在 SQLite 的實現里一般就是嵌套迴圈。在這個語句中里， t3.id 列建了索引，並且在第二層迴圈中用上了，但第一層迴圈的 SCAN TABLE是無法優化的。比如嘗試給t4的id列也建立索引：
可以看出,依然無法避免 SCAN TABLE 。對於這種 SCAN TABLE 無法優化的情況，SQLiteLint 不應該誤報。前面提到，會對查詢計劃組織成樹的結構。比如對於這個 case ，最後構建的查詢計劃分析樹為：
分析樹，有個主要的特點：葉子節點有兄弟節點的是聯表查詢，其迴圈順序對應從左往右，而無兄弟節點是單表查詢。而最後的分析會落地到葉子節點的分析。遍歷葉子節點時，有一條規則（不完整描述）是：
葉子節點有兄弟節點的，且是最左節點即第一層迴圈，且 where 子句中不含有相關常量條件表達式時，SCAN TABLE 不認為是質量問題。
這裡有兩個條件必須同時滿足，SCAN TABLE 才不報問題：第一層迴圈 & 無相關常量表達式。第一層迴圈前面已經描述，這裡再解釋下後面一個條件。
由上看到,當select子句中出現常量條件表達式 “t4.id=666” , 若 t3.id,t4.id 都建了索引，是可以優化成沒有 SCAN TABLE 。
而把 t4.id 的索引刪除後，又出現了 SCAN TABLE 。而這種 SCAN TABLE 的情況，不滿足規則里的的第二個條件，SQLiteLint 就會報出可以使用索引優化了。
這裡介紹了一個較簡單語句的查詢計劃的分析，當然還有更複雜的語句，還有子查詢、組合等等，這裡不展開討論了。巨大的複雜性，無疑對準確率有很大的挑戰，需要對分析規則不斷地迭代完善。當前 SQLiteLint 的分析演算法依然不足夠嚴謹，還有很大的優化空間。 這裡還有另一個思路去應對準確性的問題：對所有上報的問題，結合耗時、是否主線程、問題等級等信息，進行優先順序排序。這個“曲線救國”來降低誤報的策略也適用本文介紹的所有檢測問題。
二、檢測冗餘索引問題
SQLiteLint 會在應用啟動後對所有的表檢測一次是否存在冗餘索引，並建議保留最大那個索引組合。
先定義什麼是冗餘索引：如對於某個表，如果索引組合 index1，index2 是另一個索引組合 index3 的首碼，那麼一般情況下 index3 可以替代掉 index1 和 index2 的作用，所以 index1，index2 就冗餘了。而多餘的索引就會有多餘的插入消耗和空間消耗，一般就建議只保留索引 index3 。 看個例子：
以上看到，如果已經有一個 length 和 type 的組合索引，就已經滿足了單 length 列條件式的查詢，沒必要再為 length 再建一個索引。
三、檢測 select * 問題
SQLiteLint這裡通過掃描 sql 語法樹，若發現 select * 子句，就會報問題，建議儘量避免使用 select * ，而是按需 select 對應的列。
select * 是SQLite最常用的語句之一，也非常方便，為什麼還認為是問題的呢？這裡有必要辯駁一下：
- 對於 select * ，SQLite 底層依然存在一步把 * 展開成表的全部列。
- select * 也減少了可以使用覆蓋索引的機會。覆蓋索引指索引包含的列已經覆蓋了 select 所需要的列，而使用上覆蓋索引就可以減少一次數據表的查詢。
- 對於 Android 平臺而言，select * 就會投射所有的列，那麼每行結果占據的記憶體就會相對更大，那麼 CursorWindow（緩衝區）的容納條數就變少，那麼 SQLiteQuery.fillWindow 的次數就可能變多，這也有一定的性能影響。
基於以上原因，出於 SQLiteLint 目標最佳實踐的原則，這裡依然報問題。
四、檢測 Autoincrement 問題
SQLiteLint 在應用啟動後會檢測一次所有表的創建語句，發現 AUTOINCREMENT 關鍵字，就會報問題，建議避免使用 Autoincrement 。
這裡看下為什麼要檢測這個問題，下麵引用 SQLite 的官方文檔：
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
可以看出 Auto Increment 確實不是個好東西。 ps. 我這裡補充說明一下 strictly needed 是什麼是意思，也就是為什麼它不必要。通常 AUTOINCREMENT 用於修飾 INTEGER PRIMARY KEY 列，後簡稱IPK 列。而 IPK 列等同於 rowid 別名，本身也具有自增屬性，但會復用刪除的 rowid 號。比如當前有 4 行，最大的rowid是 4，這時把第 4 行刪掉，再插入一行，新插入行的 rowid 取值是比當前最大的 rowid 加 1，也就 3+1=4 ，所以復用了 rowid 號 4 。而如果加以 AUTOINCREMENT 修飾就是阻止了復用，在這個情況，rowid 號是 5 。也就是說，AUTOINCREMENT 可以保證了歷史自增的唯一性，但對於客戶端應用有多少這樣的場景呢？
五、檢測建議使用 prepared statement
SQLiteLint 會以抽樣的時機去檢測這個問題，比如每 50 條執行語句，分析一次執行序列，如果發現連續執行次數超過一定閾值的相同的（當然實參可以不同）而未使用 prepared statement 的 sql 語句，就報問題，建議使用 prepared statement 優化。 如閾值是 3 ，那麼連續執行下麵的語句，就會報問題：
使用 prepared statement 優化的好處有兩個：
- 對於相同（實參不同）的 sql 語句多次執行，會有性能提升
- 如果參數是不可信或不可控輸入，還防止了註入問題
六、檢測建議使用 without rowid 特性
SQLiteLint 會在應用啟動後檢測一次所有表的創建語句，發現未使用 without rowid 技巧且根據表信息判斷適合使用 without rowid 優化的表，就報問題，建議使用 without rowid 優化。 這是 SQLiteLint 的另一個思路，就是發現是否可以應用上一些 SQLite 的高級特性。
without rowid 在某些情況下可以同時帶來空間以及時間上將近一半的優化。簡單說下原理，如：
對於這個含有 rowid 的表（ rowid 是自動生成的），這時這裡涉及到兩次查詢，一次在 name 的索引樹上找到對應的 rowid ，一次是用這個 rowid 在數據樹上查詢到 mark 列。 而使用 without rowid 來建表：
數據樹構建是以 name 為 key ，mark 為 data 的，並且是以普通 B-tree 的方式存儲。這樣對於剛剛同樣的查詢，就需要只有一次數據樹的查詢就得到了 mark 列，所以演算法複雜度上已經省了一個 O(logn）。另外又少維護了一個 name 的索引樹，插入消耗和空間上也有了節省。
當然 withou rowid 不是處處適用的，不然肯定是預設屬性了。SQLiteLint 判斷如果同時滿足以下兩個條件，就建議使用 without rowid ：
- 表含有 non-integer or composite (multi-column) PRIMARY KEY
- 表每行數據大小不大，一個比較好的標準是行數據大小小於二十分之一的page size 。ps.預設 page size SQLite 版本3.12.0以後（對應 Android O 以上）是 4096 bytes ，以前是 1024 。而由於行數據大小業務相關，為了降低誤報，SQLiteLint 使用更嚴格的判定標準：表不含有 BLOB 列且不含有非 PRIMARY KEY TEXT 列。
簡單說下原因： 對於1，假如沒有 PRIMARY KEY ，無法使用 without rowid 特性；假如有 INTEGER PRIMARY KEY ，前面也說過，這時也已經等同於 rowid 。 對於 2，小於 20 分之一 pagesize 是官方給出的建議。 這裡說下我理解的原因。page 是 SQLite 一般的讀寫單位（實際上磁碟的讀寫 block 更關鍵，而磁碟的消耗更多在定位上，更多的page就有可能需要更多的定位）。without rowid 的表是以普通 B-Tree 存儲的，而這時數據也存儲在所有樹結點上，那麼假如數據比較大，一個 page 存儲的結點變少，那麼查找的過程就需要讀更多的 page ，從而查找的消耗更大。當然這是相對 rowid 表 B*-Tree 的存儲來說的，因為這時數據都在葉子結點，搜索路徑上的結點只有 KEY ，那麼一個page能存的結點就多了很多，查找磁碟消耗變小。這裡註意的是，不要以純記憶體的演算法複雜度去考量這個問題。以上是推論不一定正確，歡迎指教。
引申一下，這也就是為什麼 SQLite 的索引樹以 B-Tree 組織，而 rowid 表樹以 B-Tree 組織，因為索引樹每個結點的存主要是索引列和 rowid ，往往沒這麼大，相對 B-Tree 優勢就在於不用一直查找到葉子結點就能結束查找。與 without rowid 同樣的限制，不建議用大 String 作為索引列，這當然也可以加入到 SQLiteLint 的檢測。
小結
這裡介紹了一個在開發、測試或者灰度階段進行 SQLite 使用質量檢測的工具，這個思路的好處是：
- 上線前發現問題
- 關註最佳實踐
本文的較大篇幅其實是對 SQLite 最佳實踐的討論，因為 SQLiteLint 的思路就是對最佳實踐的自動化檢測。當然檢查可以覆蓋更廣的範圍，準確性也是挑戰，這裡還有很大的空間。
此文已由作者授權騰訊雲+社區發佈
搜索關註公眾號「雲加社區」，第一時間獲取技術乾貨，關註後回覆1024 送你一份技術課程大禮包！
