mysql select with in子句不使用索引 [英] Mysql select with in clause does not use index
问题描述
我有一个联系人表,其主键为id。它还有一个辅助索引idx_id_del_user(id,deleted,user_id)。
I have a contacts table which has a primary key of id. It also has a secondary index idx_id_del_user (id, deleted, user_id).
以下查询使用索引,因此非常快 -
The following query uses the index and therefore is very fast -
select id
from jts_contacts
where id = '00000402-25c8-7375-e3df-4ec5b66de11d'
and deleted = 0;
在0.0098s中获取1行
1 row fetched in 0.0098s
但是,当我使用in子句时,外部查询进入全表扫描。我希望它使用主键或idx_id_del_user。
However when I use the in clause the outer query goes into a full table scan. I am expecting it to use either the primary key or the 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
在9s内获取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万条记录和t他对桌子进行了分析。我没有使用FORCE INDEX选项尝试过它,但它仍然没有使用索引。有关更快地进行此查询的建议吗?
This table has 1.2 million records and the table was analyzed. I have tried it without the FORCE INDEX option, but it is still not using the index. Any suggestions on making this query faster?
警告:使用连接代替in子句将起作用,但是因为这是来自现有产品的生成查询 - 它无法更改为使用连接。
Caveat: using a join instead of the in clause will work, however since this is a generated query from a existing product - it cannot be changed to use joins.
推荐答案
据我所知, IN
遍历所有匹配的记录,并将它们与子句中的值进行比较,一次一行。
From what I can tell, IN
goes through all of the matching records and compares them to the values in the clause, one row at a time.
所以,你能做的最好的是使用已删除
上的索引,您只会查看 deleted = 0
的记录。
So, the best you can do is use an index on deleted
and you'll only be going through the records where deleted = 0
.
这篇关于mysql select with in子句不使用索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!