Внутренние таблицы SQLite

В данном посте описаны внутренние таблицы SQLite: их строение и назначение.

SQLITE_MASTER

Первая страница файла базы данных – это корневая страница «table B – tree», содержащая специальную таблицу «sqlite_master» (либо «sqlite_temp_master» для временной базы данных). Структура таблицы описана в таблице 1.



Нормализация поля SQL означает, что запрос соответствует следующим свойствам:

1. Ключевые слова прописаны заглавными буквами.
2. Ключевые слова TEMP и TEMPORARY удаляются, если идут после слова CREATE.
3. Начальные пробелы удаляются
4. Несколько пробелом преобразуются к одному

В данной таблице одна строка – это один объект базы данных. В дополнение к пользовательским объектам в «sqlite_master» хранятся и внутренние объекты базы, за исключением самой таблицы «sqlite_master». Имена внутренних объектов базы начинаются с «sqlite_», соответственно база запрещает пользователю создавать объекты, имена которых начинаются с «sqlite_».

SQLITE_SEQUENCE

Внутренняя таблица, необходимая для реализации AUTOINCREMENT. Для каждой пользовательской таблицы использующей инкремент, соответствует строка таблицы «sqlite_sequence».
Схема таблицы:
CREATE TABLE sqlite_sequence (name, seq);

1. name – имя таблицы использующей инкремент.
2. seq – значение инкремента. Увеличивается при добавлении строк в соотвествующую таблицу. При превышении максимального значения (9223372036854775807) возникает ошибка SQLITE_FULL.

Пользователь может добавлять, изменять и удалять строки из таблицы «sqlite_sequence», но не может удалять саму таблицу.

SQLITE_STAT1

Внутренняя таблица, создается с помощью команды ANALYZE. Используется для хранения справочной информации о таблицах и индексах, которой может воспользоваться планировщик для поиска эффективного способа выполнения запросов.
Схема таблицы:
СREATE TABLE sqlite_stat1 (tbl, idx, stat)

1. tbl – имя таблицы
2. ind – имя индекса
3. stat – последовательность целых чисел: первое – приблизительное число строк в таблице, второе – приблизительное среднее число строк в таблице, которые имеют одинаковое значение в первом столбце индекса, … N-ое число – приблизительное среднее число строк в таблице, которые имеют одинаковые значения в первых (N-1) столбцах индекса.

Поле stat после последовательности чисел, может содержать ключевое слово UNORDERED (отделенное от последнего числа пробелом). Если данное ключевое слово присутствует, то планировщик не использует индекс для сортировки или диапазонного запроса.
Если столбец idx равен NULL, то поле stat содержит число строк в указанной таблице.

SQLITE_STAT2

Устаревшая внутренняя таблица для версий SQLite 3.6.18 – 3.7.8. Содержала дополнительную информацию о распределении ключей.

SQLITE_STAT3

Внутренняя таблица для версий SQLite 3.7.9 и выше. Используется, если база собрана с параметром SQLITE_ENABLE_STAT3 или SQLITE_ENABLE_STAT4. Содержит дополнительную информацию о распределении ключей в пределах индекса.
Схема таблицы:
CREATE TABLE sqlite_stat3 (tbl, idx, nEq, nLt, nDLt, sample)

1. tbl – имя таблицы
2. idx – имя индекса
3. nEq – приблизительное среднее число записей в таблице, левый столбец которых равен sample.
4. nLt – приблизительное среднее число записей в таблице, левый столбец которых меньше sample.
5. nDlt – приблизительное среднее число разных записей в таблице, левый столбец которых меньше sample.
6. sample – содержит значение крайнего левого столбца индекса

SQLITE_STAT4

Внутренняя таблица для версий SQLite 3.8.1 и выше. Используется, если база собрана с параметром SQLITE_ENABLE_STAT4. Содержит дополнительную информацию о распределении ключей в пределах индекса.
Схема таблицы:
CREATE TABLE sqlite_stat4 (tbl, idx, nEq, nLt, nDLt, sample);

1. tbl – имя таблицы, idx – имя индекса
2. nEq – список чисел, где k-ое число это приблизительное число записей в таблице, в которых k левых столбцов, равны k левым столбцам индекса.
3. nLt – список чисел, где k-ое число это приблизительное число записей в таблице, в которых k левых столбцов, в совокупности, меньше k левых столбцов индекса.
4. nDLt – список чисел, где k-ое число это приблизительное число разных записей в таблице, в которых k левых столбцов, в совокупности, меньше k левых столбцов индекса.
5. sample – содержание индекса в формате записи (record format).

SQLITE_STAT3 vs SQLITE_STAT4

sqlite_stat4 является обобщением таблицы sqlite_stat3. В таблице sqlite_stat3 предоставляется информация о крайнем левом столбце индекса, тогда как таблица sqlite_stat4 предоставляет информацию обо всех столбцах индекса.

RECORD FORMAT

Формат записи (RF) определяет последовательность значений, соответствующих столбцов в таблице или индексе. RF определяет количество столбцов, тип данных каждого столбца и содержание каждого столбца. Для записи используется VARINT (целое переменной длинны, 1-9 байт в длину, метод статического кодирования Хаффмана).
Заголовок RF начинается с одного VARINT, который определяет общее число байт в заголовке (включая сам VARINT). Затем следует несколько VARINT (по одному на каждый столбец таблицы/индекса). Эти дополнительные числа называются Serial Type значения которых указаны в таблице 2. Значение каждого столбца идет сразу за заголовком.

Пример для понимания работы SQLITE_STAT1:

Допустим, имеется база данных:
CREATE TABLE test (a, b, c);
CREATE INDEX ind ON test(«a»,«b»,«c»)

Данные таблицы test:


После выполнения команды ANALYZE, поле stat для данной таблицы будет выглядеть (8 3 2 1):

1. 8 – это число строк в таблице.
2. 3 – это среднее число строк, у которых одинаковый столбец a:
   (4 + 3 + 1) / 3 = 2.67
3. 2 – это среднее число строк, у которых одинаковые столбцы a и b:
   (2 + 2 + 1) / 3 = 1.67
4. 1 – это среднее число строк, у которых одинаковые столбцы a, b и c:
   (1 + 1 + 1) / 3 = 1