Для держателей больших баз. Наверняка вы уже успели заметить как симпла или окей долго открывает разделы, в которых много товаров. Все дело в том, что система при загрузке раздела выполняет get_options и получает список значений для фильтров, чтобы их отобразить. В нашей случае в таблице s_options, в которой хранятся все свойства товаров 7.5млн. записей. Конечно, выборка из такой таблица требует времени, у нас полная загрузка некоторых разделов длилась до 15 секунд. А ведь нам совсем не нужны свойства всех товаров при открытии разделов, нам нужны только свойства у товаров из выбранной категории. Такой таблицы в симпле нет, поэтому придется ее создать.
Удалось качественно оптимизировать предложенную концепцию оптимизации метода получения фильтров для категорий.
Разработчик симплы и разработчики окея тестировали свою систему в очень благоприятных условиях (мало товаров, мало опций, мало фильтров). Насколько я понимаю именно поэтому они не озаботились таким насущным вопросом
"ЧТОБЫ ВСЕ ЛЕТАЛО!".
Предложенный мной ранее метод хорош, но имеет 1 существенный недостаток. Фильтры для категорий работают, но вот при включении опций какого-то фильтра, остальные не изменялись. Мы выбираем "красный" в фильтре "цвет" , а в фильтре "страна" у нас по-прежнему 15 стран, которые там были до выбора фильтра "цвет". А должны остаться только те страны, товары которых имеют в свойстве: "цвет" значение: "красный".
Решение найдено.
Подробный мануал, что нужно переделать со всеми пояснениями я напишу позже, а пока коротко.
Для отображения фильтров используется вспомогательная таблица, полученная путем копирования и модификации таблицы s_options, также как и в прошлый раз.
Вот, что она из себя представляет.
t_cat3_options
1
product_id int(11)
2 category_id int(5)
3
feature_id int(11)
4
lang_id int(2)
5 value varchar(160)
6 translit varchar(160)
Индекс используется составной, вот такой:
feature_id, value, translit, category_id, lang_id
В таблицу добавлены только те свойства, которые используются в фильтрах и только по тем товарам, которые включены для отображения. Таблица похудела с 7.5 млн. записей до 850 тыс.
Но главное достоинство не в этом. Если кто-то проводил отладку запросов сиплы или окея, то наверняка видел, что при обработке sql запроса из метода get_options всегда используется временная таблица, про которую explain select говорит: "using temporary". С временной таблице обработку больших таблиц быстро не сделать никак. Поэтому все усилия были направлены, чтобы временные таблицы не использовались, а использовался только индекс "using index".
Вот как теперь выглядит стандартный запрос для получения фильтров.
SELECT po.feature_id, po.value, po.translit FROM t_cat3_options po WHERE 1 AND po.category_id in('5','14','47','6','8','132','10','11','16','7','62','93','9','12','13','18','94','37','17','48','150','15','25','20','95','143','147','19','138','148','4') AND po.lang_id='1' AND po.feature_id in('25','2','9','13','16','24','17','18','19','20','21','22','26','31') GROUP BY po.feature_id, po.value ORDER BY NULL
При обработке используется только using index; using where
Отличия от стандартного запроса в том, что для фильтрации по категориям не используются сторонние таблицы. В оригинале к SELECT FROM s_options прикручивался INNER JOIN s_products_categories. Тонкость в том, что при наличии group by у нас все поля group by должны быть в индексе, но это невозможно потому что поля category_id изначально нет в таблице с опциями. Используем трюк select from select. Мы делаем join к таблице уже после group by.
Ушел не 1 час, чтобы добиться от мускуля не использовать временную таблицу. Секрет оказался прост.
1. Нужно чтобы все выбираемые из таблицы поля были в составном индексе.
2. Нужно, чтобы поля, используемые в GROUP BY были в составном индексе, в том же порядке, в котором они указаны в GROUP BY.
3. Все поля используемые в WHERE должны также быть в составном индексе после полей для GROUP BY.
У нас GROUP BY feature_id, value, поэтому в нашем индексе эти поля идут первыми.
Вот так теперь выглядит метод get_categories_options из api/features.php
Код:
public function get_categories_options($filter = array()) {
$feature_id_filter = '';
$category_id_filter = '';
$visible_filter = '';
$brand_id_filter = '';
$features_filter = '';
if(empty($filter['feature_id']) && empty($filter['product_id'])) {
return array();
}
$group_by = '';
if(isset($filter['feature_id'])) {
$group_by = 'GROUP BY feature_id, value';
}
if(isset($filter['feature_id'])) {
$feature_id_filter = $this->db->placehold('AND po.feature_id in(?@)', (array)$filter['feature_id']);
}
if(isset($filter['category_id'])) {
$category_id_filter = $this->db->placehold('INNER JOIN __products_categories pc ON pc.product_id=po.product_id AND pc.category_id in(?@)', (array)$filter['category_id']);
}
if(isset($filter['brand_id'])) {
$brand_id_filter = $this->db->placehold('AND po.product_id in(SELECT id FROM __products WHERE brand_id in(?@))', (array)$filter['brand_id']);
}
if(isset($filter['features'])) {
foreach($filter['features'] as $feature=>$value) {
$features_filter .= $this->db->placehold('AND (po.feature_id=? OR po.product_id in (SELECT product_id FROM t_cat3_options WHERE feature_id=? AND translit in(?@))) ', $feature, $feature, $value);
}
}
$lang_id = $this->lang->lang_id();
$lang_id_filter = '';
if($lang_id) {
$lang_id_filter = $this->db->placehold("AND po.lang_id=?", $lang_id);
}
if(isset($filter['category_id'])) {
$query = $this->db->placehold("
SELECT
po.feature_id,
po.value,
po.translit
FROM t_cat_options po
WHERE
1
$category_id_filter
$lang_id_filter
$feature_id_filter
$brand_id_filter
$features_filter
GROUP BY po.feature_id, po.value
ORDER BY NULL
");
$this->db->query($query);
return $this->db->results();
}
Пришлось сократить длину полей value и translit, чтобы их длина позволяла включить их в составной индекс.
Результат такой:
Стандартный метод get_options отрабатывает запрос за 33 секунды, страница загружается секунд 35, аналогичная страница после модификаций загружается в пределах 4 секунд, запрос отрабатывается около секунды.
По скорости до UMI CMS не дотягивает, но все таки не 33 секунды.
