提高SQLite每秒INSERT的性能？

優化SQLite是棘手的。 C應用程式的大容量插入性能可以從每秒85次插入到每秒超過96,000次插入！

背景：我們将SQLite用作桌面應用程式的一部分。我們将大量配置資料存儲在XML檔案中，這些檔案将被解析并加載到SQLite資料庫中，以便在初始化應用程式時進行進一步處理。 SQLite非常适合這種情況，因為它速度快，不需要專門的配置，并且資料庫作為單個檔案存儲在磁盤上。

基本原理：最初，我對看到的性能感到失望。事實證明，取決于資料庫的配置方式和使用API的方式，SQLite的性能可能會發生很大的變化（批量插入和選擇）。弄清楚所有選項和技術是什麼都不是一件容易的事，是以，我認為建立此社群Wiki條目與Stack Overflow讀者共享結果以節省其他人的麻煩是審慎的做法。

實驗：我認為，最好是編寫一些C代碼并實際衡量各種選擇的影響，而不是簡單地談論一般意義上的性能提示（即“使用事務！” ）。我們将從一些簡單的資料開始：

28 MB TAB分隔的文本檔案（約865,000條記錄），用于多倫多市的完整運輸時間表
我的測試計算機是運作Windows XP的3.60 GHz P4。
使用Visual C ++ 2005将代碼編譯為帶有“完整優化”（/ Ox）和“快速收藏”代碼（/ Ot）的“發行版”。
我正在使用直接編譯到測試應用程式中的SQLite“合并”。我剛好擁有的SQLite版本（3.6.7）有點舊，但是我懷疑這些結果将與最新版本相當（如果您另有意見，請發表評論）。

讓我們寫一些代碼！

代碼：一個簡單的C程式，它逐行讀取文本檔案，将字元串拆分為值，然後将資料插入SQLite資料庫。在此“基準”版本的代碼中，建立了資料庫，但實際上不會插入資料：

/*************************************************************
    Baseline code to experiment with SQLite performance.

    Input data is a 28 MB TAB-delimited text file of the
    complete Toronto Transit System schedule/route info
    from http://www.toronto.ca/open/datasets/ttc-routes/

**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"

#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

    sqlite3 * db;
    sqlite3_stmt * stmt;
    char * sErrMsg = 0;
    char * tail = 0;
    int nRetCode;
    int n = 0;

    clock_t cStartClock;

    FILE * pFile;
    char sInputBuf [BUFFER_SIZE] = "\0";

    char * sRT = 0;  /* Route */
    char * sBR = 0;  /* Branch */
    char * sVR = 0;  /* Version */
    char * sST = 0;  /* Stop Number */
    char * sVI = 0;  /* Vehicle */
    char * sDT = 0;  /* Date */
    char * sTM = 0;  /* Time */

    char sSQL [BUFFER_SIZE] = "\0";

    /*********************************************/
    /* Open the Database and create the Schema */
    sqlite3_open(DATABASE, &db);
    sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

    /*********************************************/
    /* Open input file and import into Database*/
    cStartClock = clock();

    pFile = fopen (INPUTDATA,"r");
    while (!feof(pFile)) {

        fgets (sInputBuf, BUFFER_SIZE, pFile);

        sRT = strtok (sInputBuf, "\t");     /* Get Route */
        sBR = strtok (NULL, "\t");            /* Get Branch */
        sVR = strtok (NULL, "\t");            /* Get Version */
        sST = strtok (NULL, "\t");            /* Get Stop Number */
        sVI = strtok (NULL, "\t");            /* Get Vehicle */
        sDT = strtok (NULL, "\t");            /* Get Date */
        sTM = strtok (NULL, "\t");            /* Get Time */

        /* ACTUAL INSERT WILL GO HERE */

        n++;
    }
    fclose (pFile);

    printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

“控制”

按原樣運作代碼實際上不會執行任何資料庫操作，但是它将使我們了解原始C檔案I / O和字元串處理操作的速度。

在0.94秒内導入864913記錄

大！隻要我們實際上不執行任何插入操作，我們就可以每秒執行920,000次插入操作：-)

“最壞情況”

我們将使用從檔案中讀取的值來生成SQL字元串，并使用sqlite3_exec調用該SQL操作：

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);

這将很慢，因為對于每個插入，SQL都将被編譯成VDBE代碼，并且每個插入将在其自己的事務中發生。有多慢

在9933.61秒内導入了864913條記錄

kes！ 2小時45分鐘！每秒隻有85次插入。

使用交易

預設情況下，SQLite将評估唯一事務中的每個INSERT / UPDATE語句。如果執行大量插入操作，建議将操作包裝在事務中：

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    ...

}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

在38.03秒内導入864913記錄

這樣更好隻需将所有插入物包裝在一個事務中，就可以将我們的性能提高到每秒23,000個插入物。

使用準備好的語句

使用事務是一個巨大的改進，但是如果我們反複使用相同的SQL，則對于每個插入都重新編譯SQL語句是沒有意義的。讓我們使用

sqlite3_prepare_v2

一次編譯我們的SQL語句，然後使用

sqlite3_bind_text

将參數綁定到該語句：

/* Open input file and import into the database */
cStartClock = clock();

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sRT = strtok (sInputBuf, "\t");   /* Get Route */
    sBR = strtok (NULL, "\t");        /* Get Branch */
    sVR = strtok (NULL, "\t");        /* Get Version */
    sST = strtok (NULL, "\t");        /* Get Stop Number */
    sVI = strtok (NULL, "\t");        /* Get Vehicle */
    sDT = strtok (NULL, "\t");        /* Get Date */
    sTM = strtok (NULL, "\t");        /* Get Time */

    sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);

    sqlite3_step(stmt);

    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    n++;
}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

在16.27秒内導入864913記錄

真好！還有更多代碼（不要忘記調用

sqlite3_clear_bindings

和

sqlite3_reset

），但是我們的性能提高了一倍以上，達到每秒53,000次插入。

PRAGMA同步= OFF

預設情況下，SQLite将在發出OS級寫指令後暫停。這樣可以確定将資料寫入磁盤。通過設定

synchronous = OFF

，我們訓示SQLite隻需将資料移交給OS進行寫入，然後繼續。如果計算機在将資料寫入磁盤之前遭受災難性崩潰（或電源故障），則資料庫檔案可能會損壞：

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

在12.41秒内導入864913記錄

現在，改進的幅度較小，但每秒最多可插入69,600次。

PRAGMA journal_mode =記憶體

考慮通過評估

PRAGMA journal_mode = MEMORY

将復原日志存儲在記憶體中。您的事務将更快，但是如果在事務期間斷電或程式崩潰，則資料庫可能會因部分完成的事務而處于損壞狀态：

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

在13.50秒内導入864913記錄

每秒64,000次插入的速度比之前的優化稍慢。

PRAGMA同步= OFF 和 PRAGMA journal_mode = MEMORY

讓我們結合前面的兩個優化。風險更高一些（如果發生崩潰），但是我們隻是在導入資料（不運作銀行）：

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

在12.00秒内導入864913記錄

太棒了！我們每秒能夠完成72,000次插入。

使用記憶體資料庫

隻是為了振奮人心，讓我們以所有先前的優化為基礎，并重新定義資料庫檔案名，以便我們完全在RAM中工作：

#define DATABASE ":memory:"

在10.94秒内導入864913記錄

将我們的資料庫存儲在RAM中并不是很實際，但是令人印象深刻的是我們每秒可以執行79,000次插入。

重構C代碼

盡管沒有特别改進SQLite，但我不喜歡

while

循環中額外的

char*

指派操作。讓我們快速重構該代碼，将

strtok()

的輸出直接傳遞到

sqlite3_bind_text()

，然後讓編譯器嘗試為我們加快速度：

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Time */

    sqlite3_step(stmt);        /* Execute the SQL Statement */
    sqlite3_clear_bindings(stmt);    /* Clear bindings */
    sqlite3_reset(stmt);        /* Reset VDBE */

    n++;
}
fclose (pFile);

注意：我們将回到使用真實的資料庫檔案。記憶體資料庫速度很快，但不一定實用

在8.94秒内導入864913記錄

稍微重構參數綁定中使用的字元串處理代碼，可以使我們每秒執行96,700次插入。我認為可以肯定地說這非常快。随着我們開始調整其他變量（例如頁面大小，索引建立等），這将成為我們的基準。

摘要（到目前為止）

我希望你仍然和我在一起！我們選擇這條路的原因是，使用SQLite進行大容量插入的性能變化如此之大，并不一定總是需要進行哪些更改以加快操作速度。使用相同的編譯器（和編譯器選項），相同版本的SQLite和相同資料，我們優化了代碼，并優化了SQLite的使用，使其從最壞的情況下每秒85次插入變為每秒超過96,000次插入！

先建立索引，然後插入VS.插入，然後建立索引

在開始評估

SELECT

性能之前，我們知道我們将建立索引。在下面的答案之一中，建議進行批量插入時，插入資料後建立索引的速度更快（與先建立索引然後插入資料相反）。我們試試吧：

建立索引然後插入資料

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

在18.13秒内導入864913記錄

插入資料然後建立索引

...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

在13.66秒内導入864913記錄

不出所料，如果對一列進行索引，則大容量插入會較慢，但是如果在插入資料後建立索引，則确實會有所不同。我們的無索引基準是每秒96,000次插入。首先建立索引，然後插入資料，每秒可提供47,700次插入，而先建立資料，然後建立索引，則每秒可提供63,300次插入。

我很樂意為其他情況提供建議以嘗試...，并将很快為SELECT查詢編譯類似的資料。

#1樓

避免使用

sqlite3_clear_bindings(stmt)

。

測試中的代碼每次設定綁定就足夠了。

SQLite文檔中的C API簡介說：

在第一次調用sqlite3_step（）之前或在sqlite3_reset（）之後立即調用該應用程式，應用程式可以調用sqlite3_bind（）接口将值附加到參數。每次調用sqlite3_bind（）都會覆寫先前對同一參數的綁定

在

sqlite3_clear_bindings

的文檔中，沒有任何内容表明您必須調用它，而不僅僅是設定綁定。

詳細資訊：避免使用_sqlite3_clear_bindings（）

#2樓

如果可以對INSERT / UPDATE語句進行分塊，則批量導入似乎表現最佳。在隻有幾行的表YMMV上，值10,000左右對我來說效果很好。

#3樓

幾個技巧：

将插入/更新放入事務中。
對于較舊版本的SQLite-考慮較少的偏執日志模式（ pragma journal_mode ）。有 NORMAL ，然後有 OFF ，如果您不太擔心資料庫可能因作業系統崩潰而損壞，則可以顯着提高插入速度。如果您的應用程式崩潰了，資料應該沒問題。請注意，在較新的版本中， OFF/MEMORY 設定對于應用程式級崩潰不安全。
使用頁面大小也會有所不同（ PRAGMA page_size ）。由于較大的頁面保留在記憶體中，是以具有較大的頁面大小可以使讀取和寫入的速度更快。請注意，更多的記憶體将用于您的資料庫。
如果有索引，請在完成所有插入操作後考慮調用 CREATE INDEX 。這比建立索引然後進行插入要快得多。
如果您可以并發通路SQLite，則必須非常小心，因為寫入完成後整個資料庫将被鎖定，盡管可能有多個讀取器，但寫入将被鎖定。通過在較新的SQLite版本中添加WAL，已對此進行了一些改進。
利用節省空間的優勢...較小的資料庫運作更快。例如，如果您具有鍵值對，請嘗試盡可能使鍵成為 INTEGER PRIMARY KEY ，它将替換表中隐含的唯一行号列。
如果使用多個線程，則可以嘗試使用共享頁面緩存，這将允許線上程之間共享已加載的頁面，進而避免了昂貴的I / O調用。
不要使用 !feof(file) ！

我也在這裡和這裡問過類似的問題。

#4樓

如果僅關心讀取，則從多個線程的多個連接配接（每個線程的連接配接）中讀取速度會稍快一些（但可能會讀取過時的資料）。

首先在表中找到項目：

SELECT COUNT(*) FROM table

然後讀入頁面（LIMIT / OFFSET）：

SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

每個線程在哪裡和計算，如下所示：

int limit = (count + n_threads - 1)/n_threads;

對于每個線程：

int offset = thread_index * limit

對于我們的小資料庫（200mb），這可以提高50-75％的速度（在Windows 7上為3.8.0.2 64位）。我們的表是高度非規範化的（1000-1500列，大約100,000或更多行）。

太多或太少的線程将無法執行此操作，您需要對自己進行基準測試和分析。

同樣對我們來說，SHAREDCACHE降低了性能，是以我手動設定了PRIVATECACHE（因為它是為我們全局啟用的）

#5樓

對于這些插入，請嘗試使用

SQLITE_STATIC

而不是

SQLITE_TRANSIENT

。

SQLITE_TRANSIENT

将導緻SQLite在傳回之前複制字元串資料。

SQLITE_STATIC

告訴您，您給它提供的記憶體位址在執行查詢之前将一直有效（在此循環中始終如此）。這将為您節省每個循環幾個配置設定，複制和取消配置設定操作。可能會有很大的改善。

#6樓

在将cache_size提高到更高的值之前，我不會從交易中獲得任何收益，即

PRAGMA cache_size=10000;

#7樓

閱讀本教程後，我嘗試将其實施到我的程式中。

我有4-5個包含位址的檔案。每個檔案有大約3000萬條記錄。我使用與您建議的配置相同的配置，但是每秒的INSERT數量非常低（每秒約10.000條記錄）。

這是您的建議失敗的地方。您對所有記錄使用一個事務，并使用一個插入而沒有錯誤/失敗。假設您将每個記錄拆分為不同表上的多個插入。如果唱片被打破了怎麼辦？

ON CONFLICT指令不适用，因為如果一條記錄中有10個元素，并且您需要将每個元素插入到不同的表中，如果元素5出現CONSTRAINT錯誤，那麼前面的所有4個插入也都需要執行。

是以這就是復原的地方。復原的唯一問題是您丢失了所有插入内容并從頂部開始。你怎麼解決呢？

我的解決方案是使用多個事務。我每10.000條記錄開始和結束一筆交易（不要問為什麼這個數字，這是我測試過的最快的）。我建立了一個大小為10.000的數組，并在其中插入成功的記錄。當發生錯誤時，我進行復原，開始事務，從我的數組中插入記錄，送出，然後在損壞的記錄之後開始新的事務。

該解決方案幫助我繞開了處理包含不良/重複記錄（我的不良記錄幾乎占4％）的檔案時遇到的問題。

我建立的算法幫助我将流程減少了2個小時。檔案的最終加載過程1hr 30m仍然很慢，但與最初花費的4hrs相比沒有。我設法将插入速度從10.000 / s加快到〜14.000 / s

如果有人對如何加快速度有其他想法，我歡迎您提出建議。

更新：

除了上面我的回答，您還應該記住每秒插入數也取決于您使用的硬碟驅動器。我在具有不同硬碟的3台不同PC上進行了測試，但時間差異很大。 PC1（1hr 30m），PC2（6hrs）PC3（14hrs），是以我開始懷疑為什麼會這樣。

經過兩周的研究并檢查了多種資源：硬碟，Ram，緩存，我發現硬碟上的某些設定會影響I / O速率。通過單擊所需輸出驅動器上的屬性，可以在正常頁籤中看到兩個選項。 Opt1：壓縮該驅動器，Opt2：允許對該驅動器的檔案進行索引。

通過禁用這兩個選項，現在所有3台PC大約需要花費相同的時間（1小時和20至40分鐘）完成。如果遇到插入緩慢的情況，請檢查硬碟驅動器是否配置了這些選項。這将為您節省大量時間和尋找解決方案的麻煩

#8樓

您的問題的答案是，較新的sqlite3可以提高性能，請使用該功能。

這個答案為什麼SQLAlchemy用sqlite插入比直接使用sqlite3慢25倍？作者：SqlAlchemy Orm作者在0.5秒内插入了10萬次插入，我在python-sqlite和SqlAlchemy中也看到了類似的結果。這使我相信sqlite3的性能有所提高

#9樓

使用ContentProvider在db中插入批量資料。以下用于将批量資料插入資料庫的方法。這應該提高SQLite的每秒INSERT性能。

private SQLiteDatabase database;
database = dbHelper.getWritableDatabase();

public int bulkInsert(@NonNull Uri uri, @NonNull ContentValues[] values) {

database.beginTransaction();

for (ContentValues value : values)
 db.insert("TABLE_NAME", null, value);

database.setTransactionSuccessful();
database.endTransaction();

}

調用bulkInsert方法：

App.getAppContext().getContentResolver().bulkInsert(contentUriTable,
            contentValuesArray);

連結： https ： //www.vogella.com/tutorials/AndroidSQLite/article.html檢查“使用ContentProvider”部分以了解更多詳細資訊

#10樓

在散裝刀片上

受到這篇文章以及導緻我在此處出現的Stack Overflow問題的啟發- 是否可以一次在SQLite資料庫中插入多行？ -我釋出了我的第一個Git存儲庫：

https://github.com/rdpoor/CreateOrUpdate

它将大量ActiveRecords加載到MySQL ，SQLite或PostgreSQL資料庫中。它包括一個忽略現有記錄，覆寫它們或引發錯誤的選項。我的基本基準顯示，與順序寫入YMMV相比，速度提高了10倍。

我在經常需要導入大型資料集的生産代碼中使用它，對此我感到非常滿意。

提高SQLite每秒INSERT的性能？

“控制”

“最壞情況”

使用交易

使用準備好的語句

PRAGMA同步= OFF

PRAGMA journal_mode =記憶體

PRAGMA同步= OFF 和 PRAGMA journal_mode = MEMORY

使用記憶體資料庫

重構C代碼

摘要（到目前為止）

先建立索引，然後插入VS.插入，然後建立索引

#1樓

#2樓

#3樓

#4樓

#5樓

#6樓

#7樓

#8樓

#9樓

#10樓

在散裝刀片上

繼續閱讀

恢複Linux作業系統的GRUB引導程式

緻意—程式員

dos 指令集2---DOS 常用指令 (edit)

dos 指令集2---DOS 常用指令 (sys)

C/C++頭檔案、函數使用說明

SOFTICE 使用說明 (斷點)

DOS指令(2) 磁盤操作類指令

在DOS下運作不了ipconfig指令

c寫檔案

對于0-1分數規劃的Dinkelbach算法的分析

不用iconv函數實作UTF-8編碼轉換GB2312的PHP函數

浮點數計算精度控制

C++ 第十五周報告1--《冒泡法排序》

android SQLite事務和kotlin協程

[轉]九大排序算法——C語言實作及詳解

QR碼編碼原理三（日本漢字和中文編碼）