Schema
ads_innovation_industry_company_chain_rt
+----------+-----------+----+---+-----------------+---------------------------------------------+
|Field |Type |Null|Key|Default |Extra |
+----------+-----------+----+---+-----------------+---------------------------------------------+
|id |bigint(20) |NO |PRI|null | |
|company_id|varchar(36)|NO |MUL| | |
|node_id |varchar(50)|NO | | | |
|chain_id |varchar(50)|NO | | | |
|status |varchar(50)|YES | | | |
|created_at|bigint(20) |YES |MUL|null | |
|update_ts |timestamp |YES | |CURRENT_TIMESTAMP|DEFAULT_GENERATED on update CURRENT_TIMESTAMP|
+----------+-----------+----+---+-----------------+---------------------------------------------+
数据量：817325
ads_company_basic_rt
+----------------+------------+----+---+-----------------+---------------------------------------------+
|Field |Type |Null|Key|Default |Extra |
+----------------+------------+----+---+-----------------+---------------------------------------------+
|id |bigint(20) |NO |PRI|null | |
|company_id |varchar(36) |NO |UNI| | |
|company_name |varchar(500)|YES | | | |
|ans_id |varchar(36) |YES |MUL| | |
|rating_lv2 |varchar(20) |YES | | | |
|score |double |YES | |null | |
|patent_value_sum|double |YES | |null | |
|address_province|varchar(250)|YES |MUL| | |
|address_city |varchar(250)|YES | | | |
|address_district|varchar(250)|YES | | | |
|is_listed |tinyint(3) |YES | |0 | |
|is_high_tech |tinyint(3) |YES | |0 | |
|is_high_quality |tinyint(3) |YES | |0 | |
|establish_time |int(11) |YES |MUL|null | |
|establish_year |smallint(4) |YES | |null | |
|reg_capital |double |YES | |null | |
|recent_round |tinyint(4) |YES | |null | |
|status |varchar(20) |YES | | | |
|created_at |bigint(20) |YES |MUL|null | |
|update_ts |timestamp |YES | |CURRENT_TIMESTAMP|DEFAULT_GENERATED on update CURRENT_TIMESTAMP|
|delivery_type |json |YES | |null | |
+----------------+------------+----+---+-----------------+---------------------------------------------+
数据量：1164411
后端给的 SQL
SELECT
DISTINCT rel.company_id
FROM
tffi_innovation.ads_innovation_industry_company_chain_rt rel
INNER JOIN ads_company_basic_rt basic ON rel.company_id = basic.company_id
WHERE
rel.STATUS = 'ACTIVE'
AND basic.STATUS IN ('ACTIVE', 'WARNING')
AND rel.chain_id in ('CH0000000003')
AND rel.created_at >= '1659312000000'
AND rel.created_at < '1661990399000'
order by
company_id asc
limit
500
EXPLAIN 结果
+----------------------------------+--------+---------+-----------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|id |estRows |task |access object |operator info |
+----------------------------------+--------+---------+-----------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit_18 |500.00 |root | |offset:0, count:500 |
|└─StreamAgg_132 |500.00 |root | |group by:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, funcs:firstrow(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)->tffi_innovation.ads_innovation_industry_company_chain_rt.company_id |
| └─IndexJoin_138 |1869.38 |root | |inner join, inner:IndexLookUp_137, outer key:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, inner key:tffi_innovation.ads_company_basic_rt.company_id, equal cond:eq(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, tffi_innovation.ads_company_basic_rt.company_id) |
| ├─Projection_152(Build) |1869.38 |root | |tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, tffi_innovation.ads_innovation_industry_company_chain_rt.chain_id, tffi_innovation.ads_innovation_industry_company_chain_rt.status, tffi_innovation.ads_innovation_industry_company_chain_rt.created_at |
| │ └─IndexLookUp_151 |1869.38 |root | | |
| │ ├─IndexFullScan_148(Build)|48130.36|cop[tikv]|table:rel, index:uk_company_id_node_id(company_id, node_id)|keep order:true |
| │ └─Selection_150(Probe) |1869.38 |cop[tikv]| |eq(tffi_innovation.ads_innovation_industry_company_chain_rt.chain_id, "CH0000000003"), eq(tffi_innovation.ads_innovation_industry_company_chain_rt.status, "ACTIVE"), ge(tffi_innovation.ads_innovation_industry_company_chain_rt.created_at, 1659312000000), lt(tffi_innovation.ads_innovation_industry_company_chain_rt.created_at, 1661990399000)|
| │ └─TableRowIDScan_149 |48130.36|cop[tikv]|table:rel |keep order:false |
| └─IndexLookUp_137(Probe) |1.00 |root | | |
| ├─IndexRangeScan_134(Build) |1.00 |cop[tikv]|table:basic, index:uk_company_id(company_id) |range: decided by [eq(tffi_innovation.ads_company_basic_rt.company_id, tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)], keep order:false |
| └─Selection_136(Probe) |1.00 |cop[tikv]| |in(tffi_innovation.ads_company_basic_rt.status, "ACTIVE", "WARNING") |
| └─TableRowIDScan_135 |1.00 |cop[tikv]|table:basic |keep order:false |
+----------------------------------+--------+---------+-----------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
表 ads_innovation_industry_company_chain_rt 全索引扫描，可能这部分耗时比较多。
EXPLAIN ANALYZE 结果
+----------------------------------+--------+-------+---------+-----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
|id |estRows |actRows|task |access object |execution info |operator info |memory |disk|
+----------------------------------+--------+-------+---------+-----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
|Limit_18 |500.00 |500 |root | |time:1.89s, loops:2 |offset:0, count:500 |N/A |N/A |
|└─StreamAgg_132 |500.00 |500 |root | |time:1.89s, loops:1 |group by:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, funcs:firstrow(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)->tffi_innovation.ads_innovation_industry_company_chain_rt.company_id |49.8 KB|N/A |
| └─IndexJoin_138 |1869.38 |2048 |root | |time:1.89s, loops:2, inner:{total:1.44s, concurrency:16, task:6, construct:6.5ms, fetch:1.43s, build:407.2µs}, probe:1.02ms |inner join, inner:IndexLookUp_137, outer key:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, inner key:tffi_innovation.ads_company_basic_rt.company_id, equal cond:eq(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, tffi_innovation.ads_company_basic_rt.company_id) |1.95 MB|N/A |
| ├─Projection_152(Build) |1869.38 |8832 |root | |time:1.89s, loops:15, Concurrency:16 |tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, tffi_innovation.ads_innovation_industry_company_chain_rt.chain_id, tffi_innovation.ads_innovation_industry_company_chain_rt.status, tffi_innovation.ads_innovation_industry_company_chain_rt.created_at |1.05 MB|N/A |
| │ └─IndexLookUp_151 |1869.38 |9856 |root | |time:1.89s, loops:16, index_task: {total_time: 1.69s, fetch_handle: 334.2ms, build: 160.9ms, wait: 1.2s}, table_task: {total_time: 30.3s, num: 48, concurrency: 16} | |13.2 MB|N/A |
| │ ├─IndexFullScan_148(Build)|48130.36|817325 |cop[tikv]|table:rel, index:uk_company_id_node_id(company_id, node_id)|time:278.6ms, loops:806, cop_task: {num: 3, max: 278.8ms, min: 15.7ms, avg: 178.5ms, p95: 278.8ms, max_proc_keys: 454975, p95_proc_keys: 454975, tot_proc: 516ms, rpc_num: 3, rpc_time: 535.5ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:256ms, min:12ms, p80:256ms, p95:256ms, iters:812, tasks:3}, scan_detail: {total_process_keys: 817325, total_keys: 936556, rocksdb: {delete_skipped_count: 0, key_skipped_count: 936553, block: {cache_hit_count: 1058, read_count: 1, read_byte: 20.0 KB}}} |keep order:true |N/A |N/A |
| │ └─Selection_150(Probe) |1869.38 |11212 |cop[tikv]| |time:24.4s, loops:94, cop_task: {num: 81, max: 1.48s, min: 2.89ms, avg: 361.7ms, p95: 986.1ms, max_proc_keys: 11854, p95_proc_keys: 11800, tot_proc: 24.6s, tot_wait: 3.7s, rpc_num: 81, rpc_time: 29.3s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:368ms, min:0s, p80:296ms, p95:348ms, iters:982, tasks:81}, scan_detail: {total_process_keys: 649665, total_keys: 710638, rocksdb: {delete_skipped_count: 0, key_skipped_count: 135873, block: {cache_hit_count: 4216156, read_count: 169, read_byte: 4.08 MB}}}|eq(tffi_innovation.ads_innovation_industry_company_chain_rt.chain_id, "CH0000000003"), eq(tffi_innovation.ads_innovation_industry_company_chain_rt.status, "ACTIVE"), ge(tffi_innovation.ads_innovation_industry_company_chain_rt.created_at, 1659312000000), lt(tffi_innovation.ads_innovation_industry_company_chain_rt.created_at, 1661990399000)|N/A |N/A |
| │ └─TableRowIDScan_149 |48130.36|649665 |cop[tikv]|table:rel |tikv_task:{proc max:368ms, min:0s, p80:296ms, p95:344ms, iters:982, tasks:81} |keep order:false |N/A |N/A |
| └─IndexLookUp_137(Probe) |1.00 |682 |root | |time:1.43s, loops:11, index_task: {total_time: 634.1ms, fetch_handle: 634.1ms, build: 4.76µs, wait: 51.6µs}, table_task: {total_time: 11.2s, num: 5, concurrency: 96} | |10.2 KB|N/A |
| ├─IndexRangeScan_134(Build) |1.00 |682 |cop[tikv]|table:basic, index:uk_company_id(company_id) |time:653.5ms, loops:16, cop_task: {num: 6, max: 278.4ms, min: 1.37ms, avg: 106.4ms, p95: 278.4ms, max_proc_keys: 238, p95_proc_keys: 238, tot_proc: 12ms, tot_wait: 616ms, rpc_num: 6, rpc_time: 638.3ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:4ms, min:0s, p80:0s, p95:4ms, iters:15, tasks:6}, scan_detail: {total_process_keys: 682, total_keys: 682, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 5105, read_count: 0, read_byte: 0 Bytes}}} |range: decided by [eq(tffi_innovation.ads_company_basic_rt.company_id, tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)], keep order:false |N/A |N/A |
| └─Selection_136(Probe) |1.00 |682 |cop[tikv]| |time:774.2ms, loops:10, cop_task: {num: 73, max: 302.2ms, min: 744.5µs, avg: 63.6ms, p95: 200.5ms, max_proc_keys: 38, p95_proc_keys: 26, tot_proc: 172ms, tot_wait: 4.38s, rpc_num: 73, rpc_time: 4.64s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:8ms, min:0s, p80:0s, p95:4ms, iters:75, tasks:73}, scan_detail: {total_process_keys: 682, total_keys: 682, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 7874, read_count: 0, read_byte: 0 Bytes}}} |in(tffi_innovation.ads_company_basic_rt.status, "ACTIVE", "WARNING") |N/A |N/A |
| └─TableRowIDScan_135 |1.00 |682 |cop[tikv]|table:basic |tikv_task:{proc max:8ms, min:0s, p80:0s, p95:4ms, iters:75, tasks:73} |keep order:false |N/A |N/A |
+----------------------------------+--------+-------+---------+-----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
优化器预估扫描的数据量远低于实际扫描的数据量，这点影响了执行计划，可以让表 ads_innovation_industry_company_chain_rt 走 created_at 索引，可以进行范围查询
手动 hint 走索引
EXPLAIN ANALYZE
SELECT
/*+ USE_INDEX(rel, idx_created_at) */
DISTINCT rel.company_id
FROM tffi_innovation.ads_innovation_industry_company_chain_rt rel
INNER JOIN ads_company_basic_rt basic ON rel.company_id = basic.company_id
WHERE rel.STATUS = 'ACTIVE'
AND basic.STATUS IN ('ACTIVE', 'WARNING')
AND rel.chain_id in ('CH0000000003')
AND rel.created_at >= '1659312000000'
AND rel.created_at < '1661990399000'
order by company_id asc
limit 500;
+--------------------------------+---------+-------+---------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
|id |estRows |actRows|task |access object |execution info |operator info |memory |disk|
+--------------------------------+---------+-------+---------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
|TopN_14 |500.00 |500 |root | |time:663.9ms, loops:2 |tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, offset:0, count:500 |58.6 KB|N/A |
|└─HashAgg_19 |8490.74 |3859 |root | |time:661.8ms, loops:17, partial_worker:{wall_time:661.575684ms, concurrency:16, task_num:18, tot_wait:10.560500791s, tot_exec:9.927144ms, tot_time:10.572799941s, max:661.39396ms, p95:661.39396ms}, final_worker:{wall_time:662.577001ms, concurrency:16, task_num:256, tot_wait:10.580767749s, tot_exec:10.590216ms, tot_time:10.59144665s, max:662.47622ms, p95:662.47622ms} |group by:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, funcs:firstrow(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)->tffi_innovation.ads_innovation_industry_company_chain_rt.company_id |3.25 MB|N/A |
| └─IndexHashJoin_26 |31744.80 |14726 |root | |time:660.3ms, loops:19, inner:{total:479.9ms, concurrency:16, task:8, construct:30.4ms, fetch:436ms, build:6.58ms, join:13.5ms} |inner join, inner:IndexLookUp_23, outer key:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, inner key:tffi_innovation.ads_company_basic_rt.company_id, equal cond:eq(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, tffi_innovation.ads_company_basic_rt.company_id)|6.81 MB|N/A |
| ├─IndexLookUp_39(Build) |31744.80 |14726 |root | |time:583ms, loops:20, index_task: {total_time: 172.2ms, fetch_handle: 172.1ms, build: 13.3µs, wait: 138.2µs}, table_task: {total_time: 5.06s, num: 15, concurrency: 16} | |3.17 MB|N/A |
| │ ├─IndexRangeScan_36(Build)|175809.72|149812 |cop[tikv]|table:rel, index:idx_created_at(created_at) |time:161.2ms, loops:153, cop_task: {num: 1, max: 160.8ms, proc_keys: 149812, tot_proc: 156ms, tot_wait: 4ms, rpc_num: 1, rpc_time: 160.8ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:152ms, loops:151}, scan_detail: {total_process_keys: 149812, total_keys: 449759, rocksdb: {delete_skipped_count: 108, key_skipped_count: 449866, block: {cache_hit_count: 302, read_count: 0, read_byte: 0 Bytes}}} |range:[1659312000000,1661990399000), keep order:false |N/A |N/A |
| │ └─Selection_38(Probe) |31744.80 |14726 |cop[tikv]| |time:2.21s, loops:32, cop_task: {num: 30, max: 384.6ms, min: 2.62ms, avg: 126.6ms, p95: 299.6ms, max_proc_keys: 14556, p95_proc_keys: 11705, tot_proc: 3.5s, tot_wait: 92ms, rpc_num: 30, rpc_time: 3.8s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:280ms, min:0s, p80:208ms, p95:252ms, iters:267, tasks:30}, scan_detail: {total_process_keys: 149812, total_keys: 170954, rocksdb: {delete_skipped_count: 0, key_skipped_count: 56923, block: {cache_hit_count: 849147, read_count: 0, read_byte: 0 Bytes}}}|eq(tffi_innovation.ads_innovation_industry_company_chain_rt.chain_id, "CH0000000003"), eq(tffi_innovation.ads_innovation_industry_company_chain_rt.status, "ACTIVE") |N/A |N/A |
| │ └─TableRowIDScan_37 |175809.72|149812 |cop[tikv]|table:rel |tikv_task:{proc max:280ms, min:0s, p80:204ms, p95:244ms, iters:267, tasks:30} |keep order:false |N/A |N/A |
| └─IndexLookUp_23(Probe) |1.00 |8910 |root | |time:425.3ms, loops:22, index_task: {total_time: 223ms, fetch_handle: 222.9ms, build: 10.8µs, wait: 39.2µs}, table_task: {total_time: 3.86s, num: 12, concurrency: 128} | |13.1 KB|N/A |
| ├─IndexRangeScan_20(Build)|1.00 |8910 |cop[tikv]|table:basic, index:uk_company_id(company_id)|time:222.2ms, loops:30, cop_task: {num: 56, max: 36.7ms, min: 1.26ms, avg: 13.9ms, p95: 32.1ms, max_proc_keys: 533, p95_proc_keys: 500, tot_proc: 316ms, tot_wait: 332ms, rpc_num: 56, rpc_time: 780.1ms, copr_cache_hit_ratio: 0.02}, tikv_task:{proc max:20ms, min:0s, p80:8ms, p95:16ms, iters:145, tasks:56}, scan_detail: {total_process_keys: 8624, total_keys: 8624, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 67098, read_count: 0, read_byte: 0 Bytes}}} |range: decided by [eq(tffi_innovation.ads_company_basic_rt.company_id, tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)], keep order:false |N/A |N/A |
| └─Selection_22(Probe) |1.00 |8910 |cop[tikv]| |time:295.2ms, loops:29, cop_task: {num: 190, max: 32.3ms, min: 508.5µs, avg: 11.7ms, p95: 28ms, max_proc_keys: 219, p95_proc_keys: 119, tot_proc: 612ms, tot_wait: 1.47s, rpc_num: 190, rpc_time: 2.23s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:8ms, min:0s, p80:4ms, p95:4ms, iters:320, tasks:190}, scan_detail: {total_process_keys: 8910, total_keys: 8937, rocksdb: {delete_skipped_count: 7, key_skipped_count: 63, block: {cache_hit_count: 98005, read_count: 2, read_byte: 56.9 KB}}} |in(tffi_innovation.ads_company_basic_rt.status, "ACTIVE", "WARNING") |N/A |N/A |
| └─TableRowIDScan_21 |1.00 |8910 |cop[tikv]|table:basic |tikv_task:{proc max:8ms, min:0s, p80:4ms, p95:4ms, iters:320, tasks:190} |keep order:false |N/A |N/A |
+--------------------------------+---------+-------+---------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
可以看到耗时明显下降，范围扫描数据量少了很多。
手动 hint 走 TiFlash
EXPLAIN ANALYZE
SELECT
rel.company_id
FROM
(
SELECT
/*+ READ_FROM_STORAGE(tiflash[t1]) */
DISTINCT company_id
FROM
tffi_innovation.ads_innovation_industry_company_chain_rt t1
WHERE
chain_id = 'CH0000000003'
AND created_at >= '1659312000000'
AND created_at < '1661990399000'
AND status = 'ACTIVE'
) rel
INNER JOIN ads_company_basic_rt basic ON rel.company_id = basic.company_id
WHERE
basic.STATUS IN ('ACTIVE', 'WARNING')
order by
company_id asc
limit
500;
+------------------------------+---------+-------+-----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
|id |estRows |actRows|task |access object |execution info |operator info |memory |disk|
+------------------------------+---------+-------+-----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
|TopN_17 |500.00 |500 |root | |time:163.9ms, loops:2 |tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, offset:0, count:500 |63.1 KB|N/A |
|└─IndexJoin_28 |8490.74 |3859 |root | |time:161.6ms, loops:6, inner:{total:224.9ms, concurrency:16, task:6, construct:7.53ms, fetch:215.4ms, build:1.93ms}, probe:1.85ms |inner join, inner:IndexLookUp_27, outer key:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, inner key:tffi_innovation.ads_company_basic_rt.company_id, equal cond:eq(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, tffi_innovation.ads_company_basic_rt.company_id) |2.13 MB|N/A |
| ├─HashAgg_73(Build) |8490.74 |3859 |root | |time:117.8ms, loops:18, partial_worker:{wall_time:117.526052ms, concurrency:16, task_num:6, tot_wait:1.859106926s, tot_exec:3.103979ms, tot_time:1.86302439s, max:117.287407ms, p95:117.287407ms}, final_worker:{wall_time:117.95203ms, concurrency:16, task_num:70, tot_wait:1.876637931s, tot_exec:6.6558ms, tot_time:1.883328223s, max:117.873958ms, p95:117.873958ms} |group by:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, funcs:firstrow(tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)->tffi_innovation.ads_innovation_industry_company_chain_rt.company_id |1.88 MB|N/A |
| │ └─TableReader_74 |8490.74 |3859 |root | |time:116.3ms, loops:7, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} |data:HashAgg_63 |40.7 KB|N/A |
| │ └─HashAgg_63 |8490.74 |3859 |batchCop[tiflash]| |tiflash_task:{time:114.7ms, loops:256, threads:1} |group by:tffi_innovation.ads_innovation_industry_company_chain_rt.company_id, |N/A |N/A |
| │ └─Selection_72 |31744.80 |14726 |batchCop[tiflash]| |tiflash_task:{time:106.8ms, loops:14, threads:2} |eq(tffi_innovation.ads_innovation_industry_company_chain_rt.chain_id, "CH0000000003"), eq(tffi_innovation.ads_innovation_industry_company_chain_rt.status, "ACTIVE"), ge(tffi_innovation.ads_innovation_industry_company_chain_rt.created_at, 1659312000000), lt(tffi_innovation.ads_innovation_industry_company_chain_rt.created_at, 1661990399000)|N/A |N/A |
| │ └─TableFullScan_71 |817325.00|817326 |batchCop[tiflash]|table:t1 |tiflash_task:{time:82.8ms, loops:14, threads:2} |keep order:false |N/A |N/A |
| └─IndexLookUp_27(Probe) |1.00 |3859 |root | |time:208.5ms, loops:13, index_task: {total_time: 105.4ms, fetch_handle: 105.4ms, build: 6.4µs, wait: 57µs}, table_task: {total_time: 2.4s, num: 7, concurrency: 96} | |27.3 KB|N/A |
| ├─IndexRangeScan_24(Build)|1.00 |3859 |cop[tikv] |table:basic, index:uk_company_id(company_id)|time:105ms, loops:19, cop_task: {num: 42, max: 20.9ms, min: 1.45ms, avg: 9.98ms, p95: 20.4ms, max_proc_keys: 204, p95_proc_keys: 197, tot_proc: 288ms, tot_wait: 84ms, rpc_num: 42, rpc_time: 418.6ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:12ms, min:0s, p80:4ms, p95:8ms, iters:96, tasks:42}, scan_detail: {total_process_keys: 3859, total_keys: 3859, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 30555, read_count: 0, read_byte: 0 Bytes}}} |range: decided by [eq(tffi_innovation.ads_company_basic_rt.company_id, tffi_innovation.ads_innovation_industry_company_chain_rt.company_id)], keep order:false |N/A |N/A |
| └─Selection_26(Probe) |1.00 |3859 |cop[tikv] | |time:60.3ms, loops:14, cop_task: {num: 119, max: 12.7ms, min: 548.2µs, avg: 4.1ms, p95: 8.93ms, max_proc_keys: 128, p95_proc_keys: 81, tot_proc: 208ms, tot_wait: 196ms, rpc_num: 119, rpc_time: 486.7ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:8ms, min:0s, p80:4ms, p95:4ms, iters:172, tasks:119}, scan_detail: {total_process_keys: 3859, total_keys: 3869, rocksdb: {delete_skipped_count: 3, key_skipped_count: 22, block: {cache_hit_count: 43187, read_count: 0, read_byte: 0 Bytes}}}|in(tffi_innovation.ads_company_basic_rt.status, "ACTIVE", "WARNING") |N/A |N/A |
| └─TableRowIDScan_25 |1.00 |3859 |cop[tikv] |table:basic |tikv_task:{proc max:8ms, min:0s, p80:4ms, p95:4ms, iters:172, tasks:119} |keep order:false |N/A |N/A |
+------------------------------+---------+-------+-----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
对表 ads_innovation_industry_company_chain_rt 可以走 TiFlash 查询，因为只需要查询指定条件过滤后的 company_id ，最后实际获取到的 company_id 数量很小，拿这部分数据再去 TiKV 走 IndexRangeScan 效率会高很多。
最终方案
走 TiFlash + TiKV 混合查询
文章来源: https://www.upx8.com/4514
如有侵权请联系:admin#unsafe.sh
如有侵权请联系:admin#unsafe.sh