分区表分区裁剪的问题

Raymond · 2023 年2 月 16 日 13:01

【 TiDB 使用环境】测试
请问以下，t1分区表和t2表进行关联
mysql> show create table t1;
±------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE t1 (
fname varchar(50) NOT NULL,
lname varchar(50) NOT NULL,
region_code tinyint(3) unsigned NOT NULL,
dob date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (region_code)
(PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN (MAXVALUE)) |
±------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table t2;
±------±-----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-----------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE t2 (
x int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±------±-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

分区表和普通表关联的时候无法实现分区表的分区裁剪
mysql> explain select /*+ INL_JOIN(t1, t2) */ * from t1 left join t2 on t1.region_code = t2.x where t2.x > 70 and t2.x<120;
±-------------------------------±--------±----------±-----------------------±--------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-------------------------------±--------±----------±-----------------------±--------------------------------------------------------+
| HashJoin_13 | 49.00 | root | | inner join, equal:[eq(zk.t1.region_code, zk.t2.x)] |
| ├─TableReader_26(Build) | 49.00 | root | | data:Selection_25 |
| │ └─Selection_25 | 49.00 | cop[tikv] | | gt(zk.t2.x, 70), lt(zk.t2.x, 120), not(isnull(zk.t2.x)) |
| │ └─TableFullScan_24 | 201.00 | cop[tikv] | table:t2 | keep order:false |
| └─PartitionUnion_15(Probe) | 201.00 | root | | |
| ├─TableReader_17 | 63.00 | root | | data:TableFullScan_16 |
| │ └─TableFullScan_16 | 63.00 | cop[tikv] | table:t1, partition:p0 | keep order:false |
| ├─TableReader_19 | 64.00 | root | | data:TableFullScan_18 |
| │ └─TableFullScan_18 | 64.00 | cop[tikv] | table:t1, partition:p1 | keep order:false |
| ├─TableReader_21 | 64.00 | root | | data:TableFullScan_20 |
| │ └─TableFullScan_20 | 64.00 | cop[tikv] | table:t1, partition:p2 | keep order:false |
| └─TableReader_23 | 10.00 | root | | data:TableFullScan_22 |
| └─TableFullScan_22 | 10.00 | cop[tikv] | table:t1, partition:p3 | keep order:false |
±-------------------------------±--------±----------±-----------------------±--------------------------------------------------------+
13 rows in set, 1 warning (0.00 sec

麻烦请各位老师，看下为啥？
插入数据的脚本

cat t1.sh

i=0
while [ $i -le 200 ]
do
let i++
str=“insert into zk.t1 values(‘z’,‘k’,$i,‘2023-1-15’)”
mysql -e “$str”
done

cat t2.sh
i=0
while [ $i -le 200 ]
do
let i++
str=“insert into zk.t2 values($i)”
mysql -e “$str”
done

h5n1 · 2023 年2 月 16 日 22:56

开启分区动态裁剪功能，这个版本可能还没ga

tidb菜鸟一只 · 2023 年2 月 17 日 00:51

开启动态裁剪试一下

考试没答案 · 2023 年2 月 17 日 00:54

问题解决了没？我们在这等您结论。到底行不行

考试没答案 · 2023 年2 月 17 日 00:56

不知道您这个是否符合这个场景

tidb菜鸟一只 · 2023 年2 月 17 日 01:26

我在6.5.0的数据库开启动态裁剪的库中实验了下，
EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON t1.region_code = t2.x WHERE t2.x > 70 AND t2.x<120;
确实走不到分区裁剪

EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON t1.region_code = t2.x WHERE t2.x =120;
这个才可以

我猜测是因为等值连接如果过滤条件也是等值条件执行计划会直接将过滤条件带到t1表中，然后进行了分区裁剪，如果是过滤条件是非等值条件，执行计划并没有将过滤条件带到t1表中，所以进行的是全表查询

考试没答案 · 2023 年2 月 17 日 01:29

您即使开启动态分区裁剪，也不会使用分区裁剪是吧？？

tidb菜鸟一只 · 2023 年2 月 17 日 01:32

使得，应该是我猜测的原因，执行计划的优化逻辑问题，等值连接如果过滤条件是非等值条件，执行计划并不将过滤条件带到t1表中，所以无法进行裁剪

zhouzeru · 2023 年2 月 17 日 02:01

开启动态裁剪

Raymond · 2023 年2 月 17 日 02:34

从现象来说，是这样，但是这种现象合理吗，您那边有oracle嘛，可以试下oracle 上面的执行计划嘛

Raymond · 2023 年2 月 17 日 02:57

其实，我认为是不合理的撒，毕竟t1 left join t2 on t1.region_code = t2.x where t2.x > 70 and t2.x<120;
那应该就推算出 t1.region_code >70 and <120
然后就可以用到t1的分区裁剪了

Raymond · 2023 年2 月 17 日 02:58

跟这个动态还是静态裁剪没有关系，优化器的逻辑优化做的还不是特别nb

h5n1 · 2023 年2 月 17 日 03:00

嗯，我在6.5试了下，确实优化器还需要加强

tidb菜鸟一只 · 2023 年2 月 17 日 03:11

等下我找时间试一下，oracle应该是可以走到分区的

tidb菜鸟一只 · 2023 年2 月 17 日 03:36

在oracle上，分区是都走到了的

Billdi表弟 · 2023 年2 月 17 日 03:44

explain select  * from t1 left join t2 on t1.region_code = t2.x where t2.x > 70 and t2.x<120;

EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON t1.region_code = t2.x WHERE t1.region_code > 70 AND t1.region_code<120;

执行计划优化器可能没有那么牛皮吧， t1.region_code = t2.x 更改查询条件吧

Raymond · 2023 年2 月 17 日 05:57

确实，oracle的优化器还是更nb了

Raymond · 2023 年2 月 17 日 05:57

是的，优化器在细节方面有待加强

dba-kit · 2023 年2 月 17 日 08:37

两个字段类型不一致，可能是做了隐式类型转换，导致走不到索引

dba-kit · 2023 年2 月 17 日 08:38

region_code tinyint(3) unsigned NOT NULL,
x int(11) DEFAULT NULL

一般来说，是把小范围转换为大范围，也就是把tinyint转换为int