前言
在使用 MySQL 数据库时，很多开发者都提出过类似的问题：
- What is the default order of records for a SELECT statement in MySQL?
- What is The Default Sort Order of SELECTS with no ORDER BY Clause?
这篇文章记录了一个因为没有指定 ORDER BY 而产生的分页异常场景，如果能够帮助到正在读文章的你，那便是最大的价值。
转载请注明来自 SphereEx 中文论坛 https://community.sphere-ex.com/
背景知识
在分片场景下，获取分页数据是 SQL 是要经过分页修正的。
以分库为例，将一张数据表水平拆分为 2 片：
假设每 2 条数据为一页，要取第 2 页数据，原始 SQL中写为 limit 2,2（期望获取 2 条数据），
在分片环境下将改写为 limit 0,4，在各个分片表分别执行后再进行结果归并（则结果集共有 8 条数据），并舍弃前 2 条数据，从而获取总体第 3、4 条数据，作为分页第 2 页的查询结果。
改写原理见【改写引擎-分页修正】：
https://shardingsphere.apache.org/document/legacy/3.x/document/cn/features/sharding/principle/rewrite/
异常场景
在应用系统中，有一个利息计算的定时任务，每日会把符合条件的的全部订单取出，计算当日应增加的利息。由于订单数据量大，采用分页的方式查询数据，分批处理。如以下 SQL，获取需要计算的 id：
SELECT L.ID FROM TB_LOAN L WHERE L.STATUS IN ('a' , 'b') AND L.ACCOUNT_DATE < '2022-04-14' LIMIT ?,?;
上述 limit 参数分别为0,2 / 2,2 / 4,2 / 6,2 ……;
改写后 Actual SQL 中 limit 值分别为 0,2 / 0,4 / 0,6 / 0,8 ……；
1. 原始数据
在某测试环境中，TB_LOAN 表拆分在两个数据库中，符合状态和日期条件的数据分别有：
图 1 imp-0数据
图 2 imp-1数据
如图，imp-0 库有 12 条记录，imp-1 库有 14条 记录，共 26 条；同时 ID 为全局唯一，不重复。
2. 异常表现
通过单线程方式，循环获取每一页 LoanId ，26 条记录共 13 页，部分日志如下：
图 3 分页查询日志
通过日志可以发现 [33245579051926589] 这条记录被重复获取了，同时总记录 26 条并没有错，即还有一条数据没有获取到。
由于上述异常，产生的直接效果就是当日利息计算出现重复数据（对同一笔借款重复计息），同时有一笔借款未计息。
原因剖析
经过分析，该问题并非 ShardingSphere 的缺陷，而是由于在不同 limit 数值的情况下，MySQL 数据库返回的结果集顺序不同，因而在截取分页数据的时候就产生了差异。
我们通过 Navicat 对两个分片库分别执行查询，调整原始 SQL 的 limit 参数，有如下结果：
图 4 imp-0 分页 0,14
图 5 imp-0 分页 0,26
图 6 imp-1 分页 0,14
图 7 imp-1 分页 0,26
通过以上 4 个查询，可以看出 imp-0 库在 limit 0,14 和 limit 0,26 时结果集一致，而 imp-1 库两次查询的结果集顺序不同。
回看利息计算任务的查询：
当通过 limit 12,2 获取第 7 页数据时，33245579051926589 出现在 [12+2] 的位置 (imp-0 有 12 条结果)，因而第 7 页数据得到 [33245578313724013, 33245579051926589]；
当通过 limit 24,2 获取第 13 页数据时，33245579051926589 出现在了 [12+14] 的位置，因而第 13页 数据得到 [33245578976428885, 33245579051926589]，重复结果就此产生。
也正是因为以上原因，imp-1 库中 33245578464725673 这条数据至始至终没有被查询出来，未计算利息。
同时可以观察到：
改变 limit 值并非一定会影响查询结果的顺序，因此以上场景并不一定能在每个测试环境复现。
解决方案
在所有使用 limit 分页获取数据的查询中，增加 order by 语句，使结果集按照特定顺序排列，即可避免该问题发生，如：
SELECT L.ID FROM TB_LOAN L WHERE L.STATUS IN ('a' , 'b') AND L.ACCOUNT_DATE < '2022-04-14' ORDER BY L.ID LIMIT ?,?;
总结
“Do not depend on order when ORDER BY is missing. ”
参考资料
- SQL best practice to deal with default sort order
database - SQL best practice to deal with default sort order - Stack Overflow - What is The Default Sort Order of SELECTS with no ORDER BY Clause?
https://forums.mysql.com/read.php?21,239471,239688#msg-239688 - What is the default order of records for a SELECT statement in MySQL?
index - What is the default order of records for a SELECT statement in MySQL? - Database Administrators Stack Exchange