MySQL中使用in子句的select语句未使用索引

Question

我有一个联系人表，它的主键是id。它还有一个次要索引idx_id_del_user（id，deleted，user_id）。

以下查询使用了该索引，因此非常快 -

select id
from jts_contacts
where id = '00000402-25c8-7375-e3df-4ec5b66de11d'
and deleted = 0;

0.0098秒内检索到1行数据。

然而，当我使用in子句时，外部查询会进行全表扫描。我期望它使用主键或idx_id_del_user中的一个。

select  *
from jts_contacts FORCE INDEX (idx_id_del_user)
where id in
(select id
from jts_contacts
where id = '00000402-25c8-7375-e3df-4ec5b66de11d')
and deleted = 0

9秒内获取了1行数据。

执行计划 -

id, select_type,          table,         type, possible_keys,                 key, key_len, ref, rows, Extra
------------------------------------------------------------------------------------
1, 'PRIMARY',            'jts_contacts', 'ALL', '',                           '',   '',     '', 1127275, 'Using where'
2, 'DEPENDENT SUBQUERY', 'jts_contacts', 'const', 'PRIMARY,idx_id_del_user', 'PRIMARY', '108', 'const', 1, 'Using index'

这个表有120万条记录，并且已经被分析了。我尝试了不使用FORCE INDEX选项，但仍然没有使用索引。对于加快此查询的速度有任何建议吗？

---

注意：使用联接而不是IN子句将起作用，但由于这是从现有产品生成的查询 - 不能修改以使用联接。

- user1112552

Answer 1

据我所知，IN将遍历所有匹配的记录，并逐行比较子句中的值。

因此，最好的方法是在deleted上建立索引，这样您只需要遍历那些deleted = 0的记录。

- Marcus Adams

---

谢谢。我也尝试过那个方法。但是deleted只有0和1两个值（它不是很具有选择性）。实际上，这会增加响应时间到12秒。我同意这个答案，但是它不起作用，因为删除的索引不是非常具有选择性。 - user1112552

Answer 2

我刚遇到了同样的问题，在阅读了Marcus Adams的帖子后，我想到了一些从运行基于同一表格数据的UPDATE中学到的东西：首先创建一个派生表，像这样：

SELECT * FROM jts_contacts WHERE id IN
(SELECT id FROM
    (SELECT id FROM jts_contacts WHERE id = '00000402-25c8-7375-e3df-4ec5b66de11d')
temp)
AND deleted = 0

此操作首先将内部查询结果完整地获取到派生表 temp 中，然后依据该表运行外部查询。

这种模式将我类似的查询速度从几分钟降至不到一秒。不开玩笑。

- DanMan