如何使用自動歸檔冷資料 - PolarDB

自動歸檔冷資料功能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日誌。

說明

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

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']
    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']
    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策略的備忘。
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外表

建立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。

建立帶有DLM策略的sales表。

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) 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' ON (PARTITIONS OVER 3);

該表的DLM策略名稱稱為test_policy，當分區數量大於3時，會將當前表的冷資料轉存為CSV格式，並儲存在OSS上。轉存後的表名稱為sales_history，資料唯讀。

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

SELECT * FROM mysql.dlm_policies\G
*************************** 1. row ***************************
                   Id: 1
         Table_schema: test
           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: test
   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: null
              Comment: NULL
1 row in set (0.00 sec)

目前sales表的分區數量為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');

此時，觸發了INTERVAL自動建立新的分區，導致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
) 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策略的執行條件，可以進行資料歸檔。

執行DLM策略

您可以通過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策略。

執行完成後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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ 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.01 sec)

當前表僅剩3個分區。

您可以通過mysql.dlm_progress表查看DLM策略的執行記錄，表dlm_progress定義請參見表結構說明。查看mysql.dlm_progress 表。當前DLM策略的具體執行記錄如下：

SELECT * FROM mysql.dlm_progress\G
*************************** 1. row ***************************
                  Id: 4
        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: 100
  Archived_file_info: [{"origin": "p20200101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316304018.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "p20210101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316532347.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "p20220101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316789675.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20230101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317088201.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20240101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317332186.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20250101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317596882.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}]
          Start_time: 2023-01-09 17:25:16
            End_time: 2023-01-09 17:25:18
          Extra_info: null
1 row in set (0.03 sec)

存放低頻冷資料的分區，包括p20200101000000、p20210101000000、p20220101000000、_p20230101000000、 _p20240101000000、 _p20250101000000都已轉存到OSS外表上。

查看OSS外表的表結構。

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
) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='default_oss_server/sales_history'
1 row in set (0.00 sec)

該表變為資料存放區在OSS引擎的CSV表，查詢方法與查詢本地表的方法相同。

分別查詢sales和sales_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表插入的資料量一致。

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

建立DLM策略

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

INTERVAL策略：當插入的資料超過分區範圍時，將自動建立新的分區，時間間隔為1年。
DLM策略：定義當前表僅有3個分區，當分區數量大於3時，執行DLM策略會直接將之前的分區轉存至OSS。

建立sales表。

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) 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。

在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)

使用proc_batch_insert預存程序向sales分區表中插入一定的測試資料，以觸發INTERVAL策略來自動建立新的分區。
```
CALL proc_batch_insert(1, 3000, 'sales');
```
執行結果如下，表示資料插入成功：
```
Query OK, 1 row affected, 1 warning (0.99 sec)
```

執行以下命令，查看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
) 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)

執行DLM策略

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

CALL dbms_dlm.execute_all_dlm_policies();

通過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)

執行以下命令，查看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
) 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分區表中的p20200101000000、p20210101000000、p20220101000000、_p20230101000000、_p20240101000000以及_p20250101000000分區均轉存至OSS，InnoDB引擎中僅保留了3個熱資料分區_p20260101000000、_p20270101000000和_p20280101000000。sales表變為了混合分區表，查詢混合分區表中的資料操作方法請參見查詢混合分區。

將冷資料直接刪除

建立DLM策略

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

INTERVAL策略：當插入的資料超過分區範圍時，將自動建立新的分區，時間間隔為1年。
DLM策略：定義當前表僅有3個分區，當分區數量大於3，執行DLM策略時，將直接刪除冷資料。

建立帶有DLM策略的sales表。

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) 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策略時會直接刪除冷資料。

查看mysql.dlm_policies表。當前DLM策略的詳情如下：

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)

向sales分區表中插入一定的測試資料，以觸發INTERVAL自動建立新的分區。使用proc_batch_insert預存程序插入新的資料。表結構如下：

CALL proc_batch_insert(1, 3000, 'sales');
Query OK, 1 row affected, 1 warning (0.99 sec)

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
) 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)

執行DLM策略

通過以下命令直接執行DLM策略。
```
CALL dbms_dlm.execute_all_dlm_policies();
```

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)

存放低頻冷資料的分區，包括p20200101000000、p20210101000000、p20220101000000、_p20230101000000、_p20240101000000和_p20250101000000都已被刪除。

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
) 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_stage為ARCHIVE_COMPLETE，再通過call dbms_dlm.execute_all_dlm_policies;命令手動執行DLM策略或者等待下一個執行循環自動執行。

說明

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