41 Dicas de SQL para desenvolvedores: Soluções práticas para desafios comuns

Se você já se viu travado tentando resolver problemas de performance ou corrigir comportamentos inesperados em consultas SQL, não está sozinho. Situações como condições de corrida ou a necessidade de otimizar índices são desafios frequentes, mas, assim como os "padrões de projeto" em programação, há também "padrões de soluções" que podem simplificar essas dificuldades.

Vou citar algumas dicas nesta publicação, mas recomendo conferir a lista completa no livro gratuito (em troca do seu e-mail) e explorar algumas dicas com mais detalhes no site. Essas dicas podem servir como uma referência prática para quando essas questões surgirem no seu caminho. Elas podem ajudar você a lidar com problemas que talvez nem soubesse que existiam.

O livro foi escrito por Tobias Petry em 2021, que começou a compartilhar dicas e truques de banco de dados no Twitter (agora, X) para outros desenvolvedores, e com o passar dos meses foi ganhando milhares de seguidores. Com isso, decidiu juntar as dicas num livro para não ficarem num formato efêmero como estavam na rede social.

Bloqueio transacional para evitar condições de corrida

Fonte

A primeira dica que escolhi trazer para cá é simples, mas ela é tão útil que também será utilizada na última dica desta publicação.

Sempre que vamos atualizar linhas em um banco de dados, uma solicitação é validada, a linha indicada é carregada na memória, mesclada com os novos dados e, finalmente, salva no banco de dados novamente. Como os servidores podem executar várias solicitações simultaneamente, eventualmente iremos nos deparar com uma condição de corrida, onde as validações e suposições executadas não são mais válidas por causa dos dados da nova linha naquele momento.

Para resolver esse problema, as linhas podem ser carregadas com um bloqueio exclusivo do banco de dados dentro de uma transação com a extensão especial FOR UPDATE. Apenas uma transação poderá obter o bloqueio exclusivo. Todos os bloqueios são liberados automaticamente quando a transação é confirmada (commit), revertida (rollback) ou a conexão é encerrada/perdida, o que resulta em uma reversão.

START TRANSACTION;

SELECT balance FROM account WHERE account_id = 7 FOR UPDATE;

Condições fantasmas para colunas não indexadas

Fonte

Índices perfeitos não podem ser criados para cada consulta por causa de sua sobrecarga. A solução é adicionar condições fantasmas a essas consultas, o que pode levar o banco de dados a usar índices melhores.

Condições fantasmas são suportadas por índice e não alteram os resultados. Essas condições são adicionadas apenas para ajudar o banco de dados a encontrar a maneira mais eficiente de consultar os dados. Para definir uma condição fantasma, precisamos entender o domínio.

Vamos usar como exemplo um filtro aberto que o usuário pode escolher diferentes combinações, e que gerou essa consulta:

SELECT *
FROM parcels
WHERE status = 'open' AND insurance = 1;

Nesse caso, o banco de dados pode usar o índice em status ou insurance para encontrar linhas correspondentes para a condição selecionada pelo usuário. No entanto, qualquer um dos índices é muito amplo, então muitas linhas ainda terão que ser filtradas após a aplicação do índice. Um índice de várias colunas em ambas as linhas não deve ser adicionado porque é uma combinação rara de filtros selecionados.

Ao aplicar o conhecimento de domínio, o fato de que qualquer parcela segurada (insurance = 1) é sempre do type AD ou GV pode ser inferido. A consulta agora será executada muito mais rápido porque um índice existente em status e type é aplicável por causa da nova condição. Adicionar condições fantasmas pode resultar no banco de dados usando índices mais eficientes, mas não deixará as consultas mais lentas. A única exceção são as consultas somente de índice, que são muito raras na realidade.

SELECT *
FROM parcels
WHERE status = 'open' AND insurance = 1 AND type IN('AD', 'GV');

Índices descendentes

Fonte

Para índices de coluna única, você não precisa alterar a direção de ordenação do índice, pois um índice pode ser escaneado para frente e para trás. Mas para índices de várias colunas com ordenação mista, especificar a ordenação correta das colunas fará uma grande diferença no desempenho.

SELECT * FROM highscores ORDER BY score DESC, created_at ASC LIMIT 10;

-- Índice não será utilizado para ordenação:
CREATE INDEX highscores_wrong ON highscores (score, created_at);

-- Índice será utilizado para ordenação:
CREATE INDEX highscores_correct ON highscores (score DESC, created_at ASC);

Índices baseados em função

Fonte

Uma expressão como WHERE LOWER(email) não usará um índice criado para a coluna email. Ao criar um índice baseado em função (índice funcional), você pode usar essa condição com suporte total a índices.

-- Não usará o índice:
CREATE INDEX users_email ON users (email);
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- Usará o índice:
-- MySQL (a função precisa estar entre parênteses)
CREATE INDEX users_email_lower ON users ((lower(email)));
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- PostgreSQL
CREATE INDEX users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'test@example.com';

Evitar problemas de bloqueio para atualizações em contadores

Fonte

Como o único problema é atualizar um único contador simultaneamente, ele pode ser duplicado muitas vezes para que cada consulta esteja atualizando um contador diferente. A abordagem para fazer isso é simples:

1. Crie uma nova tabela que armazenará muitos contadores para, por exemplo, cada visualização de página, substituindo o único contador por uma coluna fanout que distribuirá os bloqueios:

CREATE TABLE pageviews_counts (
  url varchar(255) PRIMARY KEY,
  fanout smallint NOT NULL,
  count int
);

CREATE UNIQUE INDEX pageviews_slots ON pageviews (
  url, fanout
);

2. Substitua a instrução update por uma instrução insert. Para não inserir milhares de novas linhas naquela tabela, elas são salvas para um parâmetro fanout específico (por exemplo, 100 contadores) e incrementadas quando já existem. A probabilidade de contenção de bloqueio foi reduzida em 100 vezes.

-- Antes:
UPDATE pageviews SET count = count + 1 WHERE url = '/home';

-- Depois:
INSERT INTO pageviews_counts (
  url, fanout, count
) VALUES (
  '/home', FLOOR(RAND() * 100), 1
) ON DUPLICATE KEY UPDATE count = count + VALUES(count);

3. Mova periodicamente as contagens resumidas da tabela pageviews_counts para a tabela pageviews.

Essa abordagem é uma melhoria significativa para atualizar diretamente o valor do contador. No entanto, ainda pode haver contenção de bloqueio ao inserir a mesma linha. Uma maneira de reduzir essa probabilidade é aumentar o fanout para distribuir o processo de contagem em mais linhas. Como alternativa, você pode cooperar com o sistema de bloqueio do banco de dados usando uma transação e tentando atualizar um contador que não está bloqueado no momento:

4. Você solicita ao banco de dados as linhas de contadores existentes que não estão bloqueadas por nenhuma outra consulta ou transação:

SELECT fanout
FROM pageviews_count
WHERE url = '/home'
LIMIT 1
FOR UPDATE SKIP LOCKED

5. Quando uma linha é retornada, ela é usada com uma consulta UPDATE simples:

UPDATE pageviews_counts
SET count = count + 1
WHERE url = '/home' AND fanout = 4

6. Quando nenhuma linha é retornada, todos os parâmetros fanout existentes são bloqueados ou não existe nenhuma linha. Você tem que voltar para a abordagem padrão:

INSERT INTO pageviews_counts (
  url, fanout, count
) VALUES (
  '/home', FLOOR(RAND() * 100), 1
) ON DUPLICATE KEY UPDATE count = count + VALUES(count);

Aviso: Você não precisa usar seu banco de dados principal para tudo. Para este exemplo, um servidor Redis teria sido uma escolha válida, que pode facilmente fazer dezenas de milhares de incrementos a cada segundo sem nenhuma otimização necessária.

Outras dicas

Como eu disse, existem muito mais dicas no site e no livro. Elas, inclusive, podem ser combinadas, como aconteceu com a primeira e última dica que citei.

Assim como ao conhecer padrões de projeto, o ganho em conhecer essas dicas é desenvolver a habilidade de adaptar e aplicar soluções em diferentes contextos, onde fizer sentido, e não apenas copiar e colar.