调优 TiFlash 性能
本文介绍如何通过合理规划机器资源和调整 TiDB 参数来调优 TiFlash 的性能。通过遵循这些方法，你的 TiFlash 集群可以达到最佳性能。
规划资源
如果你想节省机器资源且对隔离没有要求，可以使用 TiKV 和 TiFlash 混合部署的方式。建议为 TiKV 和 TiFlash 分别预留足够的资源，并且不要共享磁盘。
调整 TiDB 参数
本节介绍如何通过调整 TiDB 参数来提高 TiFlash 性能，包括：
强制启用 MPP 模式
MPP 执行计划可以充分利用分布式计算资源，从而显著提高批量数据查询的效率。当优化器没有为查询生成 MPP 执行计划时，你可以强制启用 MPP 模式：
变量 tidb_enforce_mpp 控制是否忽略优化器的成本估算，强制使用 TiFlash 的 MPP 模式执行查询。要强制启用 MPP 模式，运行以下命令：
set @@tidb_enforce_mpp = ON;以下示例展示了启用 tidb_enforce_mpp 前后的查询结果。在启用此变量之前，TiDB 需要从 TiKV 读取数据并在 TiDB 中执行 Join 和 Aggregation。启用 tidb_enforce_mpp 后，Join 和 Aggregation 被下推到 TiFlash。此外，由于优化器不一定会生成 MPP 执行计划，通过启用 tidb_enforce_mpp，你可以强制优化器生成 MPP 执行计划。
启用 MPP 模式之前：
mysql> explain analyze select o_orderpriority, count(*) as order_count from orders where o_orderdate >= '1995-01-01' and o_orderdate < date_add('1995-01-01', interval '3' month) and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate) group by o_orderpriority;
+--------------------------------------+--------------+-----------+-----------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+--------------+-----------+-----------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------+
| Projection_9 | 1.00 | 5 | root | | time:22.8s, loops:5, Concurrency:OFF | tpch_100.orders.o_orderpriority, Column#27 | 1016 Bytes | N/A |
| └─HashAgg_10 | 1.00 | 5 | root | | time:22.8s, loops:5, partial_worker:{wall_time:22.817403422s, concurrency:5, task_num:5027, tot_wait:1m52.560509138s, tot_exec:1.523754659s, tot_time:1m54.086196522s, max:22.817363394s, p95:22.817363394s}, final_worker:{wall_time:22.817416046s, concurrency:5, task_num:20, tot_wait:1m54.086784741s, tot_exec:155.94µs, tot_time:1m54.086948982s, max:22.817396284s, p95:22.817396284s} | group by:tpch_100.orders.o_orderpriority, funcs:count(1)->Column#27, funcs:firstrow(tpch_100.orders.o_orderpriority)->tpch_100.orders.o_orderpriority | 511.7 KB | N/A |
| └─IndexHashJoin_18 | 4429262.13 | 5145138 | root | | time:22.8s, loops:5028, inner:{total:1m52.6s, concurrency:5, task:227, construct:5.17s, fetch:1m40s, build:1.22s, join:7.49s} | semi join, inner:IndexLookUp_15, outer key:tpch_100.orders.o_orderkey, inner key:tpch_100.lineitem.l_orderkey, equal cond:eq(tpch_100.orders.o_orderkey, tpch_100.lineitem.l_orderkey) | 46.8 MB | N/A |
| ├─TableReader_37(Build) | 5536577.67 | 5610951 | root | | time:4.89s, loops:5467, cop_task: {num: 2127, max: 408.7ms, min: 277.5µs, avg: 81.6ms, p95: 249.7ms, max_proc_keys: 226272, p95_proc_keys: 220128, tot_proc: 2m50.2s, tot_wait: 40ms, rpc_num: 2127, rpc_time: 2m53.6s, copr_cache_hit_ratio: 0.02, distsql_concurrency: 15} | data:Selection_36 | 3.42 MB | N/A |
| │ └─Selection_36 | 5536577.67 | 5610951 | cop[tikv] | | tikv_task:{proc max:402ms, min:0s, avg: 79.7ms, p80:151ms, p95:245ms, iters:155122, tasks:2127}, scan_detail: {total_process_keys: 149478336, total_process_keys_size: 22705157593, total_keys: 149480413, get_snapshot_time: 187.9ms, rocksdb: {key_skipped_count: 149478336, block: {cache_hit_count: 393690}}} | ge(tpch_100.orders.o_orderdate, 1995-01-01 00:00:00.000000), lt(tpch_100.orders.o_orderdate, 1995-04-01 00:00:00.000000) | N/A | N/A |
| │ └─TableFullScan_35 | 150000000.00 | 150000000 | cop[tikv] | table:orders | tikv_task:{proc max:382ms, min:0s, avg: 74.6ms, p80:141ms, p95:229ms, iters:155122, tasks:2127} | keep order:false | N/A | N/A |
| └─IndexLookUp_15(Probe) | 22018496.89 | 14185840 | root | | time:1m33.4s, loops:14204, index_task: {total_time: 1m10.8s, fetch_handle: 1m10.7s, build: 3.9ms, wait: 13.3ms}, table_task: {total_time: 1m44.4s, num: 1989, concurrency: 5} | | 17.9 KB | N/A |
| ├─IndexRangeScan_12(Build) | 27523121.11 | 22441356 | cop[tikv] | table:lineitem, index:PRIMARY(L_ORDERKEY, L_LINENUMBER) | time:1m8.8s, loops:23635, cop_task: {num: 12537, max: 179.3ms, min: 331.4µs, avg: 22.7ms, p95: 66.9ms, max_proc_keys: 13755, p95_proc_keys: 5088, tot_proc: 3m44s, tot_wait: 129ms, rpc_num: 12537, rpc_time: 4m44s, copr_cache_hit_ratio: 0.06, distsql_concurrency: 15}, tikv_task:{proc max:170ms, min:0s, avg: 18.8ms, p80:29ms, p95:62ms, iters:68823, tasks:12537}, scan_detail: {total_process_keys: 21985403, total_process_keys_size: 1165226359, total_keys: 27492546, get_snapshot_time: 563.1ms, rocksdb: {key_skipped_count: 21985403, block: {cache_hit_count: 44004354}}} | range: decided by [eq(tpch_100.lineitem.l_orderkey, tpch_100.orders.o_orderkey)], keep order:false | N/A | N/A |
| └─Selection_14(Probe) | 22018496.89 | 14185840 | cop[tikv] | | time:1m34.8s, loops:16882, cop_task: {num: 12057, max: 240.4ms, min: 328.5µs, avg: 20.4ms, p95: 73.1ms, max_proc_keys: 15005, p95_proc_keys: 6118, tot_proc: 3m39.8s, tot_wait: 300ms, rpc_num: 12057, rpc_time: 4m5.7s, copr_cache_hit_ratio: 0.28, distsql_concurrency: 15}, tikv_task:{proc max:230ms, min:0s, avg: 27.5ms, p80:45ms, p95:82ms, iters:74165, tasks:12057}, scan_detail: {total_process_keys: 19916937, total_process_keys_size: 3957799619, total_keys: 24053143, get_snapshot_time: 1.07s, rocksdb: {key_skipped_count: 19254567, block: {cache_hit_count: 37816908}}} | lt(tpch_100.lineitem.l_commitdate, tpch_100.lineitem.l_receiptdate) | N/A | N/A |
| └─TableRowIDScan_13 | 27523121.11 | 22441356 | cop[tikv] | table:lineitem | tikv_task:{proc max:230ms, min:0s, avg: 27.2ms, p80:45ms, p95:81ms, iters:74165, tasks:12057} | keep order:false | N/A | N/A |
+--------------------------------------+--------------+-----------+-----------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------+
10 rows in set (22.82 sec)启用 MPP 模式：
mysql> set @@tidb_enforce_mpp = ON;
Query OK, 0 rows affected (0.00 sec)启用 MPP 模式后：
mysql> explain analyze select o_orderpriority, count(*) as order_count from orders where o_orderdate >= '1995-01-01' and o_orderdate < date_add('1995-01-01', interval '3' month) and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate) group by o_orderpriority;
+--------------------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_59 | 1.00 | 5 | root | | time:5.97s, loops:2, cop_task: {num: 8, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_58 | N/A | N/A |
| └─ExchangeSender_58 | 1.00 | 5 | mpp[tiflash] | | tiflash_task:{proc max:5.97s, min:5.97s, avg: 5.97s, p80:5.97s, p95:5.97s, iters:5, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_10 | 1.00 | 5 | mpp[tiflash] | | tiflash_task:{proc max:5.97s, min:5.96s, avg: 5.97s, p80:5.97s, p95:5.97s, iters:5, tasks:3, threads:60} | tpch_100.orders.o_orderpriority, Column#27 | N/A | N/A |
| └─Projection_54 | 1.00 | 5 | mpp[tiflash] | | tiflash_task:{proc max:5.97s, min:5.96s, avg: 5.97s, p80:5.97s, p95:5.97s, iters:5, tasks:3, threads:60} | Column#27, tpch_100.orders.o_orderpriority | N/A | N/A |
| └─HashAgg_55 | 1.00 | 5 | mpp[tiflash] | | tiflash_task:{proc max:5.97s, min:5.96s, avg: 5.97s, p80:5.97s, p95:5.97s, iters:5, tasks:3, threads:60} | group by:tpch_100.orders.o_orderpriority, funcs:sum(Column#32)->Column#27, funcs:firstrow(tpch_100.orders.o_orderpriority)->tpch_100.orders.o_orderpriority, stream_count: 20 | N/A | N/A |
| └─ExchangeReceiver_57 | 1.00 | 15 | mpp[tiflash] | | tiflash_task:{proc max:5.97s, min:5.96s, avg: 5.97s, p80:5.97s, p95:5.97s, iters:5, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| └─ExchangeSender_56 | 1.00 | 15 | mpp[tiflash] | | tiflash_task:{proc max:5.96s, min:0s, avg: 1.99s, p80:5.96s, p95:5.96s, iters:768, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100.orders.o_orderpriority, collate: utf8mb4_bin], stream_count: 20 | N/A | N/A |
| └─HashAgg_52 | 1.00 | 15 | mpp[tiflash] | | tiflash_task:{proc max:5.96s, min:0s, avg: 1.99s, p80:5.96s, p95:5.96s, iters:768, tasks:3, threads:60} | group by:tpch_100.orders.o_orderpriority, funcs:count(1)->Column#32 | N/A | N/A |
| └─HashJoin_41 | 4429262.13 | 5145138 | mpp[tiflash] | | tiflash_task:{proc max:5.95s, min:0s, avg: 1.98s, p80:5.95s, p95:5.95s, iters:118, tasks:3, threads:60} | semi join, equal:[eq(tpch_100.orders.o_orderkey, tpch_100.lineitem.l_orderkey)], stream_count: 20 | N/A | N/A |
| ├─ExchangeReceiver_26(Build) | 480030321.60 | 379356474 | mpp[tiflash] | | tiflash_task:{proc max:3.26s, min:0s, avg: 1.09s, p80:3.26s, p95:3.26s, iters:42469, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| │ └─ExchangeSender_25 | 480030321.60 | 379356474 | mpp[tiflash] | | tiflash_task:{proc max:3.37s, min:0s, avg: 1.12s, p80:3.37s, p95:3.37s, iters:9292, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100.lineitem.l_orderkey, collate: binary], stream_count: 20 | N/A | N/A |
| │ └─Projection_60 | 480030321.60 | 379356474 | mpp[tiflash] | | tiflash_task:{proc max:819.1ms, min:0s, avg: 273ms, p80:819.1ms, p95:819.1ms, iters:9292, tasks:3, threads:60} | tpch_100.lineitem.l_orderkey | N/A | N/A |
| │ └─Selection_24 | 480030321.60 | 379356474 | mpp[tiflash] | | tiflash_task:{proc max:817.1ms, min:0s, avg: 272.4ms, p80:817.1ms, p95:817.1ms, iters:9292, tasks:3, threads:60} | lt(tpch_100.lineitem.l_commitdate, tpch_100.lineitem.l_receiptdate) | N/A | N/A |
| │ └─TableFullScan_23 | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:328.1ms, min:0s, avg: 109.4ms, p80:328.1ms, p95:328.1ms, iters:9292, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73475, total_skipped_packs:13452, total_scanned_rows:600230532, total_skipped_rows:109887612, total_rs_index_load_time: 35ms, total_read_time: 34065ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
| └─ExchangeReceiver_22(Probe) | 5536577.67 | 5610951 | mpp[tiflash] | | tiflash_task:{proc max:2.5s, min:0s, avg: 833.7ms, p80:2.5s, p95:2.5s, iters:669, tasks:3, threads:60} | | N/A | N/A |
| └─ExchangeSender_21 | 5536577.67 | 5610951 | mpp[tiflash] | | tiflash_task:{proc max:5.93s, min:0s, avg: 1.98s, p80:5.93s, p95:5.93s, iters:2336, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100.orders.o_orderkey, collate: binary] | N/A | N/A |
| └─Selection_20 | 5536577.67 | 5610951 | mpp[tiflash] | | tiflash_task:{proc max:325.5ms, min:0s, avg: 108.5ms, p80:325.5ms, p95:325.5ms, iters:2336, tasks:3, threads:60} | ge(tpch_100.orders.o_orderdate, 1995-01-01 00:00:00.000000), lt(tpch_100.orders.o_orderdate, 1995-04-01 00:00:00.000000) | N/A | N/A |
| └─TableFullScan_19 | 150000000.00 | 150000000 | mpp[tiflash] | table:orders | tiflash_task:{proc max:238.5ms, min:0s, avg: 79.5ms, p80:238.5ms, p95:238.5ms, iters:2336, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:18541, total_skipped_packs:186, total_scanned_rows:150000611, total_skipped_rows:1513328, total_rs_index_load_time: 75ms, total_read_time: 11454ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+--------------------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
18 rows in set (6.00 sec)将聚合函数下推到 Join 或 Union 之前
通过将聚合操作下推到 Join 或 Union 操作之前的位置，可以减少 Join 或 Union 操作中需要处理的数据量，从而提高性能。
变量 tidb_opt_agg_push_down 控制优化器是否执行将聚合函数下推到 Join 或 Union 之前的优化操作。当查询中的聚合操作执行较慢时，你可以将此变量设置为 ON。
set @@tidb_opt_agg_push_down = ON;以下示例展示了启用 tidb_opt_agg_push_down 变量前后的查询结果。在启用此变量之前，HashAgg_58 操作在 HashJoin_41 操作之后执行。启用此变量后，新生成的 HashAgg_21 和 HashAgg_32 操作在 HashJoin_76 操作之前执行。这显著减少了 Join 操作需要处理的数据量。
启用 tidb_opt_agg_push_down 之前：
mysql> explain analyze select count(*) from t1 join t2 where t1.a = t2.b group by t1.a;
+------------------------------------------+--------------+-----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+--------------+-----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+--------+------+
| TableReader_66 | 100450000.00 | 20 | root | | time:2.13s, loops:2, cop_task: {num: 21, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_65 | N/A | N/A |
| └─ExchangeSender_65 | 100450000.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:2.13s, min:2.08s, avg: 2.11s, p80:2.13s, p95:2.13s, iters:768, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_60 | 100450000.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:2.13s, min:2.08s, avg: 2.11s, p80:2.13s, p95:2.13s, iters:768, tasks:3, threads:60} | Column#43 | N/A | N/A |
| └─HashAgg_58 | 100450000.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:2.13s, min:2.08s, avg: 2.11s, p80:2.13s, p95:2.13s, iters:768, tasks:3, threads:60} | group by:test.t1.a, funcs:count(1)->Column#43 | N/A | N/A |
| └─HashJoin_41 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:1.77s, min:1.67s, avg: 1.73s, p80:1.77s, p95:1.77s, iters:10955, tasks:3, threads:60} | inner join, equal:[eq(test.t1.a, test.t2.b)], stream_count: 20 | N/A | N/A |
| ├─ExchangeReceiver_22(Build) | 100450000.00 | 99980000 | mpp[tiflash] | | tiflash_task:{proc max:171ms, min:127ms, avg: 154.5ms, p80:171ms, p95:171ms, iters:10440, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| │ └─ExchangeSender_21 | 100450000.00 | 99980000 | mpp[tiflash] | | tiflash_task:{proc max:498.7ms, min:0s, avg: 166.2ms, p80:498.7ms, p95:498.7ms, iters:1653, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t1.a, collate: binary], stream_count: 20 | N/A | N/A |
| │ └─Selection_20 | 100450000.00 | 99980000 | mpp[tiflash] | | tiflash_task:{proc max:83.7ms, min:0s, avg: 27.9ms, p80:83.7ms, p95:83.7ms, iters:1653, tasks:3, threads:60} | not(isnull(test.t1.a)) | N/A | N/A |
| │ └─TableFullScan_19 | 100450000.00 | 99980000 | mpp[tiflash] | table:t1 | tiflash_task:{proc max:41.7ms, min:0s, avg: 13.9ms, p80:41.7ms, p95:41.7ms, iters:1653, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:12336, total_skipped_packs:5759, total_scanned_rows:100083566, total_skipped_rows:46786721, total_rs_index_load_time: 5ms, total_read_time: 1105ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
| └─ExchangeReceiver_26(Probe) | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:373ms, min:71ms, avg: 253.2ms, p80:373ms, p95:373ms, iters:28135, tasks:3, threads:60} | | N/A | N/A |
| └─ExchangeSender_25 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:2.07s, min:0s, avg: 689.6ms, p80:2.07s, p95:2.07s, iters:9388, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t2.b, collate: binary] | N/A | N/A |
| └─Selection_24 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:146.9ms, min:0s, avg: 49ms, p80:146.9ms, p95:146.9ms, iters:9388, tasks:3, threads:60} | not(isnull(test.t2.b)) | N/A | N/A |
| └─TableFullScan_23 | 600000000.00 | 600000000 | mpp[tiflash] | table:t2 | tiflash_task:{proc max:63.8ms, min:0s, avg: 21.3ms, p80:63.8ms, p95:63.8ms, iters:9388, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73834, total_skipped_packs:439, total_scanned_rows:600007458, total_skipped_rows:3553131, total_rs_index_load_time: 12ms, total_read_time: 270ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+------------------------------------------+--------------+-----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+--------+------+
13 rows in set (2.15 sec)启用 tidb_opt_agg_push_down：
mysql> set @@tidb_opt_agg_push_down = ON;
Query OK, 0 rows affected (0.00 sec)启用 tidb_opt_agg_push_down 后：
mysql> explain analyze select count(*) from t1 join t2 where t1.a = t2.b group by t1.a;
+------------------------------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_85 | 20.00 | 20 | root | | time:432.4ms, loops:2, cop_task: {num: 19, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_84 | N/A | N/A |
| └─ExchangeSender_84 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:431.1ms, min:429.3ms, avg: 430.4ms, p80:431.1ms, p95:431.1ms, iters:16, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_83 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:430.1ms, min:429.3ms, avg: 429.7ms, p80:430.1ms, p95:430.1ms, iters:16, tasks:3, threads:60} | Column#43 | N/A | N/A |
| └─HashAgg_82 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:430.1ms, min:429.3ms, avg: 429.7ms, p80:430.1ms, p95:430.1ms, iters:16, tasks:3, threads:60} | group by:test.t1.a, funcs:count(Column#44)->Column#43, stream_count: 20 | N/A | N/A |
| └─ExchangeReceiver_78 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:430.1ms, min:429.3ms, avg: 429.7ms, p80:430.1ms, p95:430.1ms, iters:16, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| └─ExchangeSender_77 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:427ms, min:0s, avg: 142.3ms, p80:427ms, p95:427ms, iters:60, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t1.a, collate: binary], stream_count: 20 | N/A | N/A |
| └─HashJoin_76 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:426ms, min:0s, avg: 142ms, p80:426ms, p95:426ms, iters:60, tasks:3, threads:60} | inner join, equal:[eq(test.t1.a, test.t2.b)] | N/A | N/A |
| ├─ExchangeReceiver_36(Build) | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:374ms, min:0s, avg: 124.7ms, p80:374ms, p95:374ms, iters:46, tasks:3, threads:60} | | N/A | N/A |
| │ └─ExchangeSender_35 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:373.7ms, min:0s, avg: 124.6ms, p80:373.7ms, p95:373.7ms, iters:16, tasks:3, threads:60} | ExchangeType: Broadcast | N/A | N/A |
| │ └─Projection_31 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:373.7ms, min:0s, avg: 124.6ms, p80:373.7ms, p95:373.7ms, iters:16, tasks:3, threads:60} | Column#44, test.t2.b | N/A | N/A |
| │ └─HashAgg_32 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:373.7ms, min:0s, avg: 124.6ms, p80:373.7ms, p95:373.7ms, iters:16, tasks:3, threads:60} | group by:test.t2.b, funcs:sum(Column#45)->Column#44, funcs:firstrow(test.t2.b)->test.t2.b, stream_count: 20 | N/A | N/A |
| │ └─ExchangeReceiver_34 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:373.7ms, min:0s, avg: 124.6ms, p80:373.7ms, p95:373.7ms, iters:16, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| │ └─ExchangeSender_33 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:368.3ms, min:0s, avg: 122.8ms, p80:368.3ms, p95:368.3ms, iters:3, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t2.b, collate: binary], stream_count: 20 | N/A | N/A |
| │ └─HashAgg_21 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:367.3ms, min:0s, avg: 122.4ms, p80:367.3ms, p95:367.3ms, iters:3, tasks:3, threads:60} | group by:test.t2.b, funcs:count(1)->Column#45 | N/A | N/A |
| │ └─Selection_30 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:77.2ms, min:0s, avg: 25.7ms, p80:77.2ms, p95:77.2ms, iters:9389, tasks:3, threads:60} | not(isnull(test.t2.b)) | N/A | N/A |
| │ └─TableFullScan_29 | 600000000.00 | 600000000 | mpp[tiflash] | table:t2 | tiflash_task:{proc max:37.2ms, min:0s, avg: 12.4ms, p80:37.2ms, p95:37.2ms, iters:9389, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73834, total_skipped_packs:412, total_scanned_rows:600002896, total_skipped_rows:3342145, total_rs_index_load_time: 0ms, total_read_time: 45ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
| └─Selection_19(Probe) | 100450000.00 | 99980000 | mpp[tiflash] | | tiflash_task:{proc max:30ms, min:0s, avg: 10ms, p80:30ms, p95:30ms, iters:1645, tasks:3, threads:60} | not(isnull(test.t1.a)) | N/A | N/A |
| └─TableFullScan_18 | 100450000.00 | 99980000 | mpp[tiflash] | table:t1 | tiflash_task:{proc max:22ms, min:0s, avg: 7.34ms, p80:22ms, p95:22ms, iters:1645, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:12326, total_skipped_packs:4762, total_scanned_rows:100045535, total_skipped_rows:38651216, total_rs_index_load_time: 4ms, total_read_time: 355ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+------------------------------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+--------+------+
18 rows in set (0.46 sec)启用 Distinct 优化
TiFlash 不支持某些带有 Distinct 列的聚合函数，例如 Sum。默认情况下，整个聚合函数在 TiDB 中计算。通过启用 Distinct 优化，某些操作可以下推到 TiFlash，从而提高查询性能。
如果查询中带有 distinct 操作的聚合函数执行较慢，你可以通过将 tidb_opt_distinct_agg_push_down 变量设置为 ON 来启用将带有 Distinct 的聚合函数（如 select sum(distinct a) from t）下推到 Coprocessor 的优化操作。
set @@tidb_opt_distinct_agg_push_down = ON;以下示例展示了启用 tidb_opt_distinct_agg_push_down 变量前后的查询结果。在启用此变量之前，TiDB 需要从 TiFlash 读取所有数据并在 TiDB 中执行 distinct。启用此变量后，distinct a 被下推到 TiFlash，并在 HashAgg_6 中添加了一个新的 group by 列 test.t.a。查询结果中的两个警告表明聚合函数无法完全下推到 TiFlash。
启用 tidb_opt_distinct_agg_push_down 之前：
mysql> explain analyze select count(distinct a) from test.t;
+----------------------------+--------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+--------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+----------+---------+
| HashAgg_6 | 1.00 | 1 | root | | time:2m23.2s, loops:2 | funcs:sum(distinct Column#23)->Column#22 | 41.3 KB | 0 Bytes |
| └─Projection_16 | 600000000.00 | 600000000 | root | | time:2.51s, loops:586548, Concurrency:5 | cast(test.t.a, decimal(10,0) BINARY)->Column#23 | 243.2 KB | N/A |
| └─TableReader_11 | 600000000.00 | 600000000 | root | | time:1.6s, loops:586548, cop_task: {num: 1173, max: 256.2ms, min: 25.1ms, avg: 46.9ms, p95: 63.5ms, rpc_num: 1173, rpc_time: 55s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_10 | 70.2 MB | N/A |
| └─TableFullScan_10 | 600000000.00 | 600000000 | cop[tiflash] | table:t | tiflash_task:{proc max:9ms, min:531µs, avg: 4.49ms, p80:5.55ms, p95:6.74ms, iters:9390, tasks:1173, threads:1173}, tiflash_scan:{dtfile:{total_scanned_packs:73834, total_skipped_packs:1231, total_scanned_rows:600010914, total_skipped_rows:9988978, total_rs_index_load_time: 0ms, total_read_time: 16ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+----------------------------+--------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+----------+---------+
4 rows in set, 2 warnings (2 min 23.21 sec)启用 tidb_opt_distinct_agg_push_down：
mysql> set @@tidb_opt_distinct_agg_push_down = ON;
Query OK, 0 rows affected (0.00 sec)启用 tidb_opt_distinct_agg_push_down 后：
mysql> explain analyze select count(distinct a) from test.t;
+-----------------------------+--------------+-----------+-------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+--------------+-----------+-------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+-----------+---------+
| HashAgg_10 | 1.00 | 1 | root | | time:233.8ms, loops:2 | funcs:sum(distinct Column#23)->Column#22 | 2.42 KB | 0 Bytes |
| └─Projection_12 | 1.00 | 3 | root | | time:233.7ms, loops:2, Concurrency:OFF | cast(test.c.a, decimal(10,0) BINARY)->Column#23 | 380 Bytes | N/A |
| └─TableReader_11 | 1.00 | 3 | root | | time:233.7ms, loops:2, cop_task: {num: 6, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:HashAgg_6 | 100 Bytes | N/A |
| └─HashAgg_6 | 1.00 | 3 | batchCop[tiflash] | | tiflash_task:{proc max:225.8ms, min:210.7ms, avg: 216.9ms, p80:225.8ms, p95:225.8ms, iters:3, tasks:3, threads:60} | group by:test.t.a, | N/A | N/A |
| └─TableFullScan_9 | 600000000.00 | 600000000 | batchCop[tiflash] | table:t | tiflash_task:{proc max:50.3ms, min:33.7ms, avg: 44.6ms, p80:50.3ms, p95:50.3ms, iters:9387, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73833, total_skipped_packs:475, total_scanned_rows:600000000, total_skipped_rows:3852098, total_rs_index_load_time: 0ms, total_read_time: 84ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+-----------------------------+--------------+-----------+-------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+-----------+---------+
5 rows in set, 2 warnings (0.24 sec)使用 ALTER TABLE ... COMPACT 语句压缩数据
执行 ALTER TABLE ... COMPACT 语句可以在 TiFlash 节点上为特定表或分区启动压缩。在压缩过程中，节点上的物理数据会被重写，包括清理已删除的行和合并由更新引起的多个数据版本。这有助于提高访问性能并减少磁盘使用。以下是示例：
ALTER TABLE employees COMPACT TIFLASH REPLICA;ALTER TABLE employees COMPACT PARTITION pNorth, pEast TIFLASH REPLICA;用广播哈希连接替换分散哈希连接
对于小表的 Join 操作，广播哈希连接算法可以避免传输大表，从而提高计算性能。
-
tidb_broadcast_join_threshold_size变量控制是否使用广播哈希连接算法。如果表大小（单位：字节）小于此变量的值，则使用广播哈希连接算法。否则，使用分散哈希连接算法。set @@tidb_broadcast_join_threshold_size = 2000000; -
tidb_broadcast_join_threshold_count变量也控制是否使用广播哈希连接算法。如果连接操作的对象属于子查询，优化器无法估计子查询结果集的大小。在这种情况下，大小由结果集中的行数决定。如果子查询的估计行数少于此变量的值，则使用广播哈希连接算法。否则，使用分散哈希连接算法。set @@tidb_broadcast_join_threshold_count = 100000;
以下示例展示了重新配置 tidb_broadcast_join_threshold_size 前后的查询结果。在重新配置之前，ExchangeSender_29 的 ExchangeType 是 HashPartition。将此变量的值更改为 10000000 后，ExchangeSender_29 的 ExchangeType 变为 Broadcast。
重新配置 tidb_broadcast_join_threshold_size 之前：
mysql> explain analyze select max(l_shipdate), max(l_commitdate), max(l_receiptdate) from supplier,lineitem where s_suppkey = l_suppkey;
+------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| HashAgg_45 | 1.00 | 1 | root | | time:3.8s, loops:2, partial_worker:{wall_time:3.798284809s, concurrency:5, task_num:1, tot_wait:18.99079929s, tot_exec:8.193µs, tot_time:18.990819019s, max:3.798181723s, p95:3.798181723s}, final_worker:{wall_time:0s, concurrency:5, task_num:1, tot_wait:18.991291379s, tot_exec:197.45µs, tot_time:18.991494363s, max:3.798334972s, p95:3.798334972s} | funcs:max(Column#28)->Column#25, funcs:max(Column#29)->Column#26, funcs:max(Column#30)->Column#27 | 17.3 KB | N/A |
| └─TableReader_47 | 1.00 | 3 | root | | time:3.8s, loops:2, cop_task: {num: 6, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_46 | N/A | N/A |
| └─ExchangeSender_46 | 1.00 | 3 | mpp[tiflash] | | tiflash_task:{proc max:3.79s, min:3.78s, avg: 3.78s, p80:3.79s, p95:3.79s, iters:3, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_13 | 1.00 | 3 | mpp[tiflash] | | tiflash_task:{proc max:3.79s, min:3.78s, avg: 3.78s, p80:3.79s, p95:3.79s, iters:3, tasks:3, threads:60} | funcs:max(tpch_100.lineitem.l_shipdate)->Column#28, funcs:max(tpch_100.lineitem.l_commitdate)->Column#29, funcs:max(tpch_100.lineitem.l_receiptdate)->Column#30 | N/A | N/A |
| └─HashJoin_44 | 600845438.27 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:3.17s, min:3.12s, avg: 3.14s, p80:3.17s, p95:3.17s, iters:11143, tasks:3, threads:60} | inner join, equal:[eq(tpch_100.supplier.s_suppkey, tpch_100.lineitem.l_suppkey)], stream_count: 20 | N/A | N/A |
| ├─ExchangeReceiver_30(Build) | 1000000.00 | 1000000 | mpp[tiflash] | | tiflash_task:{proc max:23.3ms, min:21ms, avg: 22.5ms, p80:23.3ms, p95:23.3ms, iters:120, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| │ └─ExchangeSender_29 | 1000000.00 | 1000000 | mpp[tiflash] | | tiflash_task:{proc max:22.7ms, min:0s, avg: 7.57ms, p80:22.7ms, p95:22.7ms, iters:16, tasks:3, threads:2} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100.supplier.s_suppkey, collate: binary], stream_count: 20 | N/A | N/A |
| │ └─TableFullScan_28 | 1000000.00 | 1000000 | mpp[tiflash] | table:supplier | tiflash_task:{proc max:9.71ms, min:0s, avg: 3.24ms, p80:9.71ms, p95:9.71ms, iters:16, tasks:3, threads:2}, tiflash_scan:{dtfile:{total_scanned_packs:123, total_skipped_packs:0, total_scanned_rows:1000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 2ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
| └─ExchangeReceiver_33(Probe) | 600037902.00 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:564.3ms, min:340ms, avg: 438.5ms, p80:564.3ms, p95:564.3ms, iters:27583, tasks:3, threads:60} | | N/A | N/A |
| └─ExchangeSender_32 | 600037902.00 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:3.69s, min:0s, avg: 1.23s, p80:3.69s, p95:3.69s, iters:9298, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100.lineitem.l_suppkey, collate: binary] | N/A | N/A |
| └─TableFullScan_31 | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:62.8ms, min:0s, avg: 20.9ms, p80:62.8ms, p95:62.8ms, iters:9298, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73465, total_skipped_packs:13060, total_scanned_rows:600168663, total_skipped_rows:106699256, total_rs_index_load_time: 16ms, total_read_time: 27979ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
11 rows in set (3.83 sec)将 tidb_broadcast_join_threshold_size 设置为 10000000：
mysql> set @@tidb_broadcast_join_threshold_size = 10000000;
Query OK, 0 rows affected (0.00 sec)将 tidb_broadcast_join_threshold_size 设置为 10000000 后：
mysql> explain analyze select max(l_shipdate), max(l_commitdate), max(l_receiptdate) from supplier,lineitem where s_suppkey = l_suppkey;
+------------------------------------------+--------------+-----------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+--------------+-----------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| HashAgg_43 | 1.00 | 1 | root | | time:2.75s, loops:2, partial_worker:{wall_time:2.748445779s, concurrency:5, task_num:1, tot_wait:13.74202679s, tot_exec:8.012µs, tot_time:13.742045721s, max:2.748414915s, p95:2.748414915s}, final_worker:{wall_time:2.74848039s, concurrency:5, task_num:1, tot_wait:13.742157526s, tot_exec:53.191µs, tot_time:13.742214417s, max:2.748462393s, p95:2.748462393s} | funcs:max(Column#28)->Column#25, funcs:max(Column#29)->Column#26, funcs:max(Column#30)->Column#27 | 17.3 KB | N/A |
| └─TableReader_45 | 1.00 | 3 | root | | time:2.75s, loops:2, cop_task: {num: 6, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_44 | N/A | N/A |
| └─ExchangeSender_44 | 1.00 | 3 | mpp[tiflash] | | tiflash_task:{proc max:2.74s, min:2.65s, avg: 2.7s, p80:2.74s, p95:2.74s, iters:3, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_13 | 1.00 | 3 | mpp[tiflash] | | tiflash_task:{proc max:2.74s, min:2.65s, avg: 2.7s, p80:2.74s, p95:2.74s, iters:3, tasks:3, threads:60} | funcs:max(tpch_100.lineitem.l_shipdate)->Column#28, funcs:max(tpch_100.lineitem.l_commitdate)->Column#29, funcs:max(tpch_100.lineitem.l_receiptdate)->Column#30 | N/A | N/A |
| └─HashJoin_42 | 600845438.27 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:2.15s, min:2.06s, avg: 2.11s, p80:2.15s, p95:2.15s, iters:9286, tasks:3, threads:60} | inner join, equal:[eq(tpch_100.supplier.s_suppkey, tpch_100.lineitem.l_suppkey)] | N/A | N/A |
| ├─ExchangeReceiver_30(Build) | 1000000.00 | 3000000 | mpp[tiflash] | | tiflash_task:{proc max:28.1ms, min:24.9ms, avg: 27ms, p80:28.1ms, p95:28.1ms, iters:48, tasks:3, threads:60} | | N/A | N/A |
| │ └─ExchangeSender_29 | 1000000.00 | 1000000 | mpp[tiflash] | | tiflash_task:{proc max:14ms, min:0s, avg: 4.67ms, p80:14ms, p95:14ms, iters:16, tasks:3, threads:2} | ExchangeType: Broadcast | N/A | N/A |
| │ └─TableFullScan_28 | 1000000.00 | 1000000 | mpp[tiflash] | table:supplier | tiflash_task:{proc max:9ms, min:0s, avg: 3ms, p80:9ms, p95:9ms, iters:16, tasks:3, threads:2}, tiflash_scan:{dtfile:{total_scanned_packs:123, total_skipped_packs:0, total_scanned_rows:1000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 2ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
| └─TableFullScan_31(Probe) | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:57.9ms, min:42.9ms, avg: 51.3ms, p80:57.9ms, p95:57.9ms, iters:9297, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73464, total_skipped_packs:12985, total_scanned_rows:600169085, total_skipped_rows:106014866, total_rs_index_load_time: 23ms, total_read_time: 21667ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+------------------------------------------+--------------+-----------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
9 rows in set (2.76 sec)设置更大的执行并发度
更大的执行并发度允许 TiFlash 占用系统更多的 CPU 资源，从而提高查询性能。
tidb_max_tiflash_threads 变量用于设置 TiFlash 执行请求的最大并发度。单位是线程。
set @@tidb_max_tiflash_threads = 20;以下示例展示了重新配置 tidb_max_tiflash_threads 前后的查询结果。在设置 tidb_max_tiflash_threads 之前，单个 TiFlash 实例的请求执行并发度为 8 个线程。由于集群总共有 3 个 TiFlash 实例，所有 TiFlash 实例上请求执行的总线程数为 24（8 × 3）。将 tidb_max_tiflash_threads 设置为 20 后，所有 TiFlash 实例上请求执行的总线程数为 60（20 × 3）。
重新配置 tidb_max_tiflash_threads 之前：
mysql> explain analyze select a, count(*) from t group by a;
+--------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_44 | 20.00 | 20 | root | | time:655.9ms, loops:2, cop_task: {num: 14, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_43 | N/A | N/A |
| └─ExchangeSender_43 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:654.6ms, min:652.4ms, avg: 653.8ms, p80:654.6ms, p95:654.6ms, iters:11, tasks:3, threads:24} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_5 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:654.3ms, min:651.4ms, avg: 653.1ms, p80:654.3ms, p95:654.3ms, iters:11, tasks:3, threads:24} | test.t.a, Column#22 | N/A | N/A |
| └─Projection_39 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:654.3ms, min:651.4ms, avg: 653.1ms, p80:654.3ms, p95:654.3ms, iters:11, tasks:3, threads:24} | Column#22, test.t.a | N/A | N/A |
| └─HashAgg_40 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:654.3ms, min:651.4ms, avg: 653.1ms, p80:654.3ms, p95:654.3ms, iters:11, tasks:3, threads:24} | group by:test.t.a, funcs:sum(Column#29)->Column#22, funcs:firstrow(test.t.a)->test.t.a, stream_count: 8 | N/A | N/A |
| └─ExchangeReceiver_42 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:654.3ms, min:651.4ms, avg: 653.1ms, p80:654.3ms, p95:654.3ms, iters:11, tasks:3, threads:24} | stream_count: 8 | N/A | N/A |
| └─ExchangeSender_41 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:649ms, min:0s, avg: 216.3ms, p80:649ms, p95:649ms, iters:3, tasks:3, threads:24} | ExchangeType: HashPartition, Hash Cols: [name: test.t.a, collate: binary], stream_count: 8 | N/A | N/A |
| └─HashAgg_37 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:649ms, min:0s, avg: 216.3ms, p80:649ms, p95:649ms, iters:3, tasks:3, threads:24} | group by:test.t.a, funcs:count(1)->Column#29 | N/A | N/A |
| └─TableFullScan_26 | 600000000.00 | 600000000 | mpp[tiflash] | table:t | tiflash_task:{proc max:40ms, min:0s, avg: 13.3ms, p80:40ms, p95:40ms, iters:9386, tasks:3, threads:24}, tiflash_scan:{dtfile:{total_scanned_packs:73833, total_skipped_packs:174, total_scanned_rows:600000000, total_skipped_rows:1402537, total_rs_index_load_time: 0ms, total_read_time: 4ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+--------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+--------+------+
9 rows in set (0.67 sec)将 tidb_max_tiflash_threads 设置为 20：
mysql> set @@tidb_max_tiflash_threads = 20;
Query OK, 0 rows affected (0.00 sec)将 tidb_max_tiflash_threads 设置为 20 后：
mysql> explain analyze select a, count(*) from t group by a;
+--------------------------------------+--------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+--------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_44 | 20.00 | 20 | root | | time:357.7ms, loops:2, cop_task: {num: 19, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_43 | N/A | N/A |
| └─ExchangeSender_43 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:355.3ms, min:354.6ms, avg: 355ms, p80:355.3ms, p95:355.3ms, iters:16, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_5 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:354.3ms, min:353.6ms, avg: 354ms, p80:354.3ms, p95:354.3ms, iters:16, tasks:3, threads:60} | test.t.a, Column#22 | N/A | N/A |
| └─Projection_39 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:354.3ms, min:353.6ms, avg: 354ms, p80:354.3ms, p95:354.3ms, iters:16, tasks:3, threads:60} | Column#22, test.t.a | N/A | N/A |
| └─HashAgg_40 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{proc max:354.3ms, min:353.6ms, avg: 354ms, p80:354.3ms, p95:354.3ms, iters:16, tasks:3, threads:60} | group by:test.t.a, funcs:sum(Column#29)->Column#22, funcs:firstrow(test.t.a)->test.t.a, stream_count: 20 | N/A | N/A |
| └─ExchangeReceiver_42 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:354.3ms, min:353.6ms, avg: 354ms, p80:354.3ms, p95:354.3ms, iters:16, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| └─ExchangeSender_41 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:349.6ms, min:0s, avg: 116.5ms, p80:349.6ms, p95:349.6ms, iters:3, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t.a, collate: binary], stream_count: 20 | N/A | N/A |
| └─HashAgg_37 | 20.00 | 60 | mpp[tiflash] | | tiflash_task:{proc max:347.6ms, min:0s, avg: 115.9ms, p80:347.6ms, p95:347.6ms, iters:3, tasks:3, threads:60} | group by:test.t.a, funcs:count(1)->Column#29 | N/A | N/A |
| └─TableFullScan_26 | 600000000.00 | 600000000 | mpp[tiflash] | table:t | tiflash_task:{proc max:36.6ms, min:0s, avg: 12.2ms, p80:36.6ms, p95:36.6ms, iters:9389, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73833, total_skipped_packs:418, total_scanned_rows:600001386, total_skipped_rows:3381854, total_rs_index_load_time: 10ms, total_read_time: 61ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+--------------------------------------+--------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+--------+------+
9 rows in set (0.37 sec)配置 tiflash_fine_grained_shuffle_stream_count
你可以通过配置细粒度 Shuffle 特性的 tiflash_fine_grained_shuffle_stream_count 来增加窗口函数执行的并发度。这样，窗口函数的执行可以占用更多的系统资源，从而提高查询性能。
当窗口函数被下推到 TiFlash 执行时，你可以使用此变量来控制窗口函数执行的并发度级别。单位是线程。
set @@tiflash_fine_grained_shuffle_stream_count = 20;以下示例展示了重新配置 tiflash_fine_grained_shuffle_stream_count 变量前后的查询结果。在重新配置之前，[ExchangeSender_11, ExchangeReceiver_12, Sort_13, Window_22] 的 stream_count 是 8。重新配置后，stream_count 变为 20。
重新配置 tiflash_fine_grained_shuffle_stream_count 之前：
mysql> explain analyze select *, row_number() over (partition by a) from t;
+----------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_24 | 600000000.00 | 600000000 | root | | time:4m30.5s, loops:585941, cop_task: {num: 9163, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_23 | N/A | N/A |
| └─ExchangeSender_23 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:4m30.5s, min:3m4.8s, avg: 3m36.1s, p80:4m30.5s, p95:4m30.5s, iters:9160, tasks:3, threads:24} | ExchangeType: PassThrough | N/A | N/A |
| └─Window_22 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:3m31.6s, min:2m26.2s, avg: 2m50.7s, p80:3m31.6s, p95:3m31.6s, iters:9160, tasks:3, threads:24} | row_number()->Column#23 over(partition by test.t.a rows between current row and current row), stream_count: 8 | N/A | N/A |
| └─Sort_13 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:3m28.6s, min:2m24.2s, avg: 2m48.4s, p80:3m28.6s, p95:3m28.6s, iters:9160, tasks:3, threads:24} | test.t.a, stream_count: 8 | N/A | N/A |
| └─ExchangeReceiver_12 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:32.4s, min:32s, avg: 32.1s, p80:32.4s, p95:32.4s, iters:49307, tasks:3, threads:24} | stream_count: 8 | N/A | N/A |
| └─ExchangeSender_11 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:32s, min:0s, avg: 10.7s, p80:32s, p95:32s, iters:9386, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t.a, collate: binary], stream_count: 8 | N/A | N/A |
| └─TableFullScan_10 | 600000000.00 | 600000000 | mpp[tiflash] | table:t | tiflash_task:{proc max:113.9ms, min:0s, avg: 38ms, p80:113.9ms, p95:113.9ms, iters:9386, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73834, total_skipped_packs:190, total_scanned_rows:600000000, total_skipped_rows:1536382, total_rs_index_load_time: 16ms, total_read_time: 166324ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+----------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+--------+------+
7 rows in set (4 min 30.59 sec)将 tiflash_fine_grained_shuffle_stream_count 设置为 20：
mysql> set @@tiflash_fine_grained_shuffle_stream_count = 20;
Query OK, 0 rows affected (0.00 sec)将 tiflash_fine_grained_shuffle_stream_count 设置为 20 后：
mysql> explain analyze select *, row_number() over (partition by a) from t;
+----------------------------------+--------------+-----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+--------------+-----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_24 | 600000000.00 | 600000000 | root | | time:2m55s, loops:585941, cop_task: {num: 9163, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_23 | N/A | N/A |
| └─ExchangeSender_23 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:2m55s, min:1m37s, avg: 2m28.7s, p80:2m55s, p95:2m55s, iters:9160, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─Window_22 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:2m12.9s, min:1m17s, avg: 1m54.2s, p80:2m12.9s, p95:2m12.9s, iters:9160, tasks:3, threads:60} | row_number()->Column#23 over(partition by test.t.a rows between current row and current row), stream_count: 20 | N/A | N/A |
| └─Sort_13 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:2m10.9s, min:1m16s, avg: 1m52.5s, p80:2m10.9s, p95:2m10.9s, iters:9160, tasks:3, threads:60} | test.t.a, stream_count: 20 | N/A | N/A |
| └─ExchangeReceiver_12 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:27.2s, min:25.5s, avg: 26.6s, p80:27.2s, p95:27.2s, iters:49602, tasks:3, threads:60} | stream_count: 20 | N/A | N/A |
| └─ExchangeSender_11 | 600000000.00 | 600000000 | mpp[tiflash] | | tiflash_task:{proc max:25.5s, min:0s, avg: 8.51s, p80:25.5s, p95:25.5s, iters:9388, tasks:3, threads:60} | ExchangeType: HashPartition, Hash Cols: [name: test.t.a, collate: binary], stream_count: 20 | N/A | N/A |
| └─TableFullScan_10 | 600000000.00 | 600000000 | mpp[tiflash] | table:t | tiflash_task:{proc max:167.3ms, min:0s, avg: 55.8ms, p80:167.3ms, p95:167.3ms, iters:9388, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73834, total_skipped_packs:408, total_scanned_rows:600002896, total_skipped_rows:3307316, total_rs_index_load_time: 20ms, total_read_time: 179431ms}, total_create_snapshot_time: 0ms} | keep order:false | N/A | N/A |
+----------------------------------+--------------+-----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+------+
7 rows in set (2 min 55.09 sec)