Executando verificação de segurança...
30

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) = '[email protected]';

-- 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) = '[email protected]';

-- PostgreSQL
CREATE INDEX users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = '[email protected]';

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
);
  1. 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);
  1. 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:

  1. 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
  1. Quando uma linha é retornada, ela é usada com uma consulta UPDATE simples:
UPDATE pageviews_counts
SET count = count + 1
WHERE url = '/home' AND fanout = 4
  1. 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.

Carregando publicação patrocinada...
3

Obrigado por compartilhar!

Já topei com algumas em outros lugares, mas outras são novidade. Realmente, existe um universo inteiro de padrões de design em SQL que é incrível.

Estou trabalhando em um projeto que usa muito SQL, e duas ou três dessas dicas caíram como uma luva. Quanto mais uso SQL, mais percebo que ela não é apenas uma linguagem para consultar dados, mas uma ferramenta completa para "programar de verdade". Dicas como o lateral join funcionando como um for each mostram isso, além de queries recursivas e window functions que permitem fazer computações completas com uma expressividade incrível.

E a dica do JSON como coluna? É óbvio que não é tão óbvio para todo mundo, mas é o melhor dos dois mundos, sem comprometer nada!

2

não costumo utilizar TRANSACTION no sql por falta de prática, preciso estudar um pouco mais sobre o assunto, Mas são dicas realmente muito interessantes, eu gosto bastante da parte de banco de dados, depois vou dar uma olhada melhor nessas dicas todas

2

pra mim eu uso uma regra simples:

vai mexer em mais de uma tabela: então uda transaction, senão não vejo necessidade, da pra tratar direto no código.

te garanto que quando começar a usar vc vai ter pavor de não usar rs principalmente se vc usar ferramentas ou frameworks que tornem o uso de transaction mais agil.

1
1

Bom dia, rafael. Acho que depois de sua postagem ir ao ar aqui no Tabnews, a demanda no website do livro aumentou tanto que não consegui baixá-lo 8-).

Se alguém conseguiu baixá-lo, e puder, poderia compartilhar via link por 1 dia com o wesendit ou link para um BASE64 em 0bin.net se couber?

Edit: Consegui baixar, rafael. Valeu pela dica!

4

Que estranho, gpoleszuk. Aqui está normal, mas segue o link:

Edit: vou deixar os links para caso mais alguém tenha problemas, mas se gostarem do livro, sugiro se inscreverem na newsletter do autor, pois pode ter mais coisas interessantes, e é uma troca (o autor te deu algo, e em troca você está disposto a conhecer a newsletter dele). Obviamente, se não gostarem da newsletter, podem se desinscrever.

2

Valeu, rafael. Consegui baixar por aqui pelo link archive. Caso desejar, pode remover os links de sua resposta.

MD5:            5f4556ac8b9be416051187f2382c26a2
Filename:       next-level-database-techniques-for-developers.pdf 
Title:          Next-Level Database Techniques for Developers
Author:         Tobias Petry
Creator:        Tobias Petry
Producer:       mPDF 8.1.1
CreationDate:   Fri Nov 11 06:10:31 2022 -03
ModDate:        Fri Nov 11 06:10:31 2022 -03
Tagged:         no
UserProperties: no
Suspects:       no
Form:           none
JavaScript:     no
Pages:          50
Encrypted:      no
Page size:      595.276 x 841.89 pts (A4)
Page rot:       0
File size:      435725 bytes
Optimized:      no
PDF version:    1.4

Content:        image/jpeg:    239036 bytes
Text:           text/plain:     51729 bytes

Edit: Incrível como o autor conseguiu sintetizar vários detalhes em tão poucos bytes, cerca de 2,5 posts completos aqui no Tabnews (limite de 20.000 bytes).