当我包含子查询时，为什么MySQL不使用索引？

Question

我有以下查询，它很好，但随着brands表的增长会变得更慢：

mysql> explain select brand_id as id,brands.name from tags
    ->                        INNER JOIN brands on tags.brand_id = brands.id
    ->                        where brand_id in
    ->                        (select brand_id from tags where outfit_id in
    ->                        (1,6,68,265,271))
    ->                        group by brand_id, brands.name
    ->                        ORDER BY count(brand_id)
    ->                        LIMIT 5;
+----+--------------------+--------+----------------+------------------------------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type        | table  | type           | possible_keys                                  | key                    | key_len | ref             | rows | Extra                                        |
+----+--------------------+--------+----------------+------------------------------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
|  1 | PRIMARY            | brands | ALL            | PRIMARY                                        | NULL                   | NULL    | NULL            |  165 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | tags   | ref            | index_tags_on_brand_id                         | index_tags_on_brand_id | 5       | waywn.brands.id |    1 | Using where; Using index                     |
|  2 | DEPENDENT SUBQUERY | tags   | index_subquery | index_tags_on_outfit_id,index_tags_on_brand_id | index_tags_on_brand_id | 5       | func            |    1 | Using where                                  |
+----+--------------------+--------+----------------+------------------------------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

我不明白为什么这里不使用主键作为索引并进行文件排序。如果用子查询返回的值替换该子查询，MySQL将正确使用索引：

mysql> explain select brand_id as id,brands.name from tags
    ->                       INNER JOIN brands on tags.brand_id = brands.id
    ->                       where brand_id in
    ->                       (2, 2, 9, 10, 40, 32, 9, 118)
    ->                       group by brand_id, brands.name
    ->                       ORDER BY count(brand_id)
    ->                       LIMIT 5;
+----+-------------+--------+-------+------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys          | key                    | key_len | ref             | rows | Extra                                        |
+----+-------------+--------+-------+------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
|  1 | SIMPLE      | brands | range | PRIMARY                | PRIMARY                | 4       | NULL            |    6 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | tags   | ref   | index_tags_on_brand_id | index_tags_on_brand_id | 5       | waywn.brands.id |    1 | Using where; Using index                     |
+----+-------------+--------+-------+------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select brand_id from tags where outfit_id in                        (1,6,68,265,271);
+----+-------------+-------+-------+-------------------------+-------------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys           | key                     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+-------------------------+-------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | tags  | range | index_tags_on_outfit_id | index_tags_on_outfit_id | 5       | NULL |    8 | Using where |
+----+-------------+-------+-------+-------------------------+-------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

为什么会这样呢？这对我来说并不是很有意义。我的意思是，我可以把它分成两个电话，但那看起来很糟糕。我确实注意到，我可以通过在子查询中包含distinct来提高效率，但这并没有改变它使用键的方式。

Answer 1

你为什么不直接写：

SELECT brand_id as id,brands.name 
FROM tags
INNER JOIN brands ON tags.brand_id = brands.id
WHERE outfit_id in (1,6,68,265,271)
GROUP BY brand_id, brands.name
ORDER BY count(brand_id)
LIMIT 5;