【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.6
【遇到的问题：问题现象及影响】
在v5.3.3版本运行没问题的SQL，升级至v7.5.6后变成慢查询。通过执行计划查看，发现走错索引。
表结构索引
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_refund_no` (`refund_no`),
KEY `idx_sub_order_no` (`sub_order_no`),
KEY `idx_order_no` (`order_no`),
KEY `idx_buyer_id_create_time` (`buyer_id`,`create_time`),
KEY `idx_seller_id_create_time` (`seller_id`,`create_time`)
KEY `idx_seller_id_modify_time` (`seller_id`,`modify_time`)
KEY `idx_create_time` (`create_time`)
KEY `idx_buyer_name_create_time` (`buyer_name`,`create_time`)
KEY `idx_refund_type_create_time` (`refund_type`,`create_time`),
KEY `idx_modify_time` (`modify_time`)
SQL语句
SELECT
`id`,
refund_no,
order_no,
sub_order_no,
biz_type,
biz_channel,
biz_code,
biz_id,
buyer_id,
buyer_name,
seller_id,
seller_name,
refund_type,
refund_status,
refund_reason_code,
refund_reason,
sku_id,
sku_title,
sku_price,
sku_count,
item_info,
buyer_note,
feature,
is_del,
create_time,
modify_time,
refund_close_type,
refund_close_time,
trade_close_type,
refund_money_status,
seller_type,
operator_id,
operator_type,
buyer_del,
seller_del,
buyer_pic_urls,
exchange_type,
return_back_address,
version,
feature_version,
route_type,
exchange_status
FROM
refund_order
WHERE
sub_order_no = '110181745965242788'
AND refund_type IN (
20,
50
)
AND create_time BETWEEN '2025-09-09 00:00:00.0'
AND '2025-09-16 23:59:59.999'
AND exchange_type IN (0)
AND is_del = 0
ORDER BY
create_time DESC
LIMIT
0, 20;
v5.3.3版本explain analyze执行计划
SQL执行耗时0.01秒
+----------------------------------+---------+---------+-----------+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+---------+---------+-----------+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| TopN_9 | 0.00 | 1 | root | | time:2.6ms, loops:2 | dw_trade_refund_seller_db.refund_order.create_time:desc, offset:0, count:20 | 20.9 KB | N/A |
| └─IndexLookUp_24 | 0.00 | 1 | root | | time:2.54ms, loops:3, index_task: {total_time: 894µs, fetch_handle: 890.2µs, build: 776ns, wait: 2.97µs}, table_task: {total_time: 1.53ms, num: 1, concurrency: 5} | | 21.1 KB | N/A |
| ├─IndexRangeScan_17(Build) | 1.20 | 1 | cop[tikv] | table:refund_order, index:idx_sub_order_no(sub_order_no) | time:888.9µs, loops:3, cop_task: {num: 1, max: 806.7µs, proc_keys: 1, rpc_num: 1, rpc_time: 792.7µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 73, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1, block: {cache_hit_count: 11, read_count: 1, read_byte: 63.9 KB}}} | range:["110181745965242788","110181745965242788"], keep order:false | N/A | N/A |
| └─TopN_23(Probe) | 0.00 | 1 | cop[tikv] | | time:1.4ms, loops:2, cop_task: {num: 1, max: 1.3ms, proc_keys: 1, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.29ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 3201, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 14, read_count: 0, read_byte: 0 Bytes}}} | dw_trade_refund_seller_db.refund_order.create_time:desc, offset:0, count:20 | N/A | N/A |
| └─Selection_19 | 0.00 | 1 | cop[tikv] | | tikv_task:{time:1ms, loops:1} | eq(dw_trade_refund_seller_db.refund_order.exchange_type, 0), eq(dw_trade_refund_seller_db.refund_order.is_del, 0), ge(dw_trade_refund_seller_db.refund_order.create_time, 2025-09-09 00:00:00.000000), in(dw_trade_refund_seller_db.refund_order.refund_type, 20, 50), le(dw_trade_refund_seller_db.refund_order.create_time, 2025-09-16 23:59:59.999000) | N/A | N/A |
| └─TableRowIDScan_18 | 1.20 | 1 | cop[tikv] | table:refund_order | tikv_task:{time:1ms, loops:1} | keep order:false | N/A | N/A |
+----------------------------------+---------+---------+-----------+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (0.01 sec)
v7.5.6版本explain analyze执行计划
SQL执行耗时5秒+
+------------------------------------+---------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+---------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_7 | 0.00 | 1 | root | | time:3.03s, loops:2, RU:163348.904775, Concurrency:OFF | dw_trade_refund_seller_db.refund_order.id, dw_trade_refund_seller_db.refund_order.refund_no, dw_trade_refund_seller_db.refund_order.order_no, dw_trade_refund_seller_db.refund_order.sub_order_no, dw_trade_refund_seller_db.refund_order.biz_type, dw_trade_refund_seller_db.refund_order.biz_channel, dw_trade_refund_seller_db.refund_order.biz_code, dw_trade_refund_seller_db.refund_order.biz_id, dw_trade_refund_seller_db.refund_order.buyer_id, dw_trade_refund_seller_db.refund_order.buyer_name, dw_trade_refund_seller_db.refund_order.seller_id, dw_trade_refund_seller_db.refund_order.seller_name, dw_trade_refund_seller_db.refund_order.refund_type, dw_trade_refund_seller_db.refund_order.refund_status, dw_trade_refund_seller_db.refund_order.refund_reason_code, dw_trade_refund_seller_db.refund_order.refund_reason, dw_trade_refund_seller_db.refund_order.sku_id, dw_trade_refund_seller_db.refund_order.sku_title, dw_trade_refund_seller_db.refund_order.sku_price, dw_trade_refund_seller_db.refund_order.sku_count, dw_trade_refund_seller_db.refund_order.item_info, dw_trade_refund_seller_db.refund_order.buyer_note, dw_trade_refund_seller_db.refund_order.feature, dw_trade_refund_seller_db.refund_order.is_del, dw_trade_refund_seller_db.refund_order.create_time, dw_trade_refund_seller_db.refund_order.modify_time, dw_trade_refund_seller_db.refund_order.refund_close_type, dw_trade_refund_seller_db.refund_order.refund_close_time, dw_trade_refund_seller_db.refund_order.trade_close_type, dw_trade_refund_seller_db.refund_order.refund_money_status, dw_trade_refund_seller_db.refund_order.seller_type, dw_trade_refund_seller_db.refund_order.operator_id, dw_trade_refund_seller_db.refund_order.operator_type, dw_trade_refund_seller_db.refund_order.buyer_del, dw_trade_refund_seller_db.refund_order.seller_del | 18.2 KB | N/A |
| └─Limit_12 | 0.00 | 1 | root | | time:3.03s, loops:2 | offset:0, count:20 | N/A | N/A |
| └─IndexLookUp_28 | 0.00 | 1 | root | | time:3.03s, loops:2, index_task: {total_time: 3s, fetch_handle: 575.2ms, build: 407.6ms, wait: 2.02s}, table_task: {total_time: 13s, num: 213, concurrency: 5}, next: {wait_index: 412.9ms, wait_table_lookup_build: 1.75ms, wait_table_lookup_resp: 2.62s} | | 27.9 MB | N/A |
| ├─IndexRangeScan_25(Build) | 3.94 | 4072848 | cop[tikv] | table:refund_order, index:idx_create_time(create_time) | time:417.2ms, loops:1029, cop_task: {num: 5, max: 710.1ms, min: 315.7µs, avg: 316.1ms, p95: 710.1ms, max_proc_keys: 960001, p95_proc_keys: 960001, tot_proc: 1.46s, tot_wait: 115.8µs, copr_cache_hit_ratio: 0.40, build_task_duration: 24.6µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:5, total_time:1.58s}}, tikv_task:{proc max:844ms, min:276ms, avg: 528ms, p80:844ms, p95:844ms, iters:4000, tasks:5}, scan_detail: {total_process_keys: 2617892, total_process_keys_size: 120423032, total_keys: 2617895, get_snapshot_time: 45.5µs, rocksdb: {delete_skipped_count: 543, key_skipped_count: 2618978, block: {cache_hit_count: 3423}}} | range:[2025-09-09 00:00:00,2025-09-17 00:00:00), keep order:true, desc | N/A | N/A |
| └─Selection_27(Probe) | 0.00 | 1 | cop[tikv] | | time:12.1s, loops:214, cop_task: {num: 1025, max: 169.9ms, min: 0s, avg: 29.1ms, p95: 72.6ms, max_proc_keys: 12447, p95_proc_keys: 9842, tot_proc: 24.2s, tot_wait: 317.8ms, copr_cache_hit_ratio: 0.00, build_task_duration: 3.6ms, max_distsql_concurrency: 8, max_extra_concurrency: 1, store_batch_num: 1}, rpc_info:{Cop:{num_rpc:1024, total_time:29.8s}}, tikv_task:{proc max:156ms, min:0s, avg: 27.2ms, p80:44.5ms, p95:70ms, iters:8300, tasks:1025}, scan_detail: {total_process_keys: 4070555, total_process_keys_size: 9993006843, total_keys: 5926447, get_snapshot_time: 38.8ms, rocksdb: {delete_skipped_count: 1419272, key_skipped_count: 12927252, block: {cache_hit_count: 684752, read_count: 3366, read_byte: 29.3 MB, read_time: 86.8ms}}} | eq(dw_trade_refund_seller_db.refund_order.exchange_type, 0), eq(dw_trade_refund_seller_db.refund_order.is_del, 0), eq(dw_trade_refund_seller_db.refund_order.sub_order_no, "110181745965242788"), in(dw_trade_refund_seller_db.refund_order.refund_type, 20, 50) | N/A | N/A |
| └─TableRowIDScan_26 | 3.94 | 4072848 | cop[tikv] | table:refund_order | tikv_task:{proc max:155ms, min:0s, avg: 26.7ms, p80:44ms, p95:69.3ms, iters:8300, tasks:1025} | keep order:false | N/A | N/A |
+------------------------------------+---------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (3.04 sec)