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

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

【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
每次查询后获得的条数不一样,这可能是哪里有问题


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

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

子查询数量是一致的

  1. 麻烦提供一下这个 SQL 不同结果的 explain analyze 的结果。
  2. 另外想确认一下 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 结果是否恒定。

您好,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
  1. 请问一下这个是连续跑量次必然重现的问题吗?还是说是不稳定复现。
  2. 辛苦提供下复现的 explain analyze 的结果。不是 explain 的结果。另外辛苦把结果导出到 TXT 吧。这样方便分析。

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

老师,这个问题还在看吗

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

您好,在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)

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

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

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

  2. 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;

  3. 由于你的group by 不是标准的,我修改了sql_mode,目前为:

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

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

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

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;

试下这个是否稳定呢?

@zy-xiaoyuyu

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

[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\n\t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:93\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:679\nbytes.(*Buffer).Truncate\n\t/usr/local/go/src/bytes/buffer.go:89\ngithub.com/pingcap/tidb/executor/aggfuncs.(*topNRows).concat\n\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\n\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\n\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\n\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\n\t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/executor.go:253\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).fetchProbeSideChunks\n\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\n\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\n\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\nleft JOIN ( SELECT place_id, GROUP_CONCAT( appname order by appname SEPARATOR ‘’ ) appname, \r\nGROUP_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\nWHERE 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\n\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\n\t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:88\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:679\nbytes.(*Buffer).Truncate\n\t/usr/local/go/src/bytes/buffer.go:89\ngithub.com/pingcap/tidb/executor/aggfuncs.(*topNRows).concat\n\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\n\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\n\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\n\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\n\t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/executor/executor.go:253\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).fetchProbeSideChunks\n\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\n\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\n\t/home/jenkins/agent/workspace/tidb_v4.0.8/go/src/github.com/pingcap/tidb/util/misc.go:96\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357”]

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;

试下这个呢?