43 | 什么时候使用MySQL的分区表？（分区和分表是有区别的）

一、先干一碗鸡汤

任何事情都是循序渐进的，无论自己有多期待一个好结果，都是急不来的，事压不垮人，但是情绪会压垮人。

二、干巴得的内容

2.1.分区表语法是什么？引擎中的存储结构是什么？

• 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.frm  和 四个 t#P#p_2017.ibd  t#P#p_2018.ibd  t#P#p_2019.ibd  t#P#p_other.ibd  

2.2.分区表在引擎层和server层表现差别是什么？这个差别的重要性体现在什么地方？

• 引擎层来说是4张表，server层是1张表

• 加锁过程。举例【非唯一索引加锁】：sessionA=begin; select * from t where ftime='2017-5-1' for update; sessionB=insert into values('2017-12-1',1) --blocked;

示例：

P_2018 =（2017-4-1 ～ supremum）gap锁
P_2019 =（2018-4-1 ～ supremum）

2.3.分区表饱受诟病的原因是什么？分区表分区策略有哪两种？两者不同是什么？

• server层的打开表行为。

• 通用分区策略，由server层控制（MyISAM）和本地分区策略，有引擎内部控制（Innodb），

• 通用分区策略：一个典型的报错情况，如果一个分区表的分区很多，比如超过了 1000 个，而 MySQL 启动的时候，open_files_limit 参数使用的是默认值 1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。本地分区策略：在InnoDB引擎打开文件超过 innodb_open_files这个值的时候，就会关掉一些之前打开的文件。

2.4.分区表的应用场景？如何清理数据？

• 对业务透明，方便的清理历史数据。但是，一般分区表都很大，DDL耗时严重

• alter table t drop partition ... 操作是直接删除分区文件，效果跟 drop 普通表类似

2.5.分区和分表的区别参考下面文章

文章链接：https://blog.csdn.net/zhangqiang_accp/article/details/87365717