43 使用分区表
有些时候，一些公司的规范不允许使用分区表，分区表有什么问题？
分区表是什么？
先创建一个分区表
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在磁盘上的文件
-rw-rw---- 1 mysql mysql 8586 Apr 3 09:33 t.frm -rw-rw---- 1 mysql mysql 52 Apr 3 09:33 t.par -rw-rw---- 1 mysql mysql 114688 Apr 3 09:33 t#P#p_2017.ibd -rw-rw---- 1 mysql mysql 114688 Apr 3 09:33 t#P#p_2018.ibd -rw-rw---- 1 mysql mysql 114688 Apr 3 09:33 t#P#p_2019.ibd
在表中插入2条记录，按照分区定义规则，这2个记录分别落在2个分区上，
这个分区表，包含了一个.frm文件，每个分区对应一个ibd文件
--对于engine层来说，这是4个表
--对于server层来说，这是1个表
分区表的引擎层行为
|
SESSION A |
SESSION B |
|
|
T1 |
begin; select * from t where ftime='2017-2-1' for update; |
|
|
T2 |
insert into t values('2018-2-1',1); (OK) insert into t values('2017-12-1',1);(block) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
在初始化表t的时候，只插入了两行数据，ftime的值分别是('2017-4-1',1),('2018-4-1',1),session A的select语句对索引ftime上这两个记录之间的间隙加了锁，
如果一个普通表的话，在T1时刻，在表t的索引上，间隙和加锁状态时应该是图下
也就是说，('2017-4-1',1)和('2018-4-1',1)这两个记录之间的间隙是会被锁住，那么session B的两条插入语句应该都要进入锁等待状态。
从实验上面的效果可以看出，session B的第一个insert语句是可以执行的，这是因为对于引擎来说，p_2018和p_2016是两个不同的表，
也就是说2017-4-1的下一个记录并不是2018-4-1而是p_2018分区的supermum，所以在T1时刻，在表t的ftime索引上，间隙和加锁的状态如下
由于分区的规则，session A的select语句其实只操作了分区p_2018，因此加锁的范围就是上图绿色的部分。
所以，session B要写入一行ftime是2018-2-1的时候是成功的，而要写入2017-12-1就这记录就会等待session A的间隙锁。
下面看myisam分区表的例子
用alter table t engine=myisam; 把t表的引擎改为myisam
|
SESSION A |
SESSION B |
|
alter table t engine=myisam; update t set c=sleep(100) where ftime=’2017-4-1’; |
|
|
select * from t where ftime=’2018-4-1’; select * from t where ftime=’2017-5-1’;(block) |
在SESSION A里面，用sleep（100）将这个语句的执行时间设置为100秒，由于myisam引擎只支持表锁，这个update将会锁主t表的读锁，从结果看，
session B的第一条是可以正常执行的，第二个才进入锁等待状态，这是因为myisam的表锁是引擎层实现，session a加的表锁，是在分区p_2018上面，因此只会堵塞在这个分区上执行的查询操作。
手动分表和分区的差别
分区表和手工分表，一个是由server层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表，因此，从引擎层来看，这两种方式是没有差别的。
其实这两个方案的区别，主要是在server层上，从server层看，不得不提到一个分区的问题：打开表的行为。
分区策略
每当第一次访问一个分区表的时候，mysql需要把所有的分区有访问一遍，一个典型的报错情况是这样的，如果一个分区表的分区很多，比如超过了1000个，而mysql启动的时候，
open_files_limit参数使用的是默认值1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。
--insert to t_myisam values(‘2017-4-1’,1);
--ERROR1016 ,too many open files;
这个表是myisam引擎，如果使用innodb引擎的话，并不会出现这个问题。
Myisam分区表使用的分区策略，称为通用分区策略，每次访问分区都由server层控制，通用分区策略，是mysql一开始支持分区表的行为，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。
从mysql 5.7.9 开始，innodb引擎引入了本地分区策略，这个策略是在innodb内部自己管理打开分区的行为。
Mysql 从5.7.17开始，将myisam分区表标记为弃用，从8.0版本开始，myisam不允许创建分区表。
分区表的server层行为
从server层看的话，分区表就只是一个表
|
SESSION A |
SESSION B |
|
begin; select * from t where ftime=’2018-4-1’; |
|
|
alter table t truncate partition p_2017;(block) |
--分区表的mdl锁
可以看到，虽然session b只操作了分区P-2017，但是由于sesion a持有整个表的mdl锁，就导致了session b的alter语句被堵住。
分区表，在做ddl的时候，影响会更大，如果使用的是普通分表，那么在truncate分表的时候，肯定不会跟另外一个分表上的查询语句，出现mdl锁冲突。
小结一下:
--1 mysql在第一次打开分区表的时候，需要访问所有的分区
--2 在server层，认为这是同一个表，因此所有分区公用一个mdl锁
--3 在engine层，认为这是不同的表，因此mdl锁之后的执行过程，会根据分区表规则，只访问必要的分区。
而关于”必要的分区”的判断，就是根据sql中的where条件，结合分区规则来实现，where ftime=’2017-4-1’。但是如果这个条件修改为where ftime>=’2017-4-1’,那么就会访问多个分区。
如果查询语句的where条件汇总没有分区key，那么就要扫描整个分区了，所以在使用业务分区表的时候，where条件中必须要使用分区的key。
分区表的应用场景
分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁，还有，分区表可以很方便的清理历史数据。
如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求，这时候，按照时间分区的分区表，就可以直接通过alter table t drop partition，这个语法删除分区，从而删掉过期的历史数据。与普通表的drop类似。
--参数innodb_open_files，在innodb打开文件超过这个参数的值，就会掉一些之前打开的文件。
--参数open_files_limit，mysql打开文件的数量限制。