Помогите с оптимизацией запроса

[rassigor]

Добрый день, есть часто повторяемый запрос, на который тратиться 1000 мс, подскажите как его можно оптимизировать? 

SELECT DISTINCT *, pd.name AS name, p.image, (SELECT md.name FROM oc_manufacturer_description md WHERE md.manufacturer_id = p.manufacturer_id AND md.language_id = '1') AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '18862' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'

SELECT DISTINCT *, pd.name AS name, p.image, (SELECT md.name FROM oc_manufacturer_description md WHERE md.manufacturer_id = p.manufacturer_id AND md.language_id = '1') AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '18862' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'
 

Змінено 17 листопада 2017 користувачем rassigor

[rassigor]

1 час назад, nikifalex сказал:

убрать лишнее, добавить индексы, посмотреть что mysql пишет в describe select

Спасибо!так и сделаю.через пару тройку лет, когда все изучу и разберусь и стану профи

[uk003]

Отлично ответил))) 

[rassigor]

3 часа назад, nikifalex сказал:

Так а какой ответ вы хотите. К сожалению телепатический шар сломался недавно.

 

Ответ вот какой, стоит ли поголовно делать индексы на поля, которые указаны в отборах например вот эти ps.date_end  и manufacturer_id и аналогичные

и второй вопрос, как профи запросов, можете ли вы глянуть, и сказать где тут косяк, если его тут нет, то тогда и нечего выдумывать, надо ставить железо мощнее. 

Я сам оптимизацией много времени занимался, но не сайтов, а 1С предприятие и MS SQL) там мне алгоритм понятен, тут пока не совсем догоняю, вот поэтому и интересуюсь.

[hoolygan]

2 часа назад, rassigor сказал:

Я сам оптимизацией много времени занимался, но не сайтов, а 1С предприятие и MS SQL) там мне алгоритм понятен, тут пока не совсем догоняю, вот поэтому и интересуюсь.

Т.е. Вы занимались оптимизацией запросов в ms sql?

Простите, но если так, должны понимать, каким образом запрос попадает в оптимизатор (сейчас про ms sql говорю), и что вначале оптимизатор анализирует FROM, дальше ON, потом JOIN, потом всё остальное. Могу предположить (не уверен, но чисто предположение), что тут точно так же. И к тому же NOW() вернет время с очень конкретной точностью, а значит запрос не будет закеширован. А также что индексы будут использоваться именно в том порядке, что прописаны в запросе, а также что составные индексы будут использоваться в определенных случаях более оптимально. И что самое главное- что нужно изучать план запроса.

Тогда скажите, пожалуйста, как оптимизировать этот запрос, если Вы даже не посмотрели выполнение запроса, как посоветовали постом выше?

[rassigor]

6 часов назад, hoolygan сказал:

Т.е. Вы занимались оптимизацией запросов в ms sql?

Простите, но если так, должны понимать, каким образом запрос попадает в оптимизатор (сейчас про ms sql говорю), и что вначале оптимизатор анализирует FROM, дальше ON, потом JOIN, потом всё остальное. Могу предположить (не уверен, но чисто предположение), что тут точно так же. И к тому же NOW() вернет время с очень конкретной точностью, а значит запрос не будет закеширован. А также что индексы будут использоваться именно в том порядке, что прописаны в запросе, а также что составные индексы будут использоваться в определенных случаях более оптимально. И что самое главное- что нужно изучать план запроса.

Тогда скажите, пожалуйста, как оптимизировать этот запрос, если Вы даже не посмотрели выполнение запроса, как посоветовали постом выше?

Вообщем не совсем, 1с это интерпретатор, там есть код, выполняется он на mssql , запускаешь там замер производительности, и система пишет сколько по времени выполняется код, далее его оптимизируешь, добавляешь индексы, на  уровне mssql  я не особо работал, в основном были дурищмы типа запрос в цикле, запрос без оптимизации и тд и тп

[rassigor]

параметры такие 

30 тыс товаров

4 гб рам, ссд, 4 ядра по 2.4 ггц

Стоит джеткеш и фильтр, сейчас выполню код и скину

[rassigor]

https://its.1c.ru/db/metod8dev#content:5842:hdoc

 

Во если кому интересно)

[rassigor]

7 часов назад, nikifalex сказал:

ну тогда вы же должны понимать что оптимизация и необходимость индексов зависит от количества строк  таблицах, от величины свободной памяти на сервере под mysql от производительности файловой подсистемы и прочих факторов.

 

Выполните в phpmyadmin

 

DESCRIBE SELECT DISTINCT *, pd.name AS name, p.image, (SELECT md.name FROM oc_manufacturer_description md WHERE md.manufacturer_id = p.manufacturer_id AND md.language_id = '1') AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '18862' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'

 

дайте таблицу сюда, что хоть что-то было.

 

 

Время создания: Ноя 17 2017 г., 22:07
Создан: phpMyAdmin 4.4.15.10 / MySQL 5.5.52-MariaDB
SQL запрос: DESCRIBE SELECT DISTINCT *, pd.name AS name, p.image, (SELECT md.name FROM oc_manufacturer_description md WHERE md.manufacturer_id = p.manufacturer_id AND md.language_id = '1') AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '18862' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0';
Строки: 13

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	p	const	PRIMARY,date_available,status,product_id	PRIMARY	4	const	1
1	PRIMARY	pd	const	PRIMARY,language_id,product_id	PRIMARY	8	const,const	1
1	PRIMARY	p2s	const	PRIMARY,store_id,product_id	PRIMARY	8	const,const	1	Using index
1	PRIMARY	m	const	PRIMARY,manufacturer_id	PRIMARY	4	const	1
10	SUBQUERY	r2	ref	product_id,status	product_id	4	const	1	Using where
9	SUBQUERY	r1	ref	product_id,status	product_id	4	const	1	Using where
8	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...
7	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...
6	SUBQUERY	ss	const	PRIMARY	PRIMARY	8	const,const	1
5	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...
4	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...
3	SUBQUERY	pd2	ref	product_id,quantity,customer_group_id,date_start	product_id	4	const	1	Using where; Using filesort
2	SUBQUERY	md	const	PRIMARY,manufacturer_id,language_id	PRIMARY	8	const,const	1

[rassigor]

Добавил кстати много где индексов, поуменьшилась нагрузка

 

вот осталось 2 запроса

 

Время выполнения: 1000.33мс 

SELECT r.review_id, r.author, r.rating, r.text, r.plus, r.minus, r.admin_reply, p.product_id, pd.name, p.price, p.image, r.date_added FROM oc_review r LEFT JOIN oc_product p ON (r.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '4607' AND p.date_available <= NOW() AND p.status = '1' AND r.status = '1' AND pd.language_id = '1' ORDER BY r.date_added DESC LIMIT 0,20
 

 

Время выполнения: 1001.33мс 

SELECT DISTINCT *, pd.name AS name, p.image, (SELECT md.name FROM oc_manufacturer_description md WHERE md.manufacturer_id = p.manufacturer_id AND md.language_id = '1') AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '6614' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'
 

[hoolygan]

Тогда иначе.

Одними индексами не добиться оптимальных запросов. К тому же, добавив в этом запросе индексы, Вы можете проиграть в других запросах, которые используют другие соединения, по другим полям.

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

Поэтому работа по оптимизации требует навыков и опыта, и зачастую это индивидуально у каждого сайта. 

Вам дали направление, куда двигаться - это describe, now(), удаление лишнего с запроса, и индексы. С этого можно начинать.

Или искать "оптимизатора".

[rassigor]

Ну эт понятно, те по факту Опенкарт нетиповой будет, переписанный, и обновиться на 3.0 особо не получиться как я понимаю

[rassigor]

Почитал что null это плохо, надо идекс, только не пойму к какому полю нужен

 

5	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...
4	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...

[chukcha]

Вы неверно прочитали
Читайте еще , обратите внимание на последний столбец

[rassigor]

19 минут назад, chukcha сказал:

Вы неверно прочитали
Читайте еще , обратите внимание на последний столбец

[rassigor]

Протестировал сайт, сервисом на 100 человек

двумя разными сайтами 

 

https://app.loadimpact.com/

 

http://loaddy.com/result/788473356/

это хорошо или плохо? показатели

 

 

 

 

[chukcha]

Запрос-то стандартный,
вот посмотрите, то что вы на лепили индексов, 

Вы думаете помогло?

А какие индексы применятся.
 

 

[rassigor]

Только что, chukcha сказал:

Запрос-то стандартный,
вот посмотрите, то что вы на лепили индексов, 

Вы думаете помогло?

А какие индексы применятся.
 

 

 я пока нечего не думаю) методом тыка пока делаю, разбираюсь в процессе. Какие нужно убрать?

[chukcha]

те что налепили

Поясните назначение каждого индекса.

[rassigor]

1 минуту назад, chukcha сказал:

те что налепили

Поясните назначение каждого индекса.

Назначение простое, смотрел лог медленных запросов, где стояло поле в условии, в сортировке, туда и ставил индекс. 

[rassigor]

        SELECT 
                    *
                FROM 
                    `oc_mfilter_url_alias` 
                WHERE 
                    `mfp` = '753-vid-kul-tury-vozdeystviya[Бобовые культуры]' AND `language_id` = '1' AND `store_id` = '0' AND ( `path` = '' OR `path` = 'sredstva-zashity-rastenij/insekticidy' )
                LIMIT
                    1
            

Я действовал так, смотрим запрос

 

видим поля 

mfp

language_id

store_id

path

 

И добавляем индексы, верная логика? 

[chukcha]

2 минуты назад, rassigor сказал:

И добавляем индексы, верная логика? 

Вы реально считаете, что слова
Добавьте индексы на все что в WHERE сделает запрос оптимизированным?

Вы же... "оптимизировали" MsSql - подходы там ничем не отличаются, и планы запросов похожи

Но почему вы считает что к mySql нужен другой подход?

 

Повторю
Запрос стандартный из ocStotre

[hoolygan]

Нет, логика неверная.

Если запрос использует несколько полей в блоке where - то индексы на каждое отдельное поле толку не дадут. Запрос не обязательно будет использовать данные индексы, а будет искать 1 индекс на весь набор запросов. Если этого индекса не нашлось, то оптимизатор начнет пробовать "запрашивать" остальные индексы и пытаться "предугадать" выиграш используя их. И чем больше "ненужных" индексов будет на таблице, тем больше вариантов "предугадывания" придется просмотреть оптимизатору, прямо в геометрической прогрессии.

А теперь сопоставьте это с Вашими накиданными индексами по всем таблицам, и подумайте, как Вы "облегчили" работу оптимизатора запросов.

Это если в двух словах, на самом деле там всё гораздо сложнее.

[rassigor]

3 минуты назад, chukcha сказал:

Вы реально считаете, что слова
Добавьте индексы на все что в WHERE сделает запрос оптимизированным?

Вы же... "оптимизировали" MsSql - подходы там ничем не отличаются, и планы запросов похожи

Но почему вы считает что к mySql нужен другой подход?

 

Повторю
Запрос стандартный из ocStotre

Основные оптимизации это кода)) не сильно мастер я в самом MSSQL 

Так что принимаю помощь, тыкание на статьи. 

Вот такие я штуки обычно делаю. 

 

 

[rassigor]

Ясно не вышло из меня оптимизатора на php (( Жду тогда марка с его Джеткеш 7)) пусть делают профессионалы оптимизацию