1. Введение

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

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

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

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

create-index-stmt: hide

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

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; -- uses partial index
   

   Тогда индекс применим запросом, потому что термин "b=6" появляется в определении индекса и в запросе. Помните: условия в индексе должны быть 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;  -- uses partial index
   

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

   SELECT * FROM tab2 WHERE b=456;  -- cannot use partial index
   

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

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

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

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

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