explain analyze 中的Projection_9 为什么会有Concurrency:5 ?

Raymond · 2023 年8 月 10 日 15:44

请问下各位老师，对tpch库下面的customer和orders表做实验，发现执行计划里面有explain analyze 中的Projection_9 有Concurrency:5 ，不是很理解，Projection 不是投影嘛?

mysql> explain analyze select cname,sum(oprice) from ( select c.C_NAME as ‘cname’,o.O_CUSTKEY as ‘oid’ ,o.O_TOTALPRICE as ‘oprice’ from customer c join orders o on c.C_CUSTKEY = o.O_CUSTKEY) t1 group by cname;
±--------------------------------±-----------±--------±----------±--------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------±---------±--------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±--------------------------------±-----------±--------±----------±--------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------±---------±--------+
| Projection_9 | 148224.00 | 99996 | root | | time:2.31s, loops:99, Concurrency:5 | tpch2.customer.c_name, Column#18 | 743.6 KB | N/A |
| └─HashAgg_10 | 148224.00 | 99996 | root | | time:2.31s, loops:99 | group by:tpch2.customer.c_name, funcs:sum(tpch2.orders.o_totalprice)->Column#18, funcs:firstrow(tpch2.customer.c_name)->tpch2.customer.c_name | 22.2 MB | 0 Bytes |
| └─HashJoin_21 | 1503229.30 | 1498900 | root | | time:1.09s, loops:1466, build_hash_table:{total:61ms, fetch:30.2ms, build:30.8ms}, probe:{concurrency:5, total:11.4s, max:2.29s, probe:7.77s, fetch:3.67s} | inner join, equal:[eq(tpch2.customer.c_custkey, tpch2.orders.o_custkey)] | 16.2 MB | 0 Bytes |
| ├─TableReader_25(Build) | 150000.00 | 150000 | root | | time:30.3ms, loops:149, cop_task: {num: 11, max: 17ms, min: 615.9µs, avg: 4.4ms, p95: 17ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 1ms, tot_wait: 1ms, rpc_num: 11, rpc_time: 48.3ms, copr_cache_hit_ratio: 0.64, distsql_concurrency: 15} | data:TableFullScan_24 | 2.72 MB | N/A |
| │ └─TableFullScan_24 | 150000.00 | 150000 | cop[tikv] | table:c | tikv_task:{proc max:20ms, min:0s, avg: 5.73ms, p80:13ms, p95:20ms, iters:190, tasks:11}, scan_detail: {total_process_keys: 4736, total_process_keys_size: 967579, total_keys: 4740, get_snapshot_time: 1.46ms, rocksdb: {key_skipped_count: 4736, block: {cache_hit_count: 47}}} | keep order:false | N/A | N/A |
| └─TableReader_23(Probe) | 1498900.00 | 1498900 | root | | time:137.5ms, loops:1468, cop_task: {num: 55, max: 372.8ms, min: 1.72ms, avg: 52.6ms, p95: 140.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 98ms, tot_wait: 3ms, rpc_num: 55, rpc_time: 2.89s, copr_cache_hit_ratio: 0.69, distsql_concurrency: 15} | data:TableFullScan_22 | 9.19 MB | N/A |
| └─TableFullScan_22 | 1498900.00 | 1498900 | cop[tikv] | table:o | tikv_task:{proc max:61ms, min:0s, avg: 19.4ms, p80:36ms, p95:43ms, iters:1682, tasks:55}, scan_detail: {total_process_keys: 181750, total_process_keys_size: 27525710, total_keys: 183409, get_snapshot_time: 5.01ms, rocksdb: {delete_skipped_count: 281, key_skipped_count: 183673, block: {cache_hit_count: 588}}} | keep order:false | N/A | N/A |
±--------------------------------±-----------±--------±----------±--------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------±---------±--------+
7 rows in set (2.31 sec)

redgame · 2023 年8 月 10 日 23:33

执行该操作时使用的并发线程数，尽力利用系统的多核心处理能力，提高查询性能

tidb菜鸟一只 · 2023 年8 月 11 日 00:47

代表projection算子的并发度，4之前的版本可以用参数 tidb_projection_concurrency来进行设置，5.0开始该版本参数被废止，改为 tidb_executor_concurrency参数来统一设置各个 SQL 算子的并发度

Raymond · 2023 年8 月 11 日 02:31

请问一下，projection的并发意义在哪里呢

cassblanca · 2023 年8 月 11 日 06:45

兄台是不是把projection理解为了类似mongodb的投影，Tidb的projection算子一般用于进行表达式计算，资源充足情况下，理论并行计算要比单进程效率高吧

Raymond · 2023 年8 月 11 日 13:06

请问兄台，进行表达式计算不是有专门的算子进行嘛，比如hashagg 这种嘛
我觉得应该就是你说的这种，projection 应该不是单纯的投影

人如其名 · 2023 年8 月 12 日 10:01

set tidb_projection_concurrency=1;
explain analyze select cast(O_ORDERDATE as char) col1,cast(O_TOTALPRICE*O_SHIPPRIORITY*10.1/20.3 as char) col2,case when O_SHIPPRIORITY > 0 then 1 else 0 end as col3 from orders;

mysql> explain analyze select cast(O_ORDERDATE as char) col1,cast(O_TOTALPRICE*O_SHIPPRIORITY*10.1/20.3 as char) col2,case when O_SHIPPRIORITY > 0 then 1 else 0 end as col3 from orders;
+-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                      | estRows    | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                          | operator info                                                                                                                                                                                                                                                      | memory   | disk |
+-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_3            | 1500000.00 | 1500000 | root      |               | time:729.4ms, loops:1469, RU:3712.950228, Concurrency:1                                                                                                                                                                                                                                                 | cast(tpch1.orders.o_orderdate, var_string(5))->Column#10, cast(div(mul(mul(tpch1.orders.o_totalprice, cast(tpch1.orders.o_shippriority, decimal(20,0) BINARY)), 10.1), 20.3), var_string(5))->Column#11, case(gt(tpch1.orders.o_shippriority, 0), 1, 0)->Column#12 | 126.5 KB | N/A  |
| └─TableReader_5         | 1500000.00 | 1500000 | root      |               | time:11.6ms, loops:1469, cop_task: {num: 54, max: 46.8ms, min: 926.9µs, avg: 14.9ms, p95: 33.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 660.9ms, tot_wait: 7.93ms, rpc_num: 54, rpc_time: 805.1ms, copr_cache_hit_ratio: 0.00, build_task_duration: 15µs, max_distsql_concurrency: 3}   | data:TableFullScan_4                                                                                                                                                                                                                                               | 10.7 MB  | N/A  |
|   └─TableFullScan_4     | 1500000.00 | 1500000 | cop[tikv] | table:orders  | tikv_task:{proc max:26ms, min:0s, avg: 9.57ms, p80:16ms, p95:23ms, iters:1679, tasks:54}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 227213890, total_keys: 1500054, get_snapshot_time: 1.03ms, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 7700}}}         | keep order:false                                                                                                                                                                                                                                                   | N/A      | N/A  |
+-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
3 rows in set (0.73 sec)

set tidb_projection_concurrency=5;
explain analyze select cast(O_ORDERDATE as char) col1,cast(O_TOTALPRICE*O_SHIPPRIORITY*10.1/20.3 as char) col2,case when O_SHIPPRIORITY > 0 then 1 else 0 end as col3 from orders;

mysql> explain analyze select cast(O_ORDERDATE as char) col1,cast(O_TOTALPRICE*O_SHIPPRIORITY*10.1/20.3 as char) col2,case when O_SHIPPRIORITY > 0 then 1 else 0 end as col3 from orders;
+-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                      | estRows    | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                          | operator info                                                                                                                                                                                                                                                      | memory   | disk |
+-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_3            | 1500000.00 | 1500000 | root      |               | time:557.1ms, loops:1469, RU:3808.676503, Concurrency:5                                                                                                                                                                                                                                                 | cast(tpch1.orders.o_orderdate, var_string(5))->Column#10, cast(div(mul(mul(tpch1.orders.o_totalprice, cast(tpch1.orders.o_shippriority, decimal(20,0) BINARY)), 10.1), 20.3), var_string(5))->Column#11, case(gt(tpch1.orders.o_shippriority, 0), 1, 0)->Column#12 | 534.1 KB | N/A  |
| └─TableReader_5         | 1500000.00 | 1500000 | root      |               | time:95.8ms, loops:1469, cop_task: {num: 54, max: 82.1ms, min: 920.6µs, avg: 23.4ms, p95: 57.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 948.1ms, tot_wait: 29.1ms, rpc_num: 54, rpc_time: 1.26s, copr_cache_hit_ratio: 0.00, build_task_duration: 17.8µs, max_distsql_concurrency: 3}   | data:TableFullScan_4                                                                                                                                                                                                                                               | 8.04 MB  | N/A  |
|   └─TableFullScan_4     | 1500000.00 | 1500000 | cop[tikv] | table:orders  | tikv_task:{proc max:45ms, min:0s, avg: 12.1ms, p80:23ms, p95:30ms, iters:1679, tasks:54}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 227213890, total_keys: 1500054, get_snapshot_time: 1.01ms, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 7700}}}         | keep order:false                                                                                                                                                                                                                                                   | N/A      | N/A  |
+-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
3 rows in set (0.56 sec)

当返回字段较多，运算复杂的情况下，projecion可能会成为瓶颈。在简单测试语句中都可以看到性能有一些差距，如果在实际生产中遇到很复杂的运算，那么projection成为瓶颈的概率会更大，因此并发还是很有必要的。
如果返回字段没有任何运算，那么一般来说开不开并发对性能影响不大。

Raymond · 2023 年8 月 12 日 15:50

你的这种例子我可以理解，但是我的那个例子，返回的字段数量比较少而且也什么运算 Projection 还用5个并发有很大意义嘛

人如其名 · 2023 年8 月 13 日 13:10

当一个并发就可以足够快的时候，实际就算是用了5个并发也不会占用太多资源，当一个并发不够快的时候5个并发就会尽量使用资源来提升速度。因此不管有没有意义，对我们使用者来说并没有坏处，也并没有因为并发高效率没有提升而浪费CPU资源。

Raymond · 2023 年8 月 13 日 15:25

好吧，谢谢大佬

system · 2023 年10 月 12 日 15:25

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。