Archivar datos en las tablas particionadas - Recomendaciones de AWS

Archivar datos en las tablas particionadas

MySQL admite las particiones para el motor de almacenamiento de InnoDB y puede utilizar esta característica para particionar tablas grandes. Las particiones de la tabla se almacenan como tablas físicas independientes, aunque el SQL que opera en la tabla particionada lee toda la tabla. Esto le da la libertad de eliminar las particiones innecesarias de la tabla sin necesidad de eliminarlas fila por fila, para que pueda archivar las filas históricas de la base de datos.

Tenga en cuenta el código de ejemplo siguiente. TABLE orders existe en el esquema orderprocessing. Sus datos históricos están presentes en la partición phistorical, que contiene datos que pertenecen a 2021 y versiones anteriores. En la misma tabla, los datos más importantes por aplicación están presentes en las particiones activas de cada mes de 2022. Para archivar los datos de la partición phistorical, puede crear un archivo table orders_2021_and_older con la misma estructura en el esquema archive. A continuación, puede utilizar EXCHANGE PARTITION de MySQL para mover la partición phistorical a esa tabla. Tenga en cuenta que la tabla de archivo no está particionada. Tras el archivado, puede verificar los datos y moverlos a Amazon S3.

CREATE TABLE orders (
      orderid bigint NOT NULL AUTO_INCREMENT,
      customerid bigint DEFAULT NULL,
      ............
      ............
      order_date date NOT NULL,
      PRIMARY KEY (`orderid`,`order_date`))
    PARTITION BY RANGE (TO_DAYS(order_date)) (
          PARTITION pstart   VALUES LESS THAN (0),
          PARTITION phistorical VALUES LESS THAN (TO_DAYS('2022-01-01')),
          PARTITION p2022JAN VALUES LESS THAN (TO_DAYS('2022-02-01')),
          PARTITION p2022FEB VALUES LESS THAN (TO_DAYS('2022-03-01')),
          PARTITION p2022MAR VALUES LESS THAN (TO_DAYS('2022-04-01')),
          PARTITION p2022APR VALUES LESS THAN (TO_DAYS('2022-05-01')),
          PARTITION p2022MAY VALUES LESS THAN (TO_DAYS('2022-06-01')),
          PARTITION p2022JUN VALUES LESS THAN (TO_DAYS('2022-07-01')),
          PARTITION p2022JUL VALUES LESS THAN (TO_DAYS('2022-08-01')),
          PARTITION p2022AUG VALUES LESS THAN (TO_DAYS('2022-09-01')),
          PARTITION p2022SEP VALUES LESS THAN (TO_DAYS('2022-10-01')),
          PARTITION p2022OCT VALUES LESS THAN (TO_DAYS('2022-11-01')),
          PARTITION p2022NOV VALUES LESS THAN (TO_DAYS('2022-12-01')),
          PARTITION p2022DEC VALUES LESS THAN (TO_DAYS('2023-01-01')),
          PARTITION pfuture       VALUES LESS THAN MAXVALUE
      );
CREATE TABLE orders_2021_and_older (
        orderid bigint NOT NULL AUTO_INCREMENT,
        customerid bigint DEFAULT NULL,
        ............
        ............
        order_date date NOT NULL,
        PRIMARY KEY (`orderid`,`order_date`));
mysql> alter table orderprocessing.orders exchange partition phistorical with table archive.orders_2021_and_older;
  Query OK, 0 rows affected (0.33 sec)

Cuando utiliza la característica EXCHANGE PARTITION para archivar los datos históricos, le recomendamos las siguientes prácticas:

  • Cree un esquema independiente para almacenar los datos archivados en la aplicación. Este esquema contendrá tablas de archivo que albergarán los datos archivados. Una tabla de archivado en el esquema de archivado debe tener la misma estructura que la tabla activa, lo que incluye sus índices y su clave principal. Sin embargo, la tabla de archivado de destino no puede ser una tabla particionada. El intercambio de particiones entre dos tablas particionadas no está permitido en MySQL.

  • Siga una convención de nomenclatura para su tabla de archivo que sea útil para identificar los datos históricos almacenados en esta. Esto resulta útil cuando hace tareas de auditoría o trabajos de diseño que trasladan estos datos a Amazon S3.

  • Ejecute la instrucción del lenguaje de definición de datos (DDL) de EXCHANGE PARTITION en un tiempo de inactividad cuando no llegue tráfico a su escritor de Aurora compatible con MySQL, Amazon RDS para MySQL o Amazon RDS para MariaDB.

    Es posible que se ejecute EXCHANGE PARTITION durante periodos de poco tráfico en la aplicación o microservicio. Sin embargo, no debería haber escrituras ni selecciones, o muy pocas, en la tabla particionada. Las consultas de selección de larga duración existentes pueden provocar que el DDL EXCHANGE PARTITION espere y, por lo tanto, se produzcan problemas de recursos en la base de datos. Diseñe scripts que verifiquen que se cumplen todas estas condiciones antes de ejecutar EXCHANGE PARTITION en el sistema.

Si el diseño de la aplicación admite datos particionados y, actualmente, tiene una tabla sin particiones, considere la posibilidad de mover los datos a tablas particionadas para poder archivarlos. Para obtener más información, consulte la documentación de MySQL.