Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > Запрос тормозит когда он подзапрос

Автор: maxipub 15.1.2019, 14:57

Добрый день.

Оптимизирую один код. Уже есть прогресс, но уперся в одном месте. Есть запрос на выборку товаров:

Код


SELECT some_data 
FROM goods AS g 
INNER JOIN cats AS c ON c.cat_id=g.cat_id 
INNER JOIN ( 
    SELECT DISTINCT i.goods_rev_id FROM items AS i 
    INNER JOIN stock_list AS l ON (l.stock_id=i.stock_id AND l.stock_avaliable=1) 
    WHERE i.order_id=0 ) AS n ON n.goods_rev_id=g.goods_id 
WHERE 
c.disabled=0 AND 
c.cat_id=123

goods - таблица с товарами
cats - разделы
items - наличие (одна строка - одна товарная позиция, нужно хранить индивидуальную информацию по каждой из них)
stock_list - список складов

Суть в том, что производится выборка данных по товарам указанного раздела (c.disabled=0 AND c.cat_id=123), которые есть в наличии (i.order_id=0) со всех доступных складов (l.stock_id=i.stock_id AND l.stock_avaliable=1).

Все это дело выполняется примерно 0,02 сек. Но дергается часто.

Сам по себе подзапрос возвращает список всех товаров в наличии со всех доступных складов. Он нужен для связи по конкретной ревизии товара.

Если подзапрос выполнить как самостоятельный запрос:

Код


SELECT DISTINCT i.goods_rev_id FROM items AS i 
INNER JOIN stock_list AS l ON (l.stock_id=i.stock_id AND l.stock_avaliable=1) 
WHERE i.order_id=0

То, он выполняется за 0,0004 сек и возвращает около 2500 записей - список id.

Но если сделать что-то вида:

Код


SELECT * FROM ( 
    SELECT DISTINCT i.goods_rev_id FROM items AS i 
    INNER JOIN stock_list AS l ON (l.stock_id=i.stock_id AND l.stock_avaliable=1) 
    WHERE i.order_id=0 ) AS t

То это тут же начинает выполнятся порядка 0,02 сек. Я так понимаю, именно подзапрос все дело тормозит. Индексы везде вылизаны, и используются. Само по себе подзапрос, как самостоятельный запрос, выполняется быстро, и выозвращает не много результатов. Но стоит поставить его в SELECT * FROM (-подзапрос-) AS t - как получаем эти пресловутые 0,02 сек...

Добавлено через 9 минут и 24 секунды
Если вдруг не очевидно для чего подзапрос - чтоб уменьшить пересечения при джойнах:

goods - десятки тысяч записей
cats - сотни
items - сотни тысяч
stock_list - десятки

И после этого придется накладывать на все группировку по товару...

Автор: Akina 15.1.2019, 16:04

Цитата(maxipub @ 15.1.2019, 15:57

)

Если вдруг не очевидно для чего подзапрос - чтоб уменьшить пересечения при джойнах:

Не... всё становится хуже. Уж лучше "куча пересечений", но зато можно использовать индексы. А так ты получаешь неиндексированный результат подзапроса, который далее фуллсканится. Да ещё, не приведи господи, результат материализуется...

Автор: maxipub 16.1.2019, 11:26

Akina, MySQL 5.7.19, я слышал что начиная с 5.7 он умеет делать индексы для подзапросов, ошибся? EXPLAIN содержит какой-то auto_key0:

Код

1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 4 db.g.goods_id 10 100.00 Using where; Using index

А в общем все оказалось верно. Убрал подзапрос, сейчас все выполняется не дольше 0,0015 сек. Конечно, хотелось бы еще ускорить, но понимаю что места для маневров уже не много. Пока сойдет и так, позже еще попробую вернуться к этому вопросу.

В очередной раз большое спасибо!

Автор: Akina 16.1.2019, 15:07

Цитата(maxipub @ 16.1.2019, 12:26

)

я слышал что начиная с 5.7 он умеет делать индексы для подзапросов, ошибся? EXPLAIN содержит какой-то auto_key0

Не ошибся... вот только кто сказал, что этот автосгенерённый ключ будет подходить для оптимизации запроса?

Цитата(maxipub @ 16.1.2019, 12:26

)

хотелось бы еще ускорить

Не вижу потенции для ускорения, кроме создания подходящих индексов. Сплошные INNER JOIN и отборы по одному значению - нет пространства для манёвра.