Mysql выберите производительность запроса становится плохо
Я получил запрос MySQL, который выбирает все клики для каждого часа дня.
Этот запрос работал хорошо, пока в нашей базе данных не было много записей о кликах. Теперь иногда требуется несколько секунд (до 9!), Чтобы запросить данные …
Запрос:
SELECT h.clickHour, COUNT(clicktime) AS c
FROM ( SELECT 0 AS clickHour
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23 ) AS h
INNER JOIN links l ON l.user_id = 1
LEFT OUTER
JOIN clicks
ON EXTRACT(HOUR FROM clicks.clicktime) = h.clickHour
AND DATE(clicks.clicktime) = '2014-09-21'
AND clicks.link_id = l.id
GROUP
BY h.clickHour
Я получил эти союзы, потому что мне нужны клики для каждого часа, а также пустые часы …
Пожалуйста помоги!
Итак, мы говорим о 0 до нескольких тысяч строк для кликов таблицы. Время клика сохраняется как метка времени, и каждый клик получает уникальный идентификатор. Я вижу, что союз — это плохо, и я должен его изменить.
Сейчас я пытаюсь выбрать все клики за день, сгруппированные по ЧАСУ (время клика):
Но когда я делаю это, я получаю слишком много результатов, таких как 10x, то так и должно быть.
Решение
Я бы переписал запрос так:
SELECT h.clickHour
, IFNULL(d.clickCount,0) AS c
FROM ( SELECT 0 AS clickHour UNION ALL SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) h
LEFT
JOIN ( SELECT EXTRACT(HOUR FROM c.clicktime) AS clickHour
, SUM(1) AS clickCount
FROM clicks c
JOIN links l
ON l.user_id = 1
AND l.id = c.link_id
WHERE c.clicktime >= '2014-09-21'
AND c.clicktime < '2014-09-21' + INTERVAL 1 DAY
GROUP BY EXTRACT(HOUR FROM c.clicktime)
) d
ON d.clickHour = h.clickHour
Подход здесь состоит в том, чтобы получить запрос встроенного представления d вернуть максимум 24 строки. Это провернуло через clicks стол, чтобы получить счет. Мы собираемся отложить операцию соединения до фиксированного набора из 24 строк до тех пор, пока мы не вычислим почасовые подсчеты. (Присоединение к h это только для того, чтобы получить строки с нулевым счетом, которые в противном случае были бы просто «пропущенными» строками.)
Вы можете проверить производительность запроса встроенного представления dи из всего запроса, я подозреваю, не будет большой разницы. Стоимость материализации встроенного представления h не так уж много (есть некоторые издержки, но вполне вероятно, что будет использоваться механизм хранения памяти; он достаточно мал и должен быть простым целочисленным типом данных.) И такая операция объединения 24 строк в 24 строки не будет такой дорогой даже без каких-либо доступных индексов.
Я подозреваю, что большая часть времени будет в материализации производной таблицы d,
Мы хотим, чтобы индекс с ведущим столбцом clickDate, чтобы мы могли использовать более эффективную операцию сканирования диапазона индекса, чтобы избежать оценки выражений для каждой строки в таблице.
Я изменил этот предикат: DATE(clickTime) = '2014-09-21' в предикаты, которые ссылаются на пустой столбец, это позволяет MySQL рассмотреть эффективную операцию сканирования диапазона для столбца clickTime (чтобы быстро исключить загрузку строк из рассмотрения), вместо того, чтобы требовать, чтобы MySQL вычислял функцию для каждой строки с переворачиванием в Таблица.
Некоторый прирост производительности можно получить, сделав покрывающие индексы доступными на clicks а также links таблицы (чтобы запрос мог быть выполнен по индексам, без необходимости посещать страницы в базовой таблице.)
Как минимум в таблице кликов:
ON clicks (clickTime, link_id)
Если id является уникальным (или первичным ключом) на links В таблице этот индекс может не дать какого-либо выигрыша в производительности:
ON links (id, user_id)
Если использовался индекс покрытия, вывод EXPLAIN должен показывать «Использование индекса».
Я не вижу способа обойти операцию «Использование сортировки файлов», не добавив столбец в clicks таблица, которая хранит усеченное до часа clickTime. Имея такой столбец и соответствующий индекс, мы можем получить GROUP BY операция оптимизирована с использованием индекса, избегая операции «Использование файловой сортировки».
Другие решения
Вы проиндексировали?
Таблица кликов: время клика, link_id
Таблица ссылок: id, user_id
detector