Виртуальная таблица DBSTAT

1. Обзор

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

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

Виртуальная таблица DBSTAT является виртуальной таблицей с тем же именем, что означает, что не нужно запускать CREATE VIRTUAL TABLE для создания экземпляра виртуальной таблицы dbstat перед ее использованием. Имя модуля «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 сообщает только о содержимом b-деревьев в файле базы данных. Страницы свободного списка, страницы карты указателей и страница блокировки исключаются из анализа.

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

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

Столбец «path» описывает путь, пройденный от корневого узла структуры b-дерева до каждой страницы. Путь самого корневого узла — это «/». Путь имеет значение NULL, когда «aggregate» имеет значение TRUE. Путь для самой левой дочерней страницы корня страницы b-дерева — «/000/». (B-деревья хранят содержимое в порядке слева направо, поэтому страницы слева имеют ключи меньше, чем страницы справа.) Следующая по левому краю дочерняя страница корневой страницы — «/001», и так далее, каждая соседняя страница идентифицируется трехзначным шестнадцатеричным значением. Дочерние страницы 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-дерево в базе данных вместо одной строки на страницу. При работе в агрегированном режиме столбцы «path», «pagetype» и «pgoffset» всегда имеют значение NULL, а столбец «pageno» содержит количество страниц во всем b-дереве, а не номер страницы, соответствующий строке.

В следующей таблице показаны значения (не скрытых) столбцов 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;

Эта страница была в последний раз изменена 08.01.2022 05:02:57 UTC