在實例學習中，系統性能調優的案例是最值得學習的。之前蟲蟲給大家介紹過語言方面性能的調優、算法方面調優的，今天給大家介紹一個有關數據庫查詢方面性能優化的實例。

這是一個開源連續基準測試工具Bencher遇到的真實場景，我們一起來學習一下吧。

概述

Bencher接到用戶反饋，其Bencher Perf頁面需要長時間才能加載出來。這對一個做基準測試業務的工具來說是不可接受的。為了復現問題，其工程師選擇通過Rustls Perf頁面來作為測試，其測試范圍最廣，設置也最齊全，包括112個基準測試和最完整整齊的連續基準測試設置。

該項目Perf頁面過去加載大約需要5秒?，F在測試中為38.8秒，確實是延遲的逆天了。

問題來源

Bencher Perf API是目前對性能要求最高的端點之一。面對基準要求越來越嚴苛，尤其是需要連續基準測試對比的項目，現有的基準測試工具工具無法處理所需的高維度。所謂的“高維度”是指可以跨多個維度跟蹤一段時間內的表現：分支、 測試平臺、基準和措施。這種在五個不同維度上進行切片和切塊的能力導致了一個非常復雜的模型。由于這種固有的復雜性和數據的性質， Bencher業務需要使用時間序列數據庫，并選擇使用簡單方便的SQLite。

隨著時間的推移Bencher Perf API的需求越來越多。最初，必須選擇要手動渲染整個頁面，這讓給用戶獲得有用的繪圖帶來了很大的障礙。為了解決這個問題，Perf頁面添加了最新報告的列表，默認情況下，選擇并繪制最新的報告。這樣如果最近的報告中有112個基準，那么所有112個基準都會被繪制出來。由于能夠跟蹤和可視化閾值邊界，該模型也變得更加復雜。

考慮到這一點，Bencher進行了做了一些與性能相關的改進。由于Perf Plot需要最新的報告才能開始繪制，為此改進中重構了報告API，以便通過對數據庫的單次調用來獲取報告的結果數據，而不是進行迭代。默認報告查詢的時間窗口設置為四個星期，而不是無限制。另外極大地限制了所有數據庫句柄的范圍，減少了鎖爭用。為了幫助與用戶溝通添加了狀態欄微調器“性能圖” 和 “維度”選項卡 。

也曾嘗試使用復合查詢將所有Perf結果放入單個查詢中，但失敗了，而不是使用四重嵌套for循環。導致達到了Rust類型系統遞歸限制， 反復溢出堆棧， 經歷瘋狂的（遠超過38秒）編譯時間，最后導致SQLite復合select語句中的最大術語數超出導致問題。

分許出來，問題來源，需要對其進行挖掘，然后進行調優。

從ORM獲得SQL查詢

第一個障礙是從Rust代碼中獲取SQL查詢。由于Bencher使用Diesel作為訪問數據庫的對象關系模型(ORM)。Diesel創建參數化查詢。它將SQL查詢及其綁定參數分別發送到數據庫。也就是說，替換是由數據庫完成的。因此，Diesel無法向用戶提供完整的查詢。

解決這個問題最好的方法是使用輸出參數化查詢的diesel::debug_query函數：

手動清理并將查詢參數化為有效的SQL:

SQLite查詢規劃器

關于SQLite數據的性能調優，可以使用其查詢規劃器（Query Planner）。準確地解釋SQLite如何執行SQL查詢，它會告訴哪些索引有用以及需要注意哪些操作，例如全表掃描。

為了查看查詢規劃器如何執行Perf查詢，需要在工具帶中添加一個新工具：EXPLAIN QUERY PLAN可以為SQL查詢添加前綴EXPLAIN QUERY PLAN或運行.eqp on查詢之前的點命令。

接種有很多過程，但是影響性能的，可能是一下三點：

SQLite動態創建一個物化視圖來掃描整個boundary表；

SQLite掃描整個metric表；

SQLite創建兩個動態索引；

而metric和boundary是系統中最大的兩個表，字段多、數據量最大，這可能是問題所在。

SQLite專家模式

SQLite有一個實驗性質的“專家”模式，可以通過.expert on命令啟動。它會自動對查詢進行給出建議，對上述查詢建議：

三個問題中，除了動態物化視圖，其他兩個問題已經被有優化解決了。

物化視圖

Metric與其對應的Boundary之間存在1對0/1 的關系。也就是說，一個指標可以與零個或一個邊界相關，而一個邊界只能與一個指標相關。所以，可以考慮擴大 metric表包括所有的boundary數據與每個boundary相關字段可為空?；蛘呖梢詣摻ㄒ粋€單獨的boundary表與一個UNIQUE外鍵到metric表。 先然使用，后一個方案更合適，修改、重建metric和boundary表：

然后簡單地添加一個索引boundary(metric_id)，但是問題沒有得到改善。這個可能是Perf查詢源自etric表關系是 0/1 或者換句話說，必須掃描可為null 的關系(O(n))并且無法被搜索（O(log(n))）。

為了解決這個問題，通過手動創建一個物化視圖來扁平化metric和boundary關系使SQLite不必創建動態物化視圖。其SQL語句：

通過這個解決方案，采取用用空間來換取運行時性能的辦法。實際上，盡管此視圖針對的是數據庫中兩個最大的表，空間僅增加了不過4%而已，最重要的是，它讓以在源代碼中魚與熊掌兼得。使用Diesel 創建物化視圖非常簡單。只需使用Diesel在生成正常模式時使用的完全相同的宏。

結果

添加三個新索引和一個物化視圖后，查詢計劃器：

完美，所有都解決了，所有查詢均走了索引。

更新部署后系統頁面實際加載結果也更加完美，從兩分鐘到只需要

總結

該實例中，通過問題排查，復現、查詢調試、使用專家模式優化、改變表結構，到最后手動創建無話視圖用空間換性能。最后將性能完美提高了1200倍，這個過程思路明確，一氣呵成，可以作為此類問題解決的一個完美模板。

當然最主要該案例涉及的項目是專業做性能的持續基準測試，除了學習排障思路和方法外，這個開源工具Bencher也值得大家引入和使用，對持續跟蹤項目性能問題大有裨益。