mysql 分区表 range

最新推荐文章于 2024-06-24 14:20:32 发布

我家小宝_朱朱

最新推荐文章于 2024-06-24 14:20:32 发布

阅读量841

点赞数

分类专栏： mysql

原文链接：https://my.oschina.net/u/4327542/blog/3373310

版权

mysql 专栏收录该内容

50 篇文章 2 订阅

订阅专栏

创建分区

MYSQL的分区字段，必须包含在主键字段内

1. 建立RANGE分区: 按照时间(天) 存放数据, -- 整数类型

CREATE TABLE zcy.zcy_test (
	user_code varchar(20)  NOT NULL, -- 工号
	p_date INT UNSIGNED  NOT NULL, --分区
	update_time TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
	
	CONSTRAINT zcy_test_pk PRIMARY KEY (p_date,user_code) --分区列必须在主键中
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_bin
PARTITION BY RANGE (p_date) (
    PARTITION p20210201 VALUES LESS THAN ( 20210202 ),
    PARTITION p20210202 VALUES LESS THAN ( 20210203 ),
    PARTITION p20210203 VALUES LESS THAN ( 20210204 ),
    PARTITION p20210204 VALUES LESS THAN ( 20210205 ),
    PARTITION pmax VALUES LESS THAN maxvalue
    )
;

PARTITION pmax VALUES LESS THAN maxvalue ,不加的话,不在范围内插入会报错
但是有个坑, 要是想新增分区大于20210204 ,就会报错,提示已经有max 了. 所以只能删除pmax后,才能加

p_date=20210201 插入 p20210201 ,依次类推
range 的p_date 必须数字,字符串或者时间会报错. 所以需要转换数字

2. 建立RANGE分区: 按照时间(天) 存放数据, -- DATETIME类型

TO_DAYS(now()),TO_DAYS('2021-02-19') 到天的分区
还有year(now()), year('2021-02-19') 可以到年的分区

CREATE TABLE zcy.zcy_test_day (
	user_code varchar(20)  NOT NULL,
	p_date DATETIME  NOT NULL,
	update_time TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP,
	
	CONSTRAINT zcy_test_month_pk PRIMARY KEY (p_date,user_code)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_bin
PARTITION BY RANGE (TO_DAYS(p_date)) (
    PARTITION p20210201 VALUES LESS THAN ( TO_DAYS('20210202') ),
    PARTITION p20210202 VALUES LESS THAN ( TO_DAYS('20210203')  ),
    PARTITION p20210203 VALUES LESS THAN ( TO_DAYS('20210204') ),
    PARTITION p20210204 VALUES LESS THAN (TO_DAYS('20210205') ),
    PARTITION pmax VALUES LESS THAN maxvalue
    )
;

3. 建立RANGE分区: 按照时间(天) 存放数据-TIMESTAMP类型

还可以unix_timestamp(now()),unix_timestamp('2021-02-19') 到天的分区

CREATE TABLE zcy.zcy_test_day_new (
	user_code varchar(20)  NOT NULL,
	p_date TIMESTAMP    NOT NULL,
	update_time TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP
	,
	CONSTRAINT zcy_test_day_new_pk PRIMARY KEY (p_date,user_code)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_bin
PARTITION BY RANGE (UNIX_TIMESTAMP(p_date)) (
    PARTITION p20210201 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-02-02') ),
    PARTITION p20210202 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-02-03')  ),
    PARTITION p20210203 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-02-04') ),
    PARTITION p20210204 VALUES LESS THAN (UNIX_TIMESTAMP('2021-02-05') ),
    PARTITION pmax VALUES LESS THAN maxvalue
    )
;

3. 基于RANGE COLUMNS的分区方案,而无需像上述RANGE那种，分区的对象只能为整数。-- 字符串类型

CREATE TABLE zcy.zcy_test_day_new (
	user_code varchar(20)  NOT NULL,
	p_date varchar(20)    NOT NULL,
	update_time TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP
	,
	CONSTRAINT zcy_test_day_new_pk PRIMARY KEY (p_date,user_code)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS (p_date) (
    PARTITION p20210201 VALUES LESS THAN ('2021-02-02'),
    PARTITION p20210202 VALUES LESS THAN ('2021-02-03' ),
    PARTITION p20210203 VALUES LESS THAN ( '2021-02-04' ),
    PARTITION p20210204 VALUES LESS THAN ('2021-02-05' ),
    PARTITION pmax VALUES LESS THAN maxvalue
    )
;

插入数据后,查询指定表中的分区数据情况

SELECT table_name,partition_name,partition_description,table_rows FROM
information_schema.`PARTITIONS`  WHERE table_name = 'zcy_test_day_new';

增加分区

注意: 之前新建的分区,不能有PARTITION pmax VALUES LESS THAN maxvalue. 否在新增不上

ALTER TABLE zcy_test_day ADD PARTITION (PARTITION p20210205 VALUES LESS THAN(TO_DAYS('20210206')))

ALTER TABLE zcy.zcy_test_day_new ADD partition  (PARTITION p20210205 VALUES LESS THAN('2021-02-06' ))

删除分区, 数据也会删除的

ALTER TABLE zcy.zcy_test_day_new drop partition pmax
ALTER TABLE zcy.zcy_test_day_new drop partition p2010205

分区不会自动建,存储过程+触发器

DELIMITER $$
#该表所在数据库名称
USE `demo`$$
DROP  PROCEDURE  IF EXISTS `create_partition_by_day`$$
CREATE  PROCEDURE  `create_partition_by_day`(IN_SCHEMANAME  VARCHAR (64), IN_TABLENAME  VARCHAR (64))
BEGIN
     #当前日期存在的分区的个数
     DECLARE  ROWS_CNT  INT  UNSIGNED;
     #目前日期，为当前日期的后一天
     DECLARE  TARGET_DATE  TIMESTAMP ;
     #分区的名称，格式为p20180620
     DECLARE  PARTITIONNAME  VARCHAR (9);
     #当前分区名称的分区值上限，即为 PARTITIONNAME + 1
     DECLARE  PARTITION_ADD_DAY  VARCHAR (9);
     SET  TARGET_DATE = NOW() + INTERVAL 1  DAY ;
     SET  PARTITIONNAME = DATE_FORMAT( TARGET_DATE,  'p%Y%m%d'  );
     SET  TARGET_DATE = TARGET_DATE + INTERVAL 1  DAY ;
     SET  PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE,  '%Y%m%d'  );
     SELECT  COUNT (*)  INTO  ROWS_CNT  FROM  information_schema.partitions
     WHERE  table_schema = IN_SCHEMANAME  AND  table_name = IN_TABLENAME  AND  partition_name = PARTITIONNAME;
     IF ROWS_CNT = 0  THEN
         SET  @SQL = CONCAT(  'ALTER TABLE `' , IN_SCHEMANAME,  '`.`' , IN_TABLENAME,  '`' ,
         ' ADD PARTITION (PARTITION ' , PARTITIONNAME,  " VALUES LESS THAN (" ,
             PARTITION_ADD_DAY , ") ENGINE = InnoDB);"  );
         PREPARE  STMT  FROM  @SQL;
         EXECUTE  STMT;
         DEALLOCATE  PREPARE  STMT;
      ELSE
        SELECT  CONCAT( "partition `" , PARTITIONNAME,  "` for table `" ,IN_SCHEMANAME,  "." , IN_TABLENAME,  "` already exists" )  AS  result;
      END  IF;
END $$
DELIMITER ;

CREATE EVENT `job_commodity_status`
  ON SCHEDULE EVERY 1 DAY    --多久执行一次[HOUR|MONTH|WEEK|DAY|MINUTE|...]
    STARTS '2017-03-21 22:10:00'    --从什么时候开始执行
  ON COMPLETION NOT PRESERVE    --结束时间到了，job会被删除
  ENABLE    --更改计划任务状态
  DO    --修改计划执行体
CALL `ecommerce_job_commodity_status`();    --被执行的存储过程



创建事件
CREATE EVENT 的语法如下：
CREATE EVENT
[IF NOT EXISTS] ---------------------------------------------*标注1
event_name -----------------------------------------------------*标注2

ON SCHEDULE schedule ------------------------------------*标注3 
[ON COMPLETION [NOT] PRESERVE] -----------------*标注4
[ENABLE | DISABLE] ----------------------------------------*标注5 
[COMMENT 'comment'] --------------------------------------*标注6 
DO sql_statement -----------------------------------------------*标注7

标注4： [ON COMPLETION [NOT] PRESERVE]
ON COMPLETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。

标注5：[ENABLE | DISABLE]
参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。

可以用如下命令关闭或开启事件：

ALTER EVENT event_name  ENABLE/DISABLE

归档分区历史数据

我们可能有另一种需求对数据进行归档

Mysql版本>=5.7，归档分区历史数据非常方便，提供了一个交换分区的方法

分区数据归档迁移条件：

MySQL>=5.7
结构相同
归档到的数据表一定要是非分区表
非临时表；不能有外键约束
归档引擎要是：archive

建表并交换分区

CREATE TABLE `arch_customer_login_log` (
  `customer_id` INT unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` DATETIME NOT NULL COMMENT '用户登录时间',
  `login_ip` INT unsigned NOT NULL COMMENT '登录IP',
  `login_type` TINYINT NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB ;

ALTER TABLE customer_login_log 
	exchange  PARTITION p1 WITH TABLE arch_customer_login_log;

可以发现，原customer_login_log表中的2017年的数据（p1分区中的数据）已转移到了arch_customer_login_log表中，但是p1分区未删除，只是数据转移了,所以我们还需要执行DROP命令删除分区，以免有数据插入其中

将归档数据的存储引擎改为归档引擎
最后我们将归档数据的存储引擎改为归档引擎，命令为

ALTER TABLE customer_login_log  ENGINE=ARCHIVE;

使用归档引擎的好处是：它比Innodb所占用的空间更少，但是归档引擎只能进行查询操作，不能进行写操作