集群索引和WITHOUT ROWID优化

介绍

默认情况下，每一行都有一个特殊的rowid列，用于标识一行数据。使用WITHOUT ROWID后，rowid列不会被创建，且时候有空间和性能方面的优势。
WITHOUT ROWID表使用集群索引作为主键。

语法

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

必须使用PRIMARY KEY指定主键。

兼容

3.8.2以及之后的版本可用。使用早期版本打开WITHOUT ROWID表将会报错。

rowid关键字

原文链接：https://www.sqlite.org/lang_createtable.html#rowid

不使用WITHOUT ROWID创建的表会自动创建rowid列，类型为8字节有符号整数。在访问列数据时可以通过"rowid",“oid”,"rowid"代替列名称。

如果表在创建时指定了主键只包含一个INTEGER类型的列，这个列会成为rowid列。类型必须是明确的"INTEGER”，其它整数类型的列不行。

CREATE TABLE t(x INTEGER PRIMARY KEY, y, z);

该示例中的x将作为rowid列，也就是说通过上面说明的别名可以直接检索到x列。

有一个例外就是，PRIMARY KEY后面如果紧跟DESC，也就是"PRIMARY KEY DESC"出现时，这一列不会被作为rowid列。这是一个因历史问题而保留下来的例外。

• CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
• CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
• CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

这三个示例中的x都会被当作rowid列。

• CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
  这个示例中的x不会被当作rowid列。

使用UPDATE更新rowid列时，可以使用"rowid",“oid”,“rowid”，或者被当作rowid别名的列名称。

更新一个rowid列时如果指定NULL或blob，或一个无法无损转换为整数的字符串或REAL，将会报"datatype missmatch"错误。插入时除NULL值外，其它相同处理。对于NULL值，系统会自动分配一个整数提供给rowid列。

与rowid表的区别

WITHOUT ROWID只是一个优化选项，并不提供新的能力。在有些情况下能节省空间和提高访问速度。

1. 必须要指定主键。创建一个没有主键的WITHOUT ROWID表将会报错。
2. 关于"INTEGER PRIMARY KEY"的特定行为不会被使用，因为没有rowid列。
3. AUTOINCREMENT特性不会在WITHOUT ROWID表上生效。创建表时在WITHOUT ROWID表上使用AUTOINCREMENT会报错。
4. 主键包含的每一列都会被强制应用NOT NULL特性。但是由于早期版本的BUG和历史原因，rowid表中的主键包含的列允许NULL特性存在。
5. sqlite3_last_insert_rowid()函数不能使用，因为没有rowid列。
6. incremental blob I/O 在一个表上进行增量IO操作的机制无法使用，因为其依赖于rowid列。
7. sqlite3_update_hook()设置的回调函数不会工作，因为其依赖于rowid列。

优势

减少空间和处理过程。

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

示例创建的表使用两个B-Trees存储数据。主表使用rowid作为关键字存储每一行数据，同时word索引也有一个单独的B-Trees存储word和rowid数据。当使用word查表时，先从第2个B-Trees查询rowid，再根据rowid从主表中提取数据。
在这个例子中，word列的数据被存储了2次，一是在主表，一是在索引树，检索发生了2次才完成。

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

在这个例子中，只有一个B-Trees存储索引和数据，查询操作也只需要一次就能完成。

使用WITHOUT ROWID的时机

在表没有整数类型的主键，或者有复合主键的情况下，可以考虑使用。

只有一个整数主键的WITHOUT ROWID表，正常工作是没有问题的，但速度上可能没有rowid表快。就是说，只有一个整数主键的情况下，尽量不要使用WITHOUT ROWID。

当一行数据不太大时使用WITHOUT ROWID更好。一个经验就是一行数据大小不超过数据库分页大小的1/20。例如对于1KB的分页，一行数据最好不要超过50字节，对于4KB则不要超过200字节。

当然WITHOUT ROWID表对于任意大小的行数据都是能正常工作的，只是超过上面的大小时使用rowid表在速度上会更快。
sqlite3_analyzer.exe工具可用于一个数据表的平均一行数据大小。

如何检测表是否为WITHOUT ROWID表

PRAGMA_index_info命令用于检测WITHOUT ROWID表的主键信息，对于rowid表，该命令返回空数据。

原文索引：https://www.sqlite.org/withoutrowid.html