Видя бой со стороны, каждый мнит себя стратегом (Неизв.).

5 мин для чтенияФункциональные индексы MySQL

FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
11 июня 2020
Как установить MySQL 8.0 На Ubuntu 18.04
Начиная с 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: Функциональные ключевые части
Если вы заинтересованы в архитектуре высокого уровня и дизайне низкого уровня, пожалуйста, прочтите рабочую нагрузку.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Читайте также

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close