LabEx
登录免费加入

SQLite 数据库维护

SQLiteSQLiteBeginner
立即练习

💡 本教程由 AI 辅助翻译自英文原版。如需查看原文，您可以 切换至英文原版

介绍

在这个实验中，你将学习如何维护你的 SQLite 数据库以获得最佳性能。我们将介绍一些关键技术，例如使用 VACUUM 命令来回收空间，重建索引以加速查询，以及分析表统计信息以帮助 SQLite 做出更好的决策。让我们开始吧！

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/drop_row("Delete Single Row") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") sqlite/SQLiteGroup -.-> sqlite/clear_index("Drop Single Index") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") subgraph Lab Skills sqlite/make_table -.-> lab-552549{{"SQLite 数据库维护"}} sqlite/add_rows -.-> lab-552549{{"SQLite 数据库维护"}} sqlite/drop_row -.-> lab-552549{{"SQLite 数据库维护"}} sqlite/build_index -.-> lab-552549{{"SQLite 数据库维护"}} sqlite/clear_index -.-> lab-552549{{"SQLite 数据库维护"}} sqlite/verify_table -.-> lab-552549{{"SQLite 数据库维护"}} end

创建数据库和示例表

在我们深入研究维护之前，让我们创建一个 SQLite 数据库和一个包含一些数据的示例表。这将为我们提供一些可操作的内容。

首先，在 LabEx VM 中打开你的终端。你的默认路径是 /home/labex/project。

要创建一个名为 mydb.db 的 SQLite 数据库，请运行以下命令：

sqlite3 mydb.db

这个命令将创建数据库文件（如果它不存在）并打开 SQLite 命令行工具，将你连接到数据库。你将看到 sqlite> 提示符。

现在，让我们创建一个名为 users 的表来存储用户信息。执行以下 SQL 命令：

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);

这个命令创建一个包含三列的表：id（一个唯一标识每个用户的整数）、name（用户的姓名）和 email（用户的电子邮件地址）。PRIMARY KEY 约束确保每个 id 都是唯一的。

接下来，让我们将一些示例数据插入到 users 表中：

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
INSERT INTO users (name, email) VALUES ('Charlie', '[email protected]');

这些命令向 users 表添加三行数据。

要验证表和数据是否已成功创建，请运行以下命令：

SELECT * FROM users;

预期输出：

1|Alice|[email protected]
2|Bob|[email protected]
3|Charlie|[email protected]

此输出显示 users 表的内容。

模拟数据删除

为了演示 VACUUM 的效果，我们需要模拟数据删除，这可能会导致数据库文件中的碎片（fragmentation）。

让我们从 users 表中删除一行：

DELETE FROM users WHERE id = 2;

这个命令删除 id 为 2 的行（Bob 的记录）。

要确认删除，请运行以下命令：

SELECT * FROM users;

预期输出：

1|Alice|[email protected]
3|Charlie|[email protected]

你会注意到 Bob 的记录已不在表中。此删除操作会在数据库文件中留下一个空隙，VACUUM 可以帮助解决这个问题。

运行 VACUUM 回收空间

现在，让我们使用 VACUUM 命令来整理数据库文件，并回收已删除记录留下的空间。

在 SQLite shell 中执行以下命令：

VACUUM;

这个命令会重写整个数据库文件，整合数据并删除空白空间。

运行 VACUUM 后，你不会看到任何特定的输出，但它会在后台优化你的数据库。

要退出 SQLite shell，请运行：

.exit

你现在回到了你的终端。

创建索引

索引对于加速查询至关重要。让我们在 users 表的 email 列上创建一个索引。

首先，再次连接到 SQLite 数据库：

sqlite3 mydb.db

现在，使用以下命令创建索引：

CREATE INDEX idx_users_email ON users (email);

这个命令在 email 列上创建一个名为 idx_users_email 的索引。SQLite 将使用此索引根据用户的电子邮件地址快速查找用户。

重建索引

随着时间的推移，索引可能会变得碎片化，尤其是在多次数据修改之后。重建索引可以提高其效率。

要重建我们刚刚创建的索引，请使用 REINDEX 命令：

REINDEX idx_users_email;

这个命令会重建 idx_users_email 索引，确保它针对当前数据进行了优化。

要退出 SQLite shell，请运行：

.exit

你现在回到了你的终端。

分析表统计信息

SQLite 使用关于你的数据的统计信息来优化查询。定期更新这些统计信息是一个好习惯。

首先，再次连接到 SQLite 数据库：

sqlite3 mydb.db

要分析 users 表，请运行以下命令：

ANALYZE users;

这个命令收集关于 users 表的统计信息，查询优化器可以使用这些信息来提高查询性能。

最后，退出 SQLite shell：

.exit

你现在回到了你的终端。

总结

在这个实验（Lab）中，你已经学习了如何在你的 SQLite 数据库上执行必要的维护任务。你使用了 VACUUM 命令来回收空间，创建并重建索引以提高查询性能，并分析了表统计信息以帮助 SQLite 优化查询。这些技术对于保持你的数据库平稳高效运行至关重要。