Оптимизация медлительности COUNT(DISTINCT) даже с индексами покрытия
У нас есть таблица в MySql с около 30 миллионами записей, следующая структура таблицы
CREATE TABLE `campaign_logs` (
`domain` varchar(50) DEFAULT NULL,
`campaign_id` varchar(50) DEFAULT NULL,
`subscriber_id` varchar(50) DEFAULT NULL,
`message` varchar(21000) DEFAULT NULL,
`log_time` datetime DEFAULT NULL,
`log_type` varchar(50) DEFAULT NULL,
`level` varchar(50) DEFAULT NULL,
`campaign_name` varchar(500) DEFAULT NULL,
KEY `subscriber_id_index` (`subscriber_id`),
KEY `log_type_index` (`log_type`),
KEY `log_time_index` (`log_time`),
KEY `campid_domain_logtype_logtime_subid_index` (`campaign_id`,`domain`,`log_type`,`log_time`,`subscriber_id`),
KEY `domain_logtype_logtime_index` (`domain`,`log_type`,`log_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Ниже мой запрос
Я делаю UNION ALL вместо использования операции IN
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_type = 'EMAIL_OPENED'
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_date
UNION ALL
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_type = 'EMAIL_SENT'
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_date
UNION ALL
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_type = '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_date,
Ниже приводится мое объяснение
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+------------------------------------------+
| 1 | PRIMARY | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468 | NULL | 55074 | Using where; Using index; Using filesort |
| 2 | UNION | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468 | NULL | 330578 | Using where; Using index; Using filesort |
| 3 | UNION | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468 | NULL | 1589 | Using where; Using index; Using filesort |
| NULL | UNION RESULT | <union1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+------------------------------------------+
- Я изменил COUNT(subscriber_id) на COUNT(*) и не наблюдал увеличения производительности.
2. Я удалил COUNT(DISTINCT subscriber_id) из запроса, затем я получил огромный прирост производительности, я получаю результаты примерно через 1,5 секунды, ранее это занимало 50 секунд - 1 минуту. Но мне нужно отличное количество subscriber_id от запроса
Ниже объясняется, когда я удаляю COUNT(DISTINCT subscriber_id) из запроса.
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+-----------------------------------------------------------+
| 1 | PRIMARY | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468 | NULL | 55074 | Using where; Using index; Using temporary; Using filesort |
| 2 | UNION | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468 | NULL | 330578 | Using where; Using index; Using temporary; Using filesort |
| 3 | UNION | campaign_logs | range | campid_domain_logtype_logtime_subid_index | campid_domain_logtype_logtime_subid_index | 468 | NULL | 1589 | Using where; Using index; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+---------------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+-----------------------------------------------------------+
- Я выполнил три запроса по отдельности, удалив UNION ALL. Один запрос занял 32 секунды, другие занимают по 1,5 секунды каждый, но первый запрос имеет дело примерно с 350 тыс. Записей, а другие - только 2 тыс. Строк.
Я мог решить свою проблему с производительностью, не учав COUNT(DISTINCT...) но мне нужны эти ценности. Есть ли способ реорганизовать мой запрос, или добавить индекс, или что-то, чтобы получить COUNT(DISTINCT...) значения, но намного быстрее?
ОБНОВЛЕНИЕ Следующая информация о распределении данных в таблице выше
за 1 домен 1 кампания 20 log_types 1k-200k подписчиков
Приведенный выше запрос, за которым я работаю, имеет домен с 180 тыс. Подписчиков.
6 ответов
Если запрос без 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 logtype, 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 logtype, 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
subscriber_idне нужен в вашем ключе, потому что вы группируете по вычисляемому полю вне ключа (log_date) перед подсчетом отдельных подписчиков. Это объясняет, почему это так медленно, потому что MySQL должен сортировать и фильтровать дублирующих подписчиков без использования ключа.Может быть ошибка с вашим условием log_time: у вас должно быть обратное преобразование часового пояса вашего выбора (т.е.
'+05:30','+00:00'), но это не повлияет на время запроса.Вы можете избежать "объединения всех", выполнив
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;
Это даст вам счет за каждый полчаса, полностью использующий ваш индекс.
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, то перемещение его вверх по дереву индекса приведет к замедлению его работы, что, по крайней мере, скажет вам, что он не может его использовать.
Я не могу думать о многом другом, с чем ты можешь играть.
У меня была очень похожая проблема, размещенная здесь на SO, и мне очень помогли. Вот поток: MySQL MyISAM медленный запрос count() несмотря на закрывающий индекс
Короче говоря, я обнаружил, что моя проблема не имеет ничего общего с запросом или индексами, и ВСЕ, что связано с тем, как я настроил таблицы и MySQL. Мой точно такой же запрос стал намного быстрее, когда я:
- Переключился на InnoDB (который вы уже используете)
- Переключил CHARSET на ASCII. Если вам не нужен utf8, это займет в 3 раза больше места (и времени на поиск).
- Сделайте размер каждого столбца как можно меньшим, если возможно, не нулевым.
- Увеличен размер пула буферов InnoDB в MySQL. Многие рекомендации должны увеличить его до 70% вашей оперативной памяти, если это выделенный компьютер.
- Я отсортировал свою таблицу по индексу покрытия, выписал ее через SELECT INTO OUTFILE, а затем снова вставил в новую таблицу. Это физически сортирует все записи в порядке поиска.
Я понятия не имею, какие из этих изменений решили мою проблему (потому что я был ненаучным и не пробовал их по одному), но это сделало мои запросы в 50-100 раз быстрее. YMMV.