全部產品
Search
文件中心

PolarDB:使用說明

更新時間:Nov 09, 2024

自動歸檔冷資料功能Data Lifecycle Management(簡稱DLM)支援將低頻使用的冷資料定期自動地從PolarStore轉存到低成本的OSS儲存介質上,以達到降本增效的效果。

前提條件

  • 叢集版本需滿足以下條件之一:

    • PolarDB MySQL版8.0.1版本且小版本為8.0.1.1.32或以上。

    • PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.9或以上。

    您可以通過查詢版本號碼,來確認叢集版本。

    說明

    叢集版本為PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.11.1或以上時,使用DLM功能不記錄Binlog日誌。

  • 使用DLM策略需要先開啟冷資料歸檔

    說明

    如果您未開啟冷資料歸檔功能,報錯結果如下:

    ERROR 8158 (HY000): [Data Lifecycle Management] DLM storage engine is not support. The value of polar_dlm_storage_mode is OFF.

使用限制

  • DLM功能目前僅支援分區表,分區表不能包含二級分區。分區方式需要為RANGE COLUMN類型。

  • 暫不支援在建立全域二級索引(GSI)的分區表上使用DLM功能。

  • PolarDB MySQL版不支援修改DLM策略,您可以先刪除原有策略,再建立策略。

  • 當前表上存在DLM策略時,如果執行某些DDL操作,使歸檔表和原表的表結構不一致(例如,加減列、修改列類型等),會導致後續歸檔的資料無法被解析。因此在執行此類DDL操作時,需要先刪除當前表上的DLM策略。後續如需使用資料自動歸檔功能,可以重新建立DLM策略,並指定歸檔表名為新的表名,新表名與之前歸檔的表名不能重複。

  • 建議使用INTERVAL RANGE分區功能自動拓展分區,同時使用DLM功能將低頻使用的分區的資料歸檔到OSS上。

    說明

    當前僅PolarDB MySQL版8.0.2版本且小版本為8.0.2.2.0及以上的叢集支援INTERVAL RANGE分區。

  • DLM功能需要在CREATE TABLE或者ALTER TABLE時指定。

  • 目前DLM策略在SHOW CREATE TABLE時不會展示。您可以從mysql.dlm_policies表中查看所有表上的DLM策略資訊。

注意事項

  • 冷資料歸檔完成後,OSS上的歸檔表唯讀,且查詢效能較差。您需要提前測試資料歸檔後能否滿足您的查詢效能要求。

  • 將分區表中的分區歸檔至OSS後,歸檔至OSS上的分區中的資料唯讀,且不支援對該分區表執行DDL操作。

  • 執行備份操作時,不會備份已轉存至OSS上的資料,且OSS上的資料不支援按時間點恢複。

文法說明

建立DLM策略

  • 在CREATE TABLE時建立DLM策略

    CREATE TABLE [IF NOT EXISTS] tbl_name
        (create_definition,...)
        [table_options]
        [partition_options]
        [dlm_add_options]
    
    dlm_add_options:
        DLM ADD
            [(dlm_policy_definition [, dlm_policy_definition] ...)]
    
    dlm_policy_definition:
        POLICY policy_name
        [TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
        [ENGINE [=] engine_name]
        [STORAGE SCHEMA_NAME [=] storage_schema_name]
        [STORAGE TABLE_NAME [=] storage_table_name]
        [STORAGE [=] OSS]
        [READ ONLY]
        [COMMENT 'comment_string']
        [EXTRA_INFO 'extra_info']
        ON [(PARTITIONS OVER num)]           
  • 在ALTER TABLE時建立DLM策略

    ALTER TABLE tbl_name
        [alter_option [, alter_option] ...]
        [partition_options]
        [dlm_add_options]
    
    dlm_add_options:
        DLM ADD
            [(dlm_policy_definition [, dlm_policy_definition] ...)]
    
    dlm_policy_definition:
        POLICY policy_name
        [TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
        [ENGINE [=] engine_name]
        [STORAGE SCHEMA_NAME [=] storage_schema_name]
        [STORAGE TABLE_NAME [=] storage_table_name]
        [STORAGE [=] OSS]
        [READ ONLY]
        [COMMENT 'comment_string']
        [EXTRA_INFO 'extra_info']
        ON [(PARTITIONS OVER num)]      

DLM策略參數說明

參數

是否必選

說明

tbl_name

表名稱。

policy_name

策略名稱稱。

TIER TO TABLE

歸檔至表。

TIER TO PARTITION

將分區歸檔至OSS。

說明
  • 該功能目前處於灰階階段。如需使用,請前往配額中心,根據配額ID polardb_mysql_hybrid_partition找到配額名稱,在對應的操作列單擊申請來開通該功能。

  • 僅支援將PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.17及以上的叢集中的分區表的分區歸檔至OSS。

  • 使用該功能時,需要確保分區表中總的分區數量不超過8192個。

TIER TO NONE

直接刪除需要歸檔的資料。

engine_name

歸檔資料存放的引擎,目前僅支援將資料歸檔到CSV引擎中。

storage_schema_name

歸檔為表時,表所在的資料庫。預設為當前表所在資料庫。

storage_table_name

歸檔為表時,表的名稱,您可以指定表名稱。預設為當前表名_當前DLM策略名稱

STORAGE [=] OSS

歸檔後的資料存放區在OSS引擎上(預設)。

READ ONLY

歸檔後的資料唯讀(預設)。

comment_string

DLM策略的備忘。

extra_info

指目標OSS表上的OSS_FILE_FILTER資訊。

說明
  • 僅支援將PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.25及以上的企業版叢集中的分區表的分區歸檔至OSS。

  • 該功能僅在目標表不存在時生效。在這種情況下,系統會根據EXTRA_INFO中的OSS_FILE_FILTER參數,在建立目標OSS表時,將自動產生FILE_FILTER屬性,並在歸檔時自動產生Filter資料。如果目標表已存在,則以現有的File Filter為準。

EXTRA_INFO的格式為{"oss_file_filter":"field_filter[,field_filter]"},其中field_filter的格式為:

field_filter := field_name[:filter_type]
filter_type := bloom

ON(PARTITIONS OVER num)

分區數量大於num時,進行資料歸檔。

變更DLM策略

  • 使DLM策略生效。

    ALTER TABLE table_name DLM ENABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]
  • 使DLM策略失效。

    ALTER TABLE table_name DLM DISABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]
  • 刪除DLM策略。

    ALTER TABLE table_name DLM DROP POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

其中,table_name是當前表名稱,dlm_policy_name是當前需要修改的策略名稱稱,策略名稱稱可以配置多個。

執行DLM策略

  • 執行當前叢集上所有表上的DLM策略。

    CALL dbms_dlm.execute_all_dlm_policies();
  • 執行單張表上的DLM策略。

    CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');

    其中,database_name是當前表所在的資料庫名稱,table_name是當前表的表名稱。

您可以通過mysql event功能,在您的叢集營運期間執行DLM策略,既能避免在業務高峰期執行DLM而影響資料庫效能,也可以定期轉移到期資料,減少您的資料庫儲存費用。通過EVENT執行DLM策略文法:

CREATE
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [COMMENT 'comment']
    DO event_body;

schedule: {
  EVERY interval
  [STARTS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

event_body: {
      CALL dbms_dlm.execute_all_dlm_policies();
    | CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
}

參數說明如下:

參數

是否必選

說明

event_name

當前EVENT的名稱。

schedule

當前EVENT的執行時間和周期。

comment

當前EVENT的備忘。

event_body

當前EVENT具體執行的內容。需要設定為通過EVENT執行DLM策略的語句。

說明
  • 使用CALL dbms_dlm.execute_all_dlm_policies()時,當前EVENT會執行叢集上的所有DLM策略,所以一個叢集需要建立一個EVENT。

  • 使用CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');時,當前EVENT僅會執行某個表上的所有DLM策略,所以每一張帶有DLM策略的表,都需要建立對應的EVENT,讓該表在指定的時間進行資料歸檔。

interval

EVENT的執行循環。

timestamp

開始執行EVENT的時間。

database_name

資料庫名稱。

table_name

表名稱。

MySQL EVENT功能說明請參見MySQL EVENT官方文檔

使用樣本請參見冷資料歸檔到OSS樣本

樣本

將分區表中的資料歸檔至OSS外表

  1. 建立DLM策略

    以下樣本將建立sales分區表,該表以order_time列作為分區鍵,按時間間隔劃分分區。同時,該表存在INTERVAL和DLM兩種策略:

    • INTERVAL策略:當插入的資料超過分區範圍時,將自動建立新的分區,時間間隔為1年。

    • DLM策略:定義當前表僅有3個分區,當分區數量大於3,執行DLM策略時:

      • 如果不存在OSS外表sales_history,則建立新的OSS外表sales_history,並將冷資料轉存到sales_history外表中。

      • 如果存在sales_history外表,且sales_history表在內建的OSS 空間上,則直接將冷資料轉存到sales_history外表中。

    說明

    建立INTERVAL RANGE分區表需要滿足建立條件。INTERVAL使用請參見PolarDB INTERVAL

    1. 建立帶有DLM策略的sales表。

      CREATE TABLE `sales` (
        `id` int DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime NOT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
      (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
      DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
      STORAGE TABLE_NAME = 'sales_history' EXTRA_INFO '{"oss_file_filter":"id,name:bloom"}' ON (PARTITIONS OVER 3);

      該表的DLM策略名稱稱為test_policy,當分區數量大於3時,會將當前表的冷資料轉存為CSV格式,並儲存在OSS上。轉存後的表名稱為sales_history,資料唯讀。同時,如果歸檔表OSS不存在,會自動建立OSS表,並在idname列上建立OSS_FILE_FILTER。

    2. 當前表的DLM策略會儲存在系統資料表mysql.dlm_policies中,您可以通過該表查看DLM策略的詳細資料。表mysql.dlm_policies詳情請參見表結構說明。查看mysql.dlm_policies表結構資訊。

      mysql> SELECT * FROM mysql.dlm_policies\G

      執行結果如下:

      *************************** 1. row ***************************
                         Id: 3
               Table_schema: gg
                 Table_name: sales
                Policy_name: test_policy
                Policy_type: TABLE
               Archive_type: PARTITION COUNT
               Storage_mode: READ ONLY
             Storage_engine: CSV
              Storage_media: OSS
        Storage_schema_name: gg
         Storage_table_name: sales_history
            Data_compressed: OFF
       Compressed_algorithm: NULL
                    Enabled: ENABLED
            Priority_number: 10300
      Tier_partition_number: 3
             Tier_condition: NULL
                 Extra_info: {"oss_file_filter": "id,name:bloom,order_time"}
                    Comment: NULL
      1 row in set (0.03 sec)      

      目前sales表的分區數量為3,不會進行資料歸檔。

    3. sales分區表中插入3000的測試資料,保證資料超過當前表的分區定義,觸發INTERVAL自動建立新的分區。

      DROP PROCEDURE IF EXISTS proc_batch_insert;
      delimiter $$
      CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
      BEGIN
      SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
      PREPARE stmt from @insert_stmt;
      WHILE begin <= end DO
      SET @ID1 = begin;
      SET @NAME = CONCAT(begin+begin*281313, '@stiven');
      SET @TIME = from_days(begin + 737600);
      EXECUTE stmt using @ID1, @NAME, @TIME;
      SET begin = begin + 1;
      END WHILE;
      END;
      $$
      delimiter ;
      CALL proc_batch_insert(1, 3000, 'sales');
    4. 此時,觸發了INTERVAL自動建立新的分區,導致sales表的分區增加,表結構變為:

      mysql> SHOW CREATE TABLE sales\G

      執行結果如下:

      *************************** 1. row ***************************       
            Table: sales
      Create Table: CREATE TABLE `sales` (  
        `id` int(11) DEFAULT NULL,  
        `name` varchar(20) DEFAULT NULL,  
        `order_time` datetime NOT NULL,  
         PRIMARY KEY (`order_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ 
      /*!99990 800020200 INTERVAL(YEAR, 1) */
      /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.03 sec)

      已經建立了新的分區,且分區數量大於3,滿足DLM策略的執行條件,可以進行資料歸檔。

  2. 執行DLM策略

    1. 您可以通過SQL語句直接執行DLM策略,或者通過MySQL的EVENT功能,定期執行DLM策略。假設從2022-10-11開始,您的叢集營運開始時間是每天淩晨1點,則每天淩晨1點開始執行DLM策略。建立對應的執行EVENT如下:

      CREATE EVENT dlm_system_base_event
             ON SCHEDULE EVERY 1 DAY
          STARTS '2022-10-11 01:00:00'
          do CALL 
      dbms_dlm.execute_all_dlm_policies();

      1點後,這個EVENT會執行所有表上的DLM策略。

    2. 執行以下命令,查看sales表結構資訊。

      mysql> SHOW CREATE TABLE sales\G

      執行結果如下:

      *************************** 1. row ***************************;
             Table: sales
      Create Table: CREATE TABLE `sales` (  
        `id` int(11) DEFAULT NULL,  
        `name` varchar(20) DEFAULT NULL,  
        `order_time` datetime NOT NULL,  
         PRIMARY KEY (`order_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ 
      /*!99990 800020200 INTERVAL(YEAR, 1) */
      /*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
      PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB, 
      PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.03 sec)

      當前表僅剩3個分區。

    3. 您可以通過mysql.dlm_progress表查看DLM策略的執行記錄,表dlm_progress定義請參見表結構說明。執行以下命令,查看mysql.dlm_progress 表結構資訊。

      mysql> SELECT * FROM mysql.dlm_progress\G

      執行結果如下:

      *************************** 1. row ***************************;
                        Id: 1
              Table_schema: test
                Table_name: sales
               Policy_name: test_policy
               Policy_type: TABLE
            Archive_option: PARTITIONS OVER 3
            Storage_engine: CSV
             Storage_media: OSS
           Data_compressed: OFF
      Compressed_algorithm: NULL
        Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
             Archive_stage: ARCHIVE_COMPLETE
        Archive_percentage: 0
        Archived_file_info: null
                Start_time: 2024-07-26 17:56:20
                  End_time: 2024-07-26 17:56:50
                Extra_info: null
      1 row in set (0.00 sec)

      存放低頻冷資料的分區,包括p20200101000000p20210101000000p20220101000000_p20230101000000_p20240101000000_p20250101000000都已轉存到OSS外表上。

    4. 執行以下命令,查看OSS外表的表結構。

      mysql> SHOW CREATE TABLE sales_history\G

      執行結果如下:

      *************************** 1. row ***************************;
             Table: sales_history
      Create Table: CREATE TABLE `sales_history` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime DEFAULT NULL,
         PRIMARY KEY (`order_time`)
      ) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */ /*!99990 800020224 OSS_FILE_FILTER='id,name:bloom,order_time' */
      1 row in set (0.15 sec)

      該表變為資料存放區在OSS引擎的CSV表,查詢方法與查詢本地表的方法相同。同時,指定的列已經加入到OSS_FILE_FILTER中,並且由於order_time是分區鍵,也會自動建立 OSS_FILE_FILTER。

    5. 分別查詢salessales_history兩張表上的資料。

      SELECT COUNT(*) FROM sales;
      +----------+
      | count(*) |
      +----------+
      |      984 |
      +----------+
      1 row in set (0.01 sec)
      
      SELECT COUNT(*) FROM sales_history;
      +----------+
      | count(*) |
      +----------+
      |     2016 |
      +----------+
      1 row in set (0.57 sec)           

      可以看到資料總和正好為3000,與開始時sales表插入的資料量一致。

    6. 在OSS外表上通過OSS_FILE_FILTER查詢(需要開啟OSS_FILE_FILTER開關):

      mysql> explain select * from sales_history where id = 9;
      +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
      | id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                       |
      +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
      |  1 | SIMPLE      | sales_history | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2016 |    10.00 | Using where; With pushed engine condition (`test`.`sales_history`.`id` = 9) |
      +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------+
      1 row in set, 1 warning (0.59 sec)
      
      mysql>  select * from sales_history where id = 9;
      +------+----------------+---------------------+
      | id   | name           | order_time          |
      +------+----------------+---------------------+
      |    9 | 2531826@stiven | 2019-07-04 00:00:00 |
      +------+----------------+---------------------+
      1 row in set (0.19 sec)

將分區表中的分區歸檔至OSS

  1. 建立DLM策略

    以下樣本將建立一張表名為sales的分區表,該表以order_time列作為分區鍵,按時間間隔劃分分區。同時,該表存在INTERVAL和DLM兩種策略:

    • INTERVAL策略:當插入的資料超過分區範圍時,將自動建立新的分區,時間間隔為1年。

    • DLM策略:定義當前表僅有3個分區,當分區數量大於3時,執行DLM策略會直接將之前的分區轉存至OSS。

    1. 建立sales表。

      CREATE TABLE `sales` (
        `id` int DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime NOT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
      (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
      DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=CSV STORAGE=OSS READ ONLY ON (PARTITIONS OVER 3);

      該表的DLM策略名稱稱為policy_part2part,當分區數量大於3時,會將舊的分區轉存至OSS。

    2. mysql.dlm_policies表中查看DLM策略。

      SELECT * FROM mysql.dlm_policies\G

      執行結果如下:

      *************************** 1. row ***************************
                         Id: 2
               Table_schema: test
                 Table_name: sales
                Policy_name: policy_part2part
                Policy_type: PARTITION
               Archive_type: PARTITION COUNT
               Storage_mode: READ ONLY
             Storage_engine: CSV
              Storage_media: OSS
        Storage_schema_name: NULL
         Storage_table_name: NULL
            Data_compressed: OFF
       Compressed_algorithm: NULL
                    Enabled: ENABLED
            Priority_number: 10300
      Tier_partition_number: 3
             Tier_condition: NULL
                 Extra_info: null
                    Comment: NULL
      1 row in set (0.03 sec)
    3. 使用proc_batch_insert預存程序向sales分區表中插入一定的測試資料,以觸發INTERVAL策略來自動建立新的分區。

      CALL proc_batch_insert(1, 3000, 'sales');

      執行結果如下,表示資料插入成功:

      Query OK, 1 row affected, 1 warning (0.99 sec)
    4. 執行以下命令,查看sales表的結構資訊。

      SHOW CREATE TABLE sales \G

      執行結果如下:

      *************************** 1. row ***************************
             Table: sales
      Create Table: CREATE TABLE `sales` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime DEFAULT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
      /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.03 sec)
  2. 執行DLM策略

    1. 通過以下命令來執行DLM策略。

      CALL dbms_dlm.execute_all_dlm_policies();
    2. 通過mysql.dlm_progress表查看DLM執行記錄。

      SELECT * FROM mysql.dlm_progress \G

      執行結果如下:

      *************************** 1. row ***************************
                        Id: 4
              Table_schema: test
                Table_name: sales
               Policy_name: policy_part2part
               Policy_type: PARTITION
            Archive_option: PARTITIONS OVER 3
            Storage_engine: CSV
             Storage_media: OSS
           Data_compressed: OFF
      Compressed_algorithm: NULL
        Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
             Archive_stage: ARCHIVE_COMPLETE
        Archive_percentage: 100
        Archived_file_info: null
                Start_time: 2023-09-11 18:04:39
                  End_time: 2023-09-11 18:04:40
                Extra_info: null
      1 row in set (0.02 sec)
    3. 執行以下命令,查看sales表結構資訊。

      SHOW CREATE TABLE sales \G

      執行結果如下:

      *************************** 1. row ***************************
             Table: sales
      Create Table: CREATE TABLE `sales` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime DEFAULT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CONNECTION='default_oss_server'
      /*!99990 800020205 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
      /*!99990 800020205 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = CSV,
       PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = CSV,
       PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = CSV,
       PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = CSV,
       PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = CSV,
       PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = CSV,
       PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.03 sec)

      由查詢到的表結構資訊可以看出,sales分區表中的p20200101000000p20210101000000p20220101000000_p20230101000000_p20240101000000以及_p20250101000000分區均轉存至OSS,InnoDB引擎中僅保留了3個熱資料分區_p20260101000000_p20270101000000_p20280101000000sales表變為了混合分區表,查詢混合分區表中的資料操作方法請參見查詢混合分區

將冷資料直接刪除

  1. 建立DLM策略

    以下樣本將建立sales分區表,該表以order_time列作為分區鍵,按時間間隔劃分分區。同時,該表存在INTERVAL和DLM兩種策略:

    • INTERVAL策略:當插入的資料超過分區範圍時,將自動建立新的分區,時間間隔為1年。

    • DLM策略:定義當前表僅有3個分區,當分區數量大於3,執行DLM策略時,將直接刪除冷資料。

    1. 建立帶有DLM策略的sales表。

      CREATE TABLE `sales` (
        `id` int DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime DEFAULT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
      (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
      DLM ADD POLICY test_policy TIER TO NONE ON (PARTITIONS OVER 3);

      該表的DLM策略名稱稱為test_policy,策略的執行條件為分區數量大於3。執行DLM策略時會直接刪除冷資料。

    2. 執行以下命令,查看mysql.dlm_policies表。

      SELECT * FROM mysql.dlm_policies\G

      執行結果如下:

      *************************** 1. row ***************************
                         Id: 4
               Table_schema: test
                 Table_name: sales
                Policy_name: test_policy
                Policy_type: NONE
               Archive_type: PARTITION COUNT
               Storage_mode: NULL
             Storage_engine: NULL
              Storage_media: NULL
        Storage_schema_name: NULL
         Storage_table_name: NULL
            Data_compressed: OFF
       Compressed_algorithm: NULL
                    Enabled: ENABLED
            Priority_number: 50000
      Tier_partition_number: 3
             Tier_condition: NULL
                 Extra_info: null
                    Comment: NULL
      1 row in set (0.01 sec)
    3. sales分區表中插入一定的測試資料,以觸發INTERVAL自動建立新的分區。使用proc_batch_insert預存程序插入新的資料。表結構如下:

      CALL proc_batch_insert(1, 3000, 'sales');
      Query OK, 1 row affected, 1 warning (0.99 sec)
    4. 執行如下命令,查看sales表結構資訊。

      SHOW CREATE TABLE sales \G

      執行結果如下:

      *************************** 1. row ***************************
             Table: sales
      Create Table: CREATE TABLE `sales` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime DEFAULT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
      /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.03 sec)
  2. 執行DLM策略

    1. 通過以下命令直接執行DLM策略。

      CALL dbms_dlm.execute_all_dlm_policies();
    2. DLM策略執行時,查看mysql.dlm_progress表中資料。

      SELECT * FROM mysql.dlm_progress \G

      表中結果如下:

      *************************** 1. row ***************************
                        Id: 1
              Table_schema: test
                Table_name: sales
               Policy_name: test_policy
               Policy_type: NONE
            Archive_option: PARTITIONS OVER 3
            Storage_engine: NULL
             Storage_media: NULL
           Data_compressed: OFF
      Compressed_algorithm: NULL
        Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
             Archive_stage: ARCHIVE_COMPLETE
        Archive_percentage: 100
        Archived_file_info: null
                Start_time: 2023-01-09 17:31:24
                  End_time: 2023-01-09 17:31:24
                Extra_info: null
      1 row in set (0.03 sec)

      存放低頻冷資料的分區,包括p20200101000000p20210101000000p20220101000000_p20230101000000_p20240101000000_p20250101000000都已被刪除。

    3. sales表結構如下:

      SHOW CREATE TABLE sales \G

      執行結果如下:

      *************************** 1. row ***************************
             Table: sales
      Create Table: CREATE TABLE `sales` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `order_time` datetime DEFAULT NULL,
         PRIMARY KEY (`order_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
      /*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.02 sec)

在ALTER TABLE時建立或刪除DLM策略

  • ALTER TABLE時建立DLM策略。

    ALTER TABLE t DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
    STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);

    t的DLM策略名稱稱為test_policy,策略的執行條件為分區數量大於3。執行該DLM策略且分區數量大於3時,會將表t中舊的分區中的資料歸檔至OSS,且歸檔在OSS的表名為sales_history

  • 讓表t上的DLM策略test_policy生效。

    ALTER TABLE t DLM ENABLE POLICY test_policy;
  • 讓表t上的DLM策略test_policy失效。

    ALTER TABLE t DLM DISABLE POLICY test_policy;
  • 刪除表t上的DLM策略test_policy

    ALTER TABLE t DLM DROP POLICY test_policy;

執行錯誤處理

DLM策略執行過程中,可能因為配置原因,出現DLM策略執行錯誤的情況。此時,錯誤記錄會儲存在mysql.dlm_progress表中。通過如下命令查看錯誤記錄:

SELECT * FROM mysql.dlm_progress WHERE Archive_stage = "ARCHIVE_ERROR";

Extra_info欄位中找到錯誤的詳細資料,確認錯誤原因後,刪除目前記錄或者修改目前記錄的Archive_stageARCHIVE_COMPLETE,再通過call dbms_dlm.execute_all_dlm_policies;命令手動執行DLM策略或者等待下一個執行循環自動執行。

說明

如果當前策略的執行記錄中,存在狀態為ARCHIVE_ERROR的記錄,考慮到資料安全的原因,該條策略不會再自動執行,您需要在確認執行失敗原因並修改對應的記錄後,該條策略才會繼續執行。