Linux·数据库INSERT优化

        在业务中，我们经常会要对数据进行存储，对于少量数据插入时，我们可以直接使用 INSERT 插入数据，但是当我们需要插入的数据比较多时，使用 INSERT 插入的话时间消耗是很大的，具体而言单次插入600+时，就需要十几秒，显然这个时间是用户无法忍受的，那么有没有什么办法优化数据插入时间呢？

        那么我们应该先搞清楚为什么 INSERT 插入这么耗时间，原因是 INSERT 每次都会触发磁盘同步（fsync()），写入磁盘本身就是一个耗时的操作，每次插入一点数据就同步一点数据，对于磁盘而言更是雪上加霜。到这里其实我们想到的第一个优化点，应该就是对于要插入的数据，一次性同步到磁盘，这样可以减少多次同步磁盘带来的时间消耗。

        具体的，可以使用事务 BEGIN TRANSACTION;

        具体插入方式：

BEGIN TRANSACTION; 
INSERT INTO my_table (id, value) VALUES (1, 'A'); 
INSERT INTO my_table (id, value) VALUES (2, 'B'); 
INSERT INTO my_table (id, value) VALUES (3, 'C'); 
COMMIT;

🔹 优点：

• 避免每条 INSERT 都触发 fsync()，提升写入速度。
• 适用于数据较少的批量写入（几十到几百行）。

        虽然比单条 INSERT 更快，但仍然会触发多次索引更新。

        进一步优化，使用 sqlite3_prepare_v2()（C 语言，高性能）

        在 C 语言中，推荐使用 预编译 SQL + 绑定参数，避免 SQL 解析开销：

#include <stdio.h> 
#include <sqlite3.h> 
void batch_insert(sqlite3 *db) { const char *sql = "INSERT INTO my_table (id, value) VALUES (?, ?);"; sqlite3_stmt *stmt; if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) { printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db)); return; } sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0); // 开启事务 for (int i = 1; i <= 1000; i++) { // 数据组装sqlite3_bind_int(stmt, 1, i); sqlite3_bind_text(stmt, 2, "Sample", -1, SQLITE_STATIC); if (sqlite3_step(stmt) != SQLITE_DONE) { printf("Insert failed: %s\n", sqlite3_errmsg(db)); } // 复用 statement sqlite3_reset(stmt); } sqlite3_exec(db, "COMMIT;", 0, 0, 0);// 提交事务 sqlite3_finalize(stmt); 
} 
int main() { sqlite3 *db; sqlite3_open("test.db", &db); batch_insert(db); // 批量插入 1000 条数据 sqlite3_close(db); return 0; 
}

🔹 优点：

• 使用 sqlite3_prepare_v2() 预编译 SQL，避免 SQL 解析开销。
• sqlite3_bind_*() 绑定参数，防止 SQL 注入。
• 批量插入 1000 行，仅触发 1 次事务提交，写入速度极快。
• 适用于大规模数据插入（几千行以上）。

再进一步优化

1. 开启 WAL 模式

PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;

🔹 优势：

• WAL 模式支持并发读写，写入更快。

2. 关闭 fsync() 提高速度（仅适用于低风险场景）

PRAGMA synchronous=OFF;

⚠️ 注意：这样可能导致断电时丢数据，适用于非关键数据。

🚀 结论

方法	适用场景	速度
BEGIN TRANSACTION;	小批量插入（几十到几百行）	⭐⭐⭐
sqlite3_prepare_v2() + sqlite3_bind_*()	大批量插入（1000+ 行）	⭐⭐⭐⭐⭐

👉 如果数据量较大（1000+ 行），推荐 sqlite3_prepare_v2() 方式，搭配 WAL 模式，能达到最佳写入速度。

🔥 性能对比

方法	1000 条数据写入时间
单条 INSERT（无事务）	约 5-10 秒
批量 INSERT（事务模式）	约 50-200 毫秒
预编译 SQL + 事务	约 5-20 毫秒

如果数据量更大，比如 10 万行，普通 INSERT 可能需要 几分钟，但 使用事务 + 预编译 只需 几秒。

对于多表也支持，完整示例：

#include <stdio.h>
#include <sqlite3.h>#define DB_PATH "test.db"   // 数据库文件路径
#define NUM_INSERTS 500     // 每张表插入 500 行，总共 1000 行void batch_insert(sqlite3 *db) {const char *sql1 = "INSERT INTO my_table (time, ip, dir, new, count) VALUES (?, ?, ?, ?, ?);";const char *sql2 = "INSERT INTO netflow_app_live (time, id, new, count) VALUES (?, ?, ?, ?);";sqlite3_stmt *stmt1, *stmt2;// 预编译 SQL 语句if (sqlite3_prepare_v2(db, sql1, -1, &stmt1, NULL) != SQLITE_OK ||sqlite3_prepare_v2(db, sql2, -1, &stmt2, NULL) != SQLITE_OK) {printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db));return;}// 开启事务sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);for (int i = 0; i < NUM_INSERTS; i++) {// 插入 my_tablesqlite3_bind_int(stmt1, 1, 1610000000 + i);      // timesqlite3_bind_text(stmt1, 2, "192.168.1.1", -1, SQLITE_STATIC); // ipsqlite3_bind_int(stmt1, 3, i % 2);               // dirsqlite3_bind_int(stmt1, 4, i);                  // newsqlite3_bind_int(stmt1, 5, i * 10);             // countif (sqlite3_step(stmt1) != SQLITE_DONE) {printf("Insert into my_table failed: %s\n", sqlite3_errmsg(db));}sqlite3_reset(stmt1);  // 复用 statement1// 插入 netflow_app_livesqlite3_bind_int(stmt2, 1, 1610000000 + i);  // timesqlite3_bind_int(stmt2, 2, i);              // idsqlite3_bind_int(stmt2, 3, i);              // newsqlite3_bind_int(stmt2, 4, i * 10);         // countif (sqlite3_step(stmt2) != SQLITE_DONE) {printf("Insert into netflow_app_live failed: %s\n", sqlite3_errmsg(db));}sqlite3_reset(stmt2);  // 复用 statement2}// 提交事务sqlite3_exec(db, "COMMIT;", 0, 0, 0);// 释放 statementsqlite3_finalize(stmt1);sqlite3_finalize(stmt2);
}int main() {sqlite3 *db;// 打开数据库if (sqlite3_open(DB_PATH, &db) != SQLITE_OK) {printf("Cannot open database: %s\n", sqlite3_errmsg(db));return -1;}// 设置 WAL 模式，提高写入性能sqlite3_exec(db, "PRAGMA journal_mode=WAL;", 0, 0, 0);sqlite3_exec(db, "PRAGMA synchronous=NORMAL;", 0, 0, 0);// 批量插入数据batch_insert(db);// 关闭数据库sqlite3_close(db);printf("Batch insert completed.\n");return 0;
}