SQLite的DBSTAT 虚拟表（三十六）

返回：SQLite—系列文章目录   

上一篇:SQLite运行时可加载扩展(三十五）

下一篇：SQLite如何处理CSV 虚拟表(三十七)

1. 概述

DBSTAT 虚拟表是一个只读的同名虚拟表，返回 有关用于存储内容的磁盘空间量的信息 的 SQLite 数据库。 示例用例 DBSTAT 虚拟表包括 sqlite3_analyzer.exe 实用程序和表大小饼图 Fossil 实现的版本控制系统 对于 SQLite。

当使用 SQLITE_ENABLE_DBSTAT_VTAB 编译时选项构建 SQLite 时，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”是多余的。对于任何 特定查询，则可以通过指定 作为虚拟表的函数参数的替代架构 name 在查询的 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 表仅报告数据库文件中 btree 的内容。 自由列表页面、指针映射页面和锁定页面被省略 分析。

默认情况下，DBSTAT 表中每个 DBSTAT 都有一行 btree 页面数据库文件。每行提供 有关该页的空间利用率的信息 数据库。但是，如果隐藏列“aggregate”为 TRUE，则 结果是聚合的，并且 DBSTAT 表中只有一行 对于数据库中的每个 btree，提供有关空间的信息 整个 btree 的利用率。

2. dbstat 虚拟表的 “path” 列

“path”列描述了从 btree 结构的根节点添加到每个页面。这 根节点本身的“路径”是“/”。 当“aggregate”为 TRUE 时，“path”为 NULL。 根目录最左边子页面的“路径” btree 页面为 '/000/'。（Btrees 存储内容从左到右的顺序 因此，左边的页面比右边的页面具有更小的键。 根页最左边的子项是 '/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 表 有一个名为“aggregate”的新隐藏列，如果将其约束为 TRUE 将导致 DBSTAT 在数据库中为每个 b tree 生成一行， 而不是每页一行。在聚合模式下运行时， “path”、“pagetype” 和 “pgoffset” 列始终为 NULL，并且 “pageno”列保存整个 btree 中的页数，而不是 比与行对应的页码。

下表显示了 （非隐藏） 列的含义 正常模式和聚合模式下的 DBSTAT：

列	正常含义	聚合模式含义
名字	实现的表或索引的名称 当前行的 btree
路径	请参阅上面的描述	始终为 NULL
页码	当前行的数据库页的页码	当前行的 btree 中的总页数
页面类型	“叶子”或“内部”	始终为 NULL
ncell（英语：ncell）	当前页面或 btree 上的单元格数
有效载荷	当前页面或 btree 上有用有效负载的字节数
闲置	当前页面或 btree 上未使用的字节
mx_payload	在当前页面中任意位置找到的最大有效负载 或 btree。
pgoffset	到页面开头的字节偏移量	始终为 NULL
pgsize的	当前页面或 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;