什么是数据库分区：
以mysql为例，mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件：
frm存放表结构
myd存放表数据
myi存表索引
如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能。在执行查询的时候，优化器会根据分区定义过滤哪些没有我们需要数据的分区，这样查询就无须全表扫描所有分区，只查找包含需要数据的分区即可。（查询条件包含了可以过滤分区的条件）
分区和分表的区别：
一张大表进行分区后，他还是一张表，不会变成二张表，对开代码端来说是透明的，但是他存放数据的区块变多了。分区是根据一定的规则把数据文件和索引文件进行了分割，还多出了一个.par文件，打开.par文件后你可以看出他记录了这张表的分区信息。
分区对于程序来说是透明的。
分区一般只能起到水平切分的作用。
创建分区：
1)分区列必须属于此表所有主键（组合）或唯一键（组合）的交集。关于分区列与主键的具体解释。如果没有主键和唯一键则没有此限制。
2)对于原生的RANGE分区，LIST分区，HASH分区，分区字段必须为整数字段名或返回确定整数的函数。
3)添加COLUMNS（支持RANGE分区，LIST分区），关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数。
4)在RANGE 分区中如果往分区列中插入NULL值会被当作最小的值来处理，在LIST分区中NULL值必须在枚举列表中否则插入失败，在HASH，KEY分区中NULL值会被当作0来处理。
RANGE分区：
根据范围分区，范围应该连续但是不重叠，不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。
//创建range分区表
mysql> CREATE TABLE IF NOT EXISTS `user` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
-> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
-> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY RANGE (id) (
-> PARTITION p0 VALUES LESS THAN (3),
-> PARTITION p1 VALUES LESS THAN (6),
-> PARTITION p2 VALUES LESS THAN (9),
-> PARTITION p3 VALUES LESS THAN (12),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.13 sec)
//插入一些数据
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')
-> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)
-> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)
-> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)
-> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);
Query OK, 25 rows affected (0.05 sec)
Records: 25 Duplicates: 0 Warnings: 0
//到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是
[root@BlackGhost test]# ls |grep user |xargs du -sh
4.0K user#P#p0.MYD
4.0K user#P#p0.MYI
4.0K user#P#p1.MYD
4.0K user#P#p1.MYI
4.0K user#P#p2.MYD
4.0K user#P#p2.MYI
4.0K user#P#p3.MYD
4.0K user#P#p3.MYI
4.0K user#P#p4.MYD
4.0K user#P#p4.MYI
12K user.frm
4.0K user.par
//取出数据
mysql> select count(id) as count from user;
+-------+
| count |
+-------+
| 25 |
+-------+
1 row in set (0.00 sec)
//删除第四个分区
mysql> alter table user drop partition p4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
/**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区
只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的
最小区块是4K
*/
mysql> select count(id) as count from user;
+-------+
| count |
+-------+
| 11 |
+-------+
1 row in set (0.00 sec)
//第四个区块已删除
[root@BlackGhost test]# ls |grep user |xargs du -sh
4.0K user#P#p0.MYD
4.0K user#P#p0.MYI
4.0K user#P#p1.MYD
4.0K user#P#p1.MYI
4.0K user#P#p2.MYD
4.0K user#P#p2.MYI
4.0K user#P#p3.MYD
4.0K user#P#p3.MYI
12K user.frm
4.0K user.par
/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区
中，这样就比较好了，可以省去很多事情，看下面的操作*/
mysql> alter table aa partition by RANGE(id)
-> (PARTITION p1 VALUES less than (1),
-> PARTITION p2 VALUES less than (5),
-> PARTITION p3 VALUES less than MAXVALUE);
Query OK, 15 rows affected (0.21 sec) //对15数据进行分区
Records: 15 Duplicates: 0 Warnings: 0
//总共有15条
mysql> select count(*) from aa;
+----------+
| count(*) |
+----------+
| 15 |
+----------+
1 row in set (0.00 sec)
//删除一个分区
mysql> alter table aa drop partition p2;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
//只有11条了，说明对现有的表分区成功了
mysql> select count(*) from aa;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.00 sec)
RANGE COLUMNS分区
添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围应该连续但是不重叠。
CREATE TABLE rc3 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
LIST分区
数值必须被所有分区覆盖且不重叠，否则插入一个不属于任何一个分区的数值会报错。不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。
//这种方式失败
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
-> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',
-> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
-> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY LIST (province_id) (
-> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
-> PARTITION p1 VALUES IN (9,10,11,12,16,21),
-> PARTITION p2 VALUES IN (13,14,15,19),
-> PARTITION p3 VALUES IN (17,18,20,22,23,24)
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
//这种方式成功
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
-> `id` int(11) NOT NULL COMMENT '用户ID',
-> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',
-> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
-> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8
-> PARTITION BY LIST (province_id) (
-> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
-> PARTITION p1 VALUES IN (9,10,11,12,16,21),
-> PARTITION p2 VALUES IN (13,14,15,19),
-> PARTITION p3 VALUES IN (17,18,20,22,23,24)
-> );
Query OK, 0 rows affected (0.33 sec)
LIST COLUMNS分区
与Range分区相同，添加COLUMNS关键字可支持非整数和多列。
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
HASH分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。
Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。
传统Hash可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法，线性Hash分区。
mysql> CREATE TABLE IF NOT EXISTS `hash_part` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',
-> `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',
-> `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY LINEAR HASH(id)
-> PARTITIONS 3;
Query OK, 0 rows affected (0.06 sec)
key分区
类似HASH分区，但使用的用户定义的表达式，可支持非整数。KEY分区使用的哈希函数是由MySQL 服务器提供。也有对应与线性Hash的线性Key分区方法。
mysql> CREATE TABLE IF NOT EXISTS `key_part` (
-> `news_id` int(11) NOT NULL COMMENT '新闻ID',
-> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',
-> `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',
-> `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8
-> PARTITION BY HASH(YEAR(create_time))
-> PARTITIONS 3;
Query OK, 0 rows affected (0.07 sec)
新增分区：
//range添加新分区
mysql> alter table user add partition(partition p4 values less than MAXVALUE);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
//list添加新分区
mysql> alter table list_part add partition(partition p4 values in (25,26,28));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
//range重新分区
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
Query OK, 11 rows affected (0.08 sec)
Records: 11 Duplicates: 0 Warnings: 0
//list重新分区
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
//hash重新分区
mysql> alter table hash_part add partition partitions 4;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
//key重新分区
mysql> alter table key_part add partition partitions 4;
Query OK, 1 row affected (0.06 sec) //有数据也会被重新分配
Records: 1 Duplicates: 0 Warnings: 0
原有数据会按照新的分区重新分区。
查询优化：
一般来说，就是在where条件中加入分区列。
5.7以前的版本显示分区的执行计划使用：explain PARTITIONS；5.7以后直接执行：explain
要点：
1.一张大表进行分区后，他还是一张表，不会变成二张表，对开代码端来说是透明的。
2.分区列必须属于此表所有主键（组合）或唯一键（组合）的交集。
3.对于原生的RANGE分区，LIST分区，HASH分区，分区字段必须为整数字段名或返回确定整数的函数。
4.RANGE：行数据基于属于一个给定连续区间的列值放入分区。LIST：和RANGE分区类型一样，只是LIST分区面向的是离散的值。
5.添加COLUMNS（支持RANGE分区，LIST分区），关键字可定义非integer范围及多列范围。
6.分区表在清理数据归档时非常有用，比如按天分区，如果要按天清理过期数据，只需要删除分区即可，速度快，消耗资源少。
使用 PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (6),
...
);
参考：
http://blog.51yip.com/mysql/1013.html
http://haitian299.github.io/2016/05/26/mysql-partitioning/
