Оптимизируйте полное внешнее соединение MySQL для большого количества данных

Question

У нас есть следующие таблицы mysql (упрощено для перехода прямо к делу)

CREATE TABLE `MONTH_RAW_EVENTS` (
  `idEvent` int(11) unsigned NOT NULL,
  `city` varchar(45) NOT NULL,
  `country` varchar(45) NOT NULL,
  `ts` datetime NOT NULL,
  `idClient` varchar(45) NOT NULL,
  `event_category` varchar(45) NOT NULL,
  ... bunch of other fields
  PRIMARY KEY (`idEvent`),
  KEY `idx_city` (`city`),
  KEY `idx_country` (`country`),
  KEY `idClient` (`idClient`),
) ENGINE=InnoDB;

CREATE TABLE `compilation_table` (
  `idClient` int(11) unsigned DEFAULT NULL,
  `city` varchar(200) DEFAULT NULL,
  `month` int(2) DEFAULT NULL,
  `year` int(4) DEFAULT NULL,
  `events_profile` int(10) unsigned NOT NULL DEFAULT '0',
  `events_others` int(10) unsigned NOT NULL DEFAULT '0',
  `events_total` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `idx_month` (`month`),
  KEY `idx_year` (`year`),
  KEY `idx_idClient` (`idClient`),
  KEY `idx_city` (`city`)
) ENGINE=InnoDB;

MONTH_RAW_EVENTS содержит почти 20 миллионов строк, в которых указаны действия пользователя на веб-сайте, размер почти 4 ГБ

compilation_table имеет сводку клиентов / городов за каждый месяц, мы используем ее для отображения статистики на веб-сайте в режиме реального времени

Мы обрабатываем статистику (от первой таблицы до второй) один раз в месяц, и мы пытаемся оптимизировать запрос, который выполняет такую ​​операцию (поскольку до сих пор мы обрабатываем все в PHP, которое занимает много времени)

Вот запрос, который мы придумали, который, кажется, выполняет свою работу при использовании небольших подмножеств данных, проблема, которая занимает более 6 часов, чтобы обработать полный набор данных

INSERT INTO compilation_table (idClient,city,month,year,events_profile,events_others)


    SELECT  IFNULL(OTHERS.idClient,AP.idClient) as idClient,
            IF(IFNULL(OTHERS.city,AP.city)='','Others',IFNULL(OTHERS.city,AP.city)) as city,
        01,2014,
    IFNULL(AP.cnt,0) as events_profile,
        IFNULL(OTHERS.cnt,0) as events_others           

    FROM
    (
        SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt 
        FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59'
        AND `event_category`!='CLIENT PROFILE'
        GROUP BY idClient,city
    ) as OTHERS
 LEFT JOIN 
    (
        SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt 
        FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59'
        AND `event_category`='CLIENT PROFILE'
        GROUP BY idClient,city
    ) as CLIPROFILE 
    ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient

 UNION

    SELECT  IFNULL(OTHERS.idClient,CLIPROFILE.idClient) as idClient,
            IF(IFNULL(OTHERS.city,CLIPROFILE.city)='','Others',IFNULL(OTHERS.city,CLIPROFILE.city)) as city,
            01,2014,
            IFNULL(CLIPROFILE.cnt,0) as events_profile,
            IFNULL(OTHERS.cnt,0) as events_others           
    FROM
    (
        SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt 
        FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59'
        AND `event_category`!='CLIENT PROFILE'
        GROUP BY idClient,city
    ) as OTHERS
 RIGHT JOIN 
    (
        SELECT idClient,CONCAT(city,', ',country) as city,count(*) as cnt 
        FROM `MONTH_RAW_EVENTS` WHERE `ts`>'2014-01-01 00:00:00' AND `ts`<='2014-01-31 23:59:59'
        AND `event_category`='CLIENT PROFILE'
        GROUP BY idClient,city
    ) as CLIPROFILE 
    ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient

То, что мы пытаемся сделать, это FULL Outer Join в Mysql, поэтому основная схема запроса выглядит так: предложенная здесь

Как мы можем оптимизировать запрос? мы пробовали разные индексы, обменивались вещами, но через 8 часов все еще не закончили работу,

Сервер MySQL - это выделенная машина Percona MySQL 5.5 с 2cpu, 2 Гб оперативной памяти и диском SSD, мы оптимизировали конфигурацию такого сервера с помощью инструментов Percona,

Любая помощь могла бы быть полезна,

Спасибо

Answer 1

Вы делаете UNION, который приводит к обработке DISTINCT.

Обычно лучше переписать полное объединение в левое объединение плюс несоответствующие строки правого объединения (если это правильно 1:n соединение)

OTHERS LEFT JOIN CLIPROFILE 
ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient
union all
OTHERS RIGHT JOIN CLIPROFILE 
ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient
WHERE OTHERS.idClient IS NULL 

Кроме того, вы можете материализовать результаты производных таблиц во временных таблицах до их присоединения, таким образом, вычисление выполняется только один раз (я не знаю, достаточно ли у умного оптимизатора MySQL для этого автоматически).

Кроме того, может быть более эффективно группировать и объединять город / страну в виде отдельных столбцов и делать КОНКАТ (город,',', страна) в качестве города на внешнем шаге.