SQlite高效存儲技術

07-09

SQlite高效存儲技術

來自專欄未來機器

背景

有時需要將大量信息快速寫入資料庫中。 SQLite是一個輕量級資料庫引擎，可以輕鬆嵌入到應用程序中。這篇文章涵蓋了SQLite的批量插入的優化過程。雖然下面重點是介紹SQLite 批量插入的幾種優化方法，但是其中一些方法同樣適用其他類型資料庫。第三部分簡單介紹了建表和查詢語句的注意事項。以下所有示例都將數據插入到同一個表中。這張表有7個欄位，其中ID是第一個元素，後面跟著三個FLOAT類型的欄位，最後是三個INTEGER類型的欄位。

插入方式的優化

逐條插入

這是將記錄插入SQLite的最基本的方法。每個記錄的查詢調用一次sqlite3_exec。

示例代碼：

char buffer[300];for (unsigned i = 0; i < mVal; i++){ sprintf(buffer, "INSERT INTO example VALUES (%s, %lf, %lf, %lf, %d, %d, %d)", getID().c_str(), getDouble(), getDouble(), getDouble(), getInt(), getInt(), getInt()); sqlite3_exec(mDb, buffer, NULL, NULL, NULL);}

合併 Transaction

事務是將SQL語句組合在一起的一種方式。如果遇到錯誤，可以使用ON CONFLICT語句來定義錯誤處理。用END或者COMMIT來關閉和寫入事務，然後將內容寫入SQLite資料庫。實例代碼:

char* errorMessage;sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);char buffer[300];for (unsigned i = 0; i < mVal; i++){ sprintf(buffer, "INSERT INTO example VALUES (%s, %lf, %lf, %lf, %d, %d, %d)", getID().c_str(), getDouble(), getDouble(), getDouble(), getInt(), getInt(), getInt()); sqlite3_exec(mDb, buffer, NULL, NULL, NULL);}sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);

PRAGMA 語句

PRAGMA語句控制整個SQLite的行為。它們可以用來設置Sqlite的選項，例如將數據刷新到磁碟的頻率。以下是關閉同步機制提升寫入性能的一些設置。具體可以參考一下SQLITE的官方文檔。但是同步機制關閉會導致SQLite停止並等待數據寫入硬碟。在崩潰或電源故障發生時，資料庫可能會損壞。實例代碼:

sqlite3_exec(mDb, "PRAGMA synchronous=OFF", NULL, NULL, &errorMessage);sqlite3_exec(mDb, "PRAGMA count_changes=OFF", NULL, NULL, &errorMessage);sqlite3_exec(mDb, "PRAGMA journal_mode=MEMORY", NULL, NULL, &errorMessage);sqlite3_exec(mDb, "PRAGMA temp_store=MEMORY", NULL, NULL, &errorMessage);

預解析 Statement

預解析 Statement 是一種高效的查詢方式。解析器只需要在批量查詢語句上運行一次，而不是一遍又一遍地解析語句。根據文檔，sqlite3_exec是一個便捷函數，裡面調用了sqlite3_prepare_v2（），sqlite3_step（），然後調用sqlite3_finalize（）。個人認為文檔應該明確地指出預解析的查詢方式是首選的查詢方法。 sqlite3_exec（）只適合用於一次性的查詢。

示例代碼：

char* errorMessage;sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage); char buffer[] = "INSERT INTO example VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";sqlite3_stmt* stmt;sqlite3_prepare_v2(mDb, buffer, strlen(buffer), &stmt, NULL); for (unsigned i = 0; i < mVal; i++){ std::string id = getID(); sqlite3_bind_text(stmt, 1, id.c_str(), id.size(), SQLITE_STATIC); sqlite3_bind_double(stmt, 2, getDouble()); sqlite3_bind_double(stmt, 3, getDouble()); sqlite3_bind_double(stmt, 4, getDouble()); sqlite3_bind_int(stmt, 5, getInt()); sqlite3_bind_int(stmt, 6, getInt()); sqlite3_bind_int(stmt, 7, getInt()); if (sqlite3_step(stmt) != SQLITE_DONE) { printf("Commit Failed! "); } sqlite3_reset(stmt);} sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);sqlite3_finalize(stmt);

數據用Binary Blob類型存儲

到目前為止，上面的優化已經包含了批量查詢的最常用的優化方法。但是在另一個方面，如果你沒有對某些數據進行查詢的需求，可以將其存儲為一個BLOB。雖然不建議將所有內容都放到一個blob中存儲到資料庫，但某些情況下這麼做是有意義的。例如，如果你有一個由REAL類型的數值組成的點類（x，y，z），那麼可以將它們存儲在一個BLOB中而不是三個單獨的存放。隨著更多欄位被轉換為更大的blob，這種方法的性能就越能提升。示例代碼：

char* errorMessage;sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage); char buffer[] = "INSERT INTO example VALUES (?1, ?2, ?3, ?4, ?5)";sqlite3_stmt* stmt;sqlite3_prepare_v2(mDb, buffer, strlen(buffer), &stmt, NULL); for (unsigned i = 0; i < mVal; i++){ std::string id = getID(); sqlite3_bind_text(stmt, 1, id.c_str(), id.size(), SQLITE_STATIC); char dblBuffer[24]; double d[] = {getDouble(), getDouble(), getDouble()}; memcpy(dblBuffer, (char*)&d, sizeof(d)); sqlite3_bind_blob(stmt, 2, dblBuffer, 24, SQLITE_STATIC); sqlite3_bind_int(stmt, 3, getInt()); sqlite3_bind_int(stmt, 4, getInt()); sqlite3_bind_int(stmt, 5, getInt()); int retVal = sqlite3_step(stmt); if (retVal != SQLITE_DONE) { printf("Commit Failed! %d ", retVal); } sqlite3_reset(stmt);} sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);sqlite3_finalize(stmt);

性能對比圖

各方法不同數量的插入動作的運行時間

各方法不同數量的插入的每秒插入記錄數

BLOB&Stetement不同數量的插入動作的運行時間

BLOB&Stetement不同數量的插入的每秒插入記錄數

插入方法總結

最原始的逐條插入性能最差，永遠都不是批量寫入的最佳選擇。
Transaction 合併能帶來一些性能提升，在大量的並且查詢&寫入方式不同的情景下，它是一個不錯的選擇。如果查詢&寫入方式相同，這種方式還有很大的提升空間。
預解析 Statement，在查詢&寫入方式相同的大量操作下，性能是最好的。
對於不會被用來查詢並且邏輯上適合放一起的表欄位以BLOB形式存放在資料庫，還能帶來一些性能提升。
PRAGMA 選項設置來提升寫入效率確實有效，但在有些異常情況下, 資料庫可能會損壞。

建表與查詢語句

建表之前明確好存儲欄位，找到真正的 Primary Key

反例：CREATE TABLE t1(id UNIQUE Primary Key, fileId UNIQUE, Number);插入數據到表中："insert or replace into table (id, fileId, Number) (select id from table where fileId = 1?), ?2, ?3)id 在這是多餘的，並且大幅增加了查詢和寫入語句的演算法複雜，每條插入語句都要進行一次基於fileId的查詢，這個查詢的複雜度隨著表的大小不斷增長。

修改之後

CREATE TABLE t1(fileId Primary Key UNIQUE, Number);插入數據到表中:insert or replace into table (fileId, Number) (1?, 2?)

優化之後的結果：

1000K個記錄的插入過程只持續幾秒，CPU單核佔用3%以內。