1.hash分区
PS::个人觉得HASH分区很好很强大，简单确分布极其均匀
创建实例：
CREATE TABLE HASH_EMP (
tid int,
tname char(255)
)
PARTITION BY HASH (tid) PARTITIONS 8;
将hash_emp进行的tid进行hash分区，并分为8个区
查询分区的数据分布情况:
select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema = schema() and table_name = 'hash_emp';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | tid | NULL | 0 |
| p1 | tid | NULL | 0 |
| p2 | tid | NULL | 0 |
| p3 | tid | NULL | 0 |
| p4 | tid | NULL | 0 |
| p5 | tid | NULL | 0 |
| p6 | tid | NULL | 0 |
| p7 | tid | NULL | 0 |
+----------------+----------------------+-----------------------+------------+
创建1个event，用来不间断写入数据，测试分布情况：
create event hash_emp_event on scheduler every 1 second do insert into hash_emp values (NULL,now());
set GLOBAL event_scheduler = 1; //开启调度器
再次查看分区数据分布情况:
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | tid | NULL | 41 |
| p1 | tid | NULL | 42 |
| p2 | tid | NULL | 42 |
| p3 | tid | NULL | 42 |
| p4 | tid | NULL | 42 |
| p5 | tid | NULL | 42 |
| p6 | tid | NULL | 42 |
| p7 | tid | NULL | 42 |
+----------------+----------------------+-----------------------+------------+
可以看出来，hash分布极其均匀:;
2.Key分区
PS::所谓key分区则是指mysql默认使用表的主键或唯一建进行分区管理
创建实例：
CREATE TABLE KEY_EMP (
tid int,
tname char(255)
)
PARTITION BY KEY (tid) PARTITIONS 8;
PS::因为跟hash差不多，就不进行过多测试了!!!
3.子分区
PS::顾名思义就是在分区上再建分区
PS::支持子分区的分区模式有range || list ，它们2者都可以支持hash或list的子分区
创建实例::
CREATE TABLE ZI_EMP (
tid int,
tname char(255)
)
PARTITION BY RANGE (tid)
SUBPARTITION BY HASH (tid)
SUBPARTITIONS 2 (
PARTITION p0 values less than (1990),
PARTITION p1 values less than (2028),
PARTITION p2 values less than (MAXVALUE)
);
将zi_emp分成了3个range分区，每个分区在分为2个子分区，如是，有了下面的分区结构:
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | tid | 1990 | 0 |
| p0 | tid | 1990 | 0 |
| p1 | tid | 2028 | 0 |
| p1 | tid | 2028 | 0 |
| p2 | tid | MAXVALUE | 0 |
| p2 | tid | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
也就是说，如果tid小于1990,那么数据会被hash分配到p0这2个子分区中
