在计算机之间复制 SQLite 数据库的更快方法

我在远程服务器上的 SQLite 数据库中存储了大量数据，并且经常想将它们复制到本地机器上进行分析或备份。

当我开始一个新项目并且数据库几乎为空时，这是一个简单的 rsync 操作：

rsync --progress username@server:my_remote_database.db my_local_database.db
随着项目的成熟和数据库的增长，这种方式变得越来越慢，可靠性也越来越低。通过我的家庭网络连接，从我的网络服务器下载一个 250MB 的数据库大约需要一分钟，这时间相当短——我的大多数数据库都几 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
文本文件（sqlite3 my_database.db .dump）    1.3 GB
gzip 压缩文本 ( sqlite3 my_database.db .dump | gzip -c)    240 MB

gzip 压缩的文本文件比原始 SQLite 数据库小 14 倍 - 这使得下载数据库的速度更快。

我的新 ssh+rsync 命令
现在，我不再直接复制数据库，而是在服务器上创建一个 gzip 压缩的文本文件，将其复制到本地计算机，然后重建数据库。如下所示：

Create a gzip-compressed text file on the server
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"

Copy the gzip-compressed text file to my local machine
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz

Remove the gzip-compressed text file from my server
ssh username@server "rm my_remote_database.db.txt.gz"

Uncompress the text file
gunzip my_local_database.db.txt.gz

Reconstruct the database from the text file
cat my_local_database.db.txt | sqlite3 my_local_database.db

Remove the local text file
rm my_local_database.db.txt

数据库转储是稳定的复制源
这种方法解决了我在复制 SQLite 数据库时遇到的另一个问题。

如果复制数据库需要很长时间，并且中途进行了更新，rsync 可能会返回一个无效的数据库文件。该文件的前半部分是更新前的数据，后半部分是更新后的数据，两者不匹配。当我尝试在本地打开该数据库时，出现以下错误：

database disk image is malformed

通过在开始复制操作之前创建文本转储，我为 rsync 提供了一个稳定的复制源。该文本转储在复制过程中不会发生变化，因此我始终可以获得完整且一致的文本文件。

这种方法在处理大型数据库时节省了我大量的时间，并使我的下载速度更快、更可靠。如果你需要在大型 SQLite 数据库之间进行复制，不妨试试这个方法。