Выполнение вариантов использования SQL "EXISTS"
Есть ли разница в производительности следующих трех операторов SQL?
SELECT * FROM tableA WHERE EXISTS (SELECT * FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT y FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT 1 FROM tableB WHERE tableA.x = tableB.y)
Все они должны работать и возвращать одинаковый набор результатов. Но имеет ли значение, если внутренний SELECT выбирает все поля таблицы B, одно поле или просто константу?
Есть ли лучшая практика, когда все утверждения ведут себя одинаково?
9 ответов
Правда в предложении EXISTS заключается в том, что предложение SELECT не оценивается в предложении EXISTS - вы можете попробовать:
SELECT *
FROM tableA
WHERE EXISTS (SELECT 1/0
FROM tableB
WHERE tableA.x = tableB.y)
... и следует ожидать ошибки деления на ноль, но вы этого не сделаете, потому что она не оценивается. Вот почему моя привычка - указывать NULL в EXISTS, чтобы продемонстрировать, что SELECT можно игнорировать:
SELECT *
FROM tableA
WHERE EXISTS (SELECT NULL
FROM tableB
WHERE tableA.x = tableB.y)
Все, что имеет значение в предложении EXISTS, - это предложения FROM и последующие - WHERE, GROUP BY, HAVING и т. Д.
Этот вопрос не был помечен с точки зрения базы данных, и это должно быть потому, что поставщики обрабатывают вещи по-разному - так что тестируйте и проверяйте планы объяснения / выполнения для подтверждения. Возможно, что поведение меняется между версиями...
Определенно № 1. Это "выглядит" страшно, но понимаешь, что оптимизатор поступит правильно и выразит намерение. Также есть небольшой бонус за опечатку, если кто-то случайно подумает, что СУЩЕСТВУЕТ, но напечатал IN. № 2 приемлемо, но не выразительно. Третий вариант воняет по моему не столь скромному мнению. Это слишком близко к слову "если не существует ценности" для комфорта.
В общем, важно не бояться писать код, который выглядит просто неэффективно, если он дает другие преимущества и фактически не влияет на производительность.
То есть оптимизатор почти всегда будет выполнять сложное волшебство соединения / выбора / группировки, чтобы таким же образом сохранить простой подзапрос EXISTS/.
После того, как вы дали себе ревность за умное переписывание этого неприятного ИЛИ из объединения, вы в конечном итоге поймете, что оптимизатор все еще использует тот же самый дрянной план выполнения, чтобы решить гораздо более простой для понимания запрос со встроенным ИЛИ в любом случае.
Мораль этой истории в том, что вы знаете оптимизатор ваших платформ. Попробуйте разные вещи и посмотрите, что на самом деле делается, потому что безудержные предположения по поводу "декоративной" оптимизации запросов почти всегда неверны и не имеют отношения к моему опыту.
Я понимаю, что это старый пост, но я подумал, что важно добавить ясности о том, почему один может выбрать один формат вместо другого.
Во-первых, как указывали другие, ядро базы данных должно игнорировать предложение Select. Каждая версия SQL Server имеет / делает, Oracle делает, MySQL делает и так далее. Во многих, многих лунах разработки баз данных я когда-либо встречал только одну СУБД, которая должным образом не игнорировала предложение Select: Microsoft Access. В частности, более старые версии MS Access (я не могу говорить с текущими версиями).
До моего открытия этой "функции" я использовал Exists( Select *..., Тем не менее, я обнаружил, что MS Access будет проходить через каждый столбец в подзапросе, а затем отбрасывать их (Select 1/0 также не будет работать). Это убедило меня перейти на Select 1, Если бы даже одна СУБД была глупой, другая могла бы существовать.
Пишу Exists( Select 1... так же совершенно ясно, как выразить намерение (откровенно глупо утверждать, что "это слишком близко к высказыванию" если не существует никакого значения "для удобства".) и делает шансы СУБД сделать что-то глупое с оператором Select практически невозможным. Select Null будет служить той же цели, но просто больше символов, чтобы написать.
Я перешел на Exists( Select 1 чтобы быть абсолютно уверенным, что СУБД не может быть глупой. Однако это было много месяцев назад, и сегодня я ожидаю, что большинство разработчиков ожидают увидеть Exists( Select * который будет работать точно так же.
Тем не менее, я могу предоставить одну хорошую причину, чтобы избежать Exists(Select * даже если ваша СУБД оценивает это правильно. Гораздо проще найти и устранить все виды использования Select * если вам не нужно пропускать каждый экземпляр его использования в предложении Exists.
В SQL Server, по крайней мере,
Наименьшее количество данных, которые можно прочитать с диска, представляет собой одну "страницу" дискового пространства. Как только процессор читает одну запись, которая удовлетворяет предикатам подзапроса, он может остановиться. Подзапрос не выполняется так, как если бы он стоял сам по себе, а затем включается во внешний запрос, он выполняется как часть полного плана запроса для всего этого. Таким образом, при использовании в качестве подзапроса, на самом деле не имеет значения, что находится в предложении Select, в любом случае ничего не возвращается "внешнему запросу", кроме логического значения, указывающего, была ли найдена одна запись или нет...
Все три используют один и тот же план выполнения
Я всегда использую [Select * From ... ], так как думаю, что он читается лучше, не подразумевая, что я хочу, чтобы что-то конкретное было возвращено из подзапроса.
РЕДАКТИРОВАТЬ: Из комментария Дейва Коста... Oracle также использует тот же план выполнения для всех трех вариантов
Это один из тех вопросов, которые граничат с началом какой-то священной войны.
Здесь довольно хорошая дискуссия.
Я думаю, что ответ, вероятно, заключается в использовании третьего варианта, но увеличение скорости настолько бесконечно мало, что о нем действительно не стоит беспокоиться. Это легко тот тип запроса, который SQL Server может оптимизировать внутри системы в любом случае, так что вы можете обнаружить, что все параметры эквивалентны.
EXISTS возвращает логические, а не фактические данные, в которых рекомендуется использовать № 3.
План выполнения. Изучи это, используй это, люби это
На самом деле нет никакого способа угадать.
В дополнение к тому, что сказали другие, практика использования SELECT 1 возникла на старом Microsoft SQL Server (до 2005 г.) - его оптимизатор запросов не был достаточно умен, чтобы избежать физического извлечения полей из таблицы для SELECT *, Насколько мне известно, ни одна другая СУБД не имеет такого недостатка.
EXISTS проверяет наличие строк, а не то, что в них, поэтому, кроме некоторой причуды оптимизатора, подобной приведенной выше, не имеет значения, что находится в списке SELECT.
SELECT * кажется наиболее обычным, но другие также приемлемы.
№ 3 Должен быть лучшим, так как вам все равно не нужны возвращаемые данные. Принесение полей только добавит дополнительные издержки