Оптимизация медленности COUNT(DISTINCT) даже с покрывающими индексами

Если запрос без count(distinct) выполняется намного быстрее, возможно, вы можете сделать вложенную агрегацию:

SELECT log_type, log_date,
       count(*) AS COUNT, sum(cnt) AS total
FROM (SELECT log_type,
             DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS log_date,
             subscriber_id, count(*) as cnt
      FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index)
      WHERE DOMAIN = 'xxx' AND
            campaign_id = '123' AND
            log_type IN ('EMAIL_SENT', 'EMAIL_OPENED', 'EMAIL_CLICKED') AND
            log_time BETWEEN CONVERT_TZ('2015-02-01 00:00:00','+00:00','+05:30') AND 
                             CONVERT_TZ('2015-03-01 23:59:58','+00:00','+05:30')
      GROUP BY log_type, log_date, subscriber_id
     ) l
GROUP BY logtype, log_date;

Если повезет, это займет 2-3 секунды, а не 50. Однако вам может потребоваться разбить это на подзапросы, чтобы получить полную производительность. Итак, если это не дает значительного прироста производительности, измените in обратно на = одного из типов. Если это сработает, то может понадобиться union all.

РЕДАКТИРОВАТЬ:

Другая попытка состоит в том, чтобы использовать переменные для перечисления значений перед group by:

SELECT log_type, log_date, count(*) as cnt,
       SUM(rn = 1) as sub_cnt
FROM (SELECT log_type,
             DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS log_date,
             subscriber_id,
             (@rn := if(@clt = concat_ws(':', campaign_id, log_type, log_time), @rn + 1,
                        if(@clt := concat_ws(':', campaign_id, log_type, log_time), 1, 1)
                       )
              ) as rn
      FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index) CROSS JOIN
           (SELECT @rn := 0)
      WHERE DOMAIN = 'xxx' AND
            campaign_id = '123' AND
            log_type IN ('EMAIL_SENT', 'EMAIL_OPENED', 'EMAIL_CLICKED') AND
            log_time BETWEEN CONVERT_TZ('2015-02-01 00:00:00', '+00:00', '+05:30') AND 
                             CONVERT_TZ('2015-03-01 23:59:58', '+00:00', '+05:30')
      ORDER BY log_type, log_date, subscriber_id
     ) t
GROUP BY log_type, log_date;

Для этого по-прежнему требуются данные другого типа, но это может помочь.

Чтобы ответить на ваш вопрос:

Есть ли способ реорганизовать мой запрос или добавить индекс или что-то еще, чтобы получить значения COUNT(DISTINCT...), но намного быстрее?

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

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

1) Упростите запрос и сосредоточьтесь на одной части. Оставьте только один самый долго работающий SELECT из трех, избавьтесь от UNION на период настройки. Как только самый длинный SELECT будет оптимизирован, добавьте еще и проверьте, как работает полный запрос.

2) Группировка по результату функции не позволяет движку эффективно использовать индекс. Добавьте в таблицу еще один столбец (сначала временно, просто для проверки идеи) с результатом работы этой функции. Насколько я вижу, вы хотите сгруппировать по 1 часу, поэтому добавьте столбец log_time_hour datetime и установите для него значение log_time с округлением/усечением до ближайшего часа (сохраните компонент даты).

Добавьте индекс, используя новый столбец: (domain, campaign_id, log_type, log_time_hour, subscriber_id). Порядок первых трех столбцов в индексе не должен иметь значения (поскольку вы используете равенство для сравнения с некоторой константой в запросе, а не с диапазоном), но сделайте их в том же порядке, что и в запросе. Или, лучше, сделать их в определении индекса и в запросе в порядке избирательности. Если у вас 100,000 кампаний, 1000 доменов и 3 типов журналов, то расположите их в таком порядке: campaign_id, domain, log_type. Это не должно иметь большого значения, но стоит проверить. log_time_hour должен стоять четвертым в определении индекса, а subscriber_id последним.

В запросе используйте новый столбец в WHERE и в GROUP BY. Убедитесь, что вы включили все необходимые столбцы в GROUP BY: и log_type, и log_time_hour.

Вам нужны и COUNT, и COUNT(DISTINCT)? Сначала оставьте только COUNT и измерьте производительность. Оставьте только COUNT(DISTINCT) и измерьте производительность. Оставьте оба и измерьте производительность. Посмотрите, как они сравниваются.

SELECT log_type,
       log_time_hour,
       count(DISTINCT subscriber_id) AS distinct_total,
       COUNT(subscriber_id) AS total
FROM stats.campaign_logs
WHERE DOMAIN='xxx'
  AND campaign_id='123'
  AND log_type = 'EMAIL_OPENED'
  AND log_time_hour >= '2015-02-01 00:00:00' 
  AND log_time_hour <  '2015-03-02 00:00:00'
GROUP BY log_type, log_time_hour

SELECT log_type,
       DATE_FORMAT(CONVERT_TZ(log_time,'+00:00','+05:30'),'%l %p') AS log_date,
       count(DISTINCT subscriber_id) AS COUNT,
       COUNT(subscriber_id) AS total
FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index)
WHERE DOMAIN='xxx'
  AND campaign_id='123'
  AND log_time BETWEEN CONVERT_TZ('2015-02-01 00:00:00','+00:00','+05:30') AND CONVERT_TZ('2015-03-01 23:59:58','+00:00','+05:30')
GROUP BY log_type, log_date

При необходимости добавьте AND log_type IN ('EMAIL_OPENED', 'EMAIL_SENT', 'EMAIL_CLICKED').

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

Сначала я подумал, что может использоваться только часть индекса (вообще не попадая в subscriber_id). Если он не может использовать subscriber_id, то перемещение его вверх по дереву индексов заставит его работать медленнее, что, по крайней мере, скажет вам, что он не может его использовать.

Я не могу придумать, с чем еще можно поиграть.

1. subscriber_id бесполезен в вашем ключе, потому что вы группируете по вычисляемому полю вне ключа (log_date) перед подсчетом отдельных подписчиков. Это объясняет, почему это так медленно, потому что MySQL должен сортировать и фильтровать повторяющихся подписчиков без использования ключа.

2. Может быть ошибка с вашим условием log_time: у вас должно быть преобразование часового пояса, противоположное вашему выбору (т.е. '+05:30','+00:00'), но оно не будет иметь серьезного влияния на время вашего запроса.

3. Вы можете избежать «объединения всех», выполнив log_type IN (...) и сгруппировав log_type, log_date

Лучшим эффективным решением было бы добавить поле середины часа в вашу схему базы данных и установить там одно из 48 часов дня (и позаботиться о часовом поясе середины часа). Таким образом, вы можете использовать индекс для campaign_id,domain,log_type,log_mid_hour,subscriber_id

Это будет довольно избыточно, но улучшит скорость.

Таким образом, это должно привести к некоторым инициализациям в вашей таблице: будьте осторожны: не проверяйте это на своей производственной таблице

ALTER TABLE campaign_logs
   ADD COLUMN log_mid_hour TINYINT AFTER log_time;

UPDATE campaign_logs SET log_mid_hour=2*HOUR(log_time)+IF(MINUTE(log_time)>29,1,0);

ALTER TABLE campaign_logs
ADD INDEX(`campaign_id`,`domain`,`log_time`,`log_type`,`log_mid_hour`,`subscriber_id`);

Вам также нужно будет установить log_mid_hour в своем скрипте для будущих записей.

Ваш запрос примет вид (для 11-часового сдвига во времени):

SELECT log_type,
   MOD(log_mid_hour+11, 48) tz_log_mid_hour,
   COUNT(DISTINCT subscriber_id) AS COUNT,
   COUNT(subscriber_id) AS total
FROM stats.campaign_logs
WHERE DOMAIN='xxx'
   AND campaign_id='123'
   AND log_type IN('EMAIL_SENT', 'EMAIL_OPENED','EMAIL_CLICKED')
   AND log_time BETWEEN CONVERT_TZ('2015-02-01 00:00:00','+05:30','+00:00')   
   AND CONVERT_TZ('2015-03-01 23:59:58','+05:30','+00:00')
GROUP BY log_type, log_mid_hour;

Это даст вам подсчет для каждого часа в середине, используя все преимущества вашего индекса.

У меня была очень похожая проблема, размещенная здесь, на SO, и я получил большую помощь. Вот ветка: запрос медленного подсчета MySQL MyISAM(), несмотря на покрытие индекса