Estou em permanente discussão com os desenvolvedores da empresa onde trabalho porque eles dizem que é melhor se livrar da imposição de relacionamento (via definições de restrição FOREIGN KEY) em um banco de dados relacional para agilizar consultas grandes e obter melhores atuação.
A plataforma em questão é o MySQL 5.x, e nenhuma FOREIGN KEY foi configurada, mesmo algumas restrições PRIMARY KEY das tabelas relevantes estão faltando, o que, pelo menos para mim, não é razoável. Talvez eles estejam certos e eu errado, mas não tenho argumentos suficientes para discutir sobre esta situação.
Esta tem sido a abordagem preferida há três anos. Sou novo nesta empresa (apenas um mês) mas, como o produto “funciona”, hesita-se em aumentar a base de dados; no entanto, a primeira coisa que notei é uma página demorando 1 minuto para carregar (sim, 60 segundos!).
Uma das alegações por trás do estado atual das coisas é que um banco de dados “desnormalizado” é mais rápido que um normalizado, mas não acredito que isso seja verdade.
A maioria das consultas relevantes inclui operações JOIN, o que as torna muito, muito, muito lentas com grandes quantidades de dados (o banco de dados contém milhões de linhas).
Normalmente, a manipulação de operações “CRUD” é implementada no nível do código do programa aplicativo; por exemplo, para EXCLUIR alguns dados DE, digamos TableA
:
- é necessário primeiro verificar em tempo real se existe alguma relação entre as linhas de
TableA
eTableB
, - caso a referida relação seja “detectada”, então o código do programa app não permitirá EXCLUIR a(s) linha(s) pertinente(s), mas sim
- se por algum motivo o código do programa app falhar, então a operação DELETE terá “sucesso”, independentemente de haver algum relacionamento em relação às linhas e tabelas envolvidas.
Pergunta
Você poderia me ajudar a elaborar uma resposta boa, precisa e sólida para enriquecer o debate?
Nota : Talvez algo assim tenha sido perguntado (e respondido) antes, mas não consegui encontrar nada por meio do Google.
Se, como dito em seu post, a intenção é criar um banco de dados relacional (RDB para abreviar) e, portanto, espera-se que ele funcione como tal, a resposta curta é:
O objetivo principal deve ser o de gerenciar os dados pertinentes como eles são: um ativo organizacional bastante valioso, e uma maneira confiável de atingir esse objetivo é empregar meios técnicos apoiados em teoria sólida.
Assim, como um profissional de banco de dados, você pode aproveitar os mecanismos de modelo relacional elegantes e de última geração fornecidos pelo Dr. EF Codd para impor regras de negócios e evitar os problemas (técnicos e organizacionais) que eventualmente surgiriam se eles não são utilizados.
A esse respeito, compartilharei (a) minha opinião geral sobre as restrições e também (b) várias considerações sobre o estado das coisas do banco de dados e do ambiente de trabalho em questão a seguir.
Restrições FOREIGN KEY, relacionamentos de dados e integridade referencial
Um RDB deve refletir as características do contexto de negócios de interesse com alta precisão, o que definitivamente requer uma análise aprofundada em nível conceitual liderada por um modelador ou designer que segue as melhores práticas, contando com a indispensável assistência dos especialistas de negócios. Essa análise deve resultar na correta identificação e formulação das regras de negócio aplicáveis .
Consequentemente, se tal modelador identificou que existem inter-relações entre os dados de relevância, ele deve configurar as restrições de nível lógico correspondentes para que o sistema de gerenciamento de banco de dados (SGBD) possa garantir que os dados permaneçam consistentes com as características exatas e regras determinadas na análise acima referida em todos os momentos .
Sobre a base de dados em discussão, pode-se inferir que as inter-relações pertinentes foram identificadas, pois você menciona que há uma tentativa processual (e fácil de contornar) de aplicá-las de fora das instalações do SGBD, por força do código do programa aplicativo (que é uma abordagem pré-relacional) que, em qualquer caso, tem que “tocar” o banco de dados para tentar validar a totalidade dessas inter-relações.
No entanto, como você sabe, essa não é a técnica ideal para proteger a integridade referencial , porque a ciência relacional prescreveu um instrumento muito poderoso para esse fim, ou seja, as restrições FOREIGN KEY (FK). Essas restrições são muito fáceis de criar (através da abordagem declarativa superior), pois são sentenças únicas que evitam o recurso a procedimentos ad hoc desnecessários e propensos a erros. É muito útil observar que a velocidade de execução das restrições FK foi altamente otimizada por programadores especializados (e os principais fornecedores de plataformas trabalham nisso há décadas).
Além disso, uma vez que um RDB deve ser um componente de software independente (autoprotetor, autodescritivo, etc.) capaz de ser acessado por vários programas aplicativos (desktop, automático, web, móvel, combinações dos mesmos), não deve ser “acoplado” com o código de qualquer um desses aplicativos.
Da mesma forma, os dados —sendo um recurso organizacional significativo— naturalmente tendem a sobreviver a programas de aplicativos, programadores de aplicativos, plataformas de desenvolvimento de aplicativos e paradigmas de programação.
Restrições PRIMARY KEY e implicações de linhas duplicadas
Quando —conceitualmente falando— um tipo particular de coisa foi considerado significativo em um ambiente de negócios, um modelador de banco de dados deve (1) determinar suas características relevantes —ou seja, suas propriedades—, confirmar esse tipo de coisa como um protótipo de instâncias de entidade — ou seja, um tipo de entidade - e (2) representá-lo por meio de uma tabela que é integrada por uma ou mais colunas em um design lógico.
Então, assim como é fundamental distinguir cada instância individual de um determinado tipo de entidade nos negócios do mundo real, cada linha correspondente incluída em uma tabela também deve ser distinguida exclusivamente. Se uma tabela não tiver nenhuma KEY declarada, ela eventualmente reterá duplicatas e, se houver duas ou mais linhas que retiverem exatamente os mesmos valores, todas terão o mesmo significado , todas representarão o mesmo fato .
Nesse ponto, as linhas duplicadas devem ser descartadas devido a vários motivos. Do ponto de vista teórico, o designer deve garantir que cada linha seja sempre única, com o objetivo de ter tabelas que funcionem tão relacionalmente quanto permitido pela sublinguagem de dados SQL (com repercussões importantes nas operações de manipulação de dados). Além disso, do ponto de vista informacional, se várias linhas representam o mesmo fato, registrá-las não só é supérfluo como prejudicial , conforme exemplificado abaixo:
Nesse caminho:
Como você sabe, este fenômeno pode até ter implicações legais, uma circunstância que seguramente é de enorme importância.
Além disso, o tempo e o esforço que devem ser empregados para lidar com tais contradições (talvez por meio de algum tipo de “sincronização de atualização”) devem ser mais bem dedicados a tarefas que realmente gerem valor para sua organização. Portanto, a retenção de linhas contraditórias deve ser evitada por design para manter intacta a consistência de um banco de dados.
É por isso que a identificação de uma PRIMARY KEY (PK) e a declaração da respectiva restrição devem ser sempre realizadas pelo projetista do banco de dados. Mas também deve ser mencionado que uma tabela pode ter mais de uma coluna ou combinação de colunas que contêm valores que identificam exclusivamente cada linha; como consequência, além de configurar uma restrição PK (idealmente estabelecida como PRIMARY por razões pragmáticas), o projetista também deve declarar uma ou mais ALTERNATE KEYs (geralmente definidas por meio de uma ou mais restrições UNIQUE mais NOT NULL) quando aplicável (o que é bastante comum).
Outra propriedade vantajosa das PKs é que, quando “migradas” para outras tabelas para participar de FKs simples ou compostas, podem ajudar a impor as razões de cardinalidade dos relacionamentos/associações existentes entre os dados. Tudo isso, sim, por meio de configurações declarativas simples e eficientes, asseguradas invariavelmente pelo SGBD.
(Atual) Restrições CHECK e validação de linha única
Não esqueçamos a relevância das (atuais) restrições CHECK que, restringindo declarativamente o conjunto válido de valores de coluna de uma linha (o que pode parecer simples, mas é de fato uma característica fundamental de um SGBD relacional), ajudam também a tornar certeza de que as regras do contexto empresarial são sempre refletidas com precisão .
Como você marcou sua pergunta com a tag MySQL, começando com a versão 8.0.16 (consulte também esta postagem no blog da equipe do servidor MySQL ) essa plataforma finalmente! aplica esse tipo de restrição. A este respeito, deve-se mencionar que em todas as suas versões anteriores este SGBD permitia declarar restrições CHECK, mas ignorava sua aplicação! , situação que, compreensivelmente, foi relatada como um bug desde 2004 .
Então, se estiver usando versões legadas, você teria que cuidar desse fator por outros meios, como por exemplo, ACID TRANSACTIONS , TRIGGERS, ou outros métodos dentro do próprio SGBD (veja essa resposta do @ypercubeᵀᴹ para informações sobre esse assunto) para que o os dados continuam a ser consistentes.
Restrições ASSERTION: configurar mais regras de negócios multilinhas e multitabelas de forma declarativa
Um aspecto que, por qualquer motivo, é muito mal suportado —se é que é— pelos diferentes SGBDs SQL, incluindo o MySQL, é permitir restrições multilinhas e multitabelas de forma declarativa —além de PKs e FKs, evidentemente—.
Por sua vez, o padrão SQL inclui ASSERÇÕES há muitos anos. Não sei quais regras do seu ambiente de negócios se beneficiariam com essa abordagem de validação de nível lógico, mas, como designer de banco de dados, considero que seria muito útil restringir os dados com uma ou mais ASSERÇÕES quando necessário, embora eu tenha que mencionar que, do ponto de vista dos desenvolvedores de DBMS, esse tipo primordial de ferramenta tem sido difícil de implementar no nível físico de abstração.
Parece que o fornecedor e/ou desenvolvedores da Oracle estão avaliando o suporte ASSERTION desde 2016, e isso tornaria esse DBMS mais compatível com os relacionamentos e, portanto, mais robusto e competitivo. Eu acho que, se (i) seus consumidores continuarem pressionando e (ii) a Oracle for bem-sucedida na implementação, então (iii) outros fornecedores/comunidades de DBMS terão que habilitá-los também, e seu uso começará a se espalhar. Certamente, isso seria um grande progresso no campo de gerenciamento de dados e, sendo uma das ferramentas mais distintas imaginadas pelo Dr. Codd, eu pessoalmente espero que isso aconteça em breve.
Consistência de dados e o processo de tomada de decisão
Conforme discutido acima, um dos aspectos mais importantes de um RDB é que ele garante por si só a consistência dos dados que retém, e essa consistência só é atendida quando o RDB atende às restrições de integridade declaradas pelo modelador.
Nesse sentido, é obrigatório ter tabelas base (aquelas estabelecidas em uma estrutura DDL) cuja integridade seja protegida para poder criar tabelas derivadas (por exemplo, uma instrução SELECT ou view que recupera colunas de várias tabelas) que sejam confiáveis , pois as tabelas derivadas devem ser produzidas necessariamente em termos de tabelas base.
É bastante óbvio que as pessoas usam a informação como a principal ferramenta no processo de tomada de decisão organizacional (e no comum). Então, se as informações apresentadas por um banco de dados não forem coerentes e precisas, as decisões baseadas nessas informações não serão sólidas (para dizer o mínimo). É por isso que um RDB deve ser cuidadosamente projetado e implementado: deve ser construído para se tornar um recurso confiável que possa auxiliar seus usuários a tomar decisões bem fundamentadas.
“Desnormalização”
Infelizmente, “um banco de dados 'desnormalizado' é mais rápido que um normalizado” é um equívoco amplamente difundido, embora também seja um “argumento” que pode ser refutado em bases lógicas, físicas e pragmáticas.
Em primeiro lugar, a desnormalização implica necessariamente que uma tabela base tenha sido previamente normalizada (em virtude de um procedimento formal , baseado na ciência, cumprido no nível lógico de abstração de um banco de dados).
Assim, assumindo que a referida tabela foi de fato normalizada corretamente, “desnormalizando-a” (o que, ao contrário do significado formal da palavra, envolve anexar a ela colunas que pertencem e também fazem parte de outras tabelas em um anúncio moda hoc ) pode ajudar, por exemplo, a acelerar (no nível físico) o processamento de apenas uma ou algumas instruções SELECT específicas, enquanto tal curso de ação pode, ao mesmo tempo, prejudicar a execução de muitos outros dados associados operações de manipulação (por exemplo, várias instruções INSERT, UPDATE, DELETE e SELECT, ou combinações delas incluídas em uma única ou várias ACID TRANSACTIONS).
Além disso, a desnormalização (seja formal ou informal) introduziria anomalias de atualização/modificação que deterioram a coerência da base de dados, problema que “pode” ser tratado por procedimentos complexos, dispendiosos e sujeitos a erros, quando tudo isso pode ser evitado bem no começo.
Andaimes de nível físico que suportam tabelas normalizadas e “desnormalizadas”
Um layout lógico (abstrato) (projeto SQL-DDL) destinado a ser utilizado no mundo real claramente contém repercussões físicas (concretas) que devem ser consideradas.
Desta forma, uma tabela “desnormalizada” seria necessariamente “mais larga” (contendo colunas adicionais), o que significa que suas linhas seriam necessariamente mais pesadas (exigindo mais e maiores componentes de nível físico), o que significa que os processos de computação subjacentes (por exemplo , aqueles que têm a ver com o disco rígido ou memória) podem facilmente tornar-se mais lentos.
Em contraste, uma tabela normalizada que é obviamente “mais estreita” (com menos colunas) seria um elemento “mais leve” (servido por menos e menores componentes físicos) que “se comportaria mais rápido”, o que aceleraria a série de ações relacionadas a , por exemplo, escrita e leitura de dados.
Assim sendo, é muito conveniente (a) normalizar as tabelas relevantes de forma formal e prudente, mantendo-as como tal, e (b) fazer uso de qualquer recurso de nível físico que possa otimizar a recuperação de dados e a velocidade de modificação, por exemplo, implementando uma estratégia de indexação cuidadosa e eficiente, permitindo configurações adequadas de servidor de software e hardware, atualizando os recursos de largura de banda da rede, etc.
O funcionamento do banco de dados em consideração e abordando seus colegas
Os parágrafos a seguir da sua pergunta têm a ver com a velocidade das operações de recuperação de dados:
Se o carregamento de uma determinada página demorar tanto, é evidente que os usuários do sistema não estão recebendo um serviço adequado; portanto, mesmo quando “funciona”, seu funcionamento não parece nada ótimo, ponto que demonstra que suas intenções de tornar todo o sistema informatizado de informação (banco de dados e aplicativos) mais eficiente são bem sustentadas, e mostra uma atitude muito construtiva .
Então, mesmo que a ciência definitivamente apoie você e, portanto, você deva manter uma postura firme, sugiro abordar a situação de forma diplomática, pois no final das contas, seus empregadores, colegas de trabalho e você estão UNIndo esforços para tornar o todo organização mais bem-sucedida. Portanto, esse é um argumento que você deve enfatizar, que, enquanto eles estão fazendo outras coisas mais do que bem, melhorar as práticas gerais e específicas de gerenciamento de dados pode ajudar consideravelmente na produção de mais crescimento organizacional e individual.
Vale a pena notar que o operador JOIN é um elemento essencial e poderoso que pertence à manipulação relacional de dados. Então, embora plataformas mais robustas sirvam com execuções comparativamente mais rápidas, a circunstância que você descreve é provavelmente um sintoma de um design não eficiente (nos níveis conceitual, lógico e físico de abstração). Então, minhas estimativas à primeira vista são:
Além disso, sim, como @TommCatt menciona em sua resposta , às vezes uma reescrita (lógica) de uma consulta modifica seu plano de execução (físico) acelerando a leitura/escrita de dados, que é um fator que decididamente deve ser levado em consideração.
A premissa básica de seus desenvolvedores está absolutamente errada. As chaves estrangeiras afetarão ligeiramente o desempenho do DML do seu sistema. Eles não são usados em consultas , portanto, não afetam seu desempenho. Portanto, seus desenvolvedores não sabem do que estão falando e são as últimas pessoas de quem você deve considerar receber conselhos.
As chaves estrangeiras desempenham um papel crítico na manutenção da integridade de seus dados. Isso é muito mais importante do que qualquer pequena melhoria de desempenho obtida ao removê-los (mesmo que fosse verdade).
Não remova, sob nenhuma circunstância, FKs de um banco de dados OLTP.
Além disso, a desnormalização às vezes acelera algumas consultas. Isso, como dizem, depende. Ainda assim, mesmo que haja alguma melhoria na velocidade, geralmente não vale a pena o esforço extra para manter a integridade dos dados.
É muito raro quando o ajuste simples não consegue melhorar muito mais a velocidade do que a desnormalização. É aqui que um bom DBA pode (finalmente) ganhar seu salário. Você também pode ajustar suas consultas. Certa vez, fiz uma consulta que retornou uma resposta em nada menos que 30 minutos e a fiz funcionar em menos de 8 segundos. Nenhuma alteração no banco de dados, apenas reescreveu a consulta. Concedido, este é o meu melhor recorde pessoal, então sua milhagem pode variar, mas desnormalizar deve ser a última coisa que você tenta.
Você também pode querer evitar que as consultas mais complicadas sejam escritas pelos desenvolvedores. Pergunte a eles quais dados eles desejam e em que formato eles desejam. Em seguida, forneça exibições para fornecê-los a eles. As consultas complicadas serão as visualizações. Os desenvolvedores então só precisam escrever:
Também estou assumindo que seu banco de dados foi bem projetado. Um design ruim do banco de dados, ou mesmo de pequenas partes dele, pode realmente atrasar as coisas. Eu trabalhei frequentemente com Tabelas Muito Grandes (bilhões de registros cada) com consultas que as juntavam à esquerda e à direita e esperavam (e obtinham) respostas em frações de segundo. O tamanho de uma tabela não determina a velocidade da consulta.
Eu realmente me encolho quando alguém diz: "porque o produto 'funciona', há hesitação em aprimorar o banco de dados". Se essa "hesitação" é mais como "não estou sob meu comando, amigo!" então você pode até querer começar a atualizar seu currículo. Nada de bom vem de tal ambiente e você receberá a culpa por cada fracasso futuro, mesmo que tenha feito lobby por horas para fazer uma mudança que teria evitado o fracasso. Você ouvirá repetidamente: "Agora não é um bom momento para fazer alterações". Certo. Boa sorte.
Mudar o título muda a pergunta.
FOREIGN KEYs
são opcionais. Eles fazem:INDEX
em uma das tabelas. Esse índice pode ser adicionado manualmente. (Portanto, FK não é necessário para isso.)CHECK
opção não é posta em prática.CASCADE
coisas. (Pessoalmente, prefiro manter o controle e não presumir que o FK 'fará a coisa certa'.)Resumindo para FKs: Algumas pessoas insistem em FKs; alguns produtos vivem perfeitamente bem sem eles. Você decide.
Livrar-se do
PRIMARY KEY
InnoDB é um grande erro. Por outro lado, livrar-se de um substitutoAUTO_INCREMENT
e usar um PK "natural" composto de uma (ou mais) colunas geralmente é a coisa certa a fazer. Um caso simples e comum é uma tabela de mapeamento muitos:muitos, conforme discutido aqui .Com base na experiência pessoal, sugiro que 2/3 das tabelas sejam melhores do que usar 'natural' em vez de auto_inc PK.