Перейти к публикации
Поиск в
  • Дополнительно...
Искать результаты, содержащие...
Искать результаты в...

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


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'
 

Изменено пользователем rassigor
Ссылка на комментарий
Поделиться на других сайтах


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

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

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

Ссылка на комментарий
Поделиться на других сайтах


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

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

 

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

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

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

Ссылка на комментарий
Поделиться на других сайтах


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

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

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

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

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

  • +1 1
Ссылка на комментарий
Поделиться на других сайтах


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

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

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

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

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

Ссылка на комментарий
Поделиться на других сайтах


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

30 тыс товаров

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

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

Ссылка на комментарий
Поделиться на других сайтах


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
Ссылка на комментарий
Поделиться на других сайтах


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

 

вот осталось 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'
 

Снимок.JPG

Ссылка на комментарий
Поделиться на других сайтах


Тогда иначе.

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

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

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

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

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

Ссылка на комментарий
Поделиться на других сайтах


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

Ссылка на комментарий
Поделиться на других сайтах


Почитал что 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...
Ссылка на комментарий
Поделиться на других сайтах


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

Ссылка на комментарий
Поделиться на других сайтах

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

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

:cry:

Ссылка на комментарий
Поделиться на других сайтах


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

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

 

https://app.loadimpact.com/

 

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

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

 

 

 

 

Снимок.JPG

Ссылка на комментарий
Поделиться на других сайтах


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

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

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

 

Ссылка на комментарий
Поделиться на других сайтах

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

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

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

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

 

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

Ссылка на комментарий
Поделиться на других сайтах


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

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

Ссылка на комментарий
Поделиться на других сайтах

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

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

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

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

Ссылка на комментарий
Поделиться на других сайтах


        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

 

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

Ссылка на комментарий
Поделиться на других сайтах


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

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

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


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

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

 

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

Ссылка на комментарий
Поделиться на других сайтах

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

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

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

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

Ссылка на комментарий
Поделиться на других сайтах


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

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


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

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

 

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

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

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

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

 

 

Снимок.JPG

Ссылка на комментарий
Поделиться на других сайтах


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

Ссылка на комментарий
Поделиться на других сайтах


Создайте аккаунт или войдите в него для комментирования

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

Создать аккаунт

Зарегистрируйтесь для получения аккаунта. Это просто!

Зарегистрировать аккаунт

Войти

Уже зарегистрированы? Войдите здесь.

Войти сейчас
 Поделиться

  • Сейчас на странице   0 пользователей

    • Нет пользователей, просматривающих эту страницу.
×
×
  • Создать...

Важная информация

На нашем сайте используются файлы cookie и происходит обработка некоторых персональных данных пользователей, чтобы улучшить пользовательский интерфейс. Чтобы узнать для чего и какие персональные данные мы обрабатываем перейдите по ссылке. Если Вы нажмете «Я даю согласие», это означает, что Вы понимаете и принимаете все условия, указанные в этом Уведомлении о Конфиденциальности.