"Мягкая" система типов в MySQL, с неявным приведением по контексту - расслабляет. Сегодня очередной раз убедился, что за типами данных надо следить. Суть того, что получилось сегодня, продемонстрирую на модели.
Создадим модельную таблицу:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, dt DATE, val INT);
SET @@cte_max_recursion_depth := 1000000;
INSERT INTO test
WITH RECURSIVE
cte AS ( SELECT 1 n
UNION ALL
SELECT n + 1 FROM cte WHERE n < 1000000 )
SELECT NULL, '2021-01-01' + INTERVAL RAND()*365 DAY, RAND()*1000
FROM cte;
Миллион записей, даты в пределах текущего года, в каждой записи - некое значение.
Задача - для каждой даты подсчитать сумму этих неких значений. Предполагая, что не все даты имеются в таблице (генерация данных делает такую ситуацию почти нереальной, но предположим...), использовать генерируемую таблицу-календарь.
То есть вроде бы запрос очевиден:
1.
2.
3.
4.
5.
6.
7.
8.
WITH RECURSIVE
cte AS ( SELECT '2021-01-01' dt
UNION ALL
SELECT dt + INTERVAL 1 DAY FROM cte WHERE dt < '2022-01-01' )
SELECT cte.dt, SUM(test.val) sumval
FROM cte
LEFT JOIN test USING (dt)
GROUP BY cte.dt;
На моей рабстанции этот модельный запрос выполняется где-то за 3,27-3,31 секунды.
Конечно, запрос хочется немного ускорить. Создадим покрывающий индекс:
1.
CREATE INDEX idx ON test (dt, val);
Отлично. Запрос выполняется за 2,88-2,92 секунды.
Можно ли его ещё ускорить?
Оказывается, можно, да ещё как! и всего-то для этого надо обратить внимание на типы.
Поле
dt в таблице, по которому выполняется связывание, имеет тип DATE. А что насчёт рекурсивного CTE? А вот там тип получающегося поля определяется якорем (нерекурсивным запросом), и оно - строковое!
Изменим генератор календаря так, чтобы он сразу генерировал поле типа DATA:
1.
2.
3.
4.
5.
6.
7.
8.
WITH RECURSIVE
cte AS ( SELECT DATE '2021-01-01' dt
UNION ALL
SELECT dt + INTERVAL 1 DAY FROM cte WHERE dt < '2022-01-01' )
SELECT cte.dt, SUM(test.val) sumval
FROM cte
LEFT JOIN test USING (dt)
GROUP BY cte.dt;
Есть! время выполнения запроса уменьшилось вдвое, и теперь составляет 1,41-1,45 секунды.
Если не создавать индекса, разница не столь значительна - начальные 3,27-3,31 секунды уменьшаются до 2,51-2,55 секунды. Но всё равно - вполне заметно.
В общем, не надейтесь на MySQL, следите за типами сами. И вам воздастся.
