oracle取两个值同时不为0_PostgreSQL简单验证，似是而非unionall后orderby取limit

文章之前的描述存在不少问题&＃xff0c;今天重新改写了些内容。

开篇之前还是先来回顾一下这个错误的MySQL优化案例&＃xff1a;

开发同学期望将两个表的数据汇集之后&＃xff0c;取满足条件的top N&＃xff0c;于是使用union all联合两个结果集&＃xff0c;最后使用limit取top 30。两个表的数据量接近500w&＃xff0c;执行耗时12.66秒&＃xff1a;

整体来看&＃xff0c;性能比较差&＃xff0c;下面这种优化是期望将limit推入到两个联合的子句中来减少联合对性能影响&＃xff0c;执行耗时在0.03秒&＃xff0c;效果还不错。

但这里用到的优化思路看起来好像没有问题&＃xff0c;但逻辑的实现是有问题的&＃xff0c;而且改写前原SQL的逻辑也是存在问题的&＃xff0c;具体是什么问题&＃xff0c;看完下面的实验之后&＃xff0c;相信也就自然明白了。

下面我们通过PostgreSQL来演示一下这里埋的是什么坑。MySQL和Oracle应该也是一样的效果。时间有限&＃xff0c;就不做过多对比了。

先来创建两个测试表&＃xff1a;

akendb&＃61;# \d&＃43; tab01 Table "public.tab01" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------&＃43;---------&＃43;-----------&＃43;----------&＃43;---------&＃43;---------&＃43;--------------&＃43;------------- id | integer | | not null | 0 | plain | |  num_col01 | integer |           | not null | 0       | plain   |              | akendb&＃61;# \d&＃43; tab02 Table "public.tab02" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------&＃43;---------&＃43;-----------&＃43;----------&＃43;---------&＃43;---------&＃43;--------------&＃43;------------- id | integer | | not null | 0 | plain | | num_col01 | integer | | not null | 0 | plain | | akendb&＃61;#

然后每张表简单插入3行数据即可&＃xff1a;

akendb&＃61;# select * from tab01; id | num_col01 ----&＃43;----------- 1 | 100 3 | 45 3 | 11(3 rows)akendb&＃61;# select * from tab02; id | num_col01 ----&＃43;----------- 1 | 73 2 | 58 2 | 22(3 rows)akendb&＃61;#

--两表数据union all&＃xff1a;

(select * from tab01 ) 

union all 

(select * from tab02) 

akendb&＃61;# (select * from tab01 ) akendb-# union all akendb-# (select * from tab02) ; id | num_col01 ----&＃43;----------- 1 | 100 3 | 45 3 | 11 1 | 73 2 | 58 2 | 22(6 rows)akendb&＃61;#

--测试语句&＃xff1a;业务期望是想得出两表合并后根据id排序前面的两条记录&＃xff0c;即tab01中(id&＃61;1,num_col01&＃61;100)和tab02中的(id&＃61;1,num_col01&＃61;73)。

(select * from tab01 ) 

union all 

(select * from tab02) 

order by  id asc  limit  2;

akendb&＃61;# (select * from tab01 ) akendb-# union all akendb-# (select * from tab02) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 100 1 | 73(2 rows)akendb&＃61;#

接着我们对调一下量表的先后顺序&＃xff0c;除了记录的顺序不一样&＃xff0c;得到的结果是相同的两行数据。

(select * from tab02 ) 

union all 

(select * from tab01) 

order by  id asc  limit  2;

akendb&＃61;# (select * from tab02 ) akendb-# union all akendb-# (select * from tab01) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 73 1 | 100(2 rows)akendb&＃61;#

依样画葫芦&＃xff0c;我们将order by和limit内推到union all各个字句中&＃xff0c;查询结果和上面的一致&＃xff0c;好像没什么问题。

(select * from tab01 order by id limit 2) 

union all 

(select * from tab02 order by id limit 2)   

order by  id asc  limit  2;

(select * from tab02 order by id limit 2) 

union all 

(select * from tab01 order by id limit 2)   

order by  id asc  limit  2;

akendb&＃61;# (select * from tab01 order by id limit 2) akendb-# union all akendb-# (select * from tab02 order by id limit 2) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 100 1 | 73(2 rows)akendb&＃61;# (select * from tab02 order by id limit 2) akendb-# union all akendb-# (select * from tab01 order by id limit 2) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 73 1 | 100(2 rows)akendb&＃61;#

细心的同学或许已经看出了其中的问题&＃xff0c;为了让问题更加明显&＃xff0c;我这里将order by字段的值改动一下&＃xff1a;让每个表或第一张表的order by字段重复值的数量大于limit n中的n&＃xff1a;

update tab01 set id&＃61;1;

update tab02 set id&＃61;2;

akendb&＃61;# update tab01 set id&＃61;1;   --进行update之后id&＃61;1的行数为3&＃xff0c;大于limit 2取数范围。UPDATE 3akendb&＃61;# update tab02 set id&＃61;1;UPDATE 3akendb&＃61;#

首先&＃xff0c;直接执行原来的查询语句&＃xff1a;

(select * from tab01 ) 

union all 

(select * from tab02) 

order by  id asc  limit  2;

(select * from tab02 ) 

union all 

(select * from tab01) 

order by  id asc  limit  2;

akendb&＃61;# (select * from tab01 ) akendb-# union all akendb-# (select * from tab02) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 45 1 | 100(2 rows)akendb&＃61;# (select * from tab02 ) akendb-# union all akendb-# (select * from tab01) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 58 1 | 73(2 rows)akendb&＃61;#

上面执行结果和之前的查询结果不太一样了&＃xff0c;原因是union all中做order by实际上根据第一个表的字段在排序&＃xff0c;然后limit的取值实际上倾向于能取第一个结果集的limit n&＃xff0c;所以到底是tab01还是tab02在前&＃xff0c;其实在union all的应用场景里面是有所区别的。

akendb&＃61;# explain analyze (select * from tab01 ) union all (select * from tab02) order by id asc limit 2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Limit (cost&＃61;110.40..110.41 rows&＃61;2 width&＃61;8) (actual time&＃61;0.031..0.033 rows&＃61;2 loops&＃61;1) -> Sort (cost&＃61;110.40..121.70 rows&＃61;4520 width&＃61;8) (actual time&＃61;0.030..0.030 rows&＃61;2 loops&＃61;1) Sort Key: tab01.id Sort Method: top-N heapsort Memory: 25kB -> Append (cost&＃61;0.00..65.20 rows&＃61;4520 width&＃61;8) (actual time&＃61;0.012..0.018 rows&＃61;6 loops&＃61;1) -> Seq Scan on tab01 (cost&＃61;0.00..32.60 rows&＃61;2260 width&＃61;8) (actual time&＃61;0.012..0.013 rows&＃61;3 loops&＃61;1) -> Seq Scan on tab02 (cost&＃61;0.00..32.60 rows&＃61;2260 width&＃61;8) (actual time&＃61;0.004..0.004 rows&＃61;3 loops&＃61;1) Planning time: 0.118 ms Execution time: 0.062 ms(9 rows)akendb&＃61;# explain analyze (select * from tab02 ) union all (select * from tab01) order by id asc limit 2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Limit (cost&＃61;110.40..110.41 rows&＃61;2 width&＃61;8) (actual time&＃61;0.029..0.031 rows&＃61;2 loops&＃61;1) -> Sort (cost&＃61;110.40..121.70 rows&＃61;4520 width&＃61;8) (actual time&＃61;0.028..0.028 rows&＃61;2 loops&＃61;1) Sort Key: tab02.id Sort Method: top-N heapsort Memory: 25kB -> Append (cost&＃61;0.00..65.20 rows&＃61;4520 width&＃61;8) (actual time&＃61;0.010..0.016 rows&＃61;6 loops&＃61;1) -> Seq Scan on tab02 (cost&＃61;0.00..32.60 rows&＃61;2260 width&＃61;8) (actual time&＃61;0.010..0.011 rows&＃61;3 loops&＃61;1) -> Seq Scan on tab01 (cost&＃61;0.00..32.60 rows&＃61;2260 width&＃61;8) (actual time&＃61;0.003..0.004 rows&＃61;3 loops&＃61;1) Planning time: 0.110 ms Execution time: 0.058 ms(9 rows)akendb&＃61;# 

接着&＃xff0c;我们先看看“优化”改写后的效果&＃xff1a;

(select * from tab01 order by id limit 2) 

union all 

(select * from tab02 order by id limit 2)   

order by  id asc  limit  2;

(selt * from tab02 order by id limit 2) 

union all 

(select * from tab01 order by id limit 2)   

order by  id asc  limit  2;

akendb&＃61;# (select * from tab01 order by id limit 2) akendb-# union all akendb-# (select * from tab02 order by id limit 2) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 100 1 | 45(2 rows) akendb&＃61;# (select * from tab02 order by id limit 2) akendb-# union all akendb-# (select * from tab01 order by id limit 2) akendb-# order by id asc limit 2; id | num_col01 ----&＃43;----------- 1 | 73 1 | 58(2 rows)akendb&＃61;#

上面的输出结果&＃xff0c;看似和原句的结果保持一致&＃xff0c;事实上&＃xff0c;当第一张表的中order by字段的记录满足比第二张表的记录都小的时候&＃xff0c;那么这种内推到子句的改法是明显错误的&＃xff0c;比如下面这种情况&＃xff1a;

akendb&＃61;# select * from tab01; id | num_col01 ----&＃43;----------- 1 | 45 1 | 6 1 | 65(3 rows)akendb&＃61;# select * from tab02; id | num_col01 ----&＃43;----------- 2 | 13 2 | 16 2 | 82(3 rows)akendb&＃61;# (select * from tab01 order by id limit 2) union all (select * from tab02 order by id limit 2) order by id asc limit 3; id | num_col01 ----&＃43;----------- 1 | 45 1 | 6 2 | 13(3 rows)akendb&＃61;#

这里的查询结果就不应该出现第二张表的记录&＃xff0c;但因为内推改变了取数据的逻辑&＃xff0c;这是明显搞错了。

所以&＃xff0c;在union all之后执行order by取top n看似逻辑的理解上正确了&＃xff0c;但在实际查询结果却偏差了&＃xff0c;不少开发和运维的同学可能并没有觉察&＃xff0c;以前遇到过&＃xff0c;今儿再次遇到了&＃xff0c;简单记录一下。