Как изменить сам запрос или DDL, чтобы ускорить выборку по диапазону дат?
MySQL 8.0.33. Имеется таблица (убрал незначащие поля в контексте вопроса):
CREATE TABLE `data` (
`id` int NOT NULL AUTO_INCREMENT,
`period` date DEFAULT NULL,
`h_sum` double DEFAULT '0',
`hw_sum` double DEFAULT '0',
PRIMARY KEY (`id`),
KEY `period` (`period`),
) ENGINE=InnoDB;
куда каждый месяц (поле period хранит дату = 1 число этого месяца) записываются несколько десятков тысяч записей. Сейчас в ней данные за 62 месяца, 2016426 записей. Один из запросов должен подсчитывать сумму h_sum+hw_sum за несколько месяцев (помесячно). Если так:
SELECT period, SUM(h_sum+hw_sum) paid
FROM data
WHERE period BETWEEN '2023-01-01' AND '2023-07-01'
GROUP BY period
ORDER BY period;
запрос выполняется 19 сек! Его EXPLAIN:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | data | \N | index | period | period | 4 | \N | 1934197 | 33,18 | Using where |
Если переписать его с использованием оконных функций:
SELECT DISTINCT period, SUM(h_sum+hw_sum) OVER (PARTITION BY period ORDER BY period) paid
FROM data
WHERE period BETWEEN '2023-01-01' AND '2023-07-01'
то время выполнения уменьшается до 8 сек, но перестает использоваться индекс, EXPLAIN:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | data | \N | ALL | period | \N | \N | \N | 1934197 | 33,18 | Using where; Using filesort |
Если принудить использовать индекс:
SELECT DISTINCT period, SUM(h_sum+hw_sum) OVER (PARTITION BY period ORDER BY period) paid
FROM data FORCE INDEX (period)
WHERE period BETWEEN '2023-01-01' AND '2023-07-01'
то время уменьшается до 4 сек, но EXPLAIN (поле extra) становится совсем плохим:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | data | \N | range | period | period | 4 | \N | 641720 | 100,00 | Using index condition; Using MRR; Using filesort |
Вопрос: можно ли еще как-то оптимизировать/изменить запрос для ускорения времени выполнения (чем больше месяцев в выборке, тем дольше считается)? Может изменить тип поля? На что? Индекс? Структуру таблицы? Еще что-то?
Доп. вопрос: так ли нужен ORDER BY period, если данные записываются всегда последовательно по месяцам (т.е., к примеру, данные января 2023 не могут быть записаны ранее декабря 2022)? Если убрать его из запросов, то ничего не меняется: ни порядок месяцев в выборке, ни скорость выполнения...