MySQL中该SQL重写是否会保留结果集的排序顺序？

《高性能MySQL（第三版）》中提到：
一个常见问题是偏移量（offset）过大。如果你的查询类似LIMIT 10000, 20，服务器会生成10020行数据，然后丢弃前10000行，代价非常高。
一种简单的优化技巧是在覆盖索引上进行偏移操作，而非针对完整行数据。
考虑如下查询：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果表非常大，该查询最好改写为：

mysql> SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);

这种“延迟关联”的作用在于，让服务器在不访问行数据的情况下，尽可能少地扫描索引，找到目标行后，再与全表关联以获取行中的其他列。

问题 #

由于改写后的外层查询没有ORDER BY子句，请问该改写后的SQL是否会保留原SQL结果集的排序顺序？

参考表DDL #

CREATE TABLE film (
  film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(128) NOT NULL,
  description TEXT DEFAULT NULL,
  release_year YEAR DEFAULT NULL,
  language_id TINYINT UNSIGNED NOT NULL,
  original_language_id TINYINT UNSIGNED DEFAULT NULL,
  rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
  rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
  length SMALLINT UNSIGNED DEFAULT NULL,
  replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
  rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (film_id),
  KEY idx_title (title),
  KEY idx_fk_language_id (language_id),
  KEY idx_fk_original_language_id (original_language_id),
  CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

答案是不能严格保证和原查询结果的排序完全一致，实际执行中可能碰巧保持顺序，但依赖这种行为存在风险，具体分析如下：

1. 子查询的排序是确定的
   子查询SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5明确指定了ORDER BY title，且表上有idx_title（title字段的二级索引）。InnoDB的二级索引叶子节点会按title排序，同时存储主键film_id，所以子查询会直接通过这个索引按title顺序扫描，取出的film_id是严格按title排序后的结果（如果存在相同title的行，会按主键film_id顺序排列）。

2. 外层关联的顺序无官方保证
   外层查询是将主表film与子查询结果做INNER JOIN USING(film_id)。MySQL优化器会根据数据量、统计信息等选择连接算法：

   • 如果用嵌套循环连接，会按子查询返回的film_id顺序依次去主表读取对应行，此时结果会保持子查询的排序；
   • 如果用哈希连接，结果的顺序是哈希表的存储顺序，完全无法保证和原查询一致。

MySQL并没有规定关联操作会保留子查询的输出顺序，优化器的选择可能随数据变化而改变。

3. 原查询的排序逻辑
   原查询直接对全表按title排序后取结果，当有相同title的行时，会默认按主键顺序排序（这是InnoDB的特性）。改写后的子查询在处理相同title时，返回的film_id也是主键顺序，所以如果外层用嵌套循环连接，结果会和原查询一致，但这只是执行计划的一种可能，不是必然。

结论 #

如果需要严格保证结果集的排序和原查询完全一致，必须在外层查询也加上ORDER BY title（或ORDER BY title, film_id以消除相同title时的排序歧义），不能依赖子查询的排序传递到外层。虽然某些场景下结果可能一致，但这不属于MySQL官方承诺的行为，优化器的调整可能导致顺序错乱。

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