使用CTAS带ORDER BY创建表后，分页查询能否省略ModifiedUtc排序？

结论：不能省略ORDER BY ModifiedUtc，必须显式指定才能保证分段查询的结果顺序符合预期

原因如下：

• 大多数数据库的CREATE TABLE AS SELECT (CTAS)语句中，即使查询语句带了ORDER BY，也不保证最终表内数据的物理存储顺序严格遵循该排序规则。数据库在写入数据时，可能会因存储引擎机制、磁盘分配策略等因素打乱原有顺序，查询返回的结果顺序和表的物理存储顺序是两回事。
• 就算数据写入时碰巧是有序的，后续对MyTable执行插入、更新、删除操作后，数据的物理顺序必然会被破坏，分段查询的结果会出现乱序。
• 数据库优化器不会因为你创建表时用了ORDER BY，就默认后续不带排序的查询结果是有序的，SELECT ... LIMIT ... OFFSET语句在没有显式排序时，返回的行顺序是不确定的。

建议的解决方案：

• 给MyTable的ModifiedUtc列创建索引，降低排序的性能开销：

  CREATE INDEX idx_mytable_modifiedutc ON MyTable(ModifiedUtc);
  

• 每次执行分段查询时，必须显式添加ORDER BY ModifiedUtc，确保结果顺序正确：

  SELECT * FROM MyTable ORDER BY ModifiedUtc LIMIT 15000 OFFSET 30000;
  

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