MySQL分区表 - xinwu-yang/cube-java GitHub Wiki

建表时分区

CREATE TABLE article_range (
    id INT auto_increment,
    title VARCHAR (64),
    content text,
    created_time DATETIME,
    PRIMARY KEY (id, created_time) -- 要求分区依据字段必须是主键的一部分
) charset = utf8 PARTITION BY RANGE COLUMNS(created_time) (
    -- 注意：分区的定义顺序依照created_time范围从小到大，不能颠倒
    PARTITION p20190101
    VALUES
        LESS THAN ('2019-01-31'),
        PARTITION p20190201
    VALUES
        LESS THAN ('2019-02-28'),
        PARTITION p20190301
    VALUES
        LESS THAN ('2019-03-31'),
        PARTITION pm -- 默认分区，最新数据往这里面存储
    VALUES
        LESS THAN (MAXVALUE)
);

分区结果：

动态添加表分区

增加分区需要用到 REORGANIZE 命令，它的作用是对某个分区重新分配。 比如明天是4月1号，那我们要给4月1号也增加个分区，实际上就是把 pm 分区拆分成2个分区。

ALTER TABLE
    article_range REORGANIZE PARTITION pm INTO (
        PARTITION p20190401
        VALUES
            LESS THAN ('2019-04-30'),
            PARTITION pm
        VALUES
            LESS THAN (MAXVALUE)
    );

分区结果：

已存在表分区

ALTER TABLE
    article_range PARTITION BY RANGE COLUMNS (created_time) (
        PARTITION p20190101
        VALUES
            LESS THAN ('2019-01-31'),
            PARTITION p20190201
        VALUES
            LESS THAN ('2019-02-28'),
            PARTITION p20190301
        VALUES
            LESS THAN ('2019-03-31'),
            PARTITION pm
        VALUES
            LESS THAN (MAXVALUE)
    );

移除分区

ALTER TABLE article_range REMOVE PARTITIONING; --移出表article_range所有分区，但不删除数据
ALTER TABLE article_range DROP PARTITION [分区名称]; --删除指定分区，分区中原有的数据也会随之删除！

验证

-- 插入测试数据
INSERT INTO `cube`.`article_range`(`title`, `content`, `created_time`) VALUES ('1', '1', '2019-01-01 15:10:33');
INSERT INTO `cube`.`article_range`(`title`, `content`, `created_time`) VALUES ('2', '2', '2020-02-01 00:00:00');

-- 查询分析 查询2019年1-3月的数据
EXPLAIN SELECT * FROM article_range WHERE created_time BETWEEN '2019-01-01 00:00:00' AND '2019-03-01 00:00:00';
-- 命中三个分区

最佳实践

建议是预先规划的方案，按照分区策略提前规划好5-10年的分区规模。如：按照时间每月分区，可提前建好10年的月份的表分区。