前段时间遇到一个SQL测试用例，典型的OLAP分析查询语句—4表关联，其中2张表数据量为亿级，查询结果集为百万级，希望在TiDB中测试一下大致性能预期。由于无法提供真实数据，基于表结构模拟造数测试。基于SQL用例，选择TiFlash引擎进行测试，从初始情况下无法跑出结果到最终优化到2.59秒，本文详细描述一下测试过程及优化经验。
准备工作
测试环境准备
所选择测试环境为三节点的TiDB集群，集群资源及组件部署情况如下：
默认参数调整
考虑到需要批量快速模拟造数，以及SQL中包括group_concat拼接功能，我们需要提前修改以下系统变量。
表结构及造数
测试语句共使用4张表，其中2张表模拟1亿条数据，2张表模拟1千条数据，SQL语句查询结果集为100万条左右。为了简便起见，模拟造数使用TiDB兼容的CTE递归语句批量生成。同时，为了避免大事务的影响，使用BATCH语句将大批量INSERT写入内部拆分为多个事务进行。
SQL语句
SQL语句为4表关联语句，两张亿级表按条件关联，另外两张1000行的表使用group_concat拼接为1行记录后与大表关联结果进行Json匹配，整体结果集为百万条记录级别。初始SQL语句如下：
SELECT vehicle_no,
policy_expire_date,
city_code
FROM
(SELECT vehicle_no,
policy_expire_date,
city_code,
IFNULL( JSON_EXTRACT(day_info,
concat('$.C',city_code)), JSON_EXTRACT( day_info, concat('$.C',substring(city_code, 1, 2), '0000') ) ) AS biz_begin_date_str
FROM
(SELECT t1.vehicle_no AS vehicle_no,
t2.policy_expire_date AS policy_expire_date,
IFNULL( JSON_EXTRACT( t3.city_info,
concat('$.', substring(t1.vehicle_no, 1, 2)) ), JSON_EXTRACT( t3.city_info, concat('$.', substring(t1.vehicle_no, 1, 1), '_') ) ) AS city_code, day_info
FROM
(SELECT c1 AS vehicle_no,
c2 AS vin_no
FROM c_t1 ) t1
JOIN
(SELECT c1 AS vehicle_no,
c2 AS policy_expire_date
FROM c_t4 ) t2
ON t1.vehicle_no = t2.vehicle_no
OR t1.vin_no = t2.vehicle_no
JOIN
(SELECT CONCAT( '{', GROUP_CONCAT( '"', replace(c9, '*', '_'), '":', c1, '' ), '}' ) AS city_info
FROM c_t2 ) t3
ON 1 = 1
JOIN
(SELECT CONCAT( '{', GROUP_CONCAT(' "', concat('C',c1), '": ', c2, ''), '}' ) AS day_info
FROM c_t3 ) t4
ON 1 = 1 ) t
WHERE IFNULL(city_code, '') != '' ) tt
WHERE now()
BETWEEN date_add(policy_expire_date, interval -60 day)
AND policy_expire_date
AND IF( biz_begin_date_str is null
OR biz_begin_date_str = '', 0, biz_begin_date_str ) is NOT null
SQL优化过程
初始执行—无法查询结果
使用原始语句执行，等待数小时后，无法执行出结果。
使用explain查看执行计划，
+----------------------------------------------------+---------------------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------------------+---------------------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_26 | 6400000000000000.00 | root | | za.c_t1.c1, za.c_t4.c2, Column#42 |
| └─Selection_28 | 6400000000000000.00 | root | | not(isnull(if(or(isnull(cast(Column#43, var_string(16777216))), eq(Column#43, cast("", json BINARY))), "0", cast(Column#43, var_string(16777216))))) |
| └─Projection_30 | 8000000000000000.00 | root | | za.c_t1.c1, za.c_t4.c2, Column#42, ifnull(json_extract(cast(Column#41, json BINARY), concat($.C, cast(Column#42, var_string(16777216)))), json_extract(cast(Column#41, json BINARY), concat($.C, substring(cast(Column#42, var_string(16777216)), 1, 2), 0000)))->Column#43 |
| └─Projection_32 | 8000000000000000.00 | root | | za.c_t1.c1, za.c_t4.c2, ifnull(json_extract(cast(Column#30, json BINARY), concat($., substring(za.c_t1.c1, 1, 2))), json_extract(cast(Column#30, json BINARY), concat($., substring(za.c_t1.c1, 1, 1), _)))->Column#42, Column#41 |
| └─Projection_34 | 8000000000000000.00 | root | | za.c_t1.c1, za.c_t4.c2, Column#30, Column#41 |
| └─HashJoin_38 | 8000000000000000.00 | root | | CARTESIAN inner join, other cond:ne(ifnull(cast(ifnull(json_extract(cast(Column#30, json BINARY), concat("$.", substring(za.c_t1.c1, 1, 2))), json_extract(cast(Column#30, json BINARY), concat("$.", substring(za.c_t1.c1, 1, 1), "_"))), var_string(16777216)), ""), "") |
| ├─HashJoin_48(Build) | 1.00 | root | | CARTESIAN inner join |
| │ ├─Projection_77(Build) | 1.00 | root | | concat({, Column#40, })->Column#41 |
| │ │ └─HashAgg_94 | 1.00 | root | | funcs:group_concat(Column#53 separator ",")->Column#40 |
| │ │ └─TableReader_96 | 1.00 | root | | MppVersion: 2, data:ExchangeSender_95 |
| │ │ └─ExchangeSender_95 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ │ └─HashAgg_82 | 1.00 | mpp[tiflash] | | funcs:group_concat(" "", Column#57, "": ", Column#58, "" separator ",")->Column#53 |
| │ │ └─Projection_130 | 1001.00 | mpp[tiflash] | | concat(C, za.c_t3.c1)->Column#57, cast(za.c_t3.c2, var_string(20))->Column#58 |
| │ │ └─TableFullScan_93 | 1001.00 | mpp[tiflash] | table:c_t3 | keep order:false |
| │ └─Projection_50(Probe) | 1.00 | root | | concat({, Column#29, })->Column#30 |
| │ └─HashAgg_66 | 1.00 | root | | funcs:group_concat(Column#52 separator ",")->Column#29 |
| │ └─TableReader_68 | 1.00 | root | | MppVersion: 2, data:ExchangeSender_67 |
| │ └─ExchangeSender_67 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ └─HashAgg_55 | 1.00 | mpp[tiflash] | | funcs:group_concat(""", Column#55, "":", Column#56, "" separator ",")->Column#52 |
| │ └─Projection_129 | 1000.00 | mpp[tiflash] | | replace(za.c_t2.c9, *, _)->Column#55, cast(za.c_t2.c1, var_string(20))->Column#56 |
| │ └─TableFullScan_43 | 1000.00 | mpp[tiflash] | table:c_t2 | keep order:false |
| └─HashJoin_110(Probe) | 8000000000000000.00 | root | | CARTESIAN inner join, other cond:or(eq(za.c_t1.c1, za.c_t4.c1), eq(za.c_t1.c2, za.c_t4.c1)) |
| ├─TableReader_118(Build) | 80000000.00 | root | | MppVersion: 2, data:ExchangeSender_117 |
| │ └─ExchangeSender_117 | 80000000.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ └─Selection_116 | 80000000.00 | mpp[tiflash] | | ge(2024-02-02 16:45:26, cast(date_add(za.c_t4.c2, -60, "DAY"), datetime(6) BINARY)), le(2024-02-02 16:45:26, cast(za.c_t4.c2, datetime(6) BINARY)) |
| │ └─TableFullScan_115 | 100000000.00 | mpp[tiflash] | table:c_t4 | pushed down filter:empty, keep order:false |
| └─TableReader_123(Probe) | 100000000.00 | root | | MppVersion: 2, data:ExchangeSender_122 |
| └─ExchangeSender_122 | 100000000.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─TableFullScan_121 | 100000000.00 | mpp[tiflash] | table:c_t1 | keep order:false |
+----------------------------------------------------+---------------------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
29 rows in set, 5 warnings (0.01 sec)
执行计划中表现的主要问题包括：
- 两张亿级别表关联产生笛卡尔积，导致预估处理行数超大
- 语句执行warning信息，提示group_concat、date_add未下推
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Aggregation can not be pushed to tikv because AggFunc `group_concat` is not supported now |
| Warning | 1105 | Aggregation can not be pushed to tikv because AggFunc `group_concat` is not supported now |
| Warning | 1105 | Aggregation can not be pushed to tikv because AggFunc `group_concat` is not supported now |
| Warning | 1105 | Aggregation can not be pushed to tikv because AggFunc `group_concat` is not supported now |
| Warning | 1105 | Scalar function 'date_add'(signature: AddDateStringInt, return type: var_string(29)) is not supported to push down to tikv now. |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
优化大表笛卡尔积问题—执行耗时08分34秒
上述两张亿级表关联产生了笛卡尔积，主要是因为关联条件使用t1.vehicle_no = t2.vehicle_no OR t1.vin_no = t2.vehicle_no 导致，猜测可能是优化器针对带OR条件关联场景未做特殊优化。基于过往经验，这样的关联语句可以等价改写为两个SELECT子句UNION的方式，更改如下，
修改后，SQL语句从无法执行出结果到8分钟34秒完成查询。新的执行计划中也不再看到巨大的预估行数，说明此等价修改有效。
优化Json转换导致HashJoin效率问题—执行耗时19秒
仔细查看上述语句的explain analyze输出，我们发现时间主要消耗在外层的HashJoin算子，这个HashJoin主要是对两张大表关联的结果集（约100万）与两个Json字符串（各1条记录）做笛卡尔积。关联的结果集大小并没有任何变化，但耗时却长达8分钟，这不符合常理，我们怀疑问题出在百万次的Varchar->Json类型转换上面。
从上述执行计划看，HashJoin的operator info中需要针对每一行关联的记录做2次cast(.., json BINARY)动作，因为t3和t4子查询中的city_info和day_info是字符类型，这个隐式转换动作不可避免。
因此我们的优化思路是在子查询中提前将拼接的字符串转换为Json类型，这样理论上可以规避百万次的cast(..,json BINARY)动作，于是修改如下内容，
经过上述修改，执行耗时缩短到19秒，从执行计划可以看出，虽然HashJoin的operator info中仍然能看到cast(.., json BINARY)，但由于对应字段已经提前转换为Json类型，所以内部并不需要实际的隐式转换动作，性能得到大幅提升。
优化date_add条件下推问题—执行耗时13秒
虽然优化到19秒，但warning信息我们仍然没有解决，我们首先来处理date_add。跟date_add相关的语句为：now() BETWEEN date_add(policy_expire_date, interval -60 day) AND policy_expire_date。TiFlash支持粗糙索引，针对数值、日期数据类型默认每 8K 行生成 min-max内建索引，而这里的条件过滤因为把字段放在date_add函数中，会导致无法走min-max索引的情况，因此需要将过滤条件做如下修改：
按上述修改后，语句执行后虽然不再显示date_add的warning，但是从执行计划中查看过滤条件仍然没有下推执行。
经查看，原因是policy_expire_date对应的字段（c_t4.c2）类型为varchar类型，导致在语句policy_expire_date between now() and date_add(now(), interval 60 day)内部会包含一层隐式类型转换。因此，我们将这个c2字段修改为datetime类型，为了便于对比，创建一张新表并将数据同步到新表结构中。
现在，SQL的执行时间被优化到13秒，从执行计划中可以看出，条件过滤已经能正常下推。
优化group_concat条件下推问题—执行耗时2.59秒
解决date_add的warning后，还剩下一个group_concat的warning。从TiDB官方文档可以看出，TiFlash存储引擎是支持group_concat下推的，那么为什么这里group_concat没有下推呢？在相关专家的指导下了解到，SQL示例中的group_concat所在的查询语句是一个不带group by的聚合查询，TiFlash当前针对这样的场景尚且还无法下推执行，后续版本会进行改进，详见
虽然TiFlash不支持这种情况的下推，但我们可以使用一些绕行方案，比如在不影响结果正确性的情况下通过增加ORDER BY或DISTINCT可以让查询下推。这里我们通增加DISTINCT来进行测试，修改如下
修改后的执行计划如下所示，可以看出除root以外所有的执行算子均已下推到TiFlash执行，执行耗时也被优化到2.59秒。
+--------------------------------------------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| TableReader_213 | 1842063.40 | 1028305 | root | | time:2.57s, loops:1484, RU:0.000000, cop_task: {num: 801, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_212 | 160.6 KB | N/A |
| └─ExchangeSender_212 | 1842063.40 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.56s, min:898.8ms, avg: 1.83s, p80:2.49s, p95:2.56s, iters:1652, tasks:6, threads:240} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_37 | 1842063.40 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.56s, min:898.8ms, avg: 1.82s, p80:2.49s, p95:2.56s, iters:1652, tasks:6, threads:240} | Column#37, Column#38, Column#62 | N/A | N/A |
| └─Selection_211 | 1842063.40 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.56s, min:898.8ms, avg: 1.82s, p80:2.49s, p95:2.56s, iters:1652, tasks:6, threads:240} | not(isnull(if(or(isnull(cast(Column#63, var_string(16777216))), eq(Column#63, cast("", json BINARY))), "0", cast(Column#63, var_string(16777216))))) | N/A | N/A |
| └─Projection_208 | 2302579.25 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.55s, min:898.8ms, avg: 1.82s, p80:2.48s, p95:2.55s, iters:1652, tasks:6, threads:240} | Column#37, Column#38, Column#62, ifnull(json_extract(cast(Column#61, json BINARY), concat($.C, cast(Column#62, var_string(16777216)))), json_extract(cast(Column#61, json BINARY), concat($.C, substring(cast(Column#62, var_string(16777216)), 1, 2), 0000)))->Column#63 | N/A | N/A |
| └─Projection_205 | 2302579.25 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.5s, min:898.8ms, avg: 1.79s, p80:2.42s, p95:2.5s, iters:1652, tasks:6, threads:240} | Column#37, Column#38, ifnull(json_extract(cast(Column#50, json BINARY), concat($., substring(Column#37, 1, 2))), json_extract(cast(Column#50, json BINARY), concat($., substring(Column#37, 1, 1), )))->Column#62, Column#61 | N/A | N/A |
| └─Projection_202 | 2302579.25 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.44s, min:898.8ms, avg: 1.75s, p80:2.34s, p95:2.44s, iters:1652, tasks:6, threads:240} | Column#37, Column#38, Column#50, Column#61 | N/A | N/A |
| └─HashJoin_199 | 2302579.25 | 1028305 | mpp[tiflash] | | tiflash_task:{proc max:2.44s, min:898.8ms, avg: 1.75s, p80:2.34s, p95:2.44s, iters:1652, tasks:6, threads:240} | CARTESIAN inner join, other cond:ne(ifnull(cast(ifnull(json_extract(cast(Column#50, json BINARY), concat("$.", substring(Column#37, 1, 2))), json_extract(cast(Column#50, json BINARY), concat("$.", substring(Column#37, 1, 1), ""))), var_string(16777216)), ""), "") | N/A | N/A |
| ├─ExchangeReceiver_68(Build) | 1.00 | 6 | mpp[tiflash] | | tiflash_task:{proc max:53.8ms, min:36.3ms, avg: 45.5ms, p80:51.8ms, p95:53.8ms, iters:6, tasks:6, threads:240} | | N/A | N/A |
| │ └─ExchangeSender_67 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:27.4ms, min:0s, avg: 4.56ms, p80:0s, p95:27.4ms, iters:1, tasks:6, threads:1} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ └─HashJoin_49 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:26.4ms, min:0s, avg: 4.4ms, p80:0s, p95:26.4ms, iters:1, tasks:6, threads:1} | CARTESIAN inner join | N/A | N/A |
| │ ├─ExchangeReceiver_59(Build) | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:24.4ms, min:0s, avg: 4.06ms, p80:0s, p95:24.4ms, iters:1, tasks:6, threads:40} | | N/A | N/A |
| │ │ └─ExchangeSender_58 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:19.7ms, min:0s, avg: 3.29ms, p80:0s, p95:19.7ms, iters:1, tasks:6, threads:1} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ │ └─Projection_51 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:19.7ms, min:0s, avg: 3.29ms, p80:0s, p95:19.7ms, iters:1, tasks:6, threads:1} | cast(concat({, Column#49, }), json BINARY)->Column#50 | N/A | N/A |
| │ │ └─Projection_54 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:19.7ms, min:0s, avg: 3.29ms, p80:0s, p95:19.7ms, iters:1, tasks:6, threads:1} | Column#49 | N/A | N/A |
| │ │ └─HashAgg_55 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:19.7ms, min:0s, avg: 3.29ms, p80:0s, p95:19.7ms, iters:1, tasks:6, threads:1} | funcs:group_concat(distinct Column#92, Column#93, Column#94, Column#95, Column#96 separator ",")->Column#49 | N/A | N/A |
| │ │ └─Projection_215 | 1.00 | 1000 | mpp[tiflash] | | tiflash_task:{proc max:17.7ms, min:0s, avg: 2.96ms, p80:0s, p95:17.7ms, iters:1, tasks:6, threads:40} | Column#74->Column#92, Column#75->Column#93, Column#76->Column#94, cast(za.c_t2.c1, var_string(20))->Column#95, Column#77->Column#96 | N/A | N/A |
| │ │ └─ExchangeReceiver_57 | 1.00 | 1000 | mpp[tiflash] | | tiflash_task:{proc max:17.7ms, min:0s, avg: 2.96ms, p80:0s, p95:17.7ms, iters:1, tasks:6, threads:40} | | N/A | N/A |
| │ │ └─ExchangeSender_56 | 1.00 | 1000 | mpp[tiflash] | | tiflash_task:{proc max:12.4ms, min:0s, avg: 2.06ms, p80:0s, p95:12.4ms, iters:1, tasks:6, threads:1} | ExchangeType: PassThrough, Compression: FAST | N/A | N/A |
| │ │ └─HashAgg_52 | 1.00 | 1000 | mpp[tiflash] | | tiflash_task:{proc max:12.4ms, min:0s, avg: 2.06ms, p80:0s, p95:12.4ms, iters:1, tasks:6, threads:1} | group by:"", """, "":", Column#90, Column#91, | N/A | N/A |
| │ │ └─Projection_214 | 1000.00 | 1000 | mpp[tiflash] | | tiflash_task:{proc max:10.4ms, min:0s, avg: 1.73ms, p80:0s, p95:10.4ms, iters:1, tasks:6, threads:40} | replace(za.c_t2.c9, *, _)->Column#90, za.c_t2.c1->Column#91 | N/A | N/A |
| │ │ └─TableFullScan_53 | 1000.00 | 1000 | mpp[tiflash] | table:c_t2 | tiflash_task:{proc max:10.4ms, min:0s, avg: 1.73ms, p80:0s, p95:10.4ms, iters:1, tasks:6, threads:40}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:1000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 0ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0, total_learner_read_time: 1ms} | keep order:false | N/A | N/A |
| │ └─Projection_60(Probe) | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:25.4ms, min:0s, avg: 4.23ms, p80:0s, p95:25.4ms, iters:1, tasks:6, threads:1} | cast(concat({, Column#60, }), json BINARY)->Column#61 | N/A | N/A |
| │ └─Projection_63 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:25.4ms, min:0s, avg: 4.23ms, p80:0s, p95:25.4ms, iters:1, tasks:6, threads:1} | Column#60 | N/A | N/A |
| │ └─HashAgg_64 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:25.4ms, min:0s, avg: 4.23ms, p80:0s, p95:25.4ms, iters:1, tasks:6, threads:1} | funcs:group_concat(distinct Column#99, Column#100, Column#101, Column#102, Column#103 separator ",")->Column#60 | N/A | N/A |
| │ └─Projection_217 | 1.00 | 1001 | mpp[tiflash] | | tiflash_task:{proc max:19.4ms, min:0s, avg: 3.23ms, p80:0s, p95:19.4ms, iters:1, tasks:6, threads:40} | Column#78->Column#99, Column#79->Column#100, Column#80->Column#101, cast(za.c_t3.c2, var_string(20))->Column#102, Column#81->Column#103 | N/A | N/A |
| │ └─ExchangeReceiver_66 | 1.00 | 1001 | mpp[tiflash] | | tiflash_task:{proc max:19.4ms, min:0s, avg: 3.23ms, p80:0s, p95:19.4ms, iters:1, tasks:6, threads:40} | | N/A | N/A |
| │ └─ExchangeSender_65 | 1.00 | 1001 | mpp[tiflash] | | tiflash_task:{proc max:13.9ms, min:0s, avg: 2.32ms, p80:0s, p95:13.9ms, iters:1, tasks:6, threads:1} | ExchangeType: PassThrough, Compression: FAST | N/A | N/A |
| │ └─HashAgg_61 | 1.00 | 1001 | mpp[tiflash] | | tiflash_task:{proc max:13.9ms, min:0s, avg: 2.32ms, p80:0s, p95:13.9ms, iters:1, tasks:6, threads:1} | group by:" "", "", "": ", Column#97, Column#98, | N/A | N/A |
| │ └─Projection_216 | 1001.00 | 1001 | mpp[tiflash] | | tiflash_task:{proc max:11.9ms, min:0s, avg: 1.99ms, p80:0s, p95:11.9ms, iters:1, tasks:6, threads:40} | concat(C, za.c_t3.c1)->Column#97, za.c_t3.c2->Column#98 | N/A | N/A |
| │ └─TableFullScan_62 | 1001.00 | 1001 | mpp[tiflash] | table:c_t3 | tiflash_task:{proc max:11.9ms, min:0s, avg: 1.99ms, p80:0s, p95:11.9ms, iters:1, tasks:6, threads:40}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:1001, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 0ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0, total_learner_read_time: 1ms} | keep order:false | N/A | N/A |
| └─Union_69(Probe) | 2302579.25 | 0 | mpp[tiflash] | | tiflash_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:0, tasks:6, threads:0} | | N/A | N/A |
| ├─Projection_70 | 1284216.97 | 1500895 | mpp[tiflash] | | tiflash_task:{proc max:1.7s, min:0s, avg: 742.1ms, p80:1.41s, p95:1.7s, iters:1652, tasks:6, threads:120} | cast(za.c_t4_dt.c1, varchar(32) CHARACTER SET utf8 COLLATE utf8_bin)->Column#37, za.c_t4_dt.c2->Column#38 | N/A | N/A |
| │ └─Projection_71 | 1284216.97 | 1500895 | mpp[tiflash] | | tiflash_task:{proc max:1.7s, min:0s, avg: 741.1ms, p80:1.41s, p95:1.7s, iters:1652, tasks:6, threads:120} | za.c_t4_dt.c1, za.c_t4_dt.c2 | N/A | N/A |
| │ └─HashJoin_72 | 1284216.97 | 1500895 | mpp[tiflash] | | tiflash_task:{proc max:1.7s, min:0s, avg: 741.1ms, p80:1.41s, p95:1.7s, iters:1652, tasks:6, threads:120} | inner join, equal:[eq(za.c_t4_dt.c1, za.c_t1.c1)] | N/A | N/A |
| │ ├─ExchangeReceiver_76(Build) | 1003426.73 | 3002814 | mpp[tiflash] | | tiflash_task:{proc max:200.6ms, min:0s, avg: 97.7ms, p80:199ms, p95:200.6ms, iters:315, tasks:6, threads:120} | | N/A | N/A |
| │ │ └─ExchangeSender_75 | 1003426.73 | 1000938 | mpp[tiflash] | | tiflash_task:{proc max:198.7ms, min:0s, avg: 33.1ms, p80:0s, p95:198.7ms, iters:1604, tasks:6, threads:120} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ │ └─TableFullScan_73 | 1003426.73 | 1000938 | mpp[tiflash] | table:c_t4_dt | tiflash_task:{proc max:195.7ms, min:0s, avg: 32.6ms, p80:0s, p95:195.7ms, iters:1604, tasks:6, threads:120}, tiflash_scan:{dtfile:{total_scanned_packs:24768, total_skipped_packs:1747, total_scanned_rows:200037563, total_skipped_rows:14029436, total_rs_index_check_time: 41ms, total_read_time: 6185ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 201, total_remote_region_num: 0, total_learner_read_time: 32ms} | pushed down filter:ge(za.c_t4_dt.c2, 2024-02-03 11:28:25), le(za.c_t4_dt.c2, 2024-04-03 11:28:25), keep order:false | N/A | N/A |
| │ └─Selection_78(Probe) | 100000000.00 | 100000000 | mpp[tiflash] | | tiflash_task:{proc max:1.37s, min:0s, avg: 490ms, p80:800.3ms, p95:1.37s, iters:1652, tasks:6, threads:120} | not(isnull(za.c_t1.c1)) | N/A | N/A |
| │ └─TableFullScan_77 | 100000000.00 | 100000000 | mpp[tiflash] | table:c_t1 | tiflash_task:{proc max:1.37s, min:0s, avg: 487.5ms, p80:786.3ms, p95:1.37s, iters:1652, tasks:6, threads:120}, tiflash_scan:{dtfile:{total_scanned_packs:12261, total_skipped_packs:47, total_scanned_rows:100000000, total_skipped_rows:375339, total_rs_index_check_time: 2ms, total_read_time: 5001ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 98, total_remote_region_num: 0, total_learner_read_time: 38ms} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─Projection_80 | 1018362.28 | 0 | mpp[tiflash] | | tiflash_task:{proc max:1.69s, min:0s, avg: 583.8ms, p80:913.8ms, p95:1.69s, iters:1651, tasks:6, threads:120} | za.c_t1.c1->Column#37, za.c_t4_dt.c2->Column#38 | N/A | N/A |
| └─HashJoin_81 | 1018362.28 | 0 | mpp[tiflash] | | tiflash_task:{proc max:1.69s, min:0s, avg: 583.8ms, p80:913.8ms, p95:1.69s, iters:1651, tasks:6, threads:120} | inner join, equal:[eq(za.c_t4_dt.c1, za.c_t1.c2)] | N/A | N/A |
| ├─ExchangeReceiver_85(Build) | 1003426.73 | 3002814 | mpp[tiflash] | | tiflash_task:{proc max:194.8ms, min:0s, avg: 96.7ms, p80:192.8ms, p95:194.8ms, iters:294, tasks:6, threads:120} | | N/A | N/A |
| │ └─ExchangeSender_84 | 1003426.73 | 1000938 | mpp[tiflash] | | tiflash_task:{proc max:193.2ms, min:0s, avg: 32.2ms, p80:0s, p95:193.2ms, iters:1602, tasks:6, threads:120} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ └─TableFullScan_82 | 1003426.73 | 1000938 | mpp[tiflash] | table:c_t4_dt | tiflash_task:{proc max:192.2ms, min:0s, avg: 32ms, p80:0s, p95:192.2ms, iters:1602, tasks:6, threads:120}, tiflash_scan:{dtfile:{total_scanned_packs:24770, total_skipped_packs:1742, total_scanned_rows:200013169, total_skipped_rows:13995210, total_rs_index_check_time: 16ms, total_read_time: 3521ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 201, total_remote_region_num: 0, total_learner_read_time: 31ms} | pushed down filter:ge(za.c_t4_dt.c2, 2024-02-03 11:28:25), le(za.c_t4_dt.c2, 2024-04-03 11:28:25), keep order:false | N/A | N/A |
| └─Selection_87(Probe) | 100000000.00 | 100000000 | mpp[tiflash] | | tiflash_task:{proc max:1.39s, min:0s, avg: 491ms, p80:789.8ms, p95:1.39s, iters:1651, tasks:6, threads:120} | not(isnull(za.c_t1.c2)) | N/A | N/A |
| └─TableFullScan_86 | 100000000.00 | 100000000 | mpp[tiflash] | table:c_t1 | tiflash_task:{proc max:1.39s, min:0s, avg: 490.8ms, p80:788.8ms, p95:1.39s, iters:1651, tasks:6, threads:120}, tiflash_scan:{dtfile:{total_scanned_packs:12261, total_skipped_packs:42, total_scanned_rows:100000000, total_skipped_rows:336776, total_rs_index_check_time: 10ms, total_read_time: 11296ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 98, total_remote_region_num: 0, total_learner_read_time: 15ms} | pushed down filter:empty, keep order:false | N/A | N/A |
+--------------------------------------------------------------------+--------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
47 rows in set (2.59 sec)