mysql-定時對錶分區

標籤：技術分享   tran   end   nal   tail   存在   locate   例子   ike   

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；”。通過“並行”，這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。
• 通過跨多個磁碟來分散資料查詢，來獲得更大的查詢輸送量。

 

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=utf8PARTITION 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 `nres`$$DROP PROCEDURE IF EXISTS `create_Partition_jmx_status`$$CREATE DEFINER=`iris`@`%` PROCEDURE `create_Partition_jmx_status`()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=‘jmx_sta‘ ORDER BY partition_ordinal_position DESC LIMIT 1;     SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;/* 修改表，在最大分區的後面增加一個分區，時間範圍加1天 */    SET @s1=CONCAT(‘ALTER TABLE jmx_sta ADD PARTITION (PARTITION p‘,@Max_date,‘ VALUES LESS THAN (TO_DAYS (‘‘‘,DATE(@Max_date),‘‘‘)))‘);    /* 輸出查看增加分區語句*/    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 ;

3, 添加定時事件

DELIMITER ||CREATE EVENT Partition_jmx_sta_perday     ON SCHEDULE     EVERY 1 day STARTS ‘2018-01-02 23:59:50‘     DO BEGIN    CALL iris.`create_Partition_jmx_status`; END || DELIMITER ;

 

 

 

mysql-定時對錶分區