当包含子查询时,为什么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
    ->                        (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来使其稍微提高效率,但这丝毫没有改变它使用键的方式.

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;