【 TiDB 使用环境】
生产环境
【 TiDB 版本】
V6.5.0
【资源配置】
tidb:3节点,pd:3节点,tivk:5节点
【问题描述】
同一个查询语句,每次执行结果都不一致!
sql语句如下:
select count(t.card_nbr) as balance from t_org t2
,
(
select c.ab_user,a.card_nbr,a.active_day,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a,F_CCS_ACCT b,f_ccs_apma c
where a.xaccount=b.xaccount and a.xaccount = c.account
and b.close_code !='W ' and a.active_day<='20230112' and a.active_day>0
)
t where
trim(t.ab_user)=trim(t2.code) AND
t.last_card = 1;
每次执行结果都不一致,结果在12504 到12507 之间来回变动。
测试的时候 发现 只带1个条件时,结果是固定的,如下:
where trim(t.ab_user)=trim(t2.code)
或者
where t.last_card = 1
但是两个条件一起时,结果就开始在 12504 到12507 之间来回变动
是因为分布式多节点环境导致的吗?有没有办法避免?
【更新】
脱敏后的测试数据和测试用的sql如下;在我的环境里能复现问题。
test.sql (1.9 MB)
测试用的sql:
select count(t.xaccount) as balance from t_org t2
,
(
select c.ab_user,a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a,f_ccs_apma c
where a.xaccount = c.account
)
t where
t.ab_user=t2.code and
t.last_card = 1
;
各位大佬看看在自己的环境里能不能复现?
帮忙分析一下是什么问题?
没有部署tiflash
执行计划:
mysql> explain select count(t.card_nbr) as balance from t_org t2
,
(
select c.ab_user,a.card_nbr,a.active_day,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a,F_CCS_ACCT b,f_ccs_apma c
where a.xaccount=b.xaccount and a.xaccount = c.account
and b.close_code !='W ' and a.active_day<='20230112' and a.active_day>0
)
t where
trim(t.ab_user)=trim(t2.code) AND
t.last_card = 1;
+----------------------------------------------+----------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------------+----------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_29 | 1.00 | root | | funcs:count(busidata.f_ccs_card.card_nbr)->Column#600 |
| └─HashJoin_33 | 12633.19 | root | | inner join, equal:[eq(Column#601, Column#602)] |
| ├─Projection_34(Build) | 71.00 | root | | trim(dgb-datamid.t_org.code)->Column#601 |
| │ └─TableReader_36 | 71.00 | root | | data:TableFullScan_35 |
| │ └─TableFullScan_35 | 71.00 | cop[tikv] | table:t_org | keep order:false, stats:pseudo |
| └─Projection_37(Probe) | 10106.55 | root | | busidata.f_ccs_card.card_nbr, trim(busidata.f_ccs_apma.ab_user)->Column#602 |
| └─Selection_38 | 10106.55 | root | | eq(Column#595, 1) |
| └─Shuffle_69 | 12633.19 | root | | execution info: concurrency:5, data sources:[HashJoin_41] |
| └─Window_39 | 12633.19 | root | | row_number()->Column#595 over(partition by busidata.f_ccs_card.xaccount order by busidata.f_ccs_card.cdindex desc rows between current row and current row) |
| └─Sort_68 | 12633.19 | root | | busidata.f_ccs_card.xaccount, busidata.f_ccs_card.cdindex:desc |
| └─HashJoin_41 | 12633.19 | root | | inner join, equal:[eq(busidata.f_ccs_card.xaccount, busidata.f_ccs_apma.account)] |
| ├─IndexJoin_46(Build) | 10349.89 | root | | inner join, inner:IndexLookUp_45, outer key:busidata.f_ccs_card.xaccount, inner key:busidata.f_ccs_acct.xaccount, equal cond:eq(busidata.f_ccs_card.xaccount, busidata.f_ccs_acct.xaccount) |
| │ ├─TableReader_60(Build) | 10310.32 | root | | data:Selection_59 |
| │ │ └─Selection_59 | 10310.32 | cop[tikv] | | gt(busidata.f_ccs_card.active_day, 0), le(busidata.f_ccs_card.active_day, 20230112) |
| │ │ └─TableFullScan_58 | 16546.00 | cop[tikv] | table:a | keep order:false |
| │ └─IndexLookUp_45(Probe) | 10310.32 | root | | |
| │ ├─IndexRangeScan_42(Build) | 10310.32 | cop[tikv] | table:b, index:PRIMARY(XACCOUNT) | range: decided by [eq(busidata.f_ccs_acct.xaccount, busidata.f_ccs_card.xaccount)], keep order:false |
| │ └─Selection_44(Probe) | 10310.32 | cop[tikv] | | ne(busidata.f_ccs_acct.close_code, "W ") |
| │ └─TableRowIDScan_43 | 10310.32 | cop[tikv] | table:b | keep order:false |
| └─TableReader_66(Probe) | 20040.00 | root | | data:Selection_65 |
| └─Selection_65 | 20040.00 | cop[tikv] | | not(isnull(busidata.f_ccs_apma.account)) |
| └─TableFullScan_64 | 20040.00 | cop[tikv] | table:c | keep order:false |
+----------------------------------------------+----------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set
裤衩儿飞上天:
是分区表吗
所有的表都不是 分区表,t_org 是一个视图,指向另一个数据库中的表
onlyacat:
能提供表结构和示例数据么
看看能不能复现
因为有敏感数据,所以表结构和实例数据没办法提供
我正在想办法创建几张类似的表,看看能不能复现
onlyacat:
能提供表结构和示例数据么
看看能不能复现
脱敏后的测试数据如下:
test.sql (1.9 MB)
测试的sql:
select count(t.xaccount) as balance from t_org t2
,
(
select c.ab_user,a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a,f_ccs_apma c
where a.xaccount = c.account
)
t where
t.ab_user=t2.code and
t.last_card = 1
;
我在测试库上执行,能复现问题。
结果会在19892 -19894 之间变动。
你是根据cdindex排序,分布式数据库不同节点返回的数据每次的快慢导致顺序可能不一样,哪每次按cdindex排序的相同数据的数据每次出现的位置可能就不一样,每次取第一条数据可能就不太一样了,只带第一个条件的时候每次都是全量数据所以每次都一样,只带第二个每次都是t2的所有1的数据然后笛卡儿集所以每次都一样,你可以换一个唯一的条件去排序结果就一样了
又测试了一下,窗口函数中的排序字段换成一个唯一的条件去排序,还是一样的问题,结果还是会变动
还有可能是什么问题呢?
根据这个测试数据,最小复现是这个 sql 的结果不稳定:
select
a.xaccount,
a.cdindex,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card,
c.ab_user
from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account
order by xaccount, cdindex desc, last_card, ab_user;
但是这个不稳定是符合预期的,因为 <a.xaccount, a.cdindex> 的组合在这个 join 结果集中不是唯一的,这导致 ab_user 与 last_card 的对应关系不稳定。原始 sql 里面 join 条件中有对 ab_user 以及 last_card 得过滤/join 条件,所以导致整个 sql 结果不稳定。
关于 <a.xaccount, a.cdindex> 的组合在这个 join 结果集中不是唯一 是通过下面这个对比看出来的:
mysql> select count() from (select distinct a.xaccount, a.cdindex from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account) x;
±---------+
| count( ) |
±---------+
| 16489 |
±---------+
1 row in set (0.02 sec)
mysql> select count() from (select a.xaccount, a.cdindex from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account) x;
±---------+
| count( ) |
±---------+
| 16575 |
±---------+
1 row in set (0.02 sec)
可以看到对 a.xaccount, a.cdindex 去重之后的结果与不去重的结果不一样
windtalker:
根据这个测试数据,最小复现是这个 sql 的结果不稳定 :
select
a.xaccount,
a.cdindex,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card,
c.ab_user
from f_ccs_card a,f_ccs_apma c where a.xaccount = c.account
order by xaccount, cdindex desc, last_card, ab_user;
这条sql在我的环境下是稳定 的,返回的结果集条数每次都一样
另外这个解释我不太理解,我觉得这个不稳定并不符合预期
使用的测试语句:
select count(t.xaccount) as balance from t_org t2
,
(
select c.ab_user,a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a,f_ccs_apma c
where a.xaccount = c.account
)
t where
t.ab_user=t2.code and
t.last_card = 1
;
<a.xaccount, a.cdindex>的组合确实不唯一,但是窗口函数里已经指定了order by ,那么
select c.ab_user,a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex DESC) last_card
from f_ccs_card a,f_ccs_apma c
where a.xaccount = c.account
这个子查询的每条记录的last_card肯定是固定的,结果是一个稳定的结果集才符合预期 ,单独执行这个子查询,结果集也确实是固定 的
关联查询结果
t2是固定,t的结果集也是固定的,两个数据固定的表做关联查询,返回固定的结果集才符合预期
和其他数据库执行结果对比
在oracle里执行结果集稳定,在java的H2里执行结果集也稳定。
mysql8.0之后才支持窗口函数,我这没有环境所以没有测试,谁有环境的话可以帮忙测试一下,看看结果。
这个执行结果和传统数据库的执行结果有差异,并不符合预期
关于结果集不稳定,官方的文档
结果集不稳定 | PingCAP 文档中心
这个测试场景也不符合文档里说的所有情况。
我觉得这个场景下结果集稳定才符合预期 ,如果是个bug,希望能修复这个bug
如果不是bug,希望能找到不稳定的根本原因,更新到前边的官方文档里。
这个子查询输出的是
xaccount, cdindex, ab_user, last_card
这四列,而窗口函数本质上只是对 xaccount, cdindex 做了排序,所以 ab_user, last_card 的对应关系是不稳定的。例如有下面一组数据
xaccount, cdindex, ab_user
1 1 1
1 1 2
经过窗口函数之后,结果可能是
xaccount, cdindex, ab_user, last_card
1 1 1 1
1 1 1 2
也可能是
1 1 2 1
1 1 1 2
如果你在窗口函数里面除了 ORDER by a.cdindex DESC 再加上 order by ab_user 的话,应该结果就是稳定的了
1 个赞
另外我说的不稳定不是指结果集条数不一样,条数是一样的,但是结果本身是不一样的,具体说就是相同的 ab_user 对应的 last_card 是不一样(不稳定)的
确实,我用下边的语句测试了一下,确实结果本身不一样
select ab_user,last_card,count(*) from
(
select c.ab_user,a.xaccount,
row_number() OVER(PARTITION BY a.xaccount ORDER by a.cdindex desc
,c.ab_user DESC
) last_card
from f_ccs_card a,f_ccs_apma c
where a.xaccount = c.account
) t
group by ab_user,last_card
order by ab_user,last_card;
在oracle上执行的结果是不变的
经过测试窗口函数中的排序字段增加ab_user 后,结果集固定不变 了
是不是以后用窗口函数,所有的查询的字段都需要跟在order by 后边?类似mysql的 full group by 一样?
1 个赞
我觉得这就和 order by 语句一样吧,如果想得到稳定的结果,那结果集在order by的列上就不能有重复,如果没有主键,unique key之类的列保证唯一性的话,就需要把所有的列都放在order by后面。
1 个赞
system
(system)
关闭
2023 年3 月 19 日 05:45
20
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。