避免嵌套查询 [英] Avoiding Nested Queries

问题描述

避免嵌套查询有多重要.

How Important is it to avoid nested queries.

我一直学会避免像瘟疫一样躲避它们.但是对我来说，它们是最自然的事情.在设计查询时，我首先写的是嵌套查询.然后，我将其转换为联接，有时需要花费很多时间才能正确.而且很少会带来很大的性能改善(有时确实会改善)

I have always learnt to avoid them like a plague. But they are the most natural thing to me. When I am designing a query, the first thing I write is a nested query. Then I convert it to joins, which sometimes takes a lot of time to get right. And rarely gives a big performance improvement (sometimes it does)

他们真的那么糟糕吗?有没有一种方法可以使用没有临时表和文件排序的嵌套查询

So are they really so bad. Is there a way to use nested queries without temp tables and filesort

推荐答案

这确实取决于我遇到的情况，我可以通过使用子查询来改进一些查询.

It really depends, I had situations where I improved some queries by using subqueries.

我知道的因素是:

• 如果子查询是否使用外部查询中的字段进行比较(是否相关)
• 如果外部查询和子查询之间的关系被索引覆盖
• 如果联接上没有可用的索引，并且子查询不相关并且返回的结果很小，则使用它可能会更快
• 我还遇到了以下情况:将使用order by的查询转换为不使用order的查询，然后将其转换为简单的子查询并进行排序，以提高mysql的性能.

• if the subquery uses fields from outer query for comparison or not (correlated or not)
• if the relation between the outer query and sub query is covered by indexes
• if there are no usable indexes on the joins and the subquery is not correlated and returns a small result it might be faster to use it
• i have also run into situations where transforming a query that uses order by into a query that does not use it and than turning it into a simple subquery and sort that improves performance in mysql

无论如何，测试不同的变体(请使用SQL_NO_CACHE)总是好的，将相关查询转换为联接是一个好习惯.

Anyway, it is always good to test different variants (with SQL_NO_CACHE please), and turning correlated queries into joins is a good practice.

我什至会称其为非常有用的做法.

I would even go so far to call it a very useful practice.

如果您首先想到了相关查询，则可能不是您主要不是在考虑集合操作，而主要是在过程操作以及与关系数据库打交道时考虑，这对完全使用查询很有用.对数据模型采用集合观点并对​​其进行转换.

It might be possible that if correlated queries are the first that come to your mind that you are not primarily thinking in terms of set operations, but primarily in terms of procedural operations and when dealing with relational databases it is very useful to fully adopt the set perspective on the data model and transformations on it.

程序性与关系性
关于集合操作与过程的思考可以归结为某些集合代数表达式中的等价关系，例如，在并集上的选择等同于选择的并集.两者之间没有区别.
但是，当您比较这两个过程时，例如将选择标准应用到具有make union的并集的每个元素上，然后应用选择，则这两个过程是截然不同的过程，它们可能具有非常不同的属性(例如，CPU利用率，I /O，内存).

Procedural vs Relational
Thinking in terms of set operations vs procedural boils down to equivalence in some set algebra expressions, for example selection on a union is equivalent to union of selections. There is no difference between the two.
But when you compare the two procedures, such as apply the selection criteria to every element of an union with make a union and then apply selection, the two are distinctly different procedures, which might have very different properties (for example utilization of CPU, I/O, memory).

关系数据库背后的想法是，您不会尝试描述如何获得结果(过程)，而只是描述您想要的结果，并且数据库管理系统将决定满足您请求的最佳路径(过程) .这就是为什么将SQL称为第四代语言(4GL)的原因.

The idea behind relational databases is that you do not try to describe how to get the result (procedure), but only what you want, and that the database management system will decide on the best path (procedure) to fulfil your request. This is why SQL is called 4th generation language (4GL).

可以帮助您做到这一点的技巧之一是提醒自己，元组没有固有的顺序(集合元素是无序的). 另一个人意识到关系代数非常全面，并且可以将请求(要求)直接转换为SQL(如果模型的语义很好地表示了问题空间，或者换句话说，如果表和关系的名称附带的含义正确完成) ，换句话说，如果您的数据库设计合理的话.

One of the tricks that help you do that is to remind yourself that tuples have no inherent order (set elements are unordered). Another is realizing that relational algebra is quite comprehensive and allows translation of requests (requirements) directly to SQL (if semantics of your model represent well the problem space, or in another words if meaning attached to the name of your tables and relationships is done right, or in another words if your database is designed well).

因此，您不必思考如何，只需思考什么.

Therefore, you do not have to think how, only what.

在您的情况下，它只是优先于相关查询，因此可能我没有告诉您任何新内容，但您强调了这一点，因此发表了评论.

In your case, it was just preference over correlated queries, so it might be that I am not telling you anything new, but you emphasized that point, hence the comment.

我认为，如果您完全满意将查询从一种形式转换为另一种形式的所有规则(规则(例如分配性)，则您不希望使用相关子查询(即所有形式均等).

I think that if you were completely comfortable with all the rules that transform queries from one form into another (rules such as distributiveness) that you would not prefer correlated subqueries (that you would see all forms as equal).

(注:上面讨论了理论背景，对数据库设计很重要;实际上上述概念有所不同-并非所有等效的查询重写都必须以快速执行，集群主键的确会使表在磁盘上具有继承顺序，等等. ，但这些偏差仅仅是偏差；并非所有等效查询都执行得这么快的事实是实际DBMS的缺陷，而不是其背后的概念.

(Note: above discusses theoretical background, important for database design; practically the above concepts deviate - not all equivalent rewrites of a query are necessarily executed as fast, clustered primary keys do make tables have inherit order on disk, etc... but these deviations are only deviations; the fact that not all equivalent queries execute as fast is an imperfection of the actual DBMS and not the concepts behind it)

这篇关于避免嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！