你的位置:首页 > 数据库

[数据库]SQLITE如何选择表类型


     Sqlite中表类型主要有普通表,以及WITHOUT ROWID表以及临时表。临时表一般是是查询需要临时存储结果使用。这篇文章主要讨论普通表和WITHOUT ROWID表的区别,以及AUTOINCREMENT的特性。

1.rowid的类型是什么?

      sqlite中rowid是一个隐身存储的列,8个字节存储,它有两个别名 _ROWID_ 和 OID,类型定义为INTEGER PRIMARY KEY,因此当用户建表时,某列定义为 INTEGER PRIMARY KEY,实质是rowid的别名。rowid是自增的,当该列插入null时, 会取当前表的最大值+1,作为该列的值。注意INTEGER与int不同,比如若列定义为 int primary key, 则插入null值,该列的值就是null,rowid依然会递增。查询可以通过select rowid from tablename得到rowid的值。 

2.INTEGER PRIMARY KEY AUTOINCREMENT 和不指定自增长字段用rowid区别?

1)   AUTOINCREMENT属性只能用于定义为INTEGER PRIMARY KEY的列,否则建表时会报错。

2)   不使用自增 长的rowid始终取当前最大值+1,若删除了最大rowid所在的记录,导致这个rowid会重用。 采用AUTOINCREMENT属性可以避免重用情况,系统内部通过sqlite_sequence表来维护每个表的最大sequence值, 因此即使有删除情况,也不会导致rowid重用,严格单调递增,代价时执行插入时, 需要维护sqlite_sequence表,对性能有一定的损耗。 由于rowid采用8个字节存储,因此上限值为9223372036854775807,当超过这个值时,rowid属性会随机选择一个值, 只要不与表中已有记录冲突即可;而AUTOINCREMENT属性则会提示Error: database or disk is full。

3)   vacuum命令与rowid  

网上有文章说使用vacuum命令,会导致rowid重新分配使用,比如原来rowid是1,3,5的3条记录,经过vacuum后, 会重组为1,2,3。本人亲自测试后,并没有重现。

3.WITHOUT ROWID

1) 普通表的PRIMRAY KEY实质是一个唯一索引,表数据按rowid组织(聚集索引), 通过主键访问表,实质需要访问唯一索引和聚簇索引,但对于INTEGER PRIMARY KEY除外, 它是rowid的一个别名,索引实质就是聚簇索引。

2)  B树,B-树,B+树,B*树有啥区别? B树是二叉查找树,B代表binary;而后面的B-,B+,B*则代表balance。在数据库领域讨论的B树,都注意是后面三种。 从数据结构来说,B-树中叶子节点和非叶子节点内容结构相同,查找可能在非叶子节点找到数据,直接返回;而B+树中,每 一次查找都需要找到叶子节点,key信息在叶子节点和非叶子节点存储了两遍,叶子节点包含了所有key信息,并且节点之间 有双向指针相连;B*树相对于B+树区别是非叶子节点兄弟之间也有双向指针相连。

3)  WITHOUT ROWID采用B-Tree,叶子节点和非叶子节点都有记录所有内容, 因此若记录较长(超过page_size*1/20),不适合使用WITHOUT ROWID属性, 容易造成节点频繁分裂。WITHOUT ROWID 表只有一颗B-树,访问只需要访问一次B-树, 而普通表需要访问两次(索引+表),对于INTEGER PRIMARY KEY除外。 

4) WITHOUT ROWID不支持AUTOINCREMENT属性,并且PRIMARY KEY不能为null,普通表比较变态,PRIMARY KEY 属性列也可以为null(由于历史原因,没有修改)。 

4.如何选择表类型?

1)   若主键为整型,采用普通表,将列定义为INTEGER PRIMARY KEY,这样保证只有只有1颗B*树,提高查询效率;

2)   若主键为非整型,记录比较小(不超过page_size*1/20),并且不依赖于rowid的逻辑序号,可以考虑使用WITHOUT ROWID表,节省空间 的同时,提高查询效率

3)   其它情况,则使用普通表,定义主键。