MySQL ORDER BY 使用 SUBQUERY 比使用 PLAIN 简单查询执行得更好

Question

由于某种原因，我不明白这个普通的常规 MySQL 查询运行速度比预期慢。据我了解，MySQL 首先使用 WHERE 对查询集进行切片，然后对结果子集进行排序。我得到的印象是不同的（？）。也许“优化”查询的输出并不准确，即使它返回同一行。没有 ORDER BY 子句的相同查询运行得非常快。

Server version:     5.6.10 MySQL Community Server (GPL)

这是查询：

SELECT *
FROM `core_tweet`
WHERE `core_tweet`.`streamer_id` = 44566
ORDER BY `platform_id`
LIMIT 1;
1 row in set (27.84 sec)
rerun with SQL_NO_CACHE (46.39 sec)

这是现在“优化”的查询：

SELECT *
FROM  (
  SELECT *
  FROM `core_tweet`
  WHERE `core_tweet`.`streamer_id` = 44566
) sub
ORDER BY sub.platform_id ASC
LIMIT 1;
1 row in set (0.56 sec)
rerun with SQL_NO_CACHE (3.82 sec)

以及它们各自的 EXPLAIN 输出：

+----+-------------+------------+-------+-----------------------------------------------------+-------------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys                                       | key         | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+-----------------------------------------------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | core_tweet | index | core_tweet_streamer_id_51082c18_fk_core_streamer_id | platform_id | 402     | NULL |  462 | Using where |
+----+-------------+------------+-------+-----------------------------------------------------+-------------+---------+------+------+-------------+


+----+-------------+------------+------+-----------------------------------------------------+-----------------------------------------------------+---------+-------+-------+----------------+
| id | select_type | table      | type | possible_keys                                       | key                                                 | key_len | ref   | rows  | Extra          |
+----+-------------+------------+------+-----------------------------------------------------+-----------------------------------------------------+---------+-------+-------+----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                                                | NULL                                                | NULL    | NULL  | 52072 | Using filesort |
|  2 | DERIVED     | core_tweet | ref  | core_tweet_streamer_id_51082c18_fk_core_streamer_id | core_tweet_streamer_id_51082c18_fk_core_streamer_id | 5       | const | 52072 | NULL           |
+----+-------------+------------+------+-----------------------------------------------------+-----------------------------------------------------+---------+-------+-------+----------------+

这是表：

CREATE TABLE `core_tweet` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `published_at` datetime(6) NOT NULL,
  `platform_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `retweet_count` bigint(20) DEFAULT NULL,
  `favorite_count` bigint(20) DEFAULT NULL,
  `hashtags` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tweet_url` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `streamer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform_id` (`platform_id`),
  KEY `core_tweet_streamer_id_51082c18_fk_core_streamer_id` (`streamer_id`),
  CONSTRAINT `core_tweet_streamer_id_51082c18_fk_core_streamer_id` FOREIGN KEY (`streamer_id`) REFERENCES `core_streamer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26267613 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表统计信息：

core_tweet row count: 25856893
core_tweet `WHERE streamer_id = 44566` row count: 25868
core_streamer row count: 47203

Answer 1

这甚至可以让第一个版本运行得更快：

将 KEY(streamer_id) 替换为

INDEX(streamer_id, platform_id)