掌握 SQLite 性能：超越基础
SQLite 是世界上部署最广泛的数据库。它存在于你的手机（iOS 和 Android）、浏览器、Mac，甚至可能在你的微波炉里（不开玩笑）。 默认情况下，SQLite 配置为安全和兼容，并不一定是极速。如果你在将 SQLite 用于正式的桌面应用程序或数据密集型流程，你需要深入了解其内部机制。
在这篇文章中，我们将介绍我们在 HarborDB 中用于在毫秒内查询数百万行数据的技术。
1. 首先：启用 WAL
如果你什么都不做，至少要做这个。预写日志 (Write-Ahead Logging, WAL) 模式从根本上改变了 SQLite 的并发性。 默认情况下，SQLite 使用回滚日志。这意味着当一个进程正在写入时，其他进程都无法读取（反之亦然，取决于锁定模式）。
WAL 模式允许：
- 并发读取和写入。
- 更快的写入（因为“检查点”是顺序的）。
如何启用它：
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
注意： 只要硬盘没有物理损坏或断电，
PRAGMA synchronous = NORMAL就是安全的。对于桌面应用程序，这是完美的权衡。
2. 批量插入和事务
初学者最常见的错误是在没有事务的循环中插入行。
慢的方法（Python 示例）：
# 不要这样做。这会花费很长时间。
for row in data:
cursor.execute("INSERT INTO users VALUES (?)", row)
conn.commit() # 每一行都自动提交！
SQLite 必须为每次插入等待物理磁盘旋转（或 SSD 同步命令）。
快的方法：
conn.execute("BEGIN TRANSACTION")
for row in data:
cursor.execute("INSERT INTO users VALUES (?)", row)
conn.commit()
速度差异：通常快 100 倍到 1000 倍。
3. 索引：明智地使用
索引是数据的有序副本。它使读取极快，但使写入稍慢（因为你需要同时更新表和索引）。
黄金索引提示
在猜测之前使用 EXPLAIN QUERY PLAN。
EXPLAIN QUERY PLAN SELECT * FROM logs WHERE severity = 'ERROR';
如果你看到 SCAN TABLE，那么你做错了（它正在读取每一行）。
如果你看到 SEARCH TABLE USING INDEX，那么你就在正确的轨道上。
创建索引：
CREATE INDEX idx_logs_severity ON logs(severity);
4. 高级用户的 PRAGMA 优化
以下是我们 HarborDB 中使用的一些鲜为人知的命令：
-
PRAGMA temp_store = MEMORY;强制 SQLite 将临时表和索引保存在 RAM 中，而不是写入磁盘。如果你有足够的 RAM，这是一个巨大的胜利。 -
PRAGMA mmap_size = 30000000000;使用内存映射 I/O 访问数据库文件。操作系统处理缓存。对于大量读取，这几乎可以消除 I/O 延迟。 -
PRAGMA cache_size = -2000;设置 SQLite 在内存中保留的页面数。负数表示千字节（这里约为 2MB）。正数表示页面计数。对于大型查询，请增加此值。
5. 应用程序优先架构
有时问题不在于 SQL，而在于你的代码。 在 HarborDB，我们将繁重的读取操作卸载到后台线程，同时保持 UI 响应。因为我们启用了 WAL 模式，如果用户对 UI 进行小的更改（写入），后台线程不会阻塞。
总结
SQLite 不是一个“玩具”数据库。如果你正确对待它，它是一个可以处理海量工作负载的强大工具。
- 启用 WAL。
- 始终将写入包装在 事务 中。
- 对
WHERE子句使用 索引。 - 使用 PRAGMA 优化内存。
祝优化愉快！