Оптимизация структуры БД, индексы, и скорость работы

Quasar

У меня есть несколько клиентов, которые уже немного разрослись, и они столкнулись с проблемами очень низкой производительности движка.

Первая проблема: фильтры

При выборе нескольких фильтров и внутри категорий со вложенностью, база может задуматься. Вплоть до 12 секунд, как было в моем случае.

Вот пример запроса:


SELECT MIN(`ps1`.price * `c1`.rate) min, MAX(`ps1`.price * `c1`.rate) max FROM shop_product p
        JOIN shop_product_features pf1
                ON p.id = pf1.product_id
        JOIN shop_product_features pf2
                ON p.id = pf2.product_id
        JOIN shop_product_features pf3
                ON p.id = pf3.product_id
        JOIN shop_product_features pf4
                ON p.id = pf4.product_id
        JOIN shop_product_features pf5
                ON p.id = pf5.product_id
        JOIN shop_category_products cp1
                ON p.id = cp1.product_id
        JOIN shop_product_skus ps1
                ON ps1.product_id = p.id and ps1.price <> 0
        JOIN shop_currency c1
                ON c1.code = p.currency
WHERE p.status = 1
        AND p.status = 1
        AND pf1.feature_id = 179 AND pf1.feature_value_id = 17127
        AND p.status = 1
        AND pf2.feature_id = 152 AND pf2.feature_value_id = 1559
        AND pf3.feature_id = 162 AND pf3.feature_value_id = 1129
        AND p.status = 1
        AND pf4.feature_id = 169 AND pf4.feature_value_id = 1168
        AND pf5.feature_id = 172 AND pf5.feature_value_id = 2157
        AND cp1.category_id IN(901,903,920,934,935,938,945,983,995,1007,3098,3108,1032,1033,1034,1035,1040,1163,1072,1999,1998,1088,1102,1103,1105,1106,1114,1117,3088,1359,2113,1733,1734,1735,1736,1737,1738,1739,3100,3095,3096,3097,2106,2112,2049,2193,1997,1990,2000,2001,2022,2073,2084,2091,2095,2097,2307,2100,2101,2105,2325,2893,3117,3118,3119,3120,3092,3091,3132,3133,3089,3094,3093,3165);


Этот запрос генерирует плагин SEO-фильтры (есть настройка для кеширования), и скорее всего shopProductsCollection. Такой-же запрос летит на COUNT(DISTINCT product.id) и сама выборка товаров.

Первое, что было сделано - все таблицы сконвертировали на InnoDB и сделали апдейт ПО. Результат улучшился, стало чуть меньше секунды.

Проблема в том, что в таблице нет индекса по feature_id, feature_value_id. Лечится путем создания индекса:

CREATE INDEX quasar_spf_perf_idx ON shop_product_features(feature_id, feature_value_id, product_id);

Как результат: было 12 секунд, стало 0.2 секунды.

Дальше - лучше.

Вторая проблема: не кеширует данные

Вот список того, что кеширует фреймворк:

  1. валюты
  2. категории (даже не отрендеренное дерево, а просто массив)
  3. блоки
  4. списки товаров

Из всего этого, хорошая идея кешировать категории, но лучше это делать сразу в отрендеренном виде, и вставлять в шаблон как строку. Туда же нужно писать все тяжелые запросы, а это выборки min/max, всякие COUNT(*) и другое. Возможно, стоит подумать над тем, чтобы кеш использовался более активно?

Третья проблема: shop_category_products

Товары в категориях хранятся не очень эффективно. Намного лучше было бы пойти "в тупую", и сделать category_id, product_id, sort, при этом для каждой категории с include_sub_categories=1, добавлять записи в таблицу, выбирая все товары из дочерних категорий, и привязывая их к текущей, образуя лесенку. Не знаю, на сколько тяжелее с точки зрения добавления и удаления товаров, но выгода заметна не вооруженным глазом.

Я не поленился, написал рекурсивный CTE, создал таблицу, где category_id и product_id пара создается для каждой дочерней категории, если include_sub_categories = 1, перенес туда все данные из старой категории.

Вот что я получил:

  1. Теперь запросы строятся через WHERE category_id = N, это позволяет в один прыжок спустится по дереву BTREE, и получить все товары, а не передвигаться по листьям дерева. Да и больше нету этого IN (1, 2, 3, ..., 10050)
  2. Все запросы с агрегатами выполняются значительно быстрее.
  3. Не нужно делать DISTINCT. PK по category_id, product_id это гарантирует.

На моем примере, в исходной таблице, как сейчас хранит Shop-Script - 28000 записей. В модифицированной таблице - 46635. Даже если в ней будет в 10 раз больше записей, она все равно будет работать быстрее. 

Предыдущий запрос на выборку, включая добавленный индекс, занимает 0.027 sec (0.531 sec без индекса), при этом новая структура позволяет делать выборки за 0.003 sec. Такая структура ускорит не один запрос, а минимум три - на выбор мин-макс цены, на выбор товаров, на подсчет количество товаров в категории. На скорости загрузки страницы, и на нагрузке, это скажется очень позитивно.

Проблема четвертая: MyISAM

Я понимаю, что это тяжело, но я думаю уже пора переезжать на InnoDB. Там и FK CONSTRAINT, и кластеризированные индексы, и транзакции, и многое-многое другое. Да и вообще, сам движок развивается намного активней, чем MyISAM, который оставлен с целью совместимости. Не знаю, чтобы кто-то в 2018 году использовал MyISAM. 

Результат

Вот примерный результат до создания индекса, используя старую структуру таблиц, и новый результат: после создания индекса, и с новой структурой. Учтите, что на MyISAM было не 0.5 секунд, а 12, т.е. это... в 3000 раз быстрее! Надеюсь, хотя бы индексы после этого поста добавят в таблице. 

10 ноября 2018
  • Syrnik.com 10 ноября 2018 15:20

    +1

  • outcast 10 ноября 2018 15:27

    +1 сам через это прошел

  • km 10 ноября 2018 17:25

    +1 но много нюансов

  • Евгений Леман 11 ноября 2018 06:54

    Ну нельзя же просто пройти мимо. +1

  • Quasar 11 ноября 2018 19:53

    В идеале, конечно, взять какую-то жирную базу, и при публикации плагинов так-же проверять на то, сколько выполняется вопрос по времени, как по мне, любой запрос, который выполняется дольше 100 мс. и работает на странице категории/товара/главной, должен быть либо переписан, либо закеширован. Вот, например, запрос, который возвращает 10 000 строк, в двух плагинах одного и того-же автора:

    Сам запрос работает за 400 мс., это конечно не 12 секунд, но все-же не понятно, а нужны ли все эти 10к строк, или можно исправить запрос чтобы он возвращал меньшее количество строк? Или хотя-бы закешировать? 

    SELECT pf.*, 1
    FROM shop_product_features pf
    JOIN shop_product_features_selectable pfs ON pf.product_id = pfs.product_id AND pf.feature_id = pfs.feature_id
    WHERE pf.sku_id IS NOT NULL
      AND pf.product_id IN
          (10004519, 30104, 33877, 10016375, 10015693, 10008954, 10003753, 10005061, 10012749, 10008161, 10006148, 10008032,
           10008947, 10006081, 10008199, 10009912, 10012830, 10016198, 10007920, 10015692, 10008956, 10015694, 10016893,
           10008261, 10009907, 10016359, 31335, 10005352, 10015767, 10005157, 10008046, 34376, 10016111, 33235, 10008940,
           10015613, 10008111, 10009695, 10009898, 10017352, 10005920, 32078, 10000374, 10009595, 10017353, 32378, 10017374,
           10006957, 10003900, 10001115, 10009027, 10008900, 10005353, 10005685, 10007429, 10004999, 10009742, 10007843,
           10000652, 10003088);

    В моем случае решилось через добавление хинта для оптимизатора:

    SELECT STRAIGHT_JOIN ...

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


    Также, хорошей практикой было бы сортировать список, который попадает в IN, для того, чтобы query_cache, если уж он включен, работал. Потому что кеш сравнивает запросы побайтово. Можно, например, добавить метод addIn, который принимаем массив, и сортирует его. Все равно результирующий запрос не сортируется по порядку переданных в IN значений, а время выборки не из кеша все равно дольше чем сортировка массива. И это не уже не говоря о том, что query_cache лучше вообще не использовать, кешировать нужно в самом фреймворке.

    Надеюсь, что вебасист займеться скоростью работы фреймворка, т.к. это влияет и на конверсию в том числе. А то поставил пару плагинов, и имеем в итоге 2-4 секунды на любую страницу на фронтенде. 

  • MDcode 12 ноября 2018 08:02

    +1

  • enso_studio@mail.ru 12 ноября 2018 22:24

    Вообще кеширование вещь полезная и нужная, но данный пост увы одобрить не могу.

    Нужно как минимум вначале привязать приложение shop к mysql в requirements.php.

    Да и вообще, сам движок развивается намного активней, чем MyISAM, который оставлен с целью совместимости. Не знаю, чтобы кто-то в 2018 году использовал MyISAM.

    Не знаю как сейчас, но раньше MyISAM давал выигрышь в скорости запросов для таблиц с "постоянным" контентом, например, список стран\регионов.

  • Nail 15 февраля 2019 09:21

    +1

    А можете скинуть новую структуру базы - на которой получился такой выигрыш в скорости?

  • Quasar 15 февраля 2019 10:56

    Из изменений я делал только индекс и InnoDB, остальное затрагивает код.

  • Иванович 5 апреля 2019 11:06

    Добрый день! Поможете реализовать ускорение для ИМ в облаке? 

  • Александр - TOPLAB.ru 10 апреля 2019 15:31

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

  • enso_studio@mail.ru 11 апреля 2019 12:26

    @Александр, а это сообщение удалят))



Чтобы добавить комментарий, зарегистрируйтесь или войдите