创建一个分区表:
CREATE TABLE `t` (
`ftime` datetime NOTNULL,
`c` int(11) DEFAULTNULL,
KEY (`ftime`) ) ENGINE=InnoDB DEFAULTCHARSET=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);
在表t中初始化插入了两行记录,按照定义的分区规则,这两行记录分别落在p_2018和p_2019 这两个分区上。
这个表包含了一个.frm文件和4个.ibd文件,每个分区对应一个.ibd文件。也就是说:
- 对于引擎层来说,这是4个表;
- 对于Server层来说,这是1个表。
初始化表t的时候,只插入了两行数据, ftime的值分别是,‘2017-4-1’ 和’2018-4-1’ 。 session A的select语句对索引ftime上这两个记录之间的间隙加了锁。如果是一个普通表的话, 那么T1时刻,在表t的ftime索引上,间隙和加锁状态应该是[2017-4-1,2018-4-1];
但是sessionB中前一个sql是可以查询成功的但是后一个是执行失败的。
是因为对于引擎层来说,p_2018和o_2019是两个表,所以在sessionA中加间隙锁的时候,2017-4-1的下一条记录并不是2018-4-1;而是是p_2018分区的supremum;所以实际上的间隙锁的范围是(2017-4-1,p_2018分区的supremum],所以sessionB中查询2018-2-1并不在间隙锁范围内,而2017-12-1是在间隙范围内的。
使用分区表的一个重要原因就是单表过大。那么,如果不使用分区表的话,就可以使用手动分表的方式。 比如,按照年份来划分,我们就分别创建普通表t_2017、t_2018、t_2019等等。手工分表的逻辑,也是找到需要更新的所有分表,然后依次执行更新。在性能上,这和分区表并没有实质的差别。
分区表和手工分表,一个是由server层来决定使用哪个分区,一个是由应用层代码来决定使用哪个分表(也就是后台查询代码中判断sql需要查询哪个表)。因此,从引擎层看,这两种方式也是没有差别的。
其实这两个方案的区别,主要是在server层上,分区表在打开表的行为有所不一样。
分区策略
每当第一次访问一个分区表的时候,MySQL需要把所有的分区都访问一遍。一个典型的报错情况是这样的:如果一个分区表的分区很多,比如超过了1000个,而MySQL启动的时 候,open_files_limit参数使用的是默认值1024,那么就会在访问这个表的时候,由于需要打开所有的文件,导致打开表文件的个数超过了上限而报错。
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层中,分区表其实是一张表,所以在sessionA会加上MDL读锁,导致在sessionB的sql会被阻塞住。
分区表在DDL操作影响会很大,如果使用的是手工分表,那就在一个表的DDL就不会影响其他表的查询,不会出现MDL锁冲突。
- MySQL在第一次打开分区表的时候,需要访问所有的分区;
- 在server层,认为这是同一张表,因此所有分区共用同一个MDL锁;
- 在引擎层,认为这是不同的表,因此MDL锁之后的执行过程,会根据分区表规则,只访问必要的分区。
实际上,MySQL还支持hash分区、 list分区等分区方法,实际使用时,分区表跟用户分表比起来,有两个绕不开的问题:一个是第一次访问的时候需要访问所有分区,另一个是共用MDL锁。