TiFlash亿级多表关联优化实践,从无法跑出结果优化到2.59秒

Tiit 609 2024-02-07

前段时间遇到一个SQL测试用例,典型的OLAP分析查询语句—4表关联,其中2张表数据量为亿级,查询结果集为百万级,希望在TiDB中测试一下大致性能预期。由于无法提供真实数据,基于表结构模拟造数测试。基于SQL用例,选择TiFlash引擎进行测试,从初始情况下无法跑出结果到最终优化到2.59秒,本文详细描述一下测试过程及优化经验。

准备工作

测试环境准备

所选择测试环境为三节点的TiDB集群,集群资源及组件部署情况如下:

image.png

默认参数调整

考虑到需要批量快速模拟造数,以及SQL中包括group_concat拼接功能,我们需要提前修改以下系统变量。

image.png

表结构及造数

测试语句共使用4张表,其中2张表模拟1亿条数据,2张表模拟1千条数据,SQL语句查询结果集为100万条左右。为了简便起见,模拟造数使用TiDB兼容的CTE递归语句批量生成。同时,为了避免大事务的影响,使用BATCH语句将大批量INSERT写入内部拆分为多个事务进行。

image.png

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)

执行计划中表现的主要问题包括:

  • 两张亿级别表关联产生笛卡尔积,导致预估处理行数超大

image.png

  • 语句执行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的方式,更改如下,

image.png

修改后,SQL语句从无法执行出结果到8分钟34秒完成查询。新的执行计划中也不再看到巨大的预估行数,说明此等价修改有效。

优化Json转换导致HashJoin效率问题—执行耗时19秒

仔细查看上述语句的explain analyze输出,我们发现时间主要消耗在外层的HashJoin算子,这个HashJoin主要是对两张大表关联的结果集(约100万)与两个Json字符串(各1条记录)做笛卡尔积。关联的结果集大小并没有任何变化,但耗时却长达8分钟,这不符合常理,我们怀疑问题出在百万次的Varchar->Json类型转换上面。

image.png

image.png

从上述执行计划看,HashJoin的operator info中需要针对每一行关联的记录做2次cast(.., json BINARY)动作,因为t3和t4子查询中的city_info和day_info是字符类型,这个隐式转换动作不可避免。

image.png

因此我们的优化思路是在子查询中提前将拼接的字符串转换为Json类型,这样理论上可以规避百万次的cast(..,json BINARY)动作,于是修改如下内容,

image.png

经过上述修改,执行耗时缩短到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索引的情况,因此需要将过滤条件做如下修改:

image.png

按上述修改后,语句执行后虽然不再显示date_add的warning,但是从执行计划中查看过滤条件仍然没有下推执行。

image.png

经查看,原因是policy_expire_date对应的字段(c_t4.c2)类型为varchar类型,导致在语句policy_expire_date between now() and date_add(now(), interval 60 day)内部会包含一层隐式类型转换。因此,我们将这个c2字段修改为datetime类型,为了便于对比,创建一张新表并将数据同步到新表结构中。

image.png

现在,SQL的执行时间被优化到13秒,从执行计划中可以看出,条件过滤已经能正常下推。

image.png                                  

优化group_concat条件下推问题—执行耗时2.59秒

解决date_add的warning后,还剩下一个group_concat的warning。从TiDB官方文档可以看出,TiFlash存储引擎是支持group_concat下推的,那么为什么这里group_concat没有下推呢?在相关专家的指导下了解到,SQL示例中的group_concat所在的查询语句是一个不带group by的聚合查询,TiFlash当前针对这样的场景尚且还无法下推执行,后续版本会进行改进,详见

final mode scalar agg function does not push down tiflash, and without warnings · Issue #50583 · pingcap/tidb · GitHub

image.png

虽然TiFlash不支持这种情况的下推,但我们可以使用一些绕行方案,比如在不影响结果正确性的情况下通过增加ORDER BY或DISTINCT可以让查询下推。这里我们通增加DISTINCT来进行测试,修改如下

image.png

修改后的执行计划如下所示,可以看出除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)


版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:TiDB 7.5.0 LTS 高性能数据批处理方案总结
下一篇:TiDB 与MySQL优化器在特定语句下执行效果对比(一)
相关文章