十四、mysql 分区之 HASH && KEY_MySQL

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个子分区中