A história de uma otimização do MySQL

Será sobre otimização no banco de dados MySQL.

Isso aconteceu quando criamos um sistema para boletins por e-mail. Nosso sistema deveria enviar dezenas de milhões de cartas por dia. Enviar uma carta não é uma tarefa fácil, embora tudo pareça bastante primitivo:

1. Colete uma carta do criativo html e substitua os dados personalizados.
2. Adicione um pixel de visualização de mensagens, substitua todos os links da mensagem pelos seus - para rastrear cliques.
3. Antes de enviar, verifique se o email não está na lista negra.
4. Envie um email para um pool específico.

Vou falar mais sobre o segundo parágrafo:
O construtor de correio da Microservice está preparando uma carta para enviar:

• localiza todos os links na carta;
• um uuid exclusivo de 32 caracteres é gerado para cada link;
• substitui o link original por um novo e salva os dados no banco de dados.

Assim, todos os links de origem serão substituídos por uuid e o domínio será alterado para o nosso. Quando você recebe uma solicitação GET usando este link, procuramos a imagem original ou redirecionamos para o link original. A economia ocorre no banco de dados MySQL, salvamos o uuid gerado junto com o link original e com algumas informações meta (email do usuário, ID de correspondência e outros dados). A desnormalização nos ajuda em uma solicitação para obter todos os dados necessários para salvar estatísticas ou iniciar algum tipo de cadeia de gatilhos.

Problema número 1

A geração de uuida em nós dependia do registro de data e hora.

Como as correspondências geralmente ocorrem em um determinado período de tempo e muitas instâncias de microsserviço para a montagem de uma carta são iniciadas, descobriu-se que alguns dos uuids eram muito semelhantes. Isso deu uma baixa seletividade. UPD: como os dados eram semelhantes, trabalhar com a bi-árvore não foi muito eficaz.

Resolvemos esse problema usando o módulo uuid em python, onde não há dependência de tempo.
Uma coisa tão implícita reduziu a velocidade dos índices.

Como está indo o armazenamento?

A estrutura da tabela era a seguinte:

CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

No momento da criação, tudo parecia lógico:
UUID é uma chave primária e também é um índice em cluster. Quando fazemos uma seleção nesse campo, simplesmente selecionamos o registro, porque todos os valores são armazenados ali. Esta foi uma decisão deliberada. Saiba mais sobre o índice em cluster.

Tudo ficou ótimo até a mesa crescer.

Problema número 2

Se você ler mais sobre o índice de cluster, poderá descobrir sobre essa nuance:

Ao adicionar uma nova linha à tabela, ela é adicionada não ao final do arquivo, não ao final da lista simples, mas à ramificação desejada da estrutura em árvore correspondente a ela pela classificação.

Assim, com o aumento da carga, o tempo de inserção aumentou.

A solução foi usar uma estrutura de tabela diferente.

 CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Como a chave primária agora está aumentando automaticamente, e o mysql armazena o cache do último local de inserção, agora a inserção sempre ocorre no final, ou seja, o Innodb é otimizado para escrever valores sequencialmente crescentes.

Encontrei os detalhes dessa otimização no código fonte do postgres. O Mysql implementa uma otimização muito semelhante.
Obviamente, tive que adicionar uma chave exclusiva para que não houvesse conflitos, mas aumentamos a velocidade de inserção.

Com a base crescendo ainda mais, pensamos em excluir dados antigos. O uso de DELETE no campo Inserido não é absolutamente ideal - esse é um período muito longo e o local não será liberado até que executemos o comando optimize table . A propósito, essa operação bloqueia completamente a tabela - isso não nos convinha.

Portanto, decidimos dividir nossa tabela em partições.
1 dia - 1 partição, as antigas caem automaticamente quando chegar a hora.

Problema número 3

Tivemos a oportunidade de excluir dados antigos, mas não tivemos a oportunidade de escolher a partição desejada, porque com select`e especificamos apenas uuid, o mysql não sabe em qual partição devemos procurar e está procurando em todos.

A solução nasceu do Problema # 1 - adicione um carimbo de data / hora ao uuid gerado. Só que desta vez fizemos um pouco diferente: inserimos um registro de data e hora em um local aleatório na linha, não no começo ou no final; antes e depois de adicionar um símbolo de traço para que ele possa ser obtido com uma expressão regular.

Com essa otimização, conseguimos obter a data em que o uuid foi gerado e já fizemos uma seleção indicando o valor específico do campo Inserido. Agora, lemos os dados imediatamente da partição de que precisamos.

Além disso, graças a coisas como ROW_FORMAT = COMPRESSED e alterando a codificação para latin1 , economizamos ainda mais espaço no disco rígido.