Simple JOIN查询缓慢，STRAIGHT_JOIN仅部分场景高效的优化求助

数据表结构 #

• Video (id, url, title, viewCount)：约1,000,000行
• VideoCategory (id, videoId, categoryId)：约6,000,000行
• Category (id, name)：约200行

现有索引配置 #

• VideoCategory(categoryId, videoId)：普通索引
• Category(name)：唯一索引

原始查询性能问题 #

执行以下SQL获取「Cars」分类下浏览量Top10视频时，耗时约5.5秒（该分类包含200,000个视频）：

SELECT v.* FROM Video v
  JOIN VideoCategory vc ON vc.videoId = v.id
  JOIN Category c ON vc.categoryId = c.id
  WHERE c.name = 'Cars' 
  ORDER BY v.viewCount DESC
LIMIT 10

而查询仅含100个视频的分类时，耗时仅约0.05秒。

「Cars」分类查询的EXPLAIN结果 #

+------+-------------+-------+--------+------------------------------------+--------------------+---------+-----------------------+--------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys                      | key                | key_len | ref                   | rows   | Extra                                        |
+------+-------------+-------+--------+------------------------------------+--------------------+---------+-----------------------+--------+----------------------------------------------+
|    1 | SIMPLE      | c     | const  | name_UNIQUE                        | name_UNIQUE        | 322     | const                 | 1      | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | vc    | ref    | fk_Category_idx,category_video_idx | category_video_idx | 8       | const                 | 493988 | Using index                                  |
|    1 | SIMPLE      | v     | eq_ref | PRIMARY                            | PRIMARY            | 8       | VideoDB.vc.videoId    | 1      | Using where                                  |
+------+-------------+-------+--------+------------------------------------+--------------------+---------+-----------------------+--------+----------------------------------------------+

需求：如何将查询耗时优化至0.1秒以内？移除ORDER BY可大幅提速，但无法满足业务需求。

优化尝试更新 #

尝试使用STRAIGHT_JOIN并调整查询逻辑后，以下SQL耗时仅0.011秒：

SELECT v.* FROM Video v
STRAIGHT_JOIN VideoCategory vc ON v.id = vc.videoId
WHERE vc.categoryId = (SELECT id FROM Category WHERE name = 'Cars')
ORDER BY v.viewCount ASC
LIMIT 10

同时将JOIN Category c替换为子查询，若分类不存在可直接返回0条结果。

但新问题出现：

• STRAIGHT_JOIN仅在分类包含1000+视频时高效（视频数量越多速度越快），分类视频数少于100时反而极慢，移除STRAIGHT_JOIN则恢复快速。

疑问 #

1. 如此简单的查询，为何查询优化器无法自动选择最优执行路径？
2. 将排序方向从ASC改为DESC时，部分分类查询变慢（例如ASC耗时0.01秒，DESC耗时0.8秒），这是什么原因？

内容的提问来源于stack exchange，提问作者saguinus_imperator