TL; TR
В MySQL 8.0. 16 оптимизатор снова улучшился!
Сравнения столбцов числовых типов с постоянными значениями проверяются и складываются или удаляются для недопустимых значений или значений, выходящих из-под контроля.
Цель состоит в том, чтобы ускорить выполнение запроса.
Название этой статьи (Оптимизация постоянного сворачивания), названное в честь этого вида оптимизации, довольно загадочно. Тем не менее, принцип прост и важен, с точки зрения пользователя ничего не поделаешь.
Что такое «Оптимизация постоянного сворачивания»?
Из документации MySQL:
Сравнения между константами и значениями столбцов, в которых значение константы выходит за пределы диапазона или имеет неправильный тип по отношению к типу столбца, теперь обрабатываются один раз во время оптимизации запроса, скорее строка за строкой, чем во время выполнения.
Из блога MySQL Server Team :
цель состоит в том, чтобы ускорить выполнение, потратив немного больше времени на анализ.
Всегда истинные и ложные сравнения обнаруживаются и устраняются.
В других случаях тип константы настраивается так, чтобы соответствовать типу поля, если они не совпадают, что позволяет избежать преобразования типов во время выполнения.
Достаточно ясно?
Один пример стоит тысячи слов, поэтому давайте посмотрим глубже, сравнивая старое поведение в MySQL 8.0.15 с новым, начиная с MySQL 8.0.16.
Мы используем оптимизированные образы MySQL Server Docker, созданные, поддерживаемые и поддерживаемые командой MySQL в Oracle.
Развертывание MySQL 8.0.15 и MySQL 8.0.16:
$ docker run --name=mysql_8.0.15 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql/mysql-server:8.0.15
$ docker run --name=mysql_8.0.16 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql/mysql-server:8.0.16
Пожалуйста, ознакомьтесь с рекомендациями по развертыванию MySQL в Linux с помощью Docker.
Скопируйте файл дампа тестовой таблицы на 8.0.15 и 8.0.16:
$ docker cp ./testtbl.sql mysql_8.0.15:/tmp/testtbl.sql
$ docker cp ./testtbl.sql mysql_8.0.16:/tmp/testtbl.sql
Загрузите тестовую таблицу в экземпляр 8.0.15:
$ docker exec -it mysql_8.0.15 mysql -u root -p --prompt='mysql_8.0.15> ' Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 8.0.15 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql_8.0.15> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.15 | +-----------+ mysql_8.0.15> CREATE SCHEMA test; Query OK, 1 row affected (0.04 sec) mysql_8.0.15> USE test Database changed mysql_8.0.15> source /tmp/testtbl.sql ... ...
Загрузите тестовую таблицу в экземпляр 8.0.16:
$ docker exec -it mysql_8.0.16 mysql -u root -p --prompt='mysql_8.0.16> ' Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql_8.0.16> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.16 | +-----------+ mysql_8.0.16> CREATE SCHEMA test; Query OK, 1 row affected (0.04 sec) mysql_8.0.16> USE test Database changed mysql_8.0.16> source /tmp/testtbl.sql ... ...
Давайте посмотрим, что мы загрузили:
mysql_8.0.16> SHOW CREATE TABLE testtbl\G *************************** 1. row *************************** Table: testtbl Create Table: CREATE TABLE `testtbl` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(36) NOT NULL, `val2` varchar(36) DEFAULT NULL, `val3` varchar(36) DEFAULT NULL, `val4` varchar(36) DEFAULT NULL, `num` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx2` (`val2`), KEY `idx3` (`val3`), KEY `idx4` (`val4`) ) ENGINE=InnoDB AUTO_INCREMENT=14220001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci mysql_8.0.16> SELECT COUNT(*) FROM testtbl; +----------+ | COUNT(*) | +----------+ | 5000000 | +----------+
Для нас здесь важен неиндексированный столбец – num : int (10) unsigned DEFAULT NULL. Он содержит только положительные числа:
num
mysql_8.0.16> SELECT min(num), max(num) FROM testtbl; +----------+----------+ | min(num) | max(num) | +----------+----------+ | 9130001 | 14130000 | +----------+----------+
Старое поведение
Что произойдет, если я найду отрицательное число, скажем -12345, в столбце num?
Помните, что он содержит только положительные числа и нет индекса.
mysql_8.0.15> EXPLAIN SELECT * FROM testtbl WHERE num=-12345\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4820634 filtered: 10.00 Extra: Using where
Согласно плану EXPLAIN, у нас есть полное сканирование таблицы. Таким образом, это имеет смысл, потому что нет индекса на num.
Однако мы знаем, что нет отрицательной ценности , поэтому, безусловно, есть место для улучшений.
Выполнение запроса:
mysql_8.0.15> SELECT * FROM testtbl WHERE num=-12345; Empty set (2.77 sec)
Действительно, полное сканирование таблицы может быть дорогостоящим.
Текущее поведение – 8.0.16+
Constant-Складная Оптимизация улучшает выполнение этого типа запросов.
План EXPLAIN для MySQL 8.0.16 совершенно другой:
mysql_8.0.16> EXPLAIN SELECT * FROM testtbl WHERE num=-12345\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Impossible WHERE Вы заметили: Extra: Impossible WHERE
Поиск отрицательного значения в строго положительном столбце был обработан во время оптимизации!
Таким образом, они явно положительно влияют на время выполнения запроса:
mysql_8.0.16> SELECT * FROM testtbl WHERE num=-12345; Empty set (0.00 sec)
Ура!
В дополнение к оператору = эта оптимизация в настоящее время возможна и для > , > = , < , <= , = , <>,! = И <=> .
Например
mysql_8.0.16> EXPLAIN SELECT * FROM testtbl WHERE num > -42 AND num <= -1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Impossible WHERE mysql_8.0.16 > > SELECT * FROM testtbl WHERE num > > - 42 AND num <= - 1; Empty set ( 0.00 sec)
Индексированный столбец
Кроме того, если ваш столбец индексируется оптимизатором, у него уже есть соответствующая информация, поэтому до 8.0.16 нет необходимости в постоянной оптимизации сворачивания, чтобы иметь быстрый запрос :).
mysql_8.0.15> CREATE INDEX idx_num ON testtbl(num); Query OK, 0rows affected (24.84 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql_8.0.15> EXPLAIN SELECT * FROM testtbl WHERE num = -12345\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: no matching row in consttable 1 row in set, 1 warning (0.00 sec) mysql_8.0.15> SELECT * FROM testtbl WHERE num = -12345; Empty set (0.00 sec)