sql查询，每次得到的条数不稳定

zy-xiaoyuyu · 2021 年2 月 23 日 08:54

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【TiDB 版本】v4.0.8

【问题描述】通过navicate连接tidb，执行查询sql
select *
from ex_report_place_summary_catch rps
left JOIN (
SELECT
place_id,
GROUP_CONCAT( appname SEPARATOR ’ ’ ) appname,
GROUP_CONCAT( appid SEPARATOR ’ ’ ) appid
FROM
ex_places_info
WHERE
appid <> ‘’
GROUP BY
place_id
) info ON rps.place_id = info.place_id
WHERE
1
AND s_date >= ‘2021-02-01 00:00:00’
AND s_date <= ‘2021-02-01 23:59:59’
group by appid
每次查询后获得的条数不一样，这可能是哪里有问题

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

yilong · 2021 年2 月 23 日 10:05

麻烦先测试下子查询每次一致吗？
SELECT
place_id,
GROUP_CONCAT( appname SEPARATOR ’ ’ ) appname,
GROUP_CONCAT( appid SEPARATOR ’ ’ ) appid
FROM
ex_places_info
WHERE
appid <> ‘’
GROUP BY
place_id

zy-xiaoyuyu · 2021 年2 月 23 日 10:17

子查询数量是一致的

QBin · 2021 年2 月 24 日 02:16

麻烦提供一下这个 SQL 不同结果的 explain analyze 的结果。
另外想确认一下 ex_report_place_summary_catch 这个表 s_date >= ‘2021-02-01 00:00:00’
AND s_date <= ‘2021-02-01 23:59:59’ 这个条件下的数据是不会进行增删的是吧？也看看
select count(*) from ex_report_place_summary_catch WHERE 1 AND s_date >= ‘2021-02-01 00:00:00’ AND s_date <= ‘2021-02-01 23:59:59’ 这个 SQL 结果是否恒定。

zy-xiaoyuyu · 2021 年2 月 24 日 02:44

您好，select count(*) from ex_report_place_summary_catch WHERE 1 AND s_date >= ‘2021-02-01 00:00:00’ AND s_date <= ‘2021-02-01 23:59:59’ 数量是固定的

HashAgg_13	3.04	root		group by:Column#78, funcs:firstrow(ex_public.ex_report_place_summary_catch.place_id)->ex_public.ex_report_place_summary_catch.place_id, funcs:firstrow(ex_public.ex_report_place_summary_catch.place_name)->ex_public.ex_report_place_summary_catch.place_name, funcs:firstrow(ex_public.ex_report_place_summary_catch.s_date)->ex_public.ex_report_place_summary_catch.s_date, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_show)->ex_public.ex_report_place_summary_catch.v_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cshow)->ex_public.ex_report_place_summary_catch.v_cshow, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_show_ip)->ex_public.ex_report_place_summary_catch.v_show_ip, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cshow_ip)->ex_public.ex_report_place_summary_catch.v_cshow_ip, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_show_user)->ex_public.ex_report_place_summary_catch.v_show_user, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cshow_user)->ex_public.ex_report_place_summary_catch.v_cshow_user, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_nosuc_market_show)->ex_public.ex_report_place_summary_catch.v_nosuc_market_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_nosuc_jy_show)->ex_public.ex_report_place_summary_catch.v_nosuc_jy_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_nosuc_partner_show)->ex_public.ex_report_place_summary_catch.v_nosuc_partner_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_market_show)->ex_public.ex_report_place_summary_catch.v_p_market_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_market_show)->ex_public.ex_report_place_summary_catch.v_market_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cmarket_show)->ex_public.ex_report_place_summary_catch.v_cmarket_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_click)->ex_public.ex_report_place_summary_catch.v_p_click, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click)->ex_public.ex_report_place_summary_catch.v_click, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click_u)->ex_public.ex_report_place_summary_catch.v_click_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.partner_total_price)->ex_public.ex_report_place_summary_catch.partner_total_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.total_price)->ex_public.ex_report_place_summary_catch.total_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.ctotal_price)->ex_public.ex_report_place_summary_catch.ctotal_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.create_id)->ex_public.ex_report_place_summary_catch.create_id, funcs:firstrow(ex_public.ex_report_place_summary_catch.created)->ex_public.ex_report_place_summary_catch.created, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_price)->ex_public.ex_report_place_summary_catch.v_jy_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_show)->ex_public.ex_report_place_summary_catch.v_jy_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_click)->ex_public.ex_report_place_summary_catch.v_jy_click, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_profit)->ex_public.ex_report_place_summary_catch.v_profit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_subprofit)->ex_public.ex_report_place_summary_catch.v_subprofit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_profit)->ex_public.ex_report_place_summary_catch.v_jy_profit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_ctmarket_show)->ex_public.ex_report_place_summary_catch.v_ctmarket_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_ctprice)->ex_public.ex_report_place_summary_catch.v_ctprice, funcs:firstrow(ex_public.ex_report_place_summary_catch.partner_total_price_raw)->ex_public.ex_report_place_summary_catch.partner_total_price_raw, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_market_show_raw)->ex_public.ex_report_place_summary_catch.v_p_market_show_raw, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_ad_show)->ex_public.ex_report_place_summary_catch.v_ad_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_c_ad_show)->ex_public.ex_report_place_summary_catch.v_c_ad_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_cshow)->ex_public.ex_report_place_summary_catch.v_jy_cshow, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_cprice)->ex_public.ex_report_place_summary_catch.v_jy_cprice, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_cprofit)->ex_public.ex_report_place_summary_catch.v_jy_cprofit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_csubprofit)->ex_public.ex_report_place_summary_catch.v_csubprofit, funcs:firstrow(ex_public.ex_report_place_summary_catch.dealid)->ex_public.ex_report_place_summary_catch.dealid, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_parshowsuc)->ex_public.ex_report_place_summary_catch.v_jy_parshowsuc, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_dpcm)->ex_public.ex_report_place_summary_catch.v_dpcm, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_dpcm_u)->ex_public.ex_report_place_summary_catch.v_dpcm_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_dpcm)->ex_public.ex_report_place_summary_catch.v_jy_dpcm, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_dpcm_u)->ex_public.ex_report_place_summary_catch.v_jy_dpcm_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click_dp)->ex_public.ex_report_place_summary_catch.v_click_dp, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click_dp_u)->ex_public.ex_report_place_summary_catch.v_click_dp_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_click_dp)->ex_public.ex_report_place_summary_catch.v_jy_click_dp, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_click_dp_u)->ex_public.ex_report_place_summary_catch.v_jy_click_dp_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_sdown_u)->ex_public.ex_report_place_summary_catch.v_sdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_sdown_u)->ex_public.ex_report_place_summary_catch.v_jy_sdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_sdown_u)->ex_public.ex_report_place_summary_catch.v_p_sdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_fdown_u)->ex_public.ex_report_place_summary_catch.v_fdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_fdown_u)->ex_public.ex_report_place_summary_catch.v_jy_fdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_fdown_u)->ex_public.ex_report_place_summary_catch.v_p_fdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_sinstall_u)->ex_public.ex_report_place_summary_catch.v_sinstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_sinstall_u)->ex_public.ex_report_place_summary_catch.v_jy_sinstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_sinstall_u)->ex_public.ex_report_place_summary_catch.v_p_sinstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_finstall_u)->ex_public.ex_report_place_summary_catch.v_finstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_finstall_u)->ex_public.ex_report_place_summary_catch.v_jy_finstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_finstall_u)->ex_public.ex_report_place_summary_catch.v_p_finstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_actlve_u)->ex_public.ex_report_place_summary_catch.v_actlve_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_actlve_u)->ex_public.ex_report_place_summary_catch.v_jy_actlve_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_actlve_u)->ex_public.ex_report_place_summary_catch.v_p_actlve_u, funcs:firstrow(ex_public.ex_places_info.place_id)->ex_public.ex_places_info.place_id, funcs:firstrow(Column#77)->Column#77, funcs:firstrow(Column#78)->Column#78
└─HashJoin_16	3.04	root		left outer join, equal:[eq(ex_public.ex_report_place_summary_catch.place_id, ex_public.ex_places_info.place_id)]
├─IndexLookUp_34(Build)	3.04	root
│ ├─IndexRangeScan_32(Build)	3.04	cop[tikv]	table:rps, index:s_date_place_id(s_date, place_id, dealid)	range:[2021-02-01,2021-02-01], keep order:false
│ └─TableRowIDScan_33(Probe)	3.04	cop[tikv]	table:rps	keep order:false
└─HashAgg_35(Probe)	222696.39	root		group by:ex_public.ex_places_info.place_id, funcs:group_concat(ex_public.ex_places_info.appname separator " ")->Column#77, funcs:group_concat(ex_public.ex_places_info.appid separator " ")->Column#78, funcs:firstrow(ex_public.ex_places_info.place_id)->ex_public.ex_places_info.place_id
└─TableReader_42	292104.00	root		data:Selection_41
└─Selection_41	292104.00	cop[tikv]		ne(ex_public.ex_places_info.appid, “”)
└─TableFullScan_40	292104.00	cop[tikv]	table:ex_places_info	keep order:false

第二次------------------------------------------------------------------------------------------------------------

HashAgg_13	3.04	root		group by:Column#78, funcs:firstrow(ex_public.ex_report_place_summary_catch.place_id)->ex_public.ex_report_place_summary_catch.place_id, funcs:firstrow(ex_public.ex_report_place_summary_catch.place_name)->ex_public.ex_report_place_summary_catch.place_name, funcs:firstrow(ex_public.ex_report_place_summary_catch.s_date)->ex_public.ex_report_place_summary_catch.s_date, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_show)->ex_public.ex_report_place_summary_catch.v_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cshow)->ex_public.ex_report_place_summary_catch.v_cshow, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_show_ip)->ex_public.ex_report_place_summary_catch.v_show_ip, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cshow_ip)->ex_public.ex_report_place_summary_catch.v_cshow_ip, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_show_user)->ex_public.ex_report_place_summary_catch.v_show_user, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cshow_user)->ex_public.ex_report_place_summary_catch.v_cshow_user, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_nosuc_market_show)->ex_public.ex_report_place_summary_catch.v_nosuc_market_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_nosuc_jy_show)->ex_public.ex_report_place_summary_catch.v_nosuc_jy_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_nosuc_partner_show)->ex_public.ex_report_place_summary_catch.v_nosuc_partner_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_market_show)->ex_public.ex_report_place_summary_catch.v_p_market_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_market_show)->ex_public.ex_report_place_summary_catch.v_market_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_cmarket_show)->ex_public.ex_report_place_summary_catch.v_cmarket_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_click)->ex_public.ex_report_place_summary_catch.v_p_click, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click)->ex_public.ex_report_place_summary_catch.v_click, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click_u)->ex_public.ex_report_place_summary_catch.v_click_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.partner_total_price)->ex_public.ex_report_place_summary_catch.partner_total_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.total_price)->ex_public.ex_report_place_summary_catch.total_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.ctotal_price)->ex_public.ex_report_place_summary_catch.ctotal_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.create_id)->ex_public.ex_report_place_summary_catch.create_id, funcs:firstrow(ex_public.ex_report_place_summary_catch.created)->ex_public.ex_report_place_summary_catch.created, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_price)->ex_public.ex_report_place_summary_catch.v_jy_price, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_show)->ex_public.ex_report_place_summary_catch.v_jy_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_click)->ex_public.ex_report_place_summary_catch.v_jy_click, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_profit)->ex_public.ex_report_place_summary_catch.v_profit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_subprofit)->ex_public.ex_report_place_summary_catch.v_subprofit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_profit)->ex_public.ex_report_place_summary_catch.v_jy_profit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_ctmarket_show)->ex_public.ex_report_place_summary_catch.v_ctmarket_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_ctprice)->ex_public.ex_report_place_summary_catch.v_ctprice, funcs:firstrow(ex_public.ex_report_place_summary_catch.partner_total_price_raw)->ex_public.ex_report_place_summary_catch.partner_total_price_raw, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_market_show_raw)->ex_public.ex_report_place_summary_catch.v_p_market_show_raw, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_ad_show)->ex_public.ex_report_place_summary_catch.v_ad_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_c_ad_show)->ex_public.ex_report_place_summary_catch.v_c_ad_show, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_cshow)->ex_public.ex_report_place_summary_catch.v_jy_cshow, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_cprice)->ex_public.ex_report_place_summary_catch.v_jy_cprice, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_cprofit)->ex_public.ex_report_place_summary_catch.v_jy_cprofit, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_csubprofit)->ex_public.ex_report_place_summary_catch.v_csubprofit, funcs:firstrow(ex_public.ex_report_place_summary_catch.dealid)->ex_public.ex_report_place_summary_catch.dealid, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_parshowsuc)->ex_public.ex_report_place_summary_catch.v_jy_parshowsuc, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_dpcm)->ex_public.ex_report_place_summary_catch.v_dpcm, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_dpcm_u)->ex_public.ex_report_place_summary_catch.v_dpcm_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_dpcm)->ex_public.ex_report_place_summary_catch.v_jy_dpcm, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_dpcm_u)->ex_public.ex_report_place_summary_catch.v_jy_dpcm_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click_dp)->ex_public.ex_report_place_summary_catch.v_click_dp, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_click_dp_u)->ex_public.ex_report_place_summary_catch.v_click_dp_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_click_dp)->ex_public.ex_report_place_summary_catch.v_jy_click_dp, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_click_dp_u)->ex_public.ex_report_place_summary_catch.v_jy_click_dp_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_sdown_u)->ex_public.ex_report_place_summary_catch.v_sdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_sdown_u)->ex_public.ex_report_place_summary_catch.v_jy_sdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_sdown_u)->ex_public.ex_report_place_summary_catch.v_p_sdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_fdown_u)->ex_public.ex_report_place_summary_catch.v_fdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_fdown_u)->ex_public.ex_report_place_summary_catch.v_jy_fdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_fdown_u)->ex_public.ex_report_place_summary_catch.v_p_fdown_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_sinstall_u)->ex_public.ex_report_place_summary_catch.v_sinstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_sinstall_u)->ex_public.ex_report_place_summary_catch.v_jy_sinstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_sinstall_u)->ex_public.ex_report_place_summary_catch.v_p_sinstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_finstall_u)->ex_public.ex_report_place_summary_catch.v_finstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_finstall_u)->ex_public.ex_report_place_summary_catch.v_jy_finstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_finstall_u)->ex_public.ex_report_place_summary_catch.v_p_finstall_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_actlve_u)->ex_public.ex_report_place_summary_catch.v_actlve_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_jy_actlve_u)->ex_public.ex_report_place_summary_catch.v_jy_actlve_u, funcs:firstrow(ex_public.ex_report_place_summary_catch.v_p_actlve_u)->ex_public.ex_report_place_summary_catch.v_p_actlve_u, funcs:firstrow(ex_public.ex_places_info.place_id)->ex_public.ex_places_info.place_id, funcs:firstrow(Column#77)->Column#77, funcs:firstrow(Column#78)->Column#78
└─HashJoin_16	3.04	root		left outer join, equal:[eq(ex_public.ex_report_place_summary_catch.place_id, ex_public.ex_places_info.place_id)]
├─IndexLookUp_34(Build)	3.04	root
│ ├─IndexRangeScan_32(Build)	3.04	cop[tikv]	table:rps, index:s_date_place_id(s_date, place_id, dealid)	range:[2021-02-01,2021-02-01], keep order:false
│ └─TableRowIDScan_33(Probe)	3.04	cop[tikv]	table:rps	keep order:false
└─HashAgg_35(Probe)	222696.39	root		group by:ex_public.ex_places_info.place_id, funcs:group_concat(ex_public.ex_places_info.appname separator " ")->Column#77, funcs:group_concat(ex_public.ex_places_info.appid separator " ")->Column#78, funcs:firstrow(ex_public.ex_places_info.place_id)->ex_public.ex_places_info.place_id
└─TableReader_42	292104.00	root		data:Selection_41
└─Selection_41	292104.00	cop[tikv]		ne(ex_public.ex_places_info.appid, “”)
└─TableFullScan_40	292104.00	cop[tikv]	table:ex_places_info	keep order:false

QBin · 2021 年2 月 24 日 06:47

请问一下这个是连续跑量次必然重现的问题吗？还是说是不稳定复现。
辛苦提供下复现的 explain analyze 的结果。不是 explain 的结果。另外辛苦把结果导出到 TXT 吧。这样方便分析。

zy-xiaoyuyu · 2021 年2 月 24 日 07:06

是这个查询必然出现的问题explain_analy.txt (19.9 KB)
我上传了analy,您看看

zy-xiaoyuyu · 2021 年2 月 25 日 09:45

老师，这个问题还在看吗

yilong · 2021 年2 月 25 日 11:57

这个 sql 之前在 mysql 运行过吗？是否稳定？
每次查询的结果都互相不一样吗？这个是测试环境还是正式环境？是否方便发一下建表语句和导入数据？我们复现下问题
或者你可以把每次的查询结果记录到一个临时表，看看两次查询结果有哪些记录不一样。

zy-xiaoyuyu · 2021 年2 月 26 日 02:35

您好，在mysql中查询时稳定的，这个是正式环境，
2.xlsx (380.2 KB) 3.xlsx (379.2 KB)

数据及子查询数据
https://file.51tuiyi.com/index.php/s/B3qaNlhrmJKI4Xi 123123
https://file.51tuiyi.com/index.php/s/sTL1cX1pI1amLf2 123123

这个sql查询的表是由mysql 通过 dm同步到tidb上面的，和这个有关系吗，我在mysql上面查询固定的是1406条，但是在tidb上面查，一直都会变，然后我比较在tidb上面的查询差异，拿到差异的place_id，然后再去mysql的查询结果里面查询，有的可以查到，有的就查询不到。mysql.xlsx (364.0 KB)

yilong · 2021 年2 月 26 日 06:29

抱歉，ex_report_place_summary_catch 和 ex_places_info 的表结构我没有找到，有发吗？

zy-xiaoyuyu · 2021 年2 月 26 日 06:45

建表sql.txt (5.0 KB)
抱歉，忘记了，上传了，您看看

yilong · 2021 年2 月 26 日 08:50

我测试了下，将你的zichaxun.sql直接放到一张新建表，再替换sql，查询结果保存不变都是1419行

image849×291 8.43 KB
sql替换为：
select *
from ex_report_place_summary_catch rps
left JOIN zcx info ON rps.place_id = info.place_id
WHERE
1
AND s_date >= ‘2021-02-01 00:00:00’
AND s_date <= ‘2021-02-01 23:59:59’
group by appid;
由于你的group by 不是标准的，我修改了sql_mode,目前为：

image1546×192 6.32 KB

4.我由于导入过程中有一些报错，可能丢失几条数据，这个也不影响之后查询的稳定性。

麻烦先确认下您的 sql_mode 是什么？这里看看是否有不同

zy-xiaoyuyu · 2021 年2 月 26 日 09:03

您好，我的sql_mode 是这样的 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

XuHuaiyu-PingCAP · 2021 年2 月 26 日 09:36

select * from ex_report_place_summary_catch rps left JOIN ( SELECT place_id, GROUP_CONCAT( appname order by appname SEPARATOR ‘’ ) appname, GROUP_CONCAT( appid order by appid SEPARATOR ‘’ ) appid FROM ex_places_info WHERE appid <> ‘’ GROUP BY place_id ) info ON rps.place_id = info.place_id WHERE 1 AND s_date >= ‘2021-02-01 00:00:00’ AND s_date <= ‘2021-02-01 23:59:59’ group by appid;

试下这个是否稳定呢？

XuHuaiyu-PingCAP · 2021 年2 月 26 日 09:38

@zy-xiaoyuyu

zy-xiaoyuyu · 2021 年3 月 1 日 02:00

XuHuaiyu-PingCAP · 2021 年3 月 1 日 02:23

这个报错日志能发下我吗？
在 tidb.log 中

zy-xiaoyuyu · 2021 年3 月 1 日 02:30

[2021/03/01 10:29:42.359 +08:00] [ERROR] [misc.go:91] [“panic in the recoverable goroutine”] [r="“bytes.Buffer: truncation out of range”"] [“stack trace”=“github.com/pingcap/tidb/util.WithRecovery.func1\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:93\ runtime.gopanic\ \t/usr/local/go/src/runtime/panic.go:679\ bytes.(*Buffer).Truncate\ \t/usr/local/go/src/bytes/buffer.go:89\ngithub.com/pingcap/tidb/executor/aggfuncs.(*topNRows).concat\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_group_concat.go:322\ngithub.com/pingcap/tidb/executor/aggfuncs.(*groupConcatOrder).AppendFinalResult2Chunk\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_group_concat.go:341\ngithub.com/pingcap/tidb/executor.(*HashAggExec).unparallelExec\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggregate.go:748\ngithub.com/pingcap/tidb/executor.(*HashAggExec).Next\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggregate.go:598\ngithub.com/pingcap/tidb/executor.Next\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/executor.go:253\ github.com/pingcap/tidb/executor.(*HashJoinExec).fetchProbeSideChunks\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/join.go:209\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).fetchAndProbeHashTable.func1\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/join.go:322\ngithub.com/pingcap/tidb/util.WithRecovery\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:96”]
[2021/03/01 10:29:42.368 +08:00] [INFO] [conn.go:787] [“command dispatched failed”] [conn=3643] [connInfo=“id:3643, addr:106.121.159.169:61747 status:10, collation:latin1_swedish_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“select * from ex_report_place_summary_catch rps \r\ left JOIN ( SELECT place_id, GROUP_CONCAT( appname order by appname SEPARATOR ‘’ ) appname, \r\ GROUP_CONCAT( appid order by appid SEPARATOR ‘’ ) appid FROM ex_places_info WHERE appid <> ‘’ GROUP BY place_id )info ON rps.place_id = info.place_id \r\ WHERE 1 AND s_date >= ‘2021-02-01 00:00:00’ AND s_date <= ‘2021-02-01 23:59:59’ group by appid;”] [txn_mode=PESSIMISTIC] [err=“bytes.Buffer: truncation out of range\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).handleProbeSideFetcherPanic\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/join.go:348\ngithub.com/pingcap/tidb/util.WithRecovery.func1\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:88\ runtime.gopanic\ \t/usr/local/go/src/runtime/panic.go:679\ bytes.(*Buffer).Truncate\ \t/usr/local/go/src/bytes/buffer.go:89\ngithub.com/pingcap/tidb/executor/aggfuncs.(*topNRows).concat\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_group_concat.go:322\ngithub.com/pingcap/tidb/executor/aggfuncs.(*groupConcatOrder).AppendFinalResult2Chunk\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_group_concat.go:341\ngithub.com/pingcap/tidb/executor.(*HashAggExec).unparallelExec\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggregate.go:748\ngithub.com/pingcap/tidb/executor.(*HashAggExec).Next\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/aggregate.go:598\ngithub.com/pingcap/tidb/executor.Next\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/executor.go:253\ github.com/pingcap/tidb/executor.(*HashJoinExec).fetchProbeSideChunks\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/join.go:209\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).fetchAndProbeHashTable.func1\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/join.go:322\ngithub.com/pingcap/tidb/util.WithRecovery\ \t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:96\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357”]

XuHuaiyu-PingCAP · 2021 年3 月 1 日 03:38

select * from ex_report_place_summary_catch rps left JOIN (SELECT place_id, GROUP_CONCAT( appname SEPARATOR ‘’ ) appname, GROUP_CONCAT( appid SEPARATOR ‘’ ) appid FROM ( select place_id, appname, appid from ex_places_info WHERE appid <> ‘’ order by place_id, appname, appid) t GROUP BY place_id ) info ON rps.place_id = info.place_id WHERE 1 AND s_date >= ‘2021-02-01 00:00:00’ AND s_date <= ‘2021-02-01 23:59:59’ group by appid;

试下这个呢？