MYSQL在使用NOT IN时不使用index

Question

我有一个表，其中有一个列的索引，所以像这样的查询：

select * from table where id in (...)

正确使用索引。

但是，如果我使用相反的方法：

select * from table where id not in (...)

则不使用该索引。问题是表有超过100,000行，所以全表扫描的成本非常高。NOT IN部分并不小，它可以从表中消除大约3/4的行，因此优化器使用这些ids和索引来消除行并只对其余行运行全表扫描是有意义的。但根据解释，这并不会发生。

有没有办法让MYSQL在第二种情况下使用索引？

Answer 1

我只是用随机did做了一个测试数据库，并在几行代码中使用了NOT IN，但是它确实使用了索引。MySQL可能认为在您的情况下不使用索引更好，例如，由于not IN的分布，可能是数据类型，也可能只是您的MySQL版本。

但是，可以强制查询使用索引，如本page中所述。

在本例中，您的查询将变为：

select * from table FORCE INDEX (PRIMARY) where id not in (...)

Answer 2

in列表告诉mysql跳过哪些in，但是为了找到表中存在的其他in，它必须执行全表扫描，所以这就是它所做的。我不相信mysql足够聪明，可以根据列索引来区分主键索引。

但是，您可以在查询中自己执行此操作--您可以包含一个嵌套查询，该查询拉出要返回的in，并只选择这些行。嵌套查询可以使用主键索引。就像这样

select * from table join (select id from table where id not in (...)) t using(id)

嵌套查询只查看主键，它将使用索引。select通过主键拉出特定的行，它也将使用索引。