Частичные индексы в SQLite: оптимизация базы данных

Что такое частичный индекс

Частичный индекс — это индекс по подмножеству строк таблицы.

Частичные индексы отличаются от обычных тем, что они включают лишь часть строк таблицы. Например, частичный индекс может исключать записи, где индексируемый столбец равен NULL. Правильное использование таких индексов может значительно снизить размер файла базы данных и повысить скорость выполнения запросов и операций записи.

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы.

Создание частичного индекса: синтаксис CREATE INDEX с WHERE

Создайте частичный индекс, добавив предложение WHERE в конец обычного оператора CREATE INDEX.

Синтаксис оператора CREATE INDEX с предложением WHERE:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] [schema-name .] index-name ON table-name ( indexed-column [, ...] ) WHERE expr

Любой индекс, включающий предложение WHERE в конце, считается частичным индексом. Индексы, в которых предложение WHERE отсутствует (или индексы, создаваемые ограничениями UNIQUE или PRIMARY KEY внутри операторов CREATE TABLE), являются обычными полными индексами

Выражение, следующее за предложением WHERE, может содержать операторы, литеральные значения и имена столбцов индексируемой таблицы. Предложение WHERE не может содержать подзапросы, ссылки на другие таблицы, недетерминированные функции или связанные параметры (bind parameters).

В индекс включаются только те строки таблицы, для которых предложение WHERE принимает значение TRUE. Если выражение предложения WHERE принимает значение NULL или FALSE для некоторых строк таблицы, то эти строки исключаются из индекса.

Столбцы, на которые ссылается предложение WHERE частичного индекса, могут быть любыми столбцами таблицы, а не только теми, которые оказались проиндексированными. Однако очень часто выражение предложения WHERE частичного индекса представляет собой простое выражение над индексируемым столбцом. Ниже приведён типичный пример:

CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;

При отсутствии «родительского» заказа большинство значений поля parent_po будут NULL. Это приводит к тому, что индекс будет включать только ограниченное количество строк из таблицы purchaseorder, что способствует экономии места. Более того, обновления в этой таблице будут выполняться быстрее, поскольку будут затрагиваться лишь те строки, где parent_po не равен NULL.

SELECT po_num FROM purchaseorder WHERE parent_po=?1;

Запрос будет использовать индекс po_parent для поиска, так как индекс содержит записи для всех нужных строк. Так как размер индекса po_parent меньше, чем полный индекс, запрос выполнится быстрее.

Уникальные частичные индексы

Частичный индекс может включать ключевое слово UNIQUE, указывающее на необходимость уникальности каждой записи в индексе. Это дает возможность обеспечивать уникальность в определенных подмножествах строк таблицы, которые соответствуют заданному условию.

Данный механизм полезен, например, в управлении членами команды. Если у вас есть база данных, где каждый человек принадлежит определённой «команде», и каждая команда имеет своего «лидера», можно создать уникальный индекс по team_id. Однако он будет действовать только для тех записей, где is_team_leader равно true, что обеспечит уникальность лидера для каждой команды

CREATE TABLE person( person_id INTEGER PRIMARY KEY, team_id INTEGER REFERENCES team, is_team_leader BOOLEAN, -- другие поля опущены
);

Поле team_id не может быть уникальным, поскольку в одной команде обычно несколько человек. Нельзя также сделать уникальной комбинацию team_id и is_team_leader, поскольку в каждой команде обычно несколько не-лидеров. Решением для обеспечения одного лидера на команду является создание уникального индекса по team_id, но ограниченного теми записями, для которых is_team_leader равно true:

CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;

Попутно заметим, что тот же индекс полезен для нахождения лидера конкретной команды.

Как SQLite выбирает частичный индекс для оптимизации запроса

С формулировками частичного индекса разрешено использовать запрос, если выражение W соответствует X, где W — это выражение в запросе, а X — в предложении WHERE частичного индекса. Это означает, что определение возможности использования частичного индекса сводится к проверке логического вывода.

SQLite не располагает сложным средством доказательства теорем для определения W⇒X. Вместо этого SQLite использует два простых правила для нахождения распространённых случаев, когда W⇒X истинно, и предполагает, что все остальные случаи ложны

Правило 1. Если W состоит из термов, соединённых через AND, а X — из термов, соединённых через OR, и если какой-либо терм W встречается как терм X, то частичный индекс применим. Например, пусть индекс определён как:

CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;

А запрос выглядит так:

SELECT * FROM tab1 WHERE b=6 AND a=7; -- использует частичный индекс

Тогда индекс применим для этого запроса, поскольку терм b=6 присутствует как в определении индекса, так и в запросе. Помните: термы в индексе должны быть соединены через OR, а термы в запросе — через AND. Термы в W и X должны совпадать точно. SQLite не выполняет алгебраических преобразований, чтобы привести их к одному виду. Терм b=6 не совпадает с b=3+3, b-6=0 или b BETWEEN 6 AND 6.

При этом b=6 совпадёт с 6=b при условии, что в индексе указано b=6, а в запросе — 6=b. Если в индексе встречается терм вида 6=b, он никогда ни с чем не совпадёт.

Правило 2. Если терм в X имеет вид z IS NOT NULL и если терм в W является оператором сравнения над z, отличным от IS, то эти термы совпадают. Пример: пусть индекс определён как:

CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;

Тогда любой запрос, использующий операторы =, <, >, <=, >=, <>, IN, LIKE или GLOB над столбцом c, сможет использовать частичный индекс, поскольку эти операторы сравнения истинны только тогда, когда c не равно NULL. Таким образом, следующий запрос может использовать частичный индекс:

SELECT * FROM tab2 WHERE b=456 AND c<>0; -- использует частичный индекс

Но следующий запрос не может использовать частичный индекс:

SELECT * FROM tab2 WHERE b=456; -- не может использовать частичный индекс

Последний запрос не может использовать частичный индекс, поскольку в таблице могут существовать строки с b=456, где c равно NULL. Но такие строки не будут включены в частичный индекс.

Эти два правила описывают работу планировщика запросов SQLite на момент написания данного текста (2013-08-01). Указанные правила будут соблюдаться всегда. Однако будущие версии SQLite могут включать более совершенное средство доказательства теорем, способное находить другие случаи, когда W⇒X истинно, и тем самым обнаруживать больше ситуаций, в которых частичные индексы оказываются полезными.

Типичные ошибки при работе с частичными индексами

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

Несовпадение термов из-за алгебраических преобразований. SQLite сравнивает термы буквально. Если в индексе написано b=6, а в запросе — b=3+3, планировщик не распознает совпадение и не воспользуется частичным индексом. Пишите условия в запросе ровно так же, как они записаны в определении индекса.

Использование недетерминированных функций в предложении WHERE. Функции вроде random() или datetime('now') запрещены в предложении WHERE частичного индекса. SQLite отклонит такой CREATE INDEX с ошибкой.

Ожидание, что частичный индекс покроет строки с NULL. Строки, для которых выражение WHERE вычисляется как NULL, исключаются из индекса. Если запрос должен находить в том числе строки с NULL в индексируемом столбце, частичный индекс с условием IS NOT NULL не подойдёт — нужен полный индекс.

Попытка открыть файл базы данных с частичными индексами в старой версии SQLite. Файлы баз данных, содержащие частичные индексы, не могут быть прочитаны или записаны версиями SQLite, предшествующими 3.8.0. Если совместимость с более старыми версиями критична, выполните DROP INDEX для частичных индексов перед передачей файла.

Поддерживаемые версии

Частичные индексы поддерживаются в SQLite начиная с версии 3.8.0 (2013-08-26).

Файлы баз данных, содержащие частичные индексы, не могут быть прочитаны или записаны версиями SQLite, предшествующими 3.8.0. Однако файл базы данных, созданный SQLite 3.8.0, по-прежнему доступен для чтения и записи предыдущими версиями при условии, что его схема не содержит частичных индексов. База данных, недоступная для чтения устаревшими версиями SQLite, может быть сделана доступной простым выполнением DROP INDEX для частичных индексов.

Часто задаваемые вопросы о частичных индексах SQLite

Чем частичный индекс отличается от обычного? Обычный индекс содержит запись для каждой строки таблицы. Частичный индекс содержит записи только для тех строк, которые удовлетворяют условию WHERE, указанному при создании индекса. Это уменьшает размер индекса и ускоряет операции записи.

Можно ли использовать частичный индекс для обеспечения уникальности только части строк? Да. Для этого при создании индекса добавляют ключевое слово UNIQUE вместе с предложением WHERE. SQLite будет требовать уникальности только среди строк, попавших в индекс.

Почему SQLite не использует мой частичный индекс, хотя запрос кажется подходящим? Скорее всего, условие в запросе не совпадает буквально с условием в определении индекса. SQLite не выполняет алгебраических преобразований: b=6 и b=3+3 — разные термы. Проверьте также, что запрос не обращается к строкам, которые могут содержать NULL в индексируемом столбце.

Что произойдёт, если открыть базу данных с частичными индексами в старой версии SQLite? Версии SQLite до 3.8.0 не смогут прочитать или записать такой файл. Чтобы вернуть совместимость, достаточно выполнить DROP INDEX для всех частичных индексов в схеме базы данных.

Можно ли ссылаться в предложении WHERE частичного индекса на столбцы, которые не входят в сам индекс? Да. Предложение WHERE может ссылаться на любые столбцы таблицы, а не только на те, которые перечислены в списке индексируемых столбцов. Однако нельзя использовать подзапросы, ссылки на другие таблицы или недетерминированные функции.