十四、mysql 分区之 HASH && KEY

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

    

 

十四、mysql 分区之 HASH && KEY,布布扣,bubuko.com

十四、mysql 分区之 HASH && KEY

上一篇:应用SQLServer For XML 生成XML避免在C# 拼字符串


下一篇:十三、mysql 分区之 RANGE && LIST