Clustered Indexes and the WITHOUT ROWID Optimization

默认情况下，SQLite中的每一行都有一个特殊的列，通常称为“rowid”，用于唯一标识表中的行。但是，如果将短语“WITHOUT ROWID”添加到CREATE TABLE语句的末尾，则省略特殊的“rowid”列。省略rowid有时有空间和性能方面的优势。

WITHOUT ROWID表是使用“ 集群索引”作为主键的表。

1.1.句法

要创建WITHOUT ROWID表，只需将关键字“WITHOUT ROWID”添加到CREATE TABLE语句的末尾即可。例如：

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

与所有SQL语法一样，关键字的情况并不重要。人们可以写出“WITHOUT rowid”或“没有rowid”或“WiThOuT rOwId”，它意味着同样的事情。

每个WITHOUT ROWID表都必须有一个PRIMARY KEY。如果带有WITHOUT ROWID子句的CREATE TABLE语句缺少PRIMARY KEY，则会引发错误。

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

1.2.兼容性

需要SQLite 版本3.8.2（2013-12-06）或更高版本才能使用WITHOUT ROWID表。尝试使用早期版本的SQLite打开包含一个或多个WITHOUT ROWID表的数据库将导致“格式错误的数据库模式”错误。

1.3.怪异模式

据我们所知，WITHOUT ROWID仅在SQLite中找到，并且与任何其他SQL数据库引擎都不兼容。在一个优雅的系统中，即使没有WITHOUT ROWID关键字，所有表的表现也会与WITHOUT ROWID表相同。但是，当首次设计SQLite时，它仅使用整数rowid作为行键以简化实现。这种方法多年来运作良好。但随着对SQLite需求的增长，PRIMARY KEY确实与底层行密钥对应的表的需求变得更加尖锐。WITHOUT ROWID概念是为了满足这种需求而添加的，而不会破坏当时已经使用的数十亿SQLite数据库（大约2013年）的向后兼容性。

WITHOUT ROWID语法是一种优化。它不提供新功能。任何可以使用WITHOUT ROWID表完成的事情也可以用完全相同的方式完成，并且使用普通的rowid表完全相同的语法。WITHOUT ROWID表的唯一优点是，它有时可以使用较少的磁盘空间和/或执行比普通的rowid表快一点。

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

1. 每个WITHOUT ROWID表都必须有一个PRIMARY KEY。尝试创建没有PRIMARY KEY的WITHOUT ROWID表会导致错误。

1. 与“INTEGER PRIMARY KEY”关联的特殊行为不适用于WITHOUT ROWID表。在普通表中，“INTEGER PRIMARY KEY”表示该列是rowid的别名。但是由于在WITHOUT ROWID表中没有rowid，所以该特殊含义不再适用。WITHOUT ROWID表中的“INTEGER PRIMARY KEY”列与普通表中的“INT PRIMARY KEY”列类似：它是具有整数亲和性的PRIMARY KEY。

1. AUTOINCREMENT 在WITHOUT ROWID表格上不起作用。AUTOINCREMENT机制假定存在一个rowid，所以它在WITHOUT ROWID表中不起作用。如果在WITHOUT ROWID表的CREATE TABLE语句中使用了“AUTOINCREMENT”关键字，则会出现错误。

1. NOT NULL在WITHOUT ROWID表中的PRIMARY KEY的每一列上执行。这符合SQL标准。PRIMARY KEY的每列应该是单独的NOT NULL。但是，由于错误，早期版本的SQLite并未在PRIMARY KEY列上强制执行NOT NULL。在发现这个错误的时候，已经有很多SQLite数据库已经在发布，决定不去修复这个bug，因为害怕破坏兼容性。所以，SQLite中的普通rowid表违反了SQL标准，并允许PRIMARY KEY字段中的NULL值。但WITHOUT ROWID表遵循标准，并会在尝试将NULL插入PRIMARY KEY列时引发错误。

1. 该 sqlite3_last_insert_rowid（） 函数不会对没有ROWID表工作。插入WITHOUT ROWID不会更改sqlite3_last_insert_rowid（）函数返回的值。last_insert_rowid（）SQL函数也不受影响，因为它只是sqlite3_last_insert_rowid（）的一个包装。

1. 该 增量BLOB I / O 机制不能对于没有ROWID表工作。增量BLOB I / O使用rowid为执行直接I / O创建sqlite3_blob对象。但是，WITHOUT ROWID表没有rowid，因此无法为WITHOUT ROWID表创建sqlite3_blob对象。

1. 该 sqlite3_update_hook（） 接口不火对于没有ROWID表变为回调。sqlite3_update_hook（）的部分回调是已更改的表行的rowid。但是，没有ROWID表没有rowid。因此，当WITHOUT ROWID表更改时，不会调用更新挂钩。

WITHOUT ROWID表是可以减少存储和处理要求的优化。

在普通的SQLite表中，PRIMARY KEY实际上只是一个UNIQUE索引。用于在磁盘上查找记录的键是rowid。普通SQLite表中的特殊“INTEGER PRIMARY KEY”列类型使该列成为rowid的别名，因此INTEGER PRIMARY KEY是真正的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”短语会导致在“单词”列上创建唯一索引。该索引是一个单独的B树，它使用“word”和“rowid”作为关键字，并且根本不存储数据。请注意，每个“单词”的完整文本都存储了两次：一次在主表中，另一次在索引中。

考虑查询此表以找出单词“xyzzy”的出现次数。

SELECT cnt FROM wordcount WHERE word='xyzzy';

此查询首先必须搜索索引B-Tree，查找包含“word”的匹配值的任何条目。在索引中找到条目时，rowid被提取并用于搜索主表。然后从主表中读出“cnt”值并返回。因此，需要两个独立的二进制搜索来完成请求。

WITHOUT ROWID表为同等表使用不同的数据设计。

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

在后面的表格中，只有一个B-Tree使用“word”列作为键，而“cnt”列作为其数据。（技术性：低级实现实际上在B-Tree的“key”区域中存储“word”和“cnt”，但除非您正在查看数据库文件的低级字节编码，否则不重要）。因为只有一个B-Tree，所以“字”列的文本只在数据库中存储一次。此外，查询特定“单词”的“cnt”值仅涉及在主B-Tree中的单个二进制搜索，因为可以直接从该第一搜索找到的记录中检索“cnt”值，而不需要在rowid上进行第二次二元搜索。

因此，在某些情况下，WITHOUT ROWID表可以使用大约一半的磁盘空间，并且可以以近两倍的速度运行。当然，在现实世界的模式中，通常会有二级索引和/或UNIQUE约束，情况会更复杂。但即使如此，在具有非整数或复合PRIMARY KEY的表上使用WITHOUT ROWID通常也可能具有空间和性能优势。

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

WITHOUT ROWID表将正确工作（也就是说，它们提供了正确的答案），使用单个INTEGER PRIMARY KEY表。但是，在这种情况下，普通的rowid表将运行得更快。因此，避免使用INTEGER类型的单列PRIMARY KEY创建WITHOUT ROWID表是很好的设计。

WITHOUT ROWID当单个行不太大时，表格的效果最好。一个好的经验法则是，WITHOUT ROWID表中的单个行的平均大小应该小于数据库页面大小的大约1/20。这意味着，对于1KiB页面大小，每行不应超过约50个字节，对于4KiB页面大小，每行不应超过约200个字节。没有ROWID的表对于任意大的行都会起作用（在他们得到正确答案的意义上） - 最大2GB - 但传统的rowid表往往对于较大的行大小工作得更快。这是因为rowid表被实现为B * -Trees，其中所有内容都存储在树的叶子中，而WITHOUT ROWID表则使用普通的B树来实现，其内容存储在树叶和中间节点上。

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

请注意，除了上面详细描述的几个角落差异之外，WITHOUT ROWID表和rowid表的工作原理是相同的。在给定相同的SQL语句的情况下，它们都生成相同的答案 因此，在开发周期的后期对应用程序运行实验是一件简单的事情，以测试使用WITHOUT ROWID表是否有帮助。一个好的策略是在产品开发接近尾声的时候不用担心WITHOUT ROWID，然后返回并运行测试，以确定将WITHOUT ROWID添加到具有非整数PRIMARY KEY的表是否有助于或损害性能，并仅保留WITHOUT ROWID在那些有帮助的情况下。

 SQLite在公共领域。