Índice do SQL Server: Aumentar o desempenho do banco de dados

Quando comecei a trabalhar com o SQL Server, percebi que nem todas as consultas lentas eram criadas da mesma forma. Algumas consultas eram lentas devido a um design ruim, enquanto outras eram simplesmente ineficientes. Mas uma descoberta mudou para sempre a forma como eu abordava o desempenho do banco de dados: os índices. Imagine tentar encontrar um capítulo específico em um livro didático enorme sem um índice; você teria que folhear centenas de páginas, desperdiçando um tempo precioso. Esse cenário reflete o que acontece em bancos de dados sem a indexação adequada.

Neste artigo, explicarei por que os índices do SQL Server são importantes, como eles funcionam e como você pode aproveitá-los para maximizar o desempenho dos seus bancos de dados. Se você deseja aprofundar suas habilidades em SQL no SQL Server, recomendo que faça nossos cursos Introduction to SQL Server e Intermediate SQL Server para aprender sobre T-SQL para manipulação e análise de dados.

Entendendo os índices do SQL Server

Os índices do SQL Server permitem que você consulte tabelas de forma eficiente em bancos de dados sem examinar conjuntos de dados inteiros. A seguir, você verá a definição de índices do SQL Server e sua importância no gerenciamento de bancos de dados.

O que são índices do SQL Server?

Os índices são estruturas de dados especializadas que permitem que o SQL Server localize e recupere linhas de forma mais eficiente, economizando tempo e recursos computacionais. Os índices funcionam organizando os dados de uma forma que permite pesquisas mais rápidas. Em vez de examinar toda a tabela linha por linha (um processo conhecido como varredura de tabela), o SQL Server pode usar o índice para encontrar as linhas necessárias muito mais rapidamente do que se você tivesse que examinar todas as entradas da tabela.

Há dois tipos fundamentais de índices no SQL Server:

• Índices agrupados: Um índice clusterizado determina a ordem física dos dados em uma tabela. Quando você cria um índice clusterizado em uma tabela, as linhas são armazenadas no disco na ordem da coluna indexada. Isso é particularmente vantajoso para consultas de intervalo, pois permite o acesso sequencial eficiente às linhas.

• Índices não agrupados: Os índices não agrupados não afetam a ordem física dos dados. Em vez disso, eles criam uma estrutura separada que contém ponteiros para as linhas de dados reais. Os índices não agrupados são úteis para melhorar o desempenho em consultas que filtram ou classificam com base em colunas não incluídas no índice agrupado.

Confira nosso Tutorial do SQL Server para entender o uso básico e avançado do SQL Server no gerenciamento de dados.

Por que os índices são importantes?

Os índices são importantes para melhorar o desempenho das consultas e a eficiência geral do banco de dados. Aqui estão alguns dos principais benefícios do uso de índices:

• Execução mais rápida de consultas: Os índices reduzem o tempo necessário para localizar e recuperar dados. Por exemplo, se você estiver pesquisando todos os pedidos de um cliente específico em uma tabela com milhões de linhas, um índice na coluna do cliente permitirá que o SQL Server localize essas linhas quase instantaneamente.

• Uso eficiente de recursos: Com os índices, o SQL Server pode concluir tarefas usando menos recursos de CPU e memória. Essa eficiência se traduz em melhor desempenho geral do sistema, especialmente em cargas de trabalho pesadas.

• Melhoria na classificação e no agrupamento: Os índices podem acelerar as operações que envolvem classificação (ORDER BY) e agrupamento (GROUP BY). Ao manter os dados classificados, os índices permitem que o SQL Server recupere os resultados rapidamente sem sobrecarga adicional de classificação.

Considere este caso de uso para aplicativos de índice. Imagine uma grande plataforma de comércio eletrônico em que os usuários frequentemente pesquisam produtos por nome. Ao criar um índice não agrupado na coluna de nome do produto, você pode tornar essas pesquisas quase instantâneas, melhorando a experiência do usuário e reduzindo a carga do servidor.

Recomendo que você confira o programa de habilidades SQL Server Fundamentals do DatCamp para aprender sobre classificação, agrupamento e união de tabelas para análise de dados.

Tipos de índices do SQL Server

Existem diferentes tipos de índices do SQL Server, cada um com características e casos de uso exclusivos. Vamos discutir os tipos comuns de índices.

Índices agrupados vs. não agrupados

Os dois principais tipos de índices no SQL Server são os índices agrupados e os não agrupados.

Índice agrupado

Um índice clusterizado determina a ordem física das linhas em uma tabela, classificando efetivamente a tabela pela(s) coluna(s) do índice. Como os dados podem ser ordenados fisicamente de uma única maneira, só pode haver um índice clusterizado por tabela. Um índice clusterizado é ideal para consultas que retornam um intervalo de valores ou que precisam classificar os dados pela coluna indexada. 

Por exemplo, uma tabela de vendas classificada por OrderDate pode se beneficiar de um índice clusterizado na coluna OrderDate. As consultas que buscam todos os pedidos em um intervalo de datas serão executadas muito mais rapidamente.

Índice não agrupado

Um índice não agrupado cria uma estrutura separada que aponta para as linhas reais da tabela. Em uma tabela, você pode ter vários índices não agrupados, cada um usado em um padrão de consulta diferente. Os índices não agrupados contêm uma referência (localizador de linha) aos dados reais, que pode ser um ID de linha ou uma chave de índice agrupado. Esse tipo de índice é aplicado a colunas usadas com frequência em condições de pesquisa ou junções (por exemplo, EmailAddress, ProductName).

Por exemplo, um índice não agrupado na coluna ProductName de uma tabela de inventário permite que você faça pesquisas mais rápidas de produtos específicos sem alterar a estrutura da tabela.

Índices únicos e compostos

O SQL Server também oferece suporte a índices exclusivos e compostos, cada um servindo a propósitos específicos.

Índices exclusivos

Um índice exclusivo garante que todos os valores na(s) coluna(s) indexada(s) sejam distintos. Ele reforça a integridade dos dados ao impedir a duplicação de valores e é frequentemente aplicado a colunas com exclusividade natural, como endereços de e-mail ou nomes de usuário. Por exemplo, um índice exclusivo na coluna Email de uma tabela de usuários garante que não haja dois usuários com o mesmo endereço de e-mail.

Índices compostos

Um índice composto abrange várias colunas, otimizando as consultas que filtram ou classificam com base em uma combinação dessas colunas. A ordem das colunas no índice composto é importante quando o SQL Server processa a coluna mais à esquerda primeiro. Os índices compostos oferecem suporte a consultas complexas que envolvem várias condições. Por exemplo, um índice composto em LastName e FirstName em uma tabela de clientes acelera as pesquisas de clientes pelo nome completo.

Como criar e gerenciar índices do SQL Server

Usamos o SQL Server Management Studio (SSMS) para criar um índice no SQL Server. Antes de explicar melhor, dê uma olhada em nosso blog para entender as diferenças entre os dialetos do SQL Server, PostgreSQL e MySQL. Abordarei as etapas envolvidas na criação e no gerenciamento de índices do SQL Server.

Como criar um índice do SQL Server

A seguir, você encontrará um guia passo a passo para criar índices usando o SQL Server Management Studio (SSMS) e o código SQL.

Criar um índice usando o SSMS

1. Abra o Object Explorer: Inicie o SSMS e conecte-se à instância do SQL Server. Expanda o banco de dados que contém a tabela para a qual você deseja criar um índice.

Abra o Object Explorer e navegue na tabela para que você crie o índice. Imagem do autor.

2. Localize a tabela: Navegue até a pasta Tables (Tabelas), encontre sua tabela e a expanda.

Localize a tabela para que você crie o índice e a expanda. Imagem do autor.

3. Abra a pasta Indexes: Clique com o botão direito do mouse na pasta Indexes (Índices ) e selecione New Index ( Novo índice).

Abra a pasta Index e clique com o botão direito do mouse para criar um novo índice. Imagem do autor.

4. Escolha o tipo de índice: Selecione o tipo de índice que você deseja criar (por exemplo, índice Clustered Columnstore ou índice Non-Clustered).
5. Defina as propriedades do índice: Especifique o nome do índice e, em seguida, adicione colunas ao índice clicando em Adicionar e selecionando as colunas desejadas. Configure opções adicionais, como exclusividade e ordem de classificação.

Escolha o tipo de índice e defina as propriedades. Imagem do autor.

6. Criar o índice: Clique em OK para criar o índice.

Crie o índice clicando em ok. Imagem do autor.

Criar um índice usando o código SQL

Você também pode criar índices diretamente por meio de comandos T-SQL. Abaixo estão exemplos de criação de diferentes tipos de índices usando consultas:

-- Create a non-clustered index on the Product column
CREATE NONCLUSTERED INDEX IX_Sales_Product
ON Sales (Product);

-- Create a unique index on the combination of CustomerName and OrderDate
CREATE UNIQUE NONCLUSTERED INDEX IX_Sales_CustomerName_OrderDate
ON Sales (CustomerName, OrderDate);

-- Create a clustered index on the OrderDate column
CREATE CLUSTERED INDEX IX_Sales_OrderDate
ON Sales (OrderDate);

Como eliminar um índice do SQL Server

Você pode eliminar um índice usando o método direto. Tenha cuidado, pois a remoção de um índice pode afetar o desempenho da consulta.

Eliminar o índice do SQL Server usando o SSMS

1. Navegue até a pasta Índices sob a tabela no Object Explorer.
2. Clique com o botão direito do mouse no índice que você deseja excluir e selecione Excluir.

Navegue até o índice que você deseja excluir. Imagem do autor.

3. Confirme a exclusão na caixa de diálogo.

Confirme a exclusão na caixa de diálogo e clique em ok. Imagem do autor.

Eliminação de um índice usando T-SQL

Você pode usar os seguintes comandos para eliminar os índices existentes de uma tabela no SQL Server.

-- Drop a non-clustered index
DROP INDEX IX_Sales_Product ON Sales;

-- Drop a clustered index
DROP INDEX IX_Sales_OrderDate ON Sales;

Como reorganizar um índice do SQL Server

A reorganização de um índice desfragmenta as páginas em nível de folha do índice, melhorando o desempenho sem reconstruí-lo totalmente.

Reorganizar um índice do SQL Server usando o SSMS

1. Clique com o botão direito do mouse no índice e vá para Reorganizar tudo.

Clique com o botão direito do mouse em índices e, em seguida, em Reorganizar tudo. Imagem do autor.

2. Selecione o(s) índice(s) a ser(em) reorganizado(s) e confirme.

Selecione os índices a serem reorganizados e, em seguida, confirme. Imagem do autor.

Reorganização de um índice usando T-SQL

Você também pode usar as seguintes consultas para reorganizar os índices no SQL Server.

-- Reorganize a single index
ALTER INDEX IX_Sales_Product
ON Sales
REORGANIZE;

-- Reorganize all indexes on the table
ALTER INDEX ALL
ON Sales
REORGANIZE;

Como reconstruir um índice do SQL Server

A reconstrução de um índice o recria do zero, eliminando a fragmentação e otimizando o armazenamento.

Reconstrução de um índice usando o SSMS

1. Clique com o botão direito do mouse no índice e vá para Rebuild All( Reconstruir tudo).

Clique com o botão direito do mouse no índice e vá para Rebuild All (Reconstruir tudo). Imagem do autor

2. Selecione o(s) índice(s) a ser(em) reconstruído(s) e confirme.

Selecione os índices a serem reconstruídos e confirme. Imagem do autor

Reconstrução de um índice usando T-SQL

Com a consulta a seguir, você reconstruirá os índices da tabela.

-- Rebuild a single index
ALTER INDEX IX_Sales_Product
ON Sales
REBUILD;

-- Rebuild all indexes on the table
ALTER INDEX ALL
ON Sales
REBUILD;

Práticas recomendadas para o gerenciamento de índices

Manter e otimizar os índices do SQL Server é importante para garantir o desempenho máximo do banco de dados. A seguir, você encontrará dicas para um gerenciamento eficaz do índice.

• Realize a manutenção regular do índice: Reorganize ou reconstrua seu índice regularmente para reduzir a fragmentação, que pode prejudicar o desempenho com o tempo. Use scripts de manutenção ou ferramentas como o SQL Server Maintenance Plans para automatizar essas tarefas.

• Analisar o uso do índice: Use DMVs (Dynamic Management Views), como sys.dm_db_index_usage_stats, para identificar índices não utilizados ou subutilizados. Remova os índices que não oferecem benefícios significativos de desempenho para evitar sobrecarga desnecessária.

• Evite a indexação excessiva: O excesso de índices pode tornar as operações de gravação mais lentas (INSERT, UPDATE, DELETE) porque todos os índices relevantes devem ser atualizados. Concentre-se na indexação de colunas que são usadas com frequência nas consultas, uniões ou condições de filtragem do site SELECT.

• Monitore os níveis de fragmentação: Use o site sys.dm_db_index_physical_stats para identificar índices altamente fragmentados. Reorganize os índices para fragmentação abaixo de 30% e reconstrua-os para fragmentação acima de 30%.

• Aproveite os índices filtrados: Use índices filtrados para otimizar as consultas que operam em um subconjunto de dados, como usuários ativos ou intervalos de datas específicos.

Você também deve prestar muita atenção às práticas a seguir para evitar armadilhas comuns ao gerenciar seus índices:

• Superindexação: Evite criar índices para cada consulta; isso afeta as operações de gravação e o armazenamento.

• Negligenciar a manutenção: O fato de você não reconstruir ou reorganizar os índices leva ao aumento da fragmentação.

• Ignorando a indexação para gravações: Embora os índices acelerem as leituras, eles podem reduzir significativamente a velocidade das gravações.

• Não testar índices compostos: A ordem incorreta das colunas nos índices compostos pode torná-los ineficazes.

Confira o programa de habilidades SQL Server for Database Administrators da DataCamp para saber mais sobre transações e tratamento de erros no SQL Server para melhorar o desempenho das consultas.

Considerações sobre o desempenho

Os índices são úteis para otimizar o desempenho do banco de dados, mas podem ter algumas desvantagens. Compreender seu impacto e gerenciá-los de forma eficaz é fundamental para manter um sistema de banco de dados com bom desempenho.

Impacto dos índices no desempenho da consulta

Os índices influenciam significativamente os planos de execução de consultas e o desempenho, reduzindo o tempo necessário para recuperar dados. Eles permitem que o SQL Server localize as linhas rapidamente, reduzindo a necessidade de varreduras de tabelas completas e dispendiosas. Com frequência, os planos de execução mostrarão operações como Index Seek em vez de Table Scan quando houver índices efetivos.

As ferramentas para medir o desempenho do índice incluem o seguinte:

• sys.dm_db_index_usage_stats programa como os índices são usados pelas consultas.

• sys.dm_db_index_operational_stats fornece métricas operacionais, como leituras e gravações de páginas.

• O armazenamento de consultas aanalisa o desempenho da consulta e identifica as ineficiências causadas por índices ausentes ou não utilizados.

Por exemplo, a consulta a seguir recupera estatísticas sobre como os índices em tabelas definidas pelo usuário estão sendo usados, incluindo detalhes sobre buscas, varreduras, pesquisas e atualizações, para ajudar a avaliar sua eficácia e desempenho.

-- Retrieve index usage statistics for user tables
SELECT 
    OBJECT_NAME(IXS.OBJECT_ID) AS TableName, 
    I.name AS IndexName,
    IXS.user_seeks AS Seeks,
    IXS.user_scans AS Scans,
    IXS.user_lookups AS Lookups,
    IXS.user_updates AS Updates
-- DMV providing index usage stats
FROM 
    sys.dm_db_index_usage_stats IXS         
JOIN 
    sys.indexes I ON I.OBJECT_ID = IXS.OBJECT_ID 
                 AND I.index_id = IXS.index_id
WHERE 
    OBJECTPROPERTY(IXS.OBJECT_ID, 'IsUserTable') = 1;

Equilibrar o uso e o armazenamento do índice

Embora os índices melhorem o desempenho do banco de dados, eles também têm custos de armazenamento, pois consomem espaço em disco, especialmente em grandes conjuntos de dados. É importante que você entenda como equilibrar esses fatores ao implementar índices. Mais índices podem tornar as operações de gravação mais lentas, especialmente em ambientes com muitas transações. Considere as seguintes estratégias para equilibrar o uso do índice.

• Indexe apenas o que importa: Concentre-se na indexação de colunas usadas com frequência nas cláusulas WHERE, JOIN e ORDER BY. Evite indexar colunas raramente consultadas.

• Use índices filtrados: Crie índices para subconjuntos de dados para economizar espaço e otimizar consultas específicas.

• Alavancar a compressão: Habilite a compactação de dados para reduzir o espaço de armazenamento dos índices.

• Arquivar dados antigos: Mova os dados raramente acessados para uma tabela de arquivo e reduza a indexação na tabela principal.

As estratégias acima garantirão o desempenho ideal da consulta e, ao mesmo tempo, manterão os custos de armazenamento e manutenção sob controle.

Solução de problemas de índice

Quando não são mantidos adequadamente, os índices podem se degradar com o tempo, reduzindo a eficiência do banco de dados. Vamos discutir como você pode identificar e resolver esses problemas.

• Fragmentação: Com o tempo, os índices ficam fragmentados, o que leva a uma recuperação ineficiente dos dados. Os sinais de fragmentação incluem o aumento do tempo de execução da consulta e a alta E/S do disco. Para resolver esse problema, use o site sys.dm_db_index_physical_stats para identificar a fragmentação. Em seguida, reorganize os índices para níveis de fragmentação abaixo de 30% e reconstrua os índices para níveis de fragmentação acima de 30%

• Estatísticas desatualizadas: Estatísticas desatualizadas levam a planos de execução de consultas abaixo do ideal. Portanto, você pode ter consultas lentas, apesar de ter os índices adequados. Para resolver esse problema, atualize manualmente as estatísticas dos índices críticos ou ative o site AUTO_UPDATE_STATISTICS.

• Índices não utilizados ou redundantes: Alguns índices permanecem sem uso, consumindo armazenamento e tornando as operações de gravação mais lentas. Os sintomas desse problema são os altos custos de manutenção do índice e o baixo benefício da consulta. Para resolver esse problema, identifique os índices não utilizados com sys.dm_db_index_usage_stats e elimine-os conforme necessário.

• Índices ausentes: A falta de índices adequados pode resultar em varreduras de tabelas, tornando as consultas mais lentas. Nesses cenários, os planos de execução de consulta mostram a varredura de tabela em vez da busca de índice. Para resolver esse problema, use o Missing Index DMV sys.dm_db_missing_index_details para identificar oportunidades e criar os índices recomendados com base nos padrões de consulta.

• Sobreposição de índices: Vários índices com estruturas de colunas semelhantes desperdiçam recursos, levando a um aumento no uso do armazenamento e a operações de manutenção redundantes. Sempre consolide os índices sobrepostos em um único índice bem projetado.

Experimente nosso programa de carreira de Desenvolvedor do SQL Server para saber mais sobre como solucionar problemas de índice no SQL Server e melhorar a eficiência do banco de dados.

Conclusão

Dominar os índices do SQL Server é importante se você quiser melhorar o desempenho do banco de dados escrevendo consultas eficientes. Ao trabalhar com índices, você também deve entender os diferentes tipos de índices do SQL Server para gerenciar os índices e obter o desempenho ideal do banco de dados.

Recomendo nosso programa de carreira Associate Data Analyst in SQL para que você se torne um analista de dados proficiente. Nosso curso Reporting in SQL também ajudará você a se tornar proficiente na criação de relatórios e painéis complexos para a apresentação eficaz de dados. Se você estiver se preparando para uma entrevista que inclua a demonstração de suas habilidades no SQL Server, recomendo que consulte nosso blog, Top 30 SQL Server Interview Questions, para ajudá-lo a praticar e arrasar na entrevista.