1. Обзор

Виртуальная таблица DBSTAT только для чтения, которая возвращает информацию о сумме дискового пространства для содержания базы данных SQLite. Варианты использования в качестве примера для виртуальной таблицы DBSTAT включают программу sqlite3_analyzer.exe и круговую диаграмму размера таблицы в системе управления версиями Fossil-implemented для SQLite.

Виртуальная таблица DBSTAT доступна на всех соединениях с базой данных, когда SQLite строится, используя выбор времени компиляции SQLITE_ENABLE_DBSTAT_VTAB.

Виртуальная таблица DBSTAT не требует CREATE VIRTUAL TABLE, чтобы создать экземпляр таблицы. Имя модуля "dbstat" может использоваться, как будто это было имя таблицы, чтобы запросить Виртуальную таблицу DBSTAT непосредственно. Например:

SELECT * FROM dbstat;

Если названная таблица, которая использует модуль dbstat, доступна, то рекомендуемый способ создать экземпляр виртуальной таблицы dbstat:

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

Отметьте определитель "temp." перед виртуальным именем таблицы ("stat"). Этот определитель заставляет виртуальную таблицу быть временной, чтобы существовать только во время текущего соединения с базой данных. Это рекомендуемый подход.

Параметр "main" dbstat это схема по умолчанию, для которой должна быть предоставлена информация. Умолчание "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, страницы карты указателей и страница блокировок опущены при анализе.

По умолчанию есть единственная строка в таблице DBSTAT для каждой btree-страницы файла базы данных. Каждая строка предоставляет информацию об использовании места той одной страницы базы данных. Однако, если скрытый столбец "aggregate" = TRUE, результаты соединены и есть единственная строка в таблице DBSTAT для каждого btree в базе данных, предоставляя информацию об использовании места всем btree.

2. Столбец "path" виртуальной таблицы dbstat

Столбец "path" описывает путь от корневого узла btree-структуры к каждой странице. "path" самого корневого узла '/'. "path" = NULL, когда "aggregate" = TRUE. "path" для крайней левой дочерней страницы корня btree-страницы '/000/'. Btrees хранит контент в порядке слева направо, так что у левых страниц есть меньшие ключи, чем у правых страниц. Следующая после крайней левой дочерней страницы корня '/001' и т.д., каждая страница родственная страница, определена шестнадцатеричным 3-значным значением. Потомок самой левой 451-й страницы имеет path вроде '/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 version 3.31.0 (2020-01-22), таблица DBSTAT имеет новый скрытый столбец "aggregate", который, если ограничено TRUE, заставит DBSTAT производить одну строку для btree в базе данных, а не одну строку на страницу. В этом случае колонки "path", "pagetype" и "pgoffset" всегда NULL, а "pageno" хранит число страниц во всем btree, а не число страниц, которое соответствует строке.

Следующая таблица показывает значения (нескрытых) колонок DBSTAT в нормальном и соединенном режимах:

4. Использование в качестве примера виртуальной таблицы dbstat

Чтобы найти общее количество страниц для хранения таблицы "xyz" в схеме "aux1", используйте любой из следующих двух запросов (первый является традиционным путем, второй использует соединение):

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;