mysql 表分区操作

//不支持动态创建分区
CREATE TABLE `rpt_exp_event_bucket_creative_d_across` (
`bucket_id` VARCHAR(200) NOT NULL DEFAULT 'zhu' COMMENT '分桶id',
`day` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '日志时间',
PRIMARY KEY (bucket_id,`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_SECONDS(day))
(
PARTITION p20200228 VALUES LESS THAN (TO_SECONDS('20200229')) ENGINE = InnoDB,
PARTITION p20200229 VALUES LESS THAN (TO_SECONDS('20200301')) ENGINE = InnoDB,
PARTITION p20200301 VALUES LESS THAN (TO_SECONDS('20200302')) ENGINE = InnoDB,
PARTITION p20200302 VALUES LESS THAN (TO_SECONDS('20200303')) ENGINE = InnoDB
);

//支持动态创建分区
CREATE TABLE `rpt_exp_event_bucket_creative_d_across` (
`bucket_id` VARCHAR(200) NOT NULL DEFAULT 'zhu' COMMENT '分桶id',
`day` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '日志时间',
`partition_key` int(8) NOT NULL COMMENT '分区键(格式:yyyyMMdd)',
PRIMARY KEY (bucket_id,`day`,partition_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (partition_key)
(
PARTITION p20200228 VALUES LESS THAN (20200229) ENGINE = InnoDB,
PARTITION p20200229 VALUES LESS THAN (20200301) ENGINE = InnoDB
);

删除分区:
alter table rpt_exp_event_bucket_creative_d_across drop partition 20200229;
删除多分区:
alter table emp drop partition 20200229,20200301;

增加分区:
alter table rpt_exp_event_bucket_creative_d_across add partition(partition p202002301 values LESS THAN (202002302));

删除表的所有分区:
Alter table rpt_exp_event_bucket_creative_d_across removepartitioning; --不会丢失数据

 

分区表优点

在MySQL Server层分区表为一个表，而在MySQL存储引擎层分区表是多个表，因此有如下特点：
1、分区表对业务透明，只需要维护一个表的数据结构。
2、DML操作加锁仅影响操作的分区，不会影响未访问分区。
3、通过分区交换快速将数据换入和换出分区表。
4、通过TRUNCATE操作快速清理特定分区数据。
5、通过强制分区仅访问特定分区数据，减少操作影响。
6、通过大数据量分区能有效降低索引层数，提高查询性能。

分区表缺点

由于分区表在MySQL Server层为一个表，因此：
1、DDL操作需要锁定所有分区，导致所有分区上操作都被阻塞。
2、当表数据量较小时，分区表和非分区表性能相近，分区表效果有限。
3、当表数据量较大时，对分区表进行DDL或其他运维操作难度大风险高。
4、分区表使用较少，存在未知风险多，BUG多BUG多BUG多，MySQL社区版本免费，横向扩展成本低，分库分表实现简单且中间件完善。
5、当单台服务器性能无法满足时，对分区表进行分拆的成本较高，而分库分表能很容易实现横向分拆。
6、当分区表操作不当导致访问所有分区时，会导致严重的性能问题，而分库分表操作不当仅影响访问的表。
7、使用分库分表可以有效运维降低运维操作影响，对1亿数据量表做DDL操作需要谨慎评估，而对10万数据量表做DDL操作可以默认其很快完成。
8、使用分库分表可以有效减小宕机或其他故障影响，将数据分库分表到10套群集上，一套群集发生故障仅影响业务的一成。