使用nodeJS向Sqlite数据库高效插入百万行数据

使用node.js向Sqlite数据库里的插入许多行数据，使用不同的库差别巨大。

本文使用的测试数据是勾股数三元组，下面的代码能在1秒之内生成100万条勾股数。

function* pythagoreanTriplets(count) {
    let countTriples = 0;
    for (let m = 2; ; m++) {
        for (let n = 1; n < m; n++) {
            if (++countTriples > count) return; 
            yield [m * m - n * n, 2 * m * n, m * m + n * n];
        }
    }
}

const triplesGenerator = pythagoreanTriplets(100);
for (const triplet of triplesGenerator) {
    console.log(triplet);
}

最简单的几行代码，将数据插入到DB中

使用node-sqlite3模块，可以非常方便地调用SQL语句将数据插入到数据库中。注意serialize()的用法，因为javascript的异步执行机制，使用它可保证SQL语句的顺序执行，否则可能出现CREATE TABLE还没有完成，就插入数据的错误。

const sqlite3 = require('sqlite3').verbose();

// 将勾股数保存到数据库
function gougu2db(limit) {
    const db = new sqlite3.Database('gougu.db');
    db.serialize(() => {
        db.run('DROP TABLE IF EXISTS gougu');
        db.run('CREATE TABLE IF NOT EXISTS gougu (a INTEGER, b INTEGER, c INTEGER)');
        const stmt = db.prepare('INSERT INTO gougu (a, b, c) VALUES (?, ?, ?)');
        for (const triplet of pythagoreanTriplets(limit)) {
            const [a, b, c] = triplet;
            stmt.run(a, b, c);
        }
        stmt.finalize();
        db.close();
    });
}

gougu2db(100000);

上面的程序可以正常运行，可惜插入10万数据竟然耗时有76秒。

我的机器是i7-8700 3.2GHz 12核CPU，内存32GB，输出的db文件保存在固态硬盘。nodeJS版本为22.14.0。node里的sqlite3版本为5.1.7。

问题在于：使用stmt.run()运行SQL没有问题，但如果插入大量数据，则要启用事务机制。

使用事务

在批量插入数据之间，执行’BEGIN TRANSACTION’，最后进行’COMMIT’，程序性能大幅提升，插入10万行数据，只需3秒。

这里有一个细节需要注意，‘BEGIN TRANSACTION’要在prepare语句之间调用。

const sqlite3 = require('sqlite3').verbose(); 

// 将勾股数保存到数据库
function gougu2db(limit) {
    const db = new sqlite3.Database('gougu.db');
    db.serialize(() => {
        db.run('DROP TABLE IF EXISTS gougu');
        db.run('CREATE TABLE IF NOT EXISTS gougu (a INTEGER, b INTEGER, c INTEGER)');
        db.run('BEGIN TRANSACTION'); // 注意开始事务的这条语句要出现在prepare之前!!!
        const stmt = db.prepare('INSERT INTO gougu (a, b, c) VALUES (?, ?, ?)');
        for (const triplet of pythagoreanTriplets(limit)) {
            const [a, b, c] = triplet;
            stmt.run(a, b, c);
        }
        db.run('COMMIT');
        stmt.finalize();
        db.close();
    });
}

gougu2db(100000);

后面的测试使用100万行数据，启用事务之后，耗时27秒。

去掉verbose()

require(‘sqlite3’).verbose()这条语句是用于调试的，生产环境里不要写verbose()，刚才的程序插入100万行数据，减少到18秒。

网上很多资料说在建表之前执行下面两条语句，可以提升性能，但在我的程序里，并没发现明显的变化。

db.run('PRAGMA synchronous = OFF');
db.run('PRAGMA journal_mode = WAL'); 

分批次插入

一个事务里插入100万行数据，可能会占用较多的内存，可以分批次插入数据，比如每1万行、10万行为一个事务。这里引入了Promise的写法，好像代码增加了许多。

插入100万行数据，共需要21秒。

const sqlite3 = require('sqlite3');

async function createDatabase(dbFile) {
    return new Promise((resolve, reject) => {
        const db = new sqlite3.Database(dbFile);
        db.exec(`
            DROP TABLE IF EXISTS gougu;
            CREATE TABLE IF NOT EXISTS gougu (a INTEGER, b INTEGER, c INTEGER);
            `,
            (err) => (err ? reject(err) : resolve(db))
        );
    });
}

async function insertData(db, triplets) {
    return new Promise((resolve, reject) => {
        db.serialize(() => {
            db.run('BEGIN TRANSACTION'); // 开始事务
            const stmt = db.prepare('INSERT INTO gougu (a, b, c) VALUES (?, ?, ?)');

            try {
                for (const triplet of triplets) {
                    const [a, b, c] = triplet;
                    stmt.run(a, b, c, (err) => {
                        if (err) {
                            throw err; // 抛出错误，触发回滚
                        }
                    });
                }

                db.run('COMMIT', (err) => {
                    if (err) {
                        reject(err); // 提交事务失败
                    } else {
                        resolve(triplets.length); // 返回插入的行数
                    }
                });
                stmt.finalize(); // 结束预处理语句
            } catch (err) {
                db.run('ROLLBACK', () => {
                    reject(err); // 回滚事务并返回错误
                });
            } finally {
            }
        });
    });
}

// 将勾股数保存到数据库
async function gougu2db(limit) {
    const start = performance.now();
    const db = await createDatabase('gougu.db');

    try {
        let recordCount = 0;
        BATCH_SIZE = 100000;
        batch = [];
        for (const triplet of pythagoreanTriplets(limit)) {
            batch.push(triplet);
            if (batch.length >= BATCH_SIZE) {
                const insertedRows = await insertData(db, batch);
                recordCount += insertedRows;
                batch = [];
            }
        }
        if (batch.length > 0) {
            const insertedRows = await insertData(db, batch);
            recordCount += insertedRows;
        }
        const duration = (performance.now() - start) / 1000;
        console.log(`成功插入 ${recordCount} 条数据，执行时间: ${duration.toFixed(2)} 秒`);
    } catch (err) {
        console.error('插入数据时发生错误:', err);
    } finally {
        db.close(); // 关闭数据库连接
    }
}

gougu2db(1000000);

sqlite库，方便使用async/await

node里还有一个Sqlite库，它是基于sqlite3 的高级封装库，提供了更现代化的 API 和更便捷的功能，基本上不用自己写Promise对象。

const sqlite3 = require('sqlite3');
const sqlite = require('sqlite');

async function createDatabase(dbFile) {
    const db = await sqlite.open({
        filename: dbFile,
        driver: sqlite3.Database
    });
    
    await db.exec(`
        DROP TABLE IF EXISTS gougu;
        CREATE TABLE IF NOT EXISTS gougu (a INTEGER, b INTEGER, c INTEGER);
    `);
    return db;
}

async function insertData(db, triplets) {
    await db.run('BEGIN TRANSACTION'); 
    const stmt = await db.prepare('INSERT INTO gougu (a, b, c) VALUES (?, ?, ?)');
    try {
        for (const triplet of triplets) {
            const [a, b, c] = triplet;
            await stmt.run(a, b, c);
        }
        await db.run('COMMIT');
    } catch (err) {
        console.error('插入数据时发生错误:', err);
        await db.run('ROLLBACK');
    } finally {
        await stmt.finalize();
    }
}

// 将勾股数保存到数据库
async function gougu2db(limit) {
    const start = performance.now();
    
    const db = await createDatabase('gougu.db');
    
    let recordCount = 0;
    BATCH_SIZE = 100000;
    batch = [];

    try {
        for (const triplet of pythagoreanTriplets(limit)) {
            batch.push(triplet);
            if (batch.length >= BATCH_SIZE) {
                await insertData(db, batch);
                recordCount += batch.length;
                batch = [];
            }
        }
        if (batch.length > 0) {
            await insertData(db, batch);
            recordCount += batch.length;
        }
        const duration = (performance.now() - start) / 1000;
        console.log(`成功插入 ${recordCount} 条数据，执行时间: ${duration.toFixed(2)} 秒`);
    } catch (err) {
        console.error('插入数据时发生错误:', err);
    } finally {
        db.close(); // 关闭数据库连接
    }
}

gougu2db(1000000);

不过，这个程序插入100万行数据，耗时为28秒。

问题出在await stmt.run(a, b, c)那一行里，可以利用Promise.all()改为异步执行，因此修改insertData()函数。

async function insertData(db, triplets) {
    await db.run('BEGIN TRANSACTION'); 
    const stmt = await db.prepare('INSERT INTO gougu (a, b, c) VALUES (?, ?, ?)');
    try {
        const promises = triplets.map(triplet => stmt.run(...triplet));
        await Promise.all(promises);
        await db.run('COMMIT');
    } catch (err) {
        console.error('插入数据时发生错误:', err);
        await db.run('ROLLBACK');
    } finally {
        await stmt.finalize();
    }
}

这样修改之后，插入100万行数据，重新回到22秒。

效率最高的库：better-sqlite3

还有一个更快的sqlite读写库：better-sqlite3，虽然不是异步操作，但效率比sqlite3快出许多倍，下面的程序写入100万行数据，只需1秒多！！！

function* pythagoreanTriplets(count) {
    let countTriples = 0;
    for (let m = 2; ; m++) {
        for (let n = 1; n < m; n++) {
            if (++countTriples > count) return;
            yield [m * m - n * n, 2 * m * n, m * m + n * n];
        }
    }
}

const Database = require('better-sqlite3');

function createDatabase(dbFile) {
    const db = new Database(dbFile);
    db.exec(`
        DROP TABLE IF EXISTS gougu;
        CREATE TABLE IF NOT EXISTS gougu (a INTEGER, b INTEGER, c INTEGER);
    `);
    return db;
}

function insertData(db, insertStmt, triplets) {
    const transaction = db.transaction((items) => {
        for (const item of items) {
            insertStmt.run(...item);
        }
    });
    transaction(triplets);
}

// 将勾股数保存到数据库
function gougu2db(limit) {
    const start = performance.now();

    const db = createDatabase('gougu.db');

    let recordCount = 0;
    BATCH_SIZE = 10000;
    batch = [];

    try {
        const insertStmt = db.prepare('INSERT INTO gougu (a, b, c) VALUES (?, ?, ?)');

        for (const triplet of pythagoreanTriplets(limit)) {
            batch.push(triplet);
            if (batch.length >= BATCH_SIZE) {
                insertData(db, insertStmt, batch);
                recordCount += batch.length;
                batch = [];
            }
        }
        if (batch.length > 0) {
            insertData(db, insertStmt, batch);
            recordCount += batch.length;
        }
        const duration = (performance.now() - start) / 1000;
        console.log(`成功插入 ${recordCount} 条数据，执行时间: ${duration.toFixed(2)} 秒`);
    } catch (err) {
        console.error('插入数据时发生错误:', err);
    } finally {
        db.close(); // 关闭数据库连接
    }
}

gougu2db(1000000);

还可以在建库之后，执行两条pragma命令，让速度再快一点。写入100万行数据，不到1秒。

db.pragma('synchronous = OFF');
db.pragma('journal_mode = WAL');

小结

插入100万行数据，执行效率对比：

• sqlite3无事务，760秒
• sqlite3启用事务，27秒
• sqlite3去掉verbose()，18秒
• sqlite3分批插入，21秒
• better-sqlite3，1秒

几个库的官网使用链接：

• sqlite3
• sqlite
• better-sqlite3