Оптимизация запросов в SQLite. Используем rowid

Во время недавней оптимизации запросов в базу данных наткнулся на описание работы SQLite с rowid. Если вкратце: в каждой таблице есть int64 столбец rowid, значение которого является уникальным для каждой записи в таблице. Посмотреть значение можно по имени «rowid» и в запросе * оно не показывается.

Записи хранятся как B-дерево по rowid. И это делает очень быстрым поиск и выборку по rowid. В два раза быстрее чем по primary key или по индексированному полю. Как я понял, поиск по индексированному столбцу — это поиск по B-дереву, в результате которого мы находим rowid. И уже имея rowid — ищем нужную запись.

Напрашивается очевидный вопрос: как сделать чтобы rowid и наш PRIMARY KEY совпадали? Очень просто, возможно у вас они уже совпадают, а вы об этом не знаете. :) Достаточно объявить ваше PRIMARY KEY поле одним из следующих способов:

CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

Есть случаи когда логика подсказывает что PRIMARY KEY и rowid должны совпадать, а это не так. Например:

CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
CREATE TABLE t(x INT PRIMARY KEY, y, z);

Второй случай особенно интересен, т.к. INT считается алиасом для INTEGER и поведение должно совпадать, а не тут-то было. Я на этой ошибке и попался. :) Если вы начали сомневаться совпадают ли у вас значения PRIMARY KEY и rowid, это можно просто проверить:

SELECT rowid, x FROM t;

SQLite в именах столбцов напишет x, x(2), т.е. вместо rowid будет указано имя столбца, с которым rowid ассоциирован. Значения, если они есть в таблице, будут совпадать для этих столбцов. Еще надо упомянуть что если в таблице уже есть столбец с именем «rowid» — ассоциации это не гарантирует, вы просто потеряете возможность запросить значение системного столбца rowid.

Приятной оптимизации.