何时使用 SQL 子查询与标准联接? [英] When to use SQL sub-queries versus a standard join?

问题描述

我正在重写一些写得不好的 SQL 查询，它们过度使用子查询.我正在寻找有关使用子查询的最佳实践.

I am working on rewriting some poorly written SQL queries and they are over-utilizing sub-queries. I am looking for best-practices regarding the use of sub-queries.

任何帮助将不胜感激.

推荐答案

子查询通常没问题，除非它们是 依赖子查询(也称为 相关子查询).如果您只使用独立的子查询并且它们使用适当的索引，那么它们应该快速运行.如果您有一个依赖子查询，您可能会遇到性能问题，因为依赖子查询通常需要为外部查询中的每一行运行一次.因此，如果您的外部查询有 1000 行，则子查询将运行 1000 次.另一方面，一个独立的子查询通常只需要评估一次.

Subqueries are usually fine unless they are dependent subqueries (also known as correlated subqueries). If you are only using independent subqueries and they are using appropriate indexes then they should run quickly. If you have a dependent subquery you might run into performance problems because a dependent subquery typically needs to be run once for each row in the outer query. So if your outer query has 1000 rows, the subquery will be run 1000 times. On the other hand an independent subquery typically only needs to be evaluated once.

如果您不确定子查询依赖或独立的含义，这是一个经验法则 - 如果您可以获取子查询，将其从上下文中删除，运行它并获取结果集，那么它就是一个 独立子查询.

If you're not sure what is meant by a subquery being dependent or independent here's a rule of thumb - if you can take the subquery, remove it from its context, run it, and get a result set then it's an independent subquery.

如果因为引用子查询之外的某些表而导致语法错误，那么它是一个依赖子查询.

If you get a syntax error because it refers to some tables outside of the subquery then its a dependent subquery.

一般规则当然有一些例外.例如:

The general rule of course has a few exceptions. For example:

• 许多优化器可以采用依赖子查询并找到一种方法将其作为 JOIN 高效运行.例如，NOT EXISTS 查询可能会导致 ANTI JOIN 查询计划，因此它不一定比使用 JOIN 编写查询慢.
• MySQL 有一个 错误，其中 IN 表达式中的独立子查询不正确标识为依赖子查询，因此使用次优查询计划.这显然已在最新版本的 MySQL 中得到修复.

• Many optimizers can take a dependent subquery and find a way to run it efficiently as a JOIN. For example an NOT EXISTS query might result in an ANTI JOIN query plan, so it will not necessarily be any slower than writing the query with a JOIN.
• MySQL has a bug where an independent subquery inside an IN expression is incorrectly identified as a dependent subquery and so a suboptimal query plan is used. This is apparently fixed in the very newest versions of MySQL.

如果性能是一个问题，那么衡量您的具体查询，看看什么最适合您.

If performance is an issue then measure your specific queries and see what works best for you.

这篇关于何时使用 SQL 子查询与标准联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！