DBSTAT 虛擬表格

1. 概觀

DBSTAT 虛擬表格是一個唯讀的同名虛擬表格，用於傳回有關使用磁碟空間儲存 SQLite 資料庫內容的資訊。DBSTAT 虛擬表格的範例使用案例包括 sqlite3_analyzer.exe 實用程式和表格大小圓餅圖，在 Fossil 實作的 SQLite 版本控制系統中。

當 SQLite 使用 SQLITE_ENABLE_DBSTAT_VTAB 編譯時間選項建置時，DBSTAT 虛擬表格會在所有資料庫連線上提供。

DBSTAT 虛擬表格是一個同名虛擬表格，表示不需要執行 CREATE VIRTUAL TABLE 來建立 dbstat 虛擬表格的執行個體，然後才能使用它。可以使用「dbstat」模組名稱，就像它是一個表格名稱一樣，直接查詢 dbstat 虛擬表格。例如

SELECT * FROM dbstat;

如果需要使用 dbstat 模組的命名虛擬表格，建議建立 dbstat 虛擬表格執行個體的方式如下

CREATE VIRTUAL TABLE temp.stat USING dbstat(main);

請注意虛擬表格名稱（「stat」）之前的「temp.」限定詞。此限定詞會導致虛擬表格成為暫時性的，只會存在於目前資料庫連線的期間。這是建議的方法。

dbstat 的「main」參數是預設的架構，用於提供資訊。預設值為「main」，因此在上述範例中使用「main」是多餘的。對於任何特定查詢，都可以在查詢的 FROM 子句中將虛擬表格名稱指定為函式參數，來變更架構。（請參閱 FROM 子句中的表格值函式的進一步討論，以取得更多詳細資料。）

DBSTAT 虛擬表格的架構如下所示

CREATE TABLE dbstat(
  name       TEXT,        -- Name of table or index
  path       TEXT,        -- Path to page from root
  pageno     INTEGER,     -- Page number, or page count
  pagetype   TEXT,        -- 'internal', 'leaf', 'overflow', or NULL
  ncell      INTEGER,     -- Cells on page (0 for overflow pages)
  payload    INTEGER,     -- Bytes of payload on this page or btree
  unused     INTEGER,     -- Bytes of unused space on this page or btree
  mx_payload INTEGER,     -- Largest payload size of all cells on this row
  pgoffset   INTEGER,     -- Byte offset of the page in the database file
  pgsize     INTEGER,     -- Size of the page, in bytes
  schema     TEXT HIDDEN, -- Database schema being analyzed
  aggregate  BOOL HIDDEN  -- True to enable aggregate mode
);

DBSTAT 表格只會報告資料庫檔案中 btrees 的內容。分析中會略過 freelist 頁面、指標對應頁面和鎖定頁面。

預設情況下，資料庫檔案中每個 btree 頁面在 DBSTAT 表格中有一列。每列提供有關資料庫中該頁面的空間使用率資訊。不過，如果隱藏欄位「aggregate」為 TRUE，則結果會聚合，且資料庫中每個 btree 在 DBSTAT 表格中有一列，提供整個 btree 的空間使用率資訊。

2. dbstat 虛擬表格的「path」欄位

「路徑」欄位描述從 btree 結構的根節點到每個頁面的路徑。根節點本身的「路徑」為「/」。當「總計」為 TRUE 時，「路徑」為 NULL。btree 頁面根的左端子節點頁面的「路徑」為「/000/」。(Btree 從左到右儲存已排序的內容，因此左邊的頁面比右邊的頁面有較小的鍵。)根頁面的左端子節點的下一層為「/001」，以此類推，每個同層頁面都以 3 位數的十六進位值識別。第 451 個左端子節點的子節點有如「/1c2/000/」、「/1c2/001/」等路徑。溢位頁面會附加一個「+」字元和一個六位數的十六進位值到它們連結的儲存格路徑中來指定。例如，從根頁面的第 450 個子節點的最左端儲存格連結的鏈結清單中的三個溢位頁面會以路徑識別

'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain

如果使用 BINARY 校對順序對路徑進行排序，則與儲存格相關聯的溢位頁面會在排序順序中出現在其子頁面之前

'/1c2/000/'               // Left-most child of 451st child of root

3. 總計資料

從 SQLite 版本 3.31.0 (2020-01-22) 開始，DBSTAT 表格有一個新的隱藏欄位，稱為「總計」，如果受限為 TRUE，DBSTAT 會針對資料庫中的每個 btree 產生一行，而不是針對每個頁面產生一行。在總計模式下執行時，「路徑」、「頁面類型」和「頁面偏移量」欄位永遠為 NULL，而「頁面編號」欄位會儲存整個 btree 中的頁面數，而不是與該列對應的頁面數。

下表顯示 DBSTAT 的（非隱藏）欄位在一般模式和總計模式下的意義

欄位一般意義聚合模式意義

名稱由目前列的 btree 實作的表格或索引名稱
路徑請參閱以上說明永遠為 NULL
頁碼目前列的資料庫頁面頁碼目前列的 btree 中總頁數
頁面類型 '葉' 或 '內部' 永遠為 NULL
儲存格數目前頁面或 btree 上的儲存格數
酬載目前頁面或 btree 上有用的酬載位元組
未用目前頁面或 btree 上未使用的位元組
最大酬載在目前頁面或 btree 中找到的任何位置的最大酬載。
頁面偏移頁面起點的位元組偏移永遠為 NULL
頁面大小目前頁面或 btree 使用的總儲存空間。

欄位	一般意義	聚合模式意義
名稱	由目前列的 btree 實作的表格或索引名稱
路徑	請參閱以上說明	永遠為 NULL
頁碼	目前列的資料庫頁面頁碼	目前列的 btree 中總頁數
頁面類型	'葉' 或 '內部'	永遠為 NULL
儲存格數	目前頁面或 btree 上的儲存格數
酬載	目前頁面或 btree 上有用的酬載位元組
未用	目前頁面或 btree 上未使用的位元組
最大酬載	在目前頁面或 btree 中找到的任何位置的最大酬載。
頁面偏移	頁面起點的位元組偏移	永遠為 NULL
頁面大小	目前頁面或 btree 使用的總儲存空間。

4. dbstat 虛擬表格範例用途

若要找出用於儲存架構 "aux1" 中表格 "xyz" 的總頁數，請使用下列兩個查詢之一（第一個是傳統方式，第二個顯示聚合功能的使用方式）

SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';
SELECT pageno FROM dbstat('aux1',1) WHERE name='xyz';

若要查看表格內容在磁碟上儲存的效率如何，請計算用於儲存實際內容的空間量除以使用的磁碟空間總量。此數字越接近 100%，封裝效率越高。（在此範例中，假設 'xyz' 表格位於 'main' 架構中。同樣地，有兩個不同的版本顯示 DBSTAT 的使用方式，分別未搭配和搭配新的聚合功能。）

SELECT sum(pgsize-unused)*100.0/sum(pgsize) FROM dbstat WHERE name='xyz';
SELECT (pgsize-unused)*100.0/pgsize FROM dbstat
 WHERE name='xyz' AND aggregate=TRUE;

若要找出表格的平均扇出，請執行

SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal';

當磁碟存取是順序時，現代檔案系統運作速度較快。因此，如果資料庫檔案的內容位於順序頁面中，SQLite 的執行速度會較快。若要找出資料庫中有哪些頁面比例是順序的（因此取得測量值，可能有助於決定何時 VACUUM），請執行類似下列的查詢

CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
  FROM s AS s1, s AS s2
 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;

此頁面最後修改於 2022-01-08 05:02:57 UTC