【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
运行SQL,Tiflash 内存溢出报错，但是监控显示TiFlash内存正常，而且再次运行同样的SQL,又会成功
报错如下：
DB::Exception: Code: 0, e.displayText() = DB::Exception: Memory limit (total) exceeded caused by 'out of memory quota for data computing' : would use 226.57 GiB for data computing (attempt to allocate chunk of 1409120 bytes), limit of memory for data computing
正常情况内存占用如下，溢出时候内存没有变化
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
相同的SQL,一会报内存溢出，一会正常，且监控显示内存始终正常，从哪能看到内存溢出的根因吗
看看执行计划内存在哪个表被使用了， 试着优化下SQL呢？
SQL已经不知道怎么优化了
能贴出执行计划吗？
explain执行计划
Projection_725, 250687847.21, root, , Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
└─Sort_65, 250687847.21, root, , Column#269, Column#637:desc
└─Projection_726, 250687847.21, root, , Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, field(Column#277, <500, [500,1000), [1000,2000), [2000,5000), [5000,10000), [10000,90000), >=90000, 识别异常（证件号为空）, 合计)->Column#637
└─TableReader_711, 250687847.21, root, , MppVersion: 1, data:ExchangeSender_710
└─ExchangeSender_710, 250687847.21, mpp[tiflash], , ExchangeType: PassThrough
└─Projection_67, 250687847.21, mpp[tiflash], , case(eq(Column#268, 0), <500, eq(Column#268, 1), [500,1000), eq(Column#268, 2), [1000,2000), eq(Column#268, 3), [2000,5000), eq(Column#268, 4), [5000,10000), eq(Column#268, 5), [10000,90000), eq(Column#268, 6), >=90000, eq(Column#268, 7), 识别异常（证件号为空）, eq(Column#268, 8), 合计, )->Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
└─Union_709, 250687847.21, mpp[tiflash], ,
├─Projection_722, 125343488.73, mpp[tiflash], , Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, cast(Column#271, bigint(21) BINARY)->Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
│ └─Projection_704, 125343488.73, mpp[tiflash], , Column#66, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#68, Column#67, Column#69, round(div(Column#68, Column#69), 0)->Column#273, Column#109, round(div(Column#68, Column#147), 6)->Column#275, round(div(cast(Column#67, decimal(20,0) BINARY), cast(Column#146, decimal(20,0) BINARY)), 6)->Column#276
│ └─Projection_396, 125343488.73, mpp[tiflash], , Column#67, Column#68, Column#69, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#66, Column#109, Column#146, Column#147
│ └─HashJoin_393, 125343488.73, mpp[tiflash], , left outer join, equal:[eq(Column#66, Column#105) eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)]
│ ├─Projection_178(Build), 100274790.99, mpp[tiflash], , Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, round(div(Column#107, cast(Column#108, decimal(20,0) BINARY)), 0)->Column#109
│ │ └─Projection_216, 100274790.99, mpp[tiflash], , Column#107, Column#108, Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─HashAgg_180, 100274790.99, mpp[tiflash], , group by:Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#106)->Column#107, funcs:count(1)->Column#108, funcs:firstrow(Column#105)->Column#105, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
│ │ └─ExchangeReceiver_208, 100274790.99, mpp[tiflash], , stream_count: 16
│ │ └─ExchangeSender_207, 100274790.99, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#105, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
│ │ └─Projection_206, 100274790.99, mpp[tiflash], , div(Column#106, cast(case(eq(Column#368, 0), 1, Column#368), decimal(20,0) BINARY))->Column#106, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#105
│ │ └─HashAgg_182, 100274790.99, mpp[tiflash], , group by:Column#618, Column#619, Column#620, funcs:count(Column#614)->Column#368, funcs:sum(Column#615)->Column#106, funcs:firstrow(Column#616)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:firstrow(Column#617)->Column#105
│ │ └─Projection_717, 100274790.99, mpp[tiflash], , Column#102, Column#102, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)->Column#617, case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)->Column#618, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
│ │ └─Selection_184, 100274790.99, mpp[tiflash], , not(isnull(case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)))
│ │ └─Projection_195, 125343488.73, mpp[tiflash], , Column#102, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─HashAgg_196, 125343488.73, mpp[tiflash], , group by:Column#360, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#361)->Column#102, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
│ │ └─ExchangeReceiver_198, 125343488.73, mpp[tiflash], , stream_count: 16
│ │ └─ExchangeSender_197, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, collate: N/A], stream_count: 16
│ │ └─HashAgg_187, 125343488.73, mpp[tiflash], , group by:Column#611, Column#612, Column#613, funcs:sum(Column#610)->Column#361
│ │ └─Projection_716, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#610, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#612, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
│ │ └─Selection_194, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
│ │ └─TableFullScan_193, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
│ └─ExchangeReceiver_177(Probe), 125343488.73, mpp[tiflash], ,
│ └─ExchangeSender_176, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#66, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A]
│ └─HashJoin_77, 125343488.73, mpp[tiflash], , left outer join, equal:[eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)]
│ ├─Projection_110(Build), 125343488.73, mpp[tiflash], , Column#67, Column#68, Column#69, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#66
│ │ └─HashAgg_78, 125343488.73, mpp[tiflash], , group by:Column#599, Column#600, funcs:count(distinct Column#594)->Column#67, funcs:sum(Column#595)->Column#68, funcs:sum(Column#596)->Column#69, funcs:firstrow(Column#597)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:firstrow(Column#598)->Column#66
│ │ └─Projection_713, 125343488.73, mpp[tiflash], , stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, Column#63, cast(Column#64, decimal(20,0) BINARY)->Column#596, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, case(lt(Column#63, 50000), 0, lt(Column#63, 100000), 1, lt(Column#63, 200000), 2, lt(Column#63, 500000), 3, lt(Column#63, 1000000), 4, lt(Column#63, 9000000), 5, 6)->Column#598, case(lt(Column#63, 50000), 0, lt(Column#63, 100000), 1, lt(Column#63, 200000), 2, lt(Column#63, 500000), 3, lt(Column#63, 1000000), 4, lt(Column#63, 9000000), 5, 6)->Column#599, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─Projection_93, 125343488.73, mpp[tiflash], , Column#63, Column#64, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─HashAgg_94, 125343488.73, mpp[tiflash], , group by:Column#317, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#318)->Column#63, funcs:sum(Column#319)->Column#64, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
│ │ └─ExchangeReceiver_96, 125343488.73, mpp[tiflash], , stream_count: 16
│ │ └─ExchangeSender_95, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
│ │ └─HashAgg_83, 125343488.73, mpp[tiflash], , group by:Column#591, Column#592, Column#593, funcs:sum(Column#590)->Column#318, funcs:count(1)->Column#319
│ │ └─Projection_712, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#590, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#592, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
│ │ └─Selection_92, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
│ │ └─TableFullScan_91, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
│ └─Projection_159(Probe), 125343488.73, mpp[tiflash], , Column#146, Column#147, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─HashAgg_127, 125343488.73, mpp[tiflash], , group by:Column#609, funcs:count(distinct Column#605, Column#606)->Column#146, funcs:sum(Column#607)->Column#147, funcs:firstrow(Column#608)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─Projection_715, 125343488.73, mpp[tiflash], , case(lt(Column#142, 50000), 0, lt(Column#142, 100000), 1, lt(Column#142, 200000), 2, lt(Column#142, 500000), 3, lt(Column#142, 1000000), 4, lt(Column#142, 9000000), 5, 6)->Column#605, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, Column#142, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─Projection_142, 125343488.73, mpp[tiflash], , Column#142, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─HashAgg_143, 125343488.73, mpp[tiflash], , group by:Column#342, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#343)->Column#142, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
│ └─ExchangeReceiver_145, 125343488.73, mpp[tiflash], , stream_count: 16
│ └─ExchangeSender_144, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
│ └─HashAgg_132, 125343488.73, mpp[tiflash], , group by:Column#602, Column#603, Column#604, funcs:sum(Column#601)->Column#343
│ └─Projection_714, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#601, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#603, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
│ └─Selection_141, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
│ └─TableFullScan_140, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
├─Projection_723, 125343488.73, mpp[tiflash], , cast(Column#268, bigint(1) BINARY)->Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, cast(Column#271, bigint(21) BINARY)->Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
│ └─Projection_705, 125343488.73, mpp[tiflash], , 8->Column#268, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#187, Column#188, Column#189, Column#190, Column#230, 1.000000->Column#275, 1.000000->Column#276
│ └─HashJoin_657, 125343488.73, mpp[tiflash], , left outer join, equal:[eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)]
│ ├─Projection_404(Build), 125343488.73, mpp[tiflash], , stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#187, Column#188, Column#189, round(div(Column#187, Column#189), 0)->Column#190
│ │ └─Projection_436, 125343488.73, mpp[tiflash], , Column#187, Column#188, Column#189, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─HashAgg_406, 125343488.73, mpp[tiflash], , group by:Column#630, funcs:sum(Column#625)->Column#187, funcs:count(distinct Column#626, Column#627)->Column#188, funcs:sum(Column#628)->Column#189, funcs:firstrow(Column#629)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─Projection_719, 125343488.73, mpp[tiflash], , Column#183, case(lt(Column#183, 50000), 0, lt(Column#183, 100000), 1, lt(Column#183, 200000), 2, lt(Column#183, 500000), 3, lt(Column#183, 1000000), 4, lt(Column#183, 9000000), 5, 6)->Column#626, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, cast(Column#184, decimal(20,0) BINARY)->Column#628, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─Projection_419, 125343488.73, mpp[tiflash], , Column#183, Column#184, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ │ └─HashAgg_420, 125343488.73, mpp[tiflash], , group by:Column#465, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#466)->Column#183, funcs:sum(Column#467)->Column#184, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
│ │ └─ExchangeReceiver_422, 125343488.73, mpp[tiflash], , stream_count: 16
│ │ └─ExchangeSender_421, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
│ │ └─HashAgg_409, 125343488.73, mpp[tiflash], , group by:Column#622, Column#623, Column#624, funcs:sum(Column#621)->Column#466, funcs:count(1)->Column#467
│ │ └─Projection_718, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#621, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#623, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
│ │ └─Selection_418, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
│ │ └─TableFullScan_417, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
│ └─Projection_455(Probe), 125343488.73, mpp[tiflash], , stat.dwd_gs_bill_order_detail_si.ch_dealer_id, round(div(Column#228, cast(Column#229, decimal(20,0) BINARY)), 0)->Column#230
│ └─Projection_526, 125343488.73, mpp[tiflash], , Column#228, Column#229, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─HashAgg_457, 125343488.73, mpp[tiflash], , group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#227)->Column#228, funcs:count(1)->Column#229, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─Projection_491, 125343488.73, mpp[tiflash], , div(Column#227, cast(case(eq(Column#496, 0), 1, Column#496), decimal(20,0) BINARY))->Column#227, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─HashAgg_459, 125343488.73, mpp[tiflash], , group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:count(Column#223)->Column#496, funcs:sum(Column#223)->Column#227, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─Projection_474, 125343488.73, mpp[tiflash], , Column#223, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
│ └─HashAgg_475, 125343488.73, mpp[tiflash], , group by:Column#488, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#489)->Column#223, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
│ └─ExchangeReceiver_477, 125343488.73, mpp[tiflash], , stream_count: 16
│ └─ExchangeSender_476, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
│ └─HashAgg_464, 125343488.73, mpp[tiflash], , group by:Column#632, Column#633, Column#634, funcs:sum(Column#631)->Column#489
│ └─Projection_720, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#631, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#633, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
│ └─Selection_473, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
│ └─TableFullScan_472, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
└─Projection_724, 869.74, mpp[tiflash], , cast(Column#268, bigint(1) BINARY)->Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
└─Projection_706, 869.74, mpp[tiflash], , 7->Column#268, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, cast(Column#260, decimal(63,0) BINARY)->Column#270, 0->Column#271, cast(Column#261, decimal(42,0) BINARY)->Column#272, cast(round(div(Column#260, cast(Column#261, decimal(20,0) BINARY))), decimal(65,0) BINARY)->Column#273, 0->Column#274, 0.000000->Column#275, 0.000000->Column#276
└─Projection_698, 869.74, mpp[tiflash], , Column#260, Column#261, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
└─HashAgg_699, 869.74, mpp[tiflash], , group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#587)->Column#260, funcs:sum(Column#588)->Column#261, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
└─ExchangeReceiver_701, 869.74, mpp[tiflash], , stream_count: 16
└─ExchangeSender_700, 869.74, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
└─HashAgg_696, 869.74, mpp[tiflash], , group by:Column#636, funcs:sum(Column#635)->Column#587, funcs:count(1)->Column#588
└─Projection_721, 46488962.71, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#635, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
└─Selection_685, 46488962.71, mpp[tiflash], , eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752"))
└─TableFullScan_684, 70897182.98, mpp[tiflash], table:t1, pushed down filter:eq(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), keep order:false
explain analyze 执行计划
Projection_725, 250687847.21, 15788, root, , time:26s, loops:17, RU:0.000000, Concurrency:8, Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, 3.50 MB, N/A
└─Sort_65, 250687847.21, 15788, root, , time:26s, loops:17, Column#269, Column#637:desc, 4.57 MB, 0 Bytes
└─Projection_726, 250687847.21, 15788, root, , time:25.9s, loops:20, Concurrency:8, Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, field(Column#277, <500, [500,1000), [1000,2000), [2000,5000), [5000,10000), [10000,90000), >=90000, 识别异常（证件号为空）, 合计)->Column#637, 1.32 MB, N/A
└─TableReader_711, 250687847.21, 15788, root, , time:25.9s, loops:20, cop_task: {num: 15, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}, MppVersion: 1, data:ExchangeSender_710, 1.00 MB, N/A
└─ExchangeSender_710, 250687847.21, 15788, mpp[tiflash], , tiflash_task:{proc max:25.9s, min:16.7s, avg: 22.5s, p80:25.9s, p95:25.9s, iters:9, tasks:6, threads:96}, ExchangeType: PassThrough, N/A, N/A
└─Projection_67, 250687847.21, 15788, mpp[tiflash], , tiflash_task:{proc max:25.9s, min:16.7s, avg: 22.5s, p80:25.9s, p95:25.9s, iters:9, tasks:6, threads:96}, case(eq(Column#268, 0), <500, eq(Column#268, 1), [500,1000), eq(Column#268, 2), [1000,2000), eq(Column#268, 3), [2000,5000), eq(Column#268, 4), [5000,10000), eq(Column#268, 5), [10000,90000), eq(Column#268, 6), >=90000, eq(Column#268, 7), 识别异常（证件号为空）, eq(Column#268, 8), 合计, )->Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, N/A, N/A
└─Union_709, 250687847.21, 0, mpp[tiflash], , tiflash_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:0, tasks:6, threads:0}, , N/A, N/A
├─Projection_722, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:25.9s, min:0s, avg: 8.62s, p80:25.9s, p95:25.9s, iters:4, tasks:6, threads:32}, Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, cast(Column#271, bigint(21) BINARY)->Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, N/A, N/A
│ └─Projection_704, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:25.9s, min:0s, avg: 8.62s, p80:25.9s, p95:25.9s, iters:4, tasks:6, threads:32}, Column#66, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#68, Column#67, Column#69, round(div(Column#68, Column#69), 0)->Column#273, Column#109, round(div(Column#68, Column#147), 6)->Column#275, round(div(cast(Column#67, decimal(20,0) BINARY), cast(Column#146, decimal(20,0) BINARY)), 6)->Column#276, N/A, N/A
│ └─Projection_396, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:25.9s, min:0s, avg: 8.62s, p80:25.9s, p95:25.9s, iters:4, tasks:6, threads:32}, Column#67, Column#68, Column#69, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#66, Column#109, Column#146, Column#147, N/A, N/A
│ └─HashJoin_393, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:25.9s, min:0s, avg: 8.62s, p80:25.9s, p95:25.9s, iters:4, tasks:6, threads:32}, left outer join, equal:[eq(Column#66, Column#105) eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)], N/A, N/A
│ ├─Projection_178(Build), 100274790.99, 12805, mpp[tiflash], , tiflash_task:{proc max:22.5s, min:0s, avg: 7.49s, p80:22.5s, p95:22.5s, iters:32, tasks:6, threads:32}, Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, round(div(Column#107, cast(Column#108, decimal(20,0) BINARY)), 0)->Column#109, N/A, N/A
│ │ └─Projection_216, 100274790.99, 12805, mpp[tiflash], , tiflash_task:{proc max:22.5s, min:0s, avg: 7.49s, p80:22.5s, p95:22.5s, iters:32, tasks:6, threads:32}, Column#107, Column#108, Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─HashAgg_180, 100274790.99, 12805, mpp[tiflash], , tiflash_task:{proc max:22.5s, min:0s, avg: 7.49s, p80:22.5s, p95:22.5s, iters:32, tasks:6, threads:32}, group by:Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#106)->Column#107, funcs:count(1)->Column#108, funcs:firstrow(Column#105)->Column#105, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
│ │ └─ExchangeReceiver_208, 100274790.99, 4648477, mpp[tiflash], , tiflash_task:{proc max:22.5s, min:0s, avg: 7.49s, p80:22.5s, p95:22.5s, iters:430, tasks:6, threads:32}, stream_count: 16, N/A, N/A
│ │ └─ExchangeSender_207, 100274790.99, 4648477, mpp[tiflash], , tiflash_task:{proc max:22.5s, min:0s, avg: 3.74s, p80:0s, p95:22.5s, iters:512, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#105, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16, N/A, N/A
│ │ └─Projection_206, 100274790.99, 4648477, mpp[tiflash], , tiflash_task:{proc max:22.4s, min:0s, avg: 3.74s, p80:0s, p95:22.4s, iters:512, tasks:6, threads:32}, div(Column#106, cast(case(eq(Column#368, 0), 1, Column#368), decimal(20,0) BINARY))->Column#106, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#105, N/A, N/A
│ │ └─HashAgg_182, 100274790.99, 4648477, mpp[tiflash], , tiflash_task:{proc max:22.4s, min:0s, avg: 3.73s, p80:0s, p95:22.4s, iters:512, tasks:6, threads:32}, group by:Column#618, Column#619, Column#620, funcs:count(Column#614)->Column#368, funcs:sum(Column#615)->Column#106, funcs:firstrow(Column#616)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:firstrow(Column#617)->Column#105, N/A, N/A
│ │ └─Projection_717, 100274790.99, 11220930, mpp[tiflash], , tiflash_task:{proc max:21.9s, min:0s, avg: 3.65s, p80:0s, p95:21.9s, iters:8192, tasks:6, threads:32}, Column#102, Column#102, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)->Column#617, case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)->Column#618, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, N/A, N/A
│ │ └─Selection_184, 100274790.99, 11220930, mpp[tiflash], , tiflash_task:{proc max:21.9s, min:0s, avg: 3.65s, p80:0s, p95:21.9s, iters:8192, tasks:6, threads:32}, not(isnull(case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6))), N/A, N/A
│ │ └─Projection_195, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:21.9s, min:0s, avg: 3.64s, p80:0s, p95:21.9s, iters:8192, tasks:6, threads:32}, Column#102, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─HashAgg_196, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:21.8s, min:0s, avg: 3.64s, p80:0s, p95:21.8s, iters:8192, tasks:6, threads:32}, group by:Column#360, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#361)->Column#102, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
│ │ └─ExchangeReceiver_198, 125343488.73, 13830499, mpp[tiflash], , tiflash_task:{proc max:21.3s, min:0s, avg: 3.55s, p80:0s, p95:21.3s, iters:1627, tasks:6, threads:32}, stream_count: 16, N/A, N/A
│ │ └─ExchangeSender_197, 125343488.73, 13830499, mpp[tiflash], , tiflash_task:{proc max:21.2s, min:0s, avg: 3.53s, p80:0s, p95:21.2s, iters:512, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, collate: N/A], stream_count: 16, N/A, N/A
│ │ └─HashAgg_187, 125343488.73, 13830499, mpp[tiflash], , tiflash_task:{proc max:20.7s, min:0s, avg: 3.46s, p80:0s, p95:20.7s, iters:512, tasks:6, threads:32}, group by:Column#611, Column#612, Column#613, funcs:sum(Column#610)->Column#361, N/A, N/A
│ │ └─Projection_716, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:18.1s, min:0s, avg: 3.01s, p80:0s, p95:18.1s, iters:28649, tasks:6, threads:32}, cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#610, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#612, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, N/A, N/A
│ │ └─Selection_194, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:18s, min:0s, avg: 2.99s, p80:0s, p95:18s, iters:28649, tasks:6, threads:32}, eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), N/A, N/A
│ │ └─TableFullScan_193, 615501029.09, 49868123, mpp[tiflash], table:a, tiflash_task:{proc max:17.8s, min:0s, avg: 2.96s, p80:0s, p95:17.8s, iters:29301, tasks:6, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:454396, total_skipped_packs:59682, total_scanned_rows:3679974958, total_skipped_rows:481166467, total_rs_index_load_time: 525ms, total_read_time: 116283ms}, total_create_snapshot_time: 13ms, total_local_region_num: 0, total_remote_region_num: 0}, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false, N/A, N/A
│ └─ExchangeReceiver_177(Probe), 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:3.39s, min:0s, avg: 1.13s, p80:3.38s, p95:3.39s, iters:4, tasks:6, threads:32}, , N/A, N/A
│ └─ExchangeSender_176, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:25.8s, min:0s, avg: 4.31s, p80:0s, p95:25.8s, iters:2, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#66, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], N/A, N/A
│ └─HashJoin_77, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:25.8s, min:0s, avg: 4.31s, p80:0s, p95:25.8s, iters:2, tasks:6, threads:32}, left outer join, equal:[eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)], N/A, N/A
│ ├─Projection_110(Build), 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:23.5s, min:0s, avg: 3.92s, p80:0s, p95:23.5s, iters:2, tasks:6, threads:32}, Column#67, Column#68, Column#69, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#66, N/A, N/A
│ │ └─HashAgg_78, 125343488.73, 12805, mpp[tiflash], , tiflash_task:{proc max:23.5s, min:0s, avg: 3.92s, p80:0s, p95:23.5s, iters:2, tasks:6, threads:32}, group by:Column#599, Column#600, funcs:count(distinct Column#594)->Column#67, funcs:sum(Column#595)->Column#68, funcs:sum(Column#596)->Column#69, funcs:firstrow(Column#597)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:firstrow(Column#598)->Column#66, N/A, N/A
│ │ └─Projection_713, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:23.2s, min:0s, avg: 3.87s, p80:0s, p95:23.2s, iters:8192, tasks:6, threads:32}, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, Column#63, cast(Column#64, decimal(20,0) BINARY)->Column#596, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, case(lt(Column#63, 50000), 0, lt(Column#63, 100000), 1, lt(Column#63, 200000), 2, lt(Column#63, 500000), 3, lt(Column#63, 1000000), 4, lt(Column#63, 9000000), 5, 6)->Column#598, case(lt(Column#63, 50000), 0, lt(Column#63, 100000), 1, lt(Column#63, 200000), 2, lt(Column#63, 500000), 3, lt(Column#63, 1000000), 4, lt(Column#63, 9000000), 5, 6)->Column#599, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─Projection_93, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:23.1s, min:0s, avg: 3.85s, p80:0s, p95:23.1s, iters:8192, tasks:6, threads:32}, Column#63, Column#64, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─HashAgg_94, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:23.1s, min:0s, avg: 3.85s, p80:0s, p95:23.1s, iters:8192, tasks:6, threads:32}, group by:Column#317, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#318)->Column#63, funcs:sum(Column#319)->Column#64, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
│ │ └─ExchangeReceiver_96, 125343488.73, 14617371, mpp[tiflash], , tiflash_task:{proc max:22.4s, min:0s, avg: 3.74s, p80:0s, p95:22.4s, iters:1054, tasks:6, threads:32}, stream_count: 16, N/A, N/A
│ │ └─ExchangeSender_95, 125343488.73, 14617371, mpp[tiflash], , tiflash_task:{proc max:22s, min:0s, avg: 3.67s, p80:0s, p95:22s, iters:512, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16, N/A, N/A
│ │ └─HashAgg_83, 125343488.73, 14617371, mpp[tiflash], , tiflash_task:{proc max:21.1s, min:0s, avg: 3.51s, p80:0s, p95:21.1s, iters:512, tasks:6, threads:32}, group by:Column#591, Column#592, Column#593, funcs:sum(Column#590)->Column#318, funcs:count(1)->Column#319, N/A, N/A
│ │ └─Projection_712, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:18.3s, min:0s, avg: 3.04s, p80:0s, p95:18.3s, iters:28594, tasks:6, threads:32}, cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#590, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#592, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, N/A, N/A
│ │ └─Selection_92, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:18.2s, min:0s, avg: 3.03s, p80:0s, p95:18.2s, iters:28594, tasks:6, threads:32}, eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), N/A, N/A
│ │ └─TableFullScan_91, 615501029.09, 49867648, mpp[tiflash], table:a, tiflash_task:{proc max:18s, min:0s, avg: 3s, p80:0s, p95:18s, iters:29232, tasks:6, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:455339, total_skipped_packs:61702, total_scanned_rows:3687425503, total_skipped_rows:497752620, total_rs_index_load_time: 388ms, total_read_time: 106287ms}, total_create_snapshot_time: 12ms, total_local_region_num: 0, total_remote_region_num: 0}, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false, N/A, N/A
│ └─Projection_159(Probe), 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:2.33s, min:0s, avg: 388.3ms, p80:0s, p95:2.33s, iters:2, tasks:6, threads:32}, Column#146, Column#147, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─HashAgg_127, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:2.33s, min:0s, avg: 388.3ms, p80:0s, p95:2.33s, iters:2, tasks:6, threads:32}, group by:Column#609, funcs:count(distinct Column#605, Column#606)->Column#146, funcs:sum(Column#607)->Column#147, funcs:firstrow(Column#608)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─Projection_715, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:2.05s, min:0s, avg: 341.6ms, p80:0s, p95:2.05s, iters:8192, tasks:6, threads:32}, case(lt(Column#142, 50000), 0, lt(Column#142, 100000), 1, lt(Column#142, 200000), 2, lt(Column#142, 500000), 3, lt(Column#142, 1000000), 4, lt(Column#142, 9000000), 5, 6)->Column#605, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, Column#142, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─Projection_142, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:1.94s, min:0s, avg: 323.6ms, p80:0s, p95:1.94s, iters:8192, tasks:6, threads:32}, Column#142, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─HashAgg_143, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:1.94s, min:0s, avg: 322.9ms, p80:0s, p95:1.94s, iters:8192, tasks:6, threads:32}, group by:Column#342, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#343)->Column#142, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
│ └─ExchangeReceiver_145, 125343488.73, 13845400, mpp[tiflash], , tiflash_task:{proc max:1s, min:0s, avg: 167.2ms, p80:0s, p95:1s, iters:1032, tasks:6, threads:32}, stream_count: 16, N/A, N/A
│ └─ExchangeSender_144, 125343488.73, 13845400, mpp[tiflash], , tiflash_task:{proc max:24s, min:0s, avg: 4s, p80:0s, p95:24s, iters:512, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16, N/A, N/A
│ └─HashAgg_132, 125343488.73, 13845400, mpp[tiflash], , tiflash_task:{proc max:21.8s, min:0s, avg: 3.64s, p80:0s, p95:21.8s, iters:512, tasks:6, threads:32}, group by:Column#602, Column#603, Column#604, funcs:sum(Column#601)->Column#343, N/A, N/A
│ └─Projection_714, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:19.6s, min:0s, avg: 3.26s, p80:0s, p95:19.6s, iters:28608, tasks:6, threads:32}, cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#601, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#603, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, N/A, N/A
│ └─Selection_141, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:19.4s, min:0s, avg: 3.24s, p80:0s, p95:19.4s, iters:28608, tasks:6, threads:32}, eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), N/A, N/A
│ └─TableFullScan_140, 615501029.09, 49867034, mpp[tiflash], table:a, tiflash_task:{proc max:19.3s, min:0s, avg: 3.21s, p80:0s, p95:19.3s, iters:29220, tasks:6, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:454919, total_skipped_packs:56802, total_scanned_rows:3684448908, total_skipped_rows:458424478, total_rs_index_load_time: 1057ms, total_read_time: 100986ms}, total_create_snapshot_time: 11ms, total_local_region_num: 0, total_remote_region_num: 0}, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false, N/A, N/A
├─Projection_723, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:25.4s, min:0s, avg: 8.34s, p80:24.6s, p95:25.4s, iters:2, tasks:6, threads:32}, cast(Column#268, bigint(1) BINARY)->Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, cast(Column#271, bigint(21) BINARY)->Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, N/A, N/A
│ └─Projection_705, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:25.4s, min:0s, avg: 8.34s, p80:24.6s, p95:25.4s, iters:2, tasks:6, threads:32}, 8->Column#268, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#187, Column#188, Column#189, Column#190, Column#230, 1.000000->Column#275, 1.000000->Column#276, N/A, N/A
│ └─HashJoin_657, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:25.4s, min:0s, avg: 8.34s, p80:24.6s, p95:25.4s, iters:2, tasks:6, threads:32}, left outer join, equal:[eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)], N/A, N/A
│ ├─Projection_404(Build), 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:23.4s, min:0s, avg: 7.76s, p80:23.2s, p95:23.4s, iters:2, tasks:6, threads:32}, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#187, Column#188, Column#189, round(div(Column#187, Column#189), 0)->Column#190, N/A, N/A
│ │ └─Projection_436, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:23.4s, min:0s, avg: 7.76s, p80:23.2s, p95:23.4s, iters:2, tasks:6, threads:32}, Column#187, Column#188, Column#189, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─HashAgg_406, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:23.4s, min:0s, avg: 7.76s, p80:23.2s, p95:23.4s, iters:2, tasks:6, threads:32}, group by:Column#630, funcs:sum(Column#625)->Column#187, funcs:count(distinct Column#626, Column#627)->Column#188, funcs:sum(Column#628)->Column#189, funcs:firstrow(Column#629)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─Projection_719, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:23.1s, min:0s, avg: 7.67s, p80:22.9s, p95:23.1s, iters:8192, tasks:6, threads:32}, Column#183, case(lt(Column#183, 50000), 0, lt(Column#183, 100000), 1, lt(Column#183, 200000), 2, lt(Column#183, 500000), 3, lt(Column#183, 1000000), 4, lt(Column#183, 9000000), 5, 6)->Column#626, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, cast(Column#184, decimal(20,0) BINARY)->Column#628, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─Projection_419, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:23s, min:0s, avg: 7.64s, p80:22.8s, p95:23s, iters:8192, tasks:6, threads:32}, Column#183, Column#184, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ │ └─HashAgg_420, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:23s, min:0s, avg: 7.64s, p80:22.8s, p95:23s, iters:8192, tasks:6, threads:32}, group by:Column#465, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#466)->Column#183, funcs:sum(Column#467)->Column#184, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
│ │ └─ExchangeReceiver_422, 125343488.73, 14207041, mpp[tiflash], , tiflash_task:{proc max:22.2s, min:0s, avg: 7.37s, p80:22s, p95:22.2s, iters:1036, tasks:6, threads:32}, stream_count: 16, N/A, N/A
│ │ └─ExchangeSender_421, 125343488.73, 14207041, mpp[tiflash], , tiflash_task:{proc max:21.8s, min:0s, avg: 3.63s, p80:0s, p95:21.8s, iters:512, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16, N/A, N/A
│ │ └─HashAgg_409, 125343488.73, 14207041, mpp[tiflash], , tiflash_task:{proc max:20.4s, min:0s, avg: 3.39s, p80:0s, p95:20.4s, iters:512, tasks:6, threads:32}, group by:Column#622, Column#623, Column#624, funcs:sum(Column#621)->Column#466, funcs:count(1)->Column#467, N/A, N/A
│ │ └─Projection_718, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:17.9s, min:0s, avg: 2.99s, p80:0s, p95:17.9s, iters:28652, tasks:6, threads:32}, cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#621, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#623, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, N/A, N/A
│ │ └─Selection_418, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:17.8s, min:0s, avg: 2.97s, p80:0s, p95:17.8s, iters:28652, tasks:6, threads:32}, eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), N/A, N/A
│ │ └─TableFullScan_417, 615501029.09, 49869038, mpp[tiflash], table:a, tiflash_task:{proc max:17.7s, min:0s, avg: 2.94s, p80:0s, p95:17.7s, iters:29328, tasks:6, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:454112, total_skipped_packs:56558, total_scanned_rows:3678303486, total_skipped_rows:456378467, total_rs_index_load_time: 164ms, total_read_time: 29687ms}, total_create_snapshot_time: 12ms, total_local_region_num: 0, total_remote_region_num: 0}, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false, N/A, N/A
│ └─Projection_455(Probe), 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:2.04s, min:0s, avg: 589.9ms, p80:1.5s, p95:2.04s, iters:2, tasks:6, threads:32}, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, round(div(Column#228, cast(Column#229, decimal(20,0) BINARY)), 0)->Column#230, N/A, N/A
│ └─Projection_526, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:2.04s, min:0s, avg: 589.7ms, p80:1.5s, p95:2.04s, iters:2, tasks:6, threads:32}, Column#228, Column#229, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─HashAgg_457, 125343488.73, 2980, mpp[tiflash], , tiflash_task:{proc max:2.04s, min:0s, avg: 589.7ms, p80:1.5s, p95:2.04s, iters:2, tasks:6, threads:32}, group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#227)->Column#228, funcs:count(1)->Column#229, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─Projection_491, 125343488.73, 3794503, mpp[tiflash], , tiflash_task:{proc max:2.04s, min:0s, avg: 588.1ms, p80:1.49s, p95:2.04s, iters:512, tasks:6, threads:32}, div(Column#227, cast(case(eq(Column#496, 0), 1, Column#496), decimal(20,0) BINARY))->Column#227, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─HashAgg_459, 125343488.73, 3794503, mpp[tiflash], , tiflash_task:{proc max:2.02s, min:0s, avg: 581.2ms, p80:1.47s, p95:2.02s, iters:512, tasks:6, threads:32}, group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:count(Column#223)->Column#496, funcs:sum(Column#223)->Column#227, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─Projection_474, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:1.92s, min:0s, avg: 535.1ms, p80:1.29s, p95:1.92s, iters:8192, tasks:6, threads:32}, Column#223, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
│ └─HashAgg_475, 125343488.73, 11220930, mpp[tiflash], , tiflash_task:{proc max:1.92s, min:0s, avg: 534.7ms, p80:1.29s, p95:1.92s, iters:8192, tasks:6, threads:32}, group by:Column#488, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#489)->Column#223, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
│ └─ExchangeReceiver_477, 125343488.73, 14223293, mpp[tiflash], , tiflash_task:{proc max:1.01s, min:0s, avg: 330.9ms, p80:979ms, p95:1.01s, iters:1043, tasks:6, threads:32}, stream_count: 16, N/A, N/A
│ └─ExchangeSender_476, 125343488.73, 14223293, mpp[tiflash], , tiflash_task:{proc max:23.9s, min:0s, avg: 3.98s, p80:0s, p95:23.9s, iters:512, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16, N/A, N/A
│ └─HashAgg_464, 125343488.73, 14223293, mpp[tiflash], , tiflash_task:{proc max:21.5s, min:0s, avg: 3.58s, p80:0s, p95:21.5s, iters:512, tasks:6, threads:32}, group by:Column#632, Column#633, Column#634, funcs:sum(Column#631)->Column#489, N/A, N/A
│ └─Projection_720, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:19.4s, min:0s, avg: 3.24s, p80:0s, p95:19.4s, iters:28622, tasks:6, threads:32}, cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#631, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#633, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, N/A, N/A
│ └─Selection_473, 1184004184.63, 47919035, mpp[tiflash], , tiflash_task:{proc max:19.3s, min:0s, avg: 3.22s, p80:0s, p95:19.3s, iters:28622, tasks:6, threads:32}, eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), N/A, N/A
│ └─TableFullScan_472, 615501029.09, 49867034, mpp[tiflash], table:a, tiflash_task:{proc max:19.2s, min:0s, avg: 3.19s, p80:0s, p95:19.2s, iters:29238, tasks:6, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:455827, total_skipped_packs:60006, total_scanned_rows:3691477040, total_skipped_rows:484218327, total_rs_index_load_time: 1743ms, total_read_time: 110073ms}, total_create_snapshot_time: 12ms, total_local_region_num: 0, total_remote_region_num: 0}, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false, N/A, N/A
└─Projection_724, 869.74, 3, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 5.58s, p80:16.7s, p95:16.7s, iters:3, tasks:6, threads:32}, cast(Column#268, bigint(1) BINARY)->Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, N/A, N/A
└─Projection_706, 869.74, 3, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 5.58s, p80:16.7s, p95:16.7s, iters:3, tasks:6, threads:32}, 7->Column#268, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, cast(Column#260, decimal(63,0) BINARY)->Column#270, 0->Column#271, cast(Column#261, decimal(42,0) BINARY)->Column#272, cast(round(div(Column#260, cast(Column#261, decimal(20,0) BINARY))), decimal(65,0) BINARY)->Column#273, 0->Column#274, 0.000000->Column#275, 0.000000->Column#276, N/A, N/A
└─Projection_698, 869.74, 3, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 5.58s, p80:16.7s, p95:16.7s, iters:3, tasks:6, threads:32}, Column#260, Column#261, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
└─HashAgg_699, 869.74, 3, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 5.58s, p80:16.7s, p95:16.7s, iters:3, tasks:6, threads:32}, group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#587)->Column#260, funcs:sum(Column#588)->Column#261, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16, N/A, N/A
└─ExchangeReceiver_701, 869.74, 5, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 5.58s, p80:16.7s, p95:16.7s, iters:3, tasks:6, threads:32}, stream_count: 16, N/A, N/A
└─ExchangeSender_700, 869.74, 5, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 2.78s, p80:0s, p95:16.7s, iters:2, tasks:6, threads:32}, ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16, N/A, N/A
└─HashAgg_696, 869.74, 5, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 2.78s, p80:0s, p95:16.7s, iters:2, tasks:6, threads:32}, group by:Column#636, funcs:sum(Column#635)->Column#587, funcs:count(1)->Column#588, N/A, N/A
└─Projection_721, 46488962.71, 97, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 2.78s, p80:0s, p95:16.7s, iters:34, tasks:6, threads:32}, cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#635, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, N/A, N/A
└─Selection_685, 46488962.71, 97, mpp[tiflash], , tiflash_task:{proc max:16.7s, min:0s, avg: 2.78s, p80:0s, p95:16.7s, iters:34, tasks:6, threads:32}, eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), N/A, N/A
└─TableFullScan_684, 70897182.98, 71505598, mpp[tiflash], table:t1, tiflash_task:{proc max:16.5s, min:0s, avg: 2.75s, p80:0s, p95:16.5s, iters:16565, tasks:6, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:216910, total_skipped_packs:281498, total_scanned_rows:1761215205, total_skipped_rows:2276880226, total_rs_index_load_time: 178ms, total_read_time: 32423ms}, total_create_snapshot_time: 13ms, total_local_region_num: 0, total_remote_region_num: 0}, pushed down filter:eq(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), keep order:false, N/A, N/A
使用CTE方法优化试试呢？将公共部分使用CTE 得到结果，然后再使用结果集进行查询（dwd_gs_bill_order_detail_si tiflash 6亿行，共TableFullScan了5次；使用CTE减少TableFullScan a表次数 ）
with a as (select… from dwd_gs_bill_order_detail_si where ch_dealer_id not in ())
select … from a a1 left out join a a2 on
table_a 被多次使用
with table_a as (SELECT
/+ READ_FROM_STORAGE(TIFLASH[a]) ,MERGE()/
本身使用的就是CTE, 加了MERGE()是为了内存不溢出，结果现在加上后还是偶尔溢出，但是加上后大多数时间执行挺快，占用内存不大
with table_a as (SELECT
/+ READ_FROM_STORAGE(TIFLASH[a]) ,MERGE() /
