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成为瓶颈的概率会更大,因此并发还是很有必要的。
如果返回字段没有任何运算,那么一般来说开不开并发对性能影响不大。