Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: update索引更新数据时出现2种执行计划，其中带selectLock的很慢
|
username: army
【TiDB Usage Environment】Production
【TiDB Version】v5.1.1
【Encountered Problem】Executing update statements based on index fields is occasionally very slow
【Reproduction Path】Operations performed that led to the issue
【Problem Phenomenon and Impact】
Execution plan with normal duration:
Execution plan with timeout:
Due to sensitivity, only part of the table structure is uploaded:
PRIMARY KEY (PFlowId) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY ix_channeltype_uniqueid (ChannelType,UniqueId),
KEY ix_DataChange_LastTime (datachange_lasttime),
KEY ix_ProviderId (ProviderId),
KEY ix_BusinessDodumentId (BusinessDocumentId),
KEY ix_paymentflowid (PaymentFlowId),
KEY ix_billid (BillId),
KEY ix_jobid (JobId),
KEY idx_datachange_createtime (datachange_createtime),
KEY idx_OuterDocumentId (OuterDocumentId),
KEY ix_settlementItemType (SettlementItemType),
KEY ix_PurchaseOrderId (PurchaseOrderId),
KEY ix_PFlowStatus (PFlowStatus),
KEY ix_SettlementCategory (SettlementCategory),
KEY ix_currency (Currency),
KEY ix_splitId (splitId),
KEY ix_subTradeNo (subTradeNo),
KEY ix_payableorderId (PayableorderId),
KEY ix_settlementBatchId (SettlementBatchId)
Approximately 30,000 rows meet the condition:
select count(1) from Tistm_paymentflow where BillId = 1100292925;
±---------+
| count(1) |
±---------+
| 27825 |
±---------+
|
username: 
|
username: 
|
username: