内部连接选择A和B上的(A，B)与MySQL中的select(A，B)中的(A，B) [英] inner join select (A,B) on A and B vs where (A,B) in select(A, B) in mysql

问题描述

这两个之间最好的查询是什么? 他们输出相同的结果，希望其中一个在where in内完成条件，而另一个在inner join内完成条件.

What is the best query between theses two ? They output the same result, expect one is doing the condition inside a where in and the other one inside an inner join.

select uv.* from version v inner join user_version uv ON v.id=uv.version_id
WHERE (v.number, v.master_id) IN (
select max(v.number) as number, v.master_id 
  from version v inner join user_version uv ON v.id=uv.version_id group by v.master_id);

和

 select * from user_version uv 
   inner join version v on v.id=uv.version_id and v.number
   inner join (
      select uv2.user_id, max(v2.number) maxNumber, v2.master_id master_id, v2.id version_id from version v2 
         inner join user_version uv2 on v2.id=uv2.version_id group by v2.master_id ) test
   on test.master_id=v.master_id and test.maxNumber=v.number ;

我创建了一个带有示例的sqlfiddle: http://sqlfiddle.com/#！ 2/76001/62 (该想法是获得链接到给定用户的主"实体的最大版本)

I've created a sqlfiddle with an example : http://sqlfiddle.com/#!2/76001/62 (The idea is to get the biggest version of a "master" entity linked to a given user)

如果您还有其他想法(我使用的是mysql，则无法使用Windows函数)

If you have other ideas (I'm using mysql, so I can't use windows function)

谢谢

推荐答案

回答这个问题并不容易.您应该知道一件事:MySQL将IN (<static values list>)和IN (<subquery>)视为不同的查询.第一个等于范围比较(例如.. OR = .. OR =)，而第二个等于= ANY ()-并不相同.因此，简而言之:将IN与子查询一起使用将导致对ANY()的查询，并且即使子查询是独立的并返回静态值列表，MySQL也不会为此使用索引.伤心，但真实. MySQL无法预测到这一点，因此即使显而易见，也不会使用索引.如果您将使用JOIN(即重写您的IN (<subquery>))-如果可能，MySQL将对JOIN条件使用索引.

This is not too easy to answer this question. You should know one important thing: MySQL treats IN (<static values list>) and IN (<subquery>) as different queries. First one is equal to range comparison (like .. OR = .. OR =) while second is equal to = ANY () - and it's not the same. So, to say short: using IN with subquery will cause query with ANY() and MySQL will not use index for that even if subquery is independent and returns static list of values. Sad, but true. MySQL can't predict that and so index will not be used even if it's obvious. If you'll use JOIN (i.e. rewrite your IN (<subquery>)) - then MySQL will use index for JOIN condition, if it's possible.

现在，使用分区时，第二种情况可能是JOIN和IN.如果您使用JOIN-令人遗憾的是-但MySQL在通常情况下也无法预测JOIN的分区-并且它将使用整个分区集.将JOIN替换为IN (<static list>)将会改变EXPLAIN PARTITION的图:MySQL将仅使用那些分区，这些分区是在IN子句中指定的从范围中选择值所需要的.但是，再次，这不适用于IN (<subquery>).

Now, the second case may be about JOIN and IN when using partitions. If you'll use JOIN - then, sadly - but MySQL also is not able to predict partitions for JOIN in common case - and it will use entire set of partitions for it. Replacing JOIN to IN (<static list>) will change EXPLAIN PARTITION picture: MySQL will use only those partitions, which are needed for selecting values from range, specified within IN clause. But, again, this will not work with IN (<subquery>).

作为结论-令人遗憾的是，当我们说到MySQL如何处理IN子查询时-在通常情况下，不能安全地用JOIN替换它(这是关于分区的情况).因此，常见的解决方案是:在应用程序级别上将子查询与主查询分开.如果我们要说的是独立子查询，返回静态值列表，那是最好的建议-那么您可以将该值列表替换为IN(<static list>)并获得好处:MySQL将为其使用索引，并且，如果我们说的是分区，仅使用它们中实际需要的.

As a conclusion - it's sad, when we're saying about how MySQL is handling IN subqueries - and in common case it can't be replaced with JOIN safely (that's about partitioning case). So, common solution will be: separate subquery from main query on application level. If we're saying about independent subquery, returning static values list, that's the best suggestion - then you can substitute that values list as IN(<static list>) and gain benefits: MySQL will use index for it, and, if we're saying about partitions, only actually needed from them will be used.

这篇关于内部连接选择A和B上的(A，B)与MySQL中的select(A，B)中的(A，B)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！