前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >第43期:多表关联场景下如何用好分区表

第43期:多表关联场景下如何用好分区表

原创
作者头像
爱可生开源社区
发布2022-08-10 10:40:57
7150
发布2022-08-10 10:40:57
举报
文章被收录于专栏:爱可生开源社区

如何在多表关联场景下合理利用分区表来提升查询性能?基于前几篇关于分区表的介绍,想必大家对 MySQL 分区表的认知已经非常全面:分区表存在的目的就是为了减少每次检索的数据量从而提升整体性能。

前几篇介绍了基于分区表的单表应用,那么分区表在多表关联时是否有必然的性能提升? 经常有人会问这样的一些问题:我用了分区表,但是查询一点也没有加快,反而更慢了,是什么原因?是不是分区表本身有缺陷?还是我没有理解分区表适合的场景?对于这些个问题,我今天用几类典型的查询场景来举例说明。

第一种场景:两表关联,关联键是分区键,但是没有过滤条件。

类似这样: select * from t1 inner join t2 using(id);

这类场景用分区表只会让查询性能更差,并不会加速查询性能。

不用分区表时,表关联数目只有两张;用了分区表,参与表关联的表数目就不仅仅是两张,还有众多表分区,分区数目越多,查询性能越差。

举个简单例子:表t1 为哈希分区表,有1000个分区,记录数50W行。

代码语言:sql
复制
localhost:ytt>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 1000 */
1 row in set (0.00 sec)

表 t1_no_pt 为普通表,为表t1的克隆,但是移除掉表分区,记录数也同样为50W条。

代码语言:sql
复制
localhost:ytt>show create table t1_no_pt\G
*************************** 1. row ***************************
       Table: t1_no_pt
Create Table: CREATE TABLE `t1_no_pt` (
  `id` int DEFAULT NULL,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
1 row in set (0.00 sec)

这两张表在这种场景下的查询性能对比: 分区表和普通表关联查询,执行时间为6.76秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1_no_pt a inner join t1 b using(id);
+----------+
| count(*) |
+----------+
|  1014068 |
+----------+
1 row in set (6.76 sec)

两张分区表关联查询,执行时间为4.32秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1 a inner join t1 b using(id);
+----------+
| count(*) |
+----------+
|  1014068 |
+----------+
1 row in set (4.32 sec)

两张普通表关联查询,执行时间只用了0.87秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1_no_pt a inner join t1_no_pt b using(id);
+----------+
| count(*) |
+----------+
|  1014068 |
+----------+
1 row in set (0.87 sec)

同样的查询,分区表在这样的场景下反而更加糟糕。

第二种场景:两表关联,关联键是分区键,但是有过滤条件。
这里又细分为两种子场景:
1. 过滤条件为分区键

类似这样的查询:select * from t1 inner join t2 using(id) where t1.id = xxx;

这种场景下推荐用分区表! 过滤条件为分区键并且为等值查询,最终优化器会定位到某一个固定的表分区来缩小检索记录数,完美适合分区表。

同样,用表t1和表 t1_no_pt 来举个简单例子:

两分区表关联并且过滤条件为分区键,执行时间为0.01秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1 a inner join t1 b using(id) where a.id = 19172;
+----------+
| count(*) |
+----------+
|       81 |
+----------+
1 row in set (0.01 sec)

两普通表关联,同样的条件,执行时间为0.55秒,比两分区表关联慢很多倍。

代码语言:sql
复制
localhost:ytt>select count(*) from t1_no_pt a inner join t1_no_pt b using(id) where a.id = 19172;
+----------+
| count(*) |
+----------+
|       81 |
+----------+
1 row in set (0.55 sec)

用分区表和普通表关联,执行时间0.32秒,介于前两者之间。

代码语言:sql
复制
localhost:ytt>select count(*) from t1 a inner join t1_no_pt b using(id) where a.id = 19172;
+----------+
| count(*) |
+----------+
|       81 |
+----------+
1 row in set (0.32 sec)

补一个两分区表关联和两普通表关联的执行计划对比,会表现的更加明显:分区表关联成本381.9,扫描行数为280;普通表关联成本249264389.78,扫描行数249125777。此时分区表关联性能提升非常明显!

代码语言:sql
复制

localhost:ytt>explain format=tree select count(*) from t1 a inner join t1 b using(id) where a.id = 19172\G

*** 1. row ***

EXPLAIN: -> Aggregate: count(0)

代码语言:txt
复制
   -> Inner hash join (no condition)  (cost=381.90 rows=280)
代码语言:txt
复制
       -> Filter: (b.id = 19172)  (cost=1.02 rows=53)
代码语言:txt
复制
           -> Table scan on b  (cost=1.02 rows=529)
代码语言:txt
复制
       -> Hash
代码语言:txt
复制
           -> Filter: (a.id = 19172)  (cost=53.65 rows=53)
代码语言:txt
复制
               -> Table scan on a  (cost=53.65 rows=529)

1 row in set (0.00 sec)

localhost:ytt>explain format=tree select count(*) from t1_no_pt a inner join t1_no_pt b using(id) where a.id = 19172\G

*** 1. row ***

EXPLAIN: -> Aggregate: count(0)

代码语言:txt
复制
   -> Inner hash join (no condition)  (cost=249264389.78 rows=249125777)
代码语言:txt
复制
       -> Filter: (b.id = 19172)  (cost=1.87 rows=49913)
代码语言:txt
复制
           -> Table scan on b  (cost=1.87 rows=499125)
代码语言:txt
复制
       -> Hash
代码语言:txt
复制
           -> Filter: (a.id = 19172)  (cost=50257.25 rows=49913)
代码语言:txt
复制
               -> Table scan on a  (cost=50257.25 rows=499125)

1 row in set (0.00 sec)

代码语言:txt
复制
2.过滤条件非分区键

类似这样的查询: select * from t1 inner join t2 using(id) where t1.r1 = xxx;

这种场景下,分区表非但不会带来性能提升,反而造成性能急剧下降。

依然用表t1和表t1_no_pt来举例:两分区表之间关联, 执行时间为6.16秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1 a inner join t1 b using(id) where a.r1 = 10;
+----------+
| count(*) |
+----------+
|    50552 |
+----------+
1 row in set (6.16 sec)

两普通表关联,执行时间为0.7秒,反而比分区表快很多。

代码语言:sql
复制
localhost:ytt>select count(*) from t1_no_pt a inner join t1_no_pt b using(id) where a.r1 = 10;
+----------+
| count(*) |
+----------+
|    50552 |
+----------+
1 row in set (0.70 sec)
第三种场景:两表关联,关联键非分区键,但是过滤条件是分区键。

对于这样的场景,分区表同样不能带来性能提升!

两分区表关联性能很差,执行时间为6.05秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1 a inner join t1 b using(r1) where a.id = 19172;
+----------+
| count(*) |
+----------+
|   225868 |
+----------+
1 row in set (6.05 sec)

两普通表关联性能好很多,执行时间0.54秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1_no_pt a inner join t1_no_pt b using(r1) where a.id = 19172;
+----------+
| count(*) |
+----------+
|   225868 |
+----------+
1 row in set (0.54 sec)

既然过滤条件是分区键,可以考虑让分区表和普通表关联。

改下之前的SQL,用过滤好的分区表数据和普通表关联,这样性能比两普通表关联要好些:执行时间为0.39秒。

代码语言:sql
复制
localhost:ytt>select count(*) from (select  * from t1 a where a.id = 19172) t inner join t1_no_pt b using(r1);
+----------+
| count(*) |
+----------+
|   225868 |
+----------+
1 row in set (0.39 sec)
第四种场景:分区表关联,关联键也是分区键,但是两张分区表分区算法、或者分区数目有差异。

表t2和表t1结构相同,记录数也相同,但是分区数目不一样,表t1有1000个分区,表t2只有50个分区:

代码语言:sql
复制
localhost:ytt>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int DEFAULT NULL,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 50 */
1 row in set (0.01 sec)

基于此,关联两张分区表:执行时间为6.43秒。

代码语言:sql
复制
localhost:ytt>select count(*) from t1 a inner join t2 b using(id);
+----------+
| count(*) |
+----------+
|  1014068 |
+----------+
1 row in set (6.43 sec)

同样,关联两张普通表:执行时间1.98秒。执行时间比分区表要快。

代码语言:sql
复制
localhost:ytt>select count(*) from t1_no_pt a inner join t2_no_pt b using(id);
+----------+
| count(*) |
+----------+
|  1014068 |
+----------+
1 row in set (1.98 sec)

以上性能差异原因在之前的文章中有部分提及,这里不做额外描述。

那基于表关联是否该用分区表做个总结:

用分区表做关联,最好满足以下条件,否则适得其反:

  1. 分区键为关联条件。
  2. 如果分区键为非关联条件,那过滤条件必须得是分区键。
  3. 两分区表的分区方法,分区数目必须一致。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 第一种场景:两表关联,关联键是分区键,但是没有过滤条件。
  • 第二种场景:两表关联,关联键是分区键,但是有过滤条件。
    • 这里又细分为两种子场景:
      • 1. 过滤条件为分区键
        • 2.过滤条件非分区键
        • 第三种场景:两表关联,关联键非分区键,但是过滤条件是分区键。
        • 第四种场景:分区表关联,关联键也是分区键,但是两张分区表分区算法、或者分区数目有差异。
        • 那基于表关联是否该用分区表做个总结:
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档