Для того чтобы воспринимать чужие мысли, надо не иметь своих (Л. Толстой).

Оптимизация постоянного сворачивания в MySQL 8.0

Главное меню » Базы данных » MySQL » Оптимизация постоянного сворачивания в MySQL 8.0

01.06.2020

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

Пожалуйста, ознакомьтесь с рекомендациями по развертыванию 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

Давайте посмотрим, что мы загрузили:

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

Читать Расширенный WordPress поиск и замена в базе данных в Linux с помощью sed

Поиск отрицательного значения в строго положительном столбце был обработан во время оптимизации!
Таким образом, они явно положительно влияют на время выполнения запроса:

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)

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

Просмотров поста: 15