更改 ORDER BY 方向會導致性能問題

我使用 MySQL 5.6，我有兩個表，每個表有 16M 行：

CREATE TABLE IF NOT EXISTS `newsstudios` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `newsstudio_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `newsstudio_id` (`newsstudio_id`),
 KEY `category_id` (`category_id`),
 KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

我有一個訂單查詢order by newsstudios.id ASC：

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
 select newsstudio_id from newsstudio_categories 
 where newsstudios.id=newsstudio_categories.newsstudio_id 
 and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;

此查詢的結果是：

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

但是當我將 order by 更改DESC為查詢執行時間時減少了 100 倍：

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

第一：為什麼這種順序方向的變化會導致性能上的巨大差異？

第二：在此查詢之前，我嘗試過LEFT JOIN查詢WHERE IN而不是查詢，WHERE EXISTS但它們有重複的結果，我應該使用GROUP BY它導致的結果using filesort，這會using temporary大大降低性能。您對查詢有更好的性能有什麼建議嗎？

如果您搜尋的不同類別不是很多，並且您可以建構更複雜的查詢，這將使用索引，(category_id, newsstudio_id)並且我希望它比您之前的查詢更有效，可以選擇ASC或DESC選擇。

建構起來有點複雜，如果類別數量為數十或數百，我預計效率會降低：

SELECT newsstudio_id
FROM 
 ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
     ORDER BY newsstudio_id DESC  LIMIT 5 
   ) 
   UNION 
   ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
     ORDER BY newsstudio_id DESC  LIMIT 5 
   )
     ...
   UNION
   ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
     ORDER BY newsstudio_id DESC  LIMIT 5 
   )
 ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;

引用自：https://dba.stackexchange.com/questions/45407