Загружать строки по значению DATETIME и таймфрейму
В моей базе данных mysql я каждую минуту вставляю в таблицу 100 строк. Столбец с именем «время» имеет тип DATETIME и содержит дату и час вставки (исключая секунды). Я ищу эффективный способ извлечения строк из этой таблицы - в указанный период времени. Например, если мой таймфрейм составляет 15 минут, будут извлечены следующие строки:
3-11-18 13:00:00
3-11-18 13:15:00
3-11-18 13:30:00
3-11-18 13:45:00
3-11-18 14:00:00
так далее... Для таймфрейма один час будет получено
3-11-18 13:00:00
3-11-18 14:00:00
В настоящее время я использую для этого оператор LIKE. Например, запрос таймфрейма 15 минут выглядит так:
SELECT * FROM my_table WHERE time LIKE '%:15:00' OR time LIKE '%:30:00' OR time LIKE '%:45:00' OR time LIKE '%:00:00';
Но эти запросы выполняются очень-очень медленно. Что я могу сделать для повышения производительности?
Пользуюсь версией 5.7
1) индексное время 2) вместо этого используйте HOUR(time) = [minute].
Ответы 2
Вы можете попробовать добавить сгенерированный столбец на несколько минут и установить для него индекс:
alter table my_table add column minute tinyint unsigned as (minute(time));
alter table my_table add index (minute);
Запрос вроде
select * from my_table where minute(time) = 0;
будет использовать этот индекс.
Но я не уверен, поможет ли это с таким запросом, как
select * from my_table where minute(time) in (0, 15, 30, 45);
Причина в том, что селективность условия WHERE здесь не очень хорошая. Полное сканирование таблицы с пропуском 14 из 15 строк может быть быстрее, чем поиск по индексу + второй обход кластеризованного индекса. В таком случае вы ничего не можете сделать. За исключением создания индекса покрытия. Но индекс, который будет охватывать SELECT *, вероятно, будет вреден для 100 вставок в минуту.
Используйте MOD для фильтрации по требованию таймфрейма.
Для таймфрейма 15 минут:
SELECT *
from my_table
where mod(minute(time),15) = 0
Для таймфрейма 30 минут:
mod(minute(time),30) = 0
Для таймфрейма один час (60 минут):
mod(minute(time),60) = 0
Или же
minute(time) = 0
Обобщенная статья WHERE на 15, 30 или 60 минут:
mod( minute(time), <timeframeInMinutes>) = 0
Какую версию вы используете?