Оптимизация запросов OpenCart на большом каталоге

Почему OpenCart начинает тормозить на большом каталоге

На небольшом магазине запрос с несколькими JOIN, сортировкой и отдельной загрузкой каждой картинки выглядит безобидно. На каталоге в десятки тысяч товаров он превращается в очередь: база читает лишние строки, PHP делает десятки дополнительных обращений, а администратор ждёт открытия списка товаров. Оптимизация начинается не с добавления индексов, а с ревью конкретного пути: какой контроллер вызван, какой метод модели работает, сколько SQL-запросов он отправляет и какие данные действительно нужны странице.

Главный вопрос на ревью OpenCart — не «как ускорить этот SQL», а «почему странице вообще пришлось прочитать эти строки и выполнить столько запросов».

Ниже используются примеры для OpenCart 3.x с MySQL или MariaDB. В OpenCart 4.x структура расширений и часть путей отличаются, поэтому код перед переносом нужно проверить в текущей ветке и на схеме вашей базы.

Что проверять до рефакторинга

Сначала зафиксируйте исходное состояние. Откройте страницу каталога, поиска или админского списка товаров и запишите:

• общее время ответа и время выполнения SQL;
• количество запросов к базе на один HTTP-запрос;
• самые долгие запросы и число обработанных строк;
• размер результата, который передаётся из модели в контроллер и шаблон;
• план выполнения для двух-трёх самых дорогих запросов.

Если у вас нет профилировщика, временно соберите SQL из логов базы или панели хостинга. Не включайте подробное логирование на боевом сайте надолго: оно само создаёт нагрузку и может записать чувствительные параметры.

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

Четыре типовые проблемы в коде OpenCart

1. N+1 запросов в цикле

Самый дорогой шаблон выглядит так: модель получает список товаров, затем для каждого товара отдельно загружает категорию, остаток, изображение или специальные цены. Для 100 товаров это уже 101 и больше обращений к базе. Даже быстрый отдельный запрос становится проблемой из-за сетевых задержек, блокировок и накладных расходов PHP.

На ревью ищите вызовы методов модели внутри foreach. Если метод содержит $this->db->query(), это кандидат на объединение. Сначала определите минимальный набор полей, который нужен странице, затем получите его одним запросом или несколькими пакетными запросами.

2. SELECT * и повторная работа в PHP

OpenCart хранит данные товара в нескольких таблицах: основные поля — в product, название и описание — в product_description, связь с категорией — в product_to_category, связь с магазином — в product_to_store. Запрос, который забирает все столбцы из всех этих таблиц, передаёт больше данных, чем нужно карточке или списку.

Указывайте поля явно. Это уменьшает объём результата и защищает код от неожиданных изменений схемы. Отдельно проверьте, не сортирует ли PHP массив, который база могла вернуть уже в нужном порядке.

3. Фильтр без соответствующего индекса

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

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

4. Сортировка и поиск по выражению

Условия вроде LOWER(pd.name) = ... или оборачивание индексируемого столбца в функцию часто мешают использовать обычный индекс. Такая же проблема возникает, когда в условии применяется шаблон с начальным символом %: обычный B-tree-индекс не может быстро найти начало строки.

Не переписывайте поиск наугад. Для полнотекстового поиска сначала сравните требования магазина с возможностями MySQL или MariaDB, а затем проверьте качество результатов и стоимость индекса. Полнотекстовый поиск и отдельный поисковый сервис — самостоятельная архитектурная тема.

Рефакторинг запроса списка товаров

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

Ниже — фрагмент модели для OpenCart 3.x. Он показывает принцип: язык, магазин, категорию и пагинацию фильтрует база, а в PHP попадают только поля списка.

<?php

public function getCategoryProducts(int $categoryId, int $page = 1, int $limit = 20): array
{
    $categoryId = max(0, $categoryId);
    $page = max(1, $page);
    $limit = max(1, min(100, $limit));
    $offset = ($page - 1) * $limit;

    $languageId = (int)$this->config->get('config_language_id');
    $storeId = (int)$this->config->get('config_store_id');

    $sql = "SELECT
                p.product_id,
                p.price,
                p.image,
                pd.name
            FROM " . DB_PREFIX . "product p
            INNER JOIN " . DB_PREFIX . "product_description pd
                ON pd.product_id = p.product_id
                AND pd.language_id = " . $languageId . "
            INNER JOIN " . DB_PREFIX . "product_to_category pc
                ON pc.product_id = p.product_id
                AND pc.category_id = " . $categoryId . "
            INNER JOIN " . DB_PREFIX . "product_to_store ps
                ON ps.product_id = p.product_id
                AND ps.store_id = " . $storeId . "
            WHERE p.status = 1
            ORDER BY pd.name ASC, p.product_id ASC
            LIMIT " . $offset . ", " . $limit;

    return $this->db->query($sql)->rows;
}

Целые значения здесь приводятся к типу int, а имена таблиц и сортировка заданы кодом. Это не заменяет полноценную проверку входных данных в контроллере. Второй столбец в ORDER BY делает порядок стабильным, если у товаров одинаковые названия.

Как сравнивать подходы

Кеш не исправляет плохой запрос. Он лишь скрывает его до момента очистки кеша, массового импорта или одновременного истечения записей.

Что проверять после изменения

План, количество строк и пагинация

Для каждого изменённого запроса сравните план до и после. В MySQL для этого используется EXPLAIN SELECT .... Смотрите не только на наличие индекса, но и на предполагаемое число строк, тип соединения и наличие временной таблицы или отдельной сортировки. Если установлен MySQL 8.0.18 или новее, можно дополнительно проверить фактическое выполнение через EXPLAIN ANALYZE; в MariaDB и старых версиях доступность и формат отличаются.

Проверьте первую, среднюю и последнюю страницы. Большой OFFSET может заставить базу прочитать и отбросить много строк. Если каталог действительно глубокий, сравните обычную пагинацию с выборкой «после последнего ключа» — это отдельный рефакторинг, который требует стабильной сортировки и изменения интерфейса пагинации.

Дубликаты после JOIN

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

Проверка админки и импорта

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

Когда подход ломается

• JOIN стал медленнее цикла. Проверьте дублирование строк и объём промежуточного результата. Один большой JOIN не всегда лучше двух небольших запросов.
• Индекс не используется. Сравните типы столбцов, порядок колонок в составном индексе, условие WHERE и план на данных боевого размера.
• После кеширования видны старые цены. Определите события изменения цены и остатка, затем очищайте связанные записи. Глобальная очистка кеша после каждого товара может создать новый пик нагрузки.
• Страница ускорилась, а импорт замедлился. Индексы оплачиваются стоимостью записи. Проверьте пакетный импорт и оставьте только индексы, которые подтверждены планами и метриками.

Что делать дальше

Выберите один самый долгий запрос, сохраните его план и число SQL-вызовов, затем измените только один участок кода. После этого повторите замер на копии базы с объёмом данных, близким к продакшену. Следующим шагом настройте наблюдение за медленными запросами и отдельно проверьте глубокую пагинацию, импорт и очистку кеша. Оптимизацию поиска по названию и инвалидацию кеша не смешивайте с этим ревью: каждая из этих задач требует собственных измерений.

Версия примеров: OpenCart 3.x, MySQL/MariaDB; перед применением в OpenCart 4.x проверьте API модели, структуру расширения и имена таблиц в вашей установке.