Как можно ускорить выполнение SQL запроса?

Question

[Владимир Рыжов]

MySQL запрос:

select `product_id`, count(*) as `cnt`
from `product_prop`
where `prop_id` in (1,2,3,...)
group by `product_id`
order by `cnt` desc

В таблице две колонки `product_id` и `prop_id`.
Индексы: product_id, prop_id, product_id+prop_id уникальный
В таблице 85М записей, будет больше, и если `prop_id` in (1,2,3,...) штук 20, может быть и больше, запрос выполняется 18 секунд, что прям совсем много. Если убрать сортировку, то выполняется моментально, но без неё никак. Пробовал having, но то же самое, что, как бы, и логично.

Comments

[Sergey]

Пробовали подзапросы составлять?

[Владимир Рыжов]

Sergey, выносил where in в подзапрос - никаких изменений

[Everything_is_bad]

Тут новые индексы особо и не помогут, чтобы сделать сортировку, в любом случае надо посчитать count для всех product_id, надо разбираться во что упирается в процессор или диск, либо подняться на уровень бизнес логики и перестроить задачу с учетом проблемного места.

[Владимир Рыжов]

Everything_is_bad, по логике нужно получить список id продуктов, имеющий указанный список id свойств. В данном запросе результаты сортируются по убыванию этих самых совпадений.
Надо наверное это всё на проде затестить, может это он локально на столько сильно долгий.

[otdameskapizm]

можете попробовать вместо IN использовать оператор EXISTS. А вообще в идеале неплохо было бы query plan посмотреть

[Sergey]

Владимир Рыжов, а так?

Select `product_id`, `cnt` from (select `product_id`, count(*) as `cnt`
from `product_prop`
where `prop_id` in (1,2,3,...)
group by `product_id`) as q1
order by `cnt` desc

[Сергей Горностаев]

Владимир Рыжов, если на проде больше ресурсов, то запрос может выполняться быстрее, но принципиально ничего не изменится, сортировка большого объёма данных без индекса быстрой быть не может. Вероятно, стоит посмотреть в сторону эластика, который с подобными задачами справляется на много лучше реляционной СУБД и лучше масштабируется горизонтально.

[Владимир Рыжов]

Sergey, так же долго как и мой вариант

[Akina]

Sergey, а какая разница, кэшировать выборку просто так или как подзапрос?

[V1nn1]

Колонки в индексе product_id+prop_id именно в таком порядке? Т.к. вам нужен prop_id на первом месте

[Владимир Рыжов]

V1nn1, этот индекс не используется, так что порядок не важен. Перевожу это на meilisearch, там всё шустро.

[V1nn1]

Владимир Рыжов, а как он может использоваться, если он не подходит?)

[Владимир Рыжов]

V1nn1, весь запрос упирается в "order by `cnt` desc", сортировка по вычисляемому полу, на котором и индекса нет, при тестировании мне вернуло 2М записей, которые нужно сортировать по этому полю, если сортировку убрать, то всё летает. Так что упирается оно точно не в этот индекс.

Answer 1

[Akina]

если `prop_id` in (1,2,3,...) штук 20, может быть и больше, запрос выполняется 18 секунд, что прям совсем много.

Критичное количество значений в таком списке - порядка 7..10, дальше действительно запрос резко замедляется.

Workaround: создаём индексированную временную таблицу в памяти, записываем в неё набор значений, используем в запросе:

CREATE TEMPORARY TABLE criteria (prop_id INT PRIMARY KEY) ENGINE = Memory
SELECT prop_id FROM (VALUES ROW(1), ROW(2), ROW(3), ...) criteria (prop_id);

и потом

select `product_id`, count(*) as `cnt`
from `product_prop`
NATURAL JOIN criteria 
group by `product_id`
order by `cnt` desc

Ну а ускорить ORDER BY по вычисляемому полю - вообще без шансов.

Comments

[Владимир Рыжов]

Не обязательно сортировать по вычисляемому полю, достаточно было бы `cnt` = количеству параметров, но это картины не меняет

[Dmitry Bay]

А есть практические случаи (статьи), где можно почитать о таком подходе?

[Akina]

Dmitry Bay Данный метод и указанный лимит - они получены в основном эмпирически. Хотя если долго разбираться с тонкими настройками оптимизатора, то там точно есть необходимая информация. Кто-то лет несколько этим даже озаботился, но у меня концов от этого материала не осталось.

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

[Dmitry Bay]

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

Я бы просто с прикола прогрел бы кэш на редисе. а там бы уже смотрел, сработало бы или нет.
А дальше бы сделал матрицы по prop_id и доп параметрам если нужно, и складывал бы на бэке. Не уверен что это было бы решение, но я бы попробовал так выкрутиться.

Answer 2

[Олег]

как понимаю это сортировка товаров от наиболее подходящих до обладающих 1м из признаков.
Сделай вспомогательную таблицу статистики сколько товаров всего с заданным проп_ид.
обновление по тригерам.

потом смотришь в нее и береш максимальный из (1,2,3...) например 3
Далее запрос будет из двух проекций (A и B) на твою таблицу
select product_id from `product_prop` A where prop_id=3 (этим мы уходим от фулскана по 85М)
и добавляем подзапрос посчитать сколько совпало осталных свойств
select product_id,(... подзапрос .....) from `product_prop` A

Comments

[Олег]

select count(*) from from `product_prop` B
where `prop_id` in (1,2,3,...) and B.product_id = A.product_id

[Олег]

млин. минус моего подхода потеряются товары только со свойством 2 и 1.
В общем Тут наверное не муська нужна, а сфинкс с его MVA
Но там глубина поиска ограничена и все товары не выведет.

Answer 3

[YepBro]

План запроса, пожалуйста :)

Результат запроса полностью помешается в память? peak_memory_used (из EXPLAIN filesort_summary) больше чем sort_buffer_size из настроек базы?

Запрос похоже никак не оптимизировать. Вот обсуждение подобной проблемы https://stackoverflow.com/questions/75306623/order...

Надо или тюнить mysql или думать над каким-то кешированием/промежуточными расчетами (хотя пока не вижу что тут можно кешировать).

Comments

[Владимир Рыжов]

|1 |SIMPLE |product_prop|range|product_prop_prop_id_index|product_prop_prop_id_index|8 | |9887676|Using where; Using index; Using temporary; Using filesort|

[Akina]

Владимир Рыжов,

Using where; Using index; - берётся индекс по product_id+prop_id, по нему выполняется фильтрация (отфильтровывается порядка 10 млн. записей) и подбор групп записей

Using temporary; - подобранные группы записей копируются во временную таблицу, ибо их тупо много, и по завершении будут посчитаны агрегаты

Using filesort - посчитанные агрегаты сортируются без использования индексов

Answer 4

[mayton2019]

Можно материализовать. Перечислить все комбинации prop_id и создать такое представление

prop_ids | responce
---------|---------
'1,2,3'  | [ { "prod_001" : 200 }, { "prod_333" : 134 } , ..... ]
'2,3,4'  | [.....]

Comments

[Akina]

Угу... 85М записей, 20 критериев. Ты хоть прикинь, какой размер будет у такого представления...

[mayton2019]

Akina, я думаю что там не будет все сочетания из 20.

[Владимир Рыжов]

mayton2019, Akina,
6М продуктов и 2500 свойств, запросить могут продукты которые содержат 50 свойств, или 2000 (это будет какой-то маньяк и в результате получит не больше одного продукта).
Я так думаю лучше уже сфинкса или эластика прикрутить

[mayton2019]

Владимир Рыжов, ну... смотри. Если без перфекционизма. Я-же не говорю что ты должен построить
все сочетания свойств.

Знаешь есть правило типа Паретто. 80% перформанса поедает 20% кода. Вот здесь - тоже
самое. Построй гистограмму частот этих комбинаций. И окажется что их не очень много.
А редкие - игнорируй.

Answer 5

[Руслан .]

Может индекс поменять с product_id+prop_id на
prop_id + product_id. Отбор же по prop_id идет, а тут не попадание в первое поле индекса.

Comments

[YepBro]

Тормоза на процессе сортировки по count(*). Под это дело нет индекса.

В плане запроса видно, что индекс product_prop_prop_id_index используется для выборки по where `prop_id` in (1,2,3,...), составной индекс тут не нужен по сути.

[Akina]

Может индекс поменять с product_id+prop_id на prop_id + product_id.

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

[Руслан .]

Akina, тут не должно быть фулл скан.
Если в секции in небольшая выборка всех возможных значений, то не нужно весь индекс сканировать. Скан будет только по той части, где сработало условие по первому полю(это потому что индекс по сути дерево и его значения отсортированы)

[Akina]

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

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

[Руслан .]

Akina, не всегда быстрее. Если в выборку попадает 1% всех строк таблицы, то скан не быстрее. А группировка часто под капотом включает сортировку, это можно в плане запроса увидеть. Тут как оптимизатор посчитает правильным так исделает.

[Akina]

Руслан .,

Тут как оптимизатор посчитает правильным так исделает.

Ну на то и декларативный язык..

Если в выборку попадает 1% всех строк таблицы, то скан не быстрее.

Это верно. Но частный случай, который обычно приходится хинтовать. В комментариях к одному из ответов автор показывает план. Там видно, что отбирается где-то 11-12% записей.

А ещё - сервер неспособен предсказать количество записей, отвечающих многозначному критерию из WHERE IN. А если он тупо попытается получить оценку на основании index cardinality, то у него ничего хорошего не получится - ведь он получит приблизительное количество уникальных пар, чего маловато для оценки процента отбираемых записей, ибо у композитных индексов равномерность не сказать чтобы частое явление, и к тому же у него нет cardinality по префиксу (а из другого индекса он его брать не умеет). Да и не будет он предсказывать - чай, не JOIN оптимизирует.

Answer 6

[dedmaks]

На мой взгляд, сугубо поверхностный , без разбивки таблицы на части ( Аля продукт до 1м, 2м и т.д.), хранимок или переноса части логики касательно группировки, подсчёта на бек не обойтись для ускорения.

Answer 7

[dmitryklerik]

Для выполнения группировки создаётся временная таблица, а уже потом происходит сортировка по этой временной таблице. Группировка очень быстрая так как по индексу, а сортировка медленная так как в созданной временной таблице никаких индексов для сортировки уже нет и приходится сортировать без индексов (Using filesort). Вот что могу посоветовать:

1) Убедится что временная таблица создаётся в памяти, SHOW STATUS LIKE 'Created_tmp_disk_tables' не должно расти.
2) Убедится что сортировка происходит без создания временных файлов на диске. Для этого нужно убедится что SHOW STATUS LIKE 'Sort_merge_passes'; не растёт быстро. Если растёт то перед запросом сессионно прибавьте значение настройки sort_buffer_size.
3) Лучшее решение: записей у вас много, лучше придумать другую структуру данных чтобы отказаться от этого запроса.

PS На всякий случай приведите вывод EXPLAIN и EXPLAIN ANALYZE когда у вас prop_id` in (1,2,3,...) штук 20, интересно из-за чего увеличивается время запроса: из-за роста кол-ва записей которые попадают в выборку или из-за того что в какой-то момент MySQL решает что full table scan выгодней и отказывается от использования индекса

Comments

[Владимир Рыжов]

explain
|1 |SIMPLE |product_prop|range|prop_id_product_id_IDX,prop_id_IDX|prop_id_IDX|8 | |9887676|Using where; Using index; Using temporary; Using filesort|

analyze
|1 |SIMPLE |product_prop|range|prop_id_product_id_IDX,prop_id_IDX|prop_id_IDX|8 | |9887676|4793486.00|100 |100 |Using where; Using index; Using temporary; Using filesort|