Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Подсчет кол-ва уникальных записей при больших объемах данных. / 21 сообщений из 21, страница 1 из 1
07.11.2013, 18:26:00
    #38456770
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Приветствую!

Столкнулся с такой проблемой.
Есть таблица большого размера (на данный момент 300+ млн. записей и постоянно растет):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE active_user_devices (
    date      DATE            NOT NULL,
    game_id   INT    UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NOT NULL,

    INDEX (date),
    UNIQUE INDEX (game_id, date, device_id)
  )
  ENGINE = MyISAM
  DEFAULT CHARSET = utf8;



Появилась необходимость регулярно получать кол-во уникальных пользователей за определённый период времени (например, за прошлый год) для одной, двух, N-го количества игр. Есть серьезное опасение, что время выполнения подобных запросов превысит все нормальные сроки. Что можно сделать для оптимизации?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 18:30:24
    #38456779
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Рядом же практически аналогичный топик - http://www.sql.ru/forum/1057783/ogromnaya-i-bystrorastushhaya-tablica
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 18:33:48
    #38456784
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
miksoft,

я этот топик просмотрел, но полезного ничего не нашлось. Точнее, всё что предлагалось либо уже делалось, либо не подходит к решению данной задачи :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 18:39:30
    #38456796
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Эти варианты - 15092619 - чем не устраивают?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 18:40:50
    #38456798
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
sp33dдля одной, двух, N-го количества игр.Вот это фрагмент опишите подробнее.
Возможно, тут будет закавыка...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 18:57:47
    #38456831
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
OLAP?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 18:58:12
    #38456833
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Есть несколько групп игр, и для каждой группы нужно посчитать кол-во пользователей за период. Если подробнее, то сейчас все сводиться к запросу типа:
Код: sql
1.
2.
3.
4.
5.
6.
SELECT
  COUNT(DISTINCT device_id) AS count
FROM active_user_devices
WHERE
  game_id IN (1, 2, 3, 4, 5)
  AND date BETWEEN '2013-09-01' AND '2013-10-01'



Проблема в том, что нельзя посчитать уникальных пользователей по каждой игре отдельно, а для группы - просто сложить. Пользователи могут играть в несколько игр, а значит и пересекаться.
Аналогично и с датами. Сумма уникальных пользоваталей за вчера и сегодня отдельно будет больше или равна кол-ву таких пользоваталей за эти дни, если считать их вместе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:07:29
    #38456852
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
sp33d,

какого масштаба величины общее количество пользователей и общее количество игр?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:09:19
    #38456853
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
sp33dЕсть несколько групп игрГруппы насколько фиксированы? Игры могут перемещаться между группами?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:14:43
    #38456865
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
miksoft,

Общее кол-во пользоваталей большое, 50+ млн., игр около 500. Группы можно считать фиксированными.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:15:59
    #38456868
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
sp33d,

а много ли групп?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:32:12
    #38456882
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Хм, хорошего решения сейчас не вижу.

Можно попытаться ускорить имеющийся запрос.
Можете показать план запроса?
Возможно, поможет перестановка порядка полей в индексе. Но какой именно порядок окажется лучше - без экспериментов не скажу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:40:12
    #38456893
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
miksoft,

на данный момент 2 группы. В одной порядка 25-30 игр, в другой около 200. И есть игры без групп - они в данной задаче неважны. С индексами я ещё поиграюсь, может что и получится, но судя по EXPLAIN, UNIQUE INDEX используется.

А что вы имеете в виду под планом запроса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 19:49:01
    #38456904
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
sp33dС индексами я ещё поиграюсь, может что и получится, но судя по EXPLAIN, UNIQUE INDEX используется.

А что вы имеете в виду под планом запроса?план и есть EXPLAIN
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 20:34:59
    #38456951
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
miksoft,

Не знаю как принято показывать. Вот:
Код: plaintext
1.
2.
id  select_type   table                 type    possible_keys           key               key_len   ref   rows      Extra
1   SIMPLE        active_users_unique   range   game_date_device,date   game_date_device  7         NULL  24316985  Using where; Using index
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 20:41:44
    #38456962
transpose
transpose
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Обязательно ли иметь разрешение в один день?
Если нет, огрубляйте старые данные по неделям, месяцам, годам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 20:45:15
    #38456966
transpose
transpose
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Или вот еще http://dev.mysql.com/tech-resources/articles/partitioning.html есть. В зависимости от ваших особенностей можно порубить на куски по играм или временным интервалам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2013, 22:09:08
    #38457030
lookat
lookat
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
Модератор: Прошу прекратить бессодержательую рекламу вашего сайта. Еще раз - будет бан.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2013, 02:21:58
    #38458583
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
transposeОбязательно ли иметь разрешение в один день?
Если нет, огрубляйте старые данные по неделям, месяцам, годам.

Обязательно. Старых данных почти и нет. Эти 300 млн. записей результат всего лишь полутора лет работы...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.11.2013, 21:51:43
    #38461129
transpose
transpose
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
sp33dmiksoft,

Общее кол-во пользоваталей большое, 50+ млн., игр около 500. Группы можно считать фиксированными.

можно попробовать заменить
Код: plsql
1.
select count(distinct device_id) group by game_group_id, date

на
Код: plsql
1.
select sum(game_group_indicator_1), sum(game_group_indicator_2) etc. group by date


Для этого нужно ввести новые колонки для каждой группы (или вообще новую таблицу) и каждому юзверю ставить туда единичку, когда и если он в этот день первый раз начнет играть в какую-нибудь игру из группы. Во всех остальных случаях нолик.
Как вы это будете рассчитывать и насколько это выполнимо с точки зрения размера данных - я заранее сказать не могу. Если вы будете создавать отдельную таблицу - она будет кардинальности [device_id]x[game_group_id]x[date] Ну или даже меньше, если туда нолики не писать. Что по вашем словам в 50-100-200 раз меньше чем (game_id, date, device_id).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.11.2013, 21:21:12
    #38462687
sp33d
sp33d
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подсчет кол-ва уникальных записей при больших объемах данных.
transpose,

хм, мысль интересная. Надо будет попробовать. Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Подсчет кол-ва уникальных записей при больших объемах данных. / 21 сообщений из 21, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 336ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1835736]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 36ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 207ms
total:  336ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]