【 TiDB 使用环境】生产环境
【 TiDB 版本】5.0.5
【复现路径】select for update
【遇到的问题：问题现象及影响】dashboard上显示了执行计划，并且使用了force index，仍然没有走到索引上，导致锁表10s左右，频繁出现这个问题
【资源配置】高性能服务器
【附件：截图/日志/监控】
实际执行效果显示没走到索引上
表结构:
CREATE TABLE `lp` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`account_id` bigint(20) NOT NULL,
`seg_t` varchar(10) NOT NULL DEFAULT 'CCC',
`type` varchar(1) NOT NULL,
`stock` bigint(20) NOT NULL,
`m` varchar(10) NOT NULL DEFAULT 'UUU',
`sec_t` varchar(10) NOT NULL DEFAULT 'TKTK',
`q` double NOT NULL,
`d_q` decimal(30, 10) NOT NULL DEFAULT '0.0000000000',
`a_c` decimal(30, 10) DEFAULT NULL,
`l_o_a` datetime DEFAULT NULL,
`business_from` date NOT NULL,
`business_to` date NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `lp_unique_key` (
`account_id`,
`stock`,
`business_to`,
`type`
),
KEY `idx_lp_from_date` (`business_from`),
KEY `IDX_LEDGER_POSITION_STOCK_ID_DATE` (`stock`, `business_to`),
KEY `idx_lp_account_date` (`account_id`, `business_to`)
) ENGINE = InnoDB AUTO_INCREMENT = xxxxxxxx DEFAULT CHARSET = utf8 ROW_FORMAT = DYNAMIC
执行sql语句为：
SELECT xxxxx
FROM
lp FORCE INDEX(lp_unique_key)
WHERE
(
account_id = ?
AND stock = ?
AND `type` = ?
AND business_to >= ?
) FOR
UPDATE
[arguments: (90019329700, 30443, T, 2023-01-18 00:00:00)];```
执行计划为：
id task estRows operator info actRows execution info memory disk
SelectLock_7 root 0.09 for update 0 2 time:8.38s, loops:4, lock_keys: {time:663.5µs, region:1, keys:1, lock_rpc:636.761µs, rpc_count:1} N/A N/A
└─UnionScan_8 root 0.09 eq(lp.lp.account_id, 9193297), eq(lp.lp.stock_id, 30443), eq(lp.lp.type, "T"), ge(lp.lp.business_to, 2023-01-18 00:00:00.000000) 2 time:11.3ms, loops:4 N/A N/A
└─IndexLookUp_12 root 0.09 2 time:11.3ms, loops:6, index_task: {total_time: 1.25ms, fetch_handle: 1.24ms, build: 1.44µs, wait: 4.9µs}, table_task: {total_time: 16.6ms, num: 2, concurrency: 10} 16.5 KB N/A
├─Selection_11 cop[tikv] 0.09 eq(lp.lp.type, "T") 2 time:1.21ms, loops:6, cop_task: {num: 2, max: 852.4µs, min: 309.3µs, avg: 580.8µs, p95: 852.4µs, max_proc_keys: 2, p95_proc_keys: 2, rpc_num: 2, rpc_time: 1.12ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 4, total_keys: 22, rocksdb: {delete_skipped_count: 8, key_skipped_count: 36, block: {cache_hit_count: 24, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ └─IndexRangeScan_9 cop[tikv] 0.18 table:lp, index:lp_unique_key(account_id, stock, business_to, type), range:[9193297 30443 2023-01-18,9193297 30443 +inf], keep order:false 4 tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─TableRowIDScan_10 cop[tikv] 0.09 lp, keep order:false 2 time:9.57ms, loops:4, cop_task: {num: 2, max: 572µs, min: 321.1µs, avg: 446.6µs, p95: 572µs, max_proc_keys: 1, p95_proc_keys: 1, tot_proc: 1ms, rpc_num: 3, rpc_time: 1.36ms, copr_cache_hit_ratio: 0.00}, ResolveLock:{num_rpc:1, total_time:7.99ms}, tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:2, tasks:2}, scan_detail: {total_process_keys: 2, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 16, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
阶段耗时：
隔离级别是RR，可重复读
