Otimizando aplicações com Bancos de Dados — Índices
“Vou ali tomar um café, enquanto a consulta processa”.
Se você já usou ou ouviu alguém usando esta frase batida, sabe o quanto é irritante esperar uma consulta demorada.
Diversos fatores podem provocar a demora, do lado da aplicação, do lado do banco de dados ou quando há um desequilíbrio entre os dois.
Primeiro, vamos entender como funciona a execução de consultas realizada pelo SGBD (Sistema de Gerenciamento de Banco de Dados).
À grosso modo ela segue os passos abaixo:
Tradução: onde a consulta é revisada para identificar erros de sintaxe e posteriormente transformada em expressão algébrica.
Otimização: consiste na pesquisa da melhor alternativa de seleção dos dados solicitados, em um determinado espaço de tempo. Não é a super melhor, é a melhor, dentro de algumas possibilidades.
Definição do plano de execução: uma vez construído o roteiro para a obtenção dos dados, é criada uma estratégia, conhecida como plano de execução estimado. Normalmente, quando analisamos a performance de uma consulta, começamos estudando exatamente este plano.
Geração do código: como tudo em informática funciona através de scripts, o plano é traduzido para um bloco de código e, por fim, é feito o …
Processamento: momento em que tudo o que foi planejado pelo SGBD é de fato executado e o resultado é apresentado. Geralmente, é responsável pela maior parte da demora que justifica a frase que iniciou este artigo.
O que devemos fazer é facilitar as coisas para que a consulta tenha possibilidade de encontrar uma estratégia mais rápida.
Para entender melhor, vamos focar agora no…
Otimizador de consultas
Curiosamente, o termo otimizador não está perfeitamente aplicado, porque, este cara não busca o caminho ótimo (o super extra top bodykripton tudo de melhor para sempre, amém). Ele rastreia o melhor, dentro de um curtíssimo espaço de tempo. Claro, se neste curto espaço de tempo, ele encontrar o melhorzão, maravilha! Porém, se ele perseguir o melhor, entre todos os possíveis, poderá demorar mais tempo nessa tarefa do que na própria execução da consulta.
O otimizador faz mais ou menos o que o google maps faz quando você solicita uma rota (Figura 1). Ou seja, identifica algumas alternativas e sugere a mais rápida, de acordo com seus critérios.
Nem sempre ela é a melhor, o maps pode desconhecer algumas estradas novas, a prefeitura asfaltou só hoje a estrada principal, ou uma carroça, carregada com cofos de jaca, minério de ferro e manga verde, acabou de entrar na via estreita que ele sugeriu. Mas, dentro do possível, ele vai indicar o melhor caminho.
No exemplo abaixo, utilizamos como objetivo, o trajeto entre o Pátio Norte e a Black Swan. Não será possível visualizar a carroça carregada, mas ela está em um dos trechos em vermelho.
Observe na figura 2 que, dependendo do horário, o “melhor” caminho irá variar. Isto vale também para o banco de dados. Dependendo do volume de dados em cada tabela da consulta, o plano de execução poderá mudar.
Porém, para ter bons caminhos, precisamos de boas estradas.
Aí é que entra um ponto fundamental.
Planejamento de índices.
Ok, quando desenvolvemos uma aplicação, dificilmente saberemos todas as consultas que faremos. Portanto, existe um planejamento inicial e a revisão das suas tabelas e índices, de acordo com as novas necessidades.
Para que serve um índice?
Imagine que você é um carteiro e está entregando algumas encomendas na casa X da Rua Tal.
Se a rua foi planejada corretamente, as casas estarão com os números em alguma ordem, tomara que não seja na ordem not (alfabética). Se as casas não estiverem na ordem, você precisará passar em todas as casas para saber onde está a casa X que você procura. Ah! Mas a primeira X que eu encontrar já termina a busca, certo? Quem garante que só tem uma X?
Em suma, se você precisar realizar uma consulta por nome em uma tabela, por exemplo, e não existir esta ordem nela, ele será obrigado a pesquisar em toda a tabela pelo nome solicitado. A esta ordem damos o nome de índice.
Podemos ter quantos índices precisarmos em uma tabela, um índice por nome, outro por código, outro por CPF, e por aí vai. Cada um, corresponderá a uma tabela de índice.
Opa! Resolvido então. Vou abarrotar minhas tabelas com índices até rachar o couro.
Pois é, mas tudo tem um preço.
Todas as vezes em que uma tabela é alterada, os índices precisarão ser atualizados. E, nesse caso, quanto mais índices, mais trabalho o banco terá para realizar essa alteração, além da necessidade de mais espaço em disco.
Precisamos, então, encontrar um equilíbrio entre a real necessidade de adição de índices e um trabalho de escrita da consulta.
Outro ponto importante sobre os índices é o
Key Lookup
Voltemos à Rua Tal. Você deu sorte e a rua está perfeitamente organizada. Logo, rapidamente você encontra a casa X. Porém (como alegria de pobre dura pouco), nesta casa você entregará três caixas, mas só conseguiu colocar uma nas mãos. Para concluir a entrega, você precisará voltar no seu carro e buscar as outras duas caixas. Em banco de dados, isto é conhecido como Key Lookup.
Você solicitou os campos nome e CPF do cliente de código X. O otimizador de consultas achou um índice para o campo código e rapidamente encontrou o cliente de código X. Porém, ele ainda precisa levar o nome e o CPF. Estes campos não estão na tabela de índice, estão na tabela principal. Daí, é feito o Key Lookup, para ir na tabela principal, do código encontrado, para enfim, trazer os demais campos.
Isto pode ser resolvido através da cláusula include, com ela, você adiciona na tabela de índice os campos que normalmente serão necessários para as consultas daquela tabela, evitando, claro, incluir a tabela inteira no índice, vale novamente o equilíbrio.
No exemplo, ficaria algo como CREATE INDEX INDICE ON TABELA (CODIGO) INCLUDE (NOME, CPF).
No postgres, o plano de execução utiliza o Index Scan quando precisa fazer o key lookup e o Index Only Scan quanto tudo o que ele necessita está presente no índice.
Ordenação
É muito comum apresentarmos os dados de nossas consultas de forma ordenada. Até aí, tudo bem. Afinal, nosso usuário também gosta de achar rapidamente as informações. O problema é o banco de dados fazer essa ordenação “no braço”, enquanto processa diversas outras requisições. Quando a ordenação está alinhada com o índice utilizado para selecionar os dados, perfeito! Os dados já cairão dentro da ordem. De outra forma, o banco precisará fazer a operação de sort após a seleção dos dados. Neste caso, na grande maioria das vezes, é preferível fazer essa ordenação na aplicação.
Exemplo no Postgres:
O comando da Figura 3 retorna o plano de execução da Figura 4.
Ou seja, para retornar os campos cd03_id, cd03_revisao e cd03_descricao de todos os produtos que atendem ao critério (< 11368190), o banco seguiu a seguinte ordem:
- Pesquisou no índice cd03_pkey (cd03_id, cd03_revisao), todos os produtos com a id < 11368190 e os separou.
- Foi na tabela cd03_produto e buscou o campo cd03_descricao para cada produto destacado.
- Ordenou esse resultado pelo campo cd03_revisão.
Vamos fazer uma pequena mudança, retirando o campo cd03_descricao da consulta. O resultado observamos na figura 5.
Não apenas o tamanho das linhas (width) diminuiu, como também, todos os dados necessários para a consulta foram coletados do índice cd03_pkey (observe o Index Only Scan). Se for necessário o campo cd03_descrição, você pode incluí-lo no índice cd03_pkey, ficando o índice com os campos: cd03_id, cd03_revisao e cd03_descricao.
Vamos agora fazer outra alteração simples, mudaremos a ordem para o campo cd03_id, ficando order by cd03_id (este é o campo utilizado na cd03_pkey). A consulta final observamos na figura 6, e seu resultado vemos na figura 7.
Adeus Sort! Qual o motivo do sort ter sumido? Simplesmente, porque o índice cd03_pkey, utilizado no Index Only Scan, já é ordenado pelo campo cd03_id. Desse modo, quando conclui a seleção dos registros nesse índice, eles já estão na ordem necessária. Caso você precise disponibilizar a ordem por cd03_descricao ou cd03_revisao pode oferecer direto na aplicação.
Espero ter ajudado!
