AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB MySQL版 用户指南 冷数据归档 手动和自动归档冷数据 自动归档冷数据 使用说明

使用说明

更新时间：
产品详情
相关技术圈
我的收藏

自动归档冷数据功能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表，并在id和name列上创建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)

      存放低频冷数据的分区，包括p20200101000000、p20210101000000、p20220101000000、_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. 分别查询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表插入的数据量一致。

    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分区表中的p20200101000000、p20210101000000、p20220101000000、_p20230101000000、_p20240101000000以及_p20250101000000分区均转存至OSS，InnoDB引擎中仅保留了3个热数据分区_p20260101000000、_p20270101000000和_p20280101000000。sales表变为了混合分区表，查询混合分区表中的数据操作方法请参见查询混合分区。

将冷数据直接删除

  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)

      存放低频冷数据的分区，包括p20200101000000、p20210101000000、p20220101000000、_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_stage为ARCHIVE_COMPLETE，再通过call dbms_dlm.execute_all_dlm_policies;命令手动执行DLM策略或者等待下一个执行周期自动执行。

说明

如果当前策略的执行记录中，存在状态为ARCHIVE_ERROR的记录，考虑到数据安全的原因，该条策略不会再自动执行，您需要在确认执行失败原因并修改对应的记录后，该条策略才会继续执行。

上一篇：自动归档冷数据下一篇：表结构说明
文档推荐