Node.js中SQLite大数组高效插入：run()与exec()对比及优化

我目前通过for循环调用db.run()向SQLite表插入数据，代码如下：

...
for(let i=0;i<data.length; i++){
    db.run(`INSERT INTO db (a, t, p, q, bM) VALUES (?, ?, ?, ?, ?)`, [data[i].a, data[i].t, data[i].p, data[i].q, data[i].m], (err) => {
        if (err) {
            reject(err);
        }
    });
}
...

由于要处理包含数千个对象的数组，我希望减少数据库往返次数，因此有以下疑问：

1. 使用exec()替代run()是否更优？
2. 在for循环中使用exec()是否会将SQL消息排队，待循环结束后批量执行？这是否是exec()相对run()的优势？
3. 若exec()在for循环中仍与run()产生相同的数据库往返次数，二者是否无差异？

请问在for循环中哪个方法更好？若二者无差异，能否推荐处理大数组的其他高效方法？我是SQLite及数据库领域的新手。

关于exec()和run()的对比 #

1. exec()替代run()并不更优：exec()本质是执行一段包含多个SQL语句的字符串，如果你在for循环里每次用exec()执行单条INSERT，和run()没有本质区别——都是每次请求都触发一次数据库操作，同样会产生大量往返。
2. for循环里用exec()不会自动批量执行：exec()不会帮你排队后批量提交，每次调用exec()都会立即执行对应的SQL语句，和run()一样每次循环都会和数据库交互一次，不存在所谓的排队批量执行优势。
3. 这种场景下二者无差异：如果都是在循环里单次执行INSERT，exec()和run()的数据库往返次数完全一致，性能表现几乎没有区别。

高效处理大数组批量插入的方法 #

针对数千条数据的插入，最有效的方式是使用SQLite的多行INSERT语法，配合事务来提升性能：

方法1：构建单条多行INSERT语句

把所有数据合并成一条INSERT语句，一次性执行：

// 先构建所有值的占位符片段
const values = data.map(item => `(?, ?, ?, ?, ?)`).join(',');
// 拼接完整SQL
const sql = `INSERT INTO db (a, t, p, q, bM) VALUES ${values}`;
// 把所有数据的字段按顺序整理成数组
const params = data.flatMap(item => [item.a, item.t, item.p, item.q, item.m]);

db.run(sql, params, (err) => {
    if (err) {
        reject(err);
    } else {
        resolve();
    }
});

注意：SQLite默认有单条SQL语句长度限制，不过数千条数据一般不会超出，如果数据量极大（比如上万条），可以分成若干批次执行。

方法2：开启事务批量执行

即使分批次，开启事务也能大幅提升性能——因为SQLite默认每条语句都是一个独立事务，每次提交都会写磁盘，事务可以把多个操作合并成一次提交：

db.serialize(() => {
    db.run('BEGIN TRANSACTION');
    // 这里可以用循环执行run()，或者分批次的多行INSERT
    data.forEach(item => {
        db.run(`INSERT INTO db (a, t, p, q, bM) VALUES (?, ?, ?, ?, ?)`, 
            [item.a, item.t, item.p, item.q, item.m], 
            (err) => {
                if (err) {
                    db.run('ROLLBACK');
                    reject(err);
                }
            }
        );
    });
    db.run('COMMIT', () => {
        resolve();
    });
});

db.serialize()会保证事务内的语句按顺序执行，配合BEGIN/COMMIT把所有插入合并成一个事务，避免了每次插入都写磁盘的开销，性能会比无事务的循环提升数倍甚至数十倍。

方法3：结合两种方式（推荐）

把数据分成若干小批次（比如每500条一批），每批用多行INSERT，同时包裹在事务里，兼顾性能和避免单条SQL过长：

const batchSize = 500;
db.serialize(() => {
    db.run('BEGIN TRANSACTION');
    for (let i = 0; i < data.length; i += batchSize) {
        const batch = data.slice(i, i + batchSize);
        const values = batch.map(item => `(?, ?, ?, ?, ?)`).join(',');
        const sql = `INSERT INTO db (a, t, p, q, bM) VALUES ${values}`;
        const params = batch.flatMap(item => [item.a, item.t, item.p, item.q, item.m]);
        db.run(sql, params, (err) => {
            if (err) {
                db.run('ROLLBACK');
                reject(err);
            }
        });
    }
    db.run('COMMIT', () => {
        resolve();
    });
});

总结 #

• 循环里用exec()和run()没有性能差异，都不适合批量插入。
• 优先用多行INSERT+事务的组合，这是SQLite批量插入的最优实践，能大幅减少磁盘IO和数据库往返次数，提升插入效率。

内容的提问来源于stack exchange，提问作者xtyy