Оптимизация SQL-запросов OpenCart 1.5.5.1
В общем то все три мнения имеют право на жизнь, но споры часто достигают масштаба холивара. Беда в том, что споры эти часто теоретические. Оптимизаторы в качестве панацеи от всех бед выбирают кэширование — пытаются кэшировать все и вся. А по-моему начинать надо с оптимизации SQL-запросов, а уже потом применять кэширование.
Начальные условия
- Интернет-магазин CD/DVD дисков.
- Количество категорий (разделов-подразделов): 278
- Количество товаров: 68800.
- Атрибутов и категорий нет.
- Магазин одноязычный.
Я выбрал себе простой сценарий тестирования: Главная страница — Страница категории — Страница подкатегории — Страница товара. Перед запуском сценария очищал кэш MySQL и файловый кэш OpenCart.
Медленные запросы
При помощи стандартного средства MySQL — журнала медленных запросов (или Slow Log) удалось обнаружить 3 самых медленных из частых запросов. Вот они:
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_product_to_category` p2c 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 (1) Запрос получает всего навсего список товаров в категории с разбивкой на страницы, с сортировкой.
SELECT COUNT(DISTINCT p.product_id) AS total FROM `oc_product_to_category` p2c 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 (2) Запрос подсчитывает общее количество товаров в выбранной категории.
SELECT * FROM `oc_url_alias` WHERE `query` = 'product_id=46' (3) Формирование SEO_URL.
Третий запрос самый простой, но и его можно оптимизировать. Нужно уменьшить длину поля `query`, по-умолчанию она 255 символов, 32 символа будет достаточно. Потом надо создать индекс по этому полю. Уменьшение длины поля необходимо для того, чтобы уменьшить общий размер индекса таблицы. Если индекс помещается в CACHE_INDEX MySQL, то поиск по нему идет быстрее.
ALTER TABLE `oc_url_alias` CHANGE `query` `query` VARCHAR( 32 ) NOT NULL; ALTER TABLE `oc_url_alias` ADD INDEX `query` ( `query` ); Запросы (1) и (2) формируются в файле catalod/model/catalog/product.php, в методах класса ModelCatalogProduct соответственно getProduct и getTotalProducts. Будем оптимизировать PHP-код класса.
Оптимизация класса ModelCatalogProduct
Во многих контроллерах вызов методов getProducts и getTotalProducts происходит парно:
$product_total = $this->model_catalog_product->getTotalProducts($data); $results = $this->model_catalog_product->getProducts($data);Добравив к запросу (1) параметр SQL_CALC_FOUND_ROWS можно в одном запросе не только загрузить из базы N записей, ограниченных условием LIMIT, но и подсчитать все найденные записи. Тогда запрос (2) уже будет не нужен.
Еще MySQL сам по себе кэширует результаты SQL-запросов, поэтому в дополнительном кэшировании SQL мало смысла. Только MySQL не кэширует запросы, содержащие NOW(). Заменим в запросах NOW() на date('Y-m-d H:i').':00' или даже date('Y-m-d H').':00:00' .
В самом конце метода getProducts для каждого товара вызывается другой метод getProduct
foreach ($query->rows as $result) < $product_data[$result['product_id']] = $this->getProduct($result['product_id']); >, в котором, в свою очередь, выполняется еще один SQL-запрос:
SELECT DISTINCT *, pd.name AS name, p.image, m.name 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 = '2700' AND pd.language_id = '1' AND p.status = '1' AND p.date_available В этом запросе при помощи вложенных 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, Яндекс.
Как оптимизировать 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 Сервер: 2ядра, 2gb ram.
Вопрос такой: Я знаю, что такое количесто товаро не мало, но всё же, 20 секунд это явно не нормально. Как можно оптимизировать запрос? Из-за какого подзапроса может быть такая шняга?
- Вопрос задан более трёх лет назад
- 641 просмотр
5 комментариев
Средний 5 комментариев
Оптимизация производительности OpenCart 3 - эффективные методы и практические советы
Оптимизация производительности OpenCart 3. Современные потребители требуют мгновенных результатов, и скорость работы вашего интернет-магазина играет ключевую роль в их удовлетворении. В этой статье мы рассмотрим эффективные методы оптимизации производительности магазина на платформе OpenCart 3, а также предоставим практические советы по улучшению скорости загрузки страниц, оптимизации изображений и использованию кеширования.
Оптимизация производительности OpenCart 3 - перечень
1. Оптимизация изображений
- Формат и сжатие: Используйте форматы изображений с низкой степенью сжатия, такие как JPEG для фотографий и PNG для изображений с прозрачностью. Используйте инструменты для автоматического сжатия изображений без потери качества.
2. Кеширование:
- Браузерное кеширование: Включите настройки кеширования браузера для статических ресурсов, таких как изображения и стили. Это уменьшит время загрузки при последующих посещениях.
3. Минификация CSS и JavaScript
- Удаление лишних пробелов и комментариев: Минифицируйте CSS и JavaScript файлы, удаляя лишние пробелы, комментарии и форматирование. Это сократит объем передаваемых данных.
4. Использование системы кеширования OpenCart
- Настройка системы кеширования: В административной панели OpenCart 3 активируйте встроенную систему кеширования. Это снизит нагрузку на сервер и ускорит загрузку страниц.
5. CDN (Content Delivery Network)
- Интеграция с CDN: Используйте CDN для распределения контента ближе к конечным пользователям. Это уменьшит задержки при загрузке ресурсов.
6. Оптимизация запросов к Базе Данных
- Индексация таблиц: Убедитесь, что таблицы в базе данных магазина проиндексированы правильно. Это ускорит выполнение SQL-запросов.
7. Использование кэширования платежных и доставочных систем
- Кэширование данных платежных и доставочных систем: Включите кэширование для данных, которые редко меняются, но часто запрашиваются.
8. Оптимизация шрифтов
- Локальные шрифты: Предпочтительно использовать локальные шрифты вместо загрузки их извне. Это уменьшит время загрузки страницы.
9. Анализ с Google PageSpeed Insights
- Регулярный анализ: Постоянно анализируйте производительность магазина с помощью Google PageSpeed Insights и внедряйте рекомендованные улучшения.
Заключение
Эффективная оптимизация производительности магазина на OpenCart 3 требует системного подхода. Регулярно мониторьте и анализируйте производительность, внедряйте улучшения и следите за новыми технологиями в области оптимизации. Это поможет не только удовлетворить ожидания ваших клиентов, но и улучшить позиции вашего магазина в поисковых результатах.
Автор: Opencart | Дата: 26.01.2024
Модуль Упрощенный заказ на Opencart 3.0
Категория: Модули Opencart
Очень мощный модуль для упрощения формы оформления заказа в Opencart 3.0. Он поможет вам сделать стр..
Модуль Купить в 1 клик Opencart 3.0
Категория: Модули Opencart
Модуль добавляет кнопку "Купить в 1 клик" на страницу товара и/или в списки товаров Opencart 3.0. По..
Модуль Корзина Popup для Opencart 3.0
Категория: Модули Opencart
Всплывающая корзина на сайте Opencart 3.0 - один из способов увеличить конверсию в интернет магазине..
Модуль Менеджер заказов Opencart 3.0
Категория: Модули Opencart
Модуль расширенного менеджера заказов Opencart 3.0 позволит удобно просматривать заказы в интернет-м..
Модуль Авторизация через соцсети Opencart 3.0
Категория: Модули Opencart
Модуль для регистрации и авторизации покупателей через социальные сети на Opencart 3.0.Теперь ваши к..
Модуль Блог для Opencart 3.0
Категория: Модули Opencart
С выходом новой версии Opencart 3.x так и ничего не изменилось в плане встроенных статей - их нет, к..
Модуль SEO Теги PRO для Opencart 3.0
Категория: Модули Opencart
Модуль SEO Теги PRO для Opencart 3.0 позволит сделать импорт семантического ядра и получить посадочн..
Вместе дешевле - скидка за комплект Opencart 3.0
Категория: Модули Opencart
Модуль Вместе дешевле для Opencart 3.0 - позволяет создавать акционные комплекты товаров и назначать..
Оптимизация запросов SQL в opencart
в среднем в корзине клиента 40-45 товаров (специфика магазина) - это создает 6500-6800 SQL запросов.
При любом изменении клиентом количества любой из позиций, опять сыпется 7к запросов.
Уже неоднократно превысили количество запросов в час хоста (750 000).
Нужно оптимизировать, что бы не превышалось количество запросов.
Обновление #1 от 8 октября 2018, 12:17
Опций у товаров нет.
1 день 350 UAH
1 день 350 UAH
Доброго дня
виконаємо якісно
всі розрахунки після обговорення деталей
перегляньте портфоліо
3 дня 450 UAH
3 дня 450 UAH
Я являюсь специалистом компании https://itmarketing.top/ в штате есть программист и верстальщик, который делает проекты на OpenCart.
Стоимость часа работы 450 грн
Мы специально создали сайт, где показаны наши возможности по верстке: http://design.itmarketing.top/
Выберите вариант, который вам больше всего понравился по верстке и по дизайну.
☑ Готовы созваниваться и экономить ваше время на написания сообщений и ТЗ
… ☑ Перед работой пишем подробное ТЗ (бесплатно)
☑ Делаем промежуточные отчеты, чтобы вы знали что происходит с вашим проектом