小巧，快速，可靠。
三者选其二。
聚簇索引和 WITHOUT ROWID 优化
► 目录

1. 简介

默认情况下，SQLite 中的每一行都包含一个特殊列，通常称为 "rowid"，用于唯一标识表中的该行。但是，如果在 CREATE TABLE 语句的末尾添加 "WITHOUT ROWID" 短语，则会省略特殊的 "rowid" 列。在某些情况下，省略 rowid 会带来空间和性能优势。

WITHOUT ROWID 表是一种使用 聚簇索引 作为主键的表。

1.1. 语法

要创建 WITHOUT ROWID 表，只需在 CREATE TABLE 语句的末尾添加关键字 "WITHOUT ROWID"。例如：

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;

与所有 SQL 语法一样，关键字的大小写无关紧要。您可以写 "WITHOUT rowid" 或 "without rowid" 或 "WiThOuT rOwId"，它们的意思都一样。

每个 WITHOUT ROWID 表都必须具有 主键。如果包含 WITHOUT ROWID 子句的 CREATE TABLE 语句缺少主键，则会引发错误。

在大多数情况下，普通表的特殊 "rowid" 列也可以称为 "oid" 或 "_rowid_"。但是，只有 "rowid" 可以在 CREATE TABLE 语句中用作关键字。

1.2. 兼容性

要使用 WITHOUT ROWID 表，必须使用 SQLite 3.8.2 版（2013 年 12 月 6 日）或更高版本。尝试使用更早版本的 SQLite 打开包含一个或多个 WITHOUT ROWID 表的数据库会导致 "数据库模式格式错误" 错误。

1.3. 特性

据我们所知，WITHOUT ROWID 仅在 SQLite 中存在，与任何其他 SQL 数据库引擎都不兼容。在一个优雅的系统中，所有表都应该像 WITHOUT ROWID 表一样表现，即使没有 WITHOUT ROWID 关键字。但是，在最初设计 SQLite 时，它只使用整数 rowid 作为行键，以简化实现。这种方法多年来一直运行良好。但随着对 SQLite 的需求不断增长，对主键真正对应于底层行键的表的需要也变得更加迫切。为了满足这种需求，又不破坏当时（大约在 2013 年）已在使用的数十亿个 SQLite 数据库的向后兼容性，添加了 WITHOUT ROWID 概念。

2. 与普通 Rowid 表的区别

WITHOUT ROWID 语法是一种优化。它不提供任何新功能。任何可以使用 WITHOUT ROWID 表完成的操作，也可以使用普通 rowid 表以完全相同的方式和完全相同的语法完成。WITHOUT ROWID 表的唯一优势是，它有时可以使用更少的磁盘空间，或者比普通 rowid 表运行速度略快。

在大多数情况下，普通 rowid 表和 WITHOUT ROWID 表可以互换。但 WITHOUT ROWID 表有一些额外的限制，这些限制不适用于普通 rowid 表。

  1. 每个 WITHOUT ROWID 表都必须具有主键。尝试创建没有主键的 WITHOUT ROWID 表会导致错误。

  2. 与 "INTEGER PRIMARY KEY" 相关的特殊行为不适用于 WITHOUT ROWID 表。在普通表中，"INTEGER PRIMARY KEY" 表示该列是 rowid 的别名，因此 INTEGER PRIMARY KEY 是真正的主键。但由于 WITHOUT ROWID 表中没有 rowid，因此这种特殊含义不再适用。WITHOUT ROWID 表中的 "INTEGER PRIMARY KEY" 列就像普通表中的 "INT PRIMARY KEY" 列一样：它是一个具有整数 亲和力 的主键。

  3. AUTOINCREMENT 不适用于 WITHOUT ROWID 表。AUTOINCREMENT 机制假定存在 rowid，因此它不适用于 WITHOUT ROWID 表。如果在 WITHOUT ROWID 表的 CREATE TABLE 语句中使用 "AUTOINCREMENT" 关键字，则会引发错误。

  4. WITHOUT ROWID 表中主键的每一列都强制执行 NOT NULL。这符合 SQL 标准。主键的每一列都应该单独设置 NOT NULL。但是，由于 bug 导致早期版本的 SQLite 在主键列上没有强制执行 NOT NULL。当发现此 bug 时，已经有太多 SQLite 数据库在流通，因此为了不破坏兼容性，决定不修复此 bug。因此，SQLite 中的普通 rowid 表违反了 SQL 标准，允许主键字段中出现 NULL 值。但 WITHOUT ROWID 表符合标准，并且会对尝试将 NULL 插入主键列的任何尝试抛出错误。

  5. sqlite3_last_insert_rowid() 函数不适用于 WITHOUT ROWID 表。向 WITHOUT ROWID 表中插入数据不会更改 sqlite3_last_insert_rowid() 函数返回的值。last_insert_rowid() SQL 函数也不受影响，因为它只是 sqlite3_last_insert_rowid() 的包装器。

  6. 增量 BLOB I/O 机制不适用于 WITHOUT ROWID 表。增量 BLOB I/O 使用 rowid 创建 sqlite3_blob 对象以进行直接 I/O。但是，WITHOUT ROWID 表没有 rowid，因此无法为 WITHOUT ROWID 表创建 sqlite3_blob 对象。

  7. sqlite3_update_hook() 接口不会为 WITHOUT ROWID 表的更改触发回调。来自 sqlite3_update_hook() 的回调的一部分是已更改的表行的 rowid。但是，WITHOUT ROWID 表没有 rowid。因此，当 WITHOUT ROWID 表更改时，不会调用更新钩子。

3. WITHOUT ROWID 表的优势

WITHOUT ROWID 表是一种优化，可以减少存储和处理需求。

在普通的 SQLite 表中，主键实际上只是一个 唯一 索引。用于在磁盘上查找记录的键是 rowid。普通 SQLite 表中的特殊 "INTEGER PRIMARY KEY" 列类型会导致该列成为 rowid 的别名，因此 INTEGER PRIMARY KEY 是真正的主键。但任何其他类型的 PRIMARY KEY，包括 "INT PRIMARY KEY"，在普通的 rowid 表中都只是唯一索引。

考虑一个表（如下所示），用于存储词汇表，以及每个词在某个文本语料库中出现的次数。

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
);

作为普通的 SQLite 表，"wordcount" 由两个独立的 B 树实现。主表使用隐藏的 rowid 值作为键，并将 "word" 和 "cnt" 列存储为数据。CREATE TABLE 语句中的 "TEXT PRIMARY KEY" 短语会导致在 "word" 列上创建 唯一索引。该索引是一个独立的 B 树，使用 "word" 和 "rowid" 作为键，不存储任何数据。请注意，每个 "word" 的完整文本都存储了两次：一次在主表中，另一次在索引中。

考虑查询该表以查找单词 "xsync" 出现的次数。

SELECT cnt FROM wordcount WHERE word='xsync';

此查询首先必须搜索索引 B 树，查找包含 "word" 的匹配值的任何条目。当在索引中找到条目时，将提取 rowid 并用于搜索主表。然后，从主表中读出 "cnt" 值并返回。因此，需要执行两次独立的二进制搜索才能完成请求。

WITHOUT ROWID 表使用不同的数据设计来实现等效的表。

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;

在此表中，只有一个 B 树，它使用 "word" 列作为其键，使用 "cnt" 列作为其数据。（技术细节：底层实现实际上将 "word" 和 "cnt" 都存储在 B 树的 "key" 区域中。但除非您查看数据库文件的底层字节编码，否则这一点并不重要。）由于只有一个 B 树，因此 "word" 列的文本只在数据库中存储一次。此外，查询特定 "word" 的 "cnt" 值只需要对主 B 树进行一次二进制搜索，因为可以从第一次搜索找到的记录中直接检索 "cnt" 值，而无需对 rowid 进行第二次二进制搜索。

因此，在某些情况下，WITHOUT ROWID 表可以使用大约一半的磁盘空间，并且运行速度几乎快一倍。当然，在实际的模式中，通常会有辅助索引和/或唯一约束，情况会更加复杂。但即使那样，在具有非整数或复合主键的表上使用 WITHOUT ROWID 通常仍然可以带来空间和性能优势。

4. 什么时候使用 WITHOUT ROWID

对于具有非整数或复合（多列）主键且不存储大型字符串或 BLOB 的表，WITHOUT ROWID 优化可能会有所帮助。

对于具有单个 INTEGER 主键的表，WITHOUT ROWID 表将正常工作（也就是说，它们会提供正确的答案）。但是，在这种情况下，普通 rowid 表的运行速度会更快。因此，避免创建具有单个列主键（类型为 INTEGER）的 WITHOUT ROWID 表是一个良好的设计原则。

当单个行的大小不是太大时，WITHOUT ROWID 表的效果最佳。一个经验法则是，WITHOUT ROWID 表中单个行的平均大小应该小于数据库页面大小的 1/20。这意味着对于 1KiB 的页面大小，行的大小不应超过约 50 字节，对于 4KiB 的页面大小，行的大小不应超过约 200 字节。WITHOUT ROWID 表可以处理任意大小的行（最大可达 2GB），但传统的 rowid 表在处理大型行时往往工作速度更快。这是因为 rowid 表实现为 B* 树，所有内容都存储在树的叶节点中，而 WITHOUT ROWID 表使用普通 B 树实现，内容存储在叶节点和中间节点中。在中间节点中存储内容会导致每个中间节点条目在页面上占用更多空间，从而减少扇出，增加搜索成本。

"sqlite3_analyzer.exe" 实用程序可在 SQLite 源代码树中作为源代码获得，或者作为预编译的二进制文件在 SQLite 下载页面 上获得，可用于测量现有 SQLite 数据库中表行的平均大小。

请注意，除了上面详细说明的一些极端情况以外，WITHOUT ROWID 表和 rowid 表的工作方式相同。给定相同的 SQL 语句，它们都会生成相同的答案。因此，可以在开发周期的后期对应用程序进行简单的实验，以测试使用 WITHOUT ROWID 表是否有用。一个好的策略是，在产品开发结束之前不要担心 WITHOUT ROWID，然后回溯并运行测试，以查看在具有非整数主键的表中添加 WITHOUT ROWID 是否有助于或损害性能，并且仅在有助于性能的情况下保留 WITHOUT ROWID。

5. 如何判断现有表是否为 WITHOUT ROWID

WITHOUT ROWID 表在 PRAGMA table_info 和 PRAGMA table_xinfo 命令中返回与普通表相同的内容。但与普通表不同，WITHOUT ROWID 表也能响应 PRAGMA index_info 命令。对 WITHOUT ROWID 表执行 PRAGMA index_info 命令将返回关于该表主键的信息。通过这种方式，PRAGMA index_info 命令可以用于明确地确定某个特定表是 WITHOUT ROWID 表还是普通表 - 普通表始终返回零行，而 WITHOUT ROWID 表始终返回一行或多行。

此页面最后修改于 2023-10-10 17:29:48 UTC