Плохая производительность при выполнении определенных запросов после обновления MySQL 8.0 ⇐ Php

[Anonymous]

РЕДАКТИРОВАТЬ: я наблюдаю в Python то же поведение, что и в PHP. Кажется, что-то с MySQL.
Мы пытаемся обновить MySQL с 5.7 до 8.0. Наша кодовая база использует PHP MySQLi для запросов к нашему серверу MySQL. В наших тестовых конфигурациях мы наблюдаем снижение производительности (в 50 раз медленнее) при выполнении определенных запросов, которые связывают множество параметров. Мы хотим, чтобы MySQL 8.0 работал в то же время, что и 5.7. Ниже приведен пример структуры таблицы и запроса на устранение неполадок.
CREATE TABLE IF NOT EXISTS `a` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `name` (`name`) USING BTREE,
KEY `name_id` (`id`,`name`) USING BTREE
);

CREATE TABLE IF NOT EXISTS `b` (
`id` int NOT NULL AUTO_INCREMENT,
`a_id` int NOT NULL,
`value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uniquevalue` (`a_id`,`value`) USING BTREE,
KEY `a_id` (`a_id`) USING BTREE,
KEY `v` (`value`) USING BTREE,
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS `c` (
`product` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`b_id` int NOT NULL,
PRIMARY KEY (`product`,`b_id`) USING BTREE,
KEY `b_id` (`b_id`),
KEY `product` (`product`),
CONSTRAINT `c_ibfk_2` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
);

-- example trouble query
SELECT c.product, a.name, b.value
FROM b
INNER JOIN a ON b.a_id = a.id AND a.name IN ('1be6f9eb563f3bf85c78b4219bf09de9')
-- this hash is from the dataset (linked below) but it should match a record in the 'a' table that has an associated record in the 'b' table that in turn has an associated record in the 'c' table
INNER JOIN c on c.b_id = b.id and c.product IN (?, ?, ?...) -- ... meaning dynamic number of parameters

Если запрос изменен так, чтобы возвращать только одну запись (ограничение 1), запрос по-прежнему будет медленным. Так что дело не в объеме возвращаемых данных. Если запрос выполняется без параметризации (с объединением строк), время выполнения запроса приемлемо во всех средах. Чем больше параметров вы добавляете, тем медленнее становится запрос (линейный). При 7000 связанных параметрах запрос выполняется за 100–150 мс в MySQL 5.7 и ~10 секунд в MySQL 8.0.28. Мы видим те же результаты в PHP 7.4 и 8.0. Мы видим те же результаты с MySQLi или PDO.
Это говорит мне о том, что это как-то связано с привязкой параметров. Я включил профилирование и проверил результаты запроса. Основная часть времени запроса (~95%) была потрачена на этапе выполнения, а не на этапе привязки параметров. Кроме того, я вижу, что процессор процесса mysql 8 привязан во время выполнения запроса. Меня это очень озадачило.
Вот объяснение MySQL 8.0.




id
select_type
таблица
разделы
тип
возможные_ключи
key
key_len
ref
строки
отфильтровано
Дополнительно




1
ПРОСТОЙ
а

const
PRIMARY,name,name_id
name
1022const
1
100
Использование индекса


1
ПРОСТОЙ
c
ref
ПЕРВИЧНЫЙ,b_id,продукт
продукт
152
const
1
100
Использование индекса


1
ПРОСТОЙ
b

eq_ref
PRIMARY,uniquevalue,a_id
ПЕРВИЧНЫЙ
4
DefaultWeb.c.b_id
1< /td>
5
Использование где




Вот объяснение MySQL 5.7.

< table class="s-table">


id
select_type
таблица
разделы
тип
возможные_ключи
< th>key
key_len
ref
строки
отфильтровано
Дополнительно




1
ПРОСТОЙ
а

const
PRIMARY,name,name_id
имя
257
const
1
100
Использование индекса
< /tr>

1
ПРОСТОЙ
c

ref
ПЕРВИЧНЫЙ,b_id,продукт
ПЕРВИЧНЫЙ
152
const
1
100
< td>Использование индекса


1
ПРОСТОЙ
b

eq_ref
PRIMARY,uniquevalue,a_id
ПЕРВИЧНЫЙ
4
DefaultWeb.c.b_id
1
5
Использование где



Между этими двумя объяснениями есть некоторые различия, но опять же, эта проблема возникает только с подготовленными операторами в PHP.
Ниже приведены некоторые из них. php-код, демонстрирующий проблему. Этот код написан для работы с набором данных, который я предоставил по ссылке на Google Диск ниже. Я также включил наши переменные MySQL в CSV-файл.


Подробнее здесь: https://stackoverflow.com/questions/732 ... -0-upgrade