原文链接：alexwlchan.net/2025/copyin…
原文作者：Alex Chan
译者：菜小鸟魔王
我保存在远程服务器上的 SQLite 数据库里存储了大量数据，经常需要将它们复制到本地机器进行分析或备份。
当新项目处于启动阶段时，数据库近乎空置，简单的 rsync 操作即可完成： rsync --progress username@server:my_remote_database.db my_local_database.db
随着项目的推进和数据库空间的增长，传输会越来越慢且不稳定。通过家庭网络从网络服务器下载 250MB 的数据库需要约 1 分钟，而这还只是很小的一部分 —— 我的数据库大多都有数 GB。
我一直在努力加快传输速度，最近发现了一个小窍门。
真正的瓶颈其实来自索引。SQLite 数据库中的大量索引虽然大大提升了查询速度，却导致数据库文件臃肿且传输速度缓慢（在某个数据库中，仅一个索引就独自霸占了整个数据库文件一半的存储空间！）
这些索引并不存储新内容 —— 只是复制其他数据表的数据来加快查询速度。重复传输索引数据会大大降低传输效率，因为相同的数据被多次传输了。在思考如何跳过传输索引时，我意识到 SQLite 其实内置了现成的解决方案。
将数据库转储为文本文件
SQLite 支持将数据库转储为文本文件。使用.dump命令时，数据库会被转写为一系列SQL语句。这类文本文件的体积通常远小于原数据库。
操作命令如下：
sqlite3 my_database.db .dump > my_database.db.txt
下面是该文件的开头：
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "tags" (
[name] TEXT PRIMARY KEY,
[count_uses] INTEGER NOT NULL
);
INSERT INTO tags VALUES('carving',260);
INSERT INTO tags VALUES('grass',743);
…
最重要的是，原本占据大量磁盘空间的索引被简化为一行文本 —— 即创建索引的指令，而非索引实体本身。
CREATE INDEX [idx_photo_locations]
ON [photos] ([longitude], [latitude]);
这意味着每个数据值在文本文件中仅存储一次，而非像原数据库那样，数据可能同时在原始表和多个索引中被重复保存多次。这就是文本文件比原始数据库文件更小的原因。
如果要重建数据库，可以将该文本文件导回 SQLite：
cat my_database.db.txt | sqlite3 my_reconstructed_database.db
由于 SQL 语句高度重复，文本文件特别适合压缩处理：
sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
以下是某数据库的磁盘空间占用对比：
| 文件类型 | 磁盘大小 |
|---|---|
| 原始 SQLite 数据库 | 3.4 GB |
| 文本文件 | 1.3 GB |
| 使用 gzip 算法压缩后的文本文件 | 240 MB |
经 gzip 压缩的文本文件比原始 SQLite 数据库小 14 倍，这使得下载数据库的速度大大加快。
升级版的 SSH+Rsync 传输命令
现在我不再直接拷贝数据库文件，而是先在服务器生成使用 gzip 压缩的转储文本文件，再将其传输到本地机器后重建数据库。具体流程如下：
# 在服务器端生成使用 gzip 算法压缩的文本文件
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
# 将压缩文件同步到本地机器（显示传输进度）
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
# 删除服务器端的压缩文件
ssh username@server "rm my_remote_database.db.txt.gz"
# 解压本地机器上的压缩文件
gunzip my_local_database.db.txt.gz
# 通过转储文件重建数据库
cat my_local_database.db.txt | sqlite3 my_local_database.db
# 删除本地转储文本文件
rm my_local_database.db.txt
数据库转储文件是可靠的静态数据源
这个方法还顺带解决了另一个问题。
当数据库体积较大导致传输耗时过长时，若中途数据库发生更新，rsync 可能生成部分旧数据+部分新数据的无效数据库文件。此时在本地机器尝试打开数据库会报错：
database disk image is malformed
而通过预先生成转储文本文件，相当于为 rsync 提供了一个稳定的传输源。该文本文件在传输期间不会被修改，确保最终获得的始终是完整且一致的数据副本。
这套方法能够使大型数据库的传输耗时锐减，同时大大提升传输可靠性。若您需要频繁操作 SQLite 数据库迁移，不妨一试。
