为何使用SELECT *与指定列查询的结果排序不一致？

我有两张表，生产环境中使用SELECT *查询的结果排序与指定列查询的排序不一致，本地测试无法复现该问题，想了解生产环境中可能导致此问题的原因。

测试代码 #

DROP TABLE IF EXISTS #table1
DROP TABLE IF EXISTS #table2

CREATE TABLE #table1 (id int, code varchar(10), carriercode varchar(10), maxvalue numeric(14,3))
CREATE TABLE #table2 (id int, carriercode varchar(10))

-- 注意所有记录的maxvalue值均为2000.000
INSERT INTO #table1 (id,code,carriercode, maxvalue) SELECT 1,'a','carrier_a',2000.000
INSERT INTO #table1 (id,code,carriercode, maxvalue) SELECT 2,'a','carrier_b',2000.000
INSERT INTO #table1 (id,code,carriercode, maxvalue) SELECT 3,'c','carrier_c',2000.000
INSERT INTO #table2 (id,carriercode) SELECT 1,'carrier_a'
INSERT INTO #table2 (id,carriercode) SELECT 2,'carrier_b'

指定列查询语句 #

SELECT t1.id,t1.code,t1.parentcode,t1.carriercode 
FROM #table1 t1
LEFT JOIN #table2 t2 on t1.carriercode=t2.carriercode
WHERE (t1.parentcode = 'a')
AND (t1.maxvalue >= 830 OR t1.maxvalue is null)
ORDER BY t1.maxvalue DESC

查询结果 #

id  code    parentcode  carriercode
1   a1      a           carrier_a
2   a2      a           carrier_b

SELECT *查询语句 #

SELECT t1.id,t1.code,t1.parentcode,t1.carriercode,* 
FROM #table1 t1
LEFT JOIN #table2 t2 on t1.carriercode=t2.carriercode
WHERE (t1.parentcode = 'a')
AND (t1.maxvalue >= 830 OR t1.maxvalue is null)
ORDER BY t1.maxvalue DESC

查询结果 #

id  code    parentcode  carriercode id  code    parentcode  carriercode maxvalue    dt                      id  carriercode
1   a1      a           carrier_a   1   a1      a           carrier_a   2000.000    2022-09-30 22:49:52.787 1   carrier_a
2   a2      a           carrier_b   2   a2      a           carrier_b   2000.000    2022-09-30 22:49:52.787 2   carrier_b

注：测试环境中两种查询的table1.id列顺序一致，但生产环境中顺序不同。

已尝试的排查方法 #

• 精度问题：将numeric类型CAST为int，两种查询排序仍一致
• 修改初始插入顺序，两种查询排序仍一致

生产环境可能的原因分析 #

当ORDER BY的列存在重复值时，SQL标准并未规定重复值之间的排序顺序，数据库会根据执行计划的不同返回不同的顺序，生产环境与测试环境的差异可能来自以下几点：

1. 数据量差异：生产环境数据量远大于测试环境，数据库会选择不同的执行计划（比如测试用索引扫描，生产用哈希连接+排序），不同执行计划读取数据的底层顺序不同，导致重复值的排列顺序变化。
2. 索引差异：生产环境可能存在额外索引，指定列查询可以利用索引的有序性直接返回数据；而SELECT *需要返回所有列，可能无法使用该索引，只能走全表扫描或其他索引，最终排序后的剩余顺序不同。
3. 统计信息过时：生产环境表的统计信息未及时更新，数据库生成的执行计划与实际数据分布不匹配，导致两种查询的执行路径差异，进而影响排序结果。
4. 表结构差异：生产环境的表可能比测试表多了某些列（如测试代码中未体现的parentcode、dt列），这些列会影响执行计划的选择，比如SELECT *需要读取更多数据块，改变了数据读取顺序。
5. 数据库配置差异：生产环境与测试环境的数据库配置不同（如内存分配、并行查询设置、排序缓冲区大小），会影响数据库对执行计划的选择，导致排序后的顺序不一致。
6. 并发操作影响：生产环境存在并发的写入、更新操作，可能导致查询读取的数据版本不同（如快照隔离级别下），间接影响结果顺序（概率较低）。

解决方案建议 #

如果需要保证查询结果的顺序完全一致，必须在ORDER BY子句中添加唯一标识列（如t1.id），确保重复值之间的顺序确定性，示例：

ORDER BY t1.maxvalue DESC, t1.id ASC

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