SQLite 在 Web 应用中的性能优化实践

SQLite 以零配置、单文件、跨平台著称，非常适合中小型 Web 应用、工具类服务和嵌入式场景。但很多开发者把它当成 MySQL 的"简配版"使用，忽略了它的特性与优化空间，导致数据量大时性能下降。

本文结合实践，总结 SQLite 在 Web 应用中的性能优化要点。

一、理解 SQLite 的工作模式

SQLite 是嵌入式数据库，进程直接读写文件，没有独立数据库服务进程。这意味着它天然受限于单机 IO 和单写者的特性。理解这一点是优化的前提。

SQLite 同一时刻只允许一个进程写入（写锁），读可以并发。所以它不适合高并发写入场景，但非常适合读多写少的业务。

二、索引设计

最有效、成本最低的优化就是建立合适的索引。原则：

为 WHERE 条件、JOIN 关联、ORDER BY 的列建索引
联合索引遵循"最左前缀"原则
索引不是越多越好，每个索引都会增加写入开销
用 EXPLAIN QUERY PLAN 验证索引是否生效

-- 为高频查询建联合索引
CREATE INDEX idx_orders_merchant ON orders(merchant_id, platform_id, created_at DESC);

-- 查看查询计划
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE merchant_id = 1 AND platform_id = 2 ORDER BY created_at DESC;

像 status 这种低区分度的列，单独建索引收益有限，更适合与高区分度列组成联合索引。

三、开启 WAL 模式

默认的 rollback journal 模式下，读写会相互阻塞。开启 WAL（Write-Ahead Logging）模式后，读操作不会被写操作阻塞，显著提升并发读性能：

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;   -- 平衡性能与安全

WAL 模式生成的 -wal 和 -shm 文件需要注意备份时一并处理。

四、善用事务

批量写入时，逐条 INSERT 会频繁触发磁盘提交，性能极差。应该把批量操作放在一个事务里：

db.transaction(() => {
  for (const item of items) {
    db.prepare('INSERT INTO cards (code, face_value) VALUES (?, ?)').run(item.code, item.face_value);
  }
})();

实测批量插入 1 万条数据，事务化的耗时约为逐条提交的 1/100，提升非常明显。

五、使用预处理语句

使用参数化预处理语句（prepared statement）而非字符串拼接 SQL，既防注入又能复用执行计划：

// 错误示例：字符串拼接
db.exec("SELECT * FROM cards WHERE code = '" + code + "'");

// 正确示例：参数绑定
const stmt = db.prepare('SELECT * FROM cards WHERE code = ?');
stmt.all(code);

六、常见性能陷阱

全表扫描：没有索引的查询在大表上会非常慢，务必用 EXPLAIN 检查
LIKE '%xx%'：无法走索引，高频场景考虑 FTS5 全文检索
SELECT *：只取需要的列，减少 IO
长事务：事务内尽量避免网络请求等耗时操作
VACUUM 遗忘：频繁删除后执行 VACUUM 回收空间、整理碎片

七、什么时候该换 MySQL/PostgreSQL

当出现以下情况时，应该考虑迁移到独立数据库服务：

并发写请求较多，出现大量 "database is locked"
数据量达到数十 GB 级别，单文件管理困难
需要主从复制、读写分离等高可用架构
多台应用服务器需要共享同一份数据

小结

SQLite 用好了在中小型项目中完全够用，关键是把索引、事务、WAL 和预处理这几板斧用到位。先理解它的单写者模型，再对症优化，就能写出既简单又高效的数据库代码。