Удаляет ли MySQL общие подвыражения между предложениями SELECT и HAVING/GROUP BY?

Я часто вижу, как люди отвечают на вопросы MySQL такими запросами:

SELECT DAY(date), other columns
FROM table
GROUP BY DAY(date);

SELECT somecolumn, COUNT(*)
FROM table
HAVING COUNT(*) > 1;

Мне всегда нравится давать столбцу псевдоним и ссылаться на него в предложении GROUP BY или HAVING, например.

SELECT DAY(date) AS day, other columns
FROM table
GROUP BY day;

SELECT somecolumn, COUNT(*) AS c
FROM table
HAVING c > 1;

Достаточно ли умен MySQL, чтобы заметить, что выражения в более поздних предложениях такие же, как в SELECT, и делают это только один раз? Я не уверен, как это проверить - EXPLAIN не показывает никакой разницы, но, похоже, он вообще не показывает, как он выполняет группировку или фильтрацию; это кажется в основном полезным для оптимизации соединений и предложений WHERE.

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


mysql query-optimization group-by having
person Barmar    schedule 23.05.2014    source источник
comment
На всякий случай, если вам нужен немедленный ответ, в качестве временной меры, пока я не найду авторитетную ссылку (и я подозреваю, что это может быть из источника), я почти уверен, что синтаксический анализатор распознает вызовы детерминированных функций ( и, конечно же, выражения) и кэширует результат для повторного использования в запросе.   -  person eggyal    schedule 23.05.2014
comment
+1 Внятный и полезный вопрос!   -  person    schedule 23.05.2014
comment
@eggyal Мне ничего не нужно немедленно, это больше любопытство. Интересно, следует ли мне рекомендовать людям улучшить свои ответы, когда я вижу эту избыточность. Я планирую продолжать писать свои запросы вторым способом, потому что считаю их более читабельными.   -  person Barmar    schedule 23.05.2014
comment
Бармар задает вопрос? Боюсь, мой компас необратимо откалиброван.   -  person Strawberry    schedule 23.05.2014
comment
На самом деле это обман (и, без сомнения, другие - я почти уверен, что уже задавал этот вопрос раньше, хотя, возможно, как часть другого вопроса), но меня не убеждают ответы на этот связанный вопрос, и я думаю, что это дает лучшую возможность создать каноническую ссылку.   -  person eggyal    schedule 23.05.2014
comment
Одной из простых демонстраций того, что результаты функции кэшируются, является SELECT RAND() FROM table ORDER BY RAND(), поскольку результирующий столбец действительно отсортирован, MySQL должен использовать одно и то же значение для обоих вызовов RAND().   -  person eggyal    schedule 23.05.2014
comment
Неплохо! Интересно, сможем ли мы придумать аналогичный тест для GROUP BY.   -  person Barmar    schedule 23.05.2014
comment
Не уверен, как лучше всего это продемонстрировать, но данный GROUP BY реализуется путем выполнения сортировки...   -  person eggyal    schedule 23.05.2014
comment
Да, если он сможет распознать общность в ORDER BY, я готов экстраполировать это на другие предложения. Опубликуйте свой пример в качестве ответа, и я приму его.   -  person Barmar    schedule 23.05.2014
comment
Я пытаюсь собрать немного более авторитетный ответ на основе исходного кода оптимизатора - просто подумал, что сейчас стоит упомянуть.   -  person eggyal    schedule 23.05.2014

Ответы (2)


arrow_upward
6
arrow_downward

Я думаю, это можно проверить с помощью функции sleep(),
например, взгляните на эту демонстрацию: http://sqlfiddle.com/#!2/0bc1b/1

Select * FROM t;

| X |
|---|
| 1 |
| 2 |
| 2 |

SELECT x+sleep(1)
FROM t
GROUP BY x+sleep(1);

SELECT x+sleep(1) As name
FROM t
GROUP BY name;

Время выполнения обоих запросов составляет около 3000 мс ( 3 секунды ).
В таблице 3 записи, и для каждой записи запрос приостанавливается только на 1 секунду,
поэтому это означает, что выражение вычисляется только один раз. для каждой записи, а не дважды.

person krokodilko    schedule 24.05.2014
comment
Дальнейшее «доказательство» - это когда вы меняете один из снов на (2). Теперь это занимает 9 секунд. - person Rick James; 02.11.2016
comment
select x+sleep(1), count(*) from t group by x+sleep(1) занимает 6 секунд. Что случается? Возможно, исходный пример просто преобразован в select distinct x+sleep(1) from t. - person Paul Spiegel; 03.11.2016
comment
Дополнительно: select x+sleep(1) as c from t having c > 0 также занимает 6 секунд. Итак, я думаю, каждый псевдоним внутренне заменяется выражением, стоящим за ним, и каждый раз оценивается снова. - person Paul Spiegel; 03.11.2016
comment
Но... SLEEP() на самом деле не является детерминированным. SQRT есть; RAND нет -- следовательно, SQRT можно было бы запомнить, а RAND - нет. OTOH, NOW() намеренно запоминается, а SYSDATE нет. - person Rick James; 03.11.2018

arrow_upward
1
arrow_downward

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

  • Кэширование — никакая часть запроса не «запоминается» для последующего использования в этом (или последующем) запросе. (Контраст: кэш запросов.)
  • Устранение общих подвыражений - нет. Это обычная техника компилятора, но MySQL ее не использует. Пример: (a-b)*(a-b) сделает вычитание дважды.
  • Удаление константы из цикла - да, с ограничениями. Это еще одна техника компилятора.
  • Различные SQL-ориентированные хаки - да; Смотри ниже.
  • Переоценка подзапроса - это зависит. Кроме того, оптимизатор постепенно совершенствуется.
  • VIEWs - зависит. До сих пор бывают случаи, когда VIEW суждено работать хуже, чем эквивалентный SELECT. Пример: нет условия, помещаемого в UNION в VIEW. На самом деле, это больше вопрос отсроченных действий.
  • Я думаю, что в некоторых более новых версиях MariaDB есть «кеш подзапросов».

(Предупреждение: у меня нет 100% уверенности ни в одном из моих ответов, но я верю, что большая часть из них верна, начиная с MySQL 5.7, MariaDB 10.1 и т. д.)

Думайте о многорядном SELECT как о цикле. Многие, а может быть, и все "детерминированные" выражения оцениваются один раз. Пример: константные выражения даты, даже с вызовами функций. Но...

NOW() специально оценивается один раз в начале запроса. Кроме того, значение передается ведомым устройствам при репликации. То есть к тому времени, когда запрос будет сохранен на ведомом устройстве, NOW() может устареть. (SYSDATE() — другое животное.)

Особенно с появлением only_full_group_by, GROUP BY необходимо знать, соответствует ли оно выражениям SELECT. Итак, это ищет аналогичный код.

HAVING и ORDER BY могут использовать псевдонимы из списка SELECT (в отличие от WHERE и GROUP BY). Таким образом, SELECT expr AS x ... HAVING expr, кажется, переоценивает expr, но SELECT expr AS x ... HAVING x, кажется, достигает уже оцененного expr.

Оконные функции MariaDB 10.2 имеют довольно строгие ограничения на то, где их можно/нельзя использовать повторно; У меня пока нет полного представления о них.

Как правило, все это не имеет значения — повторное вычисление выражения (DATE(date) или даже COUNT(*)) даст тот же ответ. Кроме того, просмотр строк обычно намного дороже, чем вычисление выражений. Так что, если у вас нет хорошего секундомера, вы не заметите разницы.

person Rick James    schedule 03.11.2016
comment
Учитывая демонстрацию в другом ответе, означает ли это, что он считает SLEEP(1) детерминированным выражением, поэтому оценивает его только один раз? - person Barmar; 03.11.2016
comment
Детерминированный - нет. В противном случае запрос занял бы 1 секунду, а не 3. Я думаю, что x+sleep(1) подпадает под ласковые слова, которые я произнес на GROUP BY. Обратите внимание, что GROUP BY x+sleep(2) занимает 9 секунд; Я не знаю, участвует ли x в проверке only_full_group_by. - person Rick James; 03.11.2016