Tabelas de partição para escala
As decisões de partição são quase permanentes. Uma chave de partição mal escolhida piora o desempenho do que uma tabela não particionada, e reparticionar uma tabela de vários terabytes requer reconstruí-la completamente com horas de inatividade. A chave de partição e o alinhamento do índice que escolhes durante o design determinam se a partição melhora o teu sistema ou cria pesadelos de manutenção que não podes facilmente desfazer.
A partição de tabelas divide tabelas grandes em partes menores e mais geríveis (partições), mantendo-as como uma única tabela lógica. A sua aplicação vê uma tabela, mas o motor da base de dados gere múltiplos segmentos físicos que podem ser mantidos, arquivados ou consultados de forma independente.
Compreender os conceitos de partição
A partição envolve vários componentes-chave: uma função de partição que define como os dados são divididos, um esquema de partições que mapeia partições para grupos de ficheiros, e a coluna de partição que determina a que partição cada linha pertence. Compreender estes conceitos ajuda-o a desenhar uma estratégia eficaz de particionamento.
Avaliar os benefícios de desempenho e operacionais
A particionação proporciona melhorias no desempenho das consultas através da eliminação de partições, onde as consultas filtram por chave de partição acedendo apenas às partições relevantes (um mês em vez de 120 meses), processamento paralelo onde múltiplas partições são processadas simultaneamente entre núcleos da CPU, estatísticas mais rápidas calculadas por partição em vez da tabela inteira, e buscas de índice onde partições mais pequenas significam árvores B mais rasas.
Os benefícios operacionais incluem manutenção granular, onde se reconstrói índices na partição atual enquanto as partições antigas permanecem online, arquivamento rápido ao mudar partições antigas para tabelas de arquivo em segundos através de operações de metadados, melhor disponibilidade ao manter as partições de forma independente, e armazenamento em camadas ao transferir partições antigas para armazenamento mais barato e lento.
Por exemplo, imagine uma empresa de serviços financeiros com uma tabela de transações de 1,2 TB onde as consultas filtradas por data (90% de consultas) percorrem toda a tabela. Após implementar a partição mensal, o desempenho das consultas melhora entre 10 a 20 vezes através da eliminação de partições, as reconstruções do índice passam de 6 horas para 20 minutos por partição, o arquivamento de dados antigos reduz bloqueios de quatro horas para segundos através da troca de partições, e os custos de armazenamento diminuem 40% ao mover partições antigas para armazenamento mais barato.
Compreenda quando usar particionamento
A tabela seguinte mostra quando a partição ajuda e quando acrescenta complexidade desnecessária:
| Scenario | Usar particionamento? | Porquê |
|---|---|---|
| As consultas filtram-se numa coluna específica (data, região) 80%+ do tempo | Yes | A eliminação de partições acede apenas às partições relevantes |
| Arquivo regular de dados antigos (mensal, trimestral) | Yes | Troque partições em segundos ao invés de DELETE operações |
| É necessário reconstruir índices apenas com dados recentes | Yes | Reconstruir a partição atual enquanto as partições mais antigas permanecem online |
| Tabelas grandes (multi-TB) com necessidades de armazenamento em níveis | Yes | Transferir partições antigas para armazenamento mais barato |
| A maioria das consultas analisa a tabela completa ou filtra várias colunas | Não | Todas as partições digitalizadas — desempenho pior do que não particionado |
| Consultas de linha única ou varrimentos de pequeno intervalo são comuns | Não | A partição acrescenta sobrecarga sem benefícios |
| Nenhuma coluna clara se alinha com os padrões de consulta | Não | Não é possível escolher uma chave de partição efetiva |
Criar componentes de particionamento
O exemplo seguinte mostra os três componentes: função de partição, esquema de partição e tabela particionada:
-- 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);
Este exemplo cria partições trimestrais para uma tabela de Encomendas . A função de partição define quatro valores de fronteira (janeiro, abril, julho, outubro), criando cinco partições: uma para dados anteriores a 2024 e quatro para cada trimestre de 2024. O esquema de partições mapeia todas as partições para o grupo de ficheiros PRINCIPAL. A tabela de Ordens utiliza a coluna OrderDate como chave de partição, que deve ser incluída na chave primária para um alinhamento adequado do índice.
Escolha estratégias de particionamento
A chave da partição é a tua decisão mais importante. Se fizer uma escolha inadequada, o particionamento pode prejudicar mais do que ajudar. A chave de partição ideal aparece na WHERE cláusula da maioria das consultas, cria partições razoavelmente equilibradas e alinha-se com os seus padrões de manutenção.
Os seguintes critérios de seleção de chave ajudam-no a escolher a chave de partição certa:
- Padrões de consulta: 80%+ de consultas filtram por esta coluna
- Distribuição de dados: Distribuição uniforme entre partições (nenhuma partição única com 90% de dados)
- Alinhamento de manutenção: Corresponde a padrões de arquivamento/purga (colunas de data para arquivamento baseado no tempo)
- Estabilidade: O valor não muda após INSERT (evite particionar em colunas atualizáveis)
Compreender a partição de intervalos
A partição por intervalos divide os dados com base em intervalos de valores — mais frequentemente datas. Cada partição contém um intervalo específico (dados de janeiro, dados de fevereiro, etc.). Esta é a estratégia mais utilizada.
Aqui é onde a partição por intervalos funciona melhor:
- Dados de séries temporais (ordens, registos, transações)
- Dados sequenciais (números de fatura, IDs de encomenda)
- Faixas numéricas (faixas salariais, escalões de preço)
A tabela seguinte mostra padrões comuns de partição:
| Padrão | Quando usar |
|---|---|
| Diariamente | Sistemas de alto volume, retenção curta |
| Weekly | Volume médio, retenção de 6 a 12 meses |
| Mensalmente | O mais comum equilibra o número e o tamanho das partições |
| Trimestral | Retenção de volume reduzido ao longo de vários anos |
| Anualmente | Cenários de arquivo, dados históricos de longo prazo |
Por exemplo, uma plataforma de comércio eletrónico que particiona encomendas mensalmente permite que consultas do mês atual atinjam uma partição, relatórios trimestrais acedam a 3 partições e a análise de final de ano utilize 12 partições, eliminando anos mais antigos.
Pode criar partições de intervalo definindo limites na função de partição:
-- 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);
Partição por valores categóricos
Podes usar RANGE partição com string ou valores categóricos como regiões. A função de partição atribui valores com base na ordem de ordenação. Esta abordagem funciona para distribuição geográfica, sistemas multitenant ou dados departamentais, onde as consultas frequentemente filtram por categoria.
O seguinte exemplo divide os dados por região:
-- 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);
Implementar particionamento de índice
Quando particionas uma tabela, os índices podem ser alinhados ou não alinhados. Índices alinhados usam o mesmo esquema de partição que a tabela, enquanto índices não alinhados usam partições diferentes ou não têm partição. Por defeito, índices não agrupados em tabelas particionadas herdam o esquema de partição da tabela.
Compreender índices alinhados versus não alinhados
Índices alinhados usam a mesma função de partição que a tabela. Cada partição de índice corresponde a uma partição de tabela, permitindo uma troca rápida de partições, manutenção simplificada e melhor eliminação de partições.
Os índices não alinhados usam particionamento diferente ou nenhum. Não podem usar troca de partições e não são suportados em tabelas com mais de 1.000 partições.
Use índices alinhados quando precisar de troca de partições para arquivamento, quiser reconstruir partições específicas de forma independente, ou quando os padrões de consulta filtrarem a chave de partição.
Por exemplo, imagine uma tabela de Encomendas particionada por OrderDate com um índice não clusterizado no CustomerID. Usar partições alinhadas com o mesmo esquema OrderDate permite arquivar meses antigos trocando partições, reconstruindo os índices atuais de forma independente e eliminando partições antigas sem afetar toda a tabela.
Pode criar índices particionados usando o mesmo esquema de partições da tabela 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);
Gerir operações de partição
Depois de criar tabelas particionadas, precisa de as gerir ao longo do tempo. Operações comuns incluem consultar metadados de partições, adicionar novas partições à medida que os dados crescem e remover partições antigas durante o arquivamento. Estas operações utilizam a $PARTITION função e a ALTER PARTITION FUNCTION instrução.
Consultar informação de partição
Pode visualizar a informação da partição usando a $PARTITION função. Eis um exemplo:
-- 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;
Adicionar nova fronteira de partição
Pode dividir partições para adicionar novos valores de fronteira usando ALTER PARTITION FUNCTION. Eis um exemplo:
-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
SPLIT RANGE ('2024-11-01');
Esta declaração adiciona um novo valor de fronteira (1 de novembro de 2024) à função de partição, dividindo uma partição existente em duas partições. A partição que contém datas de outubro a dezembro passa agora a ser duas partições: uma para outubro e outra para novembro a dezembro.
Arquivar e remover partição
Pode fundir partições para arquivar dados antigos usando ALTER PARTITION FUNCTION com MERGE RANGE. Eis um exemplo:
-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
MERGE RANGE ('2023-12-31');
Aplicar as melhores práticas de partição
Seguir as melhores práticas ajuda-o a evitar erros comuns de particionamento que são difíceis de corrigir após a implementação:
- Alinhar índices com partições de tabelas: Usar o mesmo esquema de partições para tabelas e índices para permitir a troca e manutenção de partições
- Monitorizar a distribuição dos dados: Verificar regularmente as estatísticas das partições para identificar partições desequilibradas e verificar a eliminação das partições
- Automatizar a gestão de partições: Agendar trabalhos para adicionar novas partições antes de atingirem os limites e arquivar partições antigas
- Evite o excesso de particionamento: Visa milhões de linhas por partição, não milhares—um número excessivo de partições cria sobrecarga
- Incluir a chave de partição na chave primária: Necessária para alinhamento de índices agrupados no esquema de partição
A partição requer um planeamento cuidadoso. A chave de partição e o alinhamento do índice que escolhes determinam se ganhas desempenho ou se crias complexidade. Quando implementada corretamente, a partição transforma a gestão de grandes tabelas através de consultas mais rápidas, arquivamento eficiente e manutenção simplificada.