Cuándo elegir el particionamiento RANGE - PolarDB - Centro de documentación de Alibaba Cloud

El particionamiento por rango divide una tabla o un índice en segmentos mediante valores límite. Es el enfoque estándar para los datos de series temporales y las tablas grandes que requieren rotación periódica de datos. Esta técnica permite que el optimizador de consultas omita por completo los segmentos irrelevantes. Además, facilita agregar o eliminar datos a nivel de partición en lugar de hacerlo fila por fila.

Casos de uso del particionamiento por rango

Acelerar consultas de rango con la eliminación de particiones

Cuando se consultan tablas grandes con frecuencia por fecha o por un rango numérico, como una tabla de pedidos (ORDER) o un programa de compras (LINEITEM), el particionamiento por rango permite que el optimizador de consultas omita por completo las particiones irrelevantes. A esta técnica se le conoce como eliminación de particiones (partition pruning).

Por ejemplo, si particiona una tabla de pedidos por año y consulta los datos de un solo año, el optimizador accede únicamente a una partición en lugar de examinar todas. En una tabla con ocho años de datos, una consulta de un solo año toca una partición de ocho. Esto reduce proporcionalmente las operaciones de E/S y el tiempo de exploración.

Simplificar la rotación de datos con una ventana deslizante

El particionamiento por rango es el enfoque estándar para mantener una ventana de datos continua; por ejemplo, conservar los datos de los últimos 36 meses. Agregar un mes nuevo y quitar el más antiguo se gestiona a nivel de partición y no a nivel de fila:

Cargue los datos del mes nuevo en una tabla de preparación (staging).
Limpie e indexe la tabla de preparación.
Ejecute EXCHANGE PARTITION para intercambiar la tabla de preparación con la tabla particionada. La tabla particionada permanece disponible para consultas durante todo el proceso.
Ejecute DROP PARTITION para eliminar la partición del mes más antiguo.

Trabajar con particiones completas resulta mucho más eficiente que eliminar filas individuales o reconstruir índices en toda la tabla.

Dividir tablas grandes para facilitar su gestión

Las operaciones de copia de seguridad y restauración en una sola tabla grande pueden exceder las ventanas de mantenimiento. El particionamiento por rango divide la tabla en bloques lógicos más pequeños. Así podrá respaldar, restaurar o archivar cada partición de forma independiente.

Ejemplo de particionamiento por rango

A continuación se muestra una tabla orders que abarca más de nueve años. La tabla se particiona por la columna o_orderdate en ocho particiones anuales, además de una partición de desbordamiento. Este diseño admite tanto el análisis de ventas en intervalos cortos mediante la eliminación de particiones como la gestión de datos con ventana deslizante.

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(o_orderdate))
(PARTITION item1 VALUES LESS THAN (TO_DAYS('1992-01-01')),
 PARTITION item2 VALUES LESS THAN (TO_DAYS('1993-01-01')),
 PARTITION item3 VALUES LESS THAN (TO_DAYS('1994-01-01')),
 PARTITION item4 VALUES LESS THAN (TO_DAYS('1995-01-01')),
 PARTITION item5 VALUES LESS THAN (TO_DAYS('1996-01-01')),
 PARTITION item6 VALUES LESS THAN (TO_DAYS('1997-01-01')),
 PARTITION item7 VALUES LESS THAN (TO_DAYS('1998-01-01')),
 PARTITION item8 VALUES LESS THAN (TO_DAYS('1999-01-01')),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

Ejecute EXPLAIN para comprobar la eliminación de particiones. Una consulta sobre o_orderDATE = '1992-03-01' debe acceder únicamente a item2:

EXPLAIN SELECT * FROM orders WHERE o_orderDATE = '1992-03-01';

+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | item2      | ref  | i_o_orderdate | i_o_orderdate | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

Debido a los límites del tipo RANGE, es posible que la instrucción DDL original no se muestre después de particionar la tabla con TO_DAYS() y ejecutar la instrucción SHOW CREATE TABLE . Los límites de las particiones podrían aparecer como valores enteros de días en lugar de cadenas de fecha. Se trata de una limitación de visualización conocida que no afecta el comportamiento de las consultas.

SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`o_orderDATE`))
(PARTITION item1 VALUES LESS THAN (727563),
 PARTITION item2 VALUES LESS THAN (727929),
 PARTITION item3 VALUES LESS THAN (728294),
 PARTITION item4 VALUES LESS THAN (728659),
 PARTITION item5 VALUES LESS THAN (729024),
 PARTITION item6 VALUES LESS THAN (729390),
 PARTITION item7 VALUES LESS THAN (729755),
 PARTITION item8 VALUES LESS THAN (730120),
 PARTITION item9 VALUES LESS THAN MAXVALUE) */

Particionamiento por columnas de rango

El particionamiento por columnas de rango amplía el particionamiento por rango de tres maneras:

Column names only: RANGE COLUMNS() acepta nombres de columnas, no expresiones.
Multi-column support: RANGE COLUMNS() puede incluir una o más columnas. La ubicación de la partición se determina mediante la comparación de tuplas en lugar de comparar valores escalares.
Mayor compatibilidad de tipos: Las columnas de clave de partición pueden ser de tipo INTEGER, STRING, DATE o DATETIME.

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));