Mysql分区 - Range分区

1、Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。
2、Hash（哈希） – 这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。
3、Key（键值） – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
4、List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。
5、Composite（复合模式） – 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

结论

• 分区和未分区占用文件空间大致相同 （数据和索引文件）
• 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间
• 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
• 对于大数据量，建议使用分区功能。
• 去除不必要的字段
• 根据手册， 增加myisam_max_sort_file_size 会增加分区性能( mysql重建索引时允许使用的临时文件最大大小)

一、RANGE分区

1、按范围分区的表的分区方式是，每个分区都包含分区表达式值位于给定范围内的行。范围应连续但不重叠，并使用小于运算符的值定义。

2、当往分区列中插入null值RANG 分区会将其当作最小值来处理即插入最小的分区中

1、创建分区

#创建新的表
CREATE TABLE `tesc`.`tange_test`  (
  `id` int NULL,
  `name` varchar(255) NULL,
  `date` datetime NULL 
)
PARTITION BY RANGE (year(date))
(
PARTITION p0 VALUES LESS THAN (1995) , PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , 
PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , 
PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , 
PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , 
PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN MAXVALUE 
);

#已存在的表
ALTER TABLE `tesc`.`tange_test` PARTITION BY RANGE (year(date)) 
(
PARTITION p0 VALUES LESS THAN (1995) , PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , 
PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , 
PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , 
PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , 
PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN MAXVALUE 
);

p0：指分区p0,这个分区名的取值可以随便取值只要同一个表里面的每个分区名不重复即可，也不需要非得从0开始，你也可以使用a、b、c、d。

THEN()：分区的范围值，这个值只能的连续不重叠的从小到大的值。

插入测试数据

insert into tange_test(id,`name`,date) values(1,'zhangsan','1990-09-02'),(2,'lisi','2000-09-02'),(3,'wangwu','2011-09-02'),(4,'liuliu','2021-09-02');

2、删除分区

ALTER TABLE tange_test DROP PARTITION p11;

注意：通过这种删除分区的方式会将分区中的数据也删除，慎用！！！！。但是通过删除分区的方式删除数据会比delete快很多，因为它相当于删除一个数据库一样因为每个分区都是一个独立的数据文件。用来删除历史分区数据是非常好的办法。

3、增加分区

ALTER TABLE tange_test ADD PARTITION ( PARTITION p11 VALUES LESS THAN MAXVALUE );

注意：增加分区只能在最大端增加

4、拆分合并分区

无论是拆分还是合并分区都不能改变分区原本的覆盖范围，并且合并分区只能合并连续的分区不能跳过分区合并；并且不能改变分区的类型，例如不能把range分区改成key分区等。

拆分合并分区统称为重新定义分区，拆分分为不会造成数据的丢失，只将会将数据从一个分区移动到另一个分区。

原分区

1、将p0、p1合并成p0

ALTER TABLE tange_test REORGANIZE PARTITION p0,p1 
INTO (
	PARTITION p0 VALUES LESS THAN (1996)
);

2、将p0拆分成p0、p1

ALTER TABLE tange_test REORGANIZE PARTITION p0 
INTO (
	PARTITION p0 VALUES LESS THAN (1995),
	PARTITION p1 VALUES LESS THAN (1996)
);

5、移除表的分区

ALTER TABLE tange_test REMOVE PARTITIONING ;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除