mysql实战45讲--- 43 使用分区表

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打开文件的数量限制。