1. Введение

По умолчанию у каждой строки в SQLite есть специальная колонка, обычно названная "rowid", которая однозначно определяет строку в таблице. Однако, если фраза "WITHOUT ROWID" добавляется до конца CREATE TABLE, то специальная колонка "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 должна иметь PRIMARY KEY. Ошибка поднята, если CREATE TABLE с WITHOUT ROWID не имеет PRIMARY KEY.

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

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

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

1.3. Причуды

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

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

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

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

1. У каждой таблицы WITHOUT ROWID должен быть PRIMARY KEY. Попытка создать WITHOUT ROWID без PRIMARY KEY приводит к ошибке.

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

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

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

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

6. Механизм incremental blob I/O не работает с таблицами WITHOUT ROWID. Incremental BLOB I/O использует rowid, чтобы создать объект sqlite3_blob для того, чтобы сделать прямой I/O. Однако, у таблиц 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 PRIMARY KEY действительно просто индекс UNIQUE. Ключ, используемый, чтобы искать записи на диске, является rowid. Специальный тип колонки " INTEGER PRIMARY KEY" в обычных таблицах SQLite заставляет колонку быть псевдонимом для rowid, и таким образом, INTEGER PRIMARY KEY является истинный 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 использует иной дизайн данных для эквивалентной таблицы.

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

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

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

4. Когда использовать 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*-Trees, где все содержание сохранено в листьях дерева, тогда как WITHOUT ROWID осуществляется, используя обычные B-деревья с содержанием, сохраненным на обоих листьях и промежуточных узлах. Хранение содержания в промежуточных узлах заставляет каждый промежуточный вход узла занимать больше места на странице и таким образом уменьшает разветвление, увеличивая стоимость поиска.

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

Обратите внимание на то, что за исключением нескольких различий выше WITHOUT ROWID и таблицы rowid работают одинаково.

5. Определение, если существующая таблица 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 будет всегда возвращать одну или несколько.