Базы данных для начинающих. Внутренние индексы SQL таблицы. Таблицы WITHOUT ROWID в SQLite.

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zametki-o-mysql/chast-11-7-indeksy-v-bazax-dannyx-sqlite-indeksaciya-tablic-v-sqlite3-algoritm-b-dereva-v-bazax-dannyx.html
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

— Столбец ROWID или внутренние индексы таблицы в базе данных SQLite

— У каждой таблицы базы данных SQLite по умолчанию есть внутренний индекс, который называет ROWID. Иногда этот индекс совпадает с первичным ключом таблицы PRIMARY KEY, иногда не совпадает, но ROWID есть у каждой таблицы. Если говорить просто и понятно, то ROWID – это дополнительный столбец, который есть у любой таблицы SQLiteи является индексом для данной таблицы.
— Помимо того, что ROWID – это индекс, это еще один способ поддержание целостности данных. Давайте разберемся с индексом ROWID в базах данных SQLite. ROWID–это 64-разрядное число, которое однозначно идентифицирую любую строку в базе данных SQLite.

— Создадим таблицу без индексов и наполним ее данными
CREATE TABLE table1(a, b ,c ,d);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);

— посмотрим содержимое таблицы
SELECT*FROM table1;

— SQLite не показывает столбец ROWID, если не обращаться к нему по имени, давайте изменим наш запрос SELECT

SELECT rowid, a, b, c, d FROM table1;
SELECT rowid, a FROM table1;

— Чтобы первичный ключ совпадал с ROWID, нужно создать таблицу следующим образом
CREATE TABLE table1(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE table2(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE table3(x INTEGER, y, z, PRIMARY KEY(x DESC));

— Обратите внимание: если столбец PRIMARY KEY объявить как-нибудь по-другому, то значения PRIMARY KEY и ROWID совпадать не будут.

— Таблицы WITHOUT ROWID в базах данных SQLite, таблицы без внутренних индексов

— В SQLite можно создавать таблицы без столбца ROWID, то есть без внутреннего индекса. Такой подход несет незначительные плюсы: иногда это ускоряет выборку данных из базы данных и немного уменьшает объем базы данных. Если вы решили создать таблицу без ROWID в SQLite, то вам необходимо сделать две вещи: во-первых, у таблицы должен быть обязательно первичный ключ, во-вторых, вам необходимо использовать ключевую фразу WITHOUT ROWID.

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

INSERT INTO wordcount (word, cnt)
VALUES (‘word1’, 2);
INSERT INTO wordcount (word, cnt)
VALUES (‘word2’, 5);

INSERT INTO wordcount (word, cnt)
VALUES (NULL, 5);

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

INSERT INTO wordcount1 (word, cnt)
VALUES (NULL, 5);

— Теперь у таблицы не будет столбца ROWID. Еще одной особенностью таблиц WITHOUT ROWID является то, что ограничение уровня столбца AUTOINCREMENT работать не будет. Первичный ключ таблицы WITHOUT ROWID ни при каких обстоятельствах не может иметь значение NULL, если вы попробуете добавить NULL в столбец PRIMARY KEY таблицы WITHOUT ROWID, то SQLite выдаст ошибку.

Из-за особенностей работы таблиц WITHOUT ROWID, разработчики SQLiteрекомендуют использовать такие таблицы в тех случаях, когда:

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

Если вам нужно повысить скорость работы таблицы с первичным ключом INTEGER, создавайте таблицу без внутреннего индекса, этим вы ускорите выборку и уменьшите объем базы данных. Если таблицы имеет индекс ROWID и индекс в виде INTEGER PRIMARY KEY, то SQLIte делает перебор двумя циклами: первый цикл идет по столбцу ROWID, второй цикл идет по столбцу INTEGER PRIMARY KEY, хотя значения этих столбцов совпадают.

Если в таблицах хранится небольшое количество строк, то таблицы без индекса (WITHOUT ROWID) будут работать несколько быстрее, чем со столбцом ROWID.

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