记录一下MySQL的表分区常用操作

一、分区条件

  1. 一个表最多只能有1024个分区。
  2. MySQL5.1之后,才支持表分区功能,且分区表达式必须是整数。
  3. MySQL5.5之后,增加了COLUMNS分区(RANGE / LIST),支持整形、日期、字符串。
  4. 分区字段,必须包含在主键字段内。

二、预处理主键

目的:将分区字段添加到主键

ALTER TABLE <表名> DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `<分区字段>`);

例如:

ALTER TABLE tb_article DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `cdate`);

三、创建分区

定义分区规则:

PARTITION BY [ RANGE | LIST ] COLUMNS(<分区字段>)(
    PARTITION <分区名> VALUES <分区规则>,
    ...
)

可以在建表(CREATE TABLE)时直接创建分区:

CREATE TABLE `<表名>` (
    `<ID字段名>` varchar(20) NOT NULL COMMENT 'ID',
    `<分区字段名>` varchar(20) NOT NULL COMMENT '分区',
    ...  ,
    PRIMARY KEY (`<ID字段名>`, `<分区字段名>`)
)
PARTITION BY [ RANGE | LIST ] COLUMNS(<分区字段>)(
    PARTITION <分区名> VALUES <分区规则>,
    ...
);

也可以为现有表创建分区(ALTER TABLE),但需要提前将分区字段添加到主键。

ALTER TABLE `<表名>`
PARTITION BY [ RANGE | LIST ] COLUMNS(<分区字段>)(
    PARTITION <分区名> VALUES <分区规则>,
    ...
);

四、添加分区

ALTER TABLE `<表名>` ADD PARTITION (
    PARTITION <分区名> VALUES <分区规则>,
    ...
);

五、删除分区

同时也会删除分区内的数据

ALTER TABLE `<表名>` DROP PARTITION <分区名>;

六、实例1:创建 RANGE COLUMNS 分区

CREATE TABLE tb_article (
    id          VARCHAR(20)     NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    cdate       DATE            NOT NULL,
    PRIMARY KEY (id, cdate)
) 
PARTITION BY RANGE COLUMNS(cdate)(
    PARTITION p2018 values less than ('2018'),
    PARTITION p2019 values less than ('2019'),
    PARTITION p2020 values less than (MAXVALUE)
);

七、实例2:创建 LIST COLUMNS 分区

CREATE TABLE tb_article (
    id          VARCHAR(20)     NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    region      VARCHAR(20)     NOT NULL,
    PRIMARY KEY (id, region)
) 
PARTITION BY LIST COLUMNS(region)(
    PARTITION pEast values in ('east'),
    PARTITION pWest values in ('west'),
    PARTITION pNorth values in ('north'),
    PARTITION pSouth values in ('south')
);

八、分区的应用

  1. 按分区检索
    SELECT * FROM tb_article PARTITION(pEast);
    SELECT atc.* 
    FROM tb_article PARTITION(pEast) atc
    INNER JOIN tb_region rg ON rg.region = atc.region;

     

  2. 删除分区内的数据
    DELETE FROM tb_artical PARTITION(pEast)

     

  3. 修改分区内的数据
    UPDATE tb_artical PARTITION(pEast)
    SET title = CONCAT(title, ' - modify')
    WHERE id = 'xxx001';

     

posted @ 2020-04-12 23:30  网无忌  阅读(103)  评论(0编辑  收藏