百万级数据如何在原有表的基础上添加分区

前言

对用户来说，分区表是一个独立的路基表，但是底层是由多个物理子表组成的。实现分区的代码实际上是对一致底层表的句柄对象（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对与SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔命名的表文件。

MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的，而没有全局索引。

分区是指根据一定的规则，把数据库一个表分解成多个更小的表，更容易管理的部分。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区使用的场景

1. 表非常大以至于无法全部都放在内存中，或者只在比偶的最后部分有热点数据，其他均是历史数据。
2. 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清楚整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
4. 可以使用分区表来比年某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
5. 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表的限制

分区表本身也有一些限制，下面是其中比较重要的几点：

1. 一个表最多智能有1024个分区；
2. 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景可以直接使用列来进行分区。
3. 如uofenqu字段中有主键或者唯一索引的列，那么所有的主键列和唯一索引的列都必须包含进来。
4. 分区表无法使用外键约束

mysql分区类型

RANGE 分区：
    基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：
    类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：
    基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY分区：

    类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区：
    基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

分区需要注意的事项

以上已经说过分区使用的场景及限制，下面特别注意的情况：

1. 需要删除添加分区表的所有外键，包含其他表与该表的外键；
2. 创建分区时，当前分区的字段不能出现null数据库；
3. 创建一个表并对其分区,被创建分区的字段必须为主键,或者创建分区时表中没有主键；
4. == 要对表的时间字段（类型：datetime）基于月进行分区，当分区表达式使用YEAR（）或TO-DAYS（）函数时，也可以对在DATE或DATETIME列上分区的表应用修剪。此外，在MySQL 5.7中，当分区表达式使用TO_SECONDS（）函数时，可以对这些表应用修剪,否则将进行全表，全分区查询==。
5. == 删除分区，指定分区的数据也会同步删除，谨慎操作==

创建分区语句

使用alter table的方式添加分区，分区按照消息时间，大体是每个月一个分区：

ALTER TABLE `tb_monitor`  PARTITION BY RANGE(YEAR(collecTime)*100+MONTH(collecTime)) (
  		 PARTITION p_201601 VALUES LESS THAN (201601) ENGINE = InnoDB,
		PARTITION p_201602 VALUES LESS THAN (201602) ENGINE = InnoDB,
		PARTIT