SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 06.06.2018 22:29:16

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Максимальное к-во событий в минуту

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

SELECT
  DATE(d) d, max(Cnt) MaxCnt
FROM (
SELECT d, COUNT(*) Cnt
FROM t join t c on c.d BETWEEN t.d AND t.d + INTERVAL 1 MINUTE
GROUP BY 1) m
GROUP BY 1;

К сожалению, на моих небольших объемах данных, выполняется недопустимо долго.
Может есть более оптимальные алгоритмы?

Отредактированно klow (06.06.2018 22:30:24)

Неактивен

 

#2 06.06.2018 22:50:43

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3823

Re: Максимальное к-во событий в минуту

Более оптимальным алгоритмом было бы скользящее окно длительностью минуту. То есть начинаете в отсортированном по времени списке с первого id1, находите для него последний id2 в пределах минуты. Затем инкрементируете id1 и увеличиваете id2 насколько возможно для сохранения интервала в пределах минуты. Все будет сделано за один проход. Такое легко можно сделать на клиенте, но средствами MySQL это сложнее.

Неактивен

 

#3 06.06.2018 23:16:49

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6691

Re: Максимальное к-во событий в минуту

Мне кажется, задача сильно упростилась бы, если бы было «в конкретную минуту». В чем смысл скользящего окна? Мы на выходе ожидаем получить всё равно конечное количество строк (== не непрерывное), поэтому мы в любом случае будем группировать по фиксированным диапазонам времени. Ну и тогда оно решится одним полным сканом с группировкой по функции.

Ну и для наглядности пример. Пусть есть всего три события — 0:00, 0:30 и 1:00. Что мы ожидаем получить на выходе? Если [2, 1], то мой способ годится. Ваш способ, кстати, посчитает 1:00 трижды, что кажется неправильным.

Неактивен

 

#4 07.06.2018 00:10:22

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6691

Re: Максимальное к-во событий в минуту

Я тут подумал, что есть предельный случай в «непрерывной задаче» — это точность позиционирования событий. Если вы используете datetime/timestamp, то самое точное, что Вы сможете получить, — это секунды. Соответственно, можно считать количество событий в секунду, это даст наиболее точный график (ну и к RPM можно привести умножением на 60). Но опять же — я бы обошелся группировкой по минутам.

Неактивен

 

#5 07.06.2018 06:59:11

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Re: Максимальное к-во событий в минуту

Спасибо за ответы!
1. Группировка по минутам может дать точность в 50%. Это многовато для моей задачи. Кстати, это было первое мое решение.
2. Использую поле DATETIME.
3. Мой алгоритм оказался не только медленным, но не точным. Если в один момент есть два (несколько) события, то результат будет задваиваться и т.д. Для меня это было неожиданно, не думал, что одновременно возможно два события. Возможно это связано с группировкой по дате.
В связи с тем, что события, в основном, идут последовательно я использовал ID (первичный ключ).

SELECT
  DATE(d) d, max(Cnt) MaxCnt
FROM (
SELECT t.id, t.d, COUNT(*) Cnt
FROM t join t c on c.id BETWEEN t.id AND t.id + @MinEvent
  AND c c.d BETWEEN t.d AND t.d + INTERVAL 1 MINUTE
GROUP BY 1, 2) m
GROUP BY 1;

Где @MinEvent - минимальное число - количество событий в минуту, которое не может наступить.
Мало того, что он дает производительность на два порядка лучше, но он исключает ошибки, когда события могут быть в один и тот-же момент (как?!).
Только я не понял, почему нужна группировка по полю t.d если есть группировка по ID? Но если ее не делать, что запрос выполняется очень долго.

Отредактированно klow (07.06.2018 07:15:34)

Неактивен

 

#6 07.06.2018 08:45:50

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3823

Re: Максимальное к-во событий в минуту

Нужно смотреть на разницу планов исполнения (EXPLAIN).

Как задается величина @MinEvent? Решение опирается на то, что id в базе должны идти подряд без пропусков, что не гарантировано стандартом.

Неактивен

 

#7 07.06.2018 08:58:25

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Re: Максимальное к-во событий в минуту

rgbeast написал:

Как задается величина @MinEvent?

Просто константа.

rgbeast написал:

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

Да, согласен, в общем случае это неприменимо, но в моем вполне допустимо.

Неактивен

 

#8 07.06.2018 09:33:07

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Re: Максимальное к-во событий в минуту

EXPLAN при группировке по дате
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    t    ALL    PRIMARY    (null)    (null)    (null)    738406    Using where; Using temporary; Using filesort
1    SIMPLE    c    ALL    PRIMARY    (null)    (null)    (null)    738406    Range checked for each record (index map: 0x1)
Без группировки по дате
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    t    ALL    PRIMARY    (null)    (null)    (null)    738412    Using where; Using temporary; Using filesort
1    SIMPLE    c    ALL    PRIMARY    (null)    (null)    (null)    738412    Using where

Разница в количестве строк, думаю, объясняется новыми событиями между запросами.

Отредактированно klow (07.06.2018 09:39:48)

Неактивен

 

#9 07.06.2018 10:09:03

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Re: Максимальное к-во событий в минуту

Все, окончательно запутался.
Сделал ограничение по дате (t.d > '2018-06-05' ) для первого случая, EXPLAN не изменился, но это предсказуемо - там нет индекса.
Сделал ограничение по ID (идут последовательно) t.id > 764900 AND c.id > 764900
EXPLAN
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    t    range    PRIMARY    PRIMARY    4    (null)    11448    Using where; Using temporary; Using filesort
1    SIMPLE    c    range    PRIMARY    PRIMARY    4    (null)    11448    Using where
Тоже все предсказуемо, но!
Время выполнения для ограничения по дате порядок(!) меньше ограничения по ID!? Это как?
кеш отключен SQL_NO_CACHE

Отредактированно klow (07.06.2018 10:25:04)

Неактивен

 

#10 07.06.2018 20:30:17

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5516

Re: Максимальное к-во событий в минуту

что будет со временем выполнения такого варианта?

SELECT
  DATE(d) d, max(Cnt) MaxCnt
FROM (
SELECT t.d, (select COUNT(*) from t c where c.id BETWEEN t.id AND t.id + @MinEvent AND c.d BETWEEN t.d AND t.d + INTERVAL 1 MINUTE) as Cnt
FROM t where t.id > 764900) m
GROUP BY 1;

Неактивен

 

#11 07.06.2018 21:27:45

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Re: Максимальное к-во событий в минуту

не дождался я результата, на 10-ой минуте прервал.
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    <derived2>    ALL    (null)    (null)    (null)    (null)    14952    Using temporary; Using filesort
2    DERIVED    t    range    PRIMARY    PRIMARY    4    (null)    14952    Using where; Using temporary; Using filesort
3    DEPENDENT SUBQUERY    c    ALL    PRIMARY    (null)    (null)    (null)    740181    Using where

Запрос, который я использую выполняется за 1,5 сек.
Остается непонятным:
1. Почему нужна группировка по дате?
2. Почему ограничение по дате (нет индекса) работает лучше чем ограничение по первичному ключу?

Отредактированно klow (07.06.2018 21:46:56)

Неактивен

 

#12 07.06.2018 21:46:11

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 355

Re: Максимальное к-во событий в минуту

Реально таблица содержит еще поля, но я их не использую для данной задачи, поэтому, думаю, они не должны влиять на результаты.

CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  type_id int(11) NOT NULL DEFAULT 1,
  Obj_id int(11) DEFAULT NULL,
  RequestBegin timestamp NULL DEFAULT NULL,
  RequestDuration int(11) DEFAULT NULL,
  UserName varchar(100) NOT NULL,
  CorrDate datetime NOT NULL, -- ДАТА события
  Result tinyint(1) NOT NULL,
  Msg varchar(2000) DEFAULT NULL,
  MsgErr varchar(2000) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 772635,
AVG_ROW_LENGTH = 83,
CHARACTER SET utf8,
COLLATE utf8_unicode_ci;

ALTER TABLE t
ADD INDEX IDX_t_type_id (type_id);

ALTER TABLE t
ADD INDEX IDX_t_UserName (UserName);

ALTER TABLE t
ADD CONSTRAINT FK_t_objs_id FOREIGN KEY (Obj_id)
REFERENCES o (id) ON DELETE CASCADE ON UPDATE CASCADE;

Отредактированно klow (07.06.2018 21:46:44)

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson