E.4. Ограничения на Подзапросы

Подоптимизация запросов для 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) оператор, который перечисляет те же самые значения, которые возвратил бы подзапрос.

Вообще, невозможно изменить таблицу и выбрать из той же самой таблицы в подзапросе. Например, это ограничение применяется к операторам следующих форм:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);UPDATE t ... WHERE col = (SELECT ... FROM t ...);{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

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

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

Здесь следствие подзапроса в FROM пункт сохранен как временная таблица, таким образом, соответствующие строки в t были уже выбраны к этому времени обновление к t имеет место.

Операции сравнения строки только частично поддерживаются:

Другими словами, для подзапроса, который возвращает строки n- кортежи, это поддерживается:

(expr_1, ..., expr_n) [NOT] IN table_subquery

Но это не поддерживается:

(expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery

Причина поддержки сравнений строки для IN но не для других это IN реализуется, переписывая это как последовательность = сравнения и AND операции. Этот подход не может использоваться для ALL, ANY, или SOME.

Подзапросы в FROM пункт не может быть связанными подзапросами. Они осуществляются полностью (оцененный, чтобы произвести набор результатов) во время выполнения запроса, таким образом, они не могут быть оценены на строку внешнего запроса. Перед MySQL 5.6.3 материализация имеет место перед оценкой внешнего запроса. С 5.6.3, оптимизатор задерживает материализацию, пока результат не необходим, который может разрешить материализации избежаться. См. Раздел 8.13.16.3, "Оптимизируя Подзапросы в FROM Пункт (Полученные Таблицы)" .

MySQL не поддерживает LIMIT в подзапросах для определенных операторов подзапроса:

mysql> SELECT * FROM t1    ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Оптимизатор более зрел для соединений чем для подзапросов, таким образом, во многих случаях оператор, который использует подзапрос, может быть выполнен более эффективно, если Вы переписываете это как соединение.

Исключение происходит для случая где IN подзапрос может быть переписан как a SELECT DISTINCT соединение. Пример:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

Тот оператор может быть переписан следующим образом:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

MySQL разрешает подзапросу обращаться к сохраненной функции, у которой есть изменяющие данные побочные эффекты, такие как вставка строк в таблицу. Например, если f() вставляет строки, следующий запрос может изменить данные:

SELECT ... WHERE x IN (SELECT f() ...);

Это поведение является расширением стандарта SQL. В MySQL это может привести к неопределенным результатам потому что f() мог бы быть выполнен различное число раз для различного выполнения данного запроса в зависимости от того, как оптимизатор хочет обрабатывать это.

Для основанного на операторе или репликации смешанного формата, одна импликация этого indeterminism - то, что такой запрос может привести к различным результатам на ведущем устройстве и его ведомых устройствах.

Перед MySQL 5.6.3, подзапросом в FROM пункт оценивается, осуществляя результат во временную таблицу, и эта таблица не использует, индексирует. С 5.6.3, оптимизатор создает индексирование на осуществленной таблице, если это приведет к более быстрому выполнению запроса. См. Раздел 8.13.16.3, "Оптимизируя Подзапросы в FROM Пункт (Полученные Таблицы)".