Что такое таблица WITHOUT ROWID в SQLite
В SQLite каждая строка имеет столбец «rowid», который уникально идентифицирует её в таблице. Если в конце оператора CREATE TABLE добавить фразу «WITHOUT ROWID», то этот столбец опускается, что может помочь оптимизировать занимаемое место и повысить производительность.
Таблица WITHOUT ROWID — это таблица, которая использует кластеризованный индекс (clustered index) — структура, где данные хранятся непосредственно в узлах B-дерева первичного ключа — в качестве первичного ключа.
По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы
По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы
Синтаксис и совместимость
Чтобы создать таблицу 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 должна иметь PRIMARY KEY. Если оператор CREATE TABLE с предложением WITHOUT ROWID не содержит PRIMARY KEY, возникает ошибка.
В большинстве контекстов специальный столбец «rowid» обычных таблиц можно также называть «oid» или «_rowid_». Однако в операторе CREATE TABLE в качестве ключевого слова работает только «rowid»
Чтобы использовать таблицу WITHOUT ROWID, требуется версия SQLite 3.8.2 или более поздняя. Попытка открыть базу данных с таблицами WITHOUT ROWID в более ранней версии SQLite приведет к ошибке «malformed database schema».
Поскольку все таблицы в SQLite изначально использовали целочисленные rowid для идентификации строк, идеальная ситуация заключалась бы в том, чтобы все таблицы работали как таблицы WITHOUT ROWID по умолчанию. Однако с увеличением требований к SQLite возникла необходимость в более гибкой системе, где PRIMARY KEY точно соответствовал бы базовому ключу строки.
В связи с этим была введена концепция WITHOUT ROWID, позволяющая учесть эти потребности, сохраняя совместимость с существующими базами данных.
Разница между таблицами WITH ROWID и WITHOUT ROWID
Синтаксис WITHOUT ROWID — это оптимизация, а не дополнительная функциональность. Всё, что можно сделать с таблицей WITHOUT ROWID, можно выполнить аналогично с обычной таблицей с rowid. Главное отличие заключается в том, что таблицы WITHOUT ROWID иногда требуют меньшего объёма хранилища на диске и могут работать быстрее.
В большинстве случаев таблицы с rowid и таблицы WITHOUT ROWID можно использовать взаимозаменяемо. Тем не менее, на таблицы WITHOUT ROWID накладываются дополнительные ограничения, отсутствующие для обычных таблиц.
Обязательный PRIMARY KEY. Попытка создать таблицу WITHOUT ROWID без PRIMARY KEY приводит к ошибке.
Псевдоним rowid для INTEGER PRIMARY KEY не действует. В обычной таблице «INTEGER PRIMARY KEY» означает, что столбец является псевдонимом rowid. Но поскольку в таблице WITHOUT ROWID нет rowid, псевдоним rowid для INTEGER PRIMARY KEY не действует. Столбец «INTEGER PRIMARY KEY» в таблице WITHOUT ROWID работает как столбец «INT PRIMARY KEY» в обычной таблице: это PRIMARY KEY с целочисленным приведением типа (INTEGER affinity).
AUTOINCREMENT не работает. Механизм AUTOINCREMENT предполагает наличие rowid, поэтому он не работает в таблице WITHOUT ROWID. Если ключевое слово «AUTOINCREMENT» используется в операторе CREATE TABLE для таблицы WITHOUT ROWID, возникает ошибка.
NOT NULL применяется к каждому столбцу PRIMARY KEY. Это соответствует стандарту SQL. Каждый столбец PRIMARY KEY должен быть индивидуально NOT NULL. Однако NOT NULL не применялся к столбцам PRIMARY KEY в ранних версиях SQLite из-за ошибки.
К тому моменту, когда эта ошибка была обнаружена, было развёрнуто столько баз данных SQLite, что было принято решение не исправлять её во избежание нарушения совместимости. Таким образом, обычные таблицы с rowid в SQLite нарушают стандарт SQL и допускают значения NULL в полях PRIMARY KEY. Таблицы WITHOUT ROWID следуют стандарту и будут выдавать ошибку при любой попытке вставить NULL в столбец PRIMARY KEY.
Функция sqlite3_last_insert_rowid() не работает. Вставки в таблицу WITHOUT ROWID не изменяют значение, возвращаемое этой функцией. SQL-функция last_insert_rowid() также не затрагивается, поскольку является лишь обёрткой вокруг sqlite3_last_insert_rowid().
Инкрементальный ввод-вывод BLOB не работает. Инкрементальный ввод-вывод BLOB использует rowid для создания объекта sqlite3_blob, предназначенного для прямого ввода-вывода. Поскольку таблицы WITHOUT ROWID не имеют rowid, создать объект sqlite3_blob для такой таблицы невозможно.
Интерфейс sqlite3_update_hook() не вызывает обратные вызовы. Часть обратного вызова от sqlite3_update_hook() — это rowid строки таблицы, которая изменилась. Поскольку таблицы WITHOUT ROWID не имеют rowid, хук обновления не вызывается при изменении таблицы WITHOUT ROWID.
Преимущества и плюсы таблиц WITHOUT ROWID в SQLite
Таблица WITHOUT ROWID — это оптимизация, которая может снизить требования к хранению данных и их обработке. Когда я впервые столкнулся с этой возможностью, разница в производительности на реальных данных оказалась ощутимее, чем я ожидал.
В обычной таблице SQLite PRIMARY KEY — это фактически просто уникальный индекс. Ключом для поиска записей на диске служит rowid. Специальный тип столбца «INTEGER PRIMARY KEY» в обычных таблицах SQLite делает столбец псевдонимом rowid, поэтому INTEGER PRIMARY KEY является настоящим первичным ключом. Но любые другие виды 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 использует иную структуру данных для эквивалентной таблицы:
В этой таблице существует только одно B-дерево, которое использует столбец «word» в качестве ключа и столбец «cnt» в качестве данных. (Техническое уточнение: низкоуровневая реализация фактически хранит и «word», и «cnt» в области «ключа» B-дерева.
Но если вы не изучаете низкоуровневое байтовое кодирование файла базы данных, этот факт несущественен.) Поскольку существует только одно B-дерево, текст столбца «word» хранится в базе данных только один раз.
Кроме того, запрос значения «cnt» для конкретного «word» требует лишь одного бинарного поиска в главном B-дереве, поскольку значение «cnt» можно получить непосредственно из записи, найденной при первом поиске, без необходимости выполнять второй бинарный поиск по rowid.
Таким образом, в некоторых случаях таблица WITHOUT ROWID может использовать примерно вдвое меньше места на диске и работать почти вдвое быстрее. Конечно, в реальной схеме обычно присутствуют вторичные индексы и/или ограничения UNIQUE, и ситуация усложняется. Но даже в этом случае использование WITHOUT ROWID для таблиц с нецелочисленными или составными PRIMARY KEY нередко даёт преимущества в объёме занимаемого места и производительности.
Когда использовать WITHOUT ROWID
Оптимизация WITHOUT ROWID, скорее всего, окажется полезной для таблиц с нецелочисленными или составными (многостолбцовыми) PRIMARY KEY, которые не хранят большие строки или BLOB.
Таблицы WITHOUT ROWID будут работать корректно (то есть давать правильный ответ) для таблиц с единственным INTEGER PRIMARY KEY. Однако в этом случае обычные таблицы с rowid будут работать быстрее. Поэтому хорошей практикой является отказ от создания таблиц WITHOUT ROWID с одностолбцовыми PRIMARY KEY типа INTEGER.
Таблицы WITHOUT ROWID работают лучше всего, когда отдельные строки не слишком велики. Хорошее практическое правило: средний размер одной строки в таблице WITHOUT ROWID должен быть менее примерно 1/20 размера страницы базы данных. Это означает, что строки не должны содержать более примерно 50 байт каждая при размере страницы 1 КиБ или около 200 байт каждая при размере страницы 4 КиБ.
Таблицы WITHOUT ROWID будут работать (в смысле получения правильного ответа) для строк произвольно большого размера — до 2 ГБ — но традиционные таблицы с rowid, как правило, работают быстрее при большом размере строк.
Это объясняется тем, что таблицы с rowid реализованы в виде B*-деревьев, где всё содержимое хранится в листьях дерева, тогда как таблицы WITHOUT ROWID реализованы с использованием обычных B-деревьев, где содержимое хранится как в листьях, так и в промежуточных узлах.
Хранение содержимого в промежуточных узлах приводит к тому, что каждая запись промежуточного узла занимает больше места на странице, что уменьшает коэффициент ветвления и увеличивает стоимость поиска.
Утилита sqlite3_analyzer.exe, доступная в виде исходного кода в дереве исходников SQLite или в виде предварительно скомпилированного двоичного файла на странице загрузки SQLite, может использоваться для измерения среднего размера строк таблицы в существующей базе данных SQLite.
На нашем опыте эксперименты с WITHOUT ROWID лучше проводить на позднем этапе цикла разработки, когда схема уже устоялась. За исключением нескольких граничных различий, подробно описанных выше, таблицы WITHOUT ROWID и таблицы с rowid работают одинаково. При одинаковых операторах SQL они оба дают одинаковые ответы.
Поэтому не составляет труда провести эксперименты с приложением на позднем этапе цикла разработки, чтобы проверить, будет ли использование таблиц WITHOUT ROWID полезным.
Я рекомендую следующую стратегию: не беспокоиться о WITHOUT ROWID до конца разработки продукта, а затем вернуться и провести тесты, чтобы выяснить, помогает или вредит добавление WITHOUT ROWID к таблицам с нецелочисленными PRIMARY KEY, и сохранять WITHOUT ROWID только в тех случаях, когда это даёт реальные преимущества.
Как проверить, является ли таблица WITHOUT ROWID
Таблица WITHOUT ROWID возвращает то же содержимое для PRAGMA table_info и PRAGMA table_xinfo, что и обычная таблица. Но в отличие от обычной таблицы, таблица WITHOUT ROWID также отвечает на команду PRAGMA index_info. PRAGMA index_info для таблицы WITHOUT ROWID возвращает информацию о PRIMARY KEY таблицы.
Таким образом, команда PRAGMA index_info может использоваться для однозначного определения того, является ли конкретная таблица таблицей WITHOUT ROWID или обычной таблицей — обычная таблица всегда вернёт ноль строк, тогда как таблица WITHOUT ROWID всегда вернёт одну или несколько строк.
Часто задаваемые вопросы о WITHOUT ROWID
Можно ли добавить WITHOUT ROWID к уже существующей таблице? Нет. WITHOUT ROWID задаётся только при создании таблицы через CREATE TABLE. Чтобы перевести существующую таблицу на WITHOUT ROWID, нужно создать новую таблицу с нужной структурой, перенести данные и удалить старую таблицу.
Работает ли WITHOUT ROWID с составными первичными ключами? Да, и именно для составных (многостолбцовых) PRIMARY KEY WITHOUT ROWID даёт наибольший выигрыш — все столбцы ключа хранятся в одном B-дереве без дублирования в отдельном индексе.
Как проверить, является ли таблица таблицей WITHOUT ROWID? Выполните PRAGMA index_info('имя_таблицы'). Если таблица является WITHOUT ROWID, команда вернёт одну или несколько строк с информацией о PRIMARY KEY. Обычная таблица всегда вернёт ноль строк.
Влияет ли WITHOUT ROWID на поведение триггеров и внешних ключей? Триггеры и внешние ключи работают с таблицами WITHOUT ROWID так же, как и с обычными таблицами. Ограничения касаются только rowid-специфичных интерфейсов: sqlite3_last_insert_rowid(), sqlite3_update_hook() и инкрементального ввода-вывода BLOB.
При каком размере строк WITHOUT ROWID перестаёт давать преимущество? Когда средний размер строки превышает примерно 1/20 размера страницы базы данных. Например, при странице 4 КиБ это порог около 200 байт на строку. При больших строках обычные таблицы с rowid, реализованные через B*-деревья, как правило, работают быстрее.
