Não sobrecarregue seu banco de dados | SQL Tuning

Consultas SQL lentas prejudicam uma aplicação diretamente em sua camada mais baixa, sendo impossível compensar essa perda de performance com otimização de código ou até mesmo na camada HTTP. Qualquer desenvolvedor que preze pela qualidade do produto e experiência do usuário deve manter suas consultas o mais performáticas possível, tendo como recompensa o consumo menor de recursos do servidor, a alegria do cliente, e consequentemente mais faturamento.

5 Técnicas de Otimização em consultas SQL

Neste artigo utilizaremos exemplos reais com o banco SQLite, você pode realizar os mesmos testes feitos aqui, para isso, com o SQLite3 instalado no seu sistema, rode no terminal:

sqlite3 teste.db

Agora que você já esta dentro do banco, crie a tabela transacoes:

CREATE TABLE IF NOT EXISTS transacoes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cliente_id INTEGER NOT NULL,
    data_transacao TEXT NOT NULL,
    valor REAL NOT NULL,
    status TEXT DEFAULT 'pendente'
);

Utilizando a query abaixo, vamos adicionar 5 milhões de registros na nossa tabela (importante para simular um sistema de médio porte e facilitar a visualização de performance ao executarmos queries).

BEGIN TRANSACTION;

WITH RECURSIVE gerador(x) AS (
SELECT 1
UNION ALL
SELECT x + 1 FROM gerador LIMIT 5000000
)
INSERT INTO transacoes (cliente_id, data_transacao, valor, status)
SELECT
ABS(RANDOM() % 10000) + 1,
datetime('2023-01-01', '+' || (ABS(RANDOM() % 1095)) || ' days'),
ABS(RANDOM() % 500000) / 100.0,

    CASE ABS(RANDOM() % 3)
        WHEN 0 THEN 'pago'
        WHEN 1 THEN 'pendente'
        ELSE 'cancelado'
    END

FROM gerador;

COMMIT;

E para finalizar, execute o seguinte comando para visualizar o tempo de execução das queries:

.timer on

1. Não use SELECT *

Se em algum momento você necessita somente de uma ou duas colunas da sua tabela, é desperdício de processamento recuperar todas. Imagine que você só precise calcular o valor total de transações de um certo cliente dentro da sua aplicação, porém sua query executa da seguinte forma:

SELECT * FROM "transacoes" WHERE "cliente_id" = 1;

Como resultado tivemos uma consulta realizada no seguinte tempo:

Run Time: real 1.116259 user 0.187500 sys 0.265625

Porém, ao buscarmos somente o campo que nos interessa, que é o valor:

SELECT "valor" FROM "transacoes" WHERE "cliente_id" = 1;

Conseguimos reduzir o tempo de execução em 41%:

Run Time: real 0.657894 user 0.156250 sys 0.234375

2. Filtre no WHERE antes de utilizar o HAVING

Para continuar nossos experimentos, crie a tabela de clientes e adicione alguns registros:

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS clientes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    email TEXT NOT NULL
);

WITH RECURSIVE gerador(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM gerador LIMIT 10000
)
INSERT INTO clientes (nome, email)
SELECT
    'Cliente ' || x,
    'contato' || x || '@empresa.com.br'
FROM gerador;

COMMIT;

O cenário em questão é onde você precisa de um relatório que mostre o total de dinheiro gasto por cada cliente, porém só iremos filtrar as transações que estão com o status 'pago'.

Nesse momento não vamos olhar para tempo de execução, mudaremos nosso foco temporariamente para esforço computacional. Primeiro, utilize o seguinte comando para termos um Raio-X da query:

.stats on

Uma abordagem ruim seria realizarmos a soma de todas as transações de cada cliente, e depois descartarmos as que não estão pagas, desta forma:

SELECT "cliente_id", SUM("valor") AS total_gasto FROM "transacoes"
GROUP BY "cliente_id", "status"
HAVING "status" = 'pago';

Salvamos as informações que o .stats on nos deu sobre a query, e agora iremos executar uma query otimizada para realizarmos a comparação. Veja que não utilizamos o HAVING desta vez, nenhuma transação que não for paga será processada:

SELECT "cliente_id", SUM("valor") AS total_gasto FROM "transacoes"
WHERE "status" = 'pago'
GROUP BY "cliente_id";

Em termos de tempo de execução, não teremos um déficit tão grande, mas vamos analisar as seguintes informações que o comando .stats on nos deu:

• Virtual Machine Steps: 43.495.528 vs 40.159.586: É a quantidade de instruções que a CPU teve que executar, uma queda de ~3 milhões de instruções pode não ter sido tão significativa neste contexto, porém escalando isso a apenas 100 usuários realizando essa consulta por minuto, são 300 milhões de instruções desnecessárias que podem prejudicar várias pontas do seu servidor.
• Temporary data spilled to disk: 1.287.989.204 bytes vs 46.660.455 bytes: Como na primeira query o banco agrupou os 5 milhões de registros antes de filtrar, a memória RAM do banco encheu, e quando essa memória RAM se esgota o banco começa a usar o SSD/HD como memória temporária (Swap), ou seja, ele gravou ~1.2 GB de registros desnecessários para em seguida jogar tudo fora no HAVING. Esse comportamento é extremamente destrutivo em infraestruturas com recursos limitados. Por fim podemos fazer a comparação com a query otimizada, que utilizou apenas 46 MB (96% menor a necessidade de espaço).

Outras estatísticas importantes que você pode se aprofundar mais em um momento futuro são:

• Page cache hits
• Page cache misses
• Page cache writes
• Page cache spills

Elas tratam sobre escrita e leitura em memória volátil (RAM) e memória persistente (SSD/HD).

3. Utilize LIMIT sempre que possível

Não é sensato extrair do banco mais dados que o necessário, se eu só necessito de uma amostra, não devo trazer diversos registros iguais. Se estiver realizando uma listagem de dados em alguma interface, devo utilizar paginação. Você alivia a carga do banco ao limitar a quantidade de resultados. Além disso, a largura de banda necessária para enviar esses registros para a aplicação cliente também será menor.

Imagina o cenário no qual realizo uma listagem de clientes na minha aplicação WEB, e retorno todos os clientes de uma só vez:

SELECT "id", "nome" FROM "clientes";

Com certeza será algo custoso em questão de tempo para meu banco:

Run Time: real 6.934765 user 0.296875 sys 0.796875

Porém na minha listagem, eu tenho 400 abas que mostram apenas 25 clientes por vez, por esse motivo eu posso retornar os dados sob demanda, conforme o usuário avança entre as abas:

SELECT "id", "nome" FROM "clientes"
LIMIT 25;

Obviamente o tempo cairá drasticamente:

Run Time: real 0.042842 user 0.000000 sys 0.000000

4. N+1 Dentro do banco de dados

Em um cenário em que você precisa listar os 10 mil clientes e junto trazer o total de transações que cada um já fez em todo o período, é possível DESTRUIR sua aplicação executando uma query dessa forma:

SELECT c."nome",
       (SELECT COUNT(*) FROM "transacoes" t WHERE t."cliente_id" = c."id") AS total_movimentacoes
FROM "clientes" c;

No melhor dos casos a query seria cancelada e seu servidor ficaria de pé, executar isso seria de um custo inestimável para o banco, pois a cada cliente, será necessário varrer 5 milhões de linhas para buscar os registros que lhe pertencem. Serão 10 mil Full Table Scans.

Felizmente para resolver é bem simples:

SELECT c."nome", COUNT(t."id") AS total_movimentacoes
FROM "clientes" c
LEFT JOIN "transacoes" t ON t."cliente_id" = c."id"
GROUP BY c."id", c."nome";

Este cenário é conveniente se a listagem é feita em momentos esporádicos, se essa consulta tem uma grande recorrência, é indispensável a criação de um INDEX para a coluna cliente_id.

5. Não realizar queries pesadas em horário de pico

Se você necessita periodicamente realizar consultas custosas para análise ou qualquer que seja o objetivo, realize esse tipo de consulta fora dos horários de pico da sua aplicação. Dessa forma você preserva os seus usuários, e não compete com eles por recursos do servidor. Geralmente durante a madrugada é o horário mais apropriado para esse tipo de trabalho.