MySQL实战45讲学习笔记:第四十三讲 要不要使⽤分区表

我经常被问到这样⼀个问题:分区表有什么问题,为什么公司规范不让使⽤分区表呢?今天,我们就来聊聊分区表的使⽤⾏ 为,然后再⼀起回答这个问题。

分区表是什么?

为了说明分区表的组织形式,我先创建⼀个表t:

CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

 图1 表t的磁盘⽂件

我在表t中初始化插⼊了两⾏记录,按照定义的分区规则,这两⾏记录分别落在p_2018和p_2019这两个分区上。

可以看到,这个表包含了⼀个.frm⽂件和4个.ibd⽂件,每个分区对应⼀个.ibd⽂件。也就是说:

对于引擎层来说,这是4个表;

对于Server层来说,这是1个表。

你可能会觉得这两句都是废话。其实不然,这两句话⾮常重要,可以帮我们理解分区表的执⾏逻辑。

分区表的引擎层⾏为

我先给你举个在分区表加间隙锁的例⼦,⽬的是说明对于InnoDB来说,这是4个表。

 图2 分区表间隙锁示例

这⾥顺便复习⼀下,我在第21篇⽂章和你介绍的间隙锁加锁规则。

我们初始化表t的时候,只插⼊了两⾏数据, ftime的值分别是,‘2017-4-1’ 和’2018-4-1’ 。session A的select语句对索引ftime 上这两个记录之间的间隙加了锁。如果是⼀个普通表的话,那么T1时刻,在表t的ftime索引上,间隙和加锁状态应该是图3这 样的。

 图3 普通表的加锁范围

也就是说,‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。那么,sesion B的两条插⼊语句应该都要进⼊锁等 待状态。

但是,从上⾯的实验效果可以看出,session B的第⼀个insert语句是可以执⾏成功的。这是因为,对于引擎来说,p_2018和 p_2019是两个不同的表,也就是说2017-4-1的下⼀个记录并不是2018-4-1,⽽是p_2018分区的supremum。所以T1时刻,在 表t的ftime索引上,间隙和加锁的状态其实是图4这样的:

 图4 分区表t的加锁范围

由于分区表的规则,session A的select语句其实只操作了分区p_2018,因此加锁范围就是图4中深绿⾊的部分。 所以,session B要写⼊⼀⾏ftime是2018-2-1的时候是可以成功的,⽽要写⼊2017-12-1这个记录,就要等session A的间隙 锁。

图5就是这时候的show engine innodb status的部分结果。

 图5 session B被锁住信息

看完InnoDB引擎的例⼦,我们再来⼀个MyISAM分区表的例⼦。

我⾸先⽤alter table t engine=myisam,把表t改成MyISAM表;然后,我再⽤下⾯这个例⼦说明,对于MyISAM引擎来说,这 是4个表。

 图6 ⽤MyISAM表锁验证

在session A⾥⾯,我⽤sleep(100)将这条语句的执⾏时间设置为100秒。由于MyISAM引擎只⽀持表锁,所以这条update语句 会锁住整个表t上的读。

但我们看到的结果是,session B的第⼀条查询语句是可以正常执⾏的,第⼆条语句才进⼊锁等待状态。

这正是因为MyISAM的表锁是在引擎层实现的,session A加的表锁,其实是锁在分区p_2018上。因此,只会堵住在这个分区 上执⾏的查询,落到其他分区的查询是不受影响的。

看到这⾥,你可能会说,分区表看来还不错嘛,为什么不让⽤呢?我们使⽤分区表的⼀个重要原因就是单表过⼤。那么,如果 不使⽤分区表的话,我们就是要使⽤⼿动分表的⽅式。

接下来,我们⼀起看看⼿动分表和分区表有什么区别。

⽐如,按照年份来划分,我们就分别创建普通表t_2017、t_2018、t_2019等等。⼿⼯分表的逻辑,也是找到需要更新的所有 分表,然后依次执⾏更新。在性能上,这和分区表并没有实质的差别。

分区表和⼿⼯分表,⼀个是由server层来决定使⽤哪个分区,⼀个是由应⽤层代码来决定使⽤哪个分表。因此,从引擎层看, 这两种⽅式也是没有差别的。

其实这两个⽅案的区别,主要是在server层上。从server层看,我们就不得不提到分区表⼀个被⼴为诟病的问题:打开表的⾏ 为。

分区策略

每当第⼀次访问⼀个分区表的时候,MySQL需要把所有的分区都访问⼀遍。

⼀个典型的报错情况是这样的:如果⼀个分区表 的分区很多,⽐如超过了1000个,⽽MySQL启动的时候,open_files_limit参数使⽤的是默认值1024,那么就会在访问这个表 的时候,由于需要打开所有的⽂件,导致打开表⽂件的个数超过了上限⽽报错。

下图就是我创建的⼀个包含了很多分区的表t_myisam,执⾏⼀条插⼊语句后报错的情况。

 图 7 insert 语句报错

可以看到,这条insert语句,明显只需要访问⼀个分区,但语句却⽆法执⾏。

这时,你⼀定从表名猜到了,这个表我⽤的是MyISAM引擎。是的,因为使⽤InnoDB引擎的话,并不会出现这个问题。

MyISAM分区表使⽤的分区策略,我们称为通⽤分区策略(generic partitioning),每次访问分区都由server层控制。通⽤分区 策略,是MySQL⼀开始⽀持分区表的时候就存在的代码,在⽂件管理、表管理的实现上很粗糙,因此有⽐较严重的性能问 题。

从MySQL 5.7.9开始,InnoDB引擎引⼊了本地分区策略(native partitioning)。这个策略是在InnoDB内部⾃⼰管理打开分区 的⾏为。

MySQL从5.7.17开始,将MyISAM分区表标记为即将弃⽤(deprecated),意思是“从这个版本开始不建议这么使⽤,请使⽤替代 ⽅案。在将来的版本中会废弃这个功能”。

从MySQL 8.0版本开始,就不允许创建MyISAM分区表了,只允许创建已经实现了本地分区策略的引擎。⽬前来看,只有 InnoDB和NDB这两个引擎⽀持了本地分区策略。

接下来,我们再看⼀下分区表在server层的⾏为。

分区表的server层⾏为

如果从server层看的话,⼀个分区表就只是⼀个表。

这句话是什么意思呢?接下来,我就⽤下⾯这个例⼦来和你说明。如图8和图9所示,分别是这个例⼦的操作序列和执⾏结果 图。

 

图8 分区表的MDL锁

  图9 show processlist结果

可以看到,虽然session B只需要操作p_2107这个分区,但是由于session A持有整个表t的MDL锁,就导致了session B的alter 语句被堵住。

这也是DBA同学经常说的,分区表,在做DDL的时候,影响会更⼤。如果你使⽤的是普通分表,那么当你在truncate⼀个分表 的时候,肯定不会跟另外⼀个分表上的查询语句,出现MDL锁冲突。

到这⾥我们⼩结⼀下:

1. MySQL在第⼀次打开分区表的时候,需要访问所有的分区;

2. 在server层,认为这是同⼀张表,因此所有分区共⽤同⼀个MDL锁;

3. 在引擎层,认为这是不同的表,因此MDL锁之后的执⾏过程,会根据分区表规则,只访问必要的分区。

⽽关于“必要的分区”的判断,就是根据SQL语句中的where条件,结合分区规则来实现的。⽐如我们上⾯的例⼦中,where ftime=‘2018-4-1’,根据分区规则year函数算出来的值是2018,那么就会落在p_2019这个分区。

但是,如果这个where 条件改成 where ftime>=‘2018-4-1’,虽然查询结果相同,但是这时候根据where条件,就要访问p_2019 和p_others这两个分区。

如果查询语句的where条件中没有分区key,那就只能访问所有分区了。当然,这并不是分区表的问题。即使是使⽤业务分表 的⽅式,where条件中没有使⽤分表的key,也必须访问所有的分表。

我们已经理解了分区表的概念,那么什么场景下适合使⽤分区表呢?

分区表的应⽤场景

分区表的⼀个显⽽易⻅的优势是对业务透明,相对于⽤户分表来说,使⽤分区表的业务代码更简洁。还有,分区表可以很⽅便 的清理历史数据。

如果⼀项业务跑的时间⾜够⻓,往往就会有根据时间删除历史数据的需求。这时候,按照时间分区的分区表,就可以直接通过 alter table t drop partition …这个语法删掉分区,从⽽删掉过期的历史数据。

这个alter table t drop partition …操作是直接删除分区⽂件,效果跟drop普通表类似。与使⽤delete语句删除数据相⽐,优势 是速度快、对系统影响⼩。

⼩结

这篇⽂章,我主要和你介绍的是server层和引擎层对分区表的处理⽅式。我希望通过这些介绍,你能够对是否选择使⽤分区 表,有更清晰的想法。

需要注意的是,我是以范围分区(range)为例和你介绍的。实际上,MySQL还⽀持hash分区、list分区等分区⽅法。你可以 在需要⽤到的时候,再翻翻⼿册。

实际使⽤时,分区表跟⽤户分表⽐起来,有两个绕不开的问题:⼀个是第⼀次访问的时候需要访问所有分区,另⼀个是共⽤ MDL锁。

因此,如果要使⽤分区表,就不要创建太多的分区。我⻅过⼀个⽤户做了按天分区策略,然后预先创建了10年的分区。这种 情况下,访问分区表的性能⾃然是不好的。这⾥有两个问题需要注意:

1. 分区并不是越细越好。实际上,单表或者单分区的数据⼀千万⾏,只要没有特别⼤的索引,对于现在的硬件能⼒来说都已 经是⼩表了。

2. 分区也不要提前预留太多,在使⽤之前预先创建即可。⽐如,如果是按⽉分区,每年年底时再把下⼀年度的12个新分区创 建上即可。对于没有数据的历史分区,要及时的drop掉。

⾄于分区表的其他问题,⽐如查询需要跨多个分区取数据,查询性能就会⽐较慢,基本上就不是分区表本身的问题,⽽是数据 量的问题或者说是使⽤⽅式的问题了。

当然,如果你的团队已经维护了成熟的分库分表中间件,⽤业务分表,对业务开发同学没有额外的复杂性,对DBA也更直观, ⾃然是更好的。

最后,我给你留下⼀个思考题吧。

我们举例的表中没有⽤到⾃增主键,假设现在要创建⼀个⾃增字段id。MySQL要求分区表中的主键必须包含分区字段。如果要 在表t的基础上做修改,你会怎么定义这个表的主键呢?为什么这么定义呢?

你可以把你的结论和分析写在留⾔区,我会在下⼀篇⽂章的末尾和你讨论这个问题。感谢你的收听,也欢迎你把这篇⽂章分享 给更多的朋友⼀起阅读。

posted @ 2022-01-22 11:37  求其在我  阅读(35)  评论(0编辑  收藏  举报