Функциональные индексы MySQL

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

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

Начиная с MySQL 8.0.13 у нас теперь есть самый простой способ создания функциональных индексов.

Давайте посмотрим, это на быстром практическом примере.

Использование MySQL 8.0.15
Запрос к базе данных test_db

Ниже структуры таблицы my_my_salaries:

mysql> SHOW CREATE TABLE my_salariesG

*************************** 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)=2019G

*************************** 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_salariesG

*************************** 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)=2019G

*************************** 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_salariesG

*************************** 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)=2019G

*************************** 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: Функциональные ключевые части

Если вы заинтересованы в архитектуре высокого уровня и дизайне низкого уровня, пожалуйста, прочтите рабочую нагрузку.

ReadMag.ru

рецепты по настройке программного обеспечения

Функциональные индексы MySQL

Требования и ограничения.