在大量数据集上使用IN是个好主意吗? [英] Is using an IN over a huge data set a good idea?

问题描述

假设我有一个查询形式:

Let's say I have a query of the form:

SELECT a, b, c, d 
FROM table1 
WHERE a IN (
  SELECT x 
  FROM table2 
  WHERE some_condition);

现在，对IN的查询可以返回大量记录. 假设a是主键，那么使用索引是编写这种查询的最佳方法吗?

Now the query for the IN can return a huge number of records. Assuming that a is the primary key, so an index is used is this the best way to write such a query?

还是最好遍历子查询返回的每个记录?

Or it is more optimal to loop over each of the records returned by the subquery?

对于我来说很明显，当我执行where a = X时，很显然我只是在执行索引(树)遍历.
但是我不确定IN(尤其是在庞大的数据集上)如何遍历/利用索引.

For me it is clear that when I do a where a = X it is clear that I just do an index (tree) traversal.
But I am not sure how an IN (especially over a huge data set) would traverse/utilize an index.

推荐答案

MySQL优化器尚未真正做好正确处理的准备(喷射)，您应将此类查询重写为iNNER JOIN并正确索引，这将是假设t1.a和t2.x是唯一的禁食方法

The MySQL optimizer isn't really ready (jet) to handle this correctly you should rewrite this kind of query to a iNNER JOIN and index correctly this will be the fasted method assuming t1.a and t2.x are unique

类似的东西.

SELECT 
a
, b
, c
, d
FROM 
  table1 as t1
INNER JOIN
  table2 as t2
ON t1.a = t2.x
WHERE 
 t1.some_condition .... 

并确保t1.a和t2.x具有PRIMARY或UNIQUE索引

And make sure that t1.a and t2.x have PRIMARY or UNIQUE indexes

这篇关于在大量数据集上使用IN是个好主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！