Tablas de particiones para la escala

13 minutos

Las decisiones de creación de particiones son casi permanentes. Una clave de partición mal elegida empeora el rendimiento más que una tabla sin particionar, y volver a particionar una tabla de varios terabytes requiere reconstruirla completamente, lo que implica horas de inactividad. La alineación de la clave de partición y el índice que elija durante el diseño determinan si la creación de particiones mejora el sistema o crea pesadillas de mantenimiento que no se pueden deshacer fácilmente.

La creación de particiones de tablas divide las tablas grandes en partes más pequeñas y manejables (particiones) al tiempo que las mantiene como una sola tabla lógica. La aplicación ve una tabla, pero el motor de base de datos administra varios segmentos físicos que se pueden mantener, archivar o consultar de forma independiente.

Comprender los conceptos de partición

La creación de particiones implica varios componentes clave: una función de partición que define cómo se dividen los datos, un esquema de partición que asigna particiones a grupos de archivos y la columna de creación de particiones que determina a qué partición pertenece cada fila. Comprender estos conceptos le ayuda a diseñar una estrategia de creación de particiones eficaz.

Evaluación del rendimiento y las ventajas operativas

La creación de particiones proporciona mejoras en el rendimiento de las consultas a través de la eliminación de particiones en las que las consultas filtran por clave de partición solo las particiones pertinentes (un mes en lugar de 120 meses), el procesamiento paralelo donde se procesan varias particiones simultáneamente entre núcleos de CPU, estadísticas más rápidas calculadas por partición en lugar de toda la tabla y el índice busca dónde las particiones más pequeñas significan árboles B poco profundos.

Entre las ventajas operativas se incluyen el mantenimiento pormenorizado en el que se vuelven a generar índices en la partición actual, mientras que las particiones más antiguas permanecen en línea, el archivado rápido cambiando las particiones antiguas a tablas de archivo en segundos a través de operaciones de metadatos, mejora la disponibilidad del mantenimiento de particiones de forma independiente y el almacenamiento en capas moviendo particiones anteriores a almacenamiento más barato y lento.

Por ejemplo, imagine una empresa de servicios financieros con una tabla de transacciones de 1,2 TB en la que las consultas que filtran por fecha (90% de consultas) examinan toda la tabla. Después de implementar la creación de particiones mensuales, el rendimiento de las consultas mejora de 10 a 20 veces a través de la eliminación de particiones, las recompilaciones de índices van de 6 horas a 20 minutos por partición, el archivado de datos antiguos reduce los bloqueos de cuatro horas a segundos mediante la conmutación de particiones y los costos de almacenamiento reducen 40% moviendo particiones anteriores a almacenamiento más barato.

Comprender cuándo usar la creación de particiones

En la tabla siguiente se muestra cuándo la creación de particiones ayuda frente a cuando agrega complejidad innecesaria:

Escenario	¿Usar particionamiento?	Por qué
Las consultas filtran en una columna específica (fecha, región) el 80%+ del tiempo	Sí	La eliminación de particiones accede únicamente a las particiones pertinentes.
Archivado normal de datos antiguos (mensual, trimestral)	Sí	Cambie particiones en segundos en vez de operaciones `DELETE`
Necesidad de volver a generar índices solo en datos recientes	Sí	Reconstruir la partición actual mientras las particiones anteriores permanecen en funcionamiento
Tablas grandes (multi-TB) con necesidades de almacenamiento en capas	Sí	Traslado de particiones anteriores al almacenamiento más barato
La mayoría de las consultas examinan la tabla completa o filtran en varias columnas	No	Todas las particiones escaneadas, un rendimiento peor que el no particionado
Las búsquedas de una sola fila o los escaneos de rango pequeño son comunes	No	La partición agrega carga adicional sin ventajas
No hay ninguna columna clara alineada con los patrones de consulta	No	No se puede elegir una clave de partición efectiva

Crear componentes de particionamiento

En el ejemplo siguiente se muestran los tres componentes: función de partición, esquema de partición y tabla con particiones:

-- Create partition function based on date ranges
-- Use RANGE RIGHT for datetime columns to keep same-day values together
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME2)
    AS RANGE RIGHT FOR VALUES 
    ('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');

-- Create partition scheme mapping to a single filegroup (recommended)
-- Use multiple filegroups only for tiered storage or independent backups
CREATE PARTITION SCHEME PS_OrderDate
    AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);

-- Create partitioned table
-- Include partition column in primary key for clustered index alignment
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON PS_OrderDate(OrderDate);

En este ejemplo se crean particiones trimestrales para una tabla Orders . La función de partición define cuatro valores de límite (enero, abril, julio, octubre) que crean cinco particiones: una para los datos anteriores a 2024 y cuatro para cada trimestre de 2024. El esquema de partición asigna todas las particiones al grupo de archivos PRIMARY. La tabla Orders usa la columna OrderDate como clave de partición, que debe incluirse en la clave principal para la alineación correcta del índice.

Elegir estrategias de creación de particiones

La clave de partición es la decisión más importante. Si eliges mal, la partición perjudica más de lo que ayuda. La clave de partición ideal aparece en la cláusula de la WHERE mayoría de las consultas, crea particiones razonablemente equilibradas y se alinea con los patrones de mantenimiento.

Los siguientes criterios de selección de claves le ayudan a elegir la clave de partición correcta:

Patrones de consulta: 80%+ de consultas filtran por esta columna
Distribución de datos: distribución uniforme entre particiones (sin una sola partición con 90% de datos)
Alineación del mantenimiento: coincide con los patrones de archivado/purga (columnas de fecha para el archivado basado en el tiempo)
Estabilidad: el valor no cambia después de INSERT (evitar la creación de particiones en columnas actualizables)

Comprender la partición por rangos

La creación de particiones de intervalos divide los datos en función de los intervalos de valores, normalmente las fechas. Cada partición contiene un intervalo específico (datos de enero, datos de febrero, etc.). Esta es la estrategia más usada.

Aquí es donde la creación de particiones de intervalo funciona mejor:

Datos de serie temporal (pedidos, registros, transacciones)
Datos secuenciales (números de factura, identificadores de pedido)
Rangos numéricos (bandas de salarios, planes de precio)

En la tabla siguiente se muestran patrones de partición comunes:

Modelo	Cuándo usar
Diariamente	Sistemas de gran volumen, retención corta
Weekly	Volumen medio, retención de 6 a 12 meses
Monthly	Más común, equilibra el número de particiones y el tamaño
Trimestralmente	Menor volumen, retención de varios años
Anual	Escenarios de archivo, datos históricos a largo plazo

Por ejemplo, una plataforma de comercio electrónico que particiona los pedidos mensualmente permite que las consultas del mes actual se dirijan a una partición, los informes trimestrales accedan a 3 particiones y el análisis de fin de año utilice 12 particiones, mientras se eliminan los años más antiguos.

Puede crear particiones de intervalo definiendo límites en la función de partición:

-- RANGE RIGHT creates 5 partitions: <100000, 100000-199999, 200000-299999, 300000-399999, >=400000
CREATE PARTITION FUNCTION PF_InvoiceNumber (INT)
    AS RANGE RIGHT FOR VALUES 
    (100000, 200000, 300000, 400000);

Partición por valores categóricos

Puede usar la creación de particiones RANGE con valores de cadena o de categorías como regiones. La función de partición coloca valores en función del criterio de ordenación. Este enfoque funciona para la distribución geográfica, los sistemas multiinquilino o los datos departamentales en los que las consultas se filtran con frecuencia por categoría.

En el ejemplo siguiente se crean particiones de datos por región:

-- Partition by region
CREATE PARTITION FUNCTION PF_Region (NVARCHAR(50))
    AS RANGE LEFT FOR VALUES ('East', 'North', 'South', 'West');

CREATE PARTITION SCHEME PS_Region
    AS PARTITION PF_Region ALL TO ([PRIMARY]);

CREATE TABLE RegionalData (
    DataID INT NOT NULL,
    Region NVARCHAR(50) NOT NULL,
    Value DECIMAL(10,2),
    CONSTRAINT PK_RegionalData PRIMARY KEY (DataID, Region)
) ON PS_Region(Region);

Implemente particiones de índice

Al particionar una tabla, los índices se pueden alinear o no alinear. Los índices alineados usan el mismo esquema de partición que la tabla, mientras que los índices no alineados usan particiones diferentes o sin particiones. De forma predeterminada, los índices no agrupados en las tablas con particiones heredan el esquema de partición de la tabla.

Comprender índices alineados versus no alineados

Los índices alineados usan la misma función de partición que la tabla. Cada partición de índice coincide con una partición de tabla, lo que permite la conmutación rápida de particiones, el mantenimiento simplificado y una mejor eliminación de particiones.

Los índices no alineados usan particiones diferentes o sin particiones. No pueden usar la conmutación de particiones y no se admiten en tablas con más de 1000 particiones.

Use índices alineados cuando necesite cambiar de partición para el archivado, desea recompilar particiones específicas de forma independiente o cuando los patrones de consulta filtren por la clave de partición.

Por ejemplo, imagine una tabla Ordersparticionada por OrderDate con un índice no agrupado en CustomerID. El uso de particiones alineadas con el mismo esquema OrderDate permite archivar los meses antiguos cambiando particiones, recompilando los índices actuales de forma independiente y quitando particiones antiguas sin afectar a toda la tabla.

Puede crear índices con particiones mediante el mismo esquema de partición que la tabla base:

-- Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer
    ON Orders(CustomerID)
    ON PS_OrderDate(OrderDate);

-- Create partitioned columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SalesData_CS
    ON SalesData(Revenue, Region)
    ON PS_SalesDate(SaleDate);

Administración de operaciones de partición

Después de crear tablas particionadas, debe administrarlas a lo largo del tiempo. Entre las operaciones comunes se incluyen la consulta de metadatos de partición, la adición de nuevas particiones a medida que crecen los datos y la eliminación de particiones antiguas durante el archivado. Estas operaciones usan la función $PARTITION y la instrucción ALTER PARTITION FUNCTION.

Consulta de la información de partición

Puede ver la información de partición mediante la $PARTITION función . Este es un ejemplo:

-- View partition information
SELECT 
    $PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber,
    MIN(OrderDate) AS MinDate,
    MAX(OrderDate) AS MaxDate,
    COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.PF_OrderDate(OrderDate)
ORDER BY PartitionNumber;

Agregar nuevo límite de partición

Puede dividir particiones para agregar nuevos valores de límite mediante ALTER PARTITION FUNCTION. Este es un ejemplo:

-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
    SPLIT RANGE ('2024-11-01');

Esta instrucción agrega un nuevo valor de límite (1 de noviembre de 2024) a la función de partición, dividiendo una partición existente en dos particiones. La partición que contiene fechas de octubre a diciembre ahora se convierte en dos particiones: una para octubre y otra para noviembre a diciembre.

Archivar y quitar particiones

Puede combinar particiones para archivar datos antiguos mediante ALTER PARTITION FUNCTION con MERGE RANGE. Este es un ejemplo:

-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
    MERGE RANGE ('2023-12-31');

Aplicar las mejores prácticas de particionamiento

Los procedimientos recomendados siguientes le ayudan a evitar errores comunes de creación de particiones difíciles de corregir después de la implementación:

Alinear índices con particiones de tabla: use el mismo esquema de partición para tablas e índices para habilitar la conmutación de particiones y el mantenimiento.
Supervisión de la distribución de datos: Verifique las estadísticas de partición periódicamente para identificar particiones desequilibradas y verificar la eliminación de particiones.
Automatización de la administración de particiones: programe trabajos para agregar nuevas particiones antes de alcanzar los límites y archivar particiones antiguas.
Evitar la creación de particiones excesivas: establecer como destino millones de filas por partición, no miles: demasiadas particiones crean sobrecarga
Incluir clave de partición en la clave principal: es necesario para alinear el índice agrupado en el esquema de particiones

La creación de particiones requiere un planeamiento cuidadoso. La alineación de la clave de partición y del índice que elige determinan si mejora el rendimiento o crea complejidad. Cuando se implementa correctamente, la creación de particiones transforma la administración de tablas grandes a través de consultas más rápidas, un archivado eficaz y un mantenimiento simplificado.

Comentarios

¿Le ha resultado útil esta página?