Начиная с MySQL 5.7, можно создавать индексы для выражений, или функциональных индексов, используя сгенерированные столбцы. В основном вам сначала нужно использовать сгенерированный столбец для определения функционального выражения, а затем проиндексировать этот столбец.
Весьма полезно при работе с функциями JSON, вы можете найти пример здесь и документацию там.
Начиная с MySQL 8.0.13 у нас теперь есть самый простой способ создания функциональных индексов.
Давайте посмотрим, это на быстром практическом примере.
- Использование MySQL 8.0.15
- Запрос к базе данных test_db
Ниже структуры таблицы my_my_salaries:
mysql> SHOW CREATE TABLE my_salaries\G *************************** 1. row *************************** Table: my_salaries Create Table: CREATE TABLE `my_salaries` ( `sal_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`sal_no`,`from_date`), CONSTRAINT `my_salaries_ibfk_1` FOREIGN KEY (`sal_no`) REFERENCES `employees` (`sal_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0,00 sec)
Она содержит некоторые данные
mysql> SELECT count(*) FROM my_salaries; +----------+ | count(*) | +----------+ | 356767 | +----------+ mysql> SELECT * FROM my_salaries LIMIT 3; +--------+--------+------------+------------+ | sal_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10001 | 55000 | 2020-01-27 | 2020-02-27 | | 10001 | 56000 | 2020-02-27 | 2020-03-27 | | 10001 | 60000 | 2020-03-27 | 2020-04-27 | +--------+--------+------------+------------+
Давайте сосредоточимся на следующем запросе:
mysql> SELECT * FROM my_salaries WHERE YEAR(to_date)=2019; +--------+--------+------------+------------+ | sal_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 9564 | 48000 | 2019-01-27 | 2019-02-27 | ...snip... | 9574 | 48000 | 2019-02-27 | 2019-03-27 | +--------+--------+------------+------------+ 89 rows in set (0,80 sec)
mysql> explain SELECT * FROM my_salaries WHERE YEAR(to_date)=2019\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: my_salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2838426 filtered: 100.00 Extra: Using where
У нас есть полное сканирование таблицы ( type: ALL), то есть индекс не используется. Возможно, из-за отсутствия индекса для столбца to_date … 😉
Итак, давайте добавим индекс для to_date!
mysql> ALTER TABLE my_salaries ADD INDEX idx_to_date (to_date); Query OK, 0 rows affected (17,13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE my_salaries\G *************************** 1. row *************************** Table: my_salaries Create Table: CREATE TABLE `my_salaries` ( `sal_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`sal_no`,`from_date`), KEY `idx_to_date` (`to_date`), CONSTRAINT `my_salaries_ibfk_1` FOREIGN KEY (`sal_no`) REFERENCES `employees` (`sal_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
И снова запустите запрос с надеждой на лучший план выполнения
mysql> explain SELECT * FROM my_salaries WHERE YEAR(to_date)=2019\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: my_salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2838426 filtered: 100.00 Extra: Using where
Ой! Еще есть полное сканирование таблицы!
Индекс нельзя использовать из-за использования функции (YEAR ()) в индексированном столбце (to_date).
Кстати, если вы действительно удивлены, может быть, вы должны прочитать это.
Это тот случай, когда вам нужен функциональный индекс!
mysql> ALTER TABLE my_salaries ADD INDEX idx_year_to_date((YEAR(to_date))); Query OK, 0 rows affected (15,12 sec) Records: 0 Duplicates: 0 Warnings: 0
Синтаксис очень похож на создание «обычного» индекса. Хотя вы должны знать о двойных скобках: ((<выражение>))
Теперь мы можем видеть наш новый индекс с именем idx_year_to_date и год индексированного выражения (to_date) :
mysql> SHOW CREATE TABLE my_salaries\G *************************** 1. row *************************** Table: my_salaries Create Table: CREATE TABLE `my_salaries` ( `sal_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`sal_no`,`from_date`), KEY `idx_to_date` (`to_date`), KEY `idx_year_to_date` ((year(`to_date`))), CONSTRAINT `my_salaries_ibfk_1` FOREIGN KEY (`sal_no`) REFERENCES `employees` (`sal_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci mysql> SELECT INDEX_NAME, EXPRESSION FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='employees' AND TABLE_NAME = "my_salaries" AND INDEX_NAME='idx_year_to_date'; +------------------+-----------------+ | INDEX_NAME | EXPRESSION | +------------------+-----------------+ | idx_year_to_date | year(`to_date`) | +------------------+-----------------+
Давайте проверим наш запрос еще раз
mysql> explain SELECT * FROM my_salaries WHERE YEAR(to_date)=2019\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: my_salaries partitions: NULL type: ref possible_keys: idx_year_to_date key: idx_year_to_date key_len: 5 ref: const rows: 89 filtered: 100.00 Extra: NULL mysql> SELECT * FROM my_salaries WHERE YEAR(to_date)=2019; +--------+--------+------------+------------+ | sal_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 9564 | 45000 | 2019-07-27 | 2019-08-08 | ...snip... | 9574 | 45000 | 2019-09-27 | 2019-09-28 | +--------+--------+------------+------------+ 89 rows in set (0,00 sec)
Вот так!
Теперь запрос может использовать индекс. И в этом случае мы положительно влияем на время исполнения.
Также интересно отметить, что можно использовать idx_to_date, первый созданный индекс (нефункциональный), если мы можем переписать исходный запрос:
mysql> EXPLAIN SELECT * FROM my_salaries WHERE to_date BETWEEN '2019-01-01' AND '2019-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: my_salaries partitions: NULL type: range possible_keys: idx_to_date key: idx_to_date key_len: 3 ref: NULL rows: 89 filtered: 100.00 Extra: Using index condition mysql> SELECT * FROM my_salaries WHERE to_date BETWEEN '2019-01-01' AND '2019-12-31' +--------+--------+------------+------------+ | sal_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 9564 | 40000 | 2019-02-27 | 2019-03-27 | ...snip... | 9564 | 45000 | 2019-08-27 | 2019-12-27 | +--------+--------+------------+------------+ 89 rows in set (0,00 sec)
Это сохраняет индекс, я имею в виду меньше индексов для двигателя. Кроме того, если говорить о стоимости обслуживания, стоимость поддержания функционального индекса выше, чем стоимость обычного.
С другой стороны, план выполнения менее хорош (стоимость запроса выше), и, очевидно, вы должны переписать запрос.
Требования и ограничения.
Первичный ключ не может быть функциональным индексом:
mysql> CREATE TABLE t1 (i INT, PRIMARY KEY ((ABS(i)))); ERROR 3756 (HY000): The primary key cannot be a functional index
Вы не можете индексировать недетерминированные функции (RAND (), UNIX_TIMESTAMP (), NOW ()…)
mysql> CREATE TABLE t1 (i int, KEY ((RAND(i)))); ERROR 3758 (HY000): Expression of functional index 'functional_index' contains a disallowed function.
Индексы SPATIAL и FULLTEXT не могут иметь функциональных ключевых частей.
Вывод
Функциональный индекс является интересной и актуальной функцией, которая может быть очень полезна для оптимизации ваших запросов без их перезаписи, особенно при работе с документами JSON и другими сложными типами.
Очевидно, что все детали, которые вы должны знать, находятся в документации MySQL: Функциональные ключевые части
Если вы заинтересованы в архитектуре высокого уровня и дизайне низкого уровня, пожалуйста, прочтите рабочую нагрузку.