SQLite PRAGMA 调优 - 优化数据库性能

介绍

在这个实验中，你将探索 SQLite PRAGMA 调优，以优化数据库的性能和可靠性。你将学习如何使用 PRAGMA 语句配置 SQLite 行为的关键方面。我们将涵盖配置日志模式（journal mode）、启用外键检查（foreign key checks）、执行完整性检查（integrity checks）以及调整缓存大小（cache size）。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/key_set("Set Primary Key") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") sqlite/SQLiteGroup -.-> sqlite/check_version("Get SQLite Version") subgraph Lab Skills sqlite/init_db -.-> lab-552554{{"SQLite PRAGMA 调优"}} sqlite/key_set -.-> lab-552554{{"SQLite PRAGMA 调优"}} sqlite/get_all -.-> lab-552554{{"SQLite PRAGMA 调优"}} sqlite/query_where -.-> lab-552554{{"SQLite PRAGMA 调优"}} sqlite/verify_table -.-> lab-552554{{"SQLite PRAGMA 调优"}} sqlite/check_version -.-> lab-552554{{"SQLite PRAGMA 调优"}} end

创建数据库并配置日志模式

在这一步中，你将创建一个 SQLite 数据库并配置其日志模式（journal mode）。日志模式控制 SQLite 如何处理事务并确保数据完整性。

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

创建一个名为 test.db 的 SQLite 数据库，并使用以下命令进入 SQLite shell：

sqlite3 test.db

这个命令会创建数据库文件 test.db（如果它不存在），并打开 SQLite 命令行工具。你将看到如下提示：

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

现在，让我们配置日志模式。SQLite 提供了几种日志模式，包括 DELETE、TRUNCATE、PERSIST、MEMORY、WAL 和 OFF。WAL（Write-Ahead Logging，预写式日志）在性能和可靠性之间提供了良好的平衡。

执行以下 SQL 命令将日志模式设置为 WAL：

PRAGMA journal_mode=WAL;

这个命令配置数据库以使用预写式日志。WAL 通过在将更改应用到数据库之前将其写入单独的 WAL 文件来提高并发性和性能。

要验证日志模式，请运行：

PRAGMA journal_mode;

预期输出：

wal

这确认日志模式已设置为 WAL。

启用外键支持

在这一步中，你将在你的 SQLite 数据库中启用外键支持（foreign key support）。外键强制执行表之间的关系，并有助于维护数据完整性。

在 SQLite shell 中（如果你在上一步退出了，使用 sqlite3 test.db 重新连接），执行以下命令：

PRAGMA foreign_keys = ON;

这个命令为当前数据库连接启用外键强制执行。请注意，你必须为每个到数据库的新连接启用外键。

要验证外键支持是否已启用，请运行：

PRAGMA foreign_keys;

预期输出：

这个输出确认外键支持已启用。

创建具有外键关系的表

现在外键支持已启用，让我们创建两个具有外键关系的表来演示其功能。

执行以下 SQL 命令来创建 users 表和 orders 表：

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

这些命令创建两个表：

users：存储用户信息，包含 id（主键）和 name 列。
orders：存储订单信息，包含 id（主键）、user_id（引用 users.id 的外键）和 amount 列。

FOREIGN KEY (user_id) REFERENCES users(id) 子句在 orders 表和 users 表之间建立关系。它确保 orders 表中的 user_id 必须存在于 users 表的 id 列中。

测试外键约束

在这一步中，你将测试外键约束（foreign key constraint），看看它是如何阻止无效数据被插入到数据库中的。

首先，向 users 表中插入一个用户：

INSERT INTO users (id, name) VALUES (1, 'Alice');

这个命令将一个 id 为 1，name 为 'Alice' 的新用户插入到 users 表中。

现在，尝试向 orders 表中插入一个 user_id 在 users 表中不存在的订单：

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

因为外键检查已启用，所以这个命令将会失败，并显示一条错误消息：

Error: FOREIGN KEY constraint failed

这表明外键约束工作正常，阻止你为一个不存在的用户创建订单。

接下来，插入一个 user_id 有效的订单：

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

这个命令将会成功，因为 user_id 1 存在于 users 表中。

执行完整性检查

在这一步中，你将对你的 SQLite 数据库执行完整性检查（integrity check），以确保没有不一致或错误。

在 SQLite shell 中执行以下命令：

PRAGMA integrity_check;

这个命令会检查整个数据库是否存在任何不一致或错误。如果数据库是健康的，输出将是 ok。

预期输出：

ok

如果数据库已损坏，输出将包含错误消息，指示损坏的性质和位置。

调整缓存大小并退出

在最后一步中，你将调整 SQLite 使用的缓存大小，然后退出 SQLite shell。

缓存大小决定了 SQLite 使用多少内存来存储最近访问的数据。增加缓存大小可以提高性能，尤其是在读取密集型工作负载（read-heavy workloads）的情况下。

执行以下命令将缓存大小设置为 4MB（4096 KB）：

PRAGMA cache_size = 4096;

此命令将当前数据库连接的缓存大小设置为 4MB。

要验证缓存大小，请运行：

PRAGMA cache_size;

预期输出：

最后，退出 SQLite shell：

.exit

此命令关闭数据库连接并返回到命令行。

总结

在这个实验中，你探索了 SQLite PRAGMA 调优。你学习了如何配置日志模式（journal mode）以提高性能和可靠性，启用外键支持以强制数据完整性，创建具有外键关系的表，测试外键约束，执行完整性检查以识别数据库损坏，以及调整缓存大小以优化内存使用。这些技能为有效地使用 SQLite 数据库提供了坚实的基础。