踩坑（Running）填坑（ZSSURE）：SQLite資料庫操作效率分析

標籤：資料庫   sqlite   事務   like   

背景：

之前對於資料庫操作部分大多停留在會使用階段，並未真正考慮過資料庫SQL語句的執行效率問題。近期響應項目組其他成員需要，協助手動修改資料庫。為了提高工作效率編寫了C#測試工程進行相關操作。由於資料庫記錄量較大（超過10K條），在編寫程式過程中發現不同的操作執行效率相差甚遠，特做記錄如下，待後續有時間再深入分析各種資料庫的SQL語句的執行效率。
此次資料庫修改要求涉及到刪除、修改、添加等各種操作，為了簡單對比各種方式下程式的執行效率，只選擇“刪除”操作進行分析。詳情如下：

for迴圈逐條刪除：

起初最直接的思路是通過for迴圈逐個刪除合格記錄，代碼如下所示:

執行效果如下:

【知識點補充】：在使用迴圈操作過程中，可以看到在資料庫檔案目錄下產生了XXX.db3-journal的臨時檔案，且大小隨時在變動。（關於journal檔案的描述參見博文）journal是SQLite用於復原操作的暫存文檔，當資料庫寫入失敗時，通過journal文檔可以複原到未更改之前的資料，通過觀察本機目錄下的journal檔案發現，其大小在隨時變動因此可以猜測，代碼中迴圈的每一步都在進行資料庫開啟、寫入操作，因此導致耗時超出容忍極限。

使用事務（Transaction）：

SQLite預設為每個操作啟動一個事務，也就是上述迴圈代碼中每一次DELETE以及每一次SELECT都啟動了一個事務，如是“事務開啟+SQL執行+事務關閉”自然耗費了大量的時間。而手動顯式調用SQLiteTransaction將迴圈中的所有SQL操作放到同一個事務中，此時只需要一次事務開啟和關閉的時間即可。（參考博文）
另外SQLite的資料庫本質上就是一個磁碟上的檔案，所以一切的資料庫操作都會轉化為對檔案的操作，而頻繁的檔案操作會是一個耗時的I/O過程，極大的影響資料庫的存取速度。
顯式調用Transaction的代碼如下：

執行效果為：

由此可見，效率較之前“暴力”方式有了明顯數量級的提升，而且在程式執行過程中看不到XXX.db3-journal臨時檔案的產生。

使用LIKE查詢+組合SQL：

再分析一下上述操作流程可以發現，之前的操作是分兩部分完成的：第一步，SELECT查詢滿足要求的待刪除資料；第二，使用for迴圈逐條DELETE之前SELECT的結果。這兩次操作之間既開闢了多餘的List變數，又重複操作了SQLite資料庫檔案，因此執行效率還有待提高。
這裡用LIKE進行條件查詢，然後使用組合SQL語句直接刪除滿足條件的結果，具體代碼如下：

執行效果如下：

由此可見在使用事務Transaction的基礎上，通過改進程式整體流程又一次減少了資料庫開啟、關閉的次數，效率有了進一步的提升。對比上述三種操作效率，結果如下：

總結：

隨著ORM技術的出現，對於資料庫的操作越來越簡單，之前也介紹過使用Telerik OpenAccess來進行資料庫相關操作。正是由於諸多新技術的出現使得在項目實際過程中越來越不注重最底層的、最基本的執行效率，對於單條SQL語句、單次操作往往不做任何考慮，直接使用最原始的“野蠻”方式。另外由於之前項目大多是單機版，不會出現WEB應用中海量使用者多並發，因此對於資料庫的執行效率感受就不夠深刻。隨著雲端運算、大資料、分布式的逐漸普及，資料庫技術（尤其是執行效率）會越發重要，【切記】後續在此方面要多用心。




[email protected]
時間：2015/08/16

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

踩坑（Running）填坑（ZSSURE）：SQLite資料庫操作效率分析