Как оптимизировать запрос sql opencart

В этом запросе при помощи вложенных SQL-запросов вновь вычисляется цена товара с учетом скидок и акций, и вновь вычисляется звездный рейтинг товара. Можно использовать данные, полученные при помощи запроса (1).

Оптимизированный OpenCart можно потестировать. Измененные файлы доступны в виде commit.

Дальнейшая оптимизация

Для дальнейшего ускорения SQL-запроса (1) можно сделать денормализацию базы данных. В таблицу `oc_product` добавить два поля rating и reviews для хранения рейтинга товара и количества оценок, формирующих этот рейтинг. Значения полей тогда можно будет вычислять не при каждом запросе, а только при одобрении рейтинга администратором магазина.

Если отказаться от скидок и акций, то можно еще ускорить SQL-запрос (1), удалив из него подзапросы:

(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 

и заменив их на

NULL AS discount, NULL AS special

Разработка на языках: PHP, JavaScript, node.js. Базы данных: MySQL, PostgreSQL, MongoDB.
Доработка CMS: OpenCart, PrestaShop, WordPress. Интеграция с: платежными системами, ВКонтакте, Facebook, Яндекс.

Как оптимизировать запрос базы даннх на opencart?

Добрый день!
Есть сайт на опенкарт, версии 2.3, у которого база товаров более 200т и будет расширяться до 500т
На данный момент база данных уже хорошо оптимизирована, но с каждым возрастанием товара становится все медленнее и медленнее. Бывает такое что при переходе на страницу, ждешь пока страница подгружается и в конечном счете выбивает ошибку. Там есть два больших запроса, которые нормально настроенные и оптимизированы и настроены. Но проблема остается, что чем больше товаров, тем медленнее он становится. Сам сайт находится на VPS.
Не могли бы подсказать в какую сторону смотреть, что можно почитать, в какую сторону смотреть?
P.S: просьба не надо писать, типа переходите на другую CMS получше, или меняйте платформу и т.п.
Вопрос нужно решить на опенкарт.
Заранее спасибо тому кто отзовется

• Вопрос задан более трёх лет назад
• 456 просмотров

Комментировать
Решения вопроса 0
Ответы на вопрос 1

Вы пишите что все хорошо оптимизирлвано. И тут же пишете что с такой маленькой базой у вас тормоза. Не стыкуется.

Возможно банально недосточно ресурсов для БД, возможно неправильно настроена, возможно оптимизирлвано запросы, но неоптимизированы индексы - лишние или недостающие. Следует профилировать запросы, а уже затем искать причины.

Ответ написан более трёх лет назад

Андрей @Andrey_Tech Автор вопроса

Дмитрий Шицков, я не писал что база маленькая, я писал что база наоборот растет.
База уже проиндексированная

Андрей, ну вы пишите что 500тыс. записей. Для БД это не большие значения.
База проиндексирована - вы уверены что правильно? Они используются в ваших самых тяжёлых выборах? Лишние индексы приведут к потреблению дополнительных ресурсов ОП для их поддержания, переиндексации. А некорректные составные индексы вовсе могут не участвовать при выполнении запросов. Индексы следует создавать под конкретные запросы.

IMDBOptimizer — Оптимизация базы данных OpenCart

Оптимизация базы данных интернет-магазина — это весьма непростой вопрос, порой требующий отдельных исследований.

И самое неприятное в этом процессе заключается в том, что сделать хоть какую-то оптимизацию может только тот, кто знает sql-запросы и разбирается в базах данных (БД).

Вот как раз для того, чтобы клиентам не приходилось заниматься тем, что им не обязательно знать, и создан IMDBOptimizer.

Статьи про модуль или просто полезные материалы:

Демки

Кэширование SQL-запросов

OpenCart, как и любая CMS, осуществляет немалое количество sql-запросов к БД, часть из которых являются однотипными (то есть для разных пользователей будет один и тот же результат).

И если товаров много, то sql-запросы легко могут стать основной причиной тормозов интернет-магазина (если у вас 5000+ товаров, то об этом вы, вероятно, хорошо знаете).

Однако, этого можно избежать за счет кэширования sql-запросов модулем IMDBOptimizer.

Возможности:

1. Гибридная система кэширования SQL-запросов (БД + файлы), позволяющая увеличить скорость генерации HTML-страницы (тестировалось на стандартном OpenCart с 5500 товаров — прирост производительности от 30% до 70-80%) и частично сбалансировать нагрузку между диском и БД.

2. Поддерживается фильтр «по словам» для исключения SQL-запросов из процесса кэширования (регистронезависимо).

3. Поддерживается фильтр «по URL» для исключения отдельных страниц из процесса кэширования SQL-запросов (регистронезависимо).

4. Так как кэшируются только SQL-запросы, то такой модуль можно успешно применять совместно с другими модулями кэширования (например, v2pagecache). Однако, совместимость лучше проверять на тестовом сервере.

5. Установили модуль? Ничего не нужно настраивать для кэширования. SQL-запросы автоматически начинают кэшироваться (с учетом фильтров), без необходимости что-то еще настраивать.

6. Еще одной отличительной особенностью кэширования именно SQL-запросов является то, что если один и тот же запрос используется при генерации разных веб-страниц или же просто выполняется повторно, то используется всего один кэш. Простой пример, открыли один и тот же товар из разных категорий — опции будут закэшированы всего 1 раз.

7. Можно применять как с созданием индексов, так и без.

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

9. Легко включается и легко отключается.

Ограничения и нюансы:

1. Так как это модуль кэширования уровня БД, то необходимо учитывать, что такие возможности, как отображение реального остатка товара или текущей цены в карточке, не поддерживаются (данные же кэшированы).

2. Кэшируются только SQL-запросы, начинающиеся с select.

3. Заменяется ядровой файл registry.php

4. Кэширование применяется только к клиентской части, в админской части все запросы выполняются как обычно.

5. Учитывайте, что кэширование это дополнительная нагрузка. Например, при первом открытии страницы товара, она может дольше загружаться (создается кэш).

Кэширование SQL-запросов — включение и очистка

Как включить кэширование:

1. Перейдите во вкладку «Кэш SQL-запросов».

2. Укажите, что кэширование «Включено» и сохраните настройки.

Для отключения нужно сделать все то же самое, только в шаге 2 установить «Отключено»

Как очистить кэш:

1. Перейдите во вкладку «Кэш SQL-запросов»

2. Нажмите кнопку «Удалить кэш»

3. Дождитесь соответствующего сообщения

Кэширование SQL-запросов — фильтры

Фильтр запросов «по словам»:

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

При фильтрации, символ «#» заменятся на префикс базы данных, что позволяет создавать собственные конфигурации, которые легко переносить в другие интернет-магазины.

Важно! Пробелы так же учитываются — сделано для того, чтобы можно было исключать отдельные таблицы. Например, строка «#order» без пробела после order исключает все таблицы, связанные с заказом. А с пробелом после исключает только oc_order (рекомендуется так же дублировать правило и указывать апостроф после order, так как SQL-запросы формируются по-разному).

Пустые строки игнорируются. Так же игнорируются пробелы вначале и в конце SQL-запроса.

Фильтр запросов «по URL»:

В данном фильтре построчно указываются комбинации «[тип поиска]: [часть URL адреса]» (или же просто «[часть URL адреса]») для отключения кэширования при генерации конкретных страниц (таких как корзина или же оформление заказа). Все комбинации приводятся к нижнему регистру (регистронезависимо).

1. [часть URL адреса] – это какая-то часть URL адреса, например, «#/cart/» или «=checkout/».
2. [тип поиска] – имеет три значения: «l» (сравнивать часть адреса сначала URL; учитываются параметры запроса), «i» (искать часть адреса внутри URL; учитываются параметры запроса), «r» (искать часть адреса справа; параметры запроса не учитываются).

При фильтрации, символ «#» заменятся на домен сайта, что позволяет создавать собственные конфигурации, которые легко переносить в другие интернет-магазины.

По умолчанию, указан список фильтров для стандартных настроек, а так же корзины с модулем Simple.

Так же учитывайте, что префиксы «http://» и «https://» обрезаются, и что если фрагмент «[тип поиска]:» не указан, то поиск происходит слева (чтобы можно было просто URL вставлять).

r: #/cart
Подходит «site.ru/cart», «site.ru/cart?asd=1». Но, не подходит «site,ru/cartini»

i: =checkout/
Подходит «site.ru/index.php?route=checkout/checkout&1=2». Но, не подходит «site.ru/index.php?route=check/some».

Кэширование SQL-запросов — дополнительные настройки

Максимальный размер вставки в БД. Данный параметр указывает свыше какого размера данных, информацию необходимо кэшировать в файл. Максимальное значение 65000. Рекомендуется использовать в диапазоне 20000 — 30000.

Время жизни кэша (сек). Здесь указывается время в секундах, в течение которого будет актуален созданный кэш.

Для тех, кому нужна только базовая оптимизация

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

Важно! Учтите, что операция весьма длительная и что требуется минимальная нагрузка в базе данных (то есть, желательно, чтобы пользователей сайта вообще не было или их было немного). В крайнем случае, вы можете создавать индексы по одной таблице отдельно, а не все скопом.

1. Сделайте бэкап всей базы данных! Это важно!
2. Откройте модуль
3. Выберите все таблицы (можно щелкнуть по ссылке «Выделить всё»)
4. Чуть ниже, щелкните по кнопке «Генерировать!»
5. Откиньтесь на спинку кресла и наблюдайте как модуль создает индексы

Несколько щелчков мыши и у вас проведена базовая оптимизация базы данных!

Профилактика — создание индексов

Для профилактики, необходимо запускать генерацию после установки любых других модулей, которые создают свои таблицы (не всегда в них имеются все необходимые индексы).

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

Профилактика — оптимизация таблиц

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

Проще говоря, товаров, их атрибутов и прочего. Что особенно актуально для магазинов с частым импортом прайсов или для магазинов, использующих парсеры.

Поэтому для повышения производительности БД рекомендуется периодически выполнять оптимизацию таблиц. В среднем, для OpenCart это, примерно, раз в неделю.

Чтобы провести оптимизацию, достаточно лишь открыть вкладку «Сервис», выбрать таблицы и нажать кнопку «Оптимизировать!».

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

Починка таблиц

Как же неприятно открыть страницу товара и увидеть вместо карточки сообщение вида «PHP … Table is currupted … try to repair it…».

Редко, но такое бывает, что таблицы в БД MySql повреждаются. К счастью, чаще всего для их починки достаточно лишь запустить специальный запрос. Однако, чтобы это сделать необходимо зайти в хостинг, открыть панель phpMyAdmin, найти в интернете как составлять запрос и запустить его (а до этого всего, еще понять что делать с этой ошибкой). Для обычных пользователей или тех, кто только начинает, это весьма непростая задача.

Модуль же позволяет существенно упростить этот процесс. Нужно лишь указать поврежденную таблицу и нажать кнопку «Починить!» во вкладке «Сервис».

Учтите, что ошибки при подсчете не всегда означают ошибки восстановления. Так, например, таблица oc_cart попросту не поддерживает данный вид команды.

Блок с именами

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

Стоит понимать, что они действуют по правилу ИЛИ. То есть если поле таблицы указано среди конкретных полей ИЛИ начинается с одного из указанных префиксов ИЛИ заканчивается на одном из указанных окончаний, то для поля в текущей таблице будет создан индекс.

Например:

Конкретные поля:
product_id, language_id

Префиксы:
stat, col

При таких настройках, индекс для поля «product_option_value_id» будет создан (если его нет в таблице), так как поле заканчивается на «_id»

Карта индексов

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

Правила составляются по следующему принципу:

Имя таблицы — Поле (, Поле)

Где имя таблицы может быть как указано с общим префиксом БД, так и вместо префикса можно использовать символ #, который будет автоматически заменен на префикс. Чтобы создать индексы из нескольких полей, их необходимо перечислить через запятую.

Например:

#product_description — language_id, product_id

В данном случае будет создан индекс (language_id, product_id) для таблицы oc_product_description (если такового индекса не было в таблице).

Удаление индексов

В закладке «Индексы» есть возможность удалять из таблиц индексы, созданные модулем. А именно те, которые имеют префикс «imdbo_».

Сделано это для того, чтобы исходные индексы или созданные вручную индексы не были случайным образом удалены из базы данных.

Подход к генерации имен индексов

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

Так что если вы будете планировать использовать данные индексы при построении своих sql-запросов (например, с возможностью повторного внедрения в другие интернет-магазины), то сделать это будет весьма просто.

Сам алгоритм подбора

Шаг 1. Составляется имя «imdbo_» + «[поле 1]» + «_[поле 2]» + … + «_[поле N]». Если у таблицы индекса с таким именем нет и его длина не превышает 64 символа (требование БД), то индексу присваивается это название. В противном случае, алгоритм переходит к следующему шагу.

Например, для индекса (product_id) будет использовано имя «imdbo_product_id», а для индекса (product_id, order_id) будет использовано имя «imdbo_product_id_order_id». И так далее.

Шаг 2. Составляется имя «imdbo_» + «[поле 1 — первые буквы слов в столбце]» + «_[поле 2 — первые буквы слов в столбце]» + … Если индекса с таким именем нет и его длина не превышает 64 символа (требование БД), то индексу присваивается это название. В противном случае, переход к следующему шагу.

Например, для индекса (order_id, customer_id, store_id, payment_zone_id, currency_id, marketing_id), чья длина больше 64 символов в шаге 1, будет использовано имя «imdbo_oi_ci_si_pzi_ci_mi».

Шаг 3. Практически нереальная ситуация, но сделана для унификации. Составленное имя из шага 2 обрезается до 61 символа (если требуется) и к нему прибавляется приставка «_[номер]», где номер от 1 до 99.

Например, «imdbo_oi_ci_si_pzi_ci_mi_1», …, «imdbo_oi_ci_si_pzi_ci_mi_25», «imdbo_oi_ci_si_pzi_ci_mi_99».

Если же и это недостижимо, то выполняется следующий шаг.

Шаг 4. Аналогично шагу 3, практически нереальная ситуация, но сделана для 100% унификации. Имя стоится как «imdbo_» + «[время UTC]» + «_[номер]». Например, «imdbo_1508711578_3».

А теперь, по-простому. При базовой оптимизации будут созданы индексы только шага 1. Шаг 2 это уже настройки из карты индексов (если вручную были указаны сложные составные индексы). Шаг 3 встретится очень редко, но если и встретится такое, то имена с постфиксами будут аналогичными от интернет-магазина к интернет-магазину. Шаг 4 сделан просто для безопасности, но в реальности невозможен (если, конечно, кто-то специально вручную не создал более 100 одинаковых индексов для таблицы).

Особенности и ограничения

1. Важно учитывать, что генерация индексов происходит для каждой таблицы отдельно. То есть для каждой таблицы отдельно посылается AJAX-запрос. Это связано с тем, что для больших таблиц создание одного индекса может быть весьма длительной операцией. Поэтому, если вы запустили генерацию, то просто дождитесь, когда рядом с кнопками появится сообщение, что генерация завершена.

2. Создаются только обычные индексы.

3. Удаляются только индексы, созданные модулем. А именно те, которые имеют префикс «imdbo_». Сделано для того, чтобы не сломать исходные настройки базы данных и вручную созданные индексы.

4. Если не существует указанных таблиц или полей, то указанные данные будут просто игнорироваться.

5. В БД проверяются только те таблицы, которые начинаются с префикса копии опенкарта (Сделано для тех, кто использует в одной БД несколько сайтов)

6. Пользователь должен иметь полные права для получения доступа к БД (или достаточные для получения метаданных и создания индексов)

7. Оптимизация производится для БД MySQL

8. Имена создаваемых индексов имеют технические названия (техническое ограничение автоматизации)

9. Если существует идентичный по полям индекс, то ничего не будет происходить.

10. Требуется boostrap и jquery

Установка, следующие версии и использование

1. Распакуйте в корень сайта содержимое (каталоги admin и system)

2. Откройте админку и установите модуль (если это следующая версия, то переустановите)

3. Обновите модификаторы

4. Откройте в админке модуль (редактирование)

Лицензия и использование

Лицензия распространяется только для одного сайта. Т.е. 1 домен + все поддомены = 1 оплата.

Купив модуль вы автоматически соглашаетесь с текстом лицензии.

Модуль имеет принцип распространения «as is» («Как есть»).

Ввод лицензионного ключа необходимо осуществить в течение 5 дней после установки модуля. Лицензионный ключ состоит из двух частей.

Ключи необходимо вводить так, как они были присланы, без лишних пробелов и символов.

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

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

Если у вас русский домен, то необходимо указывать то имя, которое указано в config.php в корне сайта.

Найти модуль можно тут:

Liveopencart

Shop.opencart-russian

Как оптимизировать sql запрос товаров opencart?

Добрый день, есть магазин на 700000 товаров.
В связи с этим страницы категорий грузятся секунд по 25.
Посмотрев какие запросы медленнее всего, нашёл 2:
getTotalProducts (Около 9 секунд)
getProducts (10-12 секунд)

Сам долгий запрос:

SELECT p.product_id, ( 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 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 FROM oc_category_path cp LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) 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) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available