MySQL 分区表简单操作记录

刺骨的言语ヽ痛彻心扉 2023-02-25 02:26 9阅读 0赞

详细的介绍可以看：[官方文档 - partitioning][- partitioning]。

## 分区限制 ##

1.  一个表最多1024个分区
2.  分区字段中的主键、唯一索引必须包含分区字段
3.  分区表无法使用外键约束

## 应用 ##

目前有一个工作流的操作日志表已经有2000W的数据量，查询速度非常的慢，为了解决查询的问题，对此表进行分区操作。

分区时建议创建一个 pnull 的空分区和一个 pmax 的最大值分区，pnull 可以在查询被包含进去时数据量很小或者0使得速度更快，pmax可以防止数据超出分区范围后仍然能很好的保留，并且方便后续的分区拆分。

如果不能按照分区键优化查询时，会对所有分区进行查询，因此分区数量不要一下子就全创建上，根据需要进行增加效果更好。

分区SQL：

ALTER TABLE wf_operation_log
    DROP PRIMARY KEY
    PARTITION BY RANGE(TO_DAYS(operation_time))(
      PARTITION pnull VALUES LESS THAN (TO_DAYS('2020-01-01')),
      PARTITION p202001 VALUES LESS THAN (TO_DAYS('2020-02-01')),
      PARTITION p202002 VALUES LESS THAN (TO_DAYS('2020-03-01')),
      PARTITION p202003 VALUES LESS THAN (TO_DAYS('2020-04-01')),
      PARTITION p202004 VALUES LESS THAN (TO_DAYS('2020-05-01')),
      PARTITION p202005 VALUES LESS THAN (TO_DAYS('2020-06-01')),
      PARTITION p202006 VALUES LESS THAN (TO_DAYS('2020-07-01')),
      PARTITION p202007 VALUES LESS THAN (TO_DAYS('2020-08-01')),
      PARTITION p202008 VALUES LESS THAN (TO_DAYS('2020-09-01')),
      PARTITION p202009 VALUES LESS THAN (TO_DAYS('2020-10-01')),
      PARTITION p202010 VALUES LESS THAN (TO_DAYS('2020-11-01')),
      PARTITION p202011 VALUES LESS THAN (TO_DAYS('2020-12-01')),
      PARTITION pmax VALUES LESS THAN MAXVALUE
    );

> 数据从 2020 年 1 月份开始，所以 pnull 没有值。

后续如果到了2021年，需要提前对 pmax 进行拆分：

ALTER TABLE `wf_operation_log` 
      REORGANIZE PARTITION pmax INTO (
        PARTITION p202012 VALUES LESS THAN (TO_DAYS('2021-01-01')),
        PARTITION p202101 VALUES LESS THAN (TO_DAYS('2021-02-01')),
        PARTITION p202102 VALUES LESS THAN (TO_DAYS('2021-03-01')),
        PARTITION p202103 VALUES LESS THAN (TO_DAYS('2021-04-01')),
        PARTITION p202104 VALUES LESS THAN (TO_DAYS('2021-05-01')),
        PARTITION p202105 VALUES LESS THAN (TO_DAYS('2021-06-01')),
        PARTITION p202106 VALUES LESS THAN (TO_DAYS('2021-07-01')),
        PARTITION p202107 VALUES LESS THAN (TO_DAYS('2021-08-01')),
        PARTITION p202108 VALUES LESS THAN (TO_DAYS('2021-09-01')),
        PARTITION p202109 VALUES LESS THAN (TO_DAYS('2021-10-01')),
        PARTITION p202110 VALUES LESS THAN (TO_DAYS('2021-11-01')),
        PARTITION p202111 VALUES LESS THAN (TO_DAYS('2021-12-01')),
        PARTITION p202112 VALUES LESS THAN (TO_DAYS('2022-01-01')),
        PARTITION pmax VALUES LESS THAN MAXVALUE
    );

如果历史数据不需要了，可以删除分区：

ALTER TABLE `wf_operation_log` DROP PARTITION p202001;

实际操作创建分区的时候，建议先建个空表进行测试，SQL语句没问题后再继续。

> 建议备份数据进行测试，通过测试预估耗时和分区后的效果。

[- partitioning]: https://dev.mysql.com/doc/refman/5.7/en/partitioning.html