mysql查询多次执行结果稳定的不一致

🪐 TiDB 技术问题 性能调优
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：V4.0.8,V4.0.9
  • 【问题描述】：该问题在V3.0.3版本中不存在。

因为故障太离奇，我都不相信，所以录了视频。请看视频：
BUG现象.mp4_org_libx265_25_slower.zip.001 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.002 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.003 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.004 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.005 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.006 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.007 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.008 (4 MB)
BUG现象.mp4_org_libx265_25_slower.zip.009 (219.8 KB)

查询语句：

set @SESSION_ID22=0;
set @created_at=2147483647;
select date,lib,remark,
round(avg(vv)) as avgvv,
round(avg(if(is_first_day is true,vv,null))) as new_avgvv,
round(avg(if(is_first_day is false,vv,null))) as old_avgvv,
round(max(vv)) as maxvv,
round(max(if(is_first_day is true,vv,null))) as new_maxvv,
round(max(if(is_first_day is false,vv,null))) as old_maxvv,
round(min(vv)) as minvv,
round(min(if(is_first_day is true,vv,null))) as new_minvv,
round(min(if(is_first_day is false,vv,null))) as old_minvv,
round(avg(usedtime)/60,1) as avgusedtime,
round(avg(if(is_first_day is true,usedtime,null))/60,1) as new_avgusedtime,
round(avg(if(is_first_day is false,usedtime,null))/60,1) as old_avgusedtime,
round(max(usedtime)/60,1) as maxusedtime,
round(max(if(is_first_day is true,usedtime,null))/60,1) as new_maxusedtime,
round(max(if(is_first_day is false,usedtime,null))/60,1) as old_maxusedtime,
round(min(usedtime)/60,1) as minusedtime,
round(min(if(is_first_day is true,usedtime,null))/60,1) as new_minusedtime,
round(min(if(is_first_day is false,usedtime,null))/60,1) as old_minusedtime
from
(select date,lib,remark,ssid,is_first_day,max(created_at)-min(created_at) as usedtime,count(*) as vv from
(
select date,distinct_id2,lib,remark,created_at,
if(created_at-@created_at>1800 and @CONCAT_USER!=concat(date,distinct_id2,lib,remark),concat(condis,@SESSION_ID22:=@SESSION_ID22 +10),concat(condis,@SESSION_ID22:=@SESSION_ID22)) as ssid,
@created_at:=created_at,is_first_day
from
(select date,distinct_id2,lib,remark,
if(@CONCAT_USER!=concat(date,distinct_id2,lib,remark),@CONCAT_USER:=concat(date,distinct_id2,lib,remark),@CONCAT_USER:=@CONCAT_USER) as condis,
created_at,is_first_day
from
(SELECT
date,
IF
(fideo_v1_user.original_id IS NOT NULL AND fideo_v1_user.original_id != ‘’, fideo_v1_user.distinct_id, fideo_v1.distinct_id ) AS distinct_id2,
fideo_v1.lib as lib,
fideo_v1.remark as remark,
fideo_v1.created_at as created_at,
JSON_EXTRACT(all_json,‘$.“properties”.“$is_first_day”’) as is_first_day
FROM
fideo_v1
LEFT JOIN fideo_v1_user ON fideo_v1.distinct_id = fideo_v1_user.original_id
AND fideo_v1.lib = fideo_v1_user.lib
WHERE
fideo_v1.date >= ‘2021-01-10’ and fideo_v1.date <=‘2021-01-10’
AND fideo_v1.EVENT IN ( ‘$pageview’, ‘$AppViewScreen’, ‘$MPViewScreen’ )
and fideo_v1.lib in (‘Android’,‘MiniProgram’,‘js’,‘iOS’) and fideo_v1.remark in (‘online’,‘h5’,‘pc’,‘android_production’)
order by date,distinct_id2,fideo_v1.created_at)day0)ss00)pvssid
GROUP BY date,lib,remark,ssid,is_first_day)onlintime
GROUP BY date,lib,remark
order by lib,remark,date

执行正确结果的解释：

Sort_14 1.00 root events.fideo_v1.lib:asc, events.fideo_v1.remark:asc, events.fideo_v1.date:asc
└─Projection_16 1.00 root events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, round(Column#47)->Column#65, round(Column#48)->Column#66, round(Column#49)->Column#67, round(Column#50)->Column#68, round(Column#51)->Column#69, round(Column#52)->Column#70, round(Column#53)->Column#71, round(Column#54)->Column#72, round(Column#55)->Column#73, round(div(Column#56, 60), 1)->Column#74, round(div(Column#57, 60), 1)->Column#75, round(div(Column#58, 60), 1)->Column#76, round(div(cast(Column#59, decimal(20,0) BINARY), 60), 1)->Column#77, round(div(cast(Column#60, decimal(20,0) BINARY), 60), 1)->Column#78, round(div(cast(Column#61, decimal(20,0) BINARY), 60), 1)->Column#79, round(div(cast(Column#62, decimal(20,0) BINARY), 60), 1)->Column#80, round(div(cast(Column#63, decimal(20,0) BINARY), 60), 1)->Column#81, round(div(cast(Column#64, decimal(20,0) BINARY), 60), 1)->Column#82
└─HashAgg_19 1.00 root group by:Column#131, Column#132, Column#133, funcs:avg(Column#110)->Column#47, funcs:avg(Column#111)->Column#48, funcs:avg(Column#112)->Column#49, funcs:max(Column#113)->Column#50, funcs:max(Column#114)->Column#51, funcs:max(Column#115)->Column#52, funcs:min(Column#116)->Column#53, funcs:min(Column#117)->Column#54, funcs:min(Column#118)->Column#55, funcs:avg(Column#119)->Column#56, funcs:avg(Column#120)->Column#57, funcs:avg(Column#121)->Column#58, funcs:max(Column#122)->Column#59, funcs:max(Column#123)->Column#60, funcs:max(Column#124)->Column#61, funcs:min(Column#125)->Column#62, funcs:min(Column#126)->Column#63, funcs:min(Column#127)->Column#64, funcs:firstrow(Column#128)->events.fideo_v1.date, funcs:firstrow(Column#129)->events.fideo_v1.lib, funcs:firstrow(Column#130)->events.fideo_v1.remark
└─Projection_52 1.00 root cast(Column#45, decimal(65,4) BINARY)->Column#110, cast(if(istrue(cast(Column#39, double BINARY)), Column#45, ), decimal(65,4) BINARY)->Column#111, cast(if(isfalse(cast(Column#39, double BINARY)), Column#45, ), decimal(65,4) BINARY)->Column#112, Column#45, if(istrue(cast(Column#39, double BINARY)), Column#45, )->Column#114, if(isfalse(cast(Column#39, double BINARY)), Column#45, )->Column#115, Column#45, if(istrue(cast(Column#39, double BINARY)), Column#45, )->Column#117, if(isfalse(cast(Column#39, double BINARY)), Column#45, )->Column#118, cast(Column#46, decimal(65,4) BINARY)->Column#119, cast(if(istrue(cast(Column#39, double BINARY)), Column#46, ), decimal(65,4) BINARY)->Column#120, cast(if(isfalse(cast(Column#39, double BINARY)), Column#46, ), decimal(65,4) BINARY)->Column#121, Column#46, if(istrue(cast(Column#39, double BINARY)), Column#46, )->Column#123, if(isfalse(cast(Column#39, double BINARY)), Column#46, )->Column#124, Column#46, if(istrue(cast(Column#39, double BINARY)), Column#46, )->Column#126, if(isfalse(cast(Column#39, double BINARY)), Column#46, )->Column#127, events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark
└─Projection_20 1.00 root events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, Column#39, minus(Column#43, Column#44)->Column#46, Column#45
└─HashAgg_23 1.00 root group by:Column#39, Column#41, events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, funcs:max(events.fideo_v1.created_at)->Column#43, funcs:min(events.fideo_v1.created_at)->Column#44, funcs:count(1)->Column#45, funcs:firstrow(events.fideo_v1.date)->events.fideo_v1.date, funcs:firstrow(events.fideo_v1.lib)->events.fideo_v1.lib, funcs:firstrow(events.fideo_v1.remark)->events.fideo_v1.remark, funcs:firstrow(Column#39)->Column#39
└─Projection_24 0.00 root events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, events.fideo_v1.created_at, if(and(gt(minus(events.fideo_v1.created_at, getvar(created_at)), 1800), ne(getvar(concat_user), concat(cast(events.fideo_v1.date, var_string(10)), Column#38, events.fideo_v1.lib, events.fideo_v1.remark))), concat(Column#40, cast(setvar(session_id22, plus(getvar(session_id22), 10)), var_string(20))), concat(Column#40, cast(setvar(session_id22, getvar(session_id22)), var_string(20))))->Column#41, setvar(created_at, events.fideo_v1.created_at)->Column#42, Column#39
└─Projection_25 0.00 root events.fideo_v1.date, Column#38, events.fideo_v1.lib, events.fideo_v1.remark, if(ne(getvar(concat_user), concat(cast(events.fideo_v1.date, var_string(10)), Column#38, events.fideo_v1.lib, events.fideo_v1.remark)), setvar(concat_user, concat(cast(events.fideo_v1.date, var_string(10)), Column#38, events.fideo_v1.lib, events.fideo_v1.remark)), setvar(concat_user, getvar(concat_user)))->Column#40, events.fideo_v1.created_at, Column#39
└─Sort_26 0.00 root events.fideo_v1.date:asc, Column#38:asc, events.fideo_v1.created_at:asc
└─Projection_28 0.00 root events.fideo_v1.date, if(and(not(isnull(events.fideo_v1_user.original_id)), ne(events.fideo_v1_user.original_id, )), events.fideo_v1_user.distinct_id, events.fideo_v1.distinct_id)->Column#38, events.fideo_v1.lib, events.fideo_v1.remark, events.fideo_v1.created_at, json_extract(events.fideo_v1.all_json, $.“properties”.“$is_first_day”)->Column#39
└─IndexJoin_33 0.00 root left outer join, inner:IndexLookUp_32, outer key:events.fideo_v1.distinct_id, inner key:events.fideo_v1_user.original_id, equal cond:eq(events.fideo_v1.distinct_id, events.fideo_v1_user.original_id), eq(events.fideo_v1.lib, events.fideo_v1_user.lib)
├─IndexLookUp_44(Build) 0.00 root
│ ├─IndexRangeScan_41(Build) 0.00 cop[tikv] table:fideo_v1, index:event_remark_date(event, remark, date) range:[“$AppViewScreen” “android_production” 2021-01-10,“$AppViewScreen” “android_production” 2021-01-10], [“$AppViewScreen” “h5” 2021-01-10,“$AppViewScreen” “h5” 2021-01-10], [“$AppViewScreen” “online” 2021-01-10,“$AppViewScreen” “online” 2021-01-10], [“$AppViewScreen” “pc” 2021-01-10,“$AppViewScreen” “pc” 2021-01-10], [“$MPViewScreen” “android_production” 2021-01-10,“$MPViewScreen” “android_production” 2021-01-10], [“$MPViewScreen” “h5” 2021-01-10,“$MPViewScreen” “h5” 2021-01-10], [“$MPViewScreen” “online” 2021-01-10,“$MPViewScreen” “online” 2021-01-10], [“$MPViewScreen” “pc” 2021-01-10,“$MPViewScreen” “pc” 2021-01-10], [“$pageview” “android_production” 2021-01-10,“$pageview” “android_production” 2021-01-10], [“$pageview” “h5” 2021-01-10,“$pageview” “h5” 2021-01-10], [“$pageview” “online” 2021-01-10,“$pageview” “online” 2021-01-10], [“$pageview” “pc” 2021-01-10,“$pageview” “pc” 2021-01-10], keep order:false
│ └─Selection_43(Probe) 0.00 cop[tikv] in(events.fideo_v1.lib, “Android”, “MiniProgram”, “js”, “iOS”)
│ └─TableRowIDScan_42 0.00 cop[tikv] table:fideo_v1 keep order:false
└─IndexLookUp_32(Probe) 1628965.00 root
├─IndexRangeScan_29(Build) 1628965.00 cop[tikv] table:fideo_v1_user, index:original_id(original_id) range: decided by [eq(events.fideo_v1_user.original_id, events.fideo_v1.distinct_id)], keep order:false
└─Selection_31(Probe) 1628965.00 cop[tikv] in(events.fideo_v1_user.lib, “Android”, “MiniProgram”, “js”, “iOS”)
└─TableRowIDScan_30 1628965.00 cop[tikv] table:fideo_v1_user keep order:false

执行正确的结果：

2021-01-10 Android online 15 20 12 82 78 82 1 1 1 5.1 5.4 4.9 53.4 35.5 53.4 0.0 0.0 0.0
2021-01-10 MiniProgram android_production 11 9 12 58 16 58 1 2 1 3.0 1.0 3.6 29.6 2.2 29.6 0.0 0.0 0.0
2021-01-10 iOS online 15 18 14 245 245 163 1 1 1 7.5 10.1 6.6 625.1 625.1 104.5 0.0 0.0 0.0
2021-01-10 js h5 2 1 2 5 5 5 1 1 1 2.2 1.9 4.0 38.3 38.3 22.3 0.0 0.0 0.0
2021-01-10 js pc 4 4 4 108 108 20 1 1 1 20.1 23.1 3.5 769.8 769.8 23.3 0.0 0.0 0.0

执行错误的解释：

Sort_14 1.00 root events.fideo_v1.lib:asc, events.fideo_v1.remark:asc, events.fideo_v1.date:asc
└─Projection_16 1.00 root events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, round(Column#47)->Column#65, round(Column#48)->Column#66, round(Column#49)->Column#67, round(Column#50)->Column#68, round(Column#51)->Column#69, round(Column#52)->Column#70, round(Column#53)->Column#71, round(Column#54)->Column#72, round(Column#55)->Column#73, round(div(Column#56, 60), 1)->Column#74, round(div(Column#57, 60), 1)->Column#75, round(div(Column#58, 60), 1)->Column#76, round(div(cast(Column#59, decimal(20,0) BINARY), 60), 1)->Column#77, round(div(cast(Column#60, decimal(20,0) BINARY), 60), 1)->Column#78, round(div(cast(Column#61, decimal(20,0) BINARY), 60), 1)->Column#79, round(div(cast(Column#62, decimal(20,0) BINARY), 60), 1)->Column#80, round(div(cast(Column#63, decimal(20,0) BINARY), 60), 1)->Column#81, round(div(cast(Column#64, decimal(20,0) BINARY), 60), 1)->Column#82
└─HashAgg_19 1.00 root group by:Column#131, Column#132, Column#133, funcs:avg(Column#110)->Column#47, funcs:avg(Column#111)->Column#48, funcs:avg(Column#112)->Column#49, funcs:max(Column#113)->Column#50, funcs:max(Column#114)->Column#51, funcs:max(Column#115)->Column#52, funcs:min(Column#116)->Column#53, funcs:min(Column#117)->Column#54, funcs:min(Column#118)->Column#55, funcs:avg(Column#119)->Column#56, funcs:avg(Column#120)->Column#57, funcs:avg(Column#121)->Column#58, funcs:max(Column#122)->Column#59, funcs:max(Column#123)->Column#60, funcs:max(Column#124)->Column#61, funcs:min(Column#125)->Column#62, funcs:min(Column#126)->Column#63, funcs:min(Column#127)->Column#64, funcs:firstrow(Column#128)->events.fideo_v1.date, funcs:firstrow(Column#129)->events.fideo_v1.lib, funcs:firstrow(Column#130)->events.fideo_v1.remark
└─Projection_52 1.00 root cast(Column#45, decimal(65,4) BINARY)->Column#110, cast(if(istrue(cast(Column#39, double BINARY)), Column#45, ), decimal(65,4) BINARY)->Column#111, cast(if(isfalse(cast(Column#39, double BINARY)), Column#45, ), decimal(65,4) BINARY)->Column#112, Column#45, if(istrue(cast(Column#39, double BINARY)), Column#45, )->Column#114, if(isfalse(cast(Column#39, double BINARY)), Column#45, )->Column#115, Column#45, if(istrue(cast(Column#39, double BINARY)), Column#45, )->Column#117, if(isfalse(cast(Column#39, double BINARY)), Column#45, )->Column#118, cast(Column#46, decimal(65,4) BINARY)->Column#119, cast(if(istrue(cast(Column#39, double BINARY)), Column#46, ), decimal(65,4) BINARY)->Column#120, cast(if(isfalse(cast(Column#39, double BINARY)), Column#46, ), decimal(65,4) BINARY)->Column#121, Column#46, if(istrue(cast(Column#39, double BINARY)), Column#46, )->Column#123, if(isfalse(cast(Column#39, double BINARY)), Column#46, )->Column#124, Column#46, if(istrue(cast(Column#39, double BINARY)), Column#46, )->Column#126, if(isfalse(cast(Column#39, double BINARY)), Column#46, )->Column#127, events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark
└─Projection_20 1.00 root events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, Column#39, minus(Column#43, Column#44)->Column#46, Column#45
└─HashAgg_23 1.00 root group by:Column#39, Column#41, events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, funcs:max(events.fideo_v1.created_at)->Column#43, funcs:min(events.fideo_v1.created_at)->Column#44, funcs:count(1)->Column#45, funcs:firstrow(events.fideo_v1.date)->events.fideo_v1.date, funcs:firstrow(events.fideo_v1.lib)->events.fideo_v1.lib, funcs:firstrow(events.fideo_v1.remark)->events.fideo_v1.remark, funcs:firstrow(Column#39)->Column#39
└─Projection_24 0.00 root events.fideo_v1.date, events.fideo_v1.lib, events.fideo_v1.remark, events.fideo_v1.created_at, if(and(gt(minus(events.fideo_v1.created_at, getvar(created_at)), 1800), ne(getvar(concat_user), concat(cast(events.fideo_v1.date, var_string(10)), Column#38, events.fideo_v1.lib, events.fideo_v1.remark))), concat(Column#40, cast(setvar(session_id22, plus(getvar(session_id22), 10)), var_string(20))), concat(Column#40, cast(setvar(session_id22, getvar(session_id22)), var_string(20))))->Column#41, setvar(created_at, events.fideo_v1.created_at)->Column#42, Column#39
└─Projection_25 0.00 root events.fideo_v1.date, Column#38, events.fideo_v1.lib, events.fideo_v1.remark, if(ne(getvar(concat_user), concat(cast(events.fideo_v1.date, var_string(10)), Column#38, events.fideo_v1.lib, events.fideo_v1.remark)), setvar(concat_user, concat(cast(events.fideo_v1.date, var_string(10)), Column#38, events.fideo_v1.lib, events.fideo_v1.remark)), setvar(concat_user, getvar(concat_user)))->Column#40, events.fideo_v1.created_at, Column#39
└─Sort_26 0.00 root events.fideo_v1.date:asc, Column#38:asc, events.fideo_v1.created_at:asc
└─Projection_28 0.00 root events.fideo_v1.date, if(and(not(isnull(events.fideo_v1_user.original_id)), ne(events.fideo_v1_user.original_id, )), events.fideo_v1_user.distinct_id, events.fideo_v1.distinct_id)->Column#38, events.fideo_v1.lib, events.fideo_v1.remark, events.fideo_v1.created_at, json_extract(events.fideo_v1.all_json, $.“properties”.“$is_first_day”)->Column#39
└─IndexJoin_33 0.00 root left outer join, inner:IndexLookUp_32, outer key:events.fideo_v1.distinct_id, inner key:events.fideo_v1_user.original_id, equal cond:eq(events.fideo_v1.distinct_id, events.fideo_v1_user.original_id), eq(events.fideo_v1.lib, events.fideo_v1_user.lib)
├─IndexLookUp_44(Build) 0.00 root
│ ├─IndexRangeScan_41(Build) 0.00 cop[tikv] table:fideo_v1, index:event_remark_date(event, remark, date) range:[“$AppViewScreen” “android_production” 2021-01-10,“$AppViewScreen” “android_production” 2021-01-10], [“$AppViewScreen” “h5” 2021-01-10,“$AppViewScreen” “h5” 2021-01-10], [“$AppViewScreen” “online” 2021-01-10,“$AppViewScreen” “online” 2021-01-10], [“$AppViewScreen” “pc” 2021-01-10,“$AppViewScreen” “pc” 2021-01-10], [“$MPViewScreen” “android_production” 2021-01-10,“$MPViewScreen” “android_production” 2021-01-10], [“$MPViewScreen” “h5” 2021-01-10,“$MPViewScreen” “h5” 2021-01-10], [“$MPViewScreen” “online” 2021-01-10,“$MPViewScreen” “online” 2021-01-10], [“$MPViewScreen” “pc” 2021-01-10,“$MPViewScreen” “pc” 2021-01-10], [“$pageview” “android_production” 2021-01-10,“$pageview” “android_production” 2021-01-10], [“$pageview” “h5” 2021-01-10,“$pageview” “h5” 2021-01-10], [“$pageview” “online” 2021-01-10,“$pageview” “online” 2021-01-10], [“$pageview” “pc” 2021-01-10,“$pageview” “pc” 2021-01-10], keep order:false
│ └─Selection_43(Probe) 0.00 cop[tikv] in(events.fideo_v1.lib, “Android”, “MiniProgram”, “js”, “iOS”)
│ └─TableRowIDScan_42 0.00 cop[tikv] table:fideo_v1 keep order:false
└─IndexLookUp_32(Probe) 1628965.00 root
├─IndexRangeScan_29(Build) 1628965.00 cop[tikv] table:fideo_v1_user, index:original_id(original_id) range: decided by [eq(events.fideo_v1_user.original_id, events.fideo_v1.distinct_id)], keep order:false
└─Selection_31(Probe) 1628965.00 cop[tikv] in(events.fideo_v1_user.lib, “Android”, “MiniProgram”, “js”, “iOS”)
└─TableRowIDScan_30 1628965.00 cop[tikv] table:fideo_v1_user keep order:false

执行错误的结果：

2021-01-10 Android online 564 490 637 637 490 637 490 490 637 1371.4 1367.1 1375.7 1375.7 1367.1 1375.7 1367.1 1367.1 1375.7
2021-01-10 MiniProgram android_production 138 53 222 222 53 222 53 53 222 1167.5 949.9 1385.1 1385.1 949.9 1385.1 949.9 949.9 1385.1
2021-01-10 iOS online 6740 4257 9222 9222 4257 9222 4257 4257 9222 1434.9 1430.0 1439.8 1439.8 1430.0 1439.8 1430.0 1430.0 1439.8
2021-01-10 js h5 35 52 17 52 52 17 17 52 17 1242.4 1427.4 1057.4 1427.4 1427.4 1057.4 1057.4 1427.4 1057.4
2021-01-10 js pc 241 411 71 411 411 71 71 411 71 1217.1 1439.2 994.9 1439.2 1439.2 994.9 994.9 1439.2 994.9

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出打印结果，请务必全选并复制粘贴上传。

2

麻烦反馈下相关的表结构，多谢

3

主表

CREATE TABLE fideo_v1 (
track_id bigint(17) DEFAULT NULL,
distinct_id varchar(64) DEFAULT NULL,
lib varchar(255) DEFAULT NULL,
event varchar(255) DEFAULT NULL,
type varchar(255) DEFAULT NULL,
all_json json DEFAULT NULL,
host varchar(255) DEFAULT NULL,
user_agent varchar(2048) DEFAULT NULL,
ua_platform varchar(1024) DEFAULT NULL,
ua_browser varchar(1024) DEFAULT NULL,
ua_version varchar(1024) DEFAULT NULL,
ua_language varchar(1024) DEFAULT NULL,
connection varchar(255) DEFAULT NULL,
pragma varchar(255) DEFAULT NULL,
cache_control varchar(255) DEFAULT NULL,
accept varchar(255) DEFAULT NULL,
accept_encoding varchar(255) DEFAULT NULL,
accept_language varchar(255) DEFAULT NULL,
ip varchar(512) DEFAULT NULL,
ip_city json DEFAULT NULL,
ip_asn json DEFAULT NULL,
url text DEFAULT NULL,
referrer varchar(2048) DEFAULT NULL,
remark varchar(255) DEFAULT NULL,
created_at int(11) DEFAULT NULL,
date date DEFAULT NULL,
hour int(2) DEFAULT NULL,
KEY distinct_id (distinct_id),
KEY event (event),
KEY date (date),
KEY date_hour (date,hour),
KEY event_date (event,date),
KEY event_remark_date (event,remark,date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

关联表

CREATE TABLE fideo_v1_user (
distinct_id varchar(255) NOT NULL,
lib varchar(255) NOT NULL,
map_id varchar(255) NOT NULL,
original_id varchar(255) NOT NULL,
user_id varchar(255) DEFAULT NULL,
all_user_profile json DEFAULT NULL,
created_at int(11) DEFAULT NULL,
updated_at int(11) DEFAULT NULL,
PRIMARY KEY (distinct_id,lib,map_id,original_id),
KEY distinct_id (distinct_id),
KEY map_id (map_id),
KEY original_id (original_id),
KEY distinct_id_lib (distinct_id,lib)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

4

问题已经在分析中，有进展会尽快反馈，多谢

5

可以的话提供一下以下的信息用于复现：

  1. 两个版本 explain analyze 的结果 （复制到文本文档上传）
  2. 两个表用于复现的数据，敏感的字段可以脱敏。(能够正常关联复现就可以)
6

已经可以准确复现执行正确和执行错误的步骤了（在下一个回复里说明了）

不过这个查询里应该触发了两个BUG，一个是这个，是V3的，你们在V4里已经修复了。还有一个就是我在下一个回复里提到的那个了。

在V3.0.3时，这个条件的结果是错误的

if(created_at-@created_at>1800 and @CONCAT_USER!=concat(date,distinct_id2,lib,remark),concat(condis,@SESSION_ID22:=@SESSION_ID22 +10),concat(condis,@SESSION_ID22:=@SESSION_ID22)) as ssid,
@created_at:=created_at,is_first_day

无论如何，SESSION_ID22都会被+10。所以调整了语句为，保证session情况时ssid保持一致：

if(created_at-@created_at>1800 and @CONCAT_USER!=concat(date,distinct_id2,lib,remark),concat(condis,@SESSION_ID22:=@SESSION_ID22 +10),concat(condis,@SESSION_ID22:=@SESSION_ID22-10)) as ssid,
@created_at:=created_at,is_first_day

才解决了问题。

但是升级到V4.0.8之后，发现是不需要这个 -10的，也不会每次必增长了，已经把同一个结果里两个判断赋值有问题的情况解决了。但是引入了一个新的问题，就是发挥不稳定，有的查询就正常-10了。有的查询就没有正常-10。造成现有问题。

我也在调整查询语句，争取绕开这个问题，输出稳定的正确结果。

用于复现的数据我想想怎么搞出来，不是敏感的问题，是量大。
错误的explain.txt (9.9 KB)
正确的explain.txt (10.0 KB)

7

我成功绕开BUG了。
原查询语句第30行

if(@CONCAT_USER!=concat(date,distinct_id2,lib,remark),@CONCAT_USER:=concat(date,distinct_id2,lib,remark),@CONCAT_USER:=@CONCAT_USER) as condis,

改为

if(@CONCAT_USER=concat(date,distinct_id2,lib,remark),@CONCAT_USER:=@CONCAT_USER,@CONCAT_USER:=concat(date,distinct_id2,lib,remark)) as condis,

即可稳定出现正确结果。

一旦执行结果正确，改回返回错误结果的语句，再执行。结果依然正确。

但是一旦链接关闭，重新开始执行，使用返回错误结果的语句执行出来的结果一定是错的。

问题应该就是在变量的!=判断上有问题。该问题在v3.0.3上不存在。在v4.0.8和v4.0.9上存在

8

:+1:

不过这种在查询里迭代更新/读取 user variable 的用法 MySQL 是不推荐的，并且这个行为是在文档里明确说过 undefined 的。

摘自：MySQL :: MySQL 8.0 Reference Manual :: 9.4 User-Defined Variables

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

1 个赞
9

收到。我看看怎么能替代不用这种用法吧。
大佬太厉害了，mysql的文档这么熟悉:+1::+1::+1::+1::+1:服气

10

:joy: 这个主要是其他同事的帮助，哈哈哈

11

可以考虑使用 window function 替代
SQL 比较复杂:sweat_smile:尝试理解了一下里面用户变量的用法，可以参考一下以下的方法来改写
@created_at 应该是用来计算每个 created_at 和上一行的 created_at 的差，这个的用法应该可以借助 LAG()，写成 created_at - lag(created_at) over ()
@concat_user 看起来是想拿来判断这一行是不是这个 concat_user 的第一行数据，这个应该可以借助 ROW_NUMBER()，写成类似 row_number() over (partition by date, distinct_id2, lib, remark) = 1 来判断

12

收到。谢谢大佬指点。我试一下，有结果会在这里反馈的。

这两个变量都是用来判断是否同一个 session 的。如果同一个data,distinct_id,lib,remark且created_at间隔不超过1800就认为一个同一个session，否则认为是不同的session。

13

:+1:

14

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。