Mysql表按天分区

最近做了个批处理，每天有30万条数据增加。数据量日益增大，查询效率会随之逐渐下降。因此考虑按天对表进行分区，保证查询效率。

1.表分区语法

    为当前存在的表添加表分区(table_name：表名，timestap：date类型)

ALTER TABLE table_name PARTITION BY RANGE (TO_DAYS(timestap))
(
     PARTITION p20200226 VALUES LESS THAN (TO_DAYS('2020-02-27')),
     PARTITION p20200227 VALUES LESS THAN (TO_DAYS('2020-02-28')),
     PARTITION p20200228 VALUES LESS THAN (TO_DAYS('2020-02-29')),
     PARTITION p20200229 VALUES LESS THAN (TO_DAYS('2020-03-01')),
     PARTITION p20200301 VALUES LESS THAN (TO_DAYS('2020-03-02')),
     PARTITION p20200302 VALUES LESS THAN (TO_DAYS('2020-03-03')),
     PARTITION p20200303 VALUES LESS THAN (TO_DAYS('2020-03-04'))
 )

 

查看表分区(table_name:表名)

SELECT 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
FROM information_schema.partitions  WHERE 
  table_schema = SCHEMA()  
  AND table_name='table_name';  

删除分区:

alter table table_name drop partition p1;

不可以删除hash或者key分区。

一次性删除多个分区

alter table table_name drop partition p1,p2;

 

增加分区:

alter table table_name add partition (PARTITION p20200303 VALUES LESS THAN (TO_DAYS('2020-03-04')));

2.创建存储过程增加分区

DELIMITER $$

DROP PROCEDURE IF EXISTS `create_Partition_monitor_ecs`$$

CREATE PROCEDURE `create_Partition_monitor_ecs`()
BEGIN
/* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
 
/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE table_name='monitor_system_ecs' ORDER BY partition_ordinal_position DESC LIMIT 1;
     SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 2 DAY))+0;
     SET @Current_date = DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    SET @s1=CONCAT('ALTER TABLE monitor_system_ecs ADD PARTITION (PARTITION p',@Current_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
    /* 输出查看增加分区语句*/
    SELECT @s1;
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
/* 提交 */
    COMMIT ;
 END$$

DELIMITER ;

3.创建定时任务，每天执行上一步中的存储过程

创建定时任务的前提是先检查mysql有没有开启定时任务：

SHOW VARIABLES LIKE 'event_scheduler';
查询结果是ON则已经开启，OFF则未开启。

如果是OFF，修改my.conf:在[mysqld]下添加一行：event_scheduler=1

然后重启mysql

DELIMITER ||  
CREATE EVENT Partition_monitor_ecs_event  
          ON SCHEDULE  
          EVERY 1 DAY STARTS '2020-03-03 10:17:00'   
          DO  
      BEGIN  

          CALL `create_Partition_monitor_ecs`;  
        
 END ||  
DELIMITER ;