43 | 要不要使用分区表

分区表是什么？

创建一个表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);

插入的两行记录分别落在p_2018和p_2019这两个分区上。

这个表包含一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件：

• 对于引擎层来说，是4个表；
• 对于Server层来说，是1个表。

分区策略

每当第一次访问一个分区表的时候，MySQL需要把所有的分区都访问一遍。一个典型的报错情况是这样的：如果一个分区表的分区很多，比如超过了1000个，而MySQL启动的时候，open_files_limit参数使用的是默认值1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。

从MySQL 5.7.9开始，InnoDB引擎引入了本地分区策略，这个策略是在InnoDB内部自己管理打开分区的行为。

从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。

目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略。

分区表的server层行为

从server层看的话，一个分区表就只是一个表。

虽然只操作一个分区，但是会持有整个表t的MDL锁。

小结：

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

分区表的应用场景

分区表优势是对业务透明，对业务来说。使用分区表的业务代码更简洁。

分区表可以很方便的清理历史数据，按照时间分区的分区表，可以直接通过alter table t drop partition... 这个语法删掉分区，从而删掉过期的历史数据。

alter table t drop partition...操作是直接删除分区文件，效果跟drop普通表类似。与使用delete语句删除数据相比，优势是速度快，对系统影响小。

总结

MySQL除了上面说的以范围分区外，还支持hash分区、list分区等分区方法。

实际使用的时候，分区表跟用户分表比起来，有两个绕不开的问题：一个是第一次访问的时候需要访问所有分区，另一个是共用MDL锁。

因此，如果使用分区表，就不要创建太多的分区。

1. 分区并不是越细越好。事实上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说算是小表。
2. 分区也不要提前预留太多，使用之前预先创建即可。

比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量的问题或者说是使用方式的问题了。

 

上一篇：42 | grant之后要跟着flsh privileges吗？

下一篇：44 | 答疑（三）