Some SQL executions are extremely slow or even fail when upgrading from TiDB 5.4.3 to 6.5.2

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Tidb5.4.3 升级 6.5.2 部分出现执行非常缓慢SQL甚至执行失败

| username: 最强王者

  1. The SQL used in the original TiDB 5.4.3 was normal, but after upgrading to version 6.5.2, some SQL executions became very slow or even failed.
  2. Index failure.
  3. Query association query and CAST(a.customer_id AS SIGNED) = orders.user_id, querying between different data types is very slow, leading to execution failure.
    Summary: Currently, it is identified that the version upgrade is the cause.
| username: 最强王者 | Original post link

Related SQL:

SELECT COUNT(1)
  FROM marketingpro.inquiry_paper_answer_summary a
  LEFT JOIN orderpro.orders AS orders
    ON a.order_no = orders.order_no
   AND CAST(a.customer_id AS SIGNED) = orders.user_id
 WHERE a.seller_id = 198
   AND a.activity_id = '814863245868335104'
   AND a.answer_time >= '2023-05-25 00:00:00'
   AND a.answer_time <= '2023-05-25 23:59:59'
| username: Holland | Original post link

How healthy is the table?
Has the “analyze table inquiry_paper_answer_summary;” been executed?

| username: 最强王者 | Original post link

Table Health

Db_name,Table_name,Partition_name,Healthy
marketingpro,inquiry_paper_answer_summary,"",95

analyze table inquiry_paper_answer_summary; No analysis has been done yet.

| username: Holland | Original post link

Try executing ANALYZE TABLE to recollect the statistics.

| username: Billmay表妹 | Original post link

Based on the information you provided, this issue might be due to a version upgrade. In TiDB 6.5.2, there may be some incompatibilities with TiDB 5.4.3. You can try using new features in TiDB 6.5.2 to optimize your queries, such as new index types like full-text indexes and JSON indexes.

Additionally, the SQL you provided uses the CAST function to convert a.customer_id to SIGNED type, which might slow down the query. You can try changing the data type of a.customer_id to SIGNED type to avoid using the CAST function.

Finally, you can use the EXPLAIN ANALYZE command to analyze the query plan, identify bottlenecks in the query, and optimize it. For example, you can use indexes to speed up the query or adjust TiDB configurations to optimize query performance.

| username: 最强王者 | Original post link

Execution Plan

Projection_73  2.30  root  ""  sqlqmai.zm_stores.name, coalesce(Column#111, orderpro.order_refunds.order_no)->Column#495, case(not(isnull(Column#111)), orderpro.order_refunds.order_no, -)->Column#496, ifnull(orderpro.orders.pay_no, )->Column#497, ifnull(concat(`, orderpro.orders.third_pay_no), )->Column#498, orderpro.order_refunds.user_id, coalesce(orgcenterpro.org_shops.name, )->Column#499, coalesce(memberpro.member_level_card.level_name, -)->Column#500, ifnull(case(eq(orderpro.orders.pay_type, 0), Unpaid, eq(orderpro.orders.pay_type, 1), WeChat Pay, eq(orderpro.orders.pay_type, 2), Alipay, eq(orderpro.orders.pay_type, 4), Balance Payment, eq(orderpro.orders.pay_type, 32), Physical Card Payment, eq(orderpro.orders.pay_type, 16), Free Payment, eq(orderpro.orders.pay_type, 8), Mixed Payment, eq(orderpro.orders.pay_type, 64), Unknown, eq(orderpro.orders.pay_type, 10), Merchant Settlement), None)->Column#501, orderpro.order_items.item_name, ifnull(json_unquote(cast(json_extract(cast(orderpro.order_items.features, json BINARY), $.membershipCardCode), var_string(16777216))), None)->Column#502, memberpro.member_paid_customer_attached.id, plus(0, cast(cast(div(cast(orderpro.order_items.item_price, decimal(10,0) BINARY), 100), var_string(5)), double BINARY))->Column#503, case(eq(memberpro.member_paid_customer_attached.get_way, 0), First Card Purchase, eq(memberpro.member_paid_customer_attached.get_way, 1), Repeat Card Purchase, eq(memberpro.member_paid_customer_attached.get_way, 2), Card Renewal, eq(memberpro.member_paid_customer_attached.get_way, 3), Data Import, eq(memberpro.member_paid_customer_attached.get_way, 4), Friend Gift, Unknown)->Column#504, ifnull(json_unquote(cast(json_extract(cast(orderpro.orders.features, json BINARY), $.workWechatUserid), var_string(16777216))), None)->Column#505, ifnull(json_unquote(cast(json_extract(cast(orderpro.orders.features, json BINARY), $.workStaffId), var_string(16777216))), None)->Column#506, ifnull(json_unquote(cast(json_extract(cast(orderpro.orders.features, json BINARY), $.workStaffName), var_string(16777216))), None)->Column#507, 1->Column#508, plus(0, cast(cast(div(Column#109, 100), var_string(5)), double BINARY))->Column#509, plus(0, cast(cast(div(Column#110, 100), var_string(5)), double BINARY))->Column#510, ifnull(plus(0, cast(cast(Column#112, var_string(5)), double BINARY)), 0)->Column#511, ifnull(orderpro.order_refunds.apply_reason, None)->Column#512, case(not(isnull(Column#111)), Membership Card Refund, Membership Card Purchase)->Column#513, coalesce(orderpro.order_refunds.deal_at, orderpro.orders.order_at)->Column#514, coalesce(date(orderpro.order_refunds.deal_at), date(orderpro.orders.order_at))->Column#515, case(isnull(Column#488), sqlqmai.zm_stores.name, concat(sqlqmai.zm_stores.name, -, coalesce(Column#488, )))->Column#516, coalesce(orgcenterpro.org_shops.name, sqlqmai.zm_stores.name)->Column#517, ifnull(orgcenterpro.org_shops.code, None)->Column#518, coalesce(cast(orgcenterpro.org_shops.id, var_string(20)), None)->Column#519, coalesce(if(eq(Column#113, ), <nil>, Column#113), Column#114, None)->Column#520, Membership Card Order->Column#521, ifnull(memberpro.customer_base.username, None)->Column#522, ifnull(memberpro.customer_base.mobile_phone, None)->Column#523, case(not(isnull(Column#111)), orderpro.orders.order_at)->Column#524
└─Projection_74  2.30  root  ""  orderpro.order_refunds.user_id, orderpro.order_refunds.order_no, orderpro.order_refunds.deal_at, orderpro.order_refunds.apply_reason, orderpro.orders.pay_no, orderpro.orders.third_pay_no, orderpro.orders.pay_type, Column#109, Column#110, orderpro.orders.order_at, orderpro.orders.features, Column#113, Column#114, Column#111, Column#112, memberpro.customer_base.mobile_phone, memberpro.customer_base.username, memberpro.member_level_card.level_name, memberpro.member_paid_customer_attached.id, memberpro.member_paid_customer_attached.get_way, sqlqmai.zm_stores.name, orgcenterpro.org_shops.id, orgcenterpro.org_shops.name, orgcenterpro.org_shops.code, orgcenterpro.org_shops.name, orderpro.order_items.item_name, orderpro.order_items.item_price, orderpro.order_items.features, Column#488
  └─IndexHashJoin_80  2.30  root  ""  left outer join, inner:IndexLookUp_77, outer key:orderpro.order_refunds.order_no, inner key:orderpro.order_items.order_no, equal cond:eq(orderpro.order_refunds.order_no, orderpro.order_items.order_no)
    ├─HashJoin_90(Build)  1.01  root  ""  left outer join, equal:[eq(orderpro.order_refunds.store_id, sqlqmai.zm_acl_organization_shops.shop_id)]
    │ ├─HashJoin_92(Build)  1.01  root  ""  left outer join, equal:[eq(Column#586, Column#587)]
    │ │ ├─Projection_93(Build)  1.01  root  ""  orderpro.order_refunds.user_id, orderpro.order_refunds.store_id, orderpro.order_refunds.order_no, orderpro.order_refunds.deal_at, orderpro.order_refunds.apply_reason, orderpro.orders.pay_no, orderpro.orders.third_pay_no, orderpro.orders.pay_type, Column#109, Column#110, orderpro.orders.order_at, orderpro.orders.features, Column#113, Column#114, Column#111, Column#112, memberpro.customer_base.mobile_phone, memberpro.customer_base.username, memberpro.member_level_card.level_name, memberpro.member_paid_customer_attached.id, memberpro.member_paid_customer_attached.get_way, sqlqmai.zm_stores.name, orgcenterpro.org_shops.id, orgcenterpro.org_shops.name, orgcenterpro.org_shops.code, json_extract(cast(cast(memberpro.customer_base.extra, json BINARY), json BINARY), $.multiStoreId)->Column#586
    │ │ │ └─Projection_94  1.01  root  ""  orderpro.order_refunds.user_id, orderpro.order_refunds.store_id, orderpro.order_refunds.order_no, orderpro.order_refunds.deal_at, orderpro.order_refunds.apply_reason, orderpro.orders.pay_no, orderpro.orders.third_pay_no, orderpro.orders.pay_type, Column#109, Column#110, orderpro.orders.order_at, orderpro.orders.features, Column#113, Column#114, Column#111, Column#112, memberpro.customer_base.mobile_phone, memberpro.customer_base.username, memberpro.customer_base.extra, memberpro.member_level_card.level_name, memberpro.member_paid_customer_attached.id, memberpro.member_paid_customer_attached.get_way, sqlqmai.zm_stores.name, orgcenterpro.org_shops.id, orgcenterpro.org_shops.name, orgcenterpro.org_shops.code
    │ │ │   └─HashJoin_96  1.01  root  ""  left outer join, equal:[eq(orderpro.order_refunds.order_no, memberpro.member_paid_customer_attached.order_no) eq(Column#573, memberpro.member_paid_customer_attached.customer_id)]
    │ │ │     ├─IndexJoin_101(Build)  1.00  root  ""  left outer join, inner:TableReader_98, outer key:orderpro.order_refunds.store_id, inner key:orgcenterpro.org_shops.id, equal cond:eq(orderpro.order_refunds.store_id, orgcenterpro.org_shops.id)
    │ │ │     │ ├─IndexJoin_112(Build)  1.00  root  ""  left outer join, inner:TableReader_109, outer key:orderpro.order_refunds.seller_id, inner key:sqlqmai.zm_stores.id, equal cond:eq(orderpro.order_refunds.seller_id, sqlqmai.zm_stores.id)
    │ │ │     │ │ ├─Projection_119(Build)  1.00  root  ""  orderpro.order_refunds.user_id, orderpro.order_refunds.seller_id, orderpro.order_refunds.store_id, orderpro.order_refunds.order_no, orderpro.order_refunds.deal_at, orderpro.order_refunds.apply_reason, orderpro.orders.pay_no, orderpro.orders.third_pay_no, orderpro.orders.pay_type, Column#109, Column#110, orderpro.orders.order_at, orderpro.orders.features, Column#113, Column#114, Column#111, Column#112, memberpro.customer_base.mobile_phone, memberpro.customer_base.username, memberpro.customer_base.extra, memberpro.member_level_card.level_name, cast(orderpro.order_refunds.user_id, var_string(20))->Column#573
    │ │ │     │ │ │ └─HashJoin_122  1.00  root  ""  left outer join, equal:[eq(Column#563, memberpro.customer_base.id)]
    │ │ │     │ │ │   ├─Projection_123(Build)  1.00  root  ""  orderpro.order_refunds.user_id, orderpro.order_refunds.seller_id, orderpro.order_refunds.store_id, orderpro.order_refunds.order_no, orderpro.order_refunds.deal_at, orderpro.order_refunds.apply_reason, orderpro.orders.pay_no, orderpro.orders.third_pay_no, orderpro.orders.pay_type, Column#109, Column#110, orderpro.orders.order_at, orderpro.orders.features, json_unquote(cast(json_extract(cast(orderpro.order_refunds.features, json BINARY), $.handlersInfo.accountName), var_string(16777216)))->Column#113, json_unquote(cast(json_extract(cast(orderpro.order_refunds.features, json BINARY), $.handlersInfo.accountNumber), var_string(16777216)))->Column#114, Column#111, Column#112, cast(orderpro.order_refunds.user_id, var_string(20))->Column#563
    │ │ │     │ │ │   │ └─HashAgg_124  1.00  root  ""  "funcs:sum(Column#662)->Column#109, funcs:sum(Column#663)->Column#110, funcs:group_concat(Column#664 separator "","")->Column#111, funcs:sum(Column#665)->Column#112, funcs:firstrow(Column#666)->orderpro.order_refunds.order_no, funcs:firstrow(Column#667)->orderpro.order_refunds.seller_id, funcs:firstrow(Column#668)->orderpro.order_refunds.store_id, funcs:firstrow(Column#669)->orderpro.order_refunds.user_id, funcs:firstrow(Column#670)->orderpro.order_refunds.apply_reason, funcs:firstrow(Column#671)->orderpro.order_refunds.features, funcs:firstrow(Column#672)->orderpro.order_refunds.deal_at, funcs:firstrow(Column#673)->orderpro.orders.pay_type, funcs:firstrow(Column#674)->orderpro.orders.pay_no, funcs:firstrow(Column#675)->orderpro.orders.third_pay_no, funcs:firstrow(Column#676)->orderpro.orders.order_at, funcs:firstrow(Column#677)->orderpro.orders.features"
    │ │ │     │ │ │   │   └─Projection_423  4.07  root  ""  cast(orderpro.orders.total_amount, decimal(10,0) BINARY)->Column#662, cast(orderpro.orders.actual_amount, decimal(10,0) BINARY)->Column#663, orderpro.order_refunds.refund_no, round(div(cast(orderpro.order_refunds.refund_amount, decimal(10,0) BINARY), 100), 2)->Column#665, orderpro.order_refunds.order_no, orderpro.order_refunds.seller_id, orderpro.order_refunds.store_id, orderpro.order_refunds.user_id, orderpro.order_refunds.apply_reason, orderpro.order_refunds.features, orderpro.order_refunds.deal_at, orderpro.orders.pay_type, orderpro.orders.pay_no, orderpro.orders.third_pay_no, orderpro.orders.order_at, orderpro.orders.features
    │ │ │     │ │ │   │     └─IndexJoin_129  4.07  root  ""  inner join, inner:IndexLookUp_128, outer key:orderpro.order_refunds.order_no, inner key:orderpro.orders.order_no, equal cond:eq(orderpro.order_refunds.order_no, orderpro.orders.order_no)
    │ │ │     │ │ │   │       ├─IndexLookUp_160(Build)  4.05  root  ""  ""
    │ │ │     │ │ │   │       │ ├─IndexRangeScan_157(Build)  8321.81  cop[tikv]  table:order_refunds, index:idx_seller_deal(seller_id, deal_at)  range:[214176 2023-05-20 00:00:00,214176 2023-05-26 00:00:00), [214177 2023-05-20 00:00:00,214177 2023-05-26 00:00:00), [214178 2023-05-20 00:00:00,214178 2023-05-26 00:00:00), [214179 2023-05-20 00:00:00,214179 2023-05-26 00:00:00), [215067 2023-05-20 00:00:00,215067 2023-05-26 00:00:00), keep order:false
    │ │ │     │ │ │   │       │ └─Selection_159(Probe)  4.05  cop[tikv]  ""  eq(orderpro.order_refunds.status, 30), or(and(eq(orderpro.order_refunds.biz_type, 5), eq(orderpro.order_refunds.order_type, 13)), and(eq(orderpro.order_refunds.biz_type, 4), eq(orderpro.order_refunds.order_type, 5)))
    │ │ │     │ │ │   │       │   └─TableRowIDScan_158  8321.81  cop[tikv]  table:order_refunds  keep order:false
    │ │ │     │ │ │   │       └─IndexLookUp_128(Probe)  4.07  root  ""  ""
    │ │ │     │ │ │   │         ├─IndexRangeScan_126(Build)  4.07  cop[tikv]  table:tb, index:uniq_order_no_user_id(order_no, user_id)  range: decided by [eq(orderpro.orders.order_no, orderpro.order_refunds.order_no)], keep order:false
    │ │ │     │ │ │   │         └─TableRowIDScan_127(Probe)  4.07  cop[tikv]  table:tb  keep order:false
    │ │ │     │ │ │   └─MergeJoin_191(Probe)  482154681.00  root  ""  left outer join, left key:memberpro.customer_base.id, right key:memberpro.customer_card.customer_id
    │ │ │     │ │ │     ├─IndexJoin_202(Build)  478378880.00  root  ""  left outer join, inner:TableReader_199, outer key:memberpro.customer_card.level_id, inner key:memberpro.member_level_card.id, equal cond:eq(memberpro.customer_card.level_id, memberpro.member_level_card.id)
    │ │ │     │ │ │     │ ├─Projection_209(Build)  478378880.00  root  ""  memberpro.customer_card.level_id, memberpro.customer_card.customer_id
    │ │ │     │ │ │     │ │ └─IndexLookUp_208  478378880.00  root  ""  ""
    │ │ │     │ │ │     │ │   ├─IndexFullScan_206(Build)  478378880.00  cop[tikv]  table:tc, index:idx_customerId(customer_id, group_id)  keep order:true
    │ │ │     │ │ │     │ │   └─TableRowIDScan_207(Probe)  478378880.00  cop[tikv]  table:tc  keep order:false
    │ │ │     │ │ │     │ └─TableReader_199(Probe)  478378880.00  root  ""  data:TableRangeScan_198
    │ │ │     │ │ │     │   └─TableRangeScan_198  478378880.00  cop[tikv]  table:td  range: decided by [memberpro.customer_card.level_id], keep order:false
    │ │ │     │ │ │     └─Projection_197(Probe)  482154681.00  root  ""  memberpro.customer_base.id, memberpro.customer_base.mobile_phone, memberpro.customer_base.username, memberpro.customer_base.extra
    │ │ │     │ │ │       └─IndexLookUp_196  482154681.00  root  ""  ""
    │ │ │     │ │ │         ├─IndexFullScan_194(Build)  482154681.00  cop[tikv]  table:tb, index:PRIMARY(id)  keep order:true
    │ │ │     │ │ │         └─TableRowIDScan_195(Probe)  482154681.00  cop[tikv]  table:tb  keep order:false
    │ │ │     │ │ └─TableReader_109(Probe)  1.00  root  ""  data:TableRangeScan_108
    │ │ │     │ │   └─TableRangeScan_108  1.00  cop[tikv]  table:ki  range: decided by [orderpro.order_refunds.seller_id], keep order:false
    │ │ │     │ └─TableReader_98
| username: 最强王者 | Original post link

Okay, currently collecting plans.

| username: redgame | Original post link

So, what was the reason in the end…?