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

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

CREATE INDEX создаёт индексы по указанному столбцу(ам) заданного отношения, которым может быть таблица или материализованное представление. Выражение в предложении WHERE может ссылаться только на столбцы нижележащей таблицы, но не обязательно ограничиваться теми, по которым строится индекс.

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

CREATE INDEX users__last_name_lower ON users( lower( last_name ) );

Это создаст уникальный индекс на "lower(last_name)". Все запросы, ищущие "lower(last_name)" смогут потом использовать этот индекс:

SELECT user_id FROM users WHERE lower( last_name ) = lower( 'Smith' );

Для столбцов типов CHAR и VARCHAR с помощью параметра col_name(length) могут создаваться индексы, для которых используется только часть столбца (для столбцов BLOB и TEXT нужно указывать длину). Команда, приведенная ниже, создает индекс, используя первые 10 символов столбца name:

mysql> CREATE INDEX part_of_name ON customer (name(10));

Поскольку большинство имен обычно имеют отличия друг от друга в первых 10 символах, данный индекс не должен быть намного медленнее, чем созданный из столбца name целиком. Кроме того, используя неполные столбцы для индексов, можно сделать файл индексов намного меньше, а это позволяет сэкономить место на диске и к тому же повысить скорость операций INSERT!

В некоторых случаях MySQL не использует индекс, даже если это возможно. Несколько примеров таких ситуаций приведено ниже:

• Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице (в таких случаях просмотр таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска). Следует учитывать, что если подобный запрос использует LIMIT по отношению только к извлекаемой части строк, то MySQL будет применять индекс в любом случае, так как небольшое количество строк можно найти намного быстрее, чтобы вернуть результат.
• Если диапазон изменения индекса может содержать величины NULL при использовании выражений ORDER BY ... DESC

Поддержка баз данных

Большие базы данных, поддерживающие индексы выражения включают в себя DB2 (с версии 10.5), Oracle Database (с релиза 8i.) and PostgreSQL (по крайней мере с версии 7).