Возможности функциональных индексов в MySQL: подробное руководство

Введение

Функциональные индексы были представлены в MySQL 8.0

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

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

Что такое функциональный индекс?

Функциональный индекс — это индекс, созданный на основе результата функции или выражения. Взгляните на следующие примеры:

# Creating a functional index on the result of a function
CREATE INDEX index_name ON table_name ((function_name(column_name)));

# Creating a functional index on the result of an expression
CREATE INDEX index_name ON table_name ((column_name + 1));

# Creating a functional index on the result of a function and an expression
CREATE INDEX index_name ON table_name ((function_name(column_name) + 1));

Как работают функциональные индексы?

Функциональные индексы работают путем создания индекса по результату функции или выражения. Это означает, что индекс будет создан по результату функции или выражения, а не по самому столбцу. Важное замечание: вы не можете использовать изменчивые функции, такие как NOW(), , UUID() и RAND() в функциональных индексах. Это связано с тем, что результат этих функций не является детерминированным и, следовательно, не может использоваться для создания индекса.

Как использовать функциональные индексы в MySQL

Создание функционального индекса

Чтобы создать функциональный индекс, вам необходимо использовать оператор CREATE INDEX.

CREATE INDEX index_name ON table_name ((function_name(column_name)));

Или вы можете определить индекс как часть оператора CREATE TABLE.

CREATE TABLE table_name
(
    column_name int NOT NULL DEFAULT '0',
    KEY `index_name` ((function_name(`column_name`)))
);

Внутри MySQL создаст новый виртуальный сгенерированный столбец и создаст для него индекс. Этот столбец не будет отображаться в списке столбцов по умолчанию или в результате оператора SHOW CREATE TABLE table_name. Чтобы просмотреть такие столбцы, вы можете выполнить следующий запрос:

SHOW EXTENDED COLUMNS FROM table_name;

И результат будет выглядеть примерно так. Вы можете увидеть столбец VIRTUAL GENERATED с именем !hidden!index_name!0!0.

| Поле | Тип | Ноль | Ключ | По умолчанию | Экстра | |:---|:---|:---|:---|:---|:---| | идентификатор | bigint без знака | НЕТ | ПРИ | ноль | авто_инкремент | | имя_столбца | интервал | НЕТ | | 0 | | | !скрытый!имя_индекса!0!0 | биинт | НЕТ | МУЛ | 0 | ВИРТУАЛЬНЫЙ СОЗДАННЫЙ | | DB_TRX_ID | | НЕТ | | ноль | | | DB_ROLL_PTR | | НЕТ | | ноль | |

Использование функционального индекса в запросе

Давайте воспользуемся, например, следующей таблицей.

CREATE TABLE table_name
(
    id          bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    column_name int             NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    KEY my_functional_index (((ABS(column_name) + 1)))
);

И выполните следующий запрос:

select * from table_name where abs(column_name) + 1 = 42;

MySQL будет использовать my_functional_index для получения данных для этого запроса, поскольку условие полностью соответствует определению нашего функционального индекса.

explain select * from table_name where abs(column_name) + 1 = 42;

| идентификатор | выберите_тип | стол | перегородки | тип | возможные_ключи | ключ | ключ_лен | ссылка | строки | фильтрованный | Экстра | |:---|:---|:---|:---|:---|:---|:---|:---|:---|:--- |:---|:---| | 1 | ПРОСТОЙ | имя_таблицы | ноль | ссылка | имя_индекса | имя_индекса | 8 | константа | 1 | 100 | ноль |

Но если мы попытаемся использовать другие условия, например, abs(column_name) + 2, MySQL не сможет использовать наш индекс.

Удаление функционального индекса

Чтобы удалить функциональный индекс, вам необходимо использовать оператор DROP INDEX. Это точно так же, как и для обычного индекса.

DROP INDEX index_name ON table_name;

Полезные приемы с функциональными индексами.

1. Индексирование вычисляемых столбцов

Предположим, у вас есть таблица продаж со столбцами цена (цена товара) и количество (количество проданных товаров). И вы часто выполняете запросы для расчета общей суммы продажи (цена * количество). Функциональный индекс этого выражения может ускорить эти запросы.

CREATE INDEX idx_total_amount ON sales((price * quantity));

1. Индексирование частей даты

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

CREATE INDEX idx_event_year ON events((YEAR(event_date)));

1. Индексация при манипуляциях со строками

Функциональные индексы удобны, когда вы часто выполняете такие операции, как извлечение подстрок. Например, если у вас есть таблица пользователей со столбцом электронной почты и вы часто ищете пользователей по доменной части электронного письма, можно создать функциональный индекс для подстроки после символа «@»:

CREATE INDEX idx_email_domain ON users((SUBSTRING(email FROM LOCATE('@', email))));

1. Индексирование данных JSON

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

CREATE INDEX idx_color ON products((JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))));

1. Индексирование при поиске без учета регистра

Если вам необходимо часто выполнять поиск без учета регистра в текстовом столбце, функциональный индекс можно создать с помощью функции LOWER(). Для таблицы «Книги» со столбцом «Заголовок»:

CREATE INDEX idx_title_lower ON books((LOWER(title)));

1. Индексирование составных столбцов

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

CREATE INDEX idx_full_name ON people((CONCAT(first_name, ' ', last_name)));

1. Индексирование нормализованных данных

В ситуациях, когда вы хотите выполнить поиск по нормализованной версии строки (например, удалить диакритические знаки), вы можете создать индекс по этой нормализованной версии. Для таблицы International_customers со столбцом имени:

CREATE INDEX idx_normalized_name ON international_customers((CONVERT(name USING ascii)));

1. Индексирование хешированных значений

Если вы запрашиваете большой текстовый столбец с точными совпадениями, может быть более эффективно индексировать хэш текста:

CREATE INDEX idx_hashed_big_text ON books((MD5(big_text_column)));

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

CREATE UNIQUE INDEX unique_idx_hashed_big_text ON books((MD5(big_text_column)));

1. Индексирование для расширенных запросов даты

Для сложных запросов даты, таких как извлечение номера недели и года, функциональный индекс может повысить эффективность:

CREATE INDEX idx_week_year ON events((YEAR(event_date)), (WEEK(event_date)));

1. Оптимизация логических выражений

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

CREATE INDEX idx_in_stock_on_sale ON products(((in_stock = 1 AND on_sale = 1)));

Заключение

В этой статье мы объяснили, что такое функциональные индексы, как они работают и как их использовать в MySQL. Мы также показали несколько приемов, как получить от них максимальную пользу. Я надеюсь, что эта статья оказалась для вас полезной и поможет вам улучшить производительность вашей базы данных. Если у вас есть какие-либо вопросы или комментарии, оставьте их ниже!

---

Оригинал