SQL 查询中 LIMIT 分页排序常见问题

在 SQL 中使用 limit 进行查询时，多次执行结果可能不同，这是符合预期的。因为数据库仅能保证输出结果中 order by 以内字段的顺序，其他字段是不保序的，当 limit 截断在“不保序行”中间时，就会造成执行结果的变化，该行为符合SQL标准。

详细说明

在 SQL 中使用 limit 进行查询时，多次执行的结果可能不同。因为数据库仅能保证 SQL 输出结果中 ORDER BY 以内字段的顺序，其他字段是不保序的，当 limit 截断在“不保序行”中间时就可能会导致结果不同，该行为符合 SQL 标准，而非数据库的 BUG。若想保证 limit 多次执行结果相同，则必须保证整个 SQL 的输出顺序，这就需要从 SQL 语义上来保证，比如 ORDER BY 唯一索引列。

对于 ORDER BY 以外字段（包括无 ORDER BY 条件的 SQL）的输出顺序，SQL 标准中并未要求，其取决于数据库的具体实现方式，不同执行方案的输出顺序可能不同（比如：一张表在磁盘上有 1W 行，占据 100 个数据块。扫描该表时，若串行读取磁盘上的数据块，那么输出的前 10 行肯定是稳定的；但若以 10 个异步 IO 并发读取不同数据块，那么每个返回的 IO 都会吐出数据，前10行的输出顺序也就可能是完全随机的）。MySQL 官方文档中提到：在执行 SQL 时，优化器在满足指定查询的条件下必须能够自由地选择最佳执行方案， 否则对于仅关注执行结果的用户而言，固定执行方案可能会带来额外的性能损耗。即使部分数据库因为实现上的原因，事实上能做到 order by 以外字段的输出保序，但这并不属于 SQL 标准的要求。

示例。

在 MySQL 5.6.11 中验证上述行为，MySQL 官方文档也表明该行为是 By Design 的，而非 BUG。

-- 创建表并插入数据
create table test(id INT, col INT);

insert into test values(1,1);
insert into test values(2,1);
insert into test values(10,1);
insert into test values(3,1);

通过改变 limit 的不同起始位置，验证上述行为
mysql> select * from test order by col limit 0,2;
+------+------+
| id   | col  |
+------+------+
|    3 |    1 |
|    2 |    1 |
+------+------+

mysql> select * from test order by col limit 2,2;
+------+------+
| id   | col  |
+------+------+
|   10 |    1 |
|    3 |    1 |
+------+------+

影响租户

影响 OceanBase 数据库中的 MySQL 租户，对于 SYS 租户和 Oracle 租户无影响。

适用版本

OceanBase 数据库所有版本。