Оптимизация запросов OpenCart: цифры до и после

На каталоге OpenCart с десятками тысяч товаров страница категории может замедлиться из-за одного неудачного плана выполнения: база сначала перебирает тысячи строк, затем сортирует их и только после этого применяет LIMIT. Универсальных цифр «было 2 секунды, стало 200 мс» здесь нет: результат зависит от версии OpenCart, СУБД, структуры индексов, числа товаров и фильтров. Поэтому оптимизация начинается не с добавления индекса, а с замера до и после на одной и той же копии данных.

Что именно измерять в OpenCart

Ниже — подход для OpenCart 3.0.x, MySQL 8.0 или MariaDB 10.6. В OpenCart 4 структура и запросы могут отличаться, поэтому перед изменениями сравните схему своей установки. Префикс таблиц тоже не всегда равен oc_.

Для каждой проблемной страницы зафиксируйте:

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

Сравнивайте одинаковые URL, язык, валюту, группу покупателя, сортировку и состояние кеша. Иначе «после» может оказаться быстрее только потому, что результат уже лежит в кеше.

Цифра после оптимизации имеет смысл только рядом с цифрой до, условиями теста и изменением, которое её объясняет.

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

Где обычно тратится время

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

Плохой сценарий плана

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

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

Замер до изменений

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

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

EXPLAIN
SELECT p.product_id, p.image, p.price, p.quantity,
       pd.name
FROM oc_product_to_category AS p2c
JOIN oc_product AS p
  ON p.product_id = p2c.product_id
JOIN oc_product_description AS pd
  ON pd.product_id = p.product_id
 AND pd.language_id = 1
JOIN oc_product_to_store AS p2s
  ON p2s.product_id = p.product_id
 AND p2s.store_id = 0
WHERE p2c.category_id = 12
  AND p.status = 1
  AND p.date_available <= NOW()
ORDER BY p.sort_order, pd.name, p.product_id
LIMIT 0, 20;

Смотрите на столбцы type, possible_keys, key, rows и Extra. Особенно подозрительны полный просмотр большой таблицы, большое значение rows и сообщения о временной таблице или сортировке. Само наличие Using filesort не означает ошибку: сортировка может быть дешевле сложного индекса. Важна её стоимость на вашем объёме данных.

Что проверять в индексах

Перед добавлением индекса получите текущую схему:

SHOW INDEX FROM oc_product;
SHOW INDEX FROM oc_product_to_category;
SHOW INDEX FROM oc_product_description;
SHOW INDEX FROM oc_product_to_store;

В стандартной схеме OpenCart у связующей таблицы категории часто уже есть индекс по category_id, а у таблицы описаний — составной первичный ключ по товару и языку. Дублировать такие индексы нельзя: они занимают место, замедляют INSERT и UPDATE и могут не менять план.

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

ALTER TABLE oc_product
  ADD INDEX idx_catalog_status_date_sort
  (status, date_available, sort_order, product_id);

Это не универсальный рецепт. При сортировке по названию база всё равно может сортировать результат после соединения с product_description. Кроме того, порядок колонок должен соответствовать реальному фильтру и селективности данных. После создания индекса повторите EXPLAIN и замер HTTP-запроса. Если план не улучшился, индекс следует удалить, а не оставлять «на всякий случай».

Почему один индекс не ускоряет весь каталог

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

Коррелированные подзапросы и цена товара

В стандартных запросах OpenCart и особенно в расширениях цена может вычисляться отдельным подзапросом для каждой строки товара. На странице из 20 товаров это терпимо. При увеличении лимита, добавлении фильтров и сложных правилах скидок стоимость растёт вместе с числом возвращённых строк.

Проверяйте это по журналу SQL, а не по исходному PHP-коду. Если один и тот же запрос к таблицам акций или скидок повторяется десятки раз за один HTTP-запрос, возможны три направления:

1. сократить набор данных до товаров текущей страницы;
2. получать связанные данные одним пакетным запросом и сопоставлять их в PHP;
3. включить кеширование результата там, где цена зависит от фиксированного набора параметров.

Третий вариант требует осторожности: цена может зависеть от группы покупателя, количества товара, валюты, налога и даты. Ошибка в ключе кеша покажет одному покупателю цену другого. Инвалидация кеша — отдельная задача; здесь важен только замер повторных обращений к базе.

Сравнение подходов и цена каждого

Цифры до и после: как не подменить измерение

Для каждого изменения заведите короткую таблицу эксперимента:

• URL и параметры запроса;
• версия OpenCart, MySQL или MariaDB, PHP и расширения, участвующие в каталоге;
• размер таблиц и количество товаров в категории;
• состояние кеша и число параллельных запросов;
• p50 и p95 времени ответа, число SQL-запросов и время самого дорогого SQL;
• изменённый индекс или фрагмент запроса;
• проверка, что список товаров, цена и доступность остались прежними.

Один прогон ничего не доказывает. Выполните серию запросов после прогрева приложения и сравните медиану и высокий перцентиль. Если p50 уменьшился, а p95 остался прежним, проблема может быть в блокировках, дисковом вводе-выводе, пуле соединений или соседних запросах расширения.

Где оптимизация ломается

Самый опасный сценарий — ускорить SQL, но изменить бизнес-результат. Перед выкладкой проверьте:

• товары без описания на текущем языке;
• несколько магазинов и языков;
• товары с акцией, скидкой и специальной ценой;
• нулевой остаток, отключённый статус и будущую дату доступности;
• сортировки по цене, названию, рейтингу и популярности;
• глубокие страницы пагинации, где LIMIT 10000, 20 может заставить базу прочитать и отбросить тысячи строк;
• фильтры, добавленные модулем: они часто меняют основной SQL сильнее, чем ядро OpenCart.

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

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

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