Mysql Оптимизация SELECT GROUP BY по нескольким полям

[Archil]

Всем привет. Тружусь над разработкой системы по управлению медийной рекламы на сайте. Одна из основных частей проекта - сбор статистики показов и кликов, при этом надо хранить страну и тип девайса, с которого совершен запрос.

Вот таблица статистики:

create table statistics
(
    visit_date int default '0' not null,
    sait_id int default '0' not null,
    block_id int default '0' not null,
    advertisement_id int default '0' not null,
    teaser_group_id int default '0' not null,
    teaser_id int default '0' not null,
    link_id int default '0' not null,
    device_type smallint(2) default '0' not null,
    advertisement_system varchar(255) default '' not null,
    country varchar(4) default '' not null,
    visit_guid bigint default '0' not null,
    id bigint not null auto_increment,
    primary key (visit_guid, visit_date, sait_id, block_id, advertisement_id, teaser_group_id, teaser_id, text_block_id, custom_widget_id, banner_id, link_id, device_type, country)
)
;

create index statisticsId
    on statistics (id)
;

comment on column statistics.device_type is 'Тип трафика, 1 - мобильный, 2 - десктоп'
;

В нее за день набегает в среднем 1,5 миллиона записей, значит в месяц около 45 миллиона записей.

Теперь самое главное. Нужно просматривать статистику с группировкой по 2-м полям. Например такой запрос:

SELECT
  sait_id,
  count(id) AS counter,
  link_id
FROM statistics
WHERE statistics.visit_date = 1498694400
GROUP BY sait_id, link_id;

вот его EXPLAIN:
1. MYISAM

2. INNODB

При 3-х миллионах записей MySQL падает, при 1-ом миллионе скорость выборки где-то 15-20 секунд.

Простановка индексов по 2-м полям не сильно помогла. Нужна ваша помощь для оптимизации самой таблицы statistics и запроса.

 

[fixxxer]

Я бы не советовал использовать для статистики РСУБД.
Посмотри в сторону ElasticSearch.

Если же очень хочется РСУБД - делай пачку таблиц и агрегируй на лету или кроном.

 

[Фанат]

я бы попробовал сделать индекс по (sait_id, link_id) и считал не id, а коснтанту, * например.

 

[Archil]

Я бы не советовал использовать для статистики РСУБД.
Посмотри в сторону ElasticSearch.

Если же очень хочется РСУБД - делай пачку таблиц и агрегируй на лету или кроном.

ElasticSearch - это отличная идея, но задача стоит построить систему именно на MySQL

я бы попробовал сделать индекс по (sait_id, link_id) и считал не id, а коснтанту, * например.

Разницы в скорости не заметил, к тому же подобных индексов надо будет много, так как нужно будет делать групповые выборки по всем колонкам.

 

[Archil]

Если же очень хочется РСУБД - делай пачку таблиц и агрегируй на лету или кроном.

Если мне нужно будет агрегировать по 8-и колонкам, то это получается, что мне нужно будет 8*7=56 таблиц? Агрегирование не подходит еще по той причине, что мне нужно будет еще фильтровать данные.

 

[Вурдалак]

Если мне нужно будет агрегировать по 8-и колонкам, то это получается, что мне нужно будет 8*7=56 таблиц?

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

 

[Фанат]

Разницы в скорости не заметил.

А что экэксплейн говорит? Индекс подхватился?

к тому же подобных индексов надо будет много,

Значит надо будет их сделать много.

задача стоит построить систему именно на MySQL

Есть один старый анекдот, "Вам шашечки или ехать?"

 

[fixxxer]

Если мне нужно будет агрегировать по 8-и колонкам, то это получается, что мне нужно будет 8*7=56 таблиц?

Пфффф.
Вот я делал, аж вспомнить страшно, в 2003-м году, код там ужасный, но работает до сих пор и летает:

> select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%stat%'\G
*************************** 1. row ***************************
count(*): 16051

Это с учетом того, что совсем древняя статистика (старше трех лет) удаляется.

Конечно, в 2003-м году никаких ElasticSearch не было. Сейчас бы в голову не пришло так заморачиваться, взял бы Эластик и не мучался.

Агрегирование не подходит еще по той причине, что мне нужно будет еще фильтровать данные.

Ну так агрегируй сразу по фильтрам, заранее. Да, будут тысячи таблиц, и что.

 

[fixxxer]

задача стоит построить систему именно на MySQL

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

 

[Archil]

А что экэксплейн говорит? Индекс подхватился?"

Да, индекс подхватился, но на скорость никак не повлияло.

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

Да, это коробочная система.

 

[fixxxer]

Да, это коробочная система.

Сочувствую.

Ну, делай пачку таблиц, тут без вариантов вообще.

 

[Archil]

Сочувствую.

Ну, делай пачку таблиц, тут без вариантов вообще.

Партиционированием тут не получится обойтись?

 

[fixxxer]

Партиционированием тут не получится обойтись?

Смотря какие запросы будут.
Если нужна группировка по чему попало и потребуется сотня разных индексов, то не поможет.
Если нужны будут такие запросы, которые приводят к скану половины партиций - тоже не поможет.
Но можно совместить - раскидывать в разные таблицы агрегированные данные для фильтров, а уже их партиционировать по дате.
Тогда можно будет обойтись десятками таблиц, а не тысячами.

 

[fixxxer]

- Естественно, оптимальные настройки InnoDB.

Это тот редкий случай, когда MyISAM, скорее всего, будет эффективнее. Тут сплошные вставки и редкие выборки (которые по хорошему вообще бы делать со слейва).

 

[WMix]

Если заранее знать какая статистика ожидается, те к примеру суммы девайсов, оригинальных посетителей на линк на день, можно заранее пересчитать и создать удобные для вычисления таблички. Те для увеличения скорости тупого добавления записи вероятнее нужен myisam или вообще простой текстовый "лог" файл, возможно там и индексы не нужны, в определенное время раз в час/день подставлять пустую табличку а старую пересчитать и разложить по innodb в удобные структуры

 

[fixxxer]

Естественно, при правильных, оптимальных настройках.

Если бы он имел возможность влиять на настройки сервера, он бы поставил elastic.

Да, это коробочная система.

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

 

[fixxxer]

или вообще простой текстовый "лог" файл

Ага.
Я в упомянутой выше древней фигне сваливал sql-запросы в текстовый файлик, а по крону пачку ротировал и писал. Если таблички полочены по какой-то причине - да и черт с ним, запишем через 5 минут при следующем запуске крона. Но вообще с myisam-ом, когда есть всего две операции - инсерт в конец и выборки - лочиться и негде. А вот с innodb надо играться с isolation level, дефолтный repeteable-read может полочить хвост. Но для статистики и read uncommitted вполне приемлем.

 

[Archil]

Это тот редкий случай, когда MyISAM, скорее всего, будет эффективнее. Тут сплошные вставки и редкие выборки (которые по хорошему вообще бы делать со слейва).

Перевел таблицу в MyISAM, выборки на 3-х миллионах записей с группировкой по двум полям делаются на 1,5 секунды, без индексов.

 

[Archil]

На InnoDB 45 секунд, а на MyISAM 1.5?
Дурите. Нагло.
Выложите дамп в общий доступ или секретно, я не поленюсь, протестирую и покажу реальную скорость обоих движков.

Я сам удивился. В понедельник проверю все ещё раз и выложу дамп.

 

[Yoskaldyr]

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