C语言 提高 SQLite 插入性能的方法

1、使用事务

默认情况下，SQLite 每次插入都会启动一个事务并立即提交，这样会导致性能低下。在一个事务中批量插入数据可以显著提高性能。

#include <stdio.h>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    char *errMsg = NULL;
    int rc;

    // 打开数据库
    rc = sqlite3_open("example.db", &db);
    if (rc) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    printf("数据库打开成功。\n");

    // 创建表
    const char *createTableSQL = 
        "CREATE TABLE IF NOT EXISTS test_table ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "name TEXT NOT NULL);";
    rc = sqlite3_exec(db, createTableSQL, NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建表失败: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }
    printf("表创建成功。\n");

    // 开始事务
    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法开始事务: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }
    printf("事务开始。\n");

    // 插入多条记录
    const char *insertSQL = "INSERT INTO test_table (name) VALUES (?);";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, insertSQL, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL 预编译失败: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    for (int i = 0; i < 10; i++) {
        char name[20];
        sprintf(name, "Name%d", i + 1);

        sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);

        rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "插入数据失败: %s\n", sqlite3_errmsg(db));
            sqlite3_finalize(stmt);
            sqlite3_close(db);
            return 1;
        }

        sqlite3_reset(stmt);  // 重置语句以便重新绑定数据
    }

    sqlite3_finalize(stmt); // 释放语句资源

    // 提交事务
    rc = sqlite3_exec(db, "COMMIT;", NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "提交事务失败: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }
    printf("事务提交成功。\n");

    // 关闭数据库
    sqlite3_close(db);
    printf("数据库关闭成功。\n");

    return 0;
}

2、使用预编译语句

sqlite3_prepare_v2() 和 sqlite3_bind_*() 可避免反复解析 SQL，提高性能。

#include <stdio.h>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    int rc;

    // 打开数据库
    rc = sqlite3_open("example.db", &db);
    if (rc) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    printf("数据库打开成功。\n");

    // 创建表
    const char *createTableSQL =
        "CREATE TABLE IF NOT EXISTS my_table ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "col1 INTEGER, "
        "col2 TEXT);";
    rc = sqlite3_exec(db, createTableSQL, NULL, NULL, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建表失败: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    printf("表创建成功。\n");

    // SQL 预编译
    const char *sql = "INSERT INTO my_table (col1, col2) VALUES (?, ?);";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL 预编译失败: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    printf("SQL 预编译成功。\n");

    // 插入数据
    for (int i = 0; i < 1000; i++) {
        sqlite3_bind_int(stmt, 1, i);                 // 绑定整数值到第一个占位符
        sqlite3_bind_text(stmt, 2, "value", -1, SQLITE_STATIC); // 绑定文本到第二个占位符

        rc = sqlite3_step(stmt); // 执行插入
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "插入数据失败: %s\n", sqlite3_errmsg(db));
            sqlite3_finalize(stmt);
            sqlite3_close(db);
            return 1;
        }

        sqlite3_reset(stmt); // 重置语句以便重用
    }

    sqlite3_finalize(stmt); // 释放语句资源
    printf("批量插入完成。\n");

    // 关闭数据库
    sqlite3_close(db);
    printf("数据库关闭成功。\n");

    return 0;
}

3、关闭自动提交

SQLite 默认会为每个语句自动启动和提交一个事务，这种行为被称为“自动提交模式”。使用 BEGIN TRANSACTION; 明确开启一个事务后，SQLite 会关闭自动提交，直到显式调用 COMMIT; 或 ROLLBACK;。可以减少磁盘 I/O，提高插入性能。如果某些操作失败，可以使用 ROLLBACK; 回滚，避免部分数据插入的情况。

#include <stdio.h>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    char *err_msg = NULL;

    // 打开数据库
    if (sqlite3_open("test.db", &db) != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    // 关闭自动提交（通过显式管理事务）
    if (sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &err_msg) != SQLITE_OK) {
        fprintf(stderr, "Failed to begin transaction: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }

    // 插入数据
    for (int i = 0; i < 10; i++) {
        char sql[256];
        snprintf(sql, sizeof(sql), "INSERT INTO test_table (id, name) VALUES (%d, 'Name%d');", i, i);

        if (sqlite3_exec(db, sql, NULL, NULL, &err_msg) != SQLITE_OK) {
            fprintf(stderr, "SQL error: %s\n", err_msg);
            sqlite3_free(err_msg);

            // 如果发生错误，可以回滚事务
            sqlite3_exec(db, "ROLLBACK;", NULL, NULL, NULL);
            sqlite3_close(db);
            return 1;
        }
    }

    // 提交事务
    if (sqlite3_exec(db, "COMMIT;", NULL, NULL, &err_msg) != SQLITE_OK) {
        fprintf(stderr, "Failed to commit transaction: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }

    printf("Data inserted successfully.\n");

    // 关闭数据库
    sqlite3_close(db);

    return 0;
}

4、使用内存模式

使用 PRAGMA journal_mode = MEMORY 将日志存储在内存中，而不是文件中。对于临时表，甚至可以使用内存数据库：sqlite3_open(":memory:", &db);

#include <stdio.h>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    char *errMsg = NULL;

    // 打开数据库
    if (sqlite3_open("test.db", &db) != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    // 设置 journal_mode 为 MEMORY 模式
    if (sqlite3_exec(db, "PRAGMA journal_mode = MEMORY;", NULL, NULL, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "Failed to set journal_mode: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    } else {
        printf("Journal mode set to MEMORY successfully.\n");
    }

    // 创建一个表（如果尚未存在）
    const char *create_table_sql = 
        "CREATE TABLE IF NOT EXISTS test_table (id INTEGER PRIMARY KEY, name TEXT);";
    if (sqlite3_exec(db, create_table_sql, NULL, NULL, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "Failed to create table: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }

    // 插入数据
    const char *insert_sql = "INSERT INTO test_table (id, name) VALUES (1, 'Alice');";
    if (sqlite3_exec(db, insert_sql, NULL, NULL, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "Failed to insert data: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    } else {
        printf("Data inserted successfully.\n");
    }

    // 关闭数据库
    sqlite3_close(db);
    return 0;
}

5、批量插入

将多个插入合并为一个 INSERT 语句。

#include <stdio.h>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    char *errMsg = NULL;

    // 打开数据库
    if (sqlite3_open("test.db", &db) != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    // 创建测试表（如果不存在）
    const char *create_table_sql = 
        "CREATE TABLE IF NOT EXISTS my_table ("
        "col1 INTEGER, "
        "col2 TEXT);";

    if (sqlite3_exec(db, create_table_sql, NULL, NULL, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "Failed to create table: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }

    // 合并多个插入语句为一个 INSERT
    const char *sql = 
        "INSERT INTO my_table (col1, col2) VALUES "
        "(1, 'val1'), "
        "(2, 'val2'), "
        "(3, 'val3');";

    // 执行插入语句
    if (sqlite3_exec(db, sql, NULL, NULL, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "Failed to insert data: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return 1;
    }

    printf("Data inserted successfully.\n");

    // 关闭数据库
    sqlite3_close(db);

    return 0;
}

6、优化索引

插入数据前删除索引，插入后再创建索引，这样可以避免在插入过程中频繁更新索引。使用 PRAGMA temp_store = MEMORY; 将临时数据存储到内存中。

#include <stdio.h>
#include <sqlite3.h>

void check_sqlite_error(int rc, sqlite3 *db) {
    if (rc != SQLITE_OK) {
        printf("SQLite error: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
}

int main() {
    sqlite3 *db;
    char *err_msg = NULL;
    int rc;

    // 打开数据库
    rc = sqlite3_open("test.db", &db);
    check_sqlite_error(rc, db);

    // 创建表
    const char *create_table_sql = 
        "CREATE TABLE IF NOT EXISTS data ("
        "id INTEGER PRIMARY KEY, "
        "name TEXT, "
        "value INTEGER);";
    rc = sqlite3_exec(db, create_table_sql, NULL, NULL, &err_msg);
    check_sqlite_error(rc, db);

    // 创建索引
    const char *create_index_sql = 
        "CREATE INDEX IF NOT EXISTS idx_value ON data(value);";
    rc = sqlite3_exec(db, create_index_sql, NULL, NULL, &err_msg);
    check_sqlite_error(rc, db);

    // 删除索引以加快插入速度
    const char *drop_index_sql = "DROP INDEX IF EXISTS idx_value;";
    rc = sqlite3_exec(db, drop_index_sql, NULL, NULL, &err_msg);
    check_sqlite_error(rc, db);

    // 开始事务
    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &err_msg);
    check_sqlite_error(rc, db);

    // 插入大量数据
    sqlite3_stmt *stmt;
    const char *insert_sql = "INSERT INTO data (name, value) VALUES (?, ?);";
    rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, NULL);
    check_sqlite_error(rc, db);

    for (int i = 0; i < 100000; i++) {
        sqlite3_bind_text(stmt, 1, "test", -1, SQLITE_STATIC); // 绑定 name 字段
        sqlite3_bind_int(stmt, 2, i);                         // 绑定 value 字段

        sqlite3_step(stmt);  // 执行插入
        sqlite3_reset(stmt); // 重置语句以便重复使用
    }

    sqlite3_finalize(stmt); // 释放预编译语句资源

    // 提交事务
    rc = sqlite3_exec(db, "COMMIT;", NULL, NULL, &err_msg);
    check_sqlite_error(rc, db);

    // 重新创建索引
    rc = sqlite3_exec(db, create_index_sql, NULL, NULL, &err_msg);
    check_sqlite_error(rc, db);

    // 关闭数据库
    sqlite3_close(db);
    printf("Data inserted and index optimized successfully.\n");

    return 0;
}

7、减少写操作

如果写操作频繁且不需要立即持久化，可以使用 PRAGMA cache_size 来增加缓存。

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

void check_sqlite_error(int rc, sqlite3 *db) {
    if (rc != SQLITE_OK) {
        printf("SQLite error: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
}

int main() {
    sqlite3 *db;
    char *errMsg = NULL;
    int rc;

    // 打开数据库
    rc = sqlite3_open("test.db", &db);
    check_sqlite_error(rc, db);

    // 设置缓存大小（单位：页面数，每页默认 4 KB）
    // 10000 页面等于约 40 MB
    rc = sqlite3_exec(db, "PRAGMA cache_size = 10000;", NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        printf("Error setting cache size: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        printf("Cache size set to 10000 pages.\n");
    }

    // 创建表
    const char *create_table_sql = 
        "CREATE TABLE IF NOT EXISTS data ("
        "id INTEGER PRIMARY KEY, "
        "value TEXT);";
    rc = sqlite3_exec(db, create_table_sql, NULL, NULL, &errMsg);
    check_sqlite_error(rc, db);

    // 开始事务
    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &errMsg);
    check_sqlite_error(rc, db);

    // 插入大量数据
    sqlite3_stmt *stmt;
    const char *insert_sql = "INSERT INTO data (value) VALUES (?);";
    rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, NULL);
    check_sqlite_error(rc, db);

    for (int i = 0; i < 100000; i++) {
        char value[32];
        snprintf(value, sizeof(value), "Value %d", i);

        sqlite3_bind_text(stmt, 1, value, -1, SQLITE_TRANSIENT);
        sqlite3_step(stmt);
        sqlite3_reset(stmt);
    }

    sqlite3_finalize(stmt);

    // 提交事务
    rc = sqlite3_exec(db, "COMMIT;", NULL, NULL, &errMsg);
    check_sqlite_error(rc, db);

    printf("Inserted 100000 records.\n");

    // 关闭数据库
    sqlite3_close(db);

    return 0;
}