Como arquivar automaticamente dados frios de uma tabela particionada em formato CSV - PolarDB: Central de Documentação do Alibaba Cloud

O recurso de gerenciamento de ciclo de vida de dados (DLM) ajuda a reduzir custos de armazenamento e aumentar a eficiência. Ele arquiva automática e periodicamente dados frios pouco acessados do PolarStore para um meio de armazenamento de baixo custo, como o Object Storage Service (OSS).

Pré-requisitos

Seu cluster deve executar o PolarDB for MySQL 8.0.2, revisão 8.0.2.2.34.1 ou posterior.
Nota
- Para verificar a versão do seu cluster, consulte Consultar a versão do mecanismo.
- Se o seu cluster executar o PolarDB for MySQL 8.0.2, revisão 8.0.2.2.11.1 ou posterior, o recurso DLM não registrará operações no log binário.
Ative o arquivamento de dados frios antes de usar políticas de DLM. Para mais informações, consulte Ativar o arquivamento de dados frios.
Nota
Caso o recurso de arquivamento de dados frios não esteja ativado, o sistema retornará o seguinte erro:
```
ERROR 8158 (HY000): [Data Lifecycle Management] DLM storage engine is not support. The value of polar_dlm_storage_mode is OFF.
```

Limitações

O recurso DLM suporta apenas tabelas particionadas sem subpartições. O método de particionamento deve ser RANGE COLUMN.
Não é possível usar o recurso DLM em uma tabela particionada que possua um índice secundário global (GSI).
O PolarDB for MySQL não suporta a modificação de uma política de DLM. Para alterar uma política, exclua primeiro a política existente e crie uma nova.
Se existir uma política de DLM em uma tabela, não execute operações DDL que causem inconsistências de esquema entre a tabela de source e a tabela de arquivamento, como adicionar ou excluir colunas ou modificar tipos de dados das colunas. Tais inconsistências podem impedir a análise dos dados arquivados posteriormente. Antes de realizar essas operações DDL, exclua a política de DLM da tabela. Para retomar o arquivamento automático de dados, crie uma nova política de DLM e especifique um novo nome para a tabela de arquivamento. O novo nome não pode ser igual a nenhum nome de tabela de arquivamento usado anteriormente.
Utilize o particionamento INTERVAL RANGE para estender partições automaticamente e o recurso DLM para arquivar dados de partições pouco utilizadas no OSS.

Nota
O particionamento INTERVAL RANGE é suportado apenas para clusters que executam o PolarDB for MySQL 8.0.2, revisão 8.0.2.2.0 ou posterior.

Especifique uma política de DLM ao executar a instrução CREATE TABLE ou ALTER TABLE.
A instrução SHOW CREATE TABLE não exibe políticas de DLM. Visualize todas as políticas de DLM na tabela mysql.dlm_policies.

Precauções

Após o arquivamento dos dados frios, a tabela de arquivamento no OSS torna-se somente leitura, e o desempenho das consultas pode ser lento. Teste antecipadamente para garantir que o desempenho das consultas atenda aos seus requisitos.
Depois que uma partição de uma tabela particionada é arquivada no OSS, os dados dessa partição tornam-se somente leitura. Não é possível executar operações DDL na tabela particionada.
As operações de backup não incluem dados arquivados no OSS. Os dados no OSS não suportam recuperação point-in-time.

Sintaxe

Criar uma política

Criar uma política de DLM com CREATE TABLE

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

Criar uma política de DLM com ALTER TABLE

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

Parâmetros da política de DLM

Parâmetro	Obrigatório	Descrição
tbl_name	Sim	Nome da tabela.
policy_name	Sim	Nome da política.
TIER TO TABLE	Sim	Archives data to a new OSS foreign table.
TIER TO PARTITION	Sim	Converte partições de dados quentes em partições de dados frios armazenadas no OSS dentro da mesma tabela, criando uma tabela particionada híbrida. Nota Este recurso está em lançamento canário. Para usá-lo, acesse o Quota Center, localize o nome da cota correspondente ao ID de cota `polardb_mysql_hybrid_partition` e clique em Apply na coluna Actions. O arquivamento de partições de uma tabela particionada para o OSS só é possível se o seu cluster executar o PolarDB for MySQL 8.0.2, revisão 8.0.2.2.17 ou posterior. Ao utilizar este recurso, certifique-se de que o número total de partições na tabela particionada não exceda 8.192.
TIER TO NONE	Sim	Exclui os dados das partições mais antigas em vez de arquivá-los.
engine_name	Não	Mecanismo de armazenamento para os dados arquivados. Atualmente, os dados só podem ser arquivados para o mecanismo CSV.
storage_schema_name	Não	Banco de dados para a tabela de arquivamento. O padrão é o banco de dados da tabela de source.
storage_table_name	Não	Nome da tabela de arquivamento. Se não especificado, o padrão será `<source_table_name>_<dlm_policy_name>`.
STORAGE [=] OSS	Não	Armazena os dados arquivados no OSS. Este é o padrão.
READ ONLY	Não	Torna os dados arquivados somente leitura. Este é o padrão.
comment_string	Não	Comentário para a política de DLM.
extra_info	Não	Especifica as informações de OSS_FILE_FILTER para a tabela OSS de destino. Nota O arquivamento de partições para o OSS exige que seu cluster execute a Enterprise Edition do PolarDB for MySQL 8.0.2, revisão 8.0.2.2.25 ou posterior. Este recurso entra em vigor apenas se a tabela de destino não existir. Nesse caso, o sistema gera automaticamente o atributo FILE_FILTER com base no parâmetro OSS_FILE_FILTER em EXTRA_INFO quando a tabela OSS de destino é criada. Se a tabela de destino já existir, o filtro de arquivo existente será utilizado. O formato de `EXTRA_INFO` é `{"oss_file_filter":"field_filter[,field_filter]"}`, onde `field_filter` é definido da seguinte forma: `field_filter := field_name[:filter_type] filter_type := bloom`
ON (PARTITIONS OVER num)	Sim	Archives data when the number of partitions is greater than `num`.

Gerenciar uma política

Ative uma política de DLM.

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

Desative uma política de DLM.

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

Exclua uma política de DLM.

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

Nessas instruções, table_name é o nome da tabela, e dlm_policy_name é o nome da política a ser gerenciada. É possível especificar vários nomes de políticas.

Executar uma política

Execute todas as políticas de DLM em todas as tabelas do cluster atual.
```
CALL dbms_dlm.execute_all_dlm_policies();
```
Execute as políticas de DLM em uma única tabela.
```
CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
```
Nesta instrução, database_name é o nome do banco de dados que contém a tabela, e table_name é o nome da tabela.

Utilize o recurso MySQL event para executar políticas de DLM durante a janela de manutenção do seu cluster. Esse método evita que o desempenho do banco de dados seja afetado durante os horários de pico de negócios e permite mover periodicamente dados expirados para reduzir custos de armazenamento. Use a seguinte sintaxe para executar uma política de DLM com um evento:

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');
}

A tabela a seguir descreve os parâmetros.

Parâmetro	Obrigatório	Descrição
event_name	Sim	Nome do evento.
schedule	Sim	Horário e frequência para executar o evento.
comment	Não	Comentário para o evento.
event_body	Sim	Conteúdo que o evento executa. Deve ser uma instrução que execute uma política de DLM. Nota Se você usar `CALL dbms_dlm.execute_all_dlm_policies()`, o evento executará todas as políticas de DLM no cluster. Portanto, crie apenas um evento desse tipo por cluster. Se você usar `CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');`, o evento executará todas as políticas de DLM apenas em uma tabela específica. Portanto, crie um evento para cada tabela que requer arquivamento agendado.
interval	Sim	Frequência de execução do evento.
timestamp	Sim	Horário para iniciar a execução do evento.
database_name	Sim	Nome do banco de dados.
table_name	Sim	Nome da tabela.

Para mais informações sobre o recurso MySQL EVENT, consulte a documentação oficial do MySQL para CREATE EVENT.

Para exemplos de uso, consulte Exemplos de arquivamento de dados frios no OSS.

Exemplos

Arquivar dados em uma tabela externa

Criar uma política de DLM

O exemplo a seguir cria uma tabela particionada chamada sales que usa a coluna order_time como chave de partição. A tabela possui uma política INTERVAL e uma política DLM:

Política INTERVAL: Quando os dados inseridos ficam fora do intervalo de partição existente, uma nova partição é criada automaticamente com um intervalo de tempo de um ano.
Política DLM: A tabela é definida para reter apenas três partições. Quando o número de partições excede três, a política DLM é acionada e executa uma das seguintes ações:
- Se a tabela externa do OSS sales_history não existir, uma nova tabela externa do OSS chamada sales_history será criada, e os dados frios serão arquivados na tabela sales_history.
- Se a tabela externa sales_history existir e a tabela sales_history estiver no OSS integrado, os dados frios serão arquivados diretamente na tabela externa sales_history.

Nota

Para criar uma tabela com particionamento INTERVAL RANGE, certifique-se de que todos os pré-requisitos sejam atendidos. Para mais informações sobre INTERVAL, consulte Particionamento INTERVAL RANGE.

Crie a tabela sales com uma política de DLM.

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

A política de DLM para a tabela chama-se test_policy. Quando o número de partições excede três, a política arquiva os dados frios da tabela de source em formato CSV no OSS. A tabela de arquivamento resultante chama-se sales_history e é somente leitura. Se a tabela de arquivamento OSS não existir, o sistema a criará automaticamente e adicionará um OSS_FILE_FILTER às colunas id e name.

As políticas de DLM da tabela atual são armazenadas na tabela de sistema mysql.dlm_policies. Consulte essa tabela para visualizar os detalhes das políticas de DLM. Para mais informações sobre a tabela mysql.dlm_policies, consulte Descrição da estrutura da tabela. Visualize a estrutura da tabela mysql.dlm_policies.

mysql> SELECT * FROM mysql.dlm_policies\G

Saída de exemplo:

*************************** 1. row ***************************
                   Id: 3
         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: {"oss_file_filter": "id,name:bloom,order_time"}
              Comment: NULL
1 row in set (0.03 sec)

Atualmente, a tabela sales possui três partições, portanto, nenhum dado foi arquivado.

Insira 3.000 linhas de dados de teste na tabela particionada sales. Isso garante que os dados excedam o intervalo de partição definido e aciona a política INTERVAL para criar novas partições automaticamente.

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

A política INTERVAL é acionada, adicionando novas partições à tabela sales. A estrutura da tabela agora é a seguinte:

mysql> SHOW CREATE TABLE sales\G

Saída de exemplo:

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

As novas partições aumentam a contagem total de partições para mais de três. Isso atende à condição da política de DLM, e os dados agora estão prontos para arquivamento.

Executar a política de DLM

Execute a política de DLM diretamente usando uma instrução SQL ou execute-a periodicamente usando o recurso MySQL EVENT. Por exemplo, suponha que sua janela de manutenção comece às 01:00 todos os dias, a partir de 11 de outubro de 2022. Crie o seguinte evento para executar a política de DLM diariamente às 01:00.
```
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();
```
Após as 01:00, este evento executa todas as políticas de DLM em todas as tabelas.

Execute o seguinte comando para visualizar a estrutura da tabela sales:

mysql> SHOW CREATE TABLE sales\G

Saída de exemplo:

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

A tabela agora possui apenas três partições.

Consulte a tabela mysql.dlm_progress para visualizar o histórico de execução da política de DLM. Para mais informações sobre a tabela dlm_progress, consulte Estruturas de tabelas. Execute o seguinte comando para consultar a tabela mysql.dlm_progress :

mysql> SELECT * FROM mysql.dlm_progress\G

Saída de exemplo:

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

As partições que armazenam dados frios pouco acessados, incluindo p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000 e _p20250101000000, foram arquivadas na tabela externa do OSS.

Execute o seguinte comando para visualizar a estrutura da tabela externa do OSS:

mysql> SHOW CREATE TABLE sales_history\G

Saída de exemplo:

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

A tabela agora é uma tabela CSV que usa o mecanismo OSS para armazenamento. Consulte-a da mesma forma que uma tabela local. As colunas especificadas foram adicionadas ao OSS_FILE_FILTER. Como order_time é uma chave de partição, um OSS_FILE_FILTER também foi criado para ela automaticamente.

Consulte os dados nas tabelas sales e sales_history separadamente.

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)

O número total de linhas é 3.000, o que corresponde ao número de linhas inseridas inicialmente na tabela sales.

Consulte a tabela externa do OSS usando o OSS_FILE_FILTER. O switch OSS_FILE_FILTER deve estar ativado.

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)

Arquivar partições no OSS

Criar uma política de DLM

O exemplo a seguir cria uma tabela particionada chamada sales que usa a coluna order_time como chave de partição. A tabela possui uma política INTERVAL e uma política DLM:

Política INTERVAL: Quando os dados inseridos ficam fora do intervalo de partição existente, uma nova partição é criada automaticamente com um intervalo de tempo de um ano.
Política DLM: A tabela é definida para reter apenas três partições. Quando o número de partições excede três, a política DLM é acionada e arquiva as partições mais antigas diretamente no OSS.

Crie a tabela 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);

A política de DLM para a tabela chama-se policy_part2part. Quando o número de partições excede três, as partições mais antigas são arquivadas no OSS.

Visualize a política de DLM na tabela mysql.dlm_policies.

SELECT * FROM mysql.dlm_policies\G

Saída de exemplo:

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

Use o procedimento armazenado proc_batch_insert para inserir dados de teste na tabela particionada sales. Esta ação aciona a política INTERVAL, que cria novas partições automaticamente.
```
CALL proc_batch_insert(1, 3000, 'sales');
```
O resultado a seguir indica que os dados foram inseridos com sucesso:
```
Query OK, 1 row affected, 1 warning (0.99 sec)
```

Execute o seguinte comando para visualizar a estrutura da tabela sales:

SHOW CREATE TABLE sales \G

Saída de exemplo:

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

Executar a política de DLM

Execute o seguinte comando para executar a política de DLM:
```
CALL dbms_dlm.execute_all_dlm_policies();
```

Consulte a tabela mysql.dlm_progress para visualizar o histórico de execução do DLM.

SELECT * FROM mysql.dlm_progress \G

Saída de exemplo:

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

Execute o seguinte comando para visualizar a estrutura da tabela sales:

SHOW CREATE TABLE sales \G

Saída de exemplo:

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

A saída mostra que as partições p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000 e _p20250101000000 da tabela particionada sales foram arquivadas no OSS. Apenas as três partições de dados quentes, _p20260101000000, _p20270101000000 e _p20280101000000, são retidas no mecanismo InnoDB. A tabela sales agora é uma tabela particionada híbrida. Para obter informações sobre como consultar dados em uma tabela particionada híbrida, consulte Consultar uma tabela particionada híbrida.

Excluir dados frios

Criar uma política de DLM

O exemplo a seguir cria uma tabela particionada chamada sales que usa a coluna order_time como chave de partição. A tabela possui uma política INTERVAL e uma política DLM:

Política INTERVAL: Quando os dados inseridos ficam fora do intervalo de partição existente, uma nova partição é criada automaticamente com um intervalo de tempo de um ano.
Política DLM: A tabela é definida para reter apenas três partições. Quando o número de partições excede três, a política DLM é acionada para excluir os dados frios.

Crie a tabela sales com uma política de DLM.

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

A política de DLM para a tabela chama-se test_policy. Ela é acionada quando o número de partições excede três. Quando executada, a política exclui os dados frios.

Execute o seguinte comando para consultar a tabela mysql.dlm_policies:

SELECT * FROM mysql.dlm_policies\G

Saída de exemplo:

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

Use o procedimento armazenado proc_batch_insert para inserir dados de teste na tabela particionada sales. Esta ação aciona a política INTERVAL, que cria novas partições automaticamente.
```
CALL proc_batch_insert(1, 3000, 'sales');
Query OK, 1 row affected, 1 warning (0.99 sec)
```

Execute o seguinte comando para visualizar a estrutura da tabela sales:

SHOW CREATE TABLE sales \G

Saída de exemplo:

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

Executar a política de DLM

Execute o seguinte comando para executar a política de DLM diretamente:
```
CALL dbms_dlm.execute_all_dlm_policies();
```

Enquanto a política de DLM estiver em execução, consulte os dados na tabela mysql.dlm_progress.

SELECT * FROM mysql.dlm_progress \G

Os resultados na tabela são os seguintes:

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

As partições que armazenam dados frios pouco acessados, incluindo p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000 e _p20250101000000, foram excluídas.

A estrutura da tabela sales agora é a seguinte:

SHOW CREATE TABLE sales \G

Saída de exemplo:

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

Gerenciar políticas com ALTER TABLE

Crie uma política de DLM usando a instrução ALTER TABLE.
```
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);
```
A política de DLM para a tabela t chama-se test_policy. Ela é acionada quando o número de partições excede três. Quando executada, esta política arquiva dados das partições mais antigas da tabela t para uma tabela OSS chamada sales_history.
Ative a política de DLM test_policy na tabela t.
```
ALTER TABLE t DLM ENABLE POLICY test_policy;
```
Desative a política de DLM test_policy na tabela t.
```
ALTER TABLE t DLM DISABLE POLICY test_policy;
```
Exclua a política de DLM test_policy da tabela t.
```
ALTER TABLE t DLM DROP POLICY test_policy;
```

Solucionar erros de execução

Políticas de DLM podem falhar na execução devido a problemas de configuração. Os registros de erro são armazenados na tabela mysql.dlm_progress. Execute o seguinte comando para visualizar os registros de erro:

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

Localize os detalhes do erro no campo Extra_info. Após identificar e resolver a causa do erro, exclua o registro ou atualize seu Archive_stage para ARCHIVE_COMPLETE. Em seguida, execute o comando call dbms_dlm.execute_all_dlm_policies; para executar a política manualmente ou aguarde a próxima execução agendada.

Nota

Para segurança dos dados, se um registro de execução de política tiver o estado ARCHIVE_ERROR, o agendador não executará a política novamente automaticamente. Depois que você confirmar a causa da falha e atualizar o registro, a política retomará sua execução agendada.