Sou um engenheiro de software com cerca de 10 anos de experiência na construção de aplicações web Laravel para pequenas empresas. Estou bastante confortável com o design de banco de dados, mas esse não é meu maior ponto forte.
Um dos aplicativos que desenvolvi é uma ferramenta de terceiros para monitorar aberturas e cliques em boletins informativos por e-mail. Este aplicativo armazena eventos de abertura e clique, e os usuários da ferramenta consultam esses eventos para ver o desempenho de seus boletins informativos por e-mail. A contagem atual de linhas da tabela de eventos analíticos é de cerca de 1,5 bilhão, o que representa cerca de um ano e meio de dados.
Pretendo migrar para um novo design de banco de dados e gostaria de ver se meu entendimento do design de banco de dados está no caminho certo.
Estas são as tabelas do novo banco de dados:
Table contacts {
id integer
email_address string
created_at datetime
updated_at datetime
indexes {
id [pk]
email_address
created_at
}
}
Table contact_opens {
id unsignedBigInteger
contact_id integer
sent_at datetime
opened_at datetime
indexes {
id [pk]
(contact_id, sent_at)
sent_at
opened_at
}
}
Table contact_clicks {
id unsignedBigInteger
contact_id integer
sent_at datetime
clicked_at datetime
indexes {
id [pk]
(contact_id, sent_at)
sent_at
clicked_at
}
}
Pretendo usar o MariaDB em um servidor linode dedicado com 64 GB de RAM, particionar o banco de dados por mês e ajustar o InnoDB da seguinte maneira:
innodb_buffer_pool_size = 48GB
InnoDB File-Per-Table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 0_DIRECT
innodb_log_file_size = 8GB
table_open_cache = 20
As consultas neste banco de dados segmentarão os Contatos com base em Aberturas e Cliques. Por exemplo:
- Contatos com 5 ou mais aberturas nos últimos 30 dias
- Contatos com pelo menos 1 clique nos últimos 5 dias
- Contatos com 1 clique em cada um dos últimos 30 dias
- etc., consultas padrão de segmentação de boletins informativos por e-mail (como visto no Mailchimp)
As consultas retornarão de 10 mil a 300 mil contatos por vez.
Fiz muitas pesquisas e parece que indexação, particionamento e/ou fragmentação parecem ser a melhor opção para ajudar a acelerar as consultas. Parece que uma combinação de indexação e particionamento seria mais adequada para este caso de uso específico.
Esse uso parece um design de banco de dados sólido, onde a tabela de aberturas cresce cerca de um bilhão de linhas por ano e a tabela de cliques cresce cerca de 300 milhões de linhas por ano?
Atualização 1: o esquema não será alterado. Ele está mudando um pouco em relação à forma como foi projetado anos atrás, mas apenas para acomodar as alterações descritas nesta questão para tornar as consultas mais rápidas.
Atualização 2: As 300 mil consultas de contato são para correspondências em massa (boletins informativos por e-mail). Atualizados os tipos de dados nas tabelas MySQL acima.