Aumentando a performance de consultas SQL adicionando índices e removendo Key Lookups
E aí galera, tudo certo?
Nesse artigo iremos realizar uma otimização de performance e consumo de recursos no SQL Server com o uso de índices, com o adendo de nos atentarmos aos “Key Lookups”. Bora?!
Antes de qualquer coisa, caso você não saiba o que é um índice de banco de dados, pode dar uma olhada nesse cara aqui.
A ideia, é fazer uma demonstração de maneira prática, pois se fossemos nos atentar a cada detalhe, e focar na teoria disso tudo, íamos nos estender muito e ficar bem cansativo, então vamos direto ao ponto.
Imagine que em um banco de dados qualquer, tenhamos uma tabela de Cliente, criada da seguinte maneira:
Simples, certo? Visualizando de maneira gráfica, a tabela fica assim:
Para realizarmos nossos testes, vamos popular essa tabela com 1.000.000 (um milhão) de registros. Para fazer isto rodamos o seguinte comando:
Abaixo, vamos rodar um comando para verificar a quantidade de registros na tabela e também faremos a seleção dos 10 primeiros registros para verificarmos como ficaram nossos dados:
O resultado é o seguinte:
Com a base populada, vamos alterar um registro qualquer, somente para fins didáticos, para procurar por um e-mail mais “bonitinho” do que um combinado de caracteres, veja:
A consulta que vamos trabalhar é a que vou exibir a seguir, não vamos mexer em nada na query em si, somente nos índices, vejam a query:
Primeiro Problema
Logo de cara, temos o seguinte plano de execução:
Com os seguintes detalhes:
Reparem na propriedade “Number of rows read”, que é 1.000.000, ou seja, todos os registros da tabela, isso significa que o banco de dados faz mais ou menos isso aqui:
O mecanismo percorre registro por registro “perguntando” se a linha em questão possui o e-mail que estamos buscando, e quando a condição é satisfeita, o registro é retornado para a consulta.
Vejam que para retornar esse registro localmente e sem concorrência, o banco de dados precisou de 280ms, e percorreu toda a tabela, gerando um custo de I/O consideravelmente alto.
Primeira solução
Para sanarmos esse problema, de percorrer a tabela toda, vamos criar um índice pelo campo “Email”, que funciona como um índice de um livro mesmo, mostrando fisicamente onde fica esse registro na tabela facilitando a busca. Para criar o índice, utilizamos o seguinte trecho de código:
A sintaxe é simples, um índice precisa ter um nome, uma tabela e um ou vários campos a serem indexados.
Se executarmos a mesma query:
Com tal alteração, o plano de execução fica dessa maneira:
Agora podemos observar que a consulta é realizada em duas partes.
O problema foi resolvido, pois o tempo de leitura foi para 0ms para cada parte da consulta, e o número de linhas lidas foi para 1, ou seja, já economizamos tempo e I/O do nosso banco de dados.
As duas etapas que são feitas na consulta são:
1ª — Busca do registro na tabela utilizando o índice “IDX_ClienteEmail” (que é o que a gente queria que acontecesse, né?);
2ª — Busca do registro utilizando a PK da tabela, que é o Id. (Why?);
Segundo problema
Mas porque isso acontece?
Vou exemplificar abaixo para entendermos melhor, vejam:
Explicando a imagem acima:
Na primeira parte, que é a busca pelo índice que criamos, o banco de dados percorre cada nó para verificar se a condição que queremos é satisfeita (Email igual ao que estamos buscando), porém, o nosso índice não “encontra” todos os atributos da tabela, ele encontra apenas o campo “Email”, que é o campo indexado, e a PK da tabela, que é o campo Id.
Como em nossa query buscamos os campos: “Id”, “Nome”, “Sobrenome” e “Email”, o banco de dados precisa percorrer a tabela novamente para encontrar os demais atributos que precisamos buscar, chamado de “Key Lookup”. Ou seja, eu busco a chave primária pelo índice que eu preciso, e depois preciso buscar o resto dos dados olhando para a chave primária.
Só que dessa vez, não precisamos ir de linha a linha, pois temos o Id exato que queremos buscar. Por isso a busca pela PK, que é nosso índice clusterizado, e que retorna todos os atributos da tabela.
Deu pra entender até aqui? Espero que sim!
Solução final
Em certos casos, o banco de dados “prefere” nem utilizar o índice por conta do Key Lookup, pois é uma tarefa muito custosa.
Para removermos isso, e buscarmos todos os atributos que precisamos da tabela de uma vez só, vamos utilizar o comando “INCLUDE”, vejam:
Vejam que incluímos os campos que precisamos buscar dentro de “INCLUDE”, com isso conseguiremos remover o Key Lookup.
A termo “DROP_EXISTING = ON” é somente para excluirmos o índice que já existe e substituir pelo novo.
E quando executamos a query:
Vejam como fica o plano de execução:
E nos detalhes:
Ou seja, a nossa consulta agora tem apenas 1 etapa, que é somente a busca pelo índice “IDX_ClienteEmail” que busca dados da tabela “Cliente” pelo campo “Email”, lê apenas 1 linha em 0ms. Isso quer dizer que o consumo de recursos é mínimo e o tempo também.
Para exemplificar, esse índice funciona dessa maneira:
Não precisamos fazer uma busca pela PK, pois os atributos da tabela que precisamos já estão incluídos no próprio índice.
Os códigos utilizados estão disponíveis no GitHub: https://github.com/vmussak/performance-sql-index-key-lookup
Por hoje é isso! Até mais 😉🤘
