Как я могу оптимизировать группировку?
Вот мой запрос:
EXPLAIN SELECT Count(1),
user_id,
type
FROM (SELECT e.user_id,
e.type,
Max(r.date_time) last_seen,
e.date_time event_time
FROM events e
JOIN requests r
ON e.user_id = r.user_id
AND e.type IN( 3, 5, 6 )
GROUP BY e.user_id,
e.date_time,
e.type
HAVING last_seen < event_time) x
GROUP BY user_id,
type
Также вот результат EXPLAIN:
Также вот результат этого подзапроса (x) EXPLAIN:
Видеть? Оптимально. Итак, проблема здесь группируется. Есть идеи, как мне улучшить этот запрос?
Обновлено: Нам понадобятся две таблицы:
Таблица
requests- в нее будет вставляться новая строка для каждого запроса пользователя. Таким образом, последний (самый большой) в некоторой степени определяет, когда пользователь был на нашем сайте в последний раз.Таблица
events- внутри нее будет вставляться новая строка для каждого ответа, комментария.
Мы говорим о веб-сайте вопросов и ответов. Все, что мы пытаемся сделать, это «отправить электронное письмо пользователям, которые получили новый комментарий / ответ после их последнего посещения нашего веб-сайта».
Я советую вам прочитать это и предоставить структуры таблиц SHOW CREATE TABLE table (для каждой таблицы, участвующей в вопросе), также предоставить пример данных и ожидаемый результат ... Могут быть лучшие методы для получения тех же результатов быстрее или более оптимизированы ... Также укажите свою версию MySQL вы можете получить это с SELECT VERSION();
Ответы 4
вам нужен правильный индекс в вашей таблице, чтобы соответствовать как предложению WHERE, так и Order by для оптимизации.
table index on...
events ( type, user_id, date_time )
requests ( user_id, date_time )
Могу даже предложить небольшую корректировку запроса. Измените свой
AND e.type IN( 3, 5, 6 )
к
WHERE e.type IN( 3, 5, 6 )
Поскольку «e.Type» основан на вашей основной таблице запроса и не имеет ничего общего с фактическим JOIN для таблицы запросов. Соединение должно представлять фактические столбцы для квалификации между таблицами.
ПРЕДЛОЖЕНИЕ опубликовать редактирование вопроса. Могу предложить альтернативный вариант. Добавьте столбец в вашу пользовательскую таблицу для поля даты / времени lastRequest. Затем при каждом вводе запроса для этого пользователя обновляйте поле в таблице пользователей. Вам не нужно сохранять подзапрос max (), чтобы узнать, когда. Это может упростить ваш запрос до чего-то вроде ... Чем больше ваша таблица запросов, тем больше будет время вашего запроса. Посмотрев непосредственно на таблицу пользователей ONCE для уже известного последнего запроса, вы получите свой ответ. Запросить 10 тысяч пользователей или 2 миллиона запросов ... на ваш выбор :)
select
u.user_id,
e.type,
count(*) CountPerType,
min( e.date_time ) firstEventDateAfterUsersLastRequest
from
user u
join events e
on u.user_id = e.user_id
AND e.type in ( 3, 5, 6 )
AND e.date_time > u.lastRequest
group by
u.user_id,
e.type
Таким образом, ваше соединение уже имеет базовую дату / время для каждого пользователя, и вы можете просто искать эти записи, поступающие ПОСЛЕ того, как человек последний раз что-то запрашивал (следовательно, последующие действия).
Затем, чтобы подготовить новый столбец в пользовательской таблице, вы можете просто обновить значение max (request.date_time) для каждого пользователя.
Если человек активен, например: 27 ноября, и есть 5 ответов на 3 разных типа событий ПОСЛЕ этого, вы все равно получите этого человека на дату 27 ноября, но у других людей могут быть более новые или более старые даты «latestRequest».
Просто необязательная мысль ..
По сравнению с тем, что было раньше ... Кроме того, какова цель ИМЕТЬ, которого видели в последний раз перед событием. Зачем кому-то запрашивать событие ПОСЛЕ того, как событие произошло? Возможно, вы захотите отредактировать и уточнить в своем исходном сообщении.
@MartinAJ, для индекса это должен быть ЕДИНЫЙ ИНДЕКС для всех ТРЕХ полей, а не отдельные индексы по одному на столбец ... это БОЛЬШАЯ разница. Также таблица REQUEST должна иметь ОДИН индекс для ОБОИХ полей, а не по одному для каждого столбца.
О, вы имеете в виду, что первый индекс в вашем ответе - это составной (который в настоящее время), а второй - это два отдельных индекса?
@MartinAJ, нет ... оба - по 1 индексу в каждой таблице ... оба - составные индексы. Если вам не нужны другие индексы в таблице, которые являются отдельными полями, я мог бы рассмотреть возможность удаления, при условии, что первый порядковый столбец в индексе такой же, но я бы обеспечил, чтобы таблица событий имела тип в первой позиции показатель.
Понятно ... также я отредактировал свой вопрос и объяснил, что я пытаюсь сделать ... пожалуйста, взгляните на него.
Скорее всего, оптимизатор MySQL переписал бы SQL ON e.user_id = r.user_id AND e.type IN( 3, 5, 6 ) уже в ON e.user_id = r.user_id WHERE e.type IN( 3, 5, 6 ) под капотом ... Так что я сомневаюсь, что ваше предложение по переписыванию поможет ... индексы действительно помогут.
@MartinAJ, см. Исправленный в вашем комментарии для другого варианта, чтобы каждый раз предотвращать подзапросы.
Если честно, я не могу добавить новый столбец в таблицу пользователя. Но я написал запрос Сюда, который имеет лучшую производительность (около 1,5 секунды). Любая идея?
ой .. время выполнения это - 0.1. ура для меня.
@DRapp. . . Это не реализует ту же логику. Этот запрос фильтрует даты, чтобы учитывать только запросы до даты события. Запрос OP отфильтровывает события, в которых самым последним запросом является после - дата события.
http://sqlfiddle.com/#!9/c73878/1
ALTER TABLE `events` ADD INDEX e_type (type);
ALTER TABLE `events` ADD INDEX user_time (user_id, date_time);
ALTER TABLE requests ADD INDEX user_time (user_id, date_time);
SELECT COUNT(*),
e.user_id,
e.type
FROM `events` e
JOIN (
SELECT user_id, Max(r.date_time) last_seen
FROM requests r
GROUP BY user_id
) r
ON e.user_id = r.user_id
AND e.date_time > r.last_seen
WHERE e.type IN( 3, 5, 6 )
GROUP BY e.user_id,
e.type
Ваш запрос будет работать, но они ищут улучшения производительности, а ваши смоделированные данные минимальны по той массе, которую они имеют в производстве. Кроме того, мое предложение добавить столбец «lastReviewed» в таблицу пользователя предотвратит необходимость перегруппировки каждого пользователя каждый раз при каждом запросе. Чем больше таблица, тем постоянно увеличивается время выполнения подзапроса.
Спасибо ... проголосовать за ... но, как упоминал @DRapp, проблема заключается в производительности.
@MartinAJ, просто предоставьте образец данных, пожалуйста, и какова ваша текущая скорость?
Я бы переписал запрос так:
select user_id, type, count(*)
from (select e.user_id, e.type, e.date_time,
(select max(r.date_time)
from requests r
where r.user_id = e.user_id
) as last_seen
from events e
where e.type in ( 3, 5, 6 )
) er
where last_seen < date_time
group by user_id, type;
Затем я хочу быть уверен, что есть индексы на requests(user_id, date_time) и events(type, user_id, date_time).
Следовательно, мой альтернативный вариант - добавление поля lastRequest для каждого пользователя. Тогда вам не нужно постоянно запрашивать постоянно растущую таблицу запросов. Просто получите все события для каждого пользователя с известной датой последнего запроса, а дата события - ПОСЛЕ того, как с человеком последний раз связались, на основе квалифицированного типа события.
@DRapp. . . Я бы сначала попробовал это с соответствующими индексами. Поддержание такого поля - это большая работа для базы данных, требующая поддержки множества триггеров, а также другого кода.
Действительно? один триггер для обновления UserTable устанавливает lastRequest = date, где userid = userId выполняет вставку? Я не вижу сложностей или сложностей
@DRapp Вероятно, добавление нового поля с именем last_seen упростит достижение моей текущей цели. Но вы знаете, я работаю php-разработчиком. У меня нет доступа к базам данных, и мне напомнили, чтобы я не трогал структуру базы данных. В любом случае, как я прокомментировал под вашим сообщением, выполнение это(а также решение Гордона) занимает 0.1 секунды (на большом наборе данных). Думаю, это можно было бы назвать нормальным спектаклем.
@MartinAJ, понятый и не знающий степени доступа людей, может предлагать предложения, но понимает, когда вы ограничены.
Посмотрите, дает ли это «правильные» ответы:
SELECT COUNT(DISTINCT(e.date_time),
e.user_id, e.type
FROM events e
JOIN requests r ON e.user_id = r.user_id
AND e.type IN( 3, 5, 6 )
GROUP BY e.user_id, e.type
HAVING MAX(r.date_time) < e.event_time
Индексы:
e: INDEX(type) -- may be useful (depends on cardinality)
r: INDEX(user_id, date_time) -- in this order
Опубликуйте схему таблиц и объясните, пожалуйста, как простой текст, а не изображения