Partilhar via

Columnstore indexes - Diretrizes de design

  • Artigo

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

Recomendações de alto nível para a criação de índices columnstore. Algumas boas decisões de design ajudam você a alcançar a alta compactação de dados e o desempenho de consulta que os índices columnstore foram projetados para fornecer.

Pré-requisitos

Este artigo pressupõe que o leitor esteja familiarizado com a arquitetura e a terminologia de armazenamento em coluna. Para obter mais informações, consulte Columnstore indexes: Overview e Columnstore Index Architecture.

Conheça os seus requisitos de dados

Antes de criar um índice columnstore, entenda o máximo possível sobre seus requisitos de dados. Por exemplo, pense nas respostas a estas perguntas:

  • Qual é o tamanho da minha mesa?
  • As minhas consultas executam principalmente análises que varrem grandes intervalos de valores? Os índices Columnstore são concebidos para funcionar eficientemente em varreduras de largo alcance, em vez de pesquisar por valores específicos.
  • Minha carga de trabalho executa muitas atualizações e exclusões? Os índices Columnstore funcionam bem quando os dados são estáveis. As consultas devem atualizar e excluir menos de 10% das linhas.
  • Tenho tabelas de factos e dimensões para um armazém de dados?
  • Preciso executar análises em uma carga de trabalho transacional? Em caso afirmativo, consulte as diretrizes de design do columnstore para análise operacional em tempo real.

Talvez você não precise de um índice columnstore. As tabelas de armazenamento de linhas (ou de árvore B) com heaps ou índices agrupados têm melhor desempenho em consultas que procuram um valor específico nos dados ou em consultas dentro de um pequeno intervalo de valores. Use índices de armazenamento de linha com cargas de trabalho transacionais, pois eles tendem a exigir principalmente pesquisas de tabela em vez de varreduras de tabela de grande alcance.

Escolha o melhor índice de armazenamento em colunas para as suas necessidades

Um índice columnstore é clusterizado ou não clusterizado. Um índice columnstore clusterizado pode incluir um ou mais índices B-tree não clusterizados. Os índices Columnstore são fáceis de experimentar. Se criar uma tabela como um índice columnstore, poderá facilmente convertê-la novamente numa tabela rowstore eliminando o índice columnstore.

Aqui está um resumo das opções e recomendações.

Opção "Columnstore" Recomendações para quando usar Compressão
Índice de armazenamento em colunas agrupado Utilização para:

1) Carga de trabalho de armazém de dados tradicional com um esquema de estrela ou floco de neve

2) Cargas de trabalho de Internet das Coisas (IOT) que inserem grandes volumes de dados com atualizações e exclusões mínimas.		 Média de 10 vezes
Índice columnstore ordenado e agrupado Utilize quando um índice columnstore clusterizado é consultado por meio de uma única coluna com predicado ordenado ou um conjunto de colunas. Esta orientação é semelhante à escolha da(s) coluna(s) de chave para um índice clusterizado de armazenamento de linhas, embora os grupos de linhas subjacentes compactados se comportem de forma diferente. Para obter mais informações, consulte CREATE COLUMNSTORE INDEX e Ajuste de desempenho com índices columnstore agrupados ordenados. Média de 10x
Índices de árvore B não agrupados num índice columnstore clusterizado Use para:

1. Imponha restrições de chave primária e chave estrangeira em um índice columnstore clusterizado.

2. Acelere as consultas que procuram valores específicos ou pequenos intervalos de valores.

3. Acelere as atualizações e exclusões de linhas específicas.		 10x, em média, mais algum armazenamento adicional para os NCIs.
índice columnstore não clusterizado em um heap baseado em disco ou índice de árvore B Utilização para:

1) Uma carga de trabalho OLTP que tem algumas consultas de análise. Você pode descartar índices de árvore B criados para análise e substituí-los por um índice columnstore não clusterizado.

2) Muitas cargas de trabalho OLTP tradicionais realizam operações ETL (Extract Transform and Load) para mover dados para um data warehouse separado. Você pode eliminar o ETL e um data warehouse separado criando um índice columnstore não clusterizado em algumas das tabelas OLTP.		 O NCCI é um índice adicional que requer, em média, 10% mais armazenamento.
índice Columnstore em uma tabela na memória Mesmas recomendações que o índice columnstore não clusterizado em uma tabela baseada em disco, exceto que a tabela base é uma tabela na memória. O índice Columnstore é um índice adicional.

Usar um índice de armazenamento de colunas clusterizado para grandes tabelas de armazém de dados.

O índice columnstore clusterizado é mais do que um índice; é o principal armazenamento de tabela. Esta tecnologia alcança alta compactação de dados e uma melhoria significativa no desempenho de consultas em grandes tabelas de fatos e dimensões de data warehouse. Os índices columnstore agrupados são mais adequados para consultas de análise em vez de consultas transacionais, uma vez que as consultas de análise tendem a executar operações em grandes intervalos de valores em vez de procurar valores específicos.

Considere o uso de um índice columnstore clusterizado quando:

  • Cada partição tem pelo menos um milhão de linhas. Os índices Columnstore têm grupos de linhas dentro de cada partição. Se a tabela for demasiado pequena para preencher um grupo de linhas dentro de cada partição, não se obterão os benefícios da compressão de armazenamento em coluna e do desempenho da consulta.
  • As consultas executam principalmente análises em intervalos de valores. Por exemplo, para encontrar o valor médio de uma coluna, a consulta precisa verificar todos os valores de coluna. Em seguida, agrega os valores somando-os para determinar a média.
  • A maioria das inserções está em grandes volumes de dados com atualizações e eliminações mínimas. Muitas cargas de trabalho, como a Internet das Coisas (IOT), inserem grandes volumes de dados com atualizações e exclusões mínimas. Essas cargas de trabalho podem beneficiar dos ganhos de desempenho em compressão e consulta proporcionados pela utilização de um índice columnstore clusterizado.

Não use um índice columnstore clusterizado quando:

  • A tabela requer o tipo de dados varchar(max), nvarchar(max)ou varbinary(max). Ou crie o índice columnstore para que ele não inclua essas colunas (Aplica-se a: SQL Server 2016 (13.x) e versões anteriores).
  • Os dados da tabela não são permanentes. Considere o uso de um heap ou tabela temporária caso necessite de armazenar e eliminar dados rapidamente.
  • A tabela tem menos de um milhão de linhas por partição.
  • Mais de 10% das operações na tabela são atualizações e exclusões. Um grande número de atualizações e exclusões causa fragmentação. A fragmentação afeta as taxas de compressão e o desempenho das consultas até que se execute uma operação chamada reorganizar, que força todos os dados no armazenamento em colunas e remove a fragmentação. Para obter mais informações, consulte Minimizando a fragmentação do índice no columnstore index.

Para obter mais informações, consulte índices Columnstore em data warehousing.

Utilize um índice columnstore clusterizado ordenado para tabelas volumosas de data warehouse

Para obter informações sobre a disponibilidade de índices columnstore ordenados, consulte Columnstore indexes: Overview.

Considere o uso de um índice columnstore clusterizado ordenado nos seguintes cenários:

  • Quando os dados são relativamente estáticos (sem gravações e exclusões frequentes) e a chave de índice columnstore clusterizada ordenada é estática, os índices columnstore agrupados ordenados podem fornecer vantagens significativas de desempenho em relação aos índices columnstore clusterizados não ordenados ou índices clusterizados rowstore para cargas de trabalho analíticas.
  • Quanto mais valores distintos na primeira coluna da chave de índice columnstore clusterizada ordenada, melhores podem ser os ganhos de desempenho para índices columnstore agrupados ordenados. Isso se deve à melhor eliminação de segmentos para dados de cadeia de caracteres. Para obter mais informações, consulte a eliminação de segmento .
  • Escolha uma chave de índice columnstore clusterizada ordenada que será consultada com frequência e pode se beneficiar da eliminação de segmento, especialmente a primeira coluna da chave. Os ganhos de desempenho devido à eliminação de segmentos em outras colunas da tabela serão menos previsíveis.
  • Casos de uso em que apenas os dados analíticos mais recentes devem ser consultados, como, por exemplo, os últimos 15 segundos, onde índices columnstore agrupados e ordenados podem fornecer eliminação de segmento para os dados mais antigos. A primeira coluna na chave dos dados columnstore agrupados ordenados deve ser os dados de data/hora, como uma data/hora inserida ou criada. A eliminação de segmento seria mais eficaz em um índice columnstore clusterizado ordenado do que em um índice columnstore clusterizado não ordenado.
  • Considere índices columnstore agrupados ordenados em tabelas que contêm chaves com dados GUID, onde agora é possível usar o tipo de dados uniqueidentifier para a eliminação de segmentos .

Um índice columnstore clusterizado ordenado poderá não ser tão eficaz nestes cenários:

  • Semelhante a outros índices columnstore, uma alta taxa de atividade de inserção poderá criar E/S de armazenamento excessivas.
  • Para cargas de trabalho que envolvem muitas operações de escrita, a qualidade da eliminação de segmento será reduzida ao longo do tempo devido à manutenção do grupo de linhas pelo transferidor de tuples. Isso pode ser atenuado pela manutenção regular do índice columnstore com ALTER INDEX REORGANIZE.

Para pesquisas eficientes em tabelas, adicione índices não-agrupados de B-tree.

A partir do SQL Server 2016 (13.x), você pode criar índices de árvore B ou rowstore não clusterizados como índices secundários em um índice columnstore clusterizado. O índice B-tree não clusterizado é atualizado conforme ocorrem alterações no índice columnstore. Este é um recurso poderoso que você pode usar a seu favor.

Usando o índice secundário de árvore B, pode-se consultar linhas específicas de forma eficiente sem verificar todas as linhas. Outras opções também ficam disponíveis. Por exemplo, você pode impor uma restrição de chave primária ou estrangeira usando uma restrição UNIQUE no índice da árvore B. Como um valor não exclusivo não será inserido no índice da árvore B, o SQL Server não pode inserir o valor no columnstore.

Considere a utilização de um índice B-tree num índice columnstore para:

  • Execute consultas que pesquisem valores específicos ou pequenos intervalos de valores.
  • Imponha uma restrição, como uma restrição de chave primária ou de chave estrangeira.
  • Execute operações de atualização e exclusão com eficiência. O índice da árvore B é capaz de localizar rapidamente as linhas específicas para atualizações e exclusões sem verificar a tabela completa ou a partição de uma tabela.
  • Você tem armazenamento adicional disponível para armazenar o índice B-tree.

Usar um índice columnstore não clusterizado para análise em tempo real

A partir do SQL Server 2016 (13.x), pode-se ter um índice columnstore não-clusterizado em uma tabela rowstore baseada em disco ou em uma tabela OLTP na memória. Isso torna possível executar a análise em tempo real em uma tabela transacional. Enquanto as transações estão ocorrendo na tabela subjacente, você pode executar análises no índice columnstore. Como uma tabela gerencia ambos os índices, as alterações estão disponíveis em tempo real para os índices rowstore e columnstore.

Como um índice columnstore alcança uma compactação de dados 10x melhor do que um índice rowstore, ele só precisa de uma pequena quantidade de armazenamento extra. Por exemplo, se a tabela rowstore compactada tiver 20 GB, o índice columnstore poderá exigir 2 GB adicionais. O espaço adicional necessário também depende do número de colunas no índice columnstore não clusterizado.

Considere o uso de um índice columnstore não clusterizado para:

  • Executar análises em tempo real numa tabela rowstore transacional. Você pode substituir índices de árvore B existentes projetados para análise por um índice columnstore não clusterizado.

  • Elimine a necessidade de um armazém de dados separado. Tradicionalmente, as empresas executam transações em uma tabela rowstore e, em seguida, carregam os dados em um armazém de dados separado para executar análises. Para muitas cargas de trabalho, pode-se eliminar o processo de carregamento e o data warehouse separado criando um índice columnstore não-clusterizado em tabelas transacionais.

O SQL Server 2016 (13.x) oferece várias estratégias para tornar esse cenário eficiente. É muito fácil experimentá-lo, pois você pode habilitar um índice columnstore não clusterizado sem alterações no seu aplicativo OLTP.

Para adicionar recursos de processamento adicionais, você pode executar a análise em um secundário legível. O uso de um secundário legível separa o processamento da carga de trabalho transacional da carga de trabalho de análise.

Para obter mais informações, consulte Introdução ao Columnstore para análise operacional em tempo real

Para obter mais informações sobre como escolher o melhor índice columnstore, consulte o blog de Sunil Agarwal Qual índice columnstore é adequado para minha carga de trabalho?.

Usar partições de tabela para gerenciamento de dados e desempenho de consultas

Os índices Columnstore suportam particionamento, que é uma boa maneira de gerenciar e arquivar dados. O particionamento também melhora o desempenho da consulta, limitando as operações a uma ou mais partições.

Use partições para facilitar o gerenciamento de dados

Para tabelas grandes, a única maneira prática de gerenciar intervalos de dados é usando partições. As vantagens das partições em tabelas rowstore também se aplicam aos índices columnstore.

Por exemplo, as tabelas rowstore e columnstore usam partições para:

  • Controle o tamanho dos backups incrementais. Você pode fazer backup de partições para separar grupos de arquivos e, em seguida, marcá-los como somente leitura. Ao fazer isso, backups futuros ignorarão os grupos de arquivos somente leitura.
  • Economize custos de armazenamento movendo uma partição mais antiga para um armazenamento mais barato. Por exemplo, você pode usar a comutação de partição para mover uma partição para um local de armazenamento mais barato.
  • Execute operações de forma eficiente limitando-as a uma partição. Por exemplo, você pode direcionar apenas as partições fragmentadas para manutenção de índice.

Além disso, com um índice columnstore, você usa o particionamento para:

  • Poupe 30% adicionais em custos de armazenamento. Pode comprimir as partições mais antigas com as opções de compressão COLUMNSTORE_ARCHIVE. Os dados serão mais lentos para o desempenho da consulta, o que é aceitável se a partição for consultada com pouca frequência.

Usar partições para melhorar o desempenho da consulta

Usando partições, você pode limitar suas consultas para verificar apenas partições específicas, o que limita o número de linhas a serem verificadas. Por exemplo, se o índice for particionado por ano e a consulta estiver analisando dados do ano passado, ele só precisará digitalizar os dados em uma partição.

Usar menos partições para um índice columnstore

A menos que você tenha um tamanho de dados grande o suficiente, um índice columnstore tem melhor desempenho com menos partições do que o que você pode usar para um índice de armazenamento de linhas. Se não tiveres pelo menos um milhão de linhas por partição, a maioria das tuas linhas pode ir para o armazenamento delta onde elas não recebem o benefício de desempenho da compactação em armazenamento em colunas. Por exemplo, se você carregar um milhão de linhas em uma tabela com 10 partições e cada partição receber 100.000 linhas, todas as linhas irão para grupos de linhas delta.

Exemplo:

  • Carregue 1.000.000 linhas em uma partição ou em uma tabela não particionada. Você obtém um grupo de linhas compactado com 1.000.000 linhas. Isso é ótimo para alta compactação de dados e rápido desempenho de consulta.
  • Carregue 1.000.000 linhas uniformemente em 10 partições. Cada partição obtém 100.000 linhas, o que é menor do que o limiar mínimo para compressão de columnstore. Como resultado, o índice columnstore pode ter 10 grupos de linhas delta com 100.000 linhas em cada. Há maneiras de forçar os grupos de linhas delta no columnstore. No entanto, se essas forem as únicas linhas no índice columnstore, os grupos de linhas compactados serão muito pequenos para melhor compactação e desempenho de consulta.

Para obter mais informações sobre particionamento, consulte a postagem do blog de Sunil Agarwal, Devo particionar meu índice columnstore?.

Escolha o método de compressão de dados apropriado

O índice columnstore oferece duas opções para compactação de dados: compactação columnstore e compactação de arquivo. Você pode escolher a opção de compactação ao criar o índice, ou mudá-la mais tarde com ALTER INDEX ... RECONSTRUIR.

Usar a compactação columnstore para obter o melhor desempenho de consulta

A compactação Columnstore normalmente alcança taxas de compactação 10x melhores em relação aos índices rowstore. É o método de compressão padrão para índices columnstore e permite um desempenho de consulta rápido.

Use a compactação de arquivo para melhor compactação de dados

A compactação de arquivo foi projetada para máxima compressão quando o desempenho das consultas não é prioritário. Esta técnica alcança taxas de compressão de dados mais altas do que a compressão em colunas, mas tem um preço. Demora mais tempo a comprimir e descomprimir os dados, pelo que não é adequado para um desempenho de consulta rápido.

Usar otimizações ao converter uma tabela rowstore em um índice columnstore

Se seus dados já estiverem em uma tabela de armazenamento de linhas, você poderá usar CREATE COLUMNSTORE INDEX para converter a tabela em um índice columnstore clusterizado. Há algumas otimizações que melhorarão o desempenho da consulta após a conversão da tabela, descritas a seguir.

Use MAXDOP para melhorar a qualidade dos grupos de linhas

Você pode configurar o número máximo de processadores para converter um índice de heap ou árvore B clusterizada em um índice columnstore. Para configurar os processadores, use a opção de grau máximo de paralelismo (MAXDOP).

Se você tiver grandes quantidades de dados, o MAXDOP 1 provavelmente será muito lento. Aumentar MAXDOP para 4 funciona bem. Se isso resultar em alguns grupos de linhas que não têm o número ideal de linhas, você pode executar ALTER INDEX REORGANIZE para mesclá-los em segundo plano.

Manter a ordem ordenada de um índice de árvore B

Como o índice B-tree já armazena linhas em uma ordem ordenada, preservar essa ordem quando as linhas são compactadas no índice columnstore pode melhorar o desempenho da consulta.

O índice columnstore não classifica os dados, mas usa metadados para controlar os valores mínimo e máximo de cada segmento de coluna em cada grupo de linhas. Ao procurar um intervalo de valores, ele pode calcular rapidamente quando ignorar o grupo de linhas. Quando os dados são ordenados, mais grupos de linhas podem ser ignorados.

Para preservar a ordem ordenada durante a conversão:

  • Utilize CREATE COLUMNSTORE INDEX com a cláusula DROP_EXISTING. Isso também preserva o nome do índice. Se você tiver scripts que já usam o nome do índice rowstore, não precisará atualizá-los.

    Este exemplo converte um índice de armazenamento de linhas clusterizado em uma tabela chamada MyFactTable em um índice columnstore clusterizado. O nome do índice, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, permanece o mesmo.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
ON MyFactTable
WITH (DROP_EXISTING = ON);

Compreender a eliminação de segmentos

Cada grupo de linhas contém um segmento de coluna para cada coluna da tabela. Cada segmento de coluna é compactado em conjunto e armazenado em mídia física.

Há metadados com cada segmento para permitir a eliminação rápida de segmentos sem lê-los. 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. Para obter mais informações, consulte a eliminação do segmento .

Essas são tarefas para criar e manter os índices columnstore.

Tarefa Artigos de referência Observações
Crie uma tabela como columnstore. CRIAR TABELA (Transact-SQL) A partir do SQL Server 2016 (13.x), você pode criar a tabela como um índice columnstore clusterizado. Não é necessário primeiro criar uma tabela rowstore e, em seguida, convertê-la em columnstore.
Crie uma tabela de memória com um índice columnstore. CRIAR TABELA (Transact-SQL) A partir do SQL Server 2016 (13.x), você pode criar uma tabela com otimização de memória com um índice columnstore. O índice columnstore também pode ser adicionado após a criação da tabela, usando a sintaxe ALTER TABLE ADD INDEX.
Converter uma tabela de armazenamento em linhas para armazenamento em colunas. Criar Columnstore Index (Transact-SQL) Converta uma pilha ou árvore B existente em um columnstore. Os exemplos mostram como lidar com índices existentes e também o nome do índice ao executar essa conversão.
Converter uma tabela columnstore em um rowstore. CRIAR ÍNDICE CLUSTERIZADO (Transact-SQL) ou Converter uma tabela columnstore de volta para um heap rowstore Normalmente, essa conversão não é necessária, mas pode haver momentos em que você precisa converter. Os exemplos mostram como converter um columnstore em um heap ou índice clusterizado.
Crie um índice columnstore em uma tabela rowstore. CRIAR ÍNDICE COLUMNSTORE (Transact-SQL) Uma tabela rowstore pode ter um único índice columnstore. A partir do SQL Server 2016 (13.x), o índice columnstore pode ter uma condição filtrada. Os exemplos mostram a sintaxe básica.
Crie índices de desempenho para análise operacional. Introdução ao Columnstore para análise operacional em tempo real Descreve como criar índices complementares de columnstore e de árvore B para que as consultas OLTP utilizem índices de árvore B e as consultas de análise empreguem índices columnstore.
Crie índices columnstore de alto desempenho para armazenamento de dados. Índices de armazenamento em colunas em armazenagem de dados Descreve como usar índices B-tree em tabelas columnstore para criar consultas de alto desempenho em armazéns de dados.
Utilize um índice B-tree para impor uma restrição de chave primária num índice columnstore. Índices de armazenamento em colunas no armazenamento de dados Mostra como combinar índices B-tree e columnstore para impor restrições de chave primária no índice columnstore.
Remover um índice columnstore ÍNDICE DE QUEDA (Transact-SQL) Remover um índice de columnstore utiliza a sintaxe padrão DROP INDEX que os índices B-tree usam. Eliminar um índice columnstore clusterizado converterá a tabela columnstore numa heap.
Excluir uma linha de um índice columnstore APAGAR (Transact-SQL) Use DELETE (Transact-SQL) para excluir uma linha.

columnstore linha: o SQL Server marca a linha como excluída logicamente, mas não recupera o armazenamento físico da linha até que o índice seja reconstruído.

deltastore linha: o SQL Server exclui a linha, tanto lógica como fisicamente.
Atualizar uma linha no índice columnstore Atualização (Transact-SQL) Use UPDATE (Transact-SQL) para atualizar uma linha.

linha columnstore: o SQL Server marca a linha como excluída logicamente e insere a linha atualizada no deltastore.

deltastore linha: o SQL Server atualiza a linha no deltastore.
Força todas as linhas no deltastore a irem para o columnstore. ALTER INDEX (Transact-SQL) ... RECONSTRUIR

Otimize a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos		 ALTER INDEX com a opção REBUILD força todas as linhas a irem para o armazenamento em colunas.
Desfragmentar um índice de armazém de colunas ALTERAR ÍNDICE (Transact-SQL) ALTER INDEX ... REORGANIZE desfragmenta os índices columnstore online.
Mesclar tabelas com índices columnstore. MERGE (Transact-SQL)

Conteúdo relacionado

Para criar um índice columnstore vazio para:

Para mais informações sobre como converter um heap de armazenamento em linha ou índice B-tree existente num índice columnstore clusterizado, ou para criar um índice columnstore não clusterizado, consulte CREATE COLUMNSTORE INDEX (Transact-SQL).