先说个很现实的问题后端要在线上挂 MySQL、PostgreSQL那是服务器该干的事可我们手上还有大量“单机、本地、离线”的数据要存比如移动端 App 的离线缓存、桌面软件的配置数据、嵌入式设备上的采集记录、甚至测试环境的临时数据库。把这些都塞给服务器型数据库不仅重还平白增加运维负担。SQLite 就是为这类“本地存储”而生的轻量级数据库它不装服务、不配账号、不起进程直接把数据库引擎写进你的程序里。我最早接触 SQLite 是在某个移动端项目里做离线数据缓存后来在桌面小工具、内网采集设备上都反复用到。用得越久越觉得SQLite 表面上只是“一个文件”但它背后的锁机制、事务模型、性能边界和坑比很多人想象的要多得多。这篇文章就把我自己踩过的坑和沉淀下来的经验整一整适合这几类人看一是准备给本地功能做存储选型、还没决定用 JSON 文件还是 SQLite 的二是已经接了 SQLite 但经常遇到 database is locked、性能越来越差的三是想搞清楚 SQLite 到底在什么场景该用、什么场景千万别用的。1. SQLite 到底是个什么东西为什么本地存储绕不开它1.1 它不是数据库服务器而是一个数据库引擎很多新手第一次看到“SQLite 是数据库”时脑子里想的是“那我要不要安装一个数据库服务、监听一个端口、再用客户端连上去”。不用它的本质是一个嵌入式的数据库库函数直接链接进你的程序进程里。你的程序调用它的接口它在本地直接读写一个数据库文件不需要 TCP 连接不需要独立进程。你可以这样理解传统数据库是“机房里的服务器”所有请求都要从客户端发送、服务器解析分配、回收资源、再返回结果。SQLite 则像“你工具箱里的内六角扳手”要用它的时候直接拿出来拧不用搭一个车间。这个特性决定了它天然适合本地存储因为零配置不需要初始化、不需要调参数、不需要账号权限。单文件整个数据库就是一个 .db 文件拷走就行备份也简单。跨平台Windows、Linux、macOS、Android、iOS 都能跑同一套库。免运维没有独立服务进程不担心服务挂了、忘了开机自启。1.2 为什么本地数据存储优先选它而不是 JSON 文件我见过不少项目开头非常简单就用 JSON 文件存配置和记录。数据量小的时候 JSON 文件确实方便可一旦数据量上来或者并发变多立刻露馅读一万条记录你得上上下下全量加载进内存再自己遍历、筛选、排序多人同时写一个文件时还会出现互相覆盖的问题。SQLite 用 SQL 查询 事务保障把这些事都帮你兜住了。反过来为什么不直接上 MySQL本地桌面工具如果依赖 MySQL用户机器上还得装一个数据库服务对普通用户来说这不是“减轻负担”这是“劝退”。尤其在做离线功能时网络一断、服务一挂所有功能都瘫痪。SQLite 直接把数据库封装在应用内部断网、无服务环境都能正常工作。这里我想强调一个很多人忽略的点SQLite 不是一个“玩具数据库”。它完整支持事务的 ACID 特性支持标准 SQL 的大部分语法还支持触发器、视图、窗口函数、JSON 处理函数。在几十 GB 级别的数据量内它的查询能力并不弱。只是它的写并发不如服务器型数据库强而这个边界后面我会重点讲。1.3 哪些场景每天都在用 SQLite移动端离线缓存比如资讯类应用离线缓存几百条文章用 SQLite 查询、筛选作者、按时间排序。桌面应用配置与数据存储浏览器书签、聊天记录、本地媒体库索引大量桌面软件直接以 SQLite 作为主存储。IoT 与嵌入式设备路由器、摄像头、工控采集器在一台资源受限、网络不稳定的设备上SQLite 是很多嵌入式 Linux 方案的默认选择。数据和 ETL 流程的中间层写脚本爬完数据后不急着入库服务端先把数据攒进本地 SQLite后面再统一清洗导入。单元测试与模拟数据跑自动化测试的时候用 SQLite 代替生产数据库构建快、清理方便。2. SQLite 核心特性逐个拆解弄懂机制才能避开坑2.1 动态类型与列亲和性看着随意实际有算计SQLite 有一个和传统数据库很不一样的特性动态类型。你建表时声明了一个列是 INTEGER 类型往里面塞一个文本值它也不会立刻报错而是尝试做类型转换转换不了就按“类型亲和性”的规则把数据存成它所能接受的形式。比如你有这样一张表CREATE TABLE task ( id INTEGER PRIMARY KEY, level INTEGER, title TEXT );你执行INSERT INTO task (id, level, title) VALUES (1, 3, demo)由于level列的亲和类型是 INTEGER字符串 3 会被转成整数 3 再存储。但如果你塞一个abc它不会报错只是原样存成文本。这个行为在不同版本、不同写法下表现很隐晦稍不注意就是脏数据。我的建议是建表时不要因为 SQLite 宽容就乱来。所有列都加NOT NULL能用CHECK约束强制数据范围比如性别的取值为male或female直接在约束里声明。默认值也尽量写在表结构里不要让应用层每次都拼凑。别把数据库当垃圾桶表结构约束越严格后续出问题的概率越低。2.2 事务、原子提交与 WAL 模式SQLite 的写操作并不是每次 INSERT 都直接往文件里硬写而是先写在日志里再在合适的时机合并回主数据库文件。旧版本默认的日志模式叫rollback journal写事务的时候先备份旧页面然后修改主文件如果中途崩溃就通过日志回滚。另一种更常用的模式是WALWrite-Ahead Logging。WAL 模式下写操作先追加到独立的-wal文件里读操作仍然直接读主文件不阻塞。这样读和写可以并发进行而不是像旧模式那样写事务会独占数据库文件、把读操作全部堵住。开启方式一句话PRAGMA journal_mode WAL;我要特别提一句WAL 模式好归好但它会额外生成.db-wal和.db-shm两个文件。你平时拷备份时如果只拷贝 .db 文件会把 WAL 里还没来得及合并的数据丢掉。正确做法是先用PRAGMA wal_checkpoint;把日志合并进主文件或者关掉数据库连接后再拷贝再或者直接用 SQLite 提供的备份接口。2.3 索引别把 SQLite 当文件线性扫描用数据量小的时候没索引也可以跑得飞快。可当表里塞了几十万条记录一条WHERE status 1 ORDER BY create_time DESC没有索引支撑SQLite 只能全表扫描、临时排序性能肉眼可见地下降。索引这块有几个关键原则主键会自动建索引。外键列、经常出现在 WHERE/JOIN/ORDER BY 里的列建议建索引。复合索引要遵守“最左前缀”原则。比如(user_id, create_time)索引对WHERE user_id ?有效但只对WHERE create_time ?就无效。不是索引建得越多越好写操作会同时维护索引索引太多会拖慢写入速度。判断一条语句到底有没有走索引不要靠猜直接看执行计划EXPLAIN QUERY PLAN SELECT * FROM event_log WHERE user_id 100 AND create_time 2024-01-01;输出里如果出现SEARCH event_log USING INDEX idx_user_time (user_id?)说明索引生效了如果出现SCAN event_log那就是全表扫描得再想想索引方案。2.4 并发模型与锁粒度知道为什么会有锁错误SQLite 官方给出的定位是“允许多进程并发读但同一时刻只允许一个进程执行写操作”。这个限制是它的架构决定的整个数据库文件本质上是“一个全局共享的数据结构”写事务要对结构做改动如果两个进程同时改文件就没法保持一致。锁的粒度是整库级别而不是行级或表级。这意味着哪怕你只想改一行数据也得先拿到全局的 EXCLUSIVE 锁这段时间内其他进程连读都可能被卡住。所以在 SQLite 里“尽量减少写事务的持续时长”比“减少写数据的量”更重要。建议设置一个忙等待时间PRAGMA busy_timeout 5000;这样当某个写操作因为锁冲突失败时不会立刻抛出database is locked而是会等 5 秒。如果 5 秒后还是拿不到锁才报错。这个参数的默认值在不同驱动里不一样有些是 0也就是一撞锁就立刻报错体验很差。3. 日常开发中最常用的操作与优化实践3.1 数据库的创建和连接方式如果只是命令行调试直接使用sqlite3命令行即可快速上手。创建数据库只需要指定一个文件路径不需要预先初始化什么目录也不需要先建库再建表sqlite3 app.db在 Python 里使用自带的标准库最方便import sqlite3 conn sqlite3.connect(app.db) cursor conn.cursor() cursor.execute(SELECT 1) print(cursor.fetchone()) conn.close()如果你日常要管理大量 SQLite 文件建议找一个开源图形客户端能直观查看表数据、执行 SQL、查看执行计划。但别完全依赖图形界面命令行下输入.tables、.schema、.indexes这几个命令往往能更快定位问题。3.2 建表规范与类型实践我在表结构上的三个习惯建表时除了字段类型我一般会把索引、约束、默认值一起考虑进去。一个比较规范的例子CREATE TABLE IF NOT EXISTS user_event ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, event_type TEXT NOT NULL CHECK (event_type IN (click, view, purchase)), event_time TEXT NOT NULL DEFAULT (datetime(now)), extra_info TEXT ); CREATE INDEX IF NOT EXISTS idx_user_event_user_time ON user_event(user_id, event_time);这里有几个判断主键用INTEGER PRIMARY KEY AUTOINCREMENT可以自动生成单调递增 ID对于手动传 ID 的业务完全没有必要反而多了限制。时间字段直接存 ISO 格式文本排序比较都自然不需要自己算时间戳除非你要做复杂的跨时区计算。event_type加CHECK约束保证脏数据进不来。3.3 批量写入的黄金法则一个事务搞定所有这是我在数据导入场景里最深的体会。同样是插入一万条记录逐条INSERT和全部包在一个事务里性能差距能达到几十倍甚至上百倍。原因很简单每一条 INSERT 默认都会自动开启事务写完立刻提交期间涉及日志、锁、同步等操作而如果你手动开启事务只需要一次日志同步其他时间都在内存里操作。正确写法import sqlite3 conn sqlite3.connect(batch.db) cursor conn.cursor() cursor.execute(CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)) data [(i, fvalue-{i}) for i in range(10000)] cursor.executemany(INSERT INTO t (id, val) VALUES (?, ?), data) conn.commit() # 一次性提交 conn.close()另外连接参数可以加一个timeout让连接层自动忙等待避免并发戳到锁就崩conn sqlite3.connect(app.db, timeout10)3.4 导出、导入与数据迁移的几个命令命令行下.dump是导出完整数据库最稳妥的方式它会把建表语句和所有数据以 SQL 文本形式输出只要目标环境能跑 SQLite就能完整重建sqlite3 app.db .dump backup.sql sqlite3 new_app.db backup.sql如果你只是需要导入一份 CSV 文件在表结构已经建好的情况下sqlite3 app.db .mode csv .import ./data.csv my_table这个命令有个坑默认导入时如果遇到整型字段会把00123自动转成123丢失前导零。要保留原始格式就需要先建一个文本类型的临时表导入后再做转换。4. 性能调优的四个关键参数4.1 调整之前先想清楚你是读为主还是写为主SQLite 调优不是一个通用配置吃遍天得先看你最频繁的操作是什么。读为主的场景重点在索引和缓存写为主的场景重点在事务批处理、日志模式和同步方式。下面这张表是我自己的参数对照你可以直接拿来当起点参数默认值建议值说明journal_modedeletewal读多写少首选提升并发读体验synchronousfull或 2normalWAL 模式下可降为 normal崩溃时最多丢最近几笔不至于数据库损坏cache_size-20002MB-6400064MB负号表示按 KB 计单位是 64MBbusy_timeout0部分驱动未设置5000-10000避免撞锁后立刻抛错temp_storedefaultmemory临时表和排序决定放在内存中4.2 page_size 与 B-tree 层级的关系SQLite 默认的页面大小是 4096 字节数据库文件被拆成很多个固定大小的页。页是读写的最小单位B-tree 的层级越高查询时理论上要访问的页越多。对几十 GB 级别的库把页面调大到 8192 或 16384 字节可以在某些场景下减少 B-tree 层数、降低 IO 次数。但这个调整只能在新库或重建库时生效并且会带来内存占用和内部分页的复杂变化不推荐新手一开始就调默认值对大部分中小型库够用。4.3 cache_size 与命中率缓存池存的是最近访问的数据库页。读操作重复访问同样数据时直接从缓存命中不再碰磁盘。把缓存从默认的 2MB 调整到 64MB对几 GB 级别的读密集应用提升非常明显。你可以用下面的方式查看实际命中情况PRAGMA cache_size -64000; PRAGMA stats;stats输出里会显示当前缓存页数和未命中次数方便定位缓存是否过小。4.4 写入场景单独说Synchronous 别乱降PRAGMA synchronous控制的是日志写入磁盘的时机。默认FULL最安全保证应用层返回“写入成功”时日志已经落盘。在 WAL 模式下可以设置成NORMAL通常只在真正的电源掉电时可能丢最近几笔事务但不会像普通模式那样产生整库损坏。我的经验是如果是记录流水、日志这类允许丢少量数据的场景NORMAL可以接受如果是订单、账户余额这种强一致数据老老实实保持FULL别为了那点性能拿数据安全冒险。5. 我遇到过的常见坑与排查技巧5.1 每次打开就报 database is locked多半是事务没有提交有一次我的桌面程序里有个功能点击按钮后去执行一个写任务第一次执行正常第二次直接卡死然后报database is locked。查到最后发现问题出在一个事务分支里某条数据校验失败时代码直接抛异常返回了commit和rollback都没执行连接一直握着写锁。之后其他连接过来永远等不到锁释放。排查方法很简单先看是不是有连接卡在事务里SELECT * FROM pragma_list;如果你看不到所在语言层的连接管理就直接去数据库文件所在目录看有没有.db-journal或.db-wal文件一直存在说明有连接没有干净关闭。另外建议显式使用上下文管理或try/finally保证事务一定会被关闭conn sqlite3.connect(app.db) try: cursor conn.cursor() cursor.execute(BEGIN) ... conn.commit() except Exception: conn.rollback() raise finally: conn.close()5.2 不要在网络共享盘和网盘目录上直接跑 SQLiteSQLite 的正常运行依赖底层文件系统提供的字节级锁。在 NFS、SMB、网盘同步目录这类环境里文件锁经常失效或者被延迟处理结果是两台机器同时写的时候数据库很快就损坏。我见过一个真实案例某个团队把软件安装目录放在公司共享盘上每个同事运行同一个工具、共享同一个 SQLite 文件。运行了一段时间后库文件直接database disk image is malformed。后来改成每人本地一个库定期上传备份问题才彻底消失。5.3 热备份不要直接复制文件特别是在 WAL 模式WAL 模式下数据库的“真正最新数据”可能有一部分还在.db-wal文件里。直接复制.db文件等于只备份了一个中间状态。正确做法是用 SQLite 提供的在线备份接口Python 里可以这样import sqlite3 src sqlite3.connect(source.db) dst sqlite3.connect(backup.db) with dst: src.backup(dst) dst.close() src.close()这个接口会自己处理 WAL 日志的合并和锁冲突是官方推荐的方式。5.4 数据查出来和存进去不一致看类型亲和性如果你发现WHERE id abc居然能查出数据或者 ORDER BY 顺序怪怪的大概率是类型亲和性在捣鬼。文本列存了各种混搭类型排序时数字、字符串混在一起结果完全不是你以为的样子。解决办法是建表时统一用TEXT NOT NULL并加上CHECK约束或者用CAST显式转换。5.5 数据库文件损坏了怎么抢救先别急着删先用命令自检sqlite3 corrupted.db PRAGMA integrity_check;如果只是部分页损坏可以尝试.dump导出部分仍可读的数据然后用新库重建。这里有个现实经验如果数据库文件是 0 字节或者完全打不开.dump也救不了提前做好备份比事后修库靠谱得多。6. 选型边界什么情况下真的不要再硬扛 SQLite 了6.1 高并发写入的在线系统不要用 SQLite我见过一个同学把 SQLite 用在 HTTP API 服务上几十个并发请求同时写库结果日常报database is lockedCPU 不高、磁盘不忙但服务就是卡死。这就是典型的用错场景。SQLite 的写并发上限也就是个位数或低十位数级别更高要跨到 PostgreSQL 或 MySQL。怎么判断当前需求是否越界有个简单的经验值如果单库写 QPS 经常超过 500并发写连接经常超过 10 个就别再折腾 SQLite 了。6.2 多机共享的数据库不要用 SQLite如果你以为可以把 SQLite 文件放到共享磁盘上、让两台服务器同时读写那 NFS 和文件锁的问题就够喝一壶。SQLite 的设计是“本地文件系统的数据库”不是“网络文件系统的数据库”。多机协作请用真正的服务端数据库。6.3 超大规模数据仓库不要用 SQLiteSQLite 在处理单表千万级、总库几十 GB 时还能保持不错的性能。一旦你去到 TB 级数据量文件的备份、迁移、维护都会变成灾难。另外分析型查询对数据库的并行计算、资源隔离都有要求SQLite 在资源控制上也远不如服务端数据库完善。数据仓库类场景上专业数据库或列式存储更合适。6.4 数据模型极其复杂、外键关系特别多的大型系统SQLite 支持外键但默认并不开启外键约束而且它没有服务器数据库的数据字典、权限分层这类功能。如果你需要一套严谨的权限体系、大量用户角色管理、复杂的增量日志回放SQLite 会很吃力。6.5 反过来什么场景我依然推荐 SQLite某个桌面工具、移动 App 的离线主存储。单机工具的配置和数据索引。嵌入式 Linux 设备里的运行数据。自动化测试中的临时数据库。数据管道里作为中间落盘和临时处理的层。这类场景的共同点是单机运行、并发量低、可靠性要求高、运维成本敏感。SQLite 在这些场景里几乎是“兵器榜第一名”。7. 后端语言与 ORM 配合时的一些心得很多人用 Python 写本地工具会直接上 SQLAlchemy 或 Peewee。这类 ORM 会让代码好写很多但要注意几个细节。SQLAlchemy 默认对 SQLite 的连接池处理是“每次新建连接”你如果自己手动开了一个连接但长期不关闭底层连接池不会主动释放内存里会堆积很多事务状态。建议明确开启连接池参数比如pool_size、max_overflow别让连接数超出预期。ORM 的自动建表看起来省事但我遇到过一个 case某个表字段在 ORM 里定义了长度限制底层 SQLite 并不强制 VARCHAR 长度结果脏数据照样写入查询端也没做二次校验最后出现一堆超长字符串。ORM 不是万能保险关键约束还是要用原生 SQL 和 CHECK 保证。另外ORM 执行批量插入时默认可能还是逐条执行需要显式开启 bulk 模式或者退回原生 SQL 的executemany。这一点在不同 ORM 版本里差异很大不能默认它最优。我在本地工具里更推荐“半 ORM 策略”用原生 SQL 建表和管理迁移用轻量查询层做数据封装。这样既有 SQL 的灵活可控又不损失开发效率。8. 如果提前做对这些事你也会少走一半弯路选择 SQLite 不等于“放弃数据库设计”。相反正因为它的约束少、并发行较弱才更需要你主动去补足这些边界。我自己的习惯是每个项目接入 SQLite 之前花十分钟把下面这张清单过一遍表结构约束是否完整NOT NULL、CHECK、外键是否都声明了时间字段格式是否统一写入端的时区和读取端的时区是否一致多进程并发访问是否开启了 WAL 和 busy_timeout写操作是否都包在显式事务里有没有可能因为异常跳过 commit数据库文件是不是放在本地磁盘有没有被网盘同步目录和杀毒软件扫描干扰备份脚本用的是备份 API 还是直接复制文件把这张清单走完SQLite 就能在本地存储场景里变得非常可靠。它不是性能瓶颈反而是一种少操心、多省事的方案。最后分享一个小习惯每次发布版本前我会对正式库跑一次PRAGMA integrity_check和PRAGMA foreign_key_check几秒钟就能提前发现结构层面的隐患。轻量级是它的优点但没有充分理解它的边界轻量也会反过来变成坑。
