Автоматизирует ли MySQL подзапросы автоматически?

Ответ 1

К сожалению, MySQL не очень хорошо оптимизирует подзапросы с IN. Это от документации MySQL:

Оптимизация подзапросов для IN не так эффективна, как для оператора = или для оператора IN (value_list).

Типичный случай низкой производительности подзапроса IN - это когда подзапрос возвращает небольшое количество строк, но внешний запрос возвращает большой количество строк для сравнения с результатом подзапроса.

Проблема заключается в том, что для оператора, который использует подзапрос IN, оптимизатор переписывает его как коррелированный подзапрос. Рассмотрим следующее оператор, который использует некоррелированный подзапрос:

SELECT... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

Оптимизатор перезаписывает оператор в коррелированный подзапрос:

SELECT... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

Если внутренний и внешний запросы возвращают строки M и N, соответственно, время выполнения становится порядка O (M × N), а не O (M + N), как это было бы для некоррелированного подзапроса.

Импликация заключается в том, что подзапрос IN может быть намного медленнее, чем запрос написанный с использованием оператора IN (value_list), который отображает те же значения что подзапрос вернется.

Попробуйте использовать JOIN вместо этого.

Поскольку MySQL работает изнутри, иногда вы можете обмануть MySQL, обернув подзапрос внутри еще одного подзапроса, например:

SELECT COUNT(*) FROM table_name WHERE device_id IN
     (SELECT * FROM (SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA') tmp)

Здесь решение JOIN:

SELECT COUNT(DISTINCT t2.id) FROM table_name t1
  JOIN table_name t2
    ON t2.device_id = t1.device_id
  WHERE t1.NAME = 'SOME_PARA'

Обратите внимание, что я начинаю изнутри и выхожу также.

Ответ 2

Изменить: я понятия не имею, в чем причина глупости MySQL в этом случае:), этот отчет об ошибках, похоже, относится к делу. Обходным путем является использование JOIN

SELECT 
    COUNT(t1.device_id) 
FROM table_name t1 
JOIN (
    SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA'
) as t2 ON t2.device_id = t1.device_id 

Ответ 3

Я думаю, вы могли бы переписать запрос как:

 SELECT sum(NumOnDevice) 
 from (SELECT device_id, count(*) as NumOnDevice
       FROM table_name
       having sum(case when NAME = 'SOME_PARA' then 1 else 0 end) > 0
      ) t

Я понимаю, что это не отвечает на ваш вопрос, но это может вам помочь.

В плане оптимизации существует различие между предоставлением запроса кучей констант и предоставлением запроса подзапроса (даже если результаты одинаковы). В первом случае оптимизатор запросов имеет гораздо больше информации для принятия решения по плану запроса. Во втором случае информация недоступна во время компиляции.

Mysql - больше, чем большинство баз данных, похоже, создает план запроса, основанный на том, как выражается запрос. SQL был разработан как декларативный язык, а не процедурный. Это означает, что SQL-запросы описывают желаемый набор результатов, и механизм запросов должен принять решение о наилучшем способе достижения этого результата. Тем не менее, есть много случаев, когда нужно помочь механизму базы данных, чтобы получить наилучшие результаты.

Ответ 4

Посмотрите на то, что вы просите MySQL, нужно посмотреть каждую запись в table_name, определить, находится ли device_id в списке, который он получает, выполнив запрос, а затем решить, добавляет ли он его в счетчик, Таким образом, он запускает подзапрос 2.1M раз.

Именно поэтому, когда этот список определен вручную, он может быстро перебирать его.