SQL（任何）请求了解查询优化

发布于 2024-07-19 13:22:57 字数 387 浏览 8 评论 0原文

由于大量信息连接在一起，我的查询特别慢。但是我需要添加一个 id in 形式的 where 子句（从表中选择 id）。

我想知道以下是否有任何收获，更紧迫的是，它是否会给出预期的结果。

select a.* from a where a.id in (select id from b where b.id = a.id)

作为替代：

select a.* from a where a.id in (select id from b)

更新： MySQL 无法更具体抱歉表 a 实际上是 7 个不同表之间的联接。使用 * 为例

编辑，b 不会被选中

原文

I have a particularly slow query due to the vast amount of information being joined together. However I needed to add a where clause in the shape of id in (select id from table).

I want to know if there is any gain from the following, and more pressing, will it even give the desired results.

select a.* from a where a.id in (select id from b where b.id = a.id)

as an alternative to:

select a.* from a where a.id in (select id from b)

Update:
MySQL
Can't be more specific sorry
table a is effectively a join between 7 different tables.
use of * is for examples

Edit, b doesn't get selected

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

思念满溢 2024-07-26 13:22:57

您列出的两个查询相当于：

select a.* 
from a 
inner join b on b.id = a.id

几乎所有优化器都会以相同的方式执行它们。

你可以发布一个真正的执行计划，这里有人可能会给你一种加快速度的方法。如果您指定正在使用的数据库服务器，将会有所帮助。

Both queries you list are the equivalent of:

select a.* 
from a 
inner join b on b.id = a.id

Almost all optimizers will execute them in the same way.

You could post a real execution plan, and someone here might give you a way to speed it up. It helps if you specify what database server you are using.

回复收藏 0 原文

誰認得朕 2024-07-26 13:22:57

YMMV，但我经常发现使用 EXISTS 而不是 IN 可以使查询运行得更快。

SELECT a.* FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id)

当然，在没有看到查询的其余部分和上下文的情况下，这可能不会使查询变得更快。

JOINing 可能是一个更可取的选项，但如果 a.id 在 b 的 id 列中出现多次，则必须在其中抛出 DISTINCT，并且很可能在优化方面出现倒退。

YMMV, but I've often found using EXISTS instead of IN makes queries run faster.

SELECT a.* FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id)

Of course, without seeing the rest of the query and the context, this may not make the query any faster.

JOINing may be a more preferable option, but if a.id appears more than once in the id column of b, you would have to throw a DISTINCT in there, and you more than likely go backwards in terms of optimization.

回复收藏 0 原文

如痴如狂 2024-07-26 13:22:57

我永远不会使用这样的子查询。加入会快得多。

select a.*
from a 
join b on a.id = b.id

当然也不要使用 select * （尤其是在进行连接时不要使用它，因为至少有一个字段是重复的），它会浪费网络资源来发送不需要的数据。

I would never use a subquery like this. A join would be much faster.

select a.*
from a 
join b on a.id = b.id

Of course don't use select * either (especially never use it when doing a join as at least one field is repeated) and it wastes network resources to send unnneeded data.

回复收藏 0 原文

森林迷了鹿 2024-07-26 13:22:57

您的问题是关于这两者之间的区别：

select a.* from a where a.id in (select id from b where b.id = a.id)

select a.* from a where a.id in (select id from b)

前者是一个相关子查询。它可能会导致MySQL对a的每一行执行子查询。

后者是一个非相关子查询。 MySQL 应该能够执行一次并缓存结果，以便与 a 的每一行进行比较。

我会使用后者。

Your question was about the difference between these two:

select a.* from a where a.id in (select id from b where b.id = a.id)

select a.* from a where a.id in (select id from b)

The former is a correlated subquery. It may cause MySQL to execute the subquery for each row of a.

The latter is a non-correlated subquery. MySQL should be able to execute it once and cache the results for comparison against each row of a.

I would use the latter.

回复收藏 0 原文

说不完的你爱 2024-07-26 13:22:57

你的问题很可能是在“a”内的七个表中，

使FROM表包含“a.id”
进行下一个联接：在 a.id = b.id 上内联接 b，

然后联接其他六个表。

如果您需要真正的帮助，您确实需要显示整个查询、列出所有索引以及每个表的大致行数

回复收藏 0 原文

别把无礼当个性 2024-07-26 13:22:57

你看过执行计划吗？

怎么样？

select a.* 
from a 
inner join b
on a.id = b.id

假设 id 字段是主键

Have you looked at the execution plan?

How about

select a.* 
from a 
inner join b
on a.id = b.id

presumably the id fields are primary keys?

回复收藏 0 原文

骄兵必败 2024-07-26 13:22:57

Select a.* from a
inner join (Select distinct id from b) c
on a.ID = c.AssetID

我尝试了所有 3 个版本，它们的运行情况大致相同。执行计划是相同的（内部联接、IN（子查询中带有和不带有where子句）、Exists）

由于您没有从B中选择任何其他字段，所以我更喜欢使用Where IN(Select...)任何人都会看在查询中并知道您要执行的操作（仅在 a if b 中显示）。

Select a.* from a
inner join (Select distinct id from b) c
on a.ID = c.AssetID

I tried all 3 versions and they ran about the same. The execution plan was the same (inner join, IN (with and without where clause in subquery), Exists)

Since you are not selecting any other fields from B, I prefer to use the Where IN(Select...) Anyone would look at the query and know what you are trying to do (Only show in a if in b.).

回复收藏 0 原文

~没有更多了~