Блог

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

Запрос является:

 SELECT DATE(DateTime) as 'SpeedDate', avg(LoadTime) as 'LoadTime'
FROM SpeedMonitor
GROUP BY Date(DateTime);
  

Объяснение запроса таково:

 id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  SpeedMonitor    ALL                 7259978 Using temporary; Using filesort
  

И структура таблицы:

 CREATE TABLE `SpeedMonitor` (
  `SMID` int(10) unsigned NOT NULL auto_increment,
  `DateTime` datetime NOT NULL,
  `LoadTime` double unsigned NOT NULL,
  PRIMARY KEY  (`SMID`)
) ENGINE=InnoDB AUTO_INCREMENT=7258294 DEFAULT CHARSET=latin1;
  

Любая помощь была бы высоко оценена.

Вы просто запрашиваете два столбца в своем запросе, поэтому индексы могут / должны идти туда:

• Дата и время
• Время загрузки

Другим способом ускорить ваш запрос может быть разделение поля DateTime на два: дата и время.
Таким образом, база данных может группироваться непосредственно по полю даты вместо вычисления даты (…).

ОТРЕДАКТИРОВАНО:
Если вы предпочитаете использовать триггер, создайте новый столбец (DATE) и назовите его newdate, и попробуйте с этим (я не могу попробовать это сейчас, чтобы проверить, правильно ли это):

 CREATE TRIGGER upd_check BEFORE INSERT ON SpeedMonitor
FOR EACH ROW
BEGIN
  SET NEW.newdate=DATE(NEW.DateTime);
END
  

ОТРЕДАКТИРОВАНО СНОВА:
Я только что создал базу данных с той же таблицей speedmonitor, заполненной примерно 900 000 записями.
Затем я запустил запросы ELECT newdate,AVG(LoadTime) loadtime FROM speedmonitor GROUP BY newdate , и это заняло около 100 секунд!!
Удаление индекса в поле newdate (и очистка кэша с помощью RESET QUERY CACHE и FLUSH TABLES ), тот же запрос занял 0,6 секунды!!!
Просто для сравнения: запрос SELECT DATE(DateTime),AVG(LoadTime) loadtime FROM speedmonitor GROUP BY DATE(DateTime) занял 0,9 секунды.
Итак, я полагаю, что индекс в newdate не подходит: удалите его.
Сейчас я собираюсь добавить столько записей, сколько смогу, и снова протестировать два запроса.

ОКОНЧАТЕЛЬНОЕ РЕДАКТИРОВАНИЕ:
Удаление индексов в столбцах newdate и DateTime, имеющих 8 миллионов записей в таблице speedmonitor, вот результаты:

• выбор и группировка в столбце newdate: 7,5с
• выбор и группировка по полю ДАТЫ (DateTime): 13,7 с

Я думаю, что это хорошее ускорение.
Время, затрачиваемое на выполнение запроса в командной строке mysql.

Проблема в том, что вы используете функцию в своем GROUP BY предложении, поэтому MySQL должен вычислять выражение Date(DateTime) для каждой записи, прежде чем он сможет сгруппировать результаты. Я бы предложил добавить вычисляемое поле для Date(DateTime) , которое вы могли бы затем проиндексировать и посмотреть, поможет ли это вашей производительности.

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

Что происходит прямо сейчас, так это то, что ваш запрос не может использовать какие-либо индексы и, следовательно, сканирует всю таблицу, создавая ответ. Не самый быстрый способ работы с относительно большими таблицами.

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

1. Насколько быстро он собирает данные?
2. Сколько истории вам нужно?
3. Насколько детализированы ваши требования к отчетности?
4. Можете ли вы приостановить ведение журнала для внесения изменений в таблицу?

Если ответ «Нет» на последний вопрос, вы всегда можете создать новую таблицу / решение и начать записывать туда записи … импортируя старые данные, если / по мере необходимости.

Важна детализация отчетов, поскольку вы могли бы, например, сжать данные за день в 24 записи. Загрузите текущий день в таблицу загрузки без индекса, а затем обработайте его на следующий день в усредненных значениях за час. Назовите каждую загружаемую таблицу на основе даты выборки, и вы сможете удалять старые таблицы по мере обработки.

Конечно, hourly может быть недостаточно детализированным.

В зависимости от ваших потребностей в хранении вы можете рассмотреть какой-либо тип секционированного хранилища. Это может позволить вам запрашивать подмножества выборочных данных и просто удалять или архивировать старые разделы, когда они уже недостаточно актуальны, чтобы быть актуальными.

В любом случае, вы, похоже, находитесь на грани создания какого-то типа системы массовой выборки, отчетности и / или мониторинга (особенно, если вы сообщали о множестве сайтов или страниц с разными характеристиками). Возможно, вы захотите приложить некоторые усилия для разработки этого, чтобы оно соответствовало вашим потребностям … 😉