Индексы по выражениям

Обычно индекс SQL ссылается на столбцы таблицы. Но индекс также можно создать по выражениям, включающим столбцы таблицы: непосредственно указав выражение вместо имени столбца в операторе CREATE INDEX либо сославшись в CREATE INDEX на виртуальный вычисляемый столбец.

В качестве примера рассмотрим следующую таблицу, в которой отслеживаются изменения сумм в долларах на различных «счетах»:

CREATE TABLE account_change(
  chng_id INTEGER PRIMARY KEY,
  acct_no INTEGER REFERENCES account,
  location INTEGER REFERENCES locations,
  amt INTEGER,  -- in cents
  authority TEXT,
  comment TEXT
);
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));

Каждая запись в таблице account_change фиксирует пополнение или снятие средств со счёта. Пополнения имеют положительное значение «amt», а снятия — отрицательное.

Индекс acctchng_magnitude построен по номеру счёта («acct_no») и абсолютному значению суммы. Этот индекс позволяет эффективно выполнять запросы, связанные с величиной изменения на счёте. Например, чтобы вывести все изменения по счёту $xyz на сумму более 100,00 $, можно выполнить запрос:

SELECT * FROM account_change WHERE acct_no=$xyz AND abs(amt)>=10000;

Или, чтобы вывести все изменения по одному конкретному счёту ($xyz) в порядке убывания величины, можно написать:

SELECT * FROM account_change WHERE acct_no=$xyz
 ORDER BY abs(amt) DESC;

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

1. Как использовать индексы по выражениям

Используйте оператор CREATE INDEX, чтобы создать новый индекс по одному или нескольким выражениям, так же, как вы создали бы индекс по столбцам. Единственное отличие состоит в том, что вместо имён столбцов в качестве элементов индекса указываются выражения.

Планировщик запросов SQLite рассмотрит возможность использования индекса по выражению, если это выражение присутствует в предложении WHERE или ORDER BY запроса в точности в том виде, в каком оно записано в операторе CREATE INDEX. Планировщик запросов не выполняет алгебраических преобразований. Чтобы сопоставить условия предложения WHERE и элементы ORDER BY с индексами, SQLite требует, чтобы выражения совпадали, за исключением незначительных синтаксических различий, таких как пробелы. Поэтому, если у вас есть:

CREATE TABLE t2(x,y,z);
CREATE INDEX t2xy ON t2(x+y);

А затем вы выполняете запрос:

SELECT * FROM t2 WHERE y+x=22;

Индекс не будет использоваться, поскольку выражение в операторе CREATE INDEX (x+y) не совпадает с выражением в запросе (y+x). Эти выражения могут быть математически эквивалентны, но планировщик запросов SQLite требует, чтобы они совпадали, а не просто были эквивалентны. Рассмотрим вариант переписывания запроса:

SELECT * FROM t2 WHERE x+y=22;

Во втором запросе, скорее всего, будет использоваться индекс, поскольку теперь выражение в предложении WHERE (x+y) точно совпадает с выражением в индексе.

2. Ограничения

Для выражений в операторах CREATE INDEX существуют некоторые разумные ограничения:

1. Выражения в операторах CREATE INDEX могут ссылаться только на столбцы индексируемой таблицы, но не на столбцы других таблиц.

2. Выражения в операторах CREATE INDEX могут содержать вызовы функций, но только таких, результат которых полностью определяется их входными параметрами (то есть детерминированных функций). Очевидно, что такие функции, как random(), не подойдут для использования в индексе. Однако и такие функции, как sqlite_version(), хотя их значения постоянны в рамках одного подключения к базе данных, не являются постоянными на протяжении всего срока существования файла базы данных, поэтому их нельзя использовать в операторе CREATE INDEX. Обратите внимание: определённые приложением функции SQL по умолчанию считаются недетерминированными и не могут использоваться в операторе CREATE INDEX, если при регистрации функции не указан флаг SQLITE_DETERMINISTIC.

   См. также: устаревшие индексы по выражениям

3. Выражения в операторах CREATE INDEX не могут использовать подзапросы.

4. Выражения можно использовать только в операторах CREATE INDEX, но не в ограничениях UNIQUE или PRIMARY KEY внутри оператора CREATE TABLE.

3. Совместимость

Возможность индексировать выражения появилась в SQLite в версии 3.9.0 (2015-10-14). База данных, в которой используется индекс по выражениям, не будет доступна для более ранних версий SQLite.