Small. Fast. Reliable.
Choose any three.
聚集索引和无行优化

1.简介

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

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

1.1。句法

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

如果不存在wordcount，则创建表
  文字主键，
  整数
）没有ROWID ;

与所有SQL语法一样，关键字的大小写无关紧要。可以写“ WITHOUT rowid”或“ without rowid”或“ WiThOuT rOwId”，这将意味着相同的意思。

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

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

1.2。兼容性

为了使用WITHOUT ROWID表，SQLite版本3.8.2（2013-12-06）或更高版本是必需的。尝试使用较早版本的SQLite打开包含一个或多个WITHOUT ROWID表的数据库时，将导致“格式错误的数据库架构”错误。

1.3。怪癖

据我们所知，WITHOUT ROWID仅在SQLite中找到，并且与任何其他SQL数据库引擎不兼容。在一个优雅的系统中，即使没有WITHOUT ROWID关键字，所有表也将表现为WITHOUT ROWID表。但是，当SQLite最初被设计时，它仅对行键使用整数rowid来简化实现。这种方法多年来一直有效。但是随着对SQLite的需求的增长，对真正符合主键与基础行键对应的表的需求也变得越来越迫切。添加了WITHOUT ROWID概念来满足该需求，而又不破坏与当时已使用的数十亿个SQLite数据库的向后兼容性（大约2013年）。

2.与普通Rowid表的区别

WITHOUT ROWID语法是一种优化。它没有提供任何新功能。使用WITHOUT ROWID表可以执行的任何操作，也可以使用普通的rowid表以完全相同的方式和完全相同的语法进行。WITHOUT ROWID表的唯一优点是，它有时可以比普通的rowid表使用更少的磁盘空间和/或更快地执行。

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

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

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

  3. AUTOINCREMENT在WITHOUT ROWID表上不起作用。 该AUTOINCREMENT机构呈ROWID的存在，所以它不会在无ROWID表工作。如果在CREATE TABLE语句中为WITHOUT ROWID表使用“ AUTOINCREMENT”关键字，则会引发错误。

  4. NOT NULL在WITHOUT ROWID表中的PRIMARY KEY的每一列上强制执行。 这符合SQL标准。应该将PRIMARY KEY的每一列分别设置为NOT NULL。但是，由于错误，早期版本的SQLite并未在PRIMARY KEY列上强制执行NOT NULL。在发现此错误时，已经有许多SQLite数据库在流通，以免由于担心破坏兼容性而决定不修复此错误。因此，SQLite中的普通rowid表违反了SQL标准，并在PRIMARY KEY字段中允许使用NULL值。但是WITHOUT ROWID表确实遵循该标准，并且在将NULL插入PRIMARY KEY列的任何尝试中都将引发错误。

  5. 该sqlite3_last_insert_rowid（）函数不会对没有ROWID表工作。 插入WITHOUT ROWID不会更改sqlite3_last_insert_rowid（）函数返回的值 。该last_insert_rowid（） SQL功能也丝毫不受影响，因为它仅仅是围绕着包装 sqlite3_last_insert_rowid（） 。

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

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

3. 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表中的唯一索引。

考虑一个表（如下所示），该表旨在存储单词的词汇表以及一些文本语料库中每个单词的出现次数： 

如果不存在wordcount，则创建表
  文字主键，
  整数
）;

作为普通的SQLite表，“ wordcount”被实现为两个单独的B树。主表使用隐藏的rowid值作为键，并将“ word”和“ cnt”列存储为数据。CREATE TABLE语句的“ TEXT PRIMARY KEY”短语导致在“ word”列上创建唯一索引。该索引是一个单独的B树，它使用“单词”和“ rowid”作为键，并且根本不存储任何数据。请注意，每个“单词”的全文均存储两次：一次在主表中，另一次在索引中。

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

从wordcount中选择cnt在哪里word ='xyzzy';

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

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

如果不存在wordcount，则创建表
  文字主键，
  整数
）没有行列；

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

因此，在某些情况下，WITHOUT ROWID表可以使用大约一半的磁盘空间，并且运行速度几乎快一倍。当然，在实际方案中，通常将存在二级索引和/或UNIQUE约束，并且情况更加复杂。但是即使那样，在具有非整数或复合主键的表上使用WITHOUT ROWID通常也具有空间和性能优势。

4.何时使用WITHOUT ROWID

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

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

当单个行不太大时，WITHOUT ROWID表最有效。一个好的经验法则是，WITHOUT ROWID表中单行的平均大小应小于数据库页面大小的1/20。这意味着对于1KiB页面大小，行中每个行所包含的字节数不应超过50个字节，对于4KiB页面大小，行中所包含的行数不应超过200个字节。WITHOUT ROWID表对于任意大的行（大小最大为2GB）将起作用（从某种意义上说，它们得到了正确的答案），但是传统的rowid表对于大的行大小倾向于更快地工作。这是因为rowid表被实现为B * -Trees其中所有内容都存储在树的叶子中，而WITHROW ROWID表是使用普通B树实现的，其内容存储在叶子和中间节点上。将内容存储在中间节点中意味着每个中间节点条目占用页面上的更多空间，从而减少了扇出，增加了搜索成本。

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

请注意，除了上面详述的一些特殊情况外，WITHOUT ROWID表和rowid表的工作原理相同。给定相同的SQL语句，它们都生成相同的答案。因此，很容易在开发周期的后期对应用程序进行实验，以测试使用WITHOUT ROWID表是否会有所帮助。一个好的策略是直到产品开发快结束时才担心WITHOUT ROWID，然后返回并运行测试，以查看将WITHOUT ROWID添加到具有非整数PRIMARY KEY的表中是否有助于或损害性能，并仅保留WITHOUT ROWID在那些有帮助的情况下。