mysql 表分区

 

修改表的主键
ALTER TABLE tb_channel_pv DROP PRIMARY KEY,ADD PRIMARY KEY (`id`,`channel`);

 

测试添加分区和删除分区

 

添加删除range分区

#(1)创建一个分区：
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by range columns(from_date)
(partition p01 values less than ('1985-12-31'),
partition p02 values less than ('1990-12-31'),
partition p03 values less than ('1995-12-31'),
partition p04 values less than ('2000-12-31'),
partition p05 values less than ('2005-12-31'),
partition p06 values less than ('2010-12-31'),
partition p07 values less than ('2015-12-31'),
partition p08 values less than ('2020-12-31'),
partition p09 values less than ('2025-12-31'),
partition p10 values less than ('2030-12-31')
);
 

#(2)添加分区：
#备注：不能超过p04的范围，严格递增每个分区，即最小不能小于前一个#分区
#下面新曾了两个分区n01和n02
 alter table titles
 reorganize partition p04 into(
 partition n01 values less than('1997-12-31'),
 partition n02 values less than('1998-12-31'),
 partition p04 values less than('2000-12-31')
 );

#(3)删除分区：
#删除分区即删除数据。
alter table titles drop partition p01; 

 

 

添加删除list分区

#(1)创建list分区
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by list columns(title)
(
partition p0 values in ('Assistant Engineer'),
partition p1 values in ('Engineer'),
partition p2 values in ('Manager'),
partition p3 values in ('Senior Engineer'),
partition p4 values in ('Senior Staff'),
partition p5 values in ('Staff'),
partition p6 values in ('Technique Leader')
);
 

#(2)添加分区
alter table titles add partition(partition p7 values in('CEO'));
 

#(3)删除分区：
alter table titles drop partition p0;

 

 

 

添加删除hash分区

#(1)创建hash分区
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by hash(emp_no)
partitions 4;
 

#(2)删除hash分区
4个分区减少到两个，4-2=2
alter table titles coalesce partition 2; 

#(3)添加hash分区
增加到5个，2+3=5
alter table titles add partition partitions 3;
 

 

 

添加删除key分区

#(1)创建key分区
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by key(emp_no)
partitions 4;
 

#(2)删除分区，同hash
alter table titles coalesce partition 2;


#(3)添加分区，同hash
alter table titles add partition partitions 3;