慢 SQL 分析

[yuanyuandeqiu]

下面的 SQL ，执行时间接近 30s ，麻烦大佬帮我分析一下原因，数据库是 tidb

DELETE
FROM
t_a a
WHERE
field_1 IN (
SELECT
t_b.field_1
FROM
t_b b
WHERE
b.field_2 = 1
AND b.field_3 = 1
AND b.field_4 IN ( 1,2,... )
)


有两个索引：
t_a.index_1 (field_1)
t_b.index_2(field_2,field_3,field_4)

EXPlAIN：

[
{
"id": "Delete_9",
"estRows": "N/A",
"actRows": "0",
"taskType": "root",
"executeInfo": "time:912.6µs, loops:1",
"operatorInfo": "N/A",
"memoryInfo": "0 Bytes",
"diskInfo": "N/A",
"subOperators": [
{
"id": "IndexHashJoin_20",
"estRows": "5.37",
"actRows": "0",
"taskType": "root",
"executeInfo": "time:909.3µs, loops:1",
"operatorInfo": "inner join, inner:IndexLookUp_17, outer key:t_b.field_1, inner key:t_a.field_1, equal cond:eq(t_b.field_1, t_a.field_1)",
"memoryInfo": "0 Bytes",
"diskInfo": "N/A",
"subOperators": [
{
"id": "HashAgg_50(Build)",
"estRows": "4.00",
"actRows": "0",
"taskType": "root",
"executeInfo": "time:852.1µs, loops:1, partial_worker:{wall_time:837.473µs, concurrency:5, task_num:0, tot_wait:4.011568ms, tot_exec:0s, tot_time:4.016061ms, max:811.853µs, p95:811.853µs}, final_worker:{wall_time:844.632µs, concurrency:5, task_num:0, tot_wait:4.066824ms, tot_exec:4.747µs, tot_time:4.074436ms, max:821.498µs, p95:821.498µs}",
"operatorInfo": "group by:t_b.field_1, funcs:firstrow(t_b.field_1)->t_b.field_1",
"memoryInfo": "9.86 KB",
"diskInfo": "N/A",
"subOperators": [
{
"id": "IndexLookUp_51",
"estRows": "4.00",
"actRows": "0",
"taskType": "root",
"executeInfo": "time:787.5µs, loops:1",
"memoryInfo": "230 Bytes",
"diskInfo": "N/A",
"subOperators": [
{
"id": "IndexRangeScan_48(Build)",
"estRows": "4.00",
"actRows": "0",
"taskType": "cop[tikv]",
"accessObject": "table:t_b, index:index_2(field_2,field_3,field_4)",
"executeInfo": "time:498.8µs, loops:1, cop_task: {num: 1, max: 669.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 651.6µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 295µs, rocksdb: {block: {cache_hit_count: 7}}}",
"operatorInfo": "range:[1 1 1,1 1 2], keep order:false",
"memoryInfo": "N/A",
"diskInfo": "N/A"
},
{
"id": "HashAgg_40(Probe)",
"estRows": "4.00",
"actRows": "0",
"taskType": "cop[tikv]",
"operatorInfo": "group by:t_b.field_1, ",
"memoryInfo": "N/A",
"diskInfo": "N/A",
"subOperators": [
{
"id": "TableRowIDScan_49",
"estRows": "4.00",
"actRows": "0",
"taskType": "cop[tikv]",
"accessObject": "table:tei",
"operatorInfo": "keep order:false",
"memoryInfo": "N/A",
"diskInfo": "N/A"
}
]
}
]
}
]
},
{
"id": "IndexLookUp_17(Probe)",
"estRows": "5.37",
"actRows": "0",
"taskType": "root",
"memoryInfo": "N/A",
"diskInfo": "N/A",
"subOperators": [
{
"id": "IndexRangeScan_15(Build)",
"estRows": "5.37",
"actRows": "0",
"taskType": "cop[tikv]",
"accessObject": "table:t_a, index:index_1(field_1)",
"operatorInfo": "range: decided by [eq(t_a.field_1, t_b.field_1)], keep order:false",
"memoryInfo": "N/A",
"diskInfo": "N/A"
},
{
"id": "TableRowIDScan_16(Probe)",
"estRows": "5.37",
"actRows": "0",
"taskType": "cop[tikv]",
"accessObject": "table:t_a",
"operatorInfo": "keep order:false",
"memoryInfo": "N/A",
"diskInfo": "N/A"
}
]
}
]
}
]
}
]

已解决

DELETE
FROM
t_a a

改为

DELETE
a.*
FROM
t_a a

[kkwa56188]

盲猜一个, B 表如果大 的话 就换用 exists 代替 in, 这样也可以让解释器更好的理解意图 :

delete from t_a a
where exists ( select 1 from t_b b where a.field_1 = b.field_1 and b.blablabla )