Compartilhar via

Índices columnstore: visão geral

  • Artigo

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

Os índices columnstore são o padrão para armazenar e consultar tabelas de fatos em grandes armazéns de dados. Esse índice usa armazenamento de dados baseado em coluna e processamento de consultas para obter até 10 vezes mais desempenho de consulta em seu data warehouse em relação ao armazenamento tradicional orientado por linha. Também é possível obter ganhos de até 10 vezes na compactação de dados sobre o tamanho dos dados descompactados. A partir do SQL Server 2016 (13.x) SP1, os índices columnstore permitem a análise operacional: a capacidade de executar análises de desempenho em tempo real em uma carga de trabalho transacional.

Confira um cenário relacionado:

O que é um índice columnstore?

Um columnstore index é uma tecnologia para armazenamento, recuperação e gerenciamento de dados usando um formato de dados colunar, chamado columnstore.

Termos e conceitos principais

Os termos e conceitos principais a seguir estão associados aos índices columnstore.

Armazenamento em colunas

Um columnstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de coluna.

Rowstore

Um rowstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de linha. Esse formato é o modo tradicional de armazenar dados da tabela relacional. No SQL Server, rowstore refere-se à uma tabela em que o formato de armazenamento de dados subjacente é um heap, um índice clusterizado ou uma tabela com otimização de memória.

Observação

Em discussões sobre índices columnstore, os termos rowstore e columnstore são usados para enfatizar o formato do armazenamento de dados.

Rowgroup

Um rowgroup é um grupo de linhas que são compactadas no formato columnstore ao mesmo tempo. Um rowgroup geralmente contém o número máximo de linhas, que é de 1.048.576 linhas por rowgroup.

Para altas taxas de desempenho e compactação, o índice columnstore divide a tabela em grupos de linhas e depois compacta cada grupo de linhas de forma coluna por coluna. O número de linhas no rowgroup precisa ser grande o suficiente para aprimorar as taxas de compactação e pequeno o suficiente para se beneficiar das operações na memória.

Um rowgroup do qual todos os dados foram excluídos das transições do estado COMPACTADO para MARCA DE EXCLUSÃO e, posteriormente, removidos por um processo em segundo plano denominado motor de tupla. Para mais informações sobre os status de rowgroup, veja sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Dica

Ter muitos grupos pequenos de rowgroups diminui a qualidade do índice columnstore. Até o SQL Server 2017 (14.x), uma operação de reorganização era necessária para mesclar rowgroups COMPRIMIDOS menores, seguindo uma política de limite interno que determina como remover linhas excluídas e combinar os rowgroups comprimidos.
A partir do SQL Server 2019 (15.x), uma tarefa de mesclagem em segundo plano também funciona para mesclar rowgroups COMPACTADO de onde um grande número de linhas foi excluído.
Depois de mesclar rowgroups menores, a qualidade do índice deve melhorar.

Observação

Começando com o SQL Server 2019 (15.x), Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e pools de SQL dedicados no Azure Synapse Analytics, o mover de tupla é ajudado por uma tarefa de mesclagem em segundo plano que compacta automaticamente rowgroups delta ABERTO menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla rowgroups COMPACTADO de onde um grande número de linhas foi excluída. Isso melhora a qualidade do índice columnstore ao longo do tempo.

Segmento de coluna

Um segmento de coluna é uma coluna de dados dentro de um grupo de linhas.

  • Cada rowgroup contém um segmento de coluna para cada coluna na tabela.
  • Cada segmento de coluna é compactado junto e armazenado em uma mídia física.
  • Há metadados com cada segmento para permitir a eliminação rápida de segmentos sem lê-los.

Diagrama lógico do segmento de coluna. Cada coluna tem um segmento de coluna para cada grupo de linhas.

Índice columnstore clusterizado

Um índice columnstore clusterizado é o armazenamento físico da tabela inteira.

Diagrama lógico de um índice columnstore clusterizado. Inclui segmentos de coluna compactados mais linhas no índice, mas não no columnstore.

Para reduzir a fragmentação dos segmentos de coluna e melhorar o desempenho, o índice columnstore pode armazenar alguns dados temporariamente em um índice clusterizado, chamado deltastore, e em uma lista árvore B de IDs para linhas excluídas. As operações de deltastore são realizadas em segundo plano. Para retornar os resultados corretos da consulta, o índice columnstore clusterizado combina os resultados da consulta de columnstore e deltastore.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o Database Engine implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

Rowgroup delta

Um rowgroup delta é um índice de árvore B clusterizado usado somente com índices columnstore. Ele melhora o desempenho e a compactação do columnstore armazenando linhas até que o número de linhas alcance um limite (de 1.048.576 linhas) e depois seja movido para o columnstore.

Quando um rowgroup delta alcança o número máximo de linhas, ele faz a transição do estado ABERTO para FECHADO. Um processo em segundo plano chamado de motor de tupla verifica os rowgroups fechados. Se o processo encontrar um rowgroup fechado, ele compactará e o armazenará no columnstore como um rowgroup COMPACTADO.

Quando um rowgroup delta é compactado, o rowgroup delta existente faz a transição para o estado MARCA DE EXCLUSÃO para ser removido posteriormente pelo mover de tupla quando não há referência a ele.

Para mais informações sobre os status de rowgroup, veja sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Observação

A partir do SQL Server 2019 (15.x), o motor de tupla é ajudado por uma tarefa de mesclagem em segundo plano que compacta automaticamente os rowgroups delta ABERTOS menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla os rowgroups COMPACTADOS dos quais um grande número de linhas foi excluído. Isso melhora a qualidade do índice columnstore ao longo do tempo.

Deltastore

Um índice columnstore pode ter mais de um rowgroup delta. Todos os rowgroups delta são coletivamente chamados de deltastore.

Durante o carregamento em massa grande, a maioria das linhas vai diretamente para o columnstore sem passar pelo deltastore. No fim do carregamento em massa, o número de linhas pode ser muito pouco para atender ao tamanho mínimo de um rowgroup, que é de 102.400 linhas. Como resultado, as linhas finais vão para o deltastore, e não para o columnstore. Para carregamentos em massa pequenos com menos de 102.400 linhas, todas as linhas vão diretamente para o deltastore.

Índice columnstore não clusterizado

Um índice de armazenamento em colunas não clusterizado e um índice de armazenamento em colunas clusterizado funcionam da mesma maneira. A diferença é que um índice não clusterizado é um índice secundário criado em uma tabela rowstore, mas um índice columnstore clusterizado é o armazenamento primário da tabela inteira.

O índice não clusterizado contém uma cópia de parte ou de todas as linhas e colunas na tabela subjacente. O índice é definido como uma ou mais colunas da tabela e tem uma condição opcional que filtra as linhas.

Um índice columnstore não clusterizado permite análises operacionais em tempo real nas quais a carga de trabalho OLTP usa o índice clusterizado subjacente, enquanto as análises são executadas simultaneamente no índice columnstore. Para obter mais informações, veja Introdução ao columnstore para análise operacional em tempo real.

Execução em modo de lote

A execução em modo de lote é um método de processamento de consulta usado para processar várias linhas simultaneamente. A execução em modo de lote é estreitamente integrada ao formato de armazenamento columnstore e otimizada com base nele. A execução do modo em lote às vezes é conhecida como execução baseada em vetor ou vetorizada. Consultas em índices columnstore usam a execução em modo de lote, o que melhora o desempenho de consulta normalmente em duas a quatro vezes. Para saber mais, confira o Guia da arquitetura de processamento de consultas.

Por que devo usar um índice columnstore?

Um índice columnstore pode fornecer um nível muito alto de compactação de dados, geralmente em dez vezes, para reduzir de maneira significativa os custos de armazenamento em data warehouse. Para análises, um índice columnstore oferece um desempenho melhor de ordem de magnitude do que um índice de árvore B. Os índice columnstore são o formato de armazenamento de dados preferencial para cargas de trabalho de análise e armazenamento de dados. A partir do SQL Server 2016 (13.x), você pode usar índices columnstore para análises em tempo real da sua carga de trabalho operacional.

Motivos pelos quais índices columnstore são tão rápidos:

  • colunas armazenam valores do mesmo domínio e normalmente têm valores semelhantes, o que resulta em altas taxas de compactação. Os gargalos de E/S em seu sistema são minimizados ou eliminados, e o volume de memória é reduzido consideravelmente.

  • As altas taxas de compactação melhoram o desempenho da consulta usando um volume de memória menor. Por sua vez, o desempenho da consulta pode ser melhorado porque o SQL Server pode realizar mais operações de consulta e dados na memória.

  • A execução em lote melhora o desempenho de consulta, normalmente em duas a quatro vezes, processando várias linhas simultaneamente.

  • Muitas vezes, as consultas selecionam apenas algumas colunas de uma tabela, o que reduz a E/S total da mídia física.

Quando devo usar um índice columnstore?

Casos de uso recomendados:

Como escolher entre um índice rowstore e um índice columnstore?

Índices rowstore têm melhor desempenho em consultas nos dados, ao procurar um valor específico ou para consultas em um pequeno intervalo de valores. Use índices rowstore com cargas de trabalho transacionais, pois eles tendem a exigir principalmente buscas de tabela em vez de verificações de tabela.

Os índices columnstore oferecem altos ganhos de desempenho para consultas analíticas que examinam grandes quantidades de dados, especialmente em tabelas grandes. Use índices columnstore em cargas de trabalho de armazenamento de dados e análise, especialmente em tabelas de fatos, pois eles tendem a exigir verificações de tabela completas em vez de buscas de tabela.

Índices columnstore clusterizados ordenados melhoram o desempenho de consultas com base em predicados de coluna ordenados. Os índices columnstore ordenados podem aprimorar a eliminação de grupo de linhas, o que pode proporcionar aprimoramentos de desempenho ao ignorar por completo os rowgroups. Para obter mais informações, veja Ajuste de desempenho com índices columnstore ordenados. Para obter a disponibilidade do índice columnstore ordenado, veja Disponibilidade do índice de coluna ordenado.

Posso combinar rowstore e columnstore na mesma tabela?

Sim. Começando com o SQL Server 2016 (13.x), você pode criar um índice columnstore não clusterizado atualizável em uma tabela rowstore. O índice columnstore armazena uma cópia das colunas selecionadas, então você precisa de espaço adicional para esses dados, mas os dados selecionados serão 10 vezes compactados, em média. Você pode executar análises no índice columnstore e transações no índice rowstore ao mesmo tempo. O columnstore é atualizado quando os dados são alterados na tabela rowstore, assim, ambos os índices trabalham com os mesmos dados.

A partir do SQL Server 2016 (13.x), você pode ter um ou mais índices rowstore não clusterizados em um índice columnstore, e executar pesquisas de tabela eficientes no columnstore subjacente. Outras opções também são disponibilizadas. Por exemplo, você pode impor uma restrição de chave primária usando uma restrição UNIQUE na tabela rowstore. Como um valor não exclusivo não pode ser inserido na tabela rowstore, o SQL Server não poderá inserir o valor no columnstore.

Índices columnstore ordenados

Ao permitir a eliminação eficiente de segmentos, os índices columnstore ordenados oferecem um desempenho mais rápido ao ignorar grandes quantidades de dados ordenados que não correspondem ao predicado da consulta. Carregar dados em um índice columnstore ordenado pode levar mais tempo do que em um índice não ordenado devido à operação de classificação de dados, no entanto, com consultas de índices columnstore ordenados podem ser executadas mais rapidamente posteriormente.

Disponibilidade de índice columnstore ordenado

Os índices columnstore ordenados estão disponíveis nas seguintes plataformas:

Plataforma Índices columnstore clusterizados ordenados Índices columnstore não clusterizados ordenados
Banco de Dados SQL do Azure Sim Sim
Instância Gerenciada de SQL do AzureAUTD Sim Sim
Instância Gerenciada de SQL do Azure2022 Sim Não
Banco de dados SQL no Microsoft Fabric Sim1 Sim
Versão prévia do SQL Server 2025 (17.x) Sim Sim
SQL Server 2022 (16.x) Sim Não
Pool de SQL dedicado no Azure Synapse Analytics Sim Não

AUTD Aplica-se à Instância Gerenciada de SQL do Azure configurada com a Política de atualização Sempre atualizado.
2022 aplica-se à Instância Gerenciada de SQL do Azure configurada com a política de atualização SQL Server 2022.
1No banco de dados SQL do Fabric, as tabelas com índices columnstore clusterizados não são espelhadas no Fabric OneLake.

Metadados

Todas as colunas em um índice columnstore são armazenadas nos metadados como colunas incluídas. Índice columnstore não tem colunas-chave.

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (Transact-SQL)

sys.column_store_segments (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_row_groups (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

Tarefa Artigos de referência Observações
Crie uma tabela como um columnstore. CREATE TABLE (Transact-SQL) Por padrão, quando você cria uma tabela, ela usa rowstore como o formato de dados subjacente. A partir do SQL Server 2016 (13.x), você pode criar a tabela com um índice columnstore clusterizado especificando a opção INDEX ... CLUSTERED COLUMNSTORE . Não é preciso criar primeiro uma tabela rowstore e convertê-la em columnstore.
Converta uma tabela rowstore em um columnstore. CRIAR ÍNDICE COLUMNSTORE (Transact-SQL) Converta um heap ou árvore B existente em um columnstore. Exemplos mostram como lidar com os índices existentes e o nome do índice ao realizar essa conversão.
Crie um índice columnstore não clusterizado numa tabela de tipo rowstore. CRIAR ÍNDICE COLUMNSTORE (Transact-SQL) Uma tabela rowstore pode ter um índice columnstore não clusterizado. A partir do SQL Server 2016 (13.x), o índice columnstore não clusterizado pode ter uma condição filtrada. Exemplos mostram a sintaxe básica.
Converta uma tabela do tipo columnstore em uma do tipo rowstore. CREATE CLUSTERED INDEX (Transact-SQL) ou Converter uma tabela columnstore novamente em um heap rowstore Geralmente, essa conversão não é necessária, mas pode haver ocasiões em que você precisa realizá-la. Exemplos mostram como converter um columnstore em um heap ou em um índice clusterizado.
Crie índices columnstore para armazenamento de dados. Índices columnstore para armazenamento de dados Descreve como usar índices columnstore para consultas rápidas de armazenamento de dados.
Crie índices para análise operacional. Introdução ao columnstore para análise operacional em tempo real Descreve como criar índices de árvore B e columnstore complementares, para que as consultas OLTP usem índices de árvore B e as consultas de análise usem índices columnstore.
Use um índice de árvore B para impor uma restrição de chave primária em um índice columnstore. Índices columnstore para armazenamento de dados Mostra como combinar índices de árvore B e columnstore para impor a restrição de chave primária em uma tabela columnstore.
Crie uma tabela com otimização de memória com um índice columnstore. CREATE TABLE (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.
Carregue dados em um índice columnstore. Carregamento de dados de índices columnstore
Remova um índice columnstore. DROP INDEX (Transact-SQL) A remoção de um índice columnstore usa a sintaxe DROP INDEX padrão usada pelos índices de árvore B. A remoção de um índice columnstore clusterizado converte a tabela columnstore em um heap.
Exclua uma linha de um índice columnstore. DELETE (Transact-SQL) Use DELETE (Transact-SQL) para excluir uma linha.

linha columnstore: 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 recriado.
linha deltastore: o SQL Server exclui lógica e fisicamente a linha.
Atualize uma linha no índice columnstore. UPDATE (Transact-SQL) Use UPDATE (Transact-SQL) para atualizar uma linha.

linha de columnstore: o SQL Server marca a linha como excluída logicamente e, em seguida, insere a linha atualizada no deltastore.
linha deltastore: o SQL Server atualiza a linha no deltastore.
Mantenha um índice columnstore. ALTER INDEX … REBUILD

REORGANIZE um índice columnstore

Métodos de manutenção de índice: reorganizar e reconstruir		 Na maioria dos casos, ALTER INDEX ... REORGANIZE fornece resultados semelhantes, ALTER INDEX ... REBUILD mas com menor consumo de recursos. ALTER INDEX ... REORGANIZE sempre é executado online. As duas opções desfragmentam um índice columnstore e forçam as linhas no deltastore a entrar no columnstore.

A partir do SQL Server 2019 (15.x), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, a qualidade do índice columnstore é mantida automaticamente, removendo a necessidade de manutenção periódica do índice na maioria dos casos.

Conteúdo relacionado