SourceDistillery.com - код двойной очистки от багов

Оптимизация SQL-запросов OpenCart 1.5.5.1

Модули для OpenCart

Для Joomla + Virtuemart

При выборе движка для Интернет-магазина, часто задают вопрос: а как поведет себя тот или иной движок при большом количестве товаров, большом количестве посетителей - при высокой нагрузке? Частые ответы на этот вопрос следующие:

  1. Не жадничай на хостинге, арендуй выделенный сервер, заплати толковому сисадмину - и все будет работать быстро.
  2. Бесплатные (готовые) движки не годятся. Чтобы что-то работало быстро - надо писать с нуля, под свои задачи.
  3. Любой движок надо дорабатывать, оптимизаровать. Можно оптимизировать производительно существующего движка под свои задачи.

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

Начальные условия

Для экспериментов возьму OpenCart версия 1.5.5.1 (январь 2013). База данных:

На момент напсания статьи еще не было официальной русской локализации OpenCart 1.5.5.1, поэтому пусть вас не смущает, что интерфейс магазина англоязычный, а товарная база русская.

Я выбрал себе простой сценарий тестирования: Главная страница - Страница категории - Страница подкатегории - Страница товара. Перед запуском сценария очищал кэш 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 <= NOW()
	AND p2s.store_id = '0' AND p2c.category_id = '5'
GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC LIMIT 0,15
(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 <= NOW()
	AND p2s.store_id = '0' AND p2c.category_id = '5'
(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 <= NOW() AND p2s.store_id = '1'
В этом запросе при помощи вложенных 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