在tiflash中join表的时候走了Batch_Point_Get算子从而导致没法用上tiflash的mpp算法

青木科技-熊猛 · 2023 年8 月 1 日 02:50

主表：
CREATE TABLE result_daily_sku_id (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,
day date NOT NULL DEFAULT ‘1970-01-01’ COMMENT ‘日期’,
brand_id int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘品牌id’,
shop_id int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘店铺id’,
sku_id varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘平台条码id’,
sku_sn varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘商家编码’,
is_live tinyint(2) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘是否直播商品 0:不是 1:是’,
PRIMARY KEY (shop_id,day,sku_id,sku_sn,is_live) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY idx_uniq_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=1 COMMENT=‘主表’;

副表：
CREATE TABLE shop (
id int(10) NOT NULL AUTO_INCREMENT COMMENT ‘店铺ID’,
shop_category varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘店铺类目’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=1 COMMENT=‘副表’；

执行如下sql：
EXPLAIN ANALYZE
SELECT /*+ read_from_storage(tiflash[result_daily_sku_id,shop]) */
shop.shop_category, COUNT(1) num
FROM result_daily_sku_id
LEFT JOIN shop ON result_daily_sku_id.shop_id = shop.id
WHERE result_daily_sku_id.day BETWEEN ‘2023-01-01’ AND ‘2023-07-31’ AND shop.id IN (12, 17)
GROUP BY shop.shop_category;

因为取副表shop数据走了Batch_Point_Get算子，从而直接导致tiflash只是读取数据返回给了root节点，而root节点刚好走了 MergeJoin，结果就是这个算法不是很优。当主表数据量比较多时，sql会很慢。

如何能不让shop表走Batch_Point_Get，而是直接走Tiflash中的TableFullScan，从而整个聚合计算都走tiflash的mpp模式？

有猫万事足 · 2023 年8 月 1 日 03:10

https://docs.pingcap.com/zh/tidb/stable/use-tiflash-mpp-mode#控制是否选择-mpp-模式

如果想要 TiDB 忽略优化器的代价估算，强制使用 MPP，可以通过如下语句来设置：

set @@session.tidb_allow_mpp=1;
set @@session.tidb_enforce_mpp=1;

你可以试试这样是否有提升。

青木科技-熊猛 · 2023 年8 月 1 日 04:08

试了下不行。
怕是系统认为 Batch_Point_Get 算子太优秀就不管不顾了？

有猫万事足 · 2023 年8 月 1 日 04:19

你的sql应该和下面这个是等价的，试试下面这个：

select shop.shop_category,sum(t.num) num from 
(
select rd.shop_id,count(1) num
from result_daily_sku_id rd 
where rd.day BETWEEN '2023-01-01' AND '2023-07-31' and rd.shop_id in (12,17)
group by rd.shop_id
) t
left join shop on shop.id=t.shop_id
group by shop.shop_category

思路就是尝试先把大表数据统计出来，再关联小表。

青木科技-熊猛 · 2023 年8 月 1 日 05:36

是的。只不过我们是做BI低代码开发，比较难把sql拼装成这样

h5n1 · 2023 年8 月 1 日 06:20

set session tidb_isolation_read_engines=[“tiflash”,“tidb”] 先试一下这个手动执行SQL能走tiflash不

青木科技-熊猛 · 2023 年8 月 1 日 08:36

试了下，通过设置变量确实可以。

h5n1 · 2023 年8 月 1 日 09:40

你是什么版本，估计优化器有待加强。

青木科技-熊猛 · 2023 年8 月 1 日 12:09

版本是6.5

redgame · 2023 年8 月 1 日 13:44

这个场景是否该尽量避免使用 Batch_Point_Get 算子

有猫万事足 · 2023 年8 月 1 日 15:55

应该说Batch_Point_Get 这个算子还不是问题的核心。

问题的核心是，应该尽量在tiflash里面把关联/聚合做完，给到tidb就是结果。
如果从tiflash取行记录，到tidb里面做mergejoin和hashagg，这是双输的一个结果。
等于两边都没有发挥最擅长的特性。

TiDBer_iCdTOZ1r · 2023 年8 月 2 日 00:17

看是哪个版本

ShawnYan · 2023 年8 月 2 日 00:32

在7.2上测试效果一样，

5.7.25-TiDB-v7.2.0 (root@shawnyan) [test] 09:31:02> set session tidb_isolation_read_engines=tiflash;
Query OK, 0 rows affected (0.001 sec)

5.7.25-TiDB-v7.2.0 (root@shawnyan) [test] 09:31:13> tidb_isolation_read_engines=\c
5.7.25-TiDB-v7.2.0 (root@shawnyan) [test] 09:31:17> EXPLAIN ANALYZE
    -> SELECT /*+ read_from_storage(tiflash[result_daily_sku_id,shop]) */
    ->     `shop`.`shop_category`, COUNT(1) num
    -> FROM `result_daily_sku_id`
    ->          LEFT JOIN `shop` ON `result_daily_sku_id`.`shop_id` = `shop`.`id`
    -> WHERE `result_daily_sku_id`.`day` BETWEEN '2023-01-01' AND '2023-07-31' AND shop.id IN (12, 17)
    -> GROUP BY `shop`.`shop_category`;
+------------------------------------------------+---------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                                             | estRows | actRows | task         | access object             | execution info                                                                                           | operator info                                                                                                                                  | memory    | disk |
+------------------------------------------------+---------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| TableReader_58                                 | 1.00    | 0       | root         |                           | time:25.7ms, loops:1, RU:0.000000, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | MppVersion: 1, data:ExchangeSender_57                                                                                                          | 707 Bytes | N/A  |
| └─ExchangeSender_57                        | 1.00    | 0       | mpp[tiflash] |                           | tiflash_task:{time:21ms, loops:0, threads:4}                                                             | ExchangeType: PassThrough                                                                                                                      | N/A       | N/A  |
|   └─Projection_9                           | 1.00    | 0       | mpp[tiflash] |                           | tiflash_task:{time:20ms, loops:0, threads:4}                                                             | test.shop.shop_category, Column#10                                                                                                             | N/A       | N/A  |
|     └─Projection_52                        | 1.00    | 0       | mpp[tiflash] |                           | tiflash_task:{time:20ms, loops:0, threads:4}                                                             | Column#10, test.shop.shop_category                                                                                                             | N/A       | N/A  |
|       └─HashAgg_50                         | 1.00    | 0       | mpp[tiflash] |                           | tiflash_task:{time:20ms, loops:0, threads:4}                                                             | group by:test.shop.shop_category, funcs:count(1)->Column#10, funcs:firstrow(test.shop.shop_category)->test.shop.shop_category, stream_count: 4 | N/A       | N/A  |
|         └─ExchangeReceiver_36              | 0.62    | 0       | mpp[tiflash] |                           | tiflash_task:{time:20ms, loops:0, threads:4}                                                             | stream_count: 4                                                                                                                                | N/A       | N/A  |
|           └─ExchangeSender_35              | 0.62    | 0       | mpp[tiflash] |                           | tiflash_task:{time:17.3ms, loops:0, threads:8}                                                           | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.shop.shop_category, collate: utf8mb4_general_ci], stream_count: 4       | N/A       | N/A  |
|             └─HashJoin_34                  | 0.62    | 0       | mpp[tiflash] |                           | tiflash_task:{time:16.3ms, loops:0, threads:8}                                                           | inner join, equal:[eq(test.result_daily_sku_id.shop_id, test.shop.id)]                                                                         | N/A       | N/A  |
|               ├─ExchangeReceiver_22(Build) | 5.00    | 0       | mpp[tiflash] |                           | tiflash_task:{time:15.3ms, loops:0, threads:8}                                                           |                                                                                                                                                | N/A       | N/A  |
|               │ └─ExchangeSender_21      | 5.00    | 0       | mpp[tiflash] |                           | tiflash_task:{time:9.11ms, loops:0, threads:1}                                                           | ExchangeType: Broadcast, Compression: FAST                                                                                                     | N/A       | N/A  |
|               │   └─TableRangeScan_20    | 5.00    | 0       | mpp[tiflash] | table:result_daily_sku_id | tiflash_task:{time:9.11ms, loops:0, threads:1}                                                           | range:[12 2023-01-01,12 2023-07-31], [17 2023-01-01,17 2023-07-31], keep order:false, stats:pseudo                                             | N/A       | N/A  |
|               └─TableRangeScan_23(Probe)   | 2.00    | 0       | mpp[tiflash] | table:shop                | tiflash_task:{time:14.3ms, loops:0, threads:1}                                                           | range:[12,12], [17,17], keep order:false, stats:pseudo                                                                                         | N/A       | N/A  |
+------------------------------------------------+---------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
12 rows in set (0.124 sec)

ShawnYan · 2023 年8 月 2 日 00:41

单点查询就是代价最小的啊。

https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain-analyze#batch_point_get

给楼主提个小建议，sql语句可以放到markdown里，大家也方便复制，另外shop表的结尾还是中文的【；】

青木科技-熊猛 · 2023 年8 月 2 日 01:32

好的，收到。
单次fullScan最小，但接下来join加聚合计算代价就大了

青木科技-熊猛 · 2023 年8 月 2 日 01:33

版本是6.5

jansu-dev · 2023 年8 月 7 日 06:37

目前老师们提供的方案你能接受吗?

感觉问题可以归结为

hint 为什么不生效, 这个违反 hint 行为定义的情况
–》原因:找到个类似的情况 Hint to read from tiflash doesn’t work when the query is a PointGet · Issue #16909 · pingcap/tidb (github.com) 从没被合并进去的 PR 看, point range 会忽略其他 AccessPath

image1282×934 108 KB
导致了直接不考虑从 tiflash 访问数据.
–》解决办法各位老师也提供了一些
全走 MPP 是否会真的更快, 如果目前测下来有提速, 就用老师们的方法绕吧😂
其实如果主表再大些, 可能会出发一些 TiFlash 其他的限制, 目前数据量还好🤔

如果没有啥问题, 就有选上面某一位老师的回答作为 workaround, 做结论结束了

青木科技-熊猛 · 2023 年8 月 9 日 06:21

全走 MPP 是否会真的更快，这个数据量还是快的。

第二点主表再大的话，会触发哪些限制呢？

“如果没有啥问题, 就有选上面某一位老师的回答作为 workaround, 做结论结束了”还有这个具体怎么操作了？我选了最佳答案了

jansu-dev · 2023 年8 月 9 日 09:57

比如: Packet is too large to send → The block returned by InputStream should obey the max_block_size constraint · Issue #3436 · pingcap/tiflash (github.com)
单个 MPPDataPacket 的数据大于2G，就会报这个错.

jansu-dev · 2023 年8 月 11 日 04:39

选最佳答案就标志结束, 可能是当时我看的时候还没选