mysql-定时对表分区

1, 分区

具体可见: http://blog.csdn.net/open_data/article/details/46893331

1, 分区类型: 

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

2), 为什么分区

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

分区的一些优点包括：

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。
• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
• 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
• 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
• 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

 3), 查看分区

SELECT
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions 
WHERE
  TABLE_SCHEMA = schema() 
  AND TABLE_NAME='table';

 或者

select partition_name, subpartition_name, table_rows
from information_schema.partitions
where table_name = 'jmx_sta';

 

2, 事件

具体可见: 

http://blog.csdn.net/jesseyoung/article/details/35257527

https://www.cnblogs.com/zoro-zero/p/6511203.html

2.1, 查看事件的开启状态

show variables like '%event_schedule%';

2.2, 将事件更改为开启状态

 set global event_scheduler = ON;

 2.3 查看调度事件

show processlist;

2.4 查看事件

show events;

2.5 删除事件

drop event if exists event_second;  

 

　　

添加定时分区任务

1), 创建表并初始化分区(必须有初始化分区)

DROP TABLE IF EXISTS `jmx_sta`;
CREATE TABLE `jmx_sta` (
    `host` varchar(20) DEFAULT NULL COMMENT '主机',
    `time` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
    `object` varchar(32) DEFAULT NULL COMMENT '对象',
    `attribute` varchar(32) DEFAULT NULL COMMENT '属性',
    `value` DOUBLE,
    PRIMARY KEY (`host`, `time`, `object`, `attribute`)
) ENGINE=InnoDB CHARSET=utf8
PARTITION BY RANGE (unix_timestamp(time)) (
    PARTITION p20171231 VALUES LESS THAN (unix_timestamp('2017-12-31 23:59:59')),
    PARTITION p20180101 VALUES LESS THAN (unix_timestamp('2018-01-01 23:59:59')),
    PARTITION p20180102 VALUES LESS THAN (unix_timestamp('2018-01-02 23:59:59'))
);

 

2), 添加存储过程

DELIMITER $$
USE `iris`$$　　#使用库
DROP PROCEDURE IF EXISTS `create_Partition_jmx_status`$$
CREATE DEFINER=`iris_admin`@`10.%` PROCEDURE `create_Partition_jmx_status`()　　# grant的授权
BEGIN
  #DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
  SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE table_name='jmx_status' ORDER BY partition_ordinal_position DESC LIMIT 1;
  SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
  SET @s1=CONCAT('ALTER TABLE jmx_status ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (unix_timestamp (''',DATE(@Max_date),' 23:59:59'')))');
  SELECT @s1;
  PREPARE stmt2 FROM @s1;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;
  COMMIT ;
END$$
DELIMITER ;

 

3, 添加定时事件

DELIMITER ||
CREATE EVENT Partition_jmx_status_perday
     ON SCHEDULE
     EVERY 1 day STARTS '2018-01-05 23:59:50'
     DO
 BEGIN
    CALL `create_Partition_jmx_status`;
 END ||
 DELIMITER ;

 

 添加定时删除分区任务: 

CREATE DEFINER=`iris_admin`@`10.%` PROCEDURE `delete_Partition_jmx_status`()
BEGIN
  #DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
  SELECT REPLACE(partition_name,'p','') INTO @P13_Name FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name='jmx_status' ORDER BY partition_ordinal_position DESC LIMIT 1;
  SET @Min_date= DATE(DATE_SUB(@P13_Name+0, INTERVAL 12 DAY))+0;
  SET @s2=CONCAT('ALTER TABLE jmx_status DROP PARTITION p',@Min_date);
  SELECT @s2;
  PREPARE stmt2 FROM @s2;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;
  COMMIT ;
END

2, 定时调用

在上一个的定时任务中, 在调用一次: 

BEGIN
    CALL `create_Partition_jmx_status`;
        CALL `delete_Partition_jmx_status`;
END