【译】SQlite-性能优化之Without RowId
-
介绍
表的每一行都有一个特殊的默认列,叫做“rowid”,用来惟一标示每一行。然而,如果"WITHOUT ROWID"语法在建表语句中被添加,"rowid" 列就会被忽略。忽略rowid列可以起到优化空间和性能的作用。
1.1. 语法
在建表语句后添加WITHOUT ROWID就可以创建没有rowid的表。例如:
CREATE TABLE IF NOT EXISTS wordcount(
word TEXT PRIMARY KEY,
cnt INTEGER
) WITHOUT ROWID;
"WITHOUT ROWID"是大小写不敏感的。
每一个没有"WITHOUT ROWID"的表都要有一个"PRIMARY KEY"。如果一个CREATE TABLE语句拥有WITHOUT ROWID条件,但是没有PRIMARY KEY就会报错。
在多数情况下,"rowid"特可以被叫做"oid"、"rowid"。然而,只有"rowid"会被作为建表关键字。 -
和普通rowid表的区别
"WITHOUT ROWID"语法是一项优化。比普通rowid表相比,可以使用更少的存储、稍快地查询。
在大多数情况下,普通rowid表和"WITHOUT ROWID"表是可以交换得。但是对于"WITHOUT ROWID"地使用具有一定限制。
2.1. 对于每一个"WITHOUT ROWID"表都必须要有一个"PRIMARY KEY"。
2.2. INTEGER PRIMARY KEY表内
不要使用"WITHOUT ROWID。在普通表,"INTEGER PRIMARY KEY"意味着列是rowid的别名,在一个"WITHOUT ROWID"表里,别名会不起作用。在一个"WITHOUT ROWID"表里"INTEGER PRIMARY KEY"的表现就和普通表的"INT PRIMARY KEY"一样:作为一个整数关系的主键。
2.3. AUTOINCREMENT不能在"WITHOUT ROWID"表内正常工作。AUTOINCREMENT机制是假设"rowid"存在的情况,如果WITHOUT ROWID 表内它将不能正常工作,而且还会报错。
2.4. 在WITHOUT ROWID表内,对于作为主键的每一列都必须不能为null。
2.5. 在WITHOUT ROWID表内,sqlite3_last_insert_rowid()函数不能正常工作。
2.6. 在WITHOUT ROWID表内,incremental blob I/O 机制不能正常工作。
2.7.在WITHOUT ROWID表内,sqlite3_update_hook() 不能正常工作。 -
WITHOUT ROWID的好处
在一个普通表内,主键是唯一索引,在硬盘内查找记录的key是rowid。特定的 "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
);
作为一个普通表,wordcount由两个B-Tree组成。主表使用隐藏rowid值作为key,"word" 和"cnt"列作为data。"TEXT PRIMARY KEY"会在word列上创建unique index。索引在单独的 B-Tree上,它使用"word"和'"rowid"作为key,并不存储data。考虑到每一个"word"都被存储了两次:一次在主表,一次在索引表。
考虑以下查询语句:
SELECT cnt FROM wordcount WHERE word='xyzzy';
查询首先在索引表内查找符合word的记录,当查找到记录后就能获取rowid用来在主表中获取记录。
WITHOUT ROWID表采用了不同的数据存储设计:
CREATE TABLE IF NOT EXISTS wordcount(
word TEXT PRIMARY KEY,
cnt INTEGER
) WITHOUT ROWID;
在这张表里,它采用的是一棵B-Tree,word作为key,cnt作为data进行存储。word在数据库里只被存储里一次。在主B-Treen,根据word查找cnt仅仅涉及一次二分查找,不需要根据rowid进行二次查找。
在一些场景下,WITHOUT ROWID表可以节省一半存储空间,提高一倍查找速度。在实际情况下,通常会有二级目录和唯一约束,情况也会更加复杂,但即使如此,对于non-integer或组合主键使用WITHOUT ROWID仍然可以带来空间和性能上地提升。 -
什么时候可以使用WITHOUT ROWID
4.1. 对于non-integer 或者组合主键,并且不存储大量字符串和BLOBs的情况下适用WITHOUT ROWID。
4.2. 数据表单行大小不超过1/20数据库页大小的情况下会具有最佳的性能。