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

Содержание

3. Запросы с использованием частичных индексов

1. Введение

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

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

2. Создание частичных индексов

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

create-index-stmt:

выражение:

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

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

В индекс включаются только те строки таблицы, для которых предложение 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 будет индексировано. Следовательно, индекс займет гораздо меньше места. И изменения в исходной таблице purchaseorder будут выполняться быстрее, так как индекс po_parent нужно обновлять только для тех исключительных строк, где parent_po не NULL. Но индекс по-прежнему полезен для запросов. В частности, если нужно узнать всех «потомков» конкретного заказа на покупку "?1", запрос будет следующим:

SELECT po_num FROM purchaseorder WHERE parent_po=?1;

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

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

Определение частичного индекса может включать ключевое слово UNIQUE. Если это так, SQLite требует, чтобы каждая запись в индексе была уникальной. Это предоставляет механизм для обеспечения уникальности среди некоторого подмножества строк в таблице.

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

CREATE TABLE person(
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);

Поле 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;

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

SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;

3. Запросы, использующие частичные индексы

Пусть X — выражение в предложении WHERE частичного индекса, а W — предложение WHERE запроса, использующего индексируемую таблицу. Тогда запрос разрешено использовать частичный индекс, если W⇒X, где оператор ⇒ (обычно читается как «имплицирует») — логический оператор, эквивалентный «X или не W». Следовательно, определение того, может ли частичный индекс использоваться в конкретном запросе, сводится к доказательству теоремы в логике первого порядка.

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

Если 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; -- uses partial index
```
Тогда индекс пригоден для использования в запросе, потому что термин «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» появляется в индексе, он никогда не будет соответствовать ничему.
Если термин в 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;  -- uses partial index
```
Но следующий запрос не может использовать частичный индекс:
```
SELECT * FROM tab2 WHERE b=456;  -- cannot use partial index
```
Последний запрос не может использовать частичный индекс, потому что могут быть строки в таблице с b=456 и где c равно NULL. Но эти строки не будут в частичном индексе.

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

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

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

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

SQLite is in the Public Domain.
https://sqlite.org/partialindex.html