MySQL分区详解

MySQL分区的好处

本文参考了《深入浅出MySQL》第二版。相当于是此书的读书笔记

优化查询提高数据库运维，快速删除

分区类型

注意:    任何分区，要么没有主键/唯一键，要么主键/唯一键包涵分区键    才可以分区,否则报错    分区的名字不区分大小写

1，range

根据分区间取值范围对数据表进行分区，区间要连续且不能重叠关键字:    partition by range (partition_key);    value less than （partition_key_value）;    value less than maxvaluerange分区常用情况：    1，删除过期的数据    2，运行包涵分区键的查询

example

    create table `emp_range`(         id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine = innodb        partition by range(store_id)(             partition p0 values less than(10),            partition p1 values less than(20),            partition p2 values less than(30)        );--------------------------------------------------    create table `emp_date`(         id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine = innodb        partition by range(YEAR(separated))(             partition p0 values less than (1996),            partition p1 values less than (2000),            partition p2 values less than (2005)        );---------------------------------------------------    create table emp_date_not_int(        id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine = innodb        partition by range(separated)(             partition p0 values less than ('1970-01-01'),            partition p1 values less than ('1980-01-01'),            partition p2 values less than ('1990-01-01')        );

list

利用离散的枚举列值进行分区，列值必须全部包含。关键字:    patition by list ()    values in ()

example

    create table expenses_list_category(         expense_date date not null,        category int,        amount decimal(10,3)    )   engine=innodb default charset=utf8        partition by list(category)(             partition p0 values in (3,5),            partition p1 values in (1,10),            partition p2 values in (2,4,6,7,8,9)        );------------------------------------------------------    create table expenses_list_category_not_int(         expense_date date not null,        category int,        amount decimal(10,3)    )   engine=innodb default charset=utf8        partition by list(category)(             partition p0 values in ('niu'),            partition p1 values in ('niushao','gang'),            partition p2 values in ('niusahogang')        );

hash partition by [linear] hash () partition num

利用散列函数，分散热点读取，数据平均分布分为常规hash分区和线性hash分区对应算法是取模算法和线性2的幂次方算法常规hash分区可以做到更均衡，不可随时增加分区，而不适合需要灵活变动的需求线性hash分区均性没有常规hash好，但可以随时增加分区

example

    create table emp_hash(         id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine=innodb default charset=utf8        partition by hash(store_id) partitions 4;    --------------------------------------------------    create table emp_ar_hash(         id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine=innodb default charset=utf8        partition by linear hash(store_id) partitions 4;

key

类似于hash分区,分区键除了文本不局限于int     partition by [linear] key() partitions 4;

example

    create table emp_key(         id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine=innodb default charset=utf8        partition by key(job) partitions 4;----------------------------------------------------    create table emp_key(         id int not null,        ename varchar(30),        hired date not null default '1970-01-01',        separated date not null default '9999-12-21',        job varchar(30) not null,        store_id int not null    )   engine=innodb default charset=utf8        partition by linear key(job) partitions 4;

Columns

包涵column下的range和list分区支持多列分区(多列分区中的组元比较法，从左往右，依次类推)

example

    create table rc3_column(        a int,        b int    )   engine=innodb default charset=utf8        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(20,30),            partition p3 values less than(30,40),            partition p4 values less than(40,maxvalue),            partition p5 values less than(maxvalue,maxvalue)        );

子分区

又叫做复合分区，给range或者list分区之后的分区再进行hash或者key分区

example

    create table ts(        id int,        purchased date    )   engine=innodb default charset=utf8        partition by range(YEAR(purchased))        subpartition by hash(to_days(purchased))        subpartition 2        (             partition p0 values less than(1990),            partition p1 values less than(2000),            partition p2 values less than maxvalue        );---------------------------------------------------------------    CREATE TABLE tb_sub_ev (id INT, purchased DATE)        PARTITION BY RANGE( YEAR(purchased) )        SUBPARTITION BY HASH( TO_DAYS(purchased) ) (            PARTITION p0 VALUES LESS THAN (1990) (                SUBPARTITION s0,                SUBPARTITION s1            ),            PARTITION p1 VALUES LESS THAN (2000) (                SUBPARTITION s2,                SUBPARTITION s3            ),            PARTITION p2 VALUES LESS THAN MAXVALUE (                SUBPARTITION s4,                SUBPARTITION s5            ));