存储管理（三）：分区表

什么是分区表

---

假设存在表t：

CREATETABLE `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中初始化插入了两行记录， 按照定义的分区规则， 这两行记录分别落在p_2018和p_2019这两个分区上。

从图中可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：

1. 对于引擎层来说， 这是4个表。
2. 对于Server层来说， 这是1个表。

注：分区表是由server层定义的，而非引擎层。

分区表的引擎层行为

---

序列示例，在分区表加间隙锁，目的是说明对于InnoDB来说，这是4个表。

初始化表t的时候， 只插入了两行数据， ftime的值分别是， ‘2017-4-1’ 和’2018-4-1’ 。

InnoDB引擎层行为

session A的select语句对索引ftime上这两个记录之间的间隙加了锁。 如果是一个普通表的话，那么T1时刻， 在表t的ftime索引上， 间隙和加锁状态如图：

也就是说， ‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。 那么， sesion B的两条插入语句应该都要进入锁等待状态。

但是， 从上面的实验效果可以看出， session B的第一个insert语句是可以执行成功的。

这是因为， 对于引擎来说， p_2018和p_2019是两个不同的表， 也就是说2017-4-1的下一个记录并不是2018-4-1， 而是p_2018分区的supremum（上界）。 所以T1时刻， 在表t的ftime索引上， 间隙和加锁的状态如下：

由于分区表的规则， session A的select语句其实只操作了分区p_2018， 因此加锁范围就是图中深绿色的部分。

sesson B加锁信息（show engine innodb status）：

总结：单个分区加锁，不影响其它分区。

MyISAM引擎层行为

先用alter table t engine=myisam， 把表t改成MyISAM表。

然后， 我再用下面这个例子说明， 对于MyISAM引擎来说， 这是4个表。

在session A里面， 我用sleep(100)将这条语句的执行时间设置为100秒。 由于MyISAM引擎只支持表锁， 所以这条update语句会锁住整个表t上的读。

问1：从上图执行结果看，session B的第一条查询语句是可以正常执行的， 第二条语句才进入锁等待状态。是什么原因？

答：这是因为MyISAM的表锁是在引擎层实现的，session A加表锁，其实是所在分区p_2018上。因此，只会堵住在这个分区上的查询，落到其它分区的查询是不受影响的。

使用分区表的一个重要原因就是单表过大。那么，如果不使用分区表的话，就需要使用手动分表的方式。

问2：手动分表和分区表有什么区别？

1. 分区表：在server层看来是一张表，由server层来决定使用哪个分区。
2. 手工分区表：在server层看来是多张表，由应用层代码来决定使用哪个分区表。
3. 从引擎层来看，这两种方式并没有差别。

问3：手动分表和分区表对MDL加锁行为的影响？

1. 分区表：分区表加MDL锁时，针对所有分区。
2. 手工分表加MDL锁时，仅针对一个分表。

分区策略

---

每当第一次访问一个分区表时，MySQL需要把所有的分区都访问一遍。

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

示例：创建一个包含了很多分区的表t_myisam，执行一条插入语句后报错：

insert语句只需要访问一个分区，但语句却无法执行。

注：该表使用的是MyISAM引擎，如果使用InnoDB引擎并不会出现上述错误。

问：为什么MyISAM分区表会报错，而InnoDB表不会报错？

答：因为open_files_limit参数限制的是MySQL server层打开句柄数，而MyISAM分区表是由server层控制的，InnoDB分区表是由引擎层控制的。

通用分区策略

MyISAM分区表使用的分区策略， 我们称为通用分区策略（generic partitioning），每次访问分区都由server层控制。

正是由于MyISAM分区表的打开是由server层控制的，所以当MyISAM分区表数量过多时，才会导致上述错误。

通用分区策略， 是MySQL一开始支持分区表的时候就存在的代码， 在文件管理、 表管理的实现上很粗糙， 因此有比较严重的性能问题。

本地分区策略

从MySQL 5.7.9开始， InnoDB引擎引入了本地分区策略（native partitioning） 。

这个策略是在InnoDB内部自己管理打开分区的行为。

MySQL从5.7.17开始， 将MyISAM分区表标记为即将弃用（deprecated），意思是“从这个版本开始不建议这么使用， 请使用替代方案。 在将来的版本中会废弃这个功能”。

从MySQL 8.0版本开始， 就不允许创建MyISAM分区表了， 只允许创建已经实现了本地分区策略的引擎。 目前来看， 只有InnoDB和NDB这两个引擎支持了本地分区策略。

问： open_files_limit参数和innodb_open_files参数的区别？

1. open_files_limit限制MySQL打开句柄数（server层），在server层打开文件超过 open_files_limit这个值的时候，就会报错。
2. innodb_open_files限制InnoDB打开句柄数，在InnoDB引擎打开文件超过 innodb_open_files这个值的时候，就会关掉一些之前打开的文件。

分区表的server层行为

---

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

示例序列：

show processlist结果：

可以看到， 虽然session B只需要操作p_2107这个分区， 但是由于session A持有整个表t的MDL锁， 就导致了session B的alter语句被堵住。

总结：

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

注1：而关于“必要的分区”的判断， 就是根据SQL语句中的where条件， 结合分区规则来实现的。 比如：where ftime=‘2018-4-1’， 根据分区规则year函数算出来的值是2018， 那么就会落在p_2019这个分区。（具体内容见：分区表的引擎层行为）

注2：如果这个where 条件改成 where ftime>=‘2018-4-1’， 虽然查询结果相同， 但是这时候根据where条件， 就要访问p_2019和p_others这两个分区。

分区表的应用场景

---

1. 分区表的优点：
   1. 使用简单，对业务透明。
   2. 方便清理数据，例如：通过命令alter table t drop partition ...清理分区数据。该命令直接删除分区数据文件，类似drop普通表，相比于delete速度更快，影响更小。
2. 分区表缺点：
   1. 第一次访问，需要打开所有分区，可能占用很多文件句柄。
   2. 多个分区共用MDL锁，性能上可能不及手工分表。
3. 适用场景：存放历史数据，并需要定时归档清理。

小结：思考题

---

小结：

1. 分区表并非越细越好，一般数据量小于2000时，不建议使用分区表。
2. 分区表不宜一开始建立过多分区，可以通过自动化脚本动态维护少量可用分区即可。
3. 分区表的目标更多是提高可维护性，而非性能。

思考：举例的表中没有用到自增主键， 假设现在要创建一个自增字段id。 MySQL要求分区表中的主键必须包含分区字段。 如果要在表t的基础上做修改， 你会怎么定义这个表的主键呢？ 为什么这么定义呢？

答：由于MySQL要求主键包含分区字段，所以肯定是要创建联合主键。

两种选择：一种是(ftime, id)， 另一种是(id, ftime)。

1. 因为ftime做分区key，说明大多数语句都是包含ftime的，使用这种模式，可以利用前缀索引规则，减少一个索引。
2. 建议尽量使用InnoDB引擎。InnoDB表要求至少有一个索引，以自增字段作为第一个字段，所以需要加一个id的单独字段。

建表语句如下：

CREATETABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ftime`datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`ftime`,`id`),
  KEY `id` (`id`)
)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);

有关分区表的使用请参考：MySQL技术内幕InnoDB存储引擎第2版