Как эффективно и быстро посчитать древовидную структуру через mysql?

Question

[Дмитрий Тарасов]

Версия mysql 5.7.
В чем суть вопроса. У меня есть таблица tb_users c вот такой структурой.
CREATE TABLE tb_users (
id int not null unsigned primary key,
username varchar(20),
id_referer int unsigned INDEX
);

Думаю не нужно объяснять что такое рефер и реферал.

Мне нужно посчитать кол-во рефералов для 3 уровней для каждого юзера.

Я создал вот такой запрос

EXPLAIN SELECT `tb1`.*, COUNT(`t3`.`id`) as `one_referals`, count( `t4`.`id`) as `two_referals`, count( `t5`.`id`) as `three_referals`
FROM `tb_users` `tb1`
LEFT JOIN `tb_users` `t3` ON `t3`.`id_referer` = `tb1`.`id`
LEFT JOIN `tb_users` `t4` ON `t4`.`id_referer` = `t3`.`id`
LEFT JOIN `tb_users` `t5` ON `t5`.`id_referer` = `t4`.`id`
GROUP BY `tb1`.`id`
LIMIT 0, 20

Тратится на него очень много кастов.
Добавим в запрос EXPLAIN format=json

И получаем вот такой результат.

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

Ужасный большой запрос

SELECT `still_socialseti`.`youtube_video`, `still_socialseti`.`youtube_podp`, `still_socialseti`.`youtube_like`, `still_socialseti`.`youtube_money`, `still_socialseti`.`youtube_money_rekl`, `still_socialseti`.`vk_groups`, `still_socialseti`.`vk_repost`, `still_socialseti`.`vk_likes`, `still_socialseti`.`vk_money`, `still_socialseti`.`vk_money_rekl`, COUNT( `be`.`id`) `num_chs_isp`, COUNT( `br`.`id`) as `num_chs_r`, COUNT( `bw`.`id`) as `num_white`, `tb1`.*, `us`.`profit_ref_ad`, `us`.`profit_ref_1`, `us`.`profit_ref_2`, `us`.`profit_ref_3`, COUNT( `ca`.`id_user`) AS count_advertiser, COUNT(`be2`.`id`) `task_bl_u`, COUNT(`t3`.`id`) as `one_referals`, count( `t4`.`id`) as `two_referals`, count( `t5`.`id`) as `three_referals`
FROM `tb_users` `tb1` 
LEFT JOIN `still_socialseti` ON `still_socialseti`.`idu` = `tb1`.`id` 
LEFT JOIN `users_statistics` `us` ON `tb1`.`id`=`us`.`id_user`
LEFT JOIN `still_block_earnings` `be` ON `be`.`idb` = `tb1`.`id`
LEFT JOIN `still_block_reklam` `br` ON `br`.`idb` = `tb1`.`id`
LEFT JOIN `still_block_white` `bw` ON `bw`.`idb` = `tb1`.`id`
LEFT JOIN `users_statistics` `ca` ON `ca`.`id_user` = `tb1`.`id` AND `ca`.`money_spent_total` > 0
LEFT JOIN `still_block_earnings` `be2` ON `be2`.`idu` = `tb1`.`id`
LEFT JOIN `tb_users` `t3` ON `t3`.`id_referer` = `tb1`.`id`
LEFT JOIN `tb_users` `t4` ON `t4`.`id_referer` = `t3`.`id`
LEFT JOIN `tb_users` `t5` ON `t5`.`id_referer` = `t4`.`id`
GROUP BY `tb1`.`id`
ORDER BY `us`.`profit_ref_1` DESC LIMIT 0, 20

Касты для второго
Выполняется порядка 9 секунд это ужас.
Индекс везде есть.

Я думаю тут дело не в индексах, а в неправильном решении задачи.
Что-то тут явно делаю не так и не пойму что, и как сделать первый и второй запрос более быстрым и с наименьшими затратами.
Думаю что обход и подсчет рефералов как минимум неправильный.

tb_users = 70к записей
users_statistics = 70к записей.
still_block_earnings = 2к записей.
still_socialseti = 50к записей.
still_block_reklam = 1к записей.
still_block_white = 1к записей.

Comments

[twobomb]

Добавьте колонку referer_count, и добавляйте\убавляйте ее при добавлении\удалении пользователя или смене реферера

Answer 1

[Максим]

Я не очень помню когда в MySQL появилась WITH RECURSIVE, если у Вас заработает - это Ваше
И брошу пример как организовывал рекурсию на старых MySQL, надеюсь разберетесь

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '2') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

Comments

[Дмитрий Тарасов]

Максим, не совсем понимаю как с помощью этого посчитать рефералов для каждого уровня юзера, эта штука посчитает всех рефералов, даже хз до какого уровня))

[Максим]

Дмитрий Тарасов, Это же Ваша задача, введите переменную level и делайте на каждом уровне +1 и проверку на не больше 3. Я бросил общий пример, т.к. нет ни данных для теста, ни понмания как у Вас все организовано.

Answer 2

[serginhold]

тебе нужно выбрать структуру хранения дерева https://qna.habr.com/answer?answer_id=391617#answe...

Comments

[Дмитрий Тарасов]

serginhold, Использую "Список смежных вершин (Adjacency List)", ситуация не поменялась.
Данные стали только целостнее.

[serginhold]

с Adjacency List нормально работать можно только через рекурсию которая только в mysql 8 появилась, выбери любой другой способ, по-гугли блин хоть немного и сравни какой тебе лучше выбрать

Answer 3

[xmoonlight]

+3 колонки к каждому пользователю: в каждом поле - храните суммарное количество рефералов на 1-ом, на 1-м и 2-м, и на 1, 2 и 3-м уровне.
И не забывайте их обновлять при реге нового реферала.
При выборке - ничего не нужно будет считать!)