小巧、快速、可靠
任選三項
叢集索引和 WITHOUT ROWID 最佳化
► 目錄

1. 簡介

預設情況下，SQLite 中的每一列都有特殊欄位，通常稱為 "rowid"，用於在表格中唯一識別該列。但是，如果在 CREATE TABLE 陳述式的結尾加上 "WITHOUT ROWID" 字句，則會略過特殊 "rowid" 欄位。略過 rowid 有時會帶來空間和效能的優勢。

WITHOUT ROWID 表格是使用 叢集索引 作為主鍵的表格。

1.1. 語法

若要建立 WITHOUT ROWID 表格，只需在 CREATE TABLE 陳述式的結尾加上 "WITHOUT ROWID" 關鍵字即可。例如

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

與所有 SQL 語法一樣，關鍵字的大小寫無關緊要。可以寫成 "WITHOUT rowid" 或 "without rowid" 或 "WiThOuT rOwId"，意思都一樣。

每個 WITHOUT ROWID 表格都必須有 PRIMARY KEY。如果 CREATE TABLE 陳述式缺少 PRIMARY KEY，則會產生錯誤。

在多數情況下，一般表格的特殊 "rowid" 欄位也可以稱為 "oid" 或 "_rowid_"。但是，只有 "rowid" 才能在 CREATE TABLE 陳述式中用作關鍵字。

1.2. 相容性

SQLite 版本 3.8.2（2013-12-06）或更新版本是使用 WITHOUT ROWID 表格的必要條件。嘗試使用較早版本的 SQLite 開啟包含一個或多個 WITHOUT ROWID 表格的資料庫將導致「資料庫結構有誤」的錯誤。

1.3. 怪癖

據我們所知，WITHOUT ROWID 只存在於 SQLite，且與任何其他 SQL 資料庫引擎不相容。在一個優雅的系統中，所有表格都應當表現得像 WITHOUT ROWID 表格，即使沒有 WITHOUT ROWID 關鍵字。然而，當 SQLite 最初設計時，它只使用整數 rowids 作為列關鍵字，以簡化實作。這種方法多年來運作良好。但隨著對 SQLite 的需求增加，對表格的需求也越來越強烈，而 PRIMARY KEY 確實對應於基礎列關鍵字。WITHOUT ROWID 概念的加入是為了滿足這種需求，同時不破壞當時（約 2013 年）已在使用的數十億個 SQLite 資料庫的向後相容性。

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」表示該欄位是 rowid 的別名。但由於 WITHOUT ROWID 表格中沒有 rowid，因此該特殊意義不再適用。WITHOUT ROWID 表格中的「INTEGER PRIMARY KEY」欄位就像一般表格中的「INT PRIMARY KEY」欄位：它是一個具有整數 相似性 的 PRIMARY KEY。

  3. AUTOINCREMENT 不適用於 WITHOUT ROWID 表格。AUTOINCREMENT 機制假設存在 rowid，因此不適用於 WITHOUT ROWID 表格。如果在 WITHOUT ROWID 表格的 CREATE TABLE 陳述中使用「AUTOINCREMENT」關鍵字，將會產生錯誤。

  4. 在 WITHOUT ROWID 表格中，PRIMARY KEY 的每個欄位都強制套用 NOT NULL。這符合 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() 函式不適用於 WITHOUT ROWID 表格。插入 WITHOUT ROWID 並不會變更 sqlite3_last_insert_rowid() 函式傳回的值。last_insert_rowid() SQL 函式也不會受到影響，因為它只是 sqlite3_last_insert_rowid() 的包裝函式。

  6. 增量 blob I/O 機制不適用於 WITHOUT ROWID 表格。增量 BLOB I/O 使用 rowid 為直接 I/O 建立 sqlite3_blob 物件。然而，WITHOUT ROWID 表格沒有 rowid，因此無法為 WITHOUT ROWID 表格建立 sqlite3_blob 物件。

  7. sqlite3_update_hook() 介面不會對 WITHOUT ROWID 表格的變更觸發回呼。sqlite3_update_hook() 回呼的一部分是已變更表格列的 rowid。然而，WITHOUT ROWID 表格沒有 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 表格中都只是唯一索引。

考慮一個表格（如下所示），用來儲存單字彙編，以及每個單字在某個文字語料庫中出現的次數 

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

作為一般 SQLite 表格，「wordcount」實作為兩個獨立的 B 樹。主表格使用隱藏的 rowid 值作為鍵，並將「word」和「cnt」欄位儲存為資料。CREATE TABLE 陳述式的「TEXT PRIMARY KEY」片語會在「word」欄位上建立一個 唯一索引。此索引是一個獨立的 B 樹，使用「word」和「rowid」作為鍵，且完全不儲存資料。請注意每個「word」的完整文字會儲存兩次：一次在主表格中，一次在索引中。

考慮查詢此表格以找出單字「xsync」出現的次數。 

SELECT cnt FROM wordcount WHERE word='xsync';

此查詢必須先搜尋索引 B 樹，尋找包含「word」配對值的任何項目。當在索引中找到項目時，會擷取 rowid 並用來搜尋主表格。然後從主表格中讀取「cnt」值並傳回。因此，需要兩個獨立的二元搜尋才能滿足要求。

沒有 ROWID 的表格使用不同的資料設計，以取得等效的表格。 

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

在後者表格中，只有一個 B-Tree，使用「word」欄位作為其金鑰，並使用「cnt」欄位作為其資料。（技術性：低階實作實際上將「word」和「cnt」都儲存在 B-Tree 的「金鑰」區域中。但是，除非您正在檢視資料庫檔案的低階位元組編碼，否則這個事實並不重要。）因為只有一個 B-Tree，「word」欄位的文字只儲存在資料庫中一次。此外，查詢特定「word」的「cnt」值只涉及對主 B-Tree 的單一二元搜尋，因為「cnt」值可以直接從該第一次搜尋找到的記錄中擷取，而不需要對 rowid 進行第二次二元搜尋。

因此，在某些情況下，沒有 ROWID 的表格可以使用大約一半的磁碟空間，並且可以執行得快上將近兩倍。當然，在真實世界的架構中，通常會有次要索引和/或 UNIQUE 約束，而且情況會更複雜。但即使如此，在具有非整數或複合 PRIMARY KEY 的表格上使用沒有 ROWID 的表格，通常可以節省空間並提升效能。

4. 何時使用沒有 ROWID

沒有 ROWID 的最佳化可能會對具有非整數或複合（多欄位）PRIMARY KEY 且不儲存大型字串或 BLOB 的表格有所幫助。

沒有 ROWID 的表格將正確運作（也就是說，它們提供正確的答案）以取得具有單一 INTEGER PRIMARY KEY 的表格。但是，一般的 rowid 表格在這種情況下會執行得更快。因此，避免建立具有類型為 INTEGER 的單一欄位 PRIMARY KEY 的沒有 ROWID 的表格，是一個良好的設計。

不帶 ROWID 的表格在各別列不會過大的情況下能發揮最佳效能。一個好的經驗法則是不帶 ROWID 表格中單一列的平均大小應小於資料庫頁面大小的 1/20。這表示列不應包含超過 1KiB 頁面大小的 50 位元組或 4KiB 頁面大小的 200 位元組。不帶 ROWID 的表格會運作（在取得正確答案的意義上），即使列大到 2GB，但傳統的 rowid 表格在列很大的情況下往往能運作得更快。這是因為 rowid 表格是以 B*-Tree 實作的，其中所有內容都儲存在樹狀結構的葉節點中，而沒有 ROWID 的表格則是使用一般的 B-Tree 實作，內容儲存在葉節點和中間節點中。將內容儲存在中間節點會導致每個中間節點條目佔用頁面上更多的空間，因此會減少扇出，增加搜尋成本。

「sqlite3_analyzer.exe」實用程式（以原始碼形式提供在 SQLite 原始碼樹中，或以預先編譯的二進位檔形式提供在 SQLite 下載頁面 上）可用於測量現有 SQLite 資料庫中表格列的平均大小。

請注意，除了上述幾個特殊情況的差異外，不帶 ROWID 的表格和 rowid 表格運作方式相同。給定相同的 SQL 陳述式，它們都會產生相同的答案。因此，在開發週期的後期，對應用程式執行實驗是很簡單的事，用於測試是否使用不帶 ROWID 的表格會有幫助。一個好的策略是，在產品開發接近尾聲時才開始考慮不帶 ROWID，然後回頭執行測試，看看在具有非整數 PRIMARY KEY 的表格中加入不帶 ROWID 是否有助於或損害效能，並且只在有幫助的情況下保留不帶 ROWID。

5. 判斷現有資料表是否為 WITHOUT ROWID

WITHOUT ROWID 資料表會傳回與 PRAGMA table_info 和 PRAGMA table_xinfo 相同的內容，如同一般資料表。但與一般資料表不同的是，WITHOUT ROWID 也會回應 PRAGMA index_info 指令。WITHOUT ROWID 資料表的 PRAGMA index_info 會傳回資料表 PRIMARY KEY 的資訊。如此一來，PRAGMA index_info 指令可用於明確判斷特定資料表是 WITHOUT ROWID 資料表還是一般資料表 - 一般資料表永遠不會傳回任何列，但 WITHOUT ROWID 資料表永遠會傳回一列或多列。

此頁面最後修改於 2023-10-10 17:29:48 UTC