读mysql45讲-分区表

创建一个分区表：

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锁冲突。

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

实际上，MySQL还支持hash分区、 list分区等分区方法，实际使用时，分区表跟用户分表比起来，有两个绕不开的问题：一个是第一次访问的时候需要访问所有分区，另一个是共用MDL锁。