Сайт на opencart, как ускорить загрузку?

Question

[ucy3000]

Имеется сайт с 47 тыс. товаров и 40 атрибутами. Opencart + шаблон Journal.
Сайт хостится на виртуалке с 2 ядрами и 4 Гб оперативной памяти.
Категория с 10 тыс+ товаров грузится за 10+ секунд.
Поставили модуль для opencart для кеширования Buslik Cache. Модуль работает отлично, но возникает другая проблема.
Модуль кеширует страницу после первого посещения страницы. Получается чтобы закешировать весть сайт, надо пройтись по всем страницам сайта. Комбинация из 47 тыс. товаром и 40 атрибутов дает нам 4.7 миллионов страниц, по. которым надо пройтись. Ситуацию еще усугубляет что товары и их цены часто меняются и кеш становиться не валидным, надо опять обходить страницы для кеширования.

Выяснили что причина долгих ответов кроется в таблице product_attribute. Она содержит 1.2 миллионов записей. 90% времени уходит на построение списка фильтров на странице со списком товаров.

Есть идеи как можно ускорить сайт?

Comments

[Adamos]

Оставить вопросу тег MySQL, привести CREATE TABLE product_attribute и EXPLAIN запроса, на который уходит 90% времени.

Answer 1

[ucy3000]

Проблемный запрос

запрос

SELECT 
  MAX(a.attribute_id) attribute_id, 
  MAX(ad.name) attribute_name, 
  MAX(pa.text) value, 
  COUNT(*) total 
FROM 
  psy_product p 
  LEFT JOIN psy_product_attribute pa ON (p.product_id = pa.product_id) 
  LEFT JOIN psy_attribute a ON (a.attribute_id = pa.attribute_id) 
  LEFT JOIN psy_attribute_description ad ON (ad.attribute_id = a.attribute_id) 
  LEFT JOIN psy_product_description pd ON (p.product_id = pd.product_id) 
  LEFT JOIN psy_product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE 
  p.status = '1' 
  AND p.date_available <= NOW() 
  AND p2s.store_id = '0' 
  AND pd.language_id = '1' 
  AND p.quantity > 0 
  AND pa.language_id = '1' 
  AND ad.language_id = '1' 
GROUP BY 
  lower(pa.text), 
  a.attribute_id 
HAVING 
  COUNT(*) > 0;

EXPLAIN запроса

explain

+----+-------------+-------+------------+--------+-------------------------+---------+---------+-----------------------------------------------------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys           | key     | key_len | ref                                                 | rows  | filtered | Extra                                      |
+----+-------------+-------+------------+--------+-------------------------+---------+---------+-----------------------------------------------------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | pd    | NULL       | index  | PRIMARY                 | name    | 767     | NULL                                                | 38629 |    10.00 | Using where; Using index; Using temporary  |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY,status,quantity | PRIMARY | 4       | test_db.pd.product_id                               |     1 |     8.33 | Using where                                |
|  1 | SIMPLE      | p2s   | NULL       | eq_ref | PRIMARY                 | PRIMARY | 8       | test_db.pd.product_id,const                         |     1 |   100.00 | Using index                                |
|  1 | SIMPLE      | ad    | NULL       | ALL    | PRIMARY                 | NULL    | NULL    | NULL                                                |    46 |    10.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY                 | PRIMARY | 4       | test_db.ad.attribute_id                             |     1 |   100.00 | Using index                                |
|  1 | SIMPLE      | pa    | NULL       | ref    | PRIMARY                 | PRIMARY | 12      | test_db.pd.product_id,test_db.ad.attribute_id,const |     1 |   100.00 | NULL                                       |
+----+-------------+-------+------------+--------+-------------------------+---------+---------+-----------------------------------------------------+-------+----------+--------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

Структура таблиц из запроса

структура

CREATE TABLE `psy_product` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `sku` varchar(64) NOT NULL,
  `upc` varchar(12) NOT NULL,
  `ean` varchar(14) NOT NULL,
  `jan` varchar(13) NOT NULL,
  `isbn` varchar(17) NOT NULL,
  `mpn` varchar(64) NOT NULL,
  `location` varchar(128) NOT NULL,
  `quantity` int NOT NULL DEFAULT '0',
  `stock_status_id` int NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `manufacturer_id` int NOT NULL,
  `shipping` tinyint(1) NOT NULL DEFAULT '1',
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `points` int NOT NULL DEFAULT '0',
  `tax_class_id` int NOT NULL,
  `date_available` date NOT NULL,
  `weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `weight_class_id` int NOT NULL DEFAULT '0',
  `length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `length_class_id` int NOT NULL DEFAULT '0',
  `subtract` tinyint(1) NOT NULL DEFAULT '1',
  `minimum` int NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `viewed` int NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `status` (`status`),
  KEY `quantity` (`quantity`)
) ENGINE=InnoDB AUTO_INCREMENT=46009 DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_attribute` (
  `attribute_id` int NOT NULL AUTO_INCREMENT,
  `attribute_group_id` int NOT NULL,
  `sort_order` int NOT NULL,
  PRIMARY KEY (`attribute_id`)
) ENGINE=MyISAM AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_product_attribute` (
  `product_id` int NOT NULL,
  `attribute_id` int NOT NULL,
  `language_id` int NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`product_id`,`attribute_id`,`language_id`,`text`(4)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_attribute_description` (
  `attribute_id` int NOT NULL,
  `language_id` int NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`attribute_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_product_description` (
  `product_id` int NOT NULL,
  `language_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `tag` text NOT NULL,
  `meta_title` varchar(255) NOT NULL,
  `meta_description` varchar(255) NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`,`language_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_product_to_store` (
  `product_id` int NOT NULL,
  `store_id` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`,`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

Answer 2

[Максим Мазеин]

Не везде используются индексы, нужно добавить.
Если используется один язык, убрать language_id из запроса.
И сам запрос странный, он откуда, какой-то модуль фильтра?