alexwlchan

一种更快速的计算机间复制 SQLite 数据库的方法

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

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

$rsync --progress username@server:my_remote_database.db my_local_database.db

随着项目成熟和数据库增长，这种方式变得越来越慢且不太可靠。从我的 Web 服务器下载一个 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 压缩的文本文件，将其复制到我的本地计算机，然后重建数据库。 像这样：

# 在服务器上创建一个 gzip 压缩的文本文件
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
# 将 gzip 压缩的文本文件复制到我的本地计算机
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
# 从我的服务器中删除 gzip 压缩的文本文件
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

数据库转储是稳定的复制源

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

如果复制数据库需要很长时间，并且在复制过程中数据库被更新，rsync 可能会给我一个无效的数据库文件。 文件的前半部分是更新前的，后半部分是更新后的，它们不匹配。 当我尝试在本地打开数据库时，会收到错误：

database disk image is malformed

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

这种方法为我在处理大型数据库时节省了数小时的时间，并使我的下载速度更快，更可靠。 如果你必须复制大型 SQLite 数据库，请尝试一下。