Guia do analista de dados para indexação SQL: Corrigir consultas lentas

O tempo de resposta de sua consulta SQL deixa a desejar? Ou talvez você não saiba se suas consultas poderiam ser mais rápidas. Neste artigo, explicaremos como a indexação SQL pode ajudar.

Mergulhar no SQL é como desbloquear um superpoder. Trata-se de dominar a arte de consultar o banco de dados de forma eficaz para que você possa recuperar as informações de que precisa com rapidez e facilidade. Mas o que acontece quando você faz uma pergunta que é complexa o suficiente para diminuir o tempo de resposta de segundos para minutos - ou até mais? É nesse momento que você diz olá à indexação SQL.

Neste artigo, compartilharei tudo o que você precisa saber sobre a indexação SQL e por que ela deve ser a próxima etapa na otimização de suas consultas SQL. Vamos mergulhar de cabeça!

SQL e consultas a bancos de dados

SQL é uma linguagem para consulta e manutenção de bancos de dados. Ela permite que você armazene e visualize dados, bem como atualize esses dados. O gerenciamento de dados é fundamental para a análise de dados e o business intelligence. Precisamos gerenciar os dados subjacentes para que possamos analisá-los e tirar conclusões a partir deles: Quais produtos estão vendendo bem? Quais clientes estão comprando muito? Quais clientes não estão pagando em dia?

Quando tivermos os dados, poderemos responder a esses tipos de perguntas. Mas, primeiro, precisamos "fazer perguntas" ou "fazer solicitações" sobre nossos dados. Essas solicitações são chamadas de consultas - por exemplo, algo como "Para as vendas do mês passado, conte o número de vezes que cada produto foi vendido e me forneça a soma das vendas".

É claro que, à medida que fazemos perguntas mais complexas, nossas consultas se tornam mais complexas. Talvez seja necessário combinar (ou JOINs) informações de várias fontes para que possamos extrair as informações de que precisamos.

Por exemplo, é útil saber que o produto ID 2123876123 foi o mais vendido durante o trimestre anterior, mas é ainda mais útil saber que esse produto é um moletom azul. À medida que a complexidade das nossas consultas aumenta, torna-se mais desafiador e demorado para o nosso sistema de banco de dados responder à consulta. Precisamos acelerar nossas consultas SQL, mas como?

É aqui que a diversão começa. Há uma arte sutil para entender quando uma consulta está respondendo lentamente e como melhorar seu desempenho. Poderíamos tentar modificar nossa consulta e torná-la mais eficiente, mas o ajuste de desempenho do SQL só nos levará até certo ponto. Em algum momento, precisamos realizar a otimização da consulta. Isso torna mais fácil para o sistema de banco de dados recuperar os resultados que estamos procurando, o que melhora o desempenho da consulta.

É aí que entram os índices.

O que é um índice SQL?

Como o índice de um livro, os índices SQL preparam o sistema de banco de dados para uma recuperação de dados mais eficiente. A criação de índices é simples; temos um curso abrangente sobre índices que ensinará tudo o que você precisa saber. Isso inclui como eles são criados no banco de dados, a sintaxe SQL para criar um índice e quando criar um índice. Há mais de 50 exercícios e uma estimativa de 10 horas de aprendizado.

Se você quiser uma introdução à parte teórica dos índices, também temos outros artigos para você sobre noções básicas de indexação SQL e What Is a Database Index?

Para nossos propósitos, não nos preocuparemos com os detalhes de como o banco de dados cria um índice ou com sua estrutura de árvore B subjacente. Em vez disso, vamos nos concentrar em como o banco de dados usa um índice. Basta dizer que um índice B-tree (árvore balanceada) nos permite acessar qualquer linha do banco de dados no mesmo tempo.

A indexação do banco de dados acelera a recuperação de dados. Volte à nossa analogia: é mais fácil encontrar a página que se refere a Abraham Lincoln procurando por "Lincoln, Abraham" no índice do livro. (Compare isso com procurar em cada página uma menção ao Sr. Lincoln e você entenderá a ideia). Usando um índice, encontrar a referência a qualquer pessoa específica no livro levaria o mesmo tempo.

Por outro lado, imagine que o livro não tenha índice e que você tenha de examinar todas as páginas para encontrar um nome. A localização de qualquer referência específica levará um tempo desconhecido e variável - as pessoas que aparecem nas primeiras páginas serão encontradas mais rapidamente do que as que aparecem no final (a menos que façamos a varredura de trás para frente).

A indexação SQL funciona da mesma forma. Um índice é aplicado a uma coluna que torna mais fácil para o banco de dados retornar informações para uma pesquisa.

Devemos criar índices para cada coluna em uma tabela? Isso não aceleraria o banco de dados? Não. A indexação de muitas colunas afetará negativamente o desempenho do banco de dados, tornando a adição e a atualização de linhas muito lentas. A prática recomendada atual é indexar somente as colunas que são usadas com frequência para ordenar ou classificar dados.

Exemplo nº 1 de indexação de banco de dados

Vamos supor que você precise armazenar informações sobre pessoas: nome, sobrenome, nome da rua, número da rua, código postal/ZIP, cidade, país, número de telefone e data de nascimento.

Agora, vamos supor que você tenha milhões de registros armazenados nesse banco de dados. Você provavelmente pesquisaria (ou seja, consultaria) o banco de dados com base no sobrenome? Sim, provavelmente. Você consultaria o banco de dados usando apenas o primeiro nome? Não, provavelmente não; é mais provável que você faça a consulta com base no primeiro nome e no sobrenome. Mas talvez você queira ver quando um determinado primeiro nome foi o mais popular no conjunto de dados. Nesse caso, você perguntaria "em que ano nasceram mais pessoas com esse primeiro nome?"

Cada um desses casos exigiria uma abordagem diferente para a indexação. Se estivermos pesquisando apenas pelo sobrenome, criaríamos um índice na coluna last_name. Se estivéssemos pesquisando pelo primeiro e pelo last nome, indexaríamos a coluna first_name e a coluna last_name. No terceiro caso, criaríamos um índice somente na coluna first_name.

Em resumo, há diferentes técnicas de indexação SQL. Ao aplicar essas diferentes técnicas, podemos obter o ajuste de desempenho do SQL. A criação de índices depende de como você usará os dados - ou, em outras palavras, como você consultará os dados.

Exemplo nº 2 de indexação de banco de dados

Imagine que temos uma tabela person com quatro colunas: ssn (número do seguro social, que é semelhante a um número de identificação nacional), first_name, last_name e zip_code (código postal). Quando temos milhões de registros, a execução de uma consulta SQL para encontrar uma linha com base no sobrenome leva quase um minuto - mesmo em uma tabela tão simples.

Já imaginou esperar um minuto para que o aplicativo recupere as informações de que você precisa cada vez que o consulta? Neste exemplo, foram necessários 46 segundos para fazer a varredura de 40 milhões de registros. Não havia índice na coluna last_name, portanto, o sistema de banco de dados teve de ler todos os registros da tabela para verificar quais registros correspondiam a um sobrenome específico. Precisamos desesperadamente acelerar essa consulta SQL.

Nessa situação, criaremos um índice na coluna last_name. Isso acelerará a consulta SQL em três ordens de magnitude (aproximadamente 3.000 vezes mais rápida) para 15 milissegundos: um ganho enorme no desempenho da consulta SQL.

Para indexar um banco de dados, precisamos definir:

1. O nome do índice.
2. Quais colunas terão o índice.
3. O nome da tabela que contém essas colunas.

Lembre-se de que não devemos criar índices para cada coluna ou para cada combinação de colunas. Se fizermos isso, corremos o risco de destruir o banco de dados e criar um aplicativo que não responde quando os usuários estão adicionando ou atualizando registros.

Vamos ver como isso é feito.

Criação de um índice SQL

A sintaxe de criação de um índice é simples. Para criar um índice simples em uma coluna da tabela, usamos a instrução a seguir. Nesse caso, ele indexará a coluna zip_code da nossa person tabela:

CREATE INDEX index_zip
ON person (zip_code);

Não é complicado. Entretanto, lembre-se de que cada índice deve ser atualizado quando novas linhas são adicionadas e as linhas existentes são modificadas ou excluídas. Essas atualizações levam tempo; se você usar os índices em excesso, eles poderão fazer com que o banco de dados e o aplicativo fiquem mais lentos ou até mesmo muito lentos para os usuários.

Você também pode alterar os índices existentes ou descartá-los (removê-los), mas isso está fora do escopo deste artigo.

Há diferentes tipos de índices:

• Os índices exclusivos mantêm a integridade dos dados definindo que não há duas linhas na tabela que possam ter o mesmo valor para o índice exclusivo.
• Os índices primários são um tipo específico de índice exclusivo, mas só pode haver um por tabela. O índice primário é criado quando a tabela é criada. Como o próprio nome indica, ele é criado a partir da chave primária da tabela.
• Os índices secundários são índices adicionais que são criados sob demanda (usando CREATE INDEX) e podem ser descartados. Os índices secundários podem ter valores duplicados e não exclusivos (ou seja, podem ter mais de uma linha com o mesmo valor).
• Os índices compostos (índices de várias colunas) são índices que incluem várias colunas.

Os índices são importantes, mas o uso excessivo é tão prejudicial quanto o uso insuficiente ou a não utilização. Uma estratégia de indexação bem implementada é fundamental. Você pode evitar índices desnecessários entendendo como os dados em suas tabelas serão mantidos. Mas não deixe de incluir índices importantes com base em como os dados serão acessados e usados.

Práticas recomendadas de indexação SQL

Vamos nos aprofundar nas práticas recomendadas de indexação.

1. Não indexetodas as tabelas. Tabelas pequenas não requerem índices, pois uma varredura de tabela será mais eficiente do que procurar no índice e, em seguida, recuperar os dados da tabela.
2. Não indexetodas as colunas. Espero que isso seja óbvio nos exemplos anteriores. A indexação de todas as colunas aumenta a sobrecarga para manter esses índices atualizados e torna mais lentas outras operações do banco de dados. Indexe as colunas nas quais você filtra (ou seja, use com frequência nas cláusulas WHERE).
3. Não indexecolunas grandes. Um campo grande em sua tabela resultará em um índice grande.
4. Indexechaves estrangeiras. Isso melhora o desempenho do JOIN
5. Use índices de várias colunas somente quando for apropriado. Os índices de várias colunas são ótimos. (Lembre-se de nosso exemplo de indexação no nome e no sobrenome para que possamos consultar com eficiência essa combinação). Entretanto, os índices compostos são mais desafiadores, pois é preciso considerar a ordem das colunas dentro do índice. Devemos criar um índice sobre o nome e o sobrenome ou sobre o sobrenome e o nome? Esses são dois índices diferentes. Qual deles terá um desempenho mais eficiente? A resposta dependerá das consultas SQL. Em geral, um índice de coluna única é suficiente (e economiza tempo).
6. Use índices para pré-classificar dados. A classificação repetida de dados pode ser evitada quando um índice é adicionado com a ordem de classificação (ou seja, ascendente ou descendente).
7. Certifique-sede que tudo esteja funcionando conforme o esperado. Use o EXPLAIN PLAN para verificar se os índices estão ajudando.

Agora que temos uma estratégia para criar índices, vamos considerar como mantê-los e ajustá-los. É necessário monitorar os índices: verifique as estatísticas do índice para entender o uso dos índices e verifique o plano de consulta para analisar como as consultas SQL estão usando os índices.

A manutenção de índices SQL envolve a reorganização ou a reconstrução de índices. A reorganização de um índice é menos intensiva do que uma reconstrução completa. Tanto a reorganização quanto a reconstrução de um índice consomem muitos recursos e podem ou não melhorar o desempenho. Portanto, analise seus índices antes de mantê-los. Não presuma que a manutenção de um índice melhorará o desempenho da consulta SQL.

O que vem a seguir com a indexação SQL?

Agora que já explicamos o que são índices e as práticas recomendadas para criá-los e mantê-los, a próxima etapa é se aprofundar nos índices. Nosso curso Understanding Indexes explica como os índices funcionam e orienta você sobre como e quando criar um índice. Seus exercícios práticos o ajudarão a compreender rapidamente o que é necessário para manter suas consultas rápidas e eficientes. Bom aprendizado!