分区表

问题

为什么公司规范不让使用分区表？

分区表是什么？

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

• 创建表t，并根据字段ftime的年份映射来划分成四个分区
• 每个字段都设置对应的分区规则，比如年份小于2017的行记录则放入分区p_2017

因此分表后：

• 对于引擎层来说，相当于有四个ibd表。
• 对于server层来说，还是一个表。

分区表的引擎层行为

对于分区表来说，InnoDB在第一条语句上加锁时，只会在p_2018这一个分区表上加锁(2017-4-1,supremum)，其它分区表上的数据不会受到影响。

如果表t改成MyISAM引擎，结果依旧类似。MyISAM仅仅支持表锁，并且是在引擎层实现，因此sessionA执行后p_2018整个表都会被锁住。而落在其它分区的查询和操作不会受到影响。

分区表的server层行为

对于DDL语句来说，分区表在server层看来只是一张表。

sessionA持有的MDL锁不是单个分区表的锁，而是整个表t的MDL读锁。因此sessionB会被阻塞住。

而如果是在应用层代码手动分区的表，持有的MDL锁肯定不会阻塞到其它语句的查询，因为普通分表本身表之间都是独立的。

分区表的打开表行为

分区表打开表的行为：每当第一次访问一个分区表的时候，MySQL需要把所有的分区都访问一遍。这也是分区表广受诟病的地方！

因此如果一个表下创建了很多个分区表(MySQL启动open_files_limit默认最多打开1024个分区)，那么即使当前SQL语句只用到一个分区，语句执行时也会报错文件打开数过多。

分区表的应用场景

总而言之分区表具有以下特点：

• 优点在于对业务透明，并且相对于手动分表而言，业务代码更加简洁。
• 💣两个最大的缺点在于①所有分区表公用MDL锁②第一次访问会打开访问所有的分区。

✨方便处理历史数据：可以将一项业务的历史数据按照时间线进行分区。如果要删除某个时间段的历史数据，直接alter table t drop partition删除某个分区表。它会直接删除分区文件，速度快。

✨当然如果有成熟的分库分表中间件，也可以代替分区表，比如dbrouter