mysql创建分区

mysql分区相对于mysql分库分表便利很多,可以对现有的mysql大表添加分区,也可以对已有分区的表扩充分区。

mysql分区常用处理数据边界清晰的案列,入财务报表数据,我们可以按月对报表数据进行分区。

值得注意的是我们再用sql查询时,必须加上开始时间和结束时间的查询条件,将查询区间限制到相应的分区,否则会遍历所有分区。

一、如下案例:我们通过时间字段按月对表数据进行分区创建sql如下:

CREATE TABLE `test_all_summary` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `cost_type` int(2) DEFAULT NULL COMMENT '类型',
 `orderid` bigint(18) DEFAULT '0' COMMENT '单号',
   `all_amount` bigint(18) DEFAULT '0' COMMENT '总金额',
  `start_tm` date NOT NULL COMMENT '日期',
  PRIMARY KEY (`id`,`start_tm`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='表'
/*!50100 PARTITION BY RANGE (to_days(start_tm))
(PARTITION p201904 VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION p201905 VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION p201906 VALUES LESS THAN (737576) ENGINE = InnoDB,
 PARTITION p201907 VALUES LESS THAN (737606) ENGINE = InnoDB,
 PARTITION p201908 VALUES LESS THAN (737637) ENGINE = InnoDB,
 PARTITION p201909 VALUES LESS THAN (737668) ENGINE = InnoDB,
 PARTITION p201910 VALUES LESS THAN (737698) ENGINE = InnoDB,
 PARTITION p201911 VALUES LESS THAN (737729) ENGINE = InnoDB,
 PARTITION p201912 VALUES LESS THAN (737759) ENGINE = InnoDB,
 PARTITION p202001 VALUES LESS THAN (737790) ENGINE = InnoDB,
 PARTITION p202002 VALUES LESS THAN (737821) ENGINE = InnoDB,
 PARTITION p202003 VALUES LESS THAN (737850) ENGINE = InnoDB,
 PARTITION p202004 VALUES LESS THAN (737881) ENGINE = InnoDB,
 PARTITION p202005 VALUES LESS THAN (737911) ENGINE = InnoDB,
 PARTITION p202006 VALUES LESS THAN (737942) ENGINE = InnoDB,
 PARTITION p202007 VALUES LESS THAN (737972) ENGINE = InnoDB,
 PARTITION p202008 VALUES LESS THAN (738003) ENGINE = InnoDB,
 PARTITION p202009 VALUES LESS THAN (738034) ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN (738064) ENGINE = InnoDB,
 PARTITION p202011 VALUES LESS THAN (738095) ENGINE = InnoDB,
 PARTITION p202012 VALUES LESS THAN (738125) ENGINE = InnoDB,
 PARTITION p202101 VALUES LESS THAN (738156) ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN (738187) ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN (738215) ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN (738246) ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN (738276) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN (738307) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN (738337) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (738368) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (738399) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (738429) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (738460) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (738490) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

分区数据展示如下,表中我是按月对表数据进行分区的,表中的描述字段是数据库时间按天换算的有需要的可以自取: 

 

二、删除分区

 alter table test_all_summary drop PARTITION pmax ;

三、扩充分区,再原有分区的表上扩分区,需要删除原表中最大的分区。

 alter table test_all_summary drop PARTITION pmax ;

alter TABLE `test_all_summary` add PARTITION(
 PARTITION p202001 VALUES LESS THAN (737790) ENGINE = InnoDB,
 PARTITION p202002 VALUES LESS THAN (737821) ENGINE = InnoDB,
 PARTITION p202003 VALUES LESS THAN (737850) ENGINE = InnoDB,
 PARTITION p202004 VALUES LESS THAN (737881) ENGINE = InnoDB,
 PARTITION p202005 VALUES LESS THAN (737911) ENGINE = InnoDB,
 PARTITION p202006 VALUES LESS THAN (737942) ENGINE = InnoDB,
 PARTITION p202007 VALUES LESS THAN (737972) ENGINE = InnoDB,
 PARTITION p202008 VALUES LESS THAN (738003) ENGINE = InnoDB,
 PARTITION p202009 VALUES LESS THAN (738034) ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN (738064) ENGINE = InnoDB,
 PARTITION p202011 VALUES LESS THAN (738095) ENGINE = InnoDB,
 PARTITION p202012 VALUES LESS THAN (738125) ENGINE = InnoDB,
 PARTITION p202101 VALUES LESS THAN (738156) ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN (738187) ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN (738215) ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN (738246) ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN (738276) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN (738307) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN (738337) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (738368) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (738399) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (738429) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (738460) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (738490) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

四、对没有分区的表添加分区,缓解表数据压力,注意分区表字段需要参与建立联合唯一索引。

已有表添加分区

alter table `table` partition by range(to_days(start_tm))(
 PARTITION p201904 VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION p201905 VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION p201906 VALUES LESS THAN (737576) ENGINE = InnoDB,
 PARTITION p201907 VALUES LESS THAN (737606) ENGINE = InnoDB,
 PARTITION p201908 VALUES LESS THAN (737637) ENGINE = InnoDB,
 PARTITION p201909 VALUES LESS THAN (737668) ENGINE = InnoDB,
 PARTITION p201910 VALUES LESS THAN (737698) ENGINE = InnoDB,
 PARTITION p201911 VALUES LESS THAN (737729) ENGINE = InnoDB,
 PARTITION p201912 VALUES LESS THAN (737759) ENGINE = InnoDB,
 PARTITION p202001 VALUES LESS THAN (737790) ENGINE = InnoDB,
 PARTITION p202002 VALUES LESS THAN (737821) ENGINE = InnoDB,
 PARTITION p202003 VALUES LESS THAN (737850) ENGINE = InnoDB,
 PARTITION p202004 VALUES LESS THAN (737881) ENGINE = InnoDB,
 PARTITION p202005 VALUES LESS THAN (737911) ENGINE = InnoDB,
 PARTITION p202006 VALUES LESS THAN (737942) ENGINE = InnoDB,
 PARTITION p202007 VALUES LESS THAN (737972) ENGINE = InnoDB,
 PARTITION p202008 VALUES LESS THAN (738003) ENGINE = InnoDB,
 PARTITION p202009 VALUES LESS THAN (738034) ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN (738064) ENGINE = InnoDB,
 PARTITION p202011 VALUES LESS THAN (738095) ENGINE = InnoDB,
 PARTITION p202012 VALUES LESS THAN (738125) ENGINE = InnoDB,
 PARTITION p202101 VALUES LESS THAN (738156) ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN (738187) ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN (738215) ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN (738246) ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN (738276) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN (738307) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN (738337) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (738368) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (738399) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (738429) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (738460) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (738490) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

五、查询分区,各区表数据量。

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'table';

 

发布了27 篇原创文章 · 获赞 61 · 访问量 7万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览