Кластеризованные индексы и оптимизация WITHOUT ROWID

1. Введение

По умолчанию каждая строка в SQLite имеет специальный столбец, обычно называемый «rowid», который уникально идентифицирует эту строку в таблице. Однако, если к оператору CREATE TABLE добавляется фраза "WITHOUT ROWID", специальный столбец "rowid" опускается. Иногда это приводит к экономии места и повышению производительности.

Таблица WITHOUT ROWID — это таблица, которая использует кластеризованный индекс в качестве первичного ключа.

1.1. Синтаксис

Для создания таблицы WITHOUT ROWID достаточно добавить ключевые слова "WITHOUT ROWID" в конец оператора CREATE TABLE. Например:

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;

Как и во всех синтаксических конструкциях SQL, регистр ключевых слов не важен. Можно написать "WITHOUT rowid", "without rowid" или "WiThOuT rOwId", и это будет означать одно и то же.

Каждая таблица WITHOUT ROWID должна иметь первичный ключ. Если в операторе CREATE TABLE с клаузой WITHOUT ROWID отсутствует первичный ключ, возникает ошибка.

В большинстве случаев специальный столбец "rowid" обычных таблиц также может называться "oid" или "_rowid_". Однако только "rowid" используется как ключевое слово в операторе CREATE TABLE.

1.2. Совместимость

Для использования таблиц WITHOUT ROWID необходима версия SQLite 3.8.2 (2013-12-06) или более поздняя. Попытка открыть базу данных, содержащую одну или несколько таблиц WITHOUT ROWID, с более ранней версией SQLite приведет к ошибке "некорректная схема базы данных".

1.3. Особенности

WITHOUT ROWID встречается только в SQLite и несовместим с другими системами управления базами данных SQL, насколько нам известно. В идеальной системе все таблицы вели бы себя как таблицы WITHOUT ROWID даже без ключевого слова WITHOUT ROWID. Однако при первоначальном проектировании SQLite использовались только целочисленные rowid для ключей строк для упрощения реализации. Этот подход хорошо работал много лет. Но по мере роста требований к SQLite возникла острая необходимость в таблицах, в которых первичный ключ действительно соответствовал основному ключу строки. Концепция WITHOUT ROWID была добавлена, чтобы удовлетворить эту потребность, не нарушая обратной совместимости с миллиардами баз данных SQLite, уже существовавших в то время (приблизительно 2013 год).

2. Отличия от обычных таблиц с rowid

Синтаксис WITHOUT ROWID — это оптимизация. Он не предоставляет новых возможностей. Всё, что можно сделать с помощью таблицы WITHOUT ROWID, можно сделать точно таким же образом и с таким же синтаксисом с помощью обычной таблицы с rowid. Единственное преимущество таблицы WITHOUT ROWID заключается в том, что она иногда использует меньше места на диске и/или работает немного быстрее, чем обычная таблица с rowid.

В большинстве случаев обычные таблицы с rowid и таблицы WITHOUT ROWID взаимозаменяемы. Но существуют некоторые дополнительные ограничения для таблиц WITHOUT ROWID, которые не применяются к обычным таблицам с rowid:

1. Каждая таблица WITHOUT ROWID должна иметь первичный ключ. Попытка создать таблицу WITHOUT ROWID без первичного ключа приводит к ошибке.

2. Специальные свойства, связанные с "INTEGER PRIMARY KEY", не применяются к таблицам WITHOUT ROWID. В обычной таблице "INTEGER PRIMARY KEY" означает, что столбец является псевдонимом для rowid. Но поскольку в таблице WITHOUT ROWID нет rowid, это специальное значение больше не применяется. Столбец "INTEGER PRIMARY KEY" в таблице WITHOUT ROWID работает как столбец "INT PRIMARY KEY" в обычной таблице: это первичный ключ, имеющий целочисленный тип данных.

3. AUTOINCREMENT не работает с таблицами WITHOUT ROWID. Механизм AUTOINCREMENT предполагает наличие rowid, поэтому он не работает с таблицей WITHOUT ROWID. Если ключевое слово "AUTOINCREMENT" используется в операторе CREATE TABLE для таблицы WITHOUT ROWID, возникает ошибка.

4. NOT NULL применяется ко всем столбцам первичного ключа в таблице WITHOUT ROWID. Это соответствует стандарту SQL. Каждый столбец первичного ключа должен быть индивидуально NOT NULL. Однако в ранних версиях SQLite ограничение NOT NULL для столбцов первичного ключа не применялось из-за ошибки. К моменту обнаружения этой ошибки настолько много баз данных SQLite было уже в обращении, что было принято решение не исправлять эту ошибку из-за опасений нарушения совместимости. Таким образом, обычные таблицы rowid в SQLite нарушают стандарт SQL и допускают NULL-значения в полях первичного ключа. Но таблицы WITHOUT ROWID следуют стандарту и выдадут ошибку при любой попытке вставить NULL в столбец первичного ключа.

5. Функция sqlite3_last_insert_rowid() не работает для таблиц WITHOUT ROWID. Вставки в таблицу WITHOUT ROWID не изменяют значение, возвращаемое функцией sqlite3_last_insert_rowid(). SQL-функция last_insert_rowid() также не затрагивается, поскольку она является просто оболочкой вокруг sqlite3_last_insert_rowid().

6. Механизм последовательного ввода-вывода BLOB не работает для таблиц WITHOUT ROWID. Механизм последовательного ввода-вывода BLOB использует rowid для создания объекта sqlite3_blob для прямого ввода-вывода. Однако у таблиц WITHOUT ROWID нет rowid, поэтому нет способа создать объект sqlite3_blob для таблицы WITHOUT ROWID.

7. Интерфейс sqlite3_update_hook() не вызывает обратные вызовы для изменений в таблице WITHOUT ROWID. Часть обратного вызова sqlite3_update_hook() — это rowid строки таблицы, которая изменилась. Однако у таблиц WITHOUT ROWID нет rowid. Следовательно, обработчик обновлений не вызывается при изменении таблицы WITHOUT ROWID.

3. Преимущества таблиц WITHOUT ROWID

Таблица WITHOUT ROWID — это оптимизация, которая может сократить требования к хранению и обработке данных.

В обычной таблице SQLite первичный ключ — это просто Уникальный индекс. Ключ, используемый для поиска записей на диске, — это rowid. Специальный тип столбца "INTEGER PRIMARY KEY" в обычных таблицах SQLite делает столбец псевдонимом для rowid, и поэтому INTEGER PRIMARY KEY — это настоящий первичный ключ. Но любой другой вид первичных ключей, включая "INT PRIMARY KEY", — это просто уникальные индексы в обычной таблице с rowid.

Рассмотрим таблицу (показанную ниже), предназначенную для хранения словаря слов вместе со счётчиком числа вхождений каждого слова в некотором текстовом корпусе:

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
);

Как обычная таблица SQLite, "wordcount" реализуется как два отдельных дерева B. Основная таблица использует скрытое значение rowid в качестве ключа и хранит столбцы "word" и "cnt" как данные. Фраза "TEXT PRIMARY KEY" оператора CREATE TABLE приводит к созданию уникального индекса на столбце "word". Этот индекс — это отдельное дерево B, использующее "word" и "rowid" в качестве ключа и не хранящее никаких данных. Обратите внимание, что полное содержимое каждого "word" хранится дважды: один раз в основной таблице и ещё раз в индексе.

Рассмотрим запрос для поиска числа вхождений слова "xsync":

SELECT cnt FROM wordcount WHERE word='xsync';

Этот запрос сначала должен выполнить поиск в индексном дереве B, ища любую запись, содержащую соответствующее значение для "word". Когда запись найдена в индексе, извлекается rowid и используется для поиска в основной таблице. Затем значение "cnt" считывается из основной таблицы и возвращается. Таким образом, для выполнения запроса требуются два отдельных двоичных поиска.

Таблица WITHOUT ROWID использует другую структуру данных для эквивалентной таблицы.

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;

В последней таблице есть только одно дерево B, которое использует столбец "word" в качестве ключа и столбец "cnt" в качестве данных. (Технически: на низком уровне реализация фактически хранит "word" и "cnt" в области "ключ" дерева B. Но если вы не рассматриваете низкоуровневое байтовое кодирование файла базы данных, этот факт несущественен.) Поскольку есть только одно дерево B, текст столбца "word" хранится только один раз в базе данных. Кроме того, запрос значения "cnt" для определённого "word" включает только один двоичный поиск в основном дереве B, поскольку значение "cnt" можно получить непосредственно из записи, найденной этим первым поиском, без необходимости второго двоичного поиска по rowid.

Таким образом, в некоторых случаях таблица WITHOUT ROWID может использовать примерно в два раза меньше места на диске и работать примерно в два раза быстрее. Конечно, в реальной схеме обычно будут вторичные индексы и/или ограничения уникальности, и ситуация более сложная. Но даже в этом случае часто бывают преимущества использования WITHOUT ROWID для таблиц с нецелочисленными или составными первичными ключами.

4. Когда использовать WITHOUT ROWID

Оптимизация WITHOUT ROWID, вероятно, будет полезна для таблиц с нецелочисленными или составными (многостолбцовыми) первичными ключами и не содержащими больших строк или BLOB-данных.

Таблицы WITHOUT ROWID будут работать правильно (т. е. они дадут правильный результат) для таблиц с единственным первичным ключом INTEGER. Однако обычные таблицы с rowid будут работать быстрее в этом случае. Поэтому хорошая практика — избегать создания таблиц WITHOUT ROWID с первичными ключами типа INTEGER из одного столбца.

Таблицы без ROWID работают лучше всего, когда отдельные строки не слишком велики. Хорошее эмпирическое правило состоит в том, что средний размер одной строки в таблице без ROWID должен быть меньше примерно 1/20 размера страницы базы данных. Это означает, что строки не должны содержать более примерно 50 байтов для размера страницы 1 КБ или около 200 байтов для размера страницы 4 КБ. Таблицы без ROWID будут работать (в том смысле, что они получат правильный ответ) для произвольно больших строк — до 2 ГБ, но традиционные таблицы с rowid обычно работают быстрее для больших размеров строк. Это связано с тем, что таблицы rowid реализованы как B*-деревья, где все содержимое хранится в листьях дерева, тогда как таблицы без ROWID реализованы с использованием обычных B-деревьев, с содержимым, хранящимся как в листьях, так и в промежуточных узлах. Хранение содержимого в промежуточных узлах приводит к тому, что каждый элемент промежуточного узла занимает больше места на странице и, следовательно, уменьшает разветвление, увеличивая затраты на поиск.

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

Обратите внимание, что за исключением нескольких отличий в особых случаях, описанных выше, таблицы без ROWID и таблицы rowid работают одинаково. Они оба генерируют одинаковые ответы при задании одних и тех же SQL-запросов. Поэтому нетрудно провести эксперименты с приложением на поздней стадии разработки, чтобы проверить, будет ли использование таблиц без ROWID полезным. Хорошая стратегия заключается в том, чтобы просто не беспокоиться о таблице без ROWID до самого конца разработки продукта, а затем вернуться и провести тесты, чтобы увидеть, помогает или вредит ли добавление таблиц без ROWID к таблицам с нецелочисленными PRIMARY KEY производительность, сохраняя таблицу без ROWID только в тех случаях, когда это помогает.

5. Определение, является ли существующая таблица таблицей без ROWID

Таблица без ROWID возвращает то же содержимое для PRAGMA table_info и PRAGMA table_xinfo, что и обычная таблица. Но в отличие от обычной таблицы, таблица без ROWID также отвечает на команду PRAGMA index_info. PRAGMA index_info таблицы без ROWID возвращает информацию о PRIMARY KEY для таблицы. Таким образом, команда PRAGMA index_info может быть использована для однозначного определения, является ли определенная таблица таблицей без ROWID или обычной таблицей — обычная таблица всегда вернет нулевые строки, а таблица без ROWID — всегда одну или несколько строк.

Эта страница была в последний раз изменена 10 октября 2023 г. в 17:29:48 UTC