Partilhar via

Índices de columnstore - Desempenho de consultas

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric

Este artigo inclui recomendações para alcançar o desempenho rápido da consulta com índices columnstore.

Os índices Columnstore podem alcançar um desempenho até 100 vezes melhor em tarefas de análise e armazenamento de dados, e até 10 vezes melhor compactação de dados do que os índices tradicionais linhares. Essas recomendações ajudam suas consultas a alcançar o desempenho de consulta rápido que os índices columnstore foram projetados para fornecer.

Recomendações para melhorar o desempenho da consulta

Aqui estão algumas recomendações para alcançar os benefícios que os índices columnstore de alto desempenho foram projetados para proporcionar.

1. Organize os dados para eliminar um maior número de grupos de linhas numa verificação completa da tabela

  • Escolha cuidadosamente a ordem de inserção. No caso comum no data warehouse tradicional, os dados são realmente inseridos em ordem de tempo e a análise é feita na dimensão temporal. Por exemplo, analisando as vendas por trimestre. Para esse tipo de carga de trabalho, a eliminação do grupo de linhas acontece automaticamente. No SQL Server 2016 (13.x), você pode descobrir o número de grupos de linhas ignorados como parte do processamento de consultas.

  • Use um índice agrupado de rowstore. Se o predicado de consulta comum estiver em uma coluna (por exemplo, C1) não relacionada à ordem de inserção, crie um índice clusterizado de armazenamento de linha na coluna C1. Em seguida, elimine o índice clusterizado rowstore e crie um índice columnstore clusterizado. Se você criar o índice columnstore clusterizado explicitamente usando MAXDOP = 1, o índice columnstore clusterizado resultante será perfeitamente ordenado na coluna C1. Se você especificar MAXDOP = 8, verá sobreposição de valores em oito grupos de linhas. Para um índice columnstore não clusterizado (NCCI), se a tabela tiver um índice clusterizado de rowstore, as linhas já estão ordenadas pela chave do índice clusterizado. Nesse caso, o índice columnstore não clusterizado também é ordenado automaticamente. Um índice columnstore não mantém inerentemente a ordem das linhas. À medida que novas linhas são inseridas ou linhas mais antigas são atualizadas, talvez seja necessário repetir o processo, pois o desempenho da consulta de análise pode se deteriorar.

  • Implemente o particionamento de tabelas. Você pode particionar o índice columnstore e, em seguida, usar a eliminação de partição para reduzir o número de grupos de linhas a serem analisados. Por exemplo, uma tabela de fatos armazena compras feitas por clientes. Um padrão comum de pesquisa é localizar as compras realizadas trimestralmente por customer. Nesse caso, combine-se a coluna de ordem de inserção com o particionamento na coluna customer. Cada partição contém linhas para cada customer, ordenadas após a inserção. Além disso, considere usar o particionamento de tabela se houver necessidade de remover dados mais antigos do columnstore. Trocar e truncar partições que não são necessárias é uma estratégia eficiente para excluir dados sem gerar fragmentação.

  • Evite excluir grandes quantidades de dados. Remover linhas compactadas de um grupo de linhas não é uma operação síncrona. Seria caro descompactar um grupo de linhas, excluir a linha e, em seguida, recompactá-la. Portanto, quando você exclui dados de grupos de linhas compactados, esses grupos de linhas ainda são verificados, mesmo que retornem menos linhas. Se o número de linhas excluídas para vários grupos de linhas for grande o suficiente para ser mesclado em menos grupos de linhas, a reorganização do columnstore aumentará a qualidade do índice e o desempenho da consulta melhorará. Se o processo de exclusão de dados geralmente esvazia grupos de linhas inteiros, considere o uso do particionamento de tabela. Alterne as partições que não são mais necessárias e trunce-as, em vez de excluir linhas.

    Observação

    A partir do SQL Server 2019 (15.x), o tuple-mover é ajudado por uma tarefa de mesclagem em segundo plano. Esta tarefa comprime automaticamente grupos de linhas delta OPEN menores que existem há algum tempo, conforme determinado por um limiar interno, ou funde grupos de linhas COMPRESSED dos quais um grande número de linhas foi eliminado. Isso melhora a qualidade do índice columnstore ao longo do tempo. Se for necessário excluir grandes quantidades de dados do índice columnstore, considere dividir essa operação em lotes de exclusão menores ao longo do tempo. O envio em lote permite que a tarefa de mesclagem em segundo plano manipule a tarefa de mesclar grupos de linhas menores e melhora a qualidade do índice. Em seguida, não há necessidade de agendar janelas de manutenção de reorganização de índice após a exclusão de dados. Para obter mais informações sobre conceitos e termos de armazenamento em coluna, consulte Índices de armazenamento em coluna: Visão Geral.

2. Planeje memória suficiente para criar índices columnstore em paralelo

A criação de um índice columnstore é, por padrão, uma operação paralela, a menos que a memória seja restringida. Criar o índice em paralelo requer mais memória do que criar o índice em série. Quando há memória ampla, a criação de um índice columnstore demora cerca de 1,5 vezes mais tempo do que a construção de uma árvore B nas mesmas colunas.

A memória necessária para criar um índice columnstore depende do número de colunas, do número de colunas de cadeia de caracteres, do grau de paralelismo (DOP) e das características dos dados. Por exemplo, se sua tabela tiver menos de um milhão de linhas, o SQL Server usará apenas um thread para criar o índice columnstore.

Se sua tabela tiver mais de um milhão de linhas, mas o SQL Server não puder obter uma concessão de memória grande o suficiente para criar o índice usando MAXDOP, o SQL Server diminuirá automaticamente MAXDOP conforme necessário. Em alguns casos, o DOP deve ser reduzido para um, a fim de construir o índice dentro das restrições da concessão de memória disponível.

Desde o SQL Server 2016 (13.x), a consulta sempre opera em modo de lote. Em versões anteriores, a execução em lote só é usada quando o DOP é maior que um.

Desempenho de Columnstore explicado

Os índices Columnstore alcançam alto desempenho de consulta combinando processamento em lote na memória de alta velocidade com técnicas que reduzem consideravelmente os requisitos de E/S. Como as consultas de análise examinam um grande volume de linhas, elas geralmente são limitadas pelo I/O e, portanto, reduzir o I/O durante a execução da consulta é crucial para o design de índices columnstore. Depois que os dados são lidos na memória, é fundamental reduzir o número de operações na memória.

Os índices Columnstore reduzem o I/O e otimizam as operações na memória por meio de elevada compactação de dados, eliminação de colunas armazenadas, eliminação de grupos de linhas e processamento em lote.

Compressão de dados

Os índices Columnstore alcançam uma compactação de dados até 10 vezes maior do que os índices rowstore. Isso reduz consideravelmente a E/S necessária para executar consultas de análise e, portanto, melhora o desempenho da consulta.

  • Os índices Columnstore leem dados compactados do disco, o que significa que menos bytes de dados precisam ser lidos na memória.

  • Os índices Columnstore armazenam dados em formato compactado na memória, reduzindo a E/S ao evitar a leitura dos mesmos dados na memória. Por exemplo, ao comprimir os dados em 10 vezes, os índices columnstore conseguem armazenar 10 vezes mais dados na memória, em comparação com o armazenamento dos dados em formato não compactado. Com mais dados na memória, é mais provável que o índice columnstore encontre os dados de que precisa na memória sem incorrer em leituras desnecessárias do disco.

  • Os índices Columnstore compactam dados por colunas em vez de por linhas, alcançando altas taxas de compactação e reduzindo o tamanho dos dados armazenados no disco. Cada coluna é comprimida e armazenada de forma independente. Os dados dentro de uma coluna sempre têm o mesmo tipo de dados e tendem a ter valores semelhantes. As técnicas de compactação de dados Columnstore são ótimas para alcançar taxas de compactação mais altas quando os valores são semelhantes.

Por exemplo, uma tabela de fatos armazena endereços de clientes e tem uma coluna para country-region. O número total de valores possíveis é inferior a 200. Alguns desses valores são repetidos muitas vezes. Se a tabela de fatos tiver 100 milhões de linhas, a coluna country-region comprime facilmente e requer pouco armazenamento. A compactação linha a linha não é capaz de tirar partido da semelhança dos valores entre colunas desta forma e deve usar mais bytes para compactar os valores na coluna country-region.

Eliminação da coluna

Os índices Columnstore ignoram a leitura em colunas que não são necessárias para o resultado da consulta. A eliminação de colunas reduz ainda mais a E/S para a execução de consultas e, portanto, melhora o desempenho da consulta.

  • A eliminação de coluna é possível porque os dados são organizados e compactados coluna por coluna. Por outro lado, quando os dados são armazenados linha a linha, os valores de coluna em cada linha são fisicamente armazenados juntos e não podem ser facilmente separados. O Processador de Consultas precisa ler numa linha inteira para recuperar valores de colunas específicos, aumentando a carga de entrada/saída porque dados extra são lidos desnecessariamente na memória.

Por exemplo, se uma tabela tiver 50 colunas e a consulta usar apenas cinco dessas colunas, o índice columnstore buscará apenas as cinco colunas do disco. Ele ignora a leitura nas outras 45 colunas, reduzindo a E/S em mais 90%, supondo que todas as colunas sejam de tamanho semelhante. Se os mesmos dados forem armazenados em um armazenamento de linhas, o processador de consultas precisará ler as 45 colunas restantes.

Eliminação de grupos de linhas

Para verificações completas de tabelas, uma grande porcentagem dos dados geralmente não corresponde aos critérios de predicados de consulta. Usando metadados, o índice columnstore é capaz de ignorar a leitura dos grupos de linhas que não contêm dados necessários para o resultado da consulta, tudo sem entrada/saída real. Essa capacidade, denominada eliminação de grupo de linhas, reduz a E/S para verificações completas de tabelas, melhorando assim o desempenho das consultas.

Quando um índice columnstore precisa executar uma verificação de tabela completa?

A partir do SQL Server 2016 (13.x), você pode criar um ou mais índices regulares de armazenamento de linhas não clusterizados, ou árvore B, em um índice columnstore clusterizado. Os índices de árvore B não agrupados podem acelerar uma consulta que tem um predicado de igualdade ou um predicado com um pequeno intervalo de valores. Para predicados mais complicados, o otimizador de consulta pode escolher uma leitura completa da tabela. Sem a capacidade de ignorar grupos de linhas, uma verificação de tabela completa pode ser demorada, especialmente para tabelas grandes.

Quando é que uma consulta de análise se beneficia da eliminação de grupos de linhas para uma varredura completa da tabela?

Por exemplo, uma empresa de retalho modela os seus dados de vendas usando uma tabela de fatos com um índice agrupado por columnstore. Cada nova venda armazena vários atributos da transação, incluindo a data em que um produto é vendido. Curiosamente, embora os índices columnstore não garantam uma ordem ordenada, as linhas nesta tabela são carregadas em uma ordem ordenada por data. Com o tempo esta tabela cresce. Embora o negócio de varejo possa manter dados de vendas dos últimos 10 anos, uma consulta de análise pode precisar apenas calcular um agregado para o último trimestre. Os índices Columnstore podem eliminar o acesso aos dados dos 39 trimestres anteriores apenas observando os metadados da coluna de data. Esta é uma redução de 97% na quantidade de dados que são lidos na memória e processados.

Quais grupos de linhas são ignorados em uma verificação de tabela completa?

Para determinar quais grupos de linhas devem ser eliminados, o índice columnstore usa metadados para armazenar os valores mínimo e máximo de cada segmento de coluna para cada grupo de linhas. Quando nenhum dos intervalos de segmentos de coluna cumpre os critérios dos predicados da consulta, o grupo de linhas inteiro é ignorado sem executar qualquer E/S real. Isso funciona porque os dados geralmente são carregados em uma ordem classificada. Embora a classificação de linhas não seja garantida, valores de dados semelhantes geralmente estão localizados dentro do mesmo grupo de linhas ou de um grupo de linhas vizinho.

Para obter mais informações sobre grupos de linhas, consulte Columnstore Index Design Guidelines.

Execução em modo de lote

Execução em modo de lote processa linhas em grupos, geralmente até 900 de cada vez, para melhorar a eficiência. Por exemplo, a consulta SELECT SUM(Sales) FROM SalesData calcula o total de vendas da tabela SalesData. No modo de lote, o mecanismo de consulta processa os dados em grupos de 900 linhas. Essa abordagem reduz o custo de acesso a metadados e outros tipos de sobrecarga espalhando-os por todas as linhas de um lote, em vez de incorrer na sobrecarga de cada linha. Além disso, o modo batch funciona com dados compactados quando possível e remove alguns dos operadores de câmbio usados no modo de linha, acelerando significativamente as consultas analíticas.

Nem todos os operadores de execução de consulta podem ser executados no modo de lote. Por exemplo, operações de linguagem de manipulação de dados (DML), como inserir, excluir ou atualizar, são executadas uma linha de cada vez. O operador de modo de lote, como Scan, Join, Aggregate, Sort e outros, pode melhorar o desempenho da consulta. Desde que o índice columnstore foi introduzido no SQL Server 2012 (11.x), há um esforço contínuo para expandir os operadores que podem ser executados no modo de lote. A tabela a seguir mostra os operadores que são executados em modo de lote de acordo com a versão do produto.

Operadores do modo de lote Quando utilizado SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) e Banco de Dados SQL1 Observações
Operações DML (inserir, excluir, atualizar, mesclar) Não Não Não DML não é uma operação de modo de lote porque não é paralela. Mesmo quando habilitamos o processamento em lote no modo serial, não vemos ganhos significativos ao permitir que o DML seja processado no modo em lote.
varredura de índice armazenado em colunas Digitalizar Não disponível Sim Sim Para índices columnstore, podemos enviar o predicado para o nó SCAN.
Scan de índice columnstore (não agrupado) ESCANEAMENTO Sim Sim Sim Sim
busca de índice Não disponível Não disponível Não Realizamos uma operação de busca através de um índice B-tree não clusterizado em modo de linha.
calcular escalar Expressão que é avaliada como um valor escalar. Sim Sim Sim Como todos os operadores de modo de lote, existem algumas restrições sobre o tipo de dados.
concatenação UNION e UNION ALL Não Sim Sim
filtrar Aplicação de predicados Sim Sim Sim
correspondência de hash Funções agregadas baseadas em hash, junção de hash externa, junção de hash direita, junção de hash esquerda, junção interna direita, junção interna esquerda Sim Sim Sim Restrições para agregação: sem min/max para strings. As funções de agregação disponíveis são soma/contagem/média/min/máx.
Restrições para junção: nenhuma junção de tipo incompatível em tipos não inteiros.
mesclar ingresso Não Não Não
consultas multithreaded Sim Sim Sim
loops aninhados Não Não Não
consultas de execução única com MAXDOP 1 Não Não Sim
Consultas de thread único com um plano de consulta serial Não Não Sim
ordenar Cláusula ORDER BY no SCAN com índice columnstore. Não Não Sim
Classificação superior Não Não Sim
agregados de janela Não disponível Não disponível Sim Novo operador do SQL Server 2016 (13.x).

1 Aplica-se ao SQL Server 2016 (13.x), às camadas Premium do Banco de Dados SQL, às camadas Standard - S3 e superiores, e a todas as camadas vCore e ao Analytics Platform System (PDW)

Para obter mais informações, consulte o Guia de Arquitetura de Processamento de Consultas.

Pushdown agregado

Um caminho de execução normal para computação agregada para buscar as linhas qualificadas do nó SCAN e agregar os valores no Modo de lote. Embora isso ofereça um bom desempenho, a partir do SQL Server 2016 (13.x), a operação agregada pode ser transferida para o nó SCAN. O pushdown agregado melhora o desempenho de cálculos agregados por ordens de magnitude em cima da execução do Modo de Lote, desde que as seguintes condições sejam atendidas:

  • Os agregados são MIN, MAX, SUM, COUNT e COUNT(*).
  • O operador de agregação deve estar no topo do nó SCAN ou do nó SCAN que inclua GROUP BY.
  • Este agregado não é um agregado distinto.
  • A coluna agregada não é uma coluna de cadeia de caracteres.
  • A coluna agregada não é uma coluna virtual.
  • O tipo de dados de entrada e saída deve ser um dos seguintes e deve caber dentro de 64 bits:
    • tinyint, int, bigint, smallint, bit
    • pequenos valores monetários, dinheiro, decimal e numérico com precisão <= 18
    • data pequena, data, data e hora, data e hora 2, hora

Por exemplo, o pushdown agregado é feito em ambas as consultas a seguir:

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Otimização de predicado em cadeias de caracteres

Ao projetar um esquema de armazém de dados, a modelagem de esquema recomendada é usar esquema em estrela ou esquema de floco de neve que consiste em uma ou mais tabelas de fatos e muitas tabelas de dimensão.

Dica

A tabela de fatos armazena as medidas ou transações de negócios, e a tabela de dimensões armazena as dimensões nas quais os fatos precisam ser analisados. Para obter mais informações sobre modelagem dimensional, consulte Modelagem dimensional no Microsoft Fabric.

Por exemplo, um fato pode ser um registro que representa uma venda de um determinado produto em uma região específica, enquanto a dimensão representa um conjunto de regiões, produtos e assim por diante. As tabelas de factos e dimensões estão ligadas através de uma relação de chave primária/externa. As consultas de análise mais usadas unem uma ou mais tabelas de dimensão com a tabela de fatos.

Consideremos uma tabela de dimensões Products. Uma chave primária típica é ProductCode, comumente representada como string. Para o desempenho de consultas, é uma prática recomendada criar uma chave substituta, normalmente uma coluna de inteiro, para fazer referência à linha na tabela de dimensões da tabela de fatos.

O índice columnstore executa consultas analíticas com junções e predicados envolvendo chaves numéricas ou baseadas em números inteiros de forma eficiente. O SQL Server 2016 (13.x) melhorou significativamente o desempenho de consultas de análise com colunas baseadas em cadeia de caracteres, empurrando para baixo os predicados com colunas de cadeia de caracteres para o nó SCAN.

O pushdown do predicado de string utiliza o dicionário primário/secundário criado para as colunas para melhorar o desempenho das consultas. Por exemplo, considere um segmento de coluna de cadeia de caracteres dentro de um grupo de linhas que consiste em 100 valores de cadeia de caracteres distintos. Cada valor de cadeia de caracteres distinto é referenciado 10.000 vezes em média, assumindo um milhão de linhas. Na execução da consulta, a técnica de pushdown de predicado de cadeia de caracteres calcula o predicado em relação aos valores no dicionário. Se o predicado se qualificar, todas as linhas referentes ao valor do dicionário serão automaticamente qualificadas. Isso melhora o desempenho de duas maneiras:

  • Somente a linha qualificada é retornada, reduzindo o número de linhas que precisam fluir para fora do nó de verificação.
  • O número de comparações de cadeia de caracteres é reduzido. Neste exemplo, apenas 100 comparações de cadeia de caracteres são necessárias contra 1 milhão de comparações. Existem algumas limitações:
    • Sem otimização de filtragem de predicado de string para grupos de linhas delta. Não há dicionário para colunas em grupos de linhas delta.
    • Nenhuma filtragem de predicados antecipada se o dicionário exceder 64 KB de entradas.
    • Não está disponível suporte para expressões que avaliam valores nulos.

Eliminação de segmentos

As opções de tipo de dados podem ter um impacto significativo no desempenho da consulta com base em predicados de filtro comuns para consultas no índice columnstore.

Nos dados columnstore, os grupos de linhas são formados por segmentos de coluna. Há metadados com cada segmento para permitir a eliminação rápida de segmentos sem lê-los. Essa eliminação de segmento aplica-se aos tipos de dados numéricos, de data e hora, e ao datetimeoffset tipo de dados com escala menor ou igual a dois. A partir do SQL Server 2022 (16.x), os recursos de eliminação de segmento se estendem aos tipos de dados string, binário, guid e ao datetimeoffset tipo de dados para escala maior que dois.

Depois de atualizar para uma versão do SQL Server que ofereça suporte à eliminação de segmento de cadeia de caracteres min/max (SQL Server 2022 (16.x) e posterior), o índice columnstore não se beneficia desse recurso até que seja reconstruído usando ALTER INDEX REBUILD ou CREATE INDEX WITH (DROP_EXISTING = ON).

A eliminação de segmentos não se aplica a tipos de dados LOB, como os comprimentos máximos de tipos de dados.

Atualmente, apenas o SQL Server 2022 (16.x) e posterior suporta a eliminação de grupos de linhas de armazenamento em colunas clusterizados para o prefixo dos predicados LIKE, por exemplo, column LIKE 'string%'. A eliminação de segmentos não é suportada para o uso sem prefixo de LIKE, como column LIKE '%string'.

Os índices columnstore ordenados também se beneficiam da eliminação de segmentos, especialmente para colunas de texto. Em índices columnstore organizados, a eliminação de segmento na primeira coluna da chave de índice é mais eficaz, por estar classificada. Os ganhos de desempenho devido à eliminação de segmentos em outras colunas da tabela são menos previsíveis. Para obter mais informações sobre índices columnstore ordenados, consulte Usar um índice columnstore ordenado para grandes tabelas de data warehouse. Para obter a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice columnstore ordenado.

Usando a opção de conexão de consulta SET STATISTICS IO, você pode visualizar a eliminação de segmentos em ação. Procure por resultados como os seguintes para indicar que ocorreu a eliminação do segmento. Os grupos de linhas são formados por segmentos de coluna, portanto, isso pode indicar a eliminação de segmentos. O seguinte exemplo de saída SET STATISTICS IO de uma consulta, aproximadamente 83% dados foram ignorados pela consulta:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Conteúdo relacionado