Очень долгий SQL запрос на функциях "Добавить в избранное" и "Купить в один клик"

Добрый день.

В нашем мультивендоре обнаружили проблему с очень длительным выполнением функций “Добавить в избранное” и “Купить в один клик”. При разборе проблемы в логах mysql нашли вот такой тяжелющий запрос, который около 2х минут выполняется на обеих функциях. Может кто-то уже с таким сталкивался, что можно с ним сделать?

Редакция: 4.14.3 Multi-Vendor Ultimate RU
Сервер достаточно мощный не загруженный: Intel® Core™ i7-8700 Hexa-Core, 128 GB DDR4, NVMe SSD

# Time: 220708 14:13:06
# Thread_id: 78  Schema: bizator_mp  QC_hit: No
# Query_time: 99.371384  Lock_time: 0.000057  Rows_sent: 63898  Rows_examined: 377792
# Rows_affected: 0  Bytes_sent: 3219415
# Tmp_tables: 2  Tmp_disk_tables: 1  Tmp_table_sizes: 4954000
# Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 1  Priority_queue: No
SET timestamp=1657278786;

SELECT cscart_product_feature_variant_descriptions.variant, cscart_product_feature_variants.variant_id, cscart_product_feature_variants.feature_id, cscart_product_features_values.variant_id as selected, cscart_product_features.feature_type, cscart_seo_names.name as seo_name, cscart_seo_names.path as seo_path, cscart_product_feature_variants.external_id, cscart_product_feature_variants.color, cscart_product_features_values.value_int, cscart_product_feature_variant_descriptions.ab__sf_seo_variant FROM cscart_product_feature_variants LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variant_descriptions.variant_id = cscart_product_feature_variants.variant_id AND cscart_product_feature_variant_descriptions.lang_code = ‘uk’ LEFT JOIN cscart_product_features_values ON cscart_product_features_values.variant_id = cscart_product_feature_variants.variant_id AND cscart_product_features_values.lang_code = ‘uk’ AND cscart_product_features_values.product_id = 179650 LEFT JOIN cscart_product_features ON cscart_product_features.feature_id = cscart_product_feature_variants.feature_id LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_product_feature_variants.variant_id AND cscart_seo_names.type = ‘e’ AND cscart_seo_names.dispatch = ‘’ AND cscart_seo_names.lang_code = ‘uk’ WHERE 1 AND cscart_product_feature_variants.feature_id IN (21306, 21305, 8302, 8865, 8867, 8267, 8868, 5648, 5719, 5930, 6443, 7465, 7629) GROUP BY cscart_product_feature_variants.variant_id ORDER BY cscart_product_feature_variants.position, cscart_product_feature_variant_descriptions.variant;

Это не тяжёлый запрос на самом деле. А сколько у Вас характеристик и вариантов?

Join на 5 таблиц уже нехорошо, но такова архитектура построения запросов в карте. И верно, когда характеристику и из вариантов немного - ничего критичного, но когда их количество возрастает… А в MV как я понимаю, они ещё и дублируются под каждого продавца (или может уже поправили?) промежуточная таблица в памяти будет ого-го

1 лайк

Тут ещё зависит от того как ключи построены. Короче, нужно отдебажить.

Около 16К характеристик, 3 языка, около 150К продуктов

Я б сказал так, что не так уж и много, как для мультивендора. Ну то есть не совсем мало, но это же мультивендор. Он должен быть на такое рассчитан.

cscart_products 148,422
cscart_product_descriptions 445,266

cscart_product_features 16,272
cscart_product_features_descriptions 48,816
cscart_product_features_values 5,608,672
cscart_product_feature_variants 177,943
cscart_product_feature_variant_descriptions 533,829

Ключи как от ЦСкарта в архитектуре шли так и работают. Мы их не перестраивали.
Самая сложная структура ключей из таблиц характеристик у cscart_product_features_values

Да, запрос с 5 джойнами еще тот запрос получается. Я вот ума не приложу как его оптимизировать даже можно. Может архитекторы из Карта подскажут, если они здесь бывают?

Судя по цифре Rows_sent: 63898, то оптимизировать в этом запросе должно быть что. Может хотя бы лимиты добавить?

Вот такой дебаг (профайлинг) делал по этому запросу

MariaDB > show profile for query 2;
±------------------------±-----------+
| Status | Duration |
±------------------------±-----------+
| Starting | 0.000056 |
| checking permissions | 0.000004 |
| Opening tables | 0.000013 |
| After opening tables | 0.000003 |
| System lock | 0.000003 |
| table lock | 0.000004 |
| init | 0.000023 |
| Optimizing | 0.000014 |
| Statistics | 0.000139 |
| Preparing | 0.000018 |
| Creating tmp table | 0.000013 |
| Sorting result | 0.000010 |
| Executing | 0.000002 |
| Sending data | 1.878086 |
| Converting HEAP to Aria | 0.002471 |
| Creating index | 0.000010 |
| Repair by sorting | 0.001279 |
| Saving state | 0.000007 |
| Creating index | 0.000003 |
| Converting HEAP to Aria | 0.000026 |
| Sending data | 106.939460 |
| Creating sort index | 0.126477 |
| Removing tmp table | 0.001310 |
| Creating sort index | 0.000007 |
| End of update loop | 0.000004 |
| Query end | 0.000002 |
| Commit | 0.000002 |
| closing tables | 0.000002 |
| Unlocking tables | 0.000001 |
| closing tables | 0.000006 |
| Starting cleanup | 0.000002 |
| Freeing items | 0.000007 |
| Updating status | 0.000009 |
| Logging slow query | 0.000027 |
| Reset for next command | 0.000004 |
±------------------------±-----------+
35 rows in set (0.000 sec)

и вот такой получается план запросов

Странно, но например таблица product_features джоинится не по ключу. Отправьте скрин структуры этой таблички и индексы.

Так же попробуйте заменить LEFT JOIN на INNER JOIN. По идее тут как раз нужен иннер. Он и быстрее по перфомансу.

Какой из LEFT JOIN заменить? Все?

Структура product_features с индексами

заменил на INNER JOIN. Теперь сам запрос да отрабатывает моментально. Остается проблема в том, что этот запрос строит то ли ядро, то ли сторонний модуль. Нужно как-то раскопать из какого кода рождается этот запрос, а потом убедить разработчика, что нужно его заменить )

А где заменили, могли бы подсказать?

Кажется запрос из ядра. Перед тем как убеждать стоит проверить ничего ли не сломалось :slight_smile:

пока просто в phpmyadmin сначала попробовал исходный запрос запустить (как в этом топике он приведен) выполняется 2 минуты. Потом, по совету @mad_old_boar, заменил в запросе все LEFT JOIN на INNER JOIN, начал доли секунд выполняться.

Результат выполнения запроса при INNER JOIN отличается от изначального, но выглядит более правдоподобным.

Могу поменять потестить сломалось или нет и вернуть потом назад. На тестовой установке. Попробую найти откуда этот запрос в ядре формируется тогда.

посмотрел в дебагере подобный запрос вот его Backtrace,
смотрите функцию fn_get_product_feature_variants

 fn_dispatch() path_to_site/index.php:25
 fn_run_controller() path_to_site/app/functions/fn.control.php:458
 include() path_to_site/app/functions/fn.control.php:722
 fn_wishlist_gather_product_data() path_to_site/app/addons/wishlist/controllers/frontend/wishlist.php:112
 fn_get_product_data() path_to_site/app/addons/wishlist/func.php:204
 fn_get_product_features() path_to_site/app/functions/fn.products.php:356
 fn_get_product_feature_variants() path_to_site/app/functions/fn.features.php:601
 db_get_hash_array() path_to_site/app/functions/fn.features.php:1211
 Tygh\Database\Connection::getHash() path_to_site/app/functions/fn.database.php:48
 Tygh\Database\Connection::query() path_to_site/app/Tygh/Database/Connection.php:345

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

Да, именно в fn_get_product_feature_variants и создается этот запрос. Большое спасибо за наводку!

@leonidov подскажите пожалуйста как дебажили этот запрос? Вызовы “Добавить в избранное” и “Купить в один клик” делаются аяксом и “жуком” на витринах не подхватываются.

Да, похоже именно через str_replace и придется костылить. Как вариант, можно еще через хук get_product_feature_variants попробовать сделать более красиво. Ну и надеяться, что это как-то подоптимизируют в следующих версиях.