天天看點
傳回

mysql資料庫按天建立表分區

背景概述

分區表的好處就不多說了，我這裡需要的場景是要對資料庫中的某一個表做備份，以現在的時間為起點備份多少天以前的資料，并且删除該表中已經備份的資料。由于該表中的資料一般都是按天查詢，并且delete加了where條件之後并不是在資料庫中直接删除資料，而是将資料檔案的辨別位删除，沒有整理檔案，是以不會徹底釋放空間，時間久了就會存在大量的垃圾資料，是以就想到了用分區表。而按天分區就可以很好的解決以上的問題。

實作方案

建立分區表

建立分區表的時候跟普通建立表基本上一樣，隻需要在末尾加上如下語句：

PARTITION BY RANGE COLUMNS(CREATION_DATE)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB)

該句說的是按範圍分區，而CREATION_DATE就是分區的條件，這個字段必須是你建立的表中有的字段，并且必須是主鍵之一。後面括号中的内容是建立一個分區，命名為p0，并把所有CREATION_DATE值小于’2019-01-01’的資料插入到該分區，這裡可以直接建立多個分區，寫法如下：

(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN ('2019-01-02') ENGINE = InnoDB)

完整的建立表代碼：

CREATE TABLE `hmw_service_instance_backup`  (
  `INST_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `SERVICE_URI` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `STATUS` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG` bigint(20) NULL DEFAULT NULL,
  `RESPONSE_MSG` bigint(20) NULL DEFAULT NULL,
  `START_TIME` datetime(6) NULL DEFAULT NULL,
  `END_TIME` datetime(6) NULL DEFAULT NULL,
  `RESPONSE_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ERROR_MSG` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ERROR_STACK_MSG` bigint(20) NULL DEFAULT NULL,
  `CONVERSATION_ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CONSUMER` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PARENT_CON_ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
  `SERVICE_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_SVC_DESC` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_SVC_STATUS` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_SVC_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQ_NO` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ORG_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EMP_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `SYS_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `USER_TOKEN` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `TRAN_TIME` datetime(6) NULL DEFAULT NULL,
  `SB_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQ_PUT_TIME` datetime(6) NULL DEFAULT NULL,
  `RESP_TIME` datetime(6) NULL DEFAULT NULL,
  `REQUEST_PUT_MSG` bigint(20) NULL DEFAULT NULL,
  `RESPONSE_REPICK_MSG` bigint(20) NULL DEFAULT NULL,
  `INTERVAL_TIME` bigint(20) NULL DEFAULT NULL,
  `READ_FLAG` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG_KEY` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ESB_NODE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG_DETAIL` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `RESPONSE_MSG_DETAIL` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `ERROR_STACK_MSG_DETAIL` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `START_TIME3` datetime(6) NULL DEFAULT NULL,
  `START_TIME1` datetime(6) NULL DEFAULT NULL,
  `END_TIME3` datetime(6) NULL DEFAULT NULL,
  `END_TIME1` datetime(6) NULL DEFAULT NULL,
  `INTERVAL_TIME3` bigint(20) NULL DEFAULT NULL,
  `INTERVAL_TIME1` bigint(20) NULL DEFAULT NULL,
  `CREATED_BY` bigint(20) NULL DEFAULT -1,
  `CREATION_DATE` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `LAST_UPDATED_BY` bigint(20) NULL DEFAULT -1,
  `LAST_UPDATE_DATE` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  `OBJECT_VERSION_NUMBER` bigint(20) NULL DEFAULT 1,
  `BUS_REQUEST_END_TIME` datetime(6) NULL DEFAULT NULL,
  `BUS_RESPONSE_START_TIME` datetime(6) NULL DEFAULT NULL,
  `BUSINESS_CODE_ONE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_TWO` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_THREE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_FOUR` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_FIVE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG_SIZE` bigint(20) NULL DEFAULT NULL,
  `RESPONSE_MSG_SIZE` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`INST_ID`,`CREATION_DATE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N1`(`START_TIME`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N2`(`END_TIME`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N3`(`SB_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N4`(`SERVICE_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N5`(`SERVICE_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N6`(`SERVICE_URI`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N7`(`READ_FLAG`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N8`(`SB_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N9`(`READ_FLAG`, `SERVICE_URI`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N10`(`SERVICE_URI`, `SB_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N11`(`READ_FLAG`, `SB_CODE`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12325 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic PARTITION BY RANGE COLUMNS(CREATION_DATE)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB);

建立存儲過程 

DELIMITER $$
#該表所在資料庫名稱
USE `hmw`$$
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    #目前日期存在的分區的個數
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，為目前日期的後一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分區的名稱，格式為p20180620
    DECLARE PARTITIONNAME VARCHAR(9);
    #目前分區名稱的分區值上限，即為 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY datetime(0);
    SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
    SET PARTITION_ADD_DAY = DATE(NOW() + INTERVAL 2 DAY);
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (
            '",PARTITION_ADD_DAY,"') ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
       SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$
DELIMITER ;

該存儲過程是根據資料庫和表名來按天建立分區表，分區的明明是pyyyymmdd的格式。

建立event事件，按天執行存儲過程 

DELIMITER $$
#該表所在的資料庫名稱
USE `hmw`$$
CREATE EVENT IF NOT EXISTS `daily_generate_partition`
ON SCHEDULE EVERY 1 DAY   #執行周期，還有天、月等等
STARTS '2019-12-03 16:59:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #調用剛才建立的存儲過程，第一個參數是資料庫名稱，第二個參數是表名稱
    CALL create_partition_by_day('hmw','hmw_service_instance');
		CALL create_partition_by_day('hmw','hmw_service_instance_backup');
END$$
DELIMITER ;

常用指令

查詢表分區：

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS t where t.TABLE_NAME=‘hmw_service_instance’

查詢事件 ：

SHOW EVENTS

删除事件：

DROP EVENT IF EXISTS daily_generate_partition;

删除表分區：

alter table hmw_service_instance_backup drop partition p20191205;

添加表分區：

alter table emp add partition (partition p3 values less than (4000));

事件不執行

原因出在沒有開啟event_scheduler！！！

1、檢視event_scheduler狀态

SHOW VARIABLES LIKE 'event_scheduler'

2、使用指令開啟（臨時開啟，重新開機mysql又還原回去了）

set global event_scheduler = on;

3、修改配置（永久修改）

配置檔案的[mysqld]部分加上event_scheduler=ON即可，一般是my.cnf檔案。

mysql資料庫按天建立表分區
mysql 表分區
上一篇: 石子遊戲 II
下一篇: 詞法分析（四）：DFA的化簡

繼續閱讀