在子查询中使用 IN 不使用索引 [英] Using IN with a subquery doesn't use index

问题描述

我有以下查询

select * from mapping_channel_fqdn_virtual_host where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

解释上面的查询给了我以下结果:

explaining the above query gives me the following result:

explain select * from mapping_channel_fqdn_virtual_host where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+----+-------------+-----------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table                             | partitions | type    | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mapping_channel_fqdn_virtual_host | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where |
+----+-------------+-----------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

id 是表的主键.看起来这是一种范围类型的查询，它使用主键作为该查询的索引.

id is the primary key of the table. It looks like this is a range type of query and it uses the Primary Key as index for this one.

当我尝试解释以下查询时，我得到了不同的结果:

When I try to explain the following query I get different results:

explain select * from mapping_channel_fqdn_virtual_host where id in (select max(id) from mapping_channel_fqdn_virtual_host group by channel_id, fqdn_virtual_host_id);     
+----+-------------+-----------------------------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table                             | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------------------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | mapping_channel_fqdn_virtual_host | NULL       | ALL   | NULL             | NULL             | NULL    | NULL | 4849 |   100.00 | Using where |
|  2 | SUBQUERY    | mapping_channel_fqdn_virtual_host | NULL       | index | idx_channel_fqdn | idx_channel_fqdn | 8       | NULL | 4849 |   100.00 | Using index |
+----+-------------+-----------------------------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0,00 sec)

idx_channel_fqdn 是 groupby 子句中使用的列对的复合索引键.但是当使用子查询时，主查询会像以前一样停止使用索引.你能解释一下为什么会这样吗?

idx_channel_fqdn is a composite index key for the column pair used in the groupby clause. But when using subquery the Primary query stops using the index like it did before. Can you explain why this is happening?

尝试了 Anouar 建议的 JOIN 查询:

Tried the JOIN query Anouar suggested:

explain select *  from mapping_channel_fqdn_virtual_host as x join (select max(id) as ids from mapping_channel_fqdn_virtual_host group by channel_id, fqdn_virtual_host_id) as y on x.id=y.ids; 
+----+-------------+-----------------------------------+------------+--------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table                             | partitions | type   | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------------------------------+------------+--------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived2>                        | NULL       | ALL    | NULL             | NULL             | NULL    | NULL  | 4849 |   100.00 | Using where |
|  1 | PRIMARY     | x                                 | NULL       | eq_ref | PRIMARY          | PRIMARY          | 4       | y.ids |    1 |   100.00 | NULL        |
|  2 | DERIVED     | mapping_channel_fqdn_virtual_host | NULL       | index  | idx_channel_fqdn | idx_channel_fqdn | 8       | NULL  | 4849 |   100.00 | Using index |
+----+-------------+-----------------------------------+------------+--------+------------------+------------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0,00 sec)

从 index 和 eq_ref 类型来看，使用 JOIN 比使用子查询更好?你能解释一下连接解释表达式g的结果吗?

Judging by the index and eq_ref types it looks like it is better to use the JOIN than the subquery? Can you explain a bit more the outcome of the join explain expressiong?

推荐答案

可以查看 这个问题的答案你会有很好的想法.

You can see the answers for this question You will fine ideas.

我引用了一些答案

有时 MySQL 不使用索引，即使索引可用.一发生这种情况的情况是优化器估计使用索引将需要 MySQL 访问一个非常大的表中行的百分比.(在这种情况下，表扫描可能要快得多，因为它需要更少的搜索.)

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)

简而言之，尝试强制索引:

Briefly, Try forcing the index:

SELECT *
FROM mapping_channel_fqdn_virtual_host FORCE INDEX (name of the index you want to use)
WHERE (mapping_channel_fqdn_virtual_host.id IN (1,2,3,4,5,6,7,8,9,10));

或者改用 JOIN 并查看说明

Or use JOIN instead and see the explain

SELECT * FROM mapping_channel_fqdn_virtual_host mcf
JOIN (select max(id) as ids from mapping_channel_fqdn_virtual_host group by channel_id, fqdn_virtual_host_id)) AS mcfv 
ON mcf.id = mcfv.ids;

这篇关于在子查询中使用 IN 不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！