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

Я хотел запустить следующий запрос:

-- Main Query    
SELECT COUNT(*) FROM table_name WHERE device_id IN 
     (SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA')

Этот следующий запрос (дополнительный запрос из основного запроса):

SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA'

выполняется за 7 секунд, давая 2691 строку из таблицы из строк 2.1M.

Я выпустил Основной запрос выше, и он все еще выполняется после 5 минут + ожидания.

Наконец, я выполнил дополнительный запрос отдельно, взял 2691 записей из результата, выполнил следующий запрос:

-- Main Query (improvised)    
SELECT COUNT(*) FROM table_name WHERE device_id IN 
     ("device_id_1", "device_id_2", ....., "device_id_2691")

Удивительно, но это дало мне ответ в течение 40 секунд.

Что дает? Почему 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 раз.

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