#mysql #mysql-8.0
#mysql #mysql-8.0
Вопрос:
Я не могу найти объяснение поведения MySQL. Примеры приведены ниже.
Поля name и name_full являются text типом, а поле price_steps — json типом. Ни у одного из них нет индекса.
SELECT
name,
name_full,
price_steps
FROM
`lots`
WHERE
EXISTS (
SELECT
*
FROM
`categories`
INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id`
WHERE
`lots`.`id` = `category_lot`.`lot_id`
AND `category_id` IN (25)
)
ORDER BY
`created_at` DESC
LIMIT 31 OFFSET 0
MySQL выдает ошибку: [Ошибка] 1038 — Не хватает памяти для сортировки, рассмотрите возможность увеличения размера буфера сортировки сервера.
Хорошо, пусть будет так.
Затем я добавил дополнительное поле в часть выбора.
SUBSTRING(`name_full`, 1, 200000000000) as name_full2
и запрос выполняется успешно (почему? Дополнительное поле должно привести к дополнительному выделению памяти, не так ли?).
Затем я решил утяжелить запрос и заменить строку
AND `category_id` IN (25)
с
AND `category_id` IN (1,2,3,4,5,6,7,8,9,10, 25)
и запрос также успешно завершается.
Количество строк с category = 25 составляет всего около 250, но с категориями в (1,2,3,4,5,6,7,8,9,10, 25) составляет около 40000 строк. Это должно привести к дополнительным требованиям к памяти, но mysql не выдает ошибку. Почему?
Есть какое-либо объяснение этому парадоксу? Заранее спасибо!
UPDATE1
EXPLAIN при сбое запроса
mysql> EXPLAIN SELECT name, name_full, price_steps FROM `lots` WHERE EXISTS ( SELECT * FROM `categories` INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id` WHERE `lots`.`id` = `category_lot`.`lot_id` AND `category_id` IN(25) ) ORDER BY `created_at` DESC;
---- ------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------------- ------ ---------- ----------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------------- ------ ---------- ----------------------------------------------
| 1 | SIMPLE | categories | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | category_lot | NULL | ref | category_lot_lot_id_foreign,category_lot_category_id_foreign | category_lot_category_id_foreign | 8 | const | 1099 | 100.00 | Start temporary |
| 1 | SIMPLE | lots | NULL | eq_ref | PRIMARY | PRIMARY | 8 | torgs.category_lot.lot_id | 1 | 100.00 | End temporary |
---- ------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------------- ------ ---------- ----------------------------------------------
3 rows in set, 2 warnings (0.00 sec)
EXPLAIN с успешным запросом (добавлено 4-е поле)
mysql> EXPLAIN SELECT name, name_full, price_steps,SUBSTRING(`name_full`, 1, 200000000000) as name_full2 FROM `lots` WHERE EXISTS ( SELECT * FROM `categories` INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id` WHERE `lots`.`id` = `category_lot`.`lot_id` AND `category_id` IN(25) ) ORDER BY `created_at` DESC;
---- ------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------------- ------ ---------- ----------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------------- ------ ---------- ----------------------------------------------
| 1 | SIMPLE | categories | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | category_lot | NULL | ref | category_lot_lot_id_foreign,category_lot_category_id_foreign | category_lot_category_id_foreign | 8 | const | 1099 | 100.00 | Start temporary |
| 1 | SIMPLE | lots | NULL | eq_ref | PRIMARY | PRIMARY | 8 | torgs.category_lot.lot_id | 1 | 100.00 | End temporary |
---- ------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------------- ------ ---------- ----------------------------------------------
3 rows in set, 2 warnings (0.00 sec)
EXPLAIN с успешным запросом (добавлено 4-е поле и category_id in (1,2,3,4,5,6,7,8,9,10,25) )
mysql> EXPLAIN SELECT name, name_full, price_steps, SUBSTRING(`name_full`, 1, 200000000000) as name_full2 FROM `lots` WHERE EXISTS ( SELECT * FROM `categories` INNER JOIN `category_lot` ON `categories`.`id` = `category_lot`.`category_id` WHERE `lots`.`id` = `category_lot`.`lot_id` AND `category_id` IN(1,2,3,4,5,6,7,8,9,10,25) ) ORDER BY `created_at` DESC;
---- -------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------- ------ ---------- ---------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- -------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------- ------ ---------- ---------------------------------
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | lots | NULL | eq_ref | PRIMARY | PRIMARY | 8 | <subquery2>.lot_id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | categories | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 11 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | category_lot | NULL | ref | category_lot_lot_id_foreign,category_lot_category_id_foreign | category_lot_category_id_foreign | 8 | torgs.categories.id | 1883 | 100.00 | NULL |
---- -------------- -------------- ------------ -------- -------------------------------------------------------------- ---------------------------------- --------- --------------------- ------ ---------- ---------------------------------
4 rows in set, 2 warnings (0.00 sec)
ОБНОВЛЕНИЕ 2
------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Note | 1276 | Field or reference 'torgs.lots.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select `torgs`.`lots`.`name` AS `name`,`torgs`.`lots`.`name_full` AS `name_full`,`torgs`.`lots`.`price_steps` AS `price_steps`,substr(`torgs`.`lots`.`name_full`,1,200000000000) AS `name_full2` from `torgs`.`lots` semi join (`torgs`.`categories` join `torgs`.`category_lot`) where ((`torgs`.`category_lot`.`category_id` = `torgs`.`categories`.`id`) and (`torgs`.`lots`.`id` = `<subquery2>`.`lot_id`) and (`torgs`.`categories`.`id` in (1,2,3,4,5,6,7,8,9,10,25))) order by `torgs`.`lots`.`created_at` desc |
------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)
Комментарии:
1. Может быть, план выполнения дает подсказку? (но я согласен, что это кажется … странным)
2. Почему вы используете
SELECT *в своемEXISTSзапросе? Нет необходимости возвращать каждый отдельный столбец, если вы не используете ни один из них. Измените это наSELECT 1 FROMвместо, что значительно уменьшит объем требуемой памяти и повысит производительность запросов.3. @KenWhite: я думаю, что MySQL значительно оптимизировал это, и это не должно иметь значения из-за
WHERE EXISTS4. @966p В конце объяснения я вижу сообщение
2 warnings. Одним из предупреждений является скомпилированный оператор SQL, но мне интересно узнать о другом предупреждении. Можете ли вы сделатьSHOW WARNINGS(сразу послеEXPLAIN...)?5. @Luuk Извинения, я пропустил ваш комментарий. Результат ОТОБРАЖЕНИЯ ПРЕДУПРЕЖДЕНИЯ был прикреплен в разделе UPDATE2. Большое спасибо за ваши ответы. Я решил проблему 23 ноября, увеличив sort_buffer_size , но все еще не решил загадку…