O banco de dados está executando lento, mesmo todas as tabelas estão tendo normalização

Gostaria de falar sobre como há muitas coisas que podem ser feitas para melhorar o desempenho. A primeira coisa é sempre investigar se os índices corretos estão no lugar. Particularmente preocupante em um banco de dados normalizado é garantir que os FKs sejam indexados. Provavelmente isso resolveria muitos problemas de desempenho.

Outras coisas a serem observadas seriam reescrever o código SQL para usar técnicas mais eficientes, como livrar-se de cursores e subconsultas correlacionadas e tornar as cláusulas where sargable. Você gostaria de analisar as consultas com pior desempenho individualmente. Você também deseja revisar as consultas que são executadas com frequência (especialmente se vários usuários as executarem simultaneamente), já que uma pequena alteração nessas pode se multiplicar por meio do sistema. Se as suas piores consultas vierem de um ORM, elas talvez precisem ser reescritas como procs armazenados para que possam ser ajustadas no desempenho.

Você também pode querer ter certeza de que tem um problema de desempenho. O que você pode ter é realmente um problema de bloqueio onde o código de desempenho está sendo bloqueado por outros processos e tem que esperar.

Em seguida, você veria o hardware, se você tiver hardware e conexões de rede com pouca potência, provavelmente nenhuma outra alteração consertará isso.

Em um sistema corporativo grande, você pode considerar o particionamento de dados.

A desnormalização é uma técnica para melhorar o desempenho, mas é a última última que você gostaria de considerar. Primeiro, você tem o risco de alterar drasticamente os dados da estrutura. Converter os dados para a nova estrutura é algo que pode dar muito errado se um erro é cometido e é mais demorado fazer esse tipo de mudança estrutural do que qualquer outra melhoria de desempenho possível. Também seria irresponsável desnormalizar sem criar gatilhos para garantir que os dados permaneçam sincronizados à medida que são alterados nas tabelas desordenadas. Isso pode significar que as seleções são imporved, mas as consultas de ação são mais lentas, portanto, o desempenho pode não ser tão importante quanto você pensa. Também é uma preocupação que, ao desnormalizar, você possa tornar as tabelas significativamente mais amplas e isso pode afetar o desempenho negativamente se você tiver tabelas largas.

Parece-me que o seu entrevistador não estava à procura de uma resposta de um cientista de dados, mas estava simplesmente procurando ter certeza de que você entendeu essa "normalização"!="desempenho". Então, vou manter essa resposta no nível que estou supondo que ele queria.

Normalização significa minimizar a redundância nos dados armazenados. Em vez disso, você configura relacionamentos (geralmente com restrições estrangeiras) entre várias tabelas. No entanto, embora a normalização possa levar a uma quantidade menor de dados armazenados, geralmente cria problemas de desempenho, pois agora muitas consultas acabam se unindo a várias tabelas. A mesma coisa com a adição de dados, onde você pode ter que atualizar várias tabelas de uma só vez.

Muitas vezes, os ganhos de velocidade podem ser alcançados pela desnormalização dos dados. Você está armazenando mais e pode haver duplicatas, mas quando se trata de executar as consultas usadas com mais freqüência, todos os seus dados agora estarão em uma tabela. Obter resultados de uma tabela geralmente é muito mais fácil no hardware do que ter que juntar várias tabelas

Tornar instruções INSERT mais rápidas é um pouco de arte arcana. Mas isso provavelmente não é o foco. O ponto de um banco de dados não está colocando dados nele; está ficando de volta de maneiras interessantes e úteis. Então, as principais coisas para se concentrar são as instruções SELECT.

A primeira coisa que eu verifico é verificar os planos de consulta em consultas lentas. Veja se você tem alguma varredura de tabela que está ocupando uma porcentagem significativa de seu tempo. Uma varredura de tabela é quando o mecanismo de banco de dados precisa examinar cada linha individualmente para ver se ela atende a um critério WHERE. Se você encontrar um desses, poderá fazer com que a consulta seja executada mais rapidamente indexando a tabela nos critérios WHERE apropriados. Isso pode levar os tempos de pesquisa de O (N) até O (log N) ou até mesmo O (1).

Alguns bancos de dados facilitarão o processo: seu analisador de plano de consultas indicará que você está perdendo um índice e sugerirá o que deve criar.

Além disso, confira as associações na sua consulta. Certifique-se de que eles não estejam usando critérios de união muito amplos e tenha cuidado para não usar as associações externas à esquerda quando uma associação completa funcionasse. Esses dois problemas podem fazer com que uma consulta mal escrita produza muitas linhas e leve mais tempo para ser executada.

Se você não tem índices faltando ou associações ruins, um truque mais avançado é desnormalização : configurar colunas em tabelas que duplicam dados que podem ser encontrados em outras tabelas, para permitir que você evite junções ou agregados que podem ser caros. Isso tem que ser feito com cuidado, com gatilhos para que os dados permaneçam sincronizados, e é melhor feito apenas se você souber o que está fazendo e se não houver alternativas melhores disponíveis.

Especificamente, no Plano de Execução de Consultas, procure ações que sejam varreduras de tabela em vez de pesquisas de índice. É uma dica que você pode querer adicionar um índice para dizer uma coluna que representa a chave estrangeira (eles não são criados automaticamente)

Outras opções seriam colocar seus arquivos de dados em diferentes discos físicos. Usar o RAID para suas partições também pode funcionar. No mínimo, você deseja separar os arquivos de log desses arquivos de dados ... para que a gravação no log não afete o tempo de gravação no arquivo de dados.

Cenários mais avançados incluem clustering e sharding para permitir que a carga das pesquisas seja distribuída entre vários nós.