Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: join 时候如何把on条件用到左右的索引上
|
username: 大飞飞呀
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the problem
[Encountered Problem: Problem Phenomenon and Impact]
SELECT
b.user_id,
b.c_date,
a.product_id,
cast(
SUM(
IF(
maker_side = "sell",
maker_out_amount,
maker_in_amount
)
) AS char
) AS amount,
count(1) AS cnt,
"" sub_sql
FROM
table_a a
JOIN (
SELECT
10960001609428992 user_id,
"2023-09-06" c_date
UNION
ALL
SELECT
10959939290952704 user_id,
"2023-09-06" c_date
) b ON (
a.taker_user_id = b.user_id
OR a.maker_user_id = b.user_id
)
AND a.created_at >= b.c_date
AND a.created_at < DATE_ADD(b.c_date, INTERVAL 1 DAY)
WHERE
STATUS = 2
AND taker_user_id <> 0
AND maker_user_id <> 0
AND maker_biz_order <> "0"
AND taker_biz_order <> "0"
GROUP BY
b.user_id,
b.c_date,
a.product_id;
id task estRows operator info actRows execution info memory disk
Projection_16 root 2 Column#41, Column#42, table_a.product_id, cast(Column#43, var_string(5))->Column#45, Column#44, ->Column#46 24 time:30.2s, loops:5, Concurrency:OFF 110.1 KB N/A
└─HashAgg_17 root 2 group by:Column#69, Column#70, Column#71, funcs:sum(Column#65)->Column#43, funcs:count(1)->Column#44, funcs:firstrow(Column#66)->table_a.product_id, funcs:firstrow(Column#67)->Column#41, funcs:firstrow(Column#68)->Column#42 24 time:30.2s, loops:5, partial_worker:{wall_time:30.241202501s, concurrency:5, task_num:58, tot_wait:2m31.180212434s, tot_exec:25.126475ms, tot_time:2m31.205397686s, max:30.241106062s, p95:30.241106062s}, final_worker:{wall_time:30.24131089s, concurrency:5, task_num:20, tot_wait:2m31.206076662s, tot_exec:111.253µs, tot_time:2m31.206193115s, max:30.24126709s, p95:30.24126709s} 2.01 MB N/A
└─Projection_68 root 9545081.25 if(eq(table_a.maker_side, sell), table_a.maker_out_amount, table_a.maker_in_amount)->Column#65, table_a.product_id, Column#41, Column#42, Column#41, Column#42, table_a.product_id 57366 time:30.2s, loops:59, Concurrency:5 1.58 MB N/A
└─Projection_18 root 9545081.25 table_a.product_id, table_a.maker_side, table_a.maker_out_amount, table_a.maker_in_amount, Column#41, Column#42 57366 time:30.2s, loops:59, Concurrency:5 1.49 MB N/A
└─HashJoin_34 root 9545081.25 CARTESIAN inner join, other cond:ge(table_a.created_at, cast(Column#42, datetime(6) BINARY)), lt(table_a.created_at, cast(date_add(Column#42, 1, "DAY"), datetime(6) BINARY)), or(eq(table_a.taker_user_id, Column#41), eq(table_a.maker_user_id, Column#41)) 57366 time:30s, loops:59, build_hash_table:{total:52.7µs, fetch:46.9µs, build:5.85µs}, probe:{concurrency:5, total:2m31.1s, max:30.2s, probe:1m46.3s, fetch:44.8s} 3.21 KB 0 Bytes
├─Union_35(Build) root 2 2 time:42.6µs, loops:3 N/A N/A
│ ├─Projection_37 root 1 1096000160009428992->Column#41, 2023-09-06->Column#42 1 time:3.82µs, loops:2, Concurrency:OFF 0 Bytes N/A
│ │ └─TableDual_38 root 1 rows:1 1 time:913ns, loops:2 N/A N/A
│ └─Projection_39 root 1 1095993953290952704->Column#41, 2023-09-06->Column#42 1 time:5.39µs, loops:2, Concurrency:OFF 0 Bytes N/A
│ └─TableDual_40 root 1 rows:1 1 time:1.02µs, loops:2 N/A N/A
└─IndexLookUp_67(Probe) root 4772540.62 3960228 time:1.38s, loops:3869, index_task: {total_time: 23.1s, fetch_handle: 407.8ms, build: 331.5µs, wait: 22.7s}, table_task: {total_time: 1m1s, num: 197, concurrency: 5}, next: {wait_index: 1.49ms, wait_table_lookup_build: 401.1µs, wait_table_lookup_resp: 163.9ms} 265.0 MB N/A
├─IndexRangeScan_64(Build) cop[tikv] 6023305.32 table:a, index:IDX_MAKER_BIZ(maker_biz_order), range:[-inf,"0"), ("0",+inf], keep order:false 3960228 time:40ms, loops:3880, cop_task: {num: 118, max: 270.9ms, min: 159.1µs, avg: 47.2ms, p95: 112ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1s, tot_wait: 9.42ms, rpc_num: 118, rpc_time: 5.57s, copr_cache_hit_ratio: 0.53, build_task_duration: 20.3µs, max_distsql_concurrency: 6}, tikv_task:{proc max:432ms, min:0s, avg: 36.8ms, p80:44ms, p95:200ms, iters:4333, tasks:118}, scan_detail: {total_process_keys: 2154596, total_process_keys_size: 157285508, total_keys: 2154652, get_snapshot_time: 5.62ms, rocksdb: {delete_skipped_count: 1170, key_skipped_count: 2155766, block: {cache_hit_count: 2749, read_count: 2683, read_byte: 27.6 MB, read_time: 7.31ms}}} N/A N/A
└─Selection_66(Probe) cop[tikv] 4772540.62 eq(table_a.status, 2), ne(table_a.maker_user_id, 0), ne(table_a.taker_biz_order, "0"), ne(table_a.taker_user_id, 0) 3960228 time:57.2s, loops:4410, cop_task: {num: 6235, max: 545.2ms, min: 0s, avg: 69.1ms, p95: 249.5ms, max_proc_keys: 7140, p95_proc_keys: 2336, tot_proc: 2m2.7s, tot_wait: 3.22s, rpc_num: 5822, rpc_time: 7m11s, copr_cache_hit_ratio: 0.55, build_task_duration: 396.9ms, max_distsql_concurrency: 15, max_extra_concurrency: 5, store_batch_num: 413}, tikv_task:{proc max:548ms, min:0s, avg: 64.7ms, p80:108ms, p95:232ms, iters:25153, tasks:6235}, scan_detail: {total_process_keys: 3088492, total_process_keys_size: 2299212502, total_keys: 3520019, get_snapshot_time: 2.68s, rocksdb: {delete_skipped_count: 6870, key_skipped_count: 1521556, block: {cache_hit_count: 29409387, read_count: 1267864, read_byte: 4.35 GB, read_time: 2.85s}}} N/A N/A
└─TableRowIDScan_65 cop[tikv] 6023305.32 table:a, keep order:false 3960228 tikv_task:{proc max:548ms, min:0s, avg: 64.5ms, p80:108ms, p95:232ms, iters:25153, tasks:6235} N/A N/A
This SQL query results in a full table scan on table_a. How can I use an index?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
|
username: 
|
username: 
|
username: 
Yes, it feels like this will be faster.