Я работаю над переписыванием некоторых плохо написанных SQL-запросов, и они чрезмерно используют подзапросы. Я ищу лучшие практики использования подзапросов.
Любая помощь будет оценена по достоинству.
Когда использовать подзапросы SQL вместо стандартного соединения?
comment
Дело не в том, что серебряной пули нет — проблема в том, что вопрос слишком широк, чтобы на него можно было дать разумный ответ. Нет никакой гарантии, что то, что работает в одной базе данных (MySQL), точно так же будет работать в другой. Самое простое правило заключается в том, что если столбцы из подзапроса находятся в конечном наборе результатов, используйте JOIN — это наиболее оптимальный способ получения данных из двух таблиц. В противном случае подзапрос подойдет, но коррелированные могут вызвать проблемы (но не с EXISTS).
- person OMG Ponies schedule 26.01.2011
comment
На каком основании вы утверждаете, что подзапросы используются чрезмерно? Кажется странным решить это, а затем запросить информацию о лучших методах использования подзапросов.
- person Larry Lustig schedule 26.01.2011
comment
Возможный дубликат Join vs. sub-query
- person Ciro Santilli 新疆再教育营六四事件ۍ schedule 12.06.2016
comment
@OMGPonies Ваше простейшее правило соответствует AskTom, они (соединения/подзапросы) семантически различны. * Используйте подзапрос, если вам не нужны столбцы из таблиц, на которые ссылается подзапрос. * Используйте объединение, когда вам нужны некоторые из столбцов. select * from emp where deptno in (выбрать deptno from dept ); будет лучше, чем выбрать emp.* из emp, dept, где emp.deptno = dept.deptno; И помните, подзапрос нельзя просто заменить соединением (и наоборот), так как они часто приводят к РАЗНЫМ ОТВЕТАМ.
- person ExcessOperatorHeadspace schedule 14.05.2019
Ответы (3)
Подзапросы обычно подходят, если они не являются зависимыми подзапросами (также известными как коррелированные подзапросы). Если вы используете только независимые подзапросы и они используют соответствующие индексы, они должны выполняться быстро. Если у вас есть зависимый подзапрос, вы можете столкнуться с проблемами производительности, поскольку зависимый подзапрос обычно необходимо запускать один раз для каждой строки внешнего запроса. Таким образом, если ваш внешний запрос содержит 1000 строк, подзапрос будет выполнен 1000 раз. С другой стороны, независимый подзапрос обычно нужно оценивать только один раз.
Если вы не уверены, что подразумевается под зависимым или независимым подзапросом, вот практическое правило: если вы можете взять подзапрос, удалить его из контекста, запустить его и получить набор результатов, то это independent subquery.
Если вы получаете синтаксическую ошибку, потому что она ссылается на некоторые таблицы вне подзапроса, тогда это dependent subquery.
Общее правило, конечно, имеет несколько исключений. Например:
- Многие оптимизаторы могут взять зависимый подзапрос и найти способ его эффективного выполнения как JOIN. Например, запрос NOT EXISTS может привести к плану запроса ANTI JOIN, поэтому он не обязательно будет медленнее, чем написание запроса с JOIN.
- В MySQL есть ошибка, из-за которой независимый подзапрос внутри выражения IN ошибочно идентифицируется как зависимый подзапрос, поэтому используется неоптимальный план запроса. По-видимому, это исправлено в самых новых версиях MySQL.
Если производительность является проблемой, измерьте свои конкретные запросы и посмотрите, что лучше всего подходит для вас.
person
Mark Byers
schedule
25.01.2011
Под корреляцией вы подразумеваете содержащую ссылку на столбец во внешнем запросе?
- person El Ronnoco; 26.01.2011
Многие оптимизаторы могут взять зависимый подзапрос и найти способ его эффективного выполнения - я согласен с этим утверждением, и оно опровергает весь ваш аргумент, т.е. коррелированные подзапросы плохи, за исключением тех случаев, когда они не плохи.
- person onedaywhen; 21.09.2011
Здесь нет серебряной пули. Каждое использование должно оцениваться независимо. В некоторых случаях коррелированные подзапросы просто неэффективны, этот ниже лучше написать как JOIN
select nickname, (select top 1 votedate from votes where user_id=u.id order by 1 desc)
from users u
С другой стороны, запросы EXISTS и NOT EXISTS преобладают над JOIN.
select ...
where NOT EXISTS (.....)
Обычно быстрее, чем
select ...
FROM A LEFT JOIN B
where B.ID is null
Однако даже эти обобщения могут быть неверны для любой конкретной схемы и распределения данных.
person
RichardTheKiwi
schedule
25.01.2011
К сожалению, ответ во многом зависит от используемого вами SQL-сервера. Теоретически соединения лучше с чисто реляционной точки зрения. Они позволяют серверу делать правильные вещи под капотом и дают им больше контроля и, таким образом, могут работать быстрее. Если сервер реализован хорошо. На практике некоторые SQL-серверы работают лучше, если вы обманываете их, оптимизируя запросы с помощью подзапросов и тому подобного.
person
Wes Hardaker
schedule
25.01.2011
Некоторые подзапросы также могут быть преобразованы в соединения (конечно, в зависимости от сервера). В большинстве случаев я считаю, что соединения лучше подходят для моей головы.
- person ; 26.01.2011
Нет, если сервер реализован хорошо, он распознает самый быстрый способ выполнения JOIN или подзапроса и создаст оптимальный план выполнения. Если JOIN и подзапрос алгебраически одинаковы, то хорошо реализованный сервер выдаст один и тот же план выполнения.
- person Larry Lustig; 26.01.2011
Да... Все верно. Если сервер реализован идеально, он также всегда оптимизирует ваши неработающие запросы ;-) Но... это было моей основной мыслью: почти все серверы sql работают лучше, если вы строите свои запросы в соответствии с тем, как он себя ведет. Это горькая правда. Как правило, чем дольше существует продукт, тем больше вероятность того, что он будет работать лучше, независимо от того, как вы структурируете свои запросы. Но даже это не всегда верно.
- person Wes Hardaker; 26.01.2011
объединения лучше с точки зрения чистой реляционной теории - нонсенс. Учтите, что SQL не был относительно полным в отношении алгебры Кодда, пока не была добавлена поддержка подзапросов.
- person onedaywhen; 21.09.2011
