高性能的 SQLite 数据库

[ApliNi]

WAL 模式

WAL 模式可以在保证数据安全的前提下不限制并发读取数据. 它将所有写入存放在一个单独的文件里并在提交事务时将其写入数据库.

-- 使用 WAL 模式
PRAGMA journal_mode = WAL;

注意! WAL 模式可能无法与关闭写同步 synchronous = OFF 一起使用, 在我的测试中, 开启 WAL 模式的数据库无法修改 synchronous .

自动整理碎片

SQLite 不会在删除和修改数据时减小体积, 而是需要手动运行 VACUUM 才能减小体积. 但运行 VACUUM 可能会消耗将近两倍数据库大小的存储空间存放临时文件. 使用 auto_vacuum = 2 会在SQLite准备占用更多磁盘空间时运行, 将空闲分页移动到文件末尾并在下一次写入数据时可以复用这部分空间. 但此时运行 VACUUM 依然会占用将近两倍数据库体积的空间以及消耗很长时间.

-- 开启自动碎片整理
PRAGMA auto_vacuum = 2;

使用 auto_vacuum = 2 并不能让数据库体积动态缩放, 而是让数据库自动自动整理和复用碎片空间.

事务

使用事务会极大的提高插入语句的运行速度, 但如果在事务提交之前发生断电或程序异常退出, 可能导致事务提交之前的数据未被写入数据库. 如果开启 WAL, 这些数据有可能还在 WAL 文件中, 但我还不知道如何读取或继续提交.

数据库默认会使用事务, 但为每条语句使用, 也就是这样:

BEGIN TRANSACTION; -- 数据库自动开启事务
-- 这一条语句
COMMIT; -- 数据库自动提交事务

只要手动创建事务并提交即可避免为每条语句创建事务带来极大的延迟.

BEGIN TRANSACTION; -- 开启事务
-- 插入语句...
-- 插入语句...
-- 插入语句...
COMMIT; -- 提交事务

自动创建提交事务

或许可以这样做:

// 开启事务
db.run('BEGIN TRANSACTION;');

// 匹配SQL语句中的写入指令
let regWriteCommand = new RegExp(/^DELETE|UPDATE|INSERT/);
// 记录上一条SQL语句
let lastSql = '';
// 锁, 在事务提交后开启
let saveData_Lock = false;

// 监听语句运行完成
db.on('profile', async (sql, time) => {
	// 如果语句中包含写入指令
	if(sql === lastSql){
		saveData();
	}else if(regWriteCommand.test(sql)){
		saveData();
		lastSql = sql;
	}

	// 如果这条语句运行时间过长
	if(time >= 1000){
		logger.warn('[SQL] 语句运行时间过长: ', sql);
	}
});

// 提交数据 // export
async function saveData(){
	if(!saveData_Lock){
		saveData_Lock = true;
		setTimeout(async () => {
			await AsyncDB.run('COMMIT;'); // 提交
			await AsyncDB.run('BEGIN;'); // 打开一个新的事务
			saveData_Lock = false;
			console.log(' -- 提交完成');
		}, 2000);
	}
};

这个程序会监听语句运行完成, 并定时提交事务以及创建新的事物. [!] 但这会导致其他语句中的事物无效或出错, 如果已经使用了事物, 请不要这样做.

根据我的测试, 仅添加这个功能而不做其他任何处理(但应用上方说到的优化方法), 数据库每秒插入的随机数据从 2000 两千 行提升到了 70000 七万 行!

SQLite YYDS (wu