什么是分区表？

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

分区表 - 图1
在表t中初始化插入了两行记录，按照定义的分区规则，两行记录分别落在p_2018和p_2019这两个分区上。

  • 创建了多少个分区就有多少个.ibd文件，对于引擎层来说这是4个表

  • 每个表都只有一个.frm文件用来保存表结构定义，对于server层来说这是1个表

    分区表的引擎层行为

    分区表加间隙锁的规则

    InnoDB引擎

    分区表 - 图2
    如上图中的两个会话

  • sessionA的select…for update语句，如果不是分区表的话，那么会在’2017-4-1’和’2018-4-1’之间加间隙锁[]，那么sessionB的两个insert语句都会被blocked。

  • 由于是分区表，2018-4-4和2017-4-1不再不同一张分区表中，因此只锁了2017-4-1到supremum

    MyISAM引擎

    对于MyISAM引擎来说，这是4个表
    分区表 - 图3
    由于MyISAM引擎没有行锁，只有表锁，因此sessionA会锁表，但是sessionB的第一个select是可以执行的，说明不是一个表。

使用分区表的原因是单表的数据量太大，如果不使用分区表，就要使用手动分表的方式。

分区表和手工分表的区别就是：一个由server层决定使用哪个分区，一个是由应用层代码决定的使用哪个分表。因此从引擎层看两种方式是没有差别的。

分区策略

分区表和手工分表两个方案的区别主要在server层上，从server层上看，我们就不得不提到分区表的一个诟病：打开分区表的行为。

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

MyISAM分区表使用的分区策略，称为默认分区策略，每次访问分区都由server层控制。

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

MySQL5.7.17开始，将MyISAM分区表标记为即将启用。

MySQL8.0开始，不再运行创建MyISAM分区表，只允许创建已经实现了本地分区策略引擎。InooDB和NDB

分区表的server层行为

从server层看的话，一个分区表就只是一个表。
分区表 - 图5
分区表 - 图6
如上边两个图中的会话，可以看出。

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

如果查询语句的where条件没有使用分区的key，就会访问所有的分区。

分区表使用场景

优势

对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁；分区表还可以很方便的清理历史数据。

如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候按照时间分区的分区表，就可以直接通过alert table t drop partition ..这个语法删掉分区，从而删掉过期的历史数据。
alert table t drop partition ..比delete的效率快。

总结

  • server层认为分区表就是一个表
  • 引擎层会把不同分区当作不同表
  • MySQL还支持hash分区、list分区等分区方法(mysql分区方法)
  • 由于分区表需要在第一次访问的时候访问所有的分区
    • 因此如果需要使用分区表，不要建太多的分区
    • 分区也不要提前预留太多，在使用之前预先创建即可