Насколько дорогая операция GROUP BY() + SUM()?

Question

[entermix]

Допустим есть таблица:

statistics

date (int), user_id(int), param1(int), param2(int), param3(int) ...

Настроены индексы:
uniq_user_id_date
fk_user_id

Делаем примерно такую выборку:

$sql_date_format = '%Y-%m';
// $sql_date_format = '%Y-%m-%u';
// $sql_date_format = '%Y-%m-%d %H:00:00';
// ...
        $statistics_obj = DB::select()
            ->select(
                ['date', 'date'],
                [DB::expr('DATE_FORMAT(FROM_UNIXTIME(`date`), :date_format)', [
                    ':date_format' => $sql_date_format,]), 'date_formatted',],
                [DB::expr('SUM(`param1`)'), 'param1']
            )
            ->from(ORM::factory('Statistic')->table_name())
            ->where('user_id', '=', $user->id)
            ->and_where('date', 'BETWEEN', [$this->_start, $this->_end])
            ->group_by('date_formatted')
            ->as_object()
            ->execute();

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

UPD: Эта информация будет использована для построения графиков (живая статистика в ЛК). Графики и таблицы (по часам/по неделям/по месяцам/по годам)

Comments

[sim3x]

Зачем гадать?

Сделайте таблицу, заустите запрос на своем реальном железе, со своими настройками

[entermix]

sim3x, интересует опыт других пользователей.

Answer 1

[ApeCoder]

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

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

Как альтернатива, проиндексировать форматированную дату

Заполните таблицу примерно похожими по распределению данными и посмотрите план запроса.

Answer 2

[m0nym]

Что значит в вашем представлении медленные?
Какое железо?
Какие индексы?

И че гадать-то - нагенерируйте случайных данных и проверьте.

Comments

[entermix]

> Что значит в вашем представлении медленные?
Эта информация будет использована для построения графиков. (по часам/по неделям/по месяцам/по годам)

> Какое железо?
Core i7-7700 4.2ГГц (4 ядра) / 16Гб RAM / 2x256Гб SSD

> Какие индексы?
Индексы указаны в вопросе: uniq_user_id_date, fk_user_id

>И че гадать-то - нагенерируйте случайных данных и проверьте.
Интересует опыт других пользователей.

[m0nym]

entermix,

Интересует опыт других пользователей.

Опыт других говорит, что критерии "хорошо-плохо" суть очень разные.

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

На моей практике график, что строится раз в неделю согласны были ждать и по 15 минут.
Они конечно хотели бы сократить до 5 секунд, но когда узнали стоимость работ (не такую уж и высокую) - отказались.

Любой ответ здесь подразумевает, что отвечающий какие-то свои сценарии подразумевает.

[entermix]

m0nym, статистика в реальном времени, построение графика может запрашиваться любым пользователем (просто при открытии странички), графиков много. Кешировать на стороне клиента можно, но не хотелось бы.

[m0nym]

entermix, здесь нужен индекс по полям, которые участвуют в group by и во where.
Причем индекс для BETWEEN типа TREE, а для прочих - типа HASH

Answer 3

[Александр Аксентьев]

Рано или поздно выборки становятся слишком жирными для построения отчетов/графиков/чего угодно на лету.

Может лучше сразу выбрать что-то более подходящее для этого
https://habr.com/company/oleg-bunin/blog/329062/

Comments

[entermix]

Вы правы по поводу специализированной БД, но пока что нужно ограничится средствами MariaDB (10.3).

Дело в том, что эта таблица представляет собой срезы статистики, чтобы не делать сложные выборки с других таблиц. Сюда сбрасывается вся информация, которая собирается посуточно (одна запись для date + user_id), никаких проблем - все быстро и четко, вот сейчас появилась необходимость сделать ее почасовой, а возможно и поминутной.

[m0nym]

entermix,

Вы правы по поводу специализированной БД, но пока что нужно ограничится средствами MariaDB (10.3).

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

[entermix]

m0nym, Это и есть вспомогательная таблица

[Maxim]

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

[entermix]

Maxim, подскажите, что конкретно Вы имеете в виду?

[Maxim]

entermix, если нарезать таблицу на куски (partitions) по определенным параметрам, например по датам, скорость выборки может увеличить. За счёт того что СУБД основываясь на информации о партицировании, будет обращаться в итоге к меньшему набору данных.
https://mariadb.com/kb/en/library/partitioning-ove...

[entermix]

Maxim, теперь понял, но, в данный момент, информации не настолько много, чтобы делатьть это. В данном случае я задал вопрос, чтобы просто разобраться в том как работает функция GROUP BY :).