SEO中文网

SQLite 性能调优 ​

SQLite 是文件型数据库，瓶颈大多来自磁盘IO、锁、事务、索引、配置参数，它不适合高并发大量写场景，读性能很强。

一、核心 PRAGMA 参数（最常用） ​

1. 开启 WAL 模式（最重要，大幅提升并发读写） ​

默认是 DELETE 日志模式，写的时候会锁库，读会被阻塞。

sql
PRAGMA journal_mode = WAL;
  • WAL：写追加到 wal 文件，不覆盖原库；读走原库，读写可以并行。
  • 好处：读和写可以同时进行，并发能力显著提高。
  • 持久化：设置一次就写入数据库文件，下次打开自动生效。
  • 配合：定期会做 checkpoint，把 wal 合并回主库。
sql
-- 手动执行检查点，把wal数据刷入主文件
PRAGMA wal_checkpoint(PASSIVE);

2. synchronous 同步级别 ​

控制数据刷磁盘策略：

sql
-- FULL：最安全，崩溃不丢数据，性能慢（默认）
PRAGMA synchronous = FULL;

-- NORMAL：折中，大多数场景推荐，性能好，断电极端情况可能丢少量事务
PRAGMA synchronous = NORMAL;

-- OFF：完全不刷盘，速度最快，崩溃可能数据库损坏，生产谨慎
PRAGMA synchronous = OFF;

WAL模式下一般用 NORMAL，兼顾速度和安全。

3. cache_size 缓存页大小 ​

设置内存缓存，减少磁盘读取。单位：页，默认一页4KB

sql
-- 设置为 -20000：代表20000页≈80MB，负数代表KB为单位
PRAGMA cache_size = -80000; -- 约80MB缓存

越大，越多数据放内存，读越快；不要超过可用内存。

4. temp_store 临时表存放位置 ​

sql
PRAGMA temp_store = MEMORY; -- 临时表、排序、分组放到内存，不要磁盘

5. mmap_size 内存映射 ​

把数据库文件映射到内存，操作系统缓存，读性能提升明显

sql
PRAGMA mmap_size = 1073741824; -- 1GB 字节，根据库大小设置

二、事务优化（写性能最大杀手） ​

SQLite 默认每条SQL自动开启事务，单条 INSERT/UPDATE 都提交一次，磁盘fsync，写入极慢。

❌ 慢写法：循环一条条 insert，每条自动提交 ✅ 快写法：批量操作包在事务里

sql
BEGIN TRANSACTION;

INSERT INTO t(name) VALUES ('a');
INSERT INTO t(name) VALUES ('b');
INSERT INTO t(name) VALUES ('c');

COMMIT;

大量导入数据，一定要手动事务包裹，速度提升几十上百倍。

批量插入也可以多值语法：

sql
INSERT INTO t(name) VALUES ('x'),('y'),('z');

三、索引优化 ​

  1. WHERE、JOIN、ORDER BY、GROUP BY 的字段建立索引
sql
CREATE INDEX idx_t_name ON t(name);
  1. 不要滥用索引：索引会减慢插入、更新、删除。一张表索引越多写越慢。
  2. 联合索引：遵循最左前缀原则
sql
CREATE INDEX idx_a_b ON t(a,b); -- where a=? / where a=? and b=? 命中
  1. 避免索引失效：
    • where col like '%xxx' 前缀通配符不走索引
    • where abs(col)=10 字段做函数运算不走索引
  2. 查看SQL执行计划，看有没有用到索引
sql
EXPLAIN QUERY PLAN
SELECT * FROM t WHERE name='abc';

四、SQL写法优化 ​

  1. 不要 SELECT *，只查需要的列，减少IO与内存
  2. 大结果集使用 LIMIT offset,size 分页；offset 很大性能差，建议主键分页
sql
-- offset大时慢
SELECT * FROM t LIMIT 100000,10;

-- 主键分页（推荐）
SELECT * FROM t WHERE id > 100000 LIMIT 10;
  1. 避免大事务做复杂查询；事务尽量短小，减少锁持有时间
  2. 尽量少 DELETE 大量数据：sqlite delete 不会释放磁盘空间，只是标记空闲页。
sql
DELETE FROM t WHERE ...;
PRAGMA vacuum; -- 回收空闲磁盘空间，重建数据库文件；会锁库，不要频繁跑

VACUUM 需要独占锁，业务高峰期不要执行。

五、表结构设计 ​

  1. 尽量用 INTEGER PRIMARY KEY，sqlite 内部 rowid，查询最快。
  2. 字段尽量小，不要存大BLOB到数据库，大二进制存文件，库里面存文件路径。

BLOB很大会严重拖慢查询、缓存效率。

  1. 开启外键记得：PRAGMA foreign_keys=ON;，外键会轻微损耗写入性能。
  2. 拆分大表，单表数据量千万级别以上性能会明显下滑。

六、并发注意点（SQLite短板） ​

  1. SQLite 文件锁：同一时刻只能有一个写，可以多个读。
  2. WAL模式下读写并发，但是写依然串行。不适合多客户端高并发写入场景。
  3. 多线程使用：
    • 推荐：每个线程一个数据库连接，不要共享连接。
    • 连接参数设置 busy_timeout，锁冲突等待时间，避免立刻报错database is locked
sql
PRAGMA busy_timeout = 5000; -- 锁冲突等待5000毫秒

七、导入大量数据最佳实践模板 ​

sql
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-100000;
PRAGMA temp_store=MEMORY;

BEGIN TRANSACTION;

-- 大量insert ...

COMMIT;

PRAGMA wal_checkpoint(FULL);

八、常见坑总结 ​

  1. 循环单条insert不包事务 → 超级慢
  2. 大量删除后磁盘文件体积不变 → 需要 VACUUM
  3. 没开WAL，一写阻塞所有读
  4. offset 分页越往后越慢，改用主键分页
  5. 索引建太多，写入卡顿
  6. 多进程同时高频写入，出现 database is locked

Copyright © 2026 SEO中文网