Otimizar com índices
Os índices são estruturas de dados que aceleram a recuperação de dados ao criar caminhos de pesquisa otimizados para as linhas das tabelas. Sem índices, o motor da base de dados tem de analisar cada linha de uma tabela para encontrar registos correspondentes, uma varredura completa da tabela que se torna proibitivamente lenta à medida que as tabelas crescem.
Um índice funciona como o índice de um livro: em vez de ler cada página para encontrar um artigo, consulta-se o índice para saltar diretamente para as páginas relevantes. A base de dados utiliza índices de forma semelhante, convertendo potencialmente milhões de comparações de linhas em algumas consultas eficientes.
No entanto, os índices consomem espaço de armazenamento e atrasam INSERT, UPDATE, e DELETE as operações porque a base de dados tem de manter a estrutura do índice juntamente com os dados. Este compromisso torna a seleção do índice uma decisão crítica de design que impacta diretamente tanto o desempenho da consulta como a largura de banda de escrita.
Diferentes tipos de índice servem diferentes propósitos.
Usar índices de rowstore
Projetar índices eficientes é fundamental para alcançar um bom desempenho de banco de dados e aplicativos. A falta de índices, a superindexação ou índices mal projetados são as principais fontes de problemas de desempenho do banco de dados.
Os índices de rowstore organizam os dados em formato de linha, armazenando todas as colunas de uma linha na mesma página, o que os torna ideais para cargas de trabalho transacionais que recuperam registos completos ou realizam atualizações frequentes.
Um índice agrupado ordena e armazena as linhas de dados na tabela com base nos seus valores-chave. Esses valores-chave são as colunas incluídas na definição do índice. Pode haver apenas um índice clusterizado por tabela, porque as próprias linhas de dados podem ser armazenadas em apenas uma ordem.
Um índice não agrupado tem uma estrutura separada das linhas de dados. Um índice não clusterizado contém os valores de chave de índice não clusterizados e cada entrada de valor de chave tem um ponteiro para a linha de dados que contém o valor da chave. Você pode criar vários índices não clusterizados em uma tabela ou modo de exibição indexado.
-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID
ON Product(ProductID);
-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category
ON Product(Category)
INCLUDE (ProductName, Price);
Índices agrupados são melhores quando se precisam de consultas de intervalo eficientes, chaves estáveis e estreitas, ou uma ordem de ordenação natural como colunas de identidade ou campos de data, porque definem a ordem física das linhas e otimizam varreduras em detrimento de dados ordenados.
Índices não agrupados são ideais quando precisas de consultas rápidas para predicados específicos, joins ou padrões de ordenação que não se alinham com a chave clusterizada, ou quando queres cobrir uma consulta incluindo colunas extra para evitar consultas de chave.
A escolha entre eles depende de como acede aos dados: use um índice clusterizado para o caminho de acesso primário e índices não agrupados para suportar padrões alternativos, altamente seletivos ou frequentemente consultados, equilibrando o custo que introduzem nas operações de escrita.
Compreender os índices de colunas
Os índices tradicionais de rowstore armazenam dados linha a linha, o que é perfeito para sistemas transacionais que recuperam registos individuais. Mas consultas analíticas que analisam milhões de linhas para calcular agregados (SUM, AVG, COUNT) perdem tempo a ler colunas de que não precisam. Os índices Columnstore procuram resolver isto armazenando dados coluna a coluna, lendo apenas as colunas necessárias para a sua consulta.
Compreender a arquitetura de columnstore
Um índice columnstore organiza os dados em grupos de linhas, cada um contendo até 1.048.576 linhas. Dentro de cada grupo de linhas, o motor armazena cada coluna separadamente como um segmento de coluna e comprime-o de forma independente. Esta arquitetura permite ao otimizador de consultas ler apenas as colunas necessárias para uma consulta, ignorando completamente dados irrelevantes.
Quando insere dados, pequenos lotes vão primeiro para um deltastore — uma estrutura temporária de rowstore usando um índice de árvore B+. Uma vez que um grupo delta acumula linhas suficientes (pelo menos 102.400), um processo de fundo chamado tuple-mover comprime-o no armazenador de colunas. As linhas que chegam através de carregamentos em massa de 102.400 ou mais linhas contornam o deltastore e são comprimidas diretamente no columnstore.
A tabela seguinte descreve a recomendação para índices columnstore:
| Scenario | Recommendation | Reason |
|---|---|---|
| Tabelas de factos de data warehouse | Usar armazenamento de colunas | Tabelas com milhões+ linhas usadas para análise beneficiam do armazenamento e compressão em colunas |
| Bases de dados de reporte | Utilizar coluna armazenada | Cargas de trabalho intensivas em leitura com consultas agregadas têm um desempenho mais rápido com acesso orientado a colunas |
| Dados históricos | Utilizar o índice columnstore | Dados arquivados, que raramente atualizas mas analisas frequentemente, alcançam altas taxas de compressão |
| Tabelas pequenas (<1 milhão de linhas) | Evitar columnstore | Os custos gerais superam os benefícios; Os grupos de linhas precisam de linhas suficientes para uma compressão eficaz |
| Atualizações/eliminações de alta frequência | Evitar o uso de columnstore | As modificações marcam as linhas como eliminadas em vez de serem atualizadas no local, causando fragmentação |
| Consultas de linha única | Evite columnstore | Os índices de rowstore são mais rápidos para recuperar registos individuais |
Utilize o Índice de Armazenamento em Colunas Agrupadas (CCI)
Um Índice Clustered Columnstore (CCI) é um tipo de índice columnstore que se torna a estrutura principal de armazenamento para toda a tabela, substituindo qualquer índice rowstore agrupado existente. Ao contrário de um índice columnstore não agrupado (NCCI), que cria uma cópia colunar secundária ao lado da tabela rowstore, um CCI armazena todos os dados da tabela exclusivamente em formato colunar.
Isto significa que a tabela não tem armazenamento tradicional baseado em linhas — o motor comprime e armazena cada coluna separadamente. Tanto o CCI como o NCCI usam as mesmas otimizações de compressão colunar e processamento em lote, mas usam um CCI quando a análise é a principal carga de trabalho e não precisas de padrões de acesso transacional ao nível das linhas. Em contraste, um NCCI permite-lhe manter índices de rowstore para consultas transacionais enquanto fornece uma estrutura colunar para consultas analíticas na mesma tabela.
Pode criar um índice columnstore agrupado utilizando a instrução CREATE CLUSTERED COLUMNSTORE INDEX. Eis um exemplo:
-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;
-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;
Use o Índice de Loja de Colunas Não Agrupado (NCCI)
Um Índice de Armazenamento de Colunas Não Agrupado (NCCI) cria uma cópia colunar separada das colunas selecionadas juntamente com a tabela de armazenamento em linhas existente, permitindo que a mesma tabela sirva eficientemente tanto cargas de trabalho transacionais quanto analíticas. A tabela mantém o seu índice original clusterizado de armazenamento de linhas para consultas rápidas de linha única e atualizações, enquanto o NCCI fornece acesso otimizado baseado em colunas para consultas analíticas. O otimizador de consultas escolhe automaticamente entre as estruturas de rowstore e columnstore com base no padrão de consulta.
Pode criar um índice de columnstore não agrupado usando a CREATE NONCLUSTERED COLUMNSTORE INDEX instrução. Eis um exemplo:
-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);
Monitorar os índices de armazenamento em coluna
Pode monitorizar a saúde e o desempenho dos seus índices de colonstore consultando a sys.dm_db_column_store_row_group_physical_stats vista de gestão dinâmica.
A consulta seguinte mostra estatísticas de grupos de linhas, incluindo estado, contagem de linhas, linhas eliminadas e tamanho de armazenamento. Os grupos de linhas abertos continuam a aceitar inserções no deltastore, os grupos de linhas fechados estão à espera que o agente de movimentação de tuplas os comprima, e os grupos de linhas comprimidos armazenam os dados em formato em colunas. Contagens elevadas de linhas eliminadas ou muitos pequenos grupos de linhas indicam fragmentação que pode ser resolvida com ALTER INDEX REORGANIZE.
-- Check columnstore health
SELECT
object_name(object_id) AS TableName,
state_desc,
total_rows,
deleted_rows,
size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');
A seleção de índice impacta diretamente tanto o desempenho das consultas como a taxa de escrita. Para evitar reconstruções custosas e problemas de desempenho em produção, indexes devem ser cuidadosamente desenhados na fase de desenvolvimento inicial.