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)   其它情況，則使用普通表，定義主鍵。    

SQLITE如何選擇表類型