Otimizando Banco de Dados com Index

INTRODUÇÃO

Eu estava assistindo uma das aulas do curso.dev que mostra a conexão com o banco de dados através da aplicação e me deparei com um comentário que perguntava como os dados são armazenados no banco de dados. AlvaroVargas teve a curiosidade de pesquisar mais a fundo e encontrou os seguintes vídeos sobre o assunto: Vídeo 1 e Vídeo 2

O primeiro vídeo me interessou o suficiente para que eu assistisse o seguinte (Vídeo 3) do mesmo autor, que fala sobre o funcionamento dos índices (INDEX). Ele me lembrou da experiência que tive implementando índices em um projeto, e esclareceu ainda mais o seu funcionamento.

Por isso, gostaria de compartilhar aqui a minha experiência:

O CONTEXTO

Na empresa onde eu trabalho, temos um banco de dados PostgreSQL em produção, que possui uma tabela de Logs. Ela possui muitas colunas, mas vou resumir em 4 campos: id, data, usuario_id, tarefa_id.

Para simplificar o exemplo, vamos considerar que é um sistema de TodoList em que os usuários possuem registros (Logs) no sistema, e as tarefas criadas por eles também possuem Logs.

Somando todos esses registros, a tabela possui cerca de 10 milhões de linhas... Sim, eu ainda fico surpreso com essa quantidade mas, para empresas grandes, esse quantitativo deve ser bem maior.

Conforme os vídeos, quando a consulta é realizada pelo id, o banco de dados faz uma pesquisa na B-TREE (Self Balancing Tree), o que me traz o respectivo Log em poucos millisegundos.

SELECT * FROM Logs WHERE id = 123
Resultado: 1 Log (0,078 segundos)

O problema acontece quando a consulta é realizada pelos demais campos. Vamos listar todos os Logs gerados pelo usuário 456

SELECT * FROM Logs WHERE usuario_id = 456
Resultado: 300000 Logs (2 segundos)

OTIMIZANDO O PRÓPRIO SELECT

Agora, digamos que esse usuário criou uma Tarefa de id 789 , e queremos ver seus Logs.

SELECT * FROM Logs WHERE usuario_id = 456 AND tarefa_id = 789
Resultado: 5 Logs (2 segundos)

O que acontece nesse caso, em que temos duas condicionais é que:

1. A consulta busca por todos os Logs cujo usuario_id é igual a 456
2. Dentre os Logs retornados com base no primeiro filtro, o banco vai buscar pelas linhas que possuem a tarefa_id igual a 789

Em outras palavras, nesse SELECT,

1. O banco filtra os 10 milhões de Logs em 'apenas' 300 mil registros que possuem o usuario_id 456.
2. Desses 300 mil registros, ele faz a busca por Logs que possuem a tarefa_id igual a 789. Como a Tarefa foi criada recentemente, ela possui no máximo 5 registros.

Perceba que o banco de dados filtra os registros na mesma ordem das condicionais definidas no SELECT. Para otimizar a consulta, temos que fazer a seguinte pergunta:

"É mais fácil buscar 5 logs de uma Tarefa dentre os 300 mil logs de um Usuário ou buscar no máximo 5 logs de Usuário dentre os 5 logs de Tarefa?"

Para responder a isso, vamos fazer o teste de inverter a ordem das condicionais.

Query a partir de usuario_id:
SELECT * FROM Logs WHERE usuario_id = 456 AND tarefa_id = 789
Resultado: 5 Logs (2 segundos)

Query a partir de tarefa_id:
SELECT * FROM Logs WHERE tarefa_id = 789 AND usuario_id = 456
Resultado: 5 Logs (1 segundo)

Com isso, podemos concluir que devemos construir nossas consultas considerando qual coluna possui menos registros, e elaborar a consulta nessa ordem de menos -> mais registros.

OTIMIZANDO CONSULTAS COM INDEX

Apesar de otimizar boa parte das consultas seguindo essa boa prática, ainda pode não ser o suficiente para determinados casos. É nesse ponto que entra o conceito de Index.

No começo deste artigo, expliquei que as tabelas de bancos de dados são armazenadas com base no id (Primary Key) em uma B-TREE. Por sua vez, isso não acontece com as demais colunas, o que obriga o banco de dados a pesquisar por todos os registros da tabela caso eu faça uma consulta pelo usuario_id e tarefa_id por exemplo.

Como a consulta por esses dois campos é frequente no contexto do sistema, que se baseia em Tarefas, é interessante que essa consulta seja otimizada para que o sistema tenha um melhor desempenho.

Para isso, podemos usar um recurso dos bancos de dados que possibilita a criação de Indexes além da chave primária. Podemos criar um índice através deste comando:

CREATE INDEX idx_tarefa_usuario ON Logs USING btree (tarefa_id DESC NULLS LAST, usuario_id DESC NULLS LAST);

Esse comando cria um Index na tabela Logs usando a estrutura BTREE. Nesse Index, configuramos as 2 colunas tarefa_id e usuario_id com a ordenação DESC (maior para menor), com os registros nulos ficando por último NULLS LAST.

Dessa forma, na hora de executar o SELECT de tarefa_id e usuario_id, o banco primeiro consultará essas duas colunas na tabela auxiliar criada pelo INDEX, o qual retornara o id dos respectivos Logs.

Após encontrar o registro no INDEX o banco buscará os registros de Logs através do id, que, como vimos no começo do artigo, retorna os registros em poucos milisegundos (0,078 segundos).

Agora, com o INDEX criado, vamos executar as mesmas consultas de antes:

SELECT * FROM Logs WHERE usuario_id = 456 AND tarefa_id = 789
Resultado: 5 Logs (0,078 segundos)

SELECT * FROM Logs WHERE tarefa_id = 789 AND usuario_id = 456
Resultado: 5 Logs (0,078 segundos)

CONCLUSÃO

Como podemos ver, a criação do index otimizou até a primeira consulta, que antes levava o dobro do tempo da segunda. Isso ocorre porque a B-TREE realiza a consulta através de 'saltos' em grupos de registros (Nodes), o que igualou o tempo de acesso para as duas colunas.

Além disso, a tabela auxiliar criada pelo INDEX contém somente os Logs que possuem tanto a tarefa_id quanto o usuario_id, o que exclui os registros do usuario_id que não têm relação com uma tarefa. Por isso, é uma boa prática selecionar duas ou três colunas que aparecem várias vezes juntas e, de preferência, que sejam chaves estrangeiras (Foreign Keys).

Obs.1: É possível criar os índices com outros tipos além do BTREE, sendo eles Hash, GiST, GIN, e outros. Cada um possui vantagens e desvantagens.

Obs.2: A criação de índices torna os SELECTs na tabela mais rápidos nos campos definidos, mas reduz o desempenho do INSERT, UPDATE e DELETE, pois alterações nas tabela principal precisam ser refletidas na tabela auxiliar criada pelo INDEX.