第十章运维与最佳实践

10.1 备份策略

SQLite 的备份比传统数据库简单得多——本质上就是备份一个文件。但要做到安全、一致的备份，需要注意一些细节。

方式一：.backup 命令（推荐）

bash

# 在 sqlite3 命令行中
sqlite3 my_app.db ".backup backup.db"

.backup 的优势：

  ✅ 原子操作，保证备份数据库的一致性
  ✅ 即使数据库正在被其他进程使用，也能安全备份
  ✅ 使用 SQLite 内部的 Backup API
  ✅ 支持增量备份（只复制变化的页）

方式二：.dump 命令（导出为 SQL 脚本）

bash

# 导出整个数据库为 SQL 脚本
sqlite3 my_app.db ".dump" > backup.sql

# 从 SQL 脚本恢复
sqlite3 new_db.db < backup.sql

.dump 的特点：

  ✅ 纯文本格式，可读、可编辑、可 diff
  ✅ 跨平台兼容（不受字节序、页大小影响）
  ✅ 适合版本控制（如 Git）
  ❌ 恢复速度较慢（要重新逐条执行 SQL）
  ❌ 文件体积较大（比二进制文件大 2~5 倍）

bash

# 只导出特定表
sqlite3 my_app.db ".dump users" > users_backup.sql

# 只导出表结构（不含数据）
sqlite3 my_app.db ".schema" > schema.sql

方式三：直接复制文件

bash

# 简单粗暴——直接复制数据库文件
cp my_app.db my_app_backup.db

⚠️ 直接复制的风险：

  如果复制时有写事务正在执行，备份可能不一致！

  安全做法：
  1. 确保没有写操作（或在事务间隙复制）
  2. WAL 模式下，一起复制三个文件：
     cp my_app.db my_app_backup.db
     cp my_app.db-wal my_app_backup.db-wal
     cp my_app.db-shm my_app_backup.db-shm
  3. 或者先执行 checkpoint 再复制：
     sqlite3 my_app.db "PRAGMA wal_checkpoint(TRUNCATE);"
     cp my_app.db my_app_backup.db

方式四：在代码中使用 Backup API

python

import sqlite3

def backup_database(source_path, backup_path):
    """使用 Python sqlite3 的 Backup API 进行在线备份"""
    source = sqlite3.connect(source_path)
    backup = sqlite3.connect(backup_path)

    with backup:
        source.backup(backup, pages=100, progress=backup_progress)

    backup.close()
    source.close()

def backup_progress(status, remaining, total):
    print(f"备份进度：{total - remaining}/{total} 页")

backup_database('my_app.db', 'backup.db')

自动备份脚本

bash

#!/bin/bash
# auto_backup.sh —— 每日自动备份，保留最近 7 天

DB_PATH="/path/to/my_app.db"
BACKUP_DIR="/path/to/backups"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

# 使用 .backup 命令进行安全备份
sqlite3 "$DB_PATH" ".backup $BACKUP_DIR/backup_$DATE.db"

# 删除 7 天前的备份
find "$BACKUP_DIR" -name "backup_*.db" -mtime +7 -delete

echo "备份完成：backup_$DATE.db"

备份方式对比

方式	安全性	速度	文件大小	适用场景
`.backup`	⭐⭐⭐	快	与原库相同	生产环境首选
`.dump`	⭐⭐⭐	较慢	较大	跨平台迁移、版本控制
文件复制	⭐⭐	最快	与原库相同	确认无写入时使用
Backup API	⭐⭐⭐	快	与原库相同	程序内自动备份

10.2 数据库修复

虽然 SQLite 非常可靠，但极端情况（硬件故障、文件系统损坏）仍可能导致数据库损坏。

第一步：完整性检查

sql

-- 快速检查（检查索引一致性）
PRAGMA quick_check;

-- 完整检查（逐页验证，更彻底但更慢）
PRAGMA integrity_check;

text

-- 正常情况
sqlite> PRAGMA integrity_check;
ok

-- 有问题时
sqlite> PRAGMA integrity_check;
*** in database main ***
Page 42: btreeInitPage() fails
row 15 missing from index idx_users_email

第二步：尝试修复

方法一：.recover 命令（SQLite 3.29+，推荐）

bash

# .recover 会尽可能从损坏的数据库中提取数据
sqlite3 corrupt.db ".recover" | sqlite3 recovered.db

.recover 的工作原理：

  1. 逐页扫描数据库文件
  2. 跳过损坏的页
  3. 把能读取的数据导出为 SQL 语句
  4. 重新插入到新的数据库中

  比 .dump 更强大——.dump 在遇到损坏页时会直接报错停止，
  而 .recover 会跳过损坏部分，尽可能抢救数据。

方法二：导出再导入（传统方法）

bash

# 如果 .dump 能成功执行
sqlite3 corrupt.db ".dump" > rescue.sql
sqlite3 new.db < rescue.sql

方法三：重建索引

有时候只是索引损坏了，数据没事：

sql

-- 重建所有索引
REINDEX;

-- 重建特定表的索引
REINDEX users;

-- 重建特定索引
REINDEX idx_users_email;

预防措施

┌─────────────────────────────────────────────────┐
│          数据库损坏预防清单                        │
│                                                 │
│  ✅ 定期备份（最重要的一条！）                    │
│  ✅ 使用 WAL 模式（比 DELETE 模式更健壮）         │
│  ✅ PRAGMA synchronous 至少设为 NORMAL           │
│  ✅ 正确关闭连接（不要强制杀进程）                │
│  ✅ 不要通过 NFS 或共享文件夹访问                 │
│  ✅ 定期运行 PRAGMA integrity_check              │
│  ❌ 不要在同一个文件上运行多个写入程序            │
│  ❌ 不要手动编辑 .db 文件                        │
│  ❌ 不要在磁盘空间不足时执行写操作                │
└─────────────────────────────────────────────────┘

10.3 VACUUM —— 数据库瘦身

为什么数据库会"变胖"？

当你删除数据后，磁盘空间并不会立即归还给操作系统。
SQLite 会把已删除数据的页标记为"空闲"，留给后续使用。

  删除 10 万行数据前：文件 500 MB
  删除 10 万行数据后：文件还是 500 MB ← 空间没释放！

  这些空闲页就像搬走了家具的空房间，
  房子面积没变，但空间浪费了。

VACUUM —— 重建数据库

sql

VACUUM;

VACUUM 的工作原理：

  1. 创建一个临时的新数据库文件
  2. 把所有数据按页顺序复制到新文件
  3. 用新文件替换旧文件

  效果：
  ✅ 释放空闲的磁盘空间
  ✅ 消除文件碎片（数据重新紧凑排列）
  ✅ 重建所有索引

  代价：
  ❌ 需要临时空间（约等于当前数据库大小）
  ❌ 执行期间数据库不可用（排它锁）
  ❌ 大数据库可能需要较长时间

查看空间使用情况

bash

# 使用 dbstat 虚拟表（需要编译时启用）
sqlite3 my_app.db "
    SELECT
        SUM(pgsize) AS total_bytes,
        SUM(CASE WHEN NOT pgno IN (SELECT pgno FROM dbstat) THEN pgsize ELSE 0 END) AS free_bytes
    FROM dbstat;
"

# 更简单的方式：比较文件大小
ls -lh my_app.db                          # VACUUM 前
sqlite3 my_app.db "VACUUM;"
ls -lh my_app.db                          # VACUUM 后

VACUUM INTO —— 瘦身到新文件（SQLite 3.27+）

sql

-- 不影响原数据库，瘦身结果写到新文件
VACUUM INTO 'my_app_compact.db';

优势：不需要排它锁，原数据库可以继续使用。适合生产环境。

增量 VACUUM（auto_vacuum）

如果不想手动执行 VACUUM，可以开启自动增量 VACUUM：

sql

-- 必须在建库时设置（或在空数据库上设置）
PRAGMA auto_vacuum = INCREMENTAL;  -- 开启增量模式

-- 手动触发一次增量 VACUUM（释放 N 个空闲页）
PRAGMA incremental_vacuum(100);

auto_vacuum 模式	说明
`NONE`（默认）	不自动回收空间
`FULL`	每次删除后自动回收（有性能开销）
`INCREMENTAL`	需要手动调用 `incremental_vacuum` 触发

什么时候该 VACUUM？

┌─────────────────────────────────────────────────┐
│          VACUUM 使用建议                         │
│                                                 │
│  ✅ 大量删除数据后                               │
│  ✅ 数据库文件远大于实际数据量时                  │
│  ✅ 发布前压缩发行版数据库                       │
│  ✅ 定期维护（如每周一次）                       │
│                                                 │
│  ❌ 不要在频繁写入时执行（影响性能）              │
│  ❌ 不要在磁盘空间不足时执行                     │
│  ❌ 数据只增不删的场景不需要 VACUUM              │
└─────────────────────────────────────────────────┘

10.4 性能调优清单

PRAGMA	默认值	推荐值	说明
`journal_mode`	`delete`	`WAL`	更好的性能和并发
`synchronous`	`full`	`NORMAL`	WAL 下安全且快
`foreign_keys`	`OFF`	`ON`	启用外键检查
`busy_timeout`	`0`	`5000`	避免立即报锁错误
`mmap_size`	`0`	`268435456`	大文件读取加速
`cache_size`	`-2000`	`-64000`	更大的页缓存
`temp_store`	`DEFAULT`	`MEMORY`	临时表存内存

查询优化 Checklist

□ 用 EXPLAIN QUERY PLAN 检查是否有全表扫描
□ 为 WHERE 条件中的列建索引
□ 为 JOIN 的关联列建索引
□ 为 ORDER BY 的列建索引（可以避免 filesort）
□ 复合索引遵循最左前缀原则
□ 避免 SELECT *，只查需要的列
□ 大分页用游标分页替代 OFFSET
□ LIKE 查询以确定字符开头（'张%' 而非 '%张'）
□ 用 EXISTS 替代大结果集的 IN 子查询
□ 复杂查询用 CTE 分步提升可读性和调试性

写入优化 Checklist

□ 批量操作用 BEGIN / COMMIT 包裹事务
□ 批量插入使用 executemany 或 prepared statement
□ 大量导入时暂时禁用索引，导入后重建
□ 使用 UPSERT (ON CONFLICT) 替代 SELECT + INSERT/UPDATE
□ 控制索引数量（每个索引都会拖慢写入）
□ WAL 模式下 synchronous = NORMAL

数据库设计 Checklist

□ INTEGER PRIMARY KEY 作为 rowid 别名（最高效的主键）
□ 适当使用 NOT NULL 约束（减少 NULL 判断）
□ 合理使用索引：高选择性列 > 低选择性列
□ 考虑使用 STRICT 表确保类型安全
□ 大文本 / 二进制数据考虑存文件路径而非直接存 BLOB
□ JSON 列中频繁查询的字段提取为生成列并建索引
□ 日期用 TEXT (ISO 8601) 或 INTEGER (时间戳)

监控命令速查

sql

-- 查看数据库文件大小和页信息
PRAGMA page_size;       -- 每页大小（默认 4096 字节）
PRAGMA page_count;      -- 总页数
PRAGMA freelist_count;  -- 空闲页数

-- 数据库文件大小 = page_size × page_count
-- 可回收空间 ≈ page_size × freelist_count

-- 查看编译选项
PRAGMA compile_options;

-- 查看 SQLite 版本
SELECT sqlite_version();

-- 查看所有表和大小（需要 dbstat 虚拟表）
SELECT name, SUM(pgsize) AS size_bytes
FROM dbstat GROUP BY name ORDER BY size_bytes DESC;

本章小结

知识点	要点
备份策略	`.backup`（推荐）/ `.dump`（文本）/ 文件复制 / Backup API
数据库修复	`PRAGMA integrity_check` → `.recover` → `REINDEX`
VACUUM	释放空间 + 消除碎片；`VACUUM INTO`（3.27+）不影响原库
auto_vacuum	`INCREMENTAL` 模式 + 手动 `incremental_vacuum` 触发
性能配置	WAL + synchronous=NORMAL + busy_timeout + mmap_size + cache_size
查询优化	EXPLAIN QUERY PLAN + 索引 + 避免 SELECT * + 游标分页
写入优化	事务包裹 + executemany + 控制索引数量

下一章预告：进入最终章——SQLite 的适用边界与架构建议：什么时候该继续用 SQLite，什么时候该切换到 PostgreSQL。

第十章 运维与最佳实践 ​

10.1 备份策略 ​

方式一：.backup 命令（推荐） ​

方式二：.dump 命令（导出为 SQL 脚本） ​

方式三：直接复制文件 ​

方式四：在代码中使用 Backup API ​

自动备份脚本 ​

备份方式对比 ​

10.2 数据库修复 ​

第一步：完整性检查 ​

第二步：尝试修复 ​

预防措施 ​

10.3 VACUUM —— 数据库瘦身 ​

为什么数据库会"变胖"？ ​

VACUUM —— 重建数据库 ​

查看空间使用情况 ​

VACUUM INTO —— 瘦身到新文件（SQLite 3.27+） ​

增量 VACUUM（auto_vacuum） ​

什么时候该 VACUUM？ ​

10.4 性能调优清单 ​

推荐 PRAGMA 配置汇总 ​

查询优化 Checklist ​

写入优化 Checklist ​

数据库设计 Checklist ​

监控命令速查 ​

本章小结 ​

第十章运维与最佳实践

10.1 备份策略

方式一：.backup 命令（推荐）

方式二：.dump 命令（导出为 SQL 脚本）

方式三：直接复制文件

方式四：在代码中使用 Backup API

自动备份脚本

备份方式对比

10.2 数据库修复

第一步：完整性检查

第二步：尝试修复

预防措施

10.3 VACUUM —— 数据库瘦身

为什么数据库会"变胖"？

VACUUM —— 重建数据库

查看空间使用情况

VACUUM INTO —— 瘦身到新文件（SQLite 3.27+）

增量 VACUUM（auto_vacuum）

什么时候该 VACUUM？

10.4 性能调优清单

推荐 PRAGMA 配置汇总

查询优化 Checklist

写入优化 Checklist

数据库设计 Checklist

监控命令速查

本章小结