Mysql自动分区

Mysql自动分区

---

​ 前提：
​ test为数据库名
​ ip_demo为表名

1. 创建分区

   ALTER TABLE ip_demo PARTITION by RANGE(TO_DAYS(date))
   (
   PARTITION p20180716 VALUES LESS THAN (TO_DAYS('2018-07-17'))
   )

   注意：这里date必须为主键，否则创建失败。但是在表中不设置主键的时候，同样可以新建分区成功

2. 查看当前表的分区信息

   SELECT PARTITION_NAME,SUBPARTITION_NAME,TABLE_ROWS
   FROM information_schema.`PARTITIONS`
   WHERE TABLE_NAME = 'ip_demo';

3. 新建分区存储过程

   DELIMITER $$
   
   /*test为数据库名*/
   USE `test`$$
      /* 删除存储过程，确保新建成功*/
      DROP PROCEDURE IF EXISTS `create_Partition_ip`$$
   
   CREATE DEFINER=`root`@`localhost` PROCEDURE `create_Partition_ip`()
   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='ip_demo' ORDER BY partition_ordinal_position DESC LIMIT 1;
      SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
      SET @Max_date_add= DATE(DATE_ADD(@P12_Name+0, INTERVAL 2 DAY))+0;
   /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
      SET @s1=CONCAT('ALTER TABLE ip_demo ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date_add),''')))');
      /* 
      ALTER TABLE ip_demo ADD PARTITION (PARTITION p20181230 VALUES LESS THAN (TO_DAYS ('2018-12-31')))
      */
      /* 输出查看增加分区语句*/
      SELECT @s1;
      PREPARE stmt2 FROM @s1;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
   /* 取出最小的分区的名称，并删除掉 。
      注意：删除分区会同时删除分区内的数据，慎重 */
      /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS 
      where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1;
      SET @s=concat('ALTER TABLE tb_3a_huandan_detail DROP PARTITION ',@P0_Name);
      PREPARE stmt1 FROM @s; 
      EXECUTE stmt1; 
      DEALLOCATE PREPARE stmt1; */
   /* 提交 */
      COMMIT ;
   END$$
   
   DELIMITER ;

4. 查看所有的存储过程

   show procedure status;

5. 查看定时任务支持

   show VARIABLES LIKE 'event_scheduler'
   /*
   若结果为
   event_scheduler  ON
   代表定时任务开启
   若为OFF则需要手动开启
   SET GLOBAL event_scheduler = ON;
   最好通过my.cnf配置文件进行修改
   mysqld标签下插入
   event_scheduler = ON
   再重启服务器
   */

6. 定时执行

   DELIMITER ||  
   CREATE EVENT Partition_3Ahuadan_event  
            ON SCHEDULE  
            EVERY 1 SECOND STARTS '2018-07-16 11:19:59'   
            DO  
        BEGIN  
   
            CALL test.`create_Partition_ip`;  
   
   END ||  
   DELIMITER ;

7. 查看所有event

   show events
   SELECT * FROM mysql.event;
   
   SELECT * FROM information_schema.events;

8. 删除event

   DROP EVENT if EXISTS Partition_3Ahuadan_event(event名称)

9. 查看具体的查询使用的分区和行数

   explain partitions 
   select * from ip_demo
   where date between '2018-07-16' and '2018-07-19';

10. 删除分区操作

    alter table ip_demo drop partition p2018-07-16;

11. 参考文章

    [MySQL每天自动增加分区]: https://www.cnblogs.com/lanceblog/p/5532068.html
    [mysql 定时自动新增分区]: https://blog.csdn.net/u012922706/article/details/67633201