Smarticen Notes

分区表

4 min read

一个分区表的例子如下：

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);

将年份低于2017的放入p_2017， 2018放入p_2018,2019放入p_2019。

每个分区对应一个.ibd文件，也就是说：

  • 对于引擎层来说，这是四个表
  • 对于Server层来说，这是一个表

这样可以将锁的粒度划分的更细，但是为什么不让使用分区表呢？

使用分区表的一个原因就是，单表过大，不使用分区表就要进行手动分表。

  • 分区表：由server层来决定使用哪个分区。
  • 手动分表：由应用层代码来决定使用哪个分表。 这两个方案的主要区别在server层上，从server层看，我们就不得不提到分区表一个被广为诟病的问题：打开表的行为。

分区策略

MyISAM引擎第一次访问一个分区表的时候，要把所有分区都访问一遍，如果一个分区表的分区很多，比如超过了1000个，那么就可能在打开表文件的时候超过了上限而报错。我们将这称为通用分区策略。

mysql5.7.9开始，InnoDB引擎引入了本地分区策略，让InnoDB内部自己管理打开分区的行为。

分区表的serer层行为

使用分区表，但是所有分区表共用一个MDL锁，所以如果执行MDL语句会很容易阻塞或者被阻塞。

  • MySQL 在第一次打开分区表的时候，需要访问所有的分区；
  • 在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；
  • 在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

分区表的应用场景

分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更加简洁。并且分区表可以很方便地清理历史数据。

一个业务跑得时间足够长，就有需要根据时间删除历史数据的需求，这就可以使用alter table t drop partition ...来删除分区从而删除历史数据。

Graph View