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

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

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

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», а снятия — отрицательный «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. Индекс 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.

Эта страница была в последний раз изменена 11 февраля 2023 г. в 20:57:33 UTC