如图所示
建议将or改成in
1 个赞
一样的效果
explain analyze看下实际的执行计划，分别发上来
看到你发的这个文本，就不想看，不妨到代码块里 
全条件计划
Projection_13 14.69 12 root time:4.05s, loops:2, RU:17616.047239, Concurrency:OFF bigdata_sync.sync_md_order_out_bound_detail.id, bigdata_sync.erp_config.ds_code, bigdata_sync.sync_md_order_out_bound.business_data_source, bigdata_sync.sync_md_order_out_bound.outbound_no, bigdata_sync.sync_md_order_out_bound.kp_order_id, bigdata_sync.sync_md_order_out_bound.bill_type, bigdata_sync.sync_md_order_out_bound.depot_date 59.8 KB N/A
└─Projection_14 14.69 12 root time:4.05s, loops:2, Concurrency:OFF bigdata_sync.sync_md_order_out_bound.outbound_no, bigdata_sync.sync_md_order_out_bound.bill_type, bigdata_sync.sync_md_order_out_bound.depot_date, bigdata_sync.sync_md_order_out_bound.business_data_source, bigdata_sync.sync_md_order_out_bound.kp_order_id, bigdata_sync.sync_md_order_out_bound_detail.id, bigdata_sync.erp_config.ds_code 99.4 KB N/A
└─HashJoin_15 14.69 12 root time:4.05s, loops:2, build_hash_table:{total:3.34ms, fetch:3.32ms, build:25µs}, probe:{concurrency:5, total:20.2s, max:4.05s, probe:964.9ms, fetch and wait:19.3s} inner join, equal:[eq(bigdata_sync.sync_md_order_out_bound.id, bigdata_sync.sync_md_order_out_bound_detail.order_out_bound_id)] 33.7 KB 0 Bytes
├─Selection_81(Build) 12.00 12 root time:3.25ms, loops:2 not(isnull(bigdata_sync.sync_md_order_out_bound_detail.order_out_bound_id)) 1.24 KB N/A
│ └─Batch_Point_Get_80 12.00 12 root table:sync_MD_order_out_bound_detail, index:pk_uix(id) time:3.21ms, loops:3, BatchGet:{num_rpc:2, total_time:2.92ms}, time_detail: {total_kv_read_wall_time: 1.43ms, tikv_wall_time: 1.54ms}, scan_detail: {total_process_keys: 24, total_process_keys_size: 15537, total_keys: 36, get_snapshot_time: 81.6µs, rocksdb: {key_skipped_count: 12, block: {cache_hit_count: 128}}} keep order:false, desc:false N/A N/A
└─IndexHashJoin_25(Probe) 1162155.13 949579 root time:4.04s, loops:929, inner:{total:4.04s, concurrency:5, task:1, construct:43.7µs, fetch:1.72s, build:22.6µs, join:2.32s} inner join, inner:IndexLookUp_22, outer key:bigdata_sync.erp_config.md_erp_code, inner key:bigdata_sync.sync_md_order_out_bound.business_data_source, equal cond:eq(bigdata_sync.erp_config.md_erp_code, bigdata_sync.sync_md_order_out_bound.business_data_source) 124.3 MB N/A
├─TableReader_61(Build) 10.99 10 root time:1.2ms, loops:3, cop_task: {num: 1, max: 1.33ms, proc_keys: 11, tot_proc: 282.5µs, tot_wait: 124.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 13.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.29ms}} data:Selection_60 544 Bytes N/A
│ └─Selection_60 10.99 10 cop[tikv] tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 11, total_process_keys_size: 1563, total_keys: 12, get_snapshot_time: 50.8µs, rocksdb: {key_skipped_count: 11, block: {cache_hit_count: 6}}}, time_detail: {total_process_time: 282.5µs, total_wait_time: 124.7µs, tikv_wall_time: 699.3µs} not(isnull(bigdata_sync.erp_config.md_erp_code)) N/A N/A
│ └─TableFullScan_59 11.00 11 cop[tikv] table:ec tikv_task:{time:0s, loops:1} keep order:false, stats:partial[ds_code:unInitialized, md_erp_code:missing, md_erp_code:unInitialized] N/A N/A
└─IndexLookUp_22(Probe) 1162155.13 949579 root time:1.72s, loops:929, index_task: {total_time: 1.47s, fetch_handle: 1.04s, build: 109µs, wait: 431.9ms}, table_task: {total_time: 7.2s, num: 47, concurrency: 5}, next: {wait_index: 41.6ms, wait_table_lookup_build: 2.33ms, wait_table_lookup_resp: 1.31s} 22.6 MB N/A
├─Selection_20(Build) 1167143.46 953461 cop[tikv] time:916.2ms, loops:935, cop_task: {num: 21, max: 84.7ms, min: 830.5µs, avg: 64.2ms, p95: 84ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.24s, tot_wait: 1.78ms, copr_cache_hit_ratio: 0.00, build_task_duration: 42.1µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:21, total_time:1.35s}}, tikv_task:{proc max:76ms, min:0s, avg: 58.5ms, p80:76ms, p95:76ms, iters:1013, tasks:21}, scan_detail: {total_process_keys: 953461, total_process_keys_size: 80515598, total_keys: 953491, get_snapshot_time: 999.3µs, rocksdb: {delete_skipped_count: 18, key_skipped_count: 953479, block: {cache_hit_count: 1978, read_count: 2, read_byte: 63.8 KB, read_time: 109.3µs}}}, time_detail: {total_process_time: 1.24s, total_suspend_time: 3.79ms, total_wait_time: 1.78ms, total_kv_read_wall_time: 1.12s, tikv_wall_time: 1.25s} not(isnull(bigdata_sync.sync_md_order_out_bound.business_data_source)) N/A N/A
│ └─IndexRangeScan_18 1167143.46 953461 cop[tikv] table:oob, index:sync_idx_56(business_data_source) tikv_task:{proc max:72ms, min:0s, avg: 53.1ms, p80:68ms, p95:72ms, iters:1013, tasks:21} range: decided by [eq(bigdata_sync.sync_md_order_out_bound.business_data_source, bigdata_sync.erp_config.md_erp_code)], keep order:false N/A N/A
└─Selection_21(Probe) 1162155.13 949579 cop[tikv] time:7s, loops:1026, cop_task: {num: 98, max: 257.8ms, min: 901.7µs, avg: 97.6ms, p95: 236.6ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 8.19s, tot_wait: 9.22ms, copr_cache_hit_ratio: 0.00, build_task_duration: 16ms, max_distsql_concurrency: 7, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:100, total_time:9.56s}, rpc_errors:{not_leader:2}}, tikv_task:{proc max:228ms, min:0s, avg: 82.9ms, p80:128ms, p95:208ms, iters:1375, tasks:98}, scan_detail: {total_process_keys: 953461, total_process_keys_size: 864062765, total_keys: 1038045, get_snapshot_time: 4.93ms, rocksdb: {delete_skipped_count: 9619, key_skipped_count: 1844272, block: {cache_hit_count: 932837}}}, time_detail: {total_process_time: 8.19s, total_suspend_time: 415.9ms, total_wait_time: 9.22ms, total_kv_read_wall_time: 7.72s, tikv_wall_time: 8.75s} in(bigdata_sync.sync_md_order_out_bound.bill_type, "X12", "X31"), not(isnull(bigdata_sync.sync_md_order_out_bound.id)) N/A N/A
└─TableRowIDScan_19 1167143.46 953461 cop[tikv] table:oob tikv_task:{proc max:224ms, min:0s, avg: 78.7ms, p80:116ms, p95:200ms, iters:1375, tasks:98} keep order:false N/A N/A
少4个值的计划
Projection_13 10.00 8 root time:5.71ms, loops:2, RU:2.829830, Concurrency:OFF bigdata_sync.sync_md_order_out_bound_detail.id, bigdata_sync.erp_config.ds_code, bigdata_sync.sync_md_order_out_bound.business_data_source, bigdata_sync.sync_md_order_out_bound.outbound_no, bigdata_sync.sync_md_order_out_bound.kp_order_id, bigdata_sync.sync_md_order_out_bound.bill_type, bigdata_sync.sync_md_order_out_bound.depot_date 113.4 KB N/A
└─Projection_14 10.00 8 root time:5.7ms, loops:2, Concurrency:OFF bigdata_sync.sync_md_order_out_bound.outbound_no, bigdata_sync.sync_md_order_out_bound.bill_type, bigdata_sync.sync_md_order_out_bound.depot_date, bigdata_sync.sync_md_order_out_bound.business_data_source, bigdata_sync.sync_md_order_out_bound.kp_order_id, bigdata_sync.sync_md_order_out_bound_detail.id, bigdata_sync.erp_config.ds_code 113.5 KB N/A
└─HashJoin_30 10.00 8 root time:5.67ms, loops:2, build_hash_table:{total:1.34ms, fetch:1.32ms, build:21µs}, probe:{concurrency:5, total:27.2ms, max:5.46ms, probe:30.8µs, fetch and wait:27.1ms} inner join, equal:[eq(bigdata_sync.sync_md_order_out_bound.business_data_source, bigdata_sync.erp_config.md_erp_code)] 40.7 KB 0 Bytes
├─TableReader_69(Build) 10.99 10 root time:1.29ms, loops:2, cop_task: {num: 1, max: 1.28ms, proc_keys: 11, tot_proc: 243.7µs, tot_wait: 112.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 17.3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.23ms}} data:Selection_68 544 Bytes N/A
│ └─Selection_68 10.99 10 cop[tikv] tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 11, total_process_keys_size: 1563, total_keys: 12, get_snapshot_time: 42.4µs, rocksdb: {key_skipped_count: 11, block: {cache_hit_count: 6}}}, time_detail: {total_process_time: 243.7µs, total_wait_time: 112.9µs, tikv_wall_time: 635.8µs} not(isnull(bigdata_sync.erp_config.md_erp_code)) N/A N/A
│ └─TableFullScan_67 11.00 11 cop[tikv] table:ec tikv_task:{time:0s, loops:1} keep order:false, stats:partial[ds_code:unInitialized, md_erp_code:missing, md_erp_code:unInitialized] N/A N/A
└─IndexHashJoin_39(Probe) 8.00 8 root time:5.42ms, loops:2, inner:{total:2.97ms, concurrency:5, task:1, construct:45.8µs, fetch:2.89ms, build:17.1µs, join:37.8µs} inner join, inner:IndexLookUp_36, outer key:bigdata_sync.sync_md_order_out_bound_detail.order_out_bound_id, inner key:bigdata_sync.sync_md_order_out_bound.id, equal cond:eq(bigdata_sync.sync_md_order_out_bound_detail.order_out_bound_id, bigdata_sync.sync_md_order_out_bound.id) 134.4 KB N/A
├─Selection_51(Build) 8.00 8 root time:2.29ms, loops:3 not(isnull(bigdata_sync.sync_md_order_out_bound_detail.order_out_bound_id)) 27.7 KB N/A
│ └─Batch_Point_Get_50 8.00 8 root table:sync_MD_order_out_bound_detail, index:pk_uix(id) time:2.25ms, loops:4, BatchGet:{num_rpc:2, total_time:1.97ms}, time_detail: {total_kv_read_wall_time: 861.8µs, tikv_wall_time: 949.1µs}, scan_detail: {total_process_keys: 16, total_process_keys_size: 10364, total_keys: 24, get_snapshot_time: 51.9µs, rocksdb: {key_skipped_count: 8, block: {cache_hit_count: 88}}} keep order:false, desc:false N/A N/A
└─IndexLookUp_36(Probe) 8.00 1 root time:2.7ms, loops:2, index_task: {total_time: 1.08ms, fetch_handle: 1.07ms, build: 1.35µs, wait: 2.41µs}, table_task: {total_time: 1.46ms, num: 1, concurrency: 5}, next: {wait_index: 1.23ms, wait_table_lookup_build: 112.1µs, wait_table_lookup_resp: 1.32ms} 113.5 KB N/A
├─Selection_34(Build) 8.00 1 cop[tikv] time:1.05ms, loops:3, cop_task: {num: 1, max: 909.8µs, proc_keys: 1, tot_proc: 268.6µs, tot_wait: 72.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 36µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:868.5µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 118, total_keys: 4, get_snapshot_time: 29.7µs, rocksdb: {key_skipped_count: 3, block: {cache_hit_count: 5, read_count: 1, read_byte: 31.9 KB, read_time: 60.3µs}}}, time_detail: {total_process_time: 268.6µs, total_wait_time: 72.1µs, tikv_wall_time: 504.7µs} not(isnull(bigdata_sync.sync_md_order_out_bound.id)) N/A N/A
│ └─IndexRangeScan_32 8.00 1 cop[tikv] table:oob, index:pk_uix(id) tikv_task:{time:0s, loops:1} range: decided by [eq(bigdata_sync.sync_md_order_out_bound.id, bigdata_sync.sync_md_order_out_bound_detail.order_out_bound_id)], keep order:false N/A N/A
└─Selection_35(Probe) 8.00 1 cop[tikv] time:1.29ms, loops:2, cop_task: {num: 1, max: 1.16ms, proc_keys: 1, tot_proc: 256.1µs, tot_wait: 100.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 58.9µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.14ms}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 977, total_keys: 1, get_snapshot_time: 33.8µs, rocksdb: {block: {cache_hit_count: 11}}}, time_detail: {total_process_time: 256.1µs, total_wait_time: 100.7µs, tikv_wall_time: 662.7µs} in(bigdata_sync.sync_md_order_out_bound.bill_type, "X12", "X31"), not(isnull(bigdata_sync.sync_md_order_out_bound.business_data_source)) N/A N/A
└─TableRowIDScan_33 8.00 1 cop[tikv] table:oob tikv_task:{time:0s, loops:1} keep order:false N/A N/A
结果差了很多啊，第一个计划 join 的结果有快 100w 行了，第二个才 8 行。
是，减掉4个条件值后出现这个差异
数据多了，慢也是正常的呀
不是，4个主键4条数据。全表数据是100w行
/*+ USE_INDEX(oob, pk_uix) */ 加这个hint试试。
快的sql和慢的sql，大致有2个地方明显不同。
1，是连接顺序不同。
2，是oob这个表执行indexjoin的时候用到的索引不一样。
快的用的是oob.pk_uix,慢的用的是oob.sync_idx_56。
先指定一下索引看看，如果不行，再考虑调整连接顺序。
oob不要和ec先连接，这两个表的连接条件符合的数据太多。
时间相差多少先不管，为啥不用in？ 这么多or 看着别扭死了
为什么必须用in呢。这是动态sql编码方便，还会有复合主键的表
使用or容易执行计划出问题