MySQL和ORACLE每天自动增加分区

有一个表tb_3a_huandan_detail，每天有300W左右的数据。查询太慢了，网上了解了一下，可以做表分区。由于数据较大，所以决定做定时任务每天执行存过自动进行分区。

1、在进行自动增加分区前一定得先对表手动分几个区

1 ALTER TABLE tb_3a_huandan_detail PARTITION BY RANGE (TO_DAYS(ServiceStartTime))
2 (
3     PARTITION p20160523 VALUES LESS THAN (TO_DAYS('2016-05-23')),
4     PARTITION p20160524 VALUES LESS THAN (TO_DAYS('2016-05-24')),
5     PARTITION p20160525 VALUES LESS THAN (TO_DAYS('2016-05-25')),
6     PARTITION p20160526 VALUES LESS THAN (TO_DAYS('2016-05-26')),
7     PARTITION p20160527 VALUES LESS THAN (TO_DAYS('2016-05-27'))
8 )

2、分区存过如下：

 1 DELIMITER $$
 2 
 3 USE `nres`$$
 4 
 5 DROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$
 6 
 7 CREATE DEFINER=`nres`@`%` PROCEDURE `create_Partition_3Ahuadan`()
 8 BEGIN
 9 /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
10     DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
11     START TRANSACTION;
12  
13 /* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
14     SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS 
15     WHERE table_name='tb_3a_huandan_detail' ORDER BY partition_ordinal_position DESC LIMIT 1;
16      SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
17 /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
18     SET @s1=CONCAT('ALTER TABLE tb_3a_huandan_detail ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
19     /* 输出查看增加分区语句*/
20     SELECT @s1;
21     PREPARE stmt2 FROM @s1;
22     EXECUTE stmt2;
23     DEALLOCATE PREPARE stmt2;
24 /* 取出最小的分区的名称，并删除掉 。
25     注意：删除分区会同时删除分区内的数据，慎重 */
26     /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS 
27     where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1;
28     SET @s=concat('ALTER TABLE tb_3a_huandan_detail DROP PARTITION ',@P0_Name);
29     PREPARE stmt1 FROM @s; 
30     EXECUTE stmt1; 
31     DEALLOCATE PREPARE stmt1; */
32 /* 提交 */
33     COMMIT ;
34  END$$
35 
36 DELIMITER ;

3、增加定时事件

 1 DELIMITER ||  
 2 CREATE EVENT Partition_3Ahuadan_event  
 3           ON SCHEDULE  
 4           EVERY 1 day STARTS '2016-05-27 23:59:59'   
 5           DO  
 6       BEGIN  
 7 
 8           CALL nres.`create_Partition_3Ahuadan`;  
 9         
10  END ||  
11 DELIMITER ;  

===================================================================================================
按照上面的过程，自己写的一个每日自动分区SP，过程如下：

（1）创建分区表：


（2）自动分区存储过程：

（3）创建定时事件

 DELIMITER ||  
 CREATE EVENT Partition_auto_add_event  
            ON SCHEDULE  
            EVERY 1 day STARTS '2017-02-19 23:59:59'   
            DO  
        BEGIN  
  
            CALL test.`create_partition_auto_add`;  
          
  END ||  
 DELIMITER ; 

====================================================================================================
查看分区表的SQL：可以直接用 select * from table_name partition p1;

下面是介绍一下ORACLE 11g 的interval 自动创建子分区
在11g里的Interval创建，这种方法对没有写全的分区会自动创建。 比如我这里只写了1月日期，如果插入的数据有其他月份的，会自动生成对应的分区。

 

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */

CREATE TABLE intervaldave

PARTITION BY RANGE (time_fee)

   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

   (PARTITION part1

       VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))

AS

   SELECT   ID, TIME_FEE FROM DAVE;

 

 

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';

 

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

INTERVALDAVE                   PART1

INTERVALDAVE                   SYS_P24

INTERVALDAVE                   SYS_P25

INTERVALDAVE                   SYS_P26

INTERVALDAVE                   SYS_P33

INTERVALDAVE                   SYS_P27

INTERVALDAVE                   SYS_P28

下面再补充一下两个函数：

numtodsinterval(<x>,<c>),x是一个数字,c是一个字符串,

表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 ('day','hour','minute','second')
example
SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
SYSDATE             RES
------------------- -------------------
2007-09-05 01:45:34 2007-09-05 04:45:34

numtoyminterval与numtodsinterval函数类似,将x转为interval year to month数据类型

常用的单位有'year','month'

example

SQL> select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;

SYSDATE             RES
------------------- -------------------
2007-09-05 01:54:53 2010-09-05 01:54:53