План выполнения движка SQL при НАЛИЧИИ vs (подзапроса) ГДЕ

#mysql #sql #optimization

#mysql #sql #оптимизация

Вопрос:

При выполнении есть ли какая-либо разница между следующими двумя sql-запросами:

 SELECT name, count(*) FROM mytable GROUP BY name HAVING count(*) > 1

И:

 SELECT * from (SELECT name, count(*) cnt FROM mytable GROUP BY name) x where cnt > 1

Другими словами, имеет ли наличие больше «удобного» предложения для упрощения выполнения подзапроса, или производительность механизма запросов принципиально отличается при использовании оператора having по сравнению со вторым подходом? В настоящее время в mysql:

Создать таблицу:

 CREATE TABLE `mytable` (
  `name` varchar(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf-8;

1. Очень широкий вопрос, особенно если у нас нет инструкции create table.. А также без знания версии MySQL

2. @RaymondNijland теперь обновлен с таблицей.

3. @RaymondNijland в любом случае, судя по вашему ответу, эти два варианта отличаются, и его выполнение основано на конкретном движке?

4. «его выполнение основано на конкретном движке?» Что ж, в MySQL 8 оптимизатор намного лучше, чем в MySQL 5.7. «судя по вашему ответу, эти два параметра действительно отличаются» что ж, в этом случае я думаю, что скорости выполнения будут более или менее равными, в то время как некоторые оптимизаторы могли бы оптимизировать вариант внутреннего sql-запроса намного лучше, потому что внутренний набор результатов sql более «ограничен» и выполняет его намного быстрее.

5. Простое правило (в MySQL): избегайте подзапросов, когда альтернатива не является более запутанной. MySQL — это годы (десятилетия?) отстает от других поставщиков в оптимизации подзапроса.

Ответ №1:

Практически в любой другой базе данных эти два параметра были бы эквивалентны. Для краткости, HAVING обычно является лучшим выбором.

По крайней мере, исторически MySQL материализовал подзапросы. Итак, этот запрос:

 SELECT * 
FROM (SELECT name, count(*) as cnt
      FROM mytable
      GROUP BY name
     ) x 
WHERE cnt > 1;

предполагает, что он собирается записать производную таблицу, а затем повторно просканировать ее для получения окончательной WHERE . Однако это мало влияет на производительность, потому что GROUP BY уже считывает и записывает данные.

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