tidb5.4
内存报警，查看长时间运行的sql，达到了几千秒，查询的视图，单独拿出sql执行很快，单独执行时也会出现卡住的情况，请问如何排查，这种情况挺多的，最终导致内存报警
查视图的时候是加了条件的？可以举个例子吗？
一个视图有问题还是所有视图有问题？ 视图是单表还是多表关联？ 能否给个慢的查询SQL 、视图SQL及其执行计划
查询视图，条件where 1=1 and order_date between ‘2023-07-01’ and ‘2023-07-31’ group by order_date order by 1 desc limit 10 offset 0
目前就一个视图，有这种情况，视图是多表进行的left join
看一下直接查视图和用sql查走的执行计划不一样吗？
同样的sql查询视图，不是用sql直接查基础表，查询语句是一样的执行多次就会卡住
执行计划：
*************************** 1. row ***************************
id: Projection_12
estRows: 8.39
task: root
access object:
operator info: xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.order_date, minus(Column#90, Column#91)->Column#104, minus(Column#92, Column#93)->Column#105, Column#94, Column#95, Column#92, Column#90, Column#96, Column#97, Column#93, ifnull(Column#91, 0)->Column#106, Column#98, Column#99, Column#100, Column#101, Column#102, Column#103
*************************** 2. row ***************************
id: └─TopN_15
estRows: 8.39
task: root
access object:
operator info: xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.order_date:desc, offset:0, count:10
*************************** 3. row ***************************
id: └─HashAgg_20
estRows: 8.39
task: root
access object:
operator info: group by:Column#130, funcs:sum(Column#115)->Column#90, funcs:sum(Column#116)->Column#91, funcs:count(distinct Column#117)->Column#92, funcs:count(distinct Column#118)->Column#93, funcs:count(distinct Column#119)->Column#94, funcs:count(distinct Column#120)->Column#95, funcs:count(distinct Column#121)->Column#96, funcs:count(distinct Column#122)->Column#97, funcs:count(distinct Column#123)->Column#98, funcs:count(distinct Column#124)->Column#99, funcs:sum(Column#125)->Column#100, funcs:sum(Column#126)->Column#101, funcs:sum(Column#127)->Column#102, funcs:sum(Column#128)->Column#103, funcs:firstrow(Column#129)->xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.order_date
*************************** 4. row ***************************
id: └─Projection_70
estRows: 386835.94
task: root
access object:
operator info: if(and(eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.flag, 0), not(isnull(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.right_refund_code))), 0, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.price)->Column#115, case(isnull(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.refund_price), 0, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.refund_price)->Column#116, if(eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.flag, 1), xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.voucher_code, )->Column#117, if(not(isnull(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.right_refund_code)), xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.voucher_code, )->Column#118, if(eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.flag, 1), xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.right_order_code, )->Column#119, if(eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.flag, 1), xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.member_no, )->Column#120, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.right_refund_code, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.refund_mobile, if(eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.reexchange_flag, 1), xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.member_no, )->Column#123, if(eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.reexchange_flag, 1), xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.voucher_code, )->Column#124, cast(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.reexchange_tickets, decimal(10,0) BINARY)->Column#125, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.reexchange_amount, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.bt_value, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.dk_value, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.order_date, xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.order_date
*************************** 5. row ***************************
id: └─HashJoin_31
estRows: 386835.94
task: root
access object:
operator info: left outer join, equal:[eq(xxx_xxxx_xxxxx.hhh_dy_group_purchase_detail.reexchange_inner_code, xxx_xxxx_xxxxx.dim_cinema_detail.cinema_inner_code)]
*************************** 6. row ***************************
id: ├─IndexReader_42(Build)
estRows: 1002.00
task: root
access object:
operator info: index:IndexFullScan_41
*************************** 7. row ***************************
id: │ └─IndexFullScan_41
estRows: 1002.00
task: cop[tikv]
access object: table:c, index:idx_dim_cinema_detail_inner_code(cinema_inner_code)
operator info: keep order:false, stats:pseudo
*************************** 8. row ***************************
id: └─IndexLookUp_38(Probe)
estRows: 309468.75
task: root
access object:
operator info:
*************************** 9. row ***************************
id: ├─IndexRangeScan_36(Build)
estRows: 309468.75
task: cop[tikv]
access object: table:a, index:idx_dm_spread_activity_detail(order_date)
operator info: range:[“2023-07-01”,“2023-07-31”], keep order:false, stats:pseudo
*************************** 10. row ***************************
id: └─TableRowIDScan_37(Probe)
estRows: 309468.75
task: cop[tikv]
access object: table:a
operator info: keep order:false, stats:pseudo
具体的sql语句如下：
select order_date, (sum(if(flag = ‘0’ and right_refund_code is not null,0,price)) - sum(case when refund_price is null then 0 else refund_price end)) as sjsmxsje, (ifnull(count(distinct if(flag = ‘1’ ,voucher_code,null)),0) - ifnull(count(distinct if(right_refund_code is not null,voucher_code,null)),0)) as sjsmqzs, (count(distinct if(flag = ‘1’,right_order_code,null))) as right_order_num, (count(distinct if(flag = ‘1’,member_no,null))) as member_num, (count(distinct if(flag = ‘1’ ,voucher_code,null))) as sell_voucher_num, (sum(if(flag = ‘0’ and right_refund_code is not null,0,price))) as order_price, (count(distinct right_refund_code)) as right_refund_num, (count(distinct refund_mobile)) as refund_member_num, (count(distinct if(right_refund_code is not null,voucher_code,null))) as refund_voucher_num, (ifnull(sum(case when refund_price is null then 0 else refund_price end),0)) as efund_price, (count(distinct if(reexchange_flag=‘1’ , member_no,null))) as check_user_num, (count(distinct if(reexchange_flag=‘1’ , voucher_code,null))) as check_voucher_num,sum(reexchange_tickets) as reexchange_tickets,sum(reexchange_amount) as reexchange_amount,sum(bt_value) as bt_value,sum(dk_value) as dk_value from hhh_dy_group_purchase_detail where 1=1 and order_date between ‘2023-07-01’ and ‘2023-07-31’ group by order_date order by 1 desc limit 10 offset 0
如果是这个bug的话，升级到5.4.3，应该包含了这个修复。
目前只有这一个视图有这种情况，其它视图也有基表left join连接查询的
发现，我开一个session，执行这个sql，经过4秒返回了结果，但是我开的其它session上，这个sql并没有执行完，还在一直执行中，什么情况？
可以explain analyze sql看一下执行计划到底慢在哪
4秒就返回数据了，为啥还说慢呢
视图是2个表进行的left join查询，没有过滤条件，关联字段有一个表有索引，一个没有索引，全表关联查询，加索引有用嘛
estRows最多才38w，除非统计信息不准导致实际的行数比这个大的多，就现在来看4s绝对是时间算长的了。相关issue也有，强烈建议做好备份，升级一下看看。
这个能否贴一下截图，文字排版看得很吃力
目前情况是有时候快有时候会卡住？
先收集统计信息。
上面的sql在客户端执行完后，数据也返回了，但这个sql并没有结束，其它session 还是能看到这个sql处于一直运行状态