在MySQL中，inner join select (A,B) on A and B与where (A,B) in select(A, B)有何区别？

Question

两种查询语句哪个更好？ 它们输出相同的结果，只是一个在where in中进行条件判断，而另一个在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（这个想法是获取与给定用户相关的“主”实体的最大版本）。如果您有其他想法（我正在使用mysql，因此无法使用Windows函数），谢谢。

- Quentin

---

一般来说，“不相关子查询”这样的解决方案（如第二个）往往比它们的替代方案更快。虽然您的查询有点复杂，但我认为原则仍然适用。 - Strawberry

Answer 1

回答这个问题并不太容易。你需要知道一件重要的事情：MySQL将IN(<static values list>)和IN(<subquery>)视为不同的查询。第一个等同于范围比较(像.. OR = .. OR =)，而第二个等同于= ANY ()——并且它们并不相同。所以, 简而言之: 使用带有子查询的IN将导致带有ANY()的查询，并且即使子查询是独立的并返回静态值列表，MySQL也不会对其使用索引。很遗憾，这是真的。MySQL无法预测这一点，因此即使显而易见，索引也不会被使用。如果你使用JOIN(即重写你的IN (<subquery>))，那么如果可能，MySQL将在JOIN条件中使用索引。

现在，第二种情况可能涉及到JOIN和IN在使用分区时。如果你使用JOIN，那么不幸的是，MySQL在普通情况下也无法预测JOIN的分区，它将使用整个分区集。将JOIN替换为IN(<static list>)将改变EXPLAIN PARTITION图片:MySQL将只使用那些从IN子句中指定的范围选择值所需的分区。但是，这在IN(<subquery>)中将不起作用。

作为结论——当我们谈论MySQL如何处理IN子查询时，情况很糟糕，并且在普遍情况下，它无法安全地替换为JOIN(这是关于分区的情况)。因此，常见的解决方案是：在应用程序级别上将子查询与主查询分开。如果我们谈论的是返回静态值列表的独立子查询，则这是最佳建议——然后你可以将该值列表替换为IN(<static list>)并获得好处:MySQL将对其使用索引，并且如果我们谈论的是分区，则只会使用实际需要的分区。

- Alma Do

---

你是说第一个请求不会使用master_id/number的索引，而对于第二个请求，子查询将返回与每行选择匹配的所有结果，没有在test.master_id=v.master_id条件上进行过滤吗？（在这种情况下，分区的想法对我来说并不是很清楚） - Quentin

第一个不会在IN子查询中使用索引。 - Alma Do