Как ускорить поиск по бд MySQL?

Question

[viktorross]

Хотелось бы понять как можно ускорить поиск товаров в базе данных MySQL, когда приходится использовать большое количество Id категорий в in. У меня увеличивается количество подкатегорий, неизвестно сколько будет, но допустим 10 000 тыс в общей сложности, структура такая, основная категория -> подкатегория -> подкатегория -> возможно ещё одна категория. Я открываю страницу с основной категорией, где высыпается каталог продукции соответствующей всем подкатегориям основной категории, т.е захватываю абсолютно все вложенные категории, и соответственно формируется такой запрос (and table.product in (сто штук id подкатегорий)), я понимаю, что в дальнейшем, чем больше товаров будет и чем больше категорий, тем медленнее будет запрос, а он уже достаточно медленный «0.0090» сек учитывая group_concat содержащийся в запросе и 3 left join’а. Сейчас всего 15 тыс позиций в бд, а что будет, когда станет 500 тыс позиций, как быть чтобы не использовать 100 штук id категорий при выборке?

Comments

[Алексей 〒.]

По описанию структуры не понятно почеме для страницы категории извлекаются все товары через подкатегории, вместо того что бы добавить прямую связать товара с этой категорией?

[Akina]

учитывая group_concat содержащийся в запросе и 3 left join’а

А где структуры таблиц, сам запрос и план его выполнения? или мы всё это сами придумать должны?

[Михаил Ливач]

Алексей 〒.,

По описанию структуры не понятно почеме для страницы категории извлекаются все товары через подкатегории, вместо того что бы добавить прямую связать товара с этой категорией?

Это как раз понятно. Есть родительская категория и 3 уровня вложенности подкатегорий. Товары привязаны только к конечным ( листовым) категориям. Привязывать товар ко всем уровням иерархии - это трёхкратный оверхед.
Индекс по таблице связи разделов с товарами должен нормально работать...

Answer 1

[Rsa97]

Добавить категории и товарам Materialized Path. Тогда вместо IN будет индексируемый LIKE.

Comments

[Руслан .]

Тут есть нюанс, на очень длинных строках индексы плохо работают. И этот вариант можется скатиться к table scan.
В задасе заявлены категории и товары, т.е. вероятность длинных путей очень большая.

[Rsa97]

Руслан ., Materialized Path можно составлять по id категорий, тогда он не будет длинным.
1/15/371/575
vs

noutbuki-kompyutery-komplektuyushchie/monitory-i-aksessuary/monitory/monitury_48"

[Руслан .]

Rsa97, это да. Но тогда возможно все придет к исходному варианту с джойнами. Так как юзер пишет текст, а по тексту нужно наиграть строку айдишников.

[Руслан .]

Rsa97, У меня была похожая задача, но мне не требовался like, а хватало полного совпаления пути. И чтобы индекс работал я создал отдельное поле, где зранил sha256 хэш от пути и это поле уже нормально индексиркется. Правда для поиска нужно вычислять хэш от пути. И при вставке новых категорий нужно проверять коллизии, хотя вероятность коллизий не большая.

[Rsa97]

Руслан ., Исходная задача - поиск всех товаров категории - решается через индексируемый path LIKE CONCAT(:categoryPath, '%') И, ЕМНИП, если для поля пути задать однобайтную кодировку, то в современном MySQL получим ключ 3072 символа, что при id категорий до миллиона даст вложенность более 400 уровней. Для магазина, думаю, хватит. И даже для хранения пути по именам с длиной имени до 128 символов в однобайтной кодировке получим 23 уровня вложенности.

[Руслан .]

Rsa97, однобатовые скорее всего не выйдет использовать если это магазин и там товары на кирилице, то будет юникод.

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

[Rsa97]

Руслан ., Каждое поле может иметь свою кодировку, так что ничто не мешает использовать latin1 для пути и utf8mb4 для названия категории/товара.

Answer 2

[Ипатьев]

Хотелось бы понять как можно ускорить поиск товаров в базе данных MySQL, когда приходится использовать большое количество Id категорий в in.

Дело не в большом количестве, а в том, сколько уходит времени на поиск одной. Если у колонки нет индекса, то поиск любого количества будет медленным. А если есть индекс, то будет быстрым.

Плюс непонятно, с чего вы решили, что проблема именно в in. Если у вас сто тыщ товаров, и все их надо отсортировать или сгруппировать, то проблема вообще не в in. А в той самой сортировке или группировке. И надо подбирать составной индекс специально под них.

Чтобы ускорить поиск по бд MySQL, надо не писать на тостер глубокомысленные умозаключения про три джойна и сто тыщ товаров, а выполнить команду EXPLAIN и начинать тупить в её вывод. или по крайней мере привести его в своем вопросе, чтобы он стал предметным.

Comments

[Rsa97]

Это явно поиск не по первичному ключу.

[Ипатьев]

Rsa97, Ээээ, да, ступил, спасибо

[batyrmastyr]

> чего вы решили, что проблема именно в in

Мускуль плохо переваривает IN по куче значений.
Емнип, где-то на тысячах начинаются проблемы.

Answer 3

[Steel_Balls]

В первую очередь надо построить индексы по тем полям, по которым будет производиться поиск

Comments

[Ипатьев]

В первую очередь надо смотреть explain. А потом уже строить индексы.

[batyrmastyr]

Подушню: EXPLAIN ANALYZE даже до мускуля добрался несколько лет как

Answer 4

[batyrmastyr]

Сымитируйте ваши 100500 категорий.
Если нужно ускорять, то вас спасёт денормализация: в дополнение к идентификатору текущей категории (category_id) у товаров появятся идентификаторы категорий высоких уровней (category_level1_id, category_level2_id, category_level3_id) и на первых уровнях фильтровать по выделенным столбцам.

Цена: усложнение логики, новые индексы, нужно периодически запускать скрипт исправляющий значения в новых колонках после перетасовки категорий.