Os stored procedures do MySQL os utilizam ou não para usá-los

Estamos no início de um novo projeto, e estamos realmente nos perguntando se devemos usar stored procedures no MySQL ou não.

Nós usaríamos os stored procedures apenas para inserir e atualizar as entidades do modelo de negócios. Existem várias tabelas que representam uma entidade modelo, e nós a abstrairíamos nos stored procedures insert / update.

Por outro lado, podemos chamar insert e update a partir da camada Model, mas não no MySQL, mas no PHP.

Na sua experiência, qual é a melhor opção? vantagens e desvantagens de ambas as abordagens. Qual é o mais rápido em termos de alto desempenho?

PS: É um projeto web com leitura e alto desempenho é o requisito mais importante.

Ao contrário do código real da linguagem de programação, eles:

  • não portável (cada db tem sua própria versão de PL / SQL. Às vezes, versões diferentes do mesmo database são incompatíveis – eu já vi isso!)
  • não é facilmente testável – você precisa de uma instância de database real (dev) para testá-los e, assim, a unidade testando seu código como parte de uma compilation é praticamente impossível
  • não é facilmente atualizável / liberável – você deve descartá-los / criá-los, ou seja, modificar o database de produção para liberá-los
  • não tem suporte a biblioteca (por que escrever código quando alguém tem)
  • não são facilmente integráveis ​​com outras tecnologias (tente chamar um serviço web deles)
  • eles usam uma linguagem tão primitiva quanto Fortran e, portanto, são deselegantes e trabalhosos para obter uma codificação útil, por isso é difícil expressar a lógica de negócios, mesmo que normalmente seja esse o objective principal deles.
  • não ofereça debugging / tracing / message-logging etc (alguns dbs podem suportar isso – eu ainda não o vi)
  • falta um IDE decente para ajudar com a syntax e vincular a outros procedimentos existentes (por exemplo, como o Eclipse faz para java)
  • pessoas especializadas em codificá-las são mais raras e mais caras que os codificadores de aplicativos
  • seu “alto desempenho” é um mito, porque eles executam no servidor de database, eles geralmente aumentam a carga do servidor db, então usá-los normalmente reduzirá a taxa de transferência máxima da transação
  • incapacidade de compartilhar eficientemente constantes (normalmente resolvidas criando uma tabela e questionando-a de dentro de seu procedimento – muito ineficiente)
  • etc.

Se você tiver uma ação muito específica do database (por exemplo, uma ação em transação para manter a integridade do database) ou manter seus procedimentos muito atômicos e simples, talvez você os considere.

Recomenda-se precaução ao especificar “alto desempenho” na frente. Muitas vezes, leva a escolhas erradas em detrimento de um bom design e vai te morder muito mais cedo do que você pensa.

Use stored procedures por sua própria conta e risco (de alguém que já esteve lá e nunca quer voltar). Minha recomendação é evitá-los como a peste.

Ao contrário do código de programação, eles:

  • tornar ataques de injeção SQL quase impossíveis (a menos que você esteja
    construção e execução dinâmica
    SQL de dentro de seus procedimentos)
  • exigem muito menos dados a serem enviados pelo IPC como parte do texto explicativo
  • habilitar o database para planos de cache e conjuntos de resultados muito melhores (isso é reconhecidamente não tão efetivo com o MySQL devido às suas estruturas internas de cache)
  • são facilmente testáveis ​​em isolamento (ou seja, não como parte dos testes JUnit)
  • são portáteis no sentido de que eles permitem que você use resources específicos do database, abstraídos por trás de um nome de procedimento (no código, você está preso a coisas genéricas do tipo SQL)
  • quase nunca são mais lentos do que o SQL chamado do código

mas, como diz Bohemian, também há muitos contras (isto é apenas por oferecer outra perspectiva). Você terá que talvez fazer benchmark antes de decidir o que é melhor para você.

Quanto às performances, elas têm o potencial de ter um desempenho real em uma versão futura do MySQL (no SQL Server ou Oracle, elas são um verdadeiro deleite!). No entanto, para todo o resto … Eles acabam totalmente com a competição. Vou resumir:

  • Segurança: Você pode dar ao seu aplicativo o EXECUTE apenas, está tudo bem. Seu SP irá inserir update select …, sem nenhum tipo de vazamento. Isso significa controle global sobre seu modelo e uma segurança de dados reforçada.

  • Segurança 2: Eu sei que é raro, mas às vezes o código php vaza do servidor (isto é, fica visível para o público). Se include suas consultas, possíveis invasores conhecerão seu modelo. Isso é muito estranho, mas eu queria sinalizar mesmo assim

  • Task force: sim, a criação de SPs SQL eficientes requer alguns resources específicos, às vezes mais caros. Mas se você acha que não precisa desses resources só porque está integrando suas consultas no seu cliente … você terá sérios problemas. Eu mencionaria a analogia do desenvolvimento web: é bom separar a visão do resto porque seu projetista pode trabalhar em sua própria tecnologia enquanto os programadores podem se concentrar em programar a camada de negócios.

  • Encapsulando a camada de negócios: o uso de stored procedures isola totalmente o negócio onde ele pertence: o maldito database.

  • Rapidamente testável: uma linha de comando sob o seu shell e seu código é testado.

  • Independência da tecnologia do cliente: se amanhã você gostaria de mudar de php para outra coisa, não há problema. Ok, apenas armazenar esses SQL em um arquivo separado também funcionaria. Além disso, bom ponto nos comentários sobre se você decidir mudar de mecanismos de sql, você teria muito trabalho a fazer. Você tem que ter um bom motivo para fazer isso de qualquer maneira, porque para grandes projetos e grandes empresas, isso raramente acontece (devido ao custo e ao gerenciamento de RH principalmente)

  • Impondo desenvolvimentos ágeis de 3 + níveis: se o seu database não estiver no mesmo servidor que o seu código de cliente, você poderá ter servidores diferentes, mas apenas um para o database. Nesse caso, você não precisa atualizar nenhum dos seus servidores php quando precisar alterar o código relacionado ao SQL.

Ok, eu acho que é a coisa mais importante que eu tenho a dizer sobre o assunto. Eu desenvolvi em ambos os espíritos (SP vs cliente) e eu realmente amo o estilo SP. Eu só queria que o Mysql tivesse um IDE real para eles, porque agora é um tipo de dor no traseiro limitado.

Procedimentos armazenados são bons para usar porque eles mantêm suas consultas organizadas e permitem que você execute um lote de uma só vez. Os stored procedures normalmente são rápidos na execução porque são pré-compilados, ao contrário das consultas que são compiladas em todas as execuções. Isso tem um impacto significativo em situações em que o database está em um servidor remoto; Se as consultas estiverem em um script PHP, haverá várias comunicações entre o aplicativo e o servidor de database – a consulta é enviada, executada e o resultado é retornado. No entanto, se estiver usando stored procedures, será necessário enviar apenas uma instrução CALL pequena em vez de consultas grandes e complicadas.

Pode demorar um pouco para se adaptar à programação de um procedimento armazenado porque eles têm sua própria linguagem e syntaxs. Mas quando você estiver acostumado, verá que seu código está realmente limpo.

Em termos de desempenho, pode não ser um ganho significativo se você usar stored procedures ou não.

Eu vou deixar saber a minha opinião, apesar de meus pensamentos possivelmente não estarem diretamente relacionados à questão:

Como em muitos problemas, responda sobre o uso de Procedimentos armazenados ou uma solução orientada por camada de aplicativo depende de perguntas que direcionarão o esforço geral:

  • O que você quer conseguir?

Você está tentando fazer operações em lote ou operações on-line? eles são completamente transacionais? quão recorrentes são essas operações? qual é a carga de trabalho esperada para o database?

  • O que você tem para conseguir.

Que tipo de tecnologia de database você tem? Que tipo de infra-estrutura? Sua equipe é totalmente treinada na tecnologia de database? Sua equipe é mais capaz de criar uma solução de database de diagnóstico?

  • Hora de pegar.

Não há segredos sobre isso.

  • Arquitetura.

Sua solução precisa ser distribuída em vários locais? a sua solução é necessária para usar comunicações remotas? a sua solução está funcionando em vários servidores de database ou possivelmente usando uma arquitetura baseada em cluster?

  • Manutenção.

Quanto custa o aplicativo para mudar? Você tem pessoal especificamente treinado para manter a solução?

  • Mudar a gestão.

Você vê que sua tecnologia de database mudará em um curto, médio e longo prazo? você vê será necessário migrar a solução com freqüência?

  • Custo

Quanto custará implementar essa solução usando uma ou outra estratégia?

O total desses pontos levará a resposta. Então você tem que se importar com cada um desses pontos ao tomar uma decisão sobre o uso ou não de qualquer estratégia. Há casos em que o uso de stored procedures é melhor do que as consultas gerenciadas da camada de aplicativos e outros quando a realização de consultas e o uso de uma solução baseada em camada de aplicativo é melhor.

A utilização de stored procedures tende a ser mais addequada quando:

  1. Sua tecnologia de database não é fornecida para mudar em um curto período de tempo.
  2. Sua tecnologia de database pode manipular operações paralelas, partições de tabelas ou qualquer outra estratégia para dividir a carga de trabalho em vários processadores, memory e resources (clustering, grade).
  3. Sua tecnologia de database é totalmente integrada à linguagem de definição de procedimento armazenado, ou seja, o suporte está dentro do mecanismo do database.
  4. Você tem uma equipe de desenvolvimento que não tem medo de usar uma linguagem procedural (3ª. Linguagem de geração) para obter um resultado.
  5. As operações que você quer alcançar são embutidas ou suportadas dentro do database (Exportando para dados XML, gerenciando a integridade e coerência dos dados apropriadamente com triggers, operações agendadas, etc).
  6. Portabilidade não é uma questão importante e você não faz uma mudança tecnológica em um curto espaço de tempo em sua organização, nem mesmo é desejável. Geralmente, a portabilidade é vista como um marco pelos desenvolvedores orientados a aplicativos e orientados por camadas. Do meu ponto de vista, a portabilidade não é um problema quando o aplicativo não precisa ser implantado em várias plataformas, menos quando não há motivos para fazer uma mudança de tecnologia ou o esforço para migrar todos os dados organizacionais é maior do que o benefício para fazer uma mudança. O que você pode ganhar usando uma abordagem orientada por camada de aplicação (portabilidade) que você pode perder em desempenho e valor obtido de seu database (Por que gastar milhares de dólares para obter uma Ferrari que você dirigirá não mais que 60 milles / hr ?)
  7. O desempenho é um problema. Primeiro: em vários casos, você pode obter melhores resultados usando uma única chamada de procedimento armazenado do que várias solicitações de dados de outro aplicativo. Além disso, algumas características que você precisa executar podem ser embutidas em seu database e seu uso é menos dispendioso em termos de carga de trabalho. Quando você usa uma solução orientada por camada de aplicação, você deve levar em conta o custo associado a fazer conexões de database, fazer chamadas ao database, tráfego de rede, quebra de dados (ou seja, usando Java ou .NET, há um custo implícito quando Usando chamadas JDBC / ADO.NET, você tem que agrupar seus dados em objects que representam os dados do database, assim a instanciação tem um custo associado em termos de processamento, memory e rede quando os dados vêm e vão para fora).

O uso de soluções baseadas em camada de aplicativo tende a ser mais adequado quando:

  1. Portabilidade é uma questão importante.
  2. O aplicativo será implantado em vários locais com apenas um ou alguns repositorys de database.
  3. Seu aplicativo usará regras pesadas de negócios, que precisam ser agnósticas da tecnologia de database subjacente.
  4. Você tem em mente fazer mudanças nos provedores de tecnologia com base nas tendências e no orçamento do mercado.
  5. Seu database não está totalmente integrado ao idioma do procedimento armazenado que chama o database.
  6. Seus resources de database são limitados e seu requisito vai além do que você pode alcançar com sua tecnologia de database.
  7. Seu aplicativo pode suportar a penalidade inerente às chamadas externas, é mais baseado em transactions com regras específicas de negócios e precisa abstrair o modelo de database em um modelo de negócios para os usuários.
  8. Paralelizar operações de database não é importante, além disso, seu database não possui resources de paralelização.
  9. Você tem uma equipe de desenvolvimento que não é bem treinada na tecnologia de database e é mais produtiva usando uma tecnologia baseada em aplicativos.

Espero que isso ajude alguém a se perguntar o que é melhor usar.

Eu recomendaria que você não usasse stored procedures:

  • Sua linguagem no MySQL é muito ruim
  • Não é possível enviar matrizes, listas ou outros tipos de estrutura de dados para um procedimento armazenado
  • Um procedimento armazenado nunca pode alterar sua interface; MySQL não permite parâmetros nomeados nem opcionais
  • Isso torna a implantação de novas versões do seu aplicativo mais complicada – digamos que você tenha 10 servidores de aplicativos e 2 bancos de dados, o que você atualiza primeiro?
  • Todos os seus desenvolvedores precisam aprender e entender a linguagem do procedimento armazenado – o que é muito ruim (como mencionei antes)

Em vez disso, recomendo criar uma camada / biblioteca e colocar todas as suas consultas lá

Você pode

  • Atualize esta biblioteca e envie-a nos seus servidores de aplicativos com seu aplicativo
  • Tem tipos de dados ricos, como matrizes, estruturas, etc.
  • Unidade teste esta biblioteca, em vez dos stored procedures.

No desempenho:

  • Usar stored procedures diminuirá o desempenho de seus desenvolvedores de aplicativos, que é a principal coisa de que você gosta.
  • É extremamente difícil identificar problemas de desempenho em um procedimento armazenado complicado (é muito mais fácil para consultas simples)
  • Você pode enviar um lote de consulta em um único bloco pela rede (se o sinalizador CLIENT_MULTI_STATEMENTS estiver habilitado), o que significa que você não obterá mais latência sem stored procedures.
  • O código do lado do aplicativo geralmente é melhor do que o código do lado do database

Se o seu database for complexo e não for um tipo de fórum com respostas, o SP de armazenamento real certamente será beneficiado. Você pode ter toda a sua lógica de negócios lá e nem um único desenvolvedor vai se importar com isso, eles apenas chamam seus SP’s. Eu tenho feito isso juntando mais de 15 tabelas não é divertido, e você não pode explicar isso para um novo desenvolvedor.

Os desenvolvedores também não têm access a um database, ótimo! Deixe isso para projetistas e mantenedores de database. Se você também decidir que a estrutura da tabela será alterada, poderá ocultá-la por trás de sua interface. n-Tier, lembra?

Alto desempenho e DBs relacionais não são algo que combina, nem mesmo com o MySQL O InnoDB é lento, o MyISAM deve ser descartado pela janela agora. Se você precisar de desempenho com um aplicativo da Web, precisará de cache, memcache ou outros.

no seu caso, porque você mencionou ‘Web’ eu não usaria stored procedures, se fosse data warehouse eu definitivamente consideraria (usamos SP’s para nosso warehouse).

Dica: Desde que você mencionou o projeto da Web, nunca sobre o tipo de solução nosql? Além disso, você precisa de um database rápido, por que não usar o PostgreSQL? (tentando defender aqui …)

Eu costumava usar MySql e meu entendimento de sql era ruim na melhor das hipóteses, eu passei um bom tempo usando o Sql Server, eu tenho uma separação clara de uma camada de dados e uma camada de aplicação, atualmente cuido de um servidor com 0,5 terabytes.

Eu me senti frustrado às vezes não usando um ORM como o desenvolvimento é muito rápido com os stored procedures é muito mais lento. Acho que muito do nosso trabalho poderia ter sido acelerado usando um ORM.

Quando seu aplicativo atingir massa crítica, o desempenho do ORM sofrerá, um procedimento armazenado bem escrito, fornecerá seus resultados mais rapidamente.

Como um exemplo de desempenho, eu coleciono 10 tipos diferentes de dados em um aplicativo e os converto em XML, que processo no procedimento armazenado. Eu tenho uma chamada para o database em vez de 10.

Sql é realmente bom em lidar com conjuntos de dados, uma coisa que me deixa frustrado é quando vejo alguém obtendo dados do SQL em uma forma bruta e usando o código do aplicativo para fazer um loop sobre os resultados e formatar e agrupá-los. .

Meu conselho é aprender e entender o SQL o suficiente e seus aplicativos serão realmente beneficiados.

Eu recomendaria que você fique longe de stored procedures específicos do database.

Já passei por vários projetos em que eles, de repente, querem trocar a plataforma DB e o código dentro de um SP geralmente não é muito portátil = trabalho extra e possíveis erros.

O desenvolvimento de Procedimento Armazenado também requer que o desenvolvedor tenha access diretamente ao mecanismo SQL, em que uma conexão normal pode ser alterada por qualquer pessoa no projeto apenas com access ao código.

Quanto à sua ideia de modelo / camada / camada: sim, fique com isso.

  • Chamadas do site Business layer (BL)
  • BL chama camada de dados (DL)
  • DL chama qualquer armazenamento (SQL, XML, Webservice, Sockets, Textfiles etc.)

Dessa forma, você pode manter o nível lógico entre camadas. SE e APENAS SE as chamadas DL parecem ser muito lentas, você pode começar a mexer com os Procedimentos Armazenados, mas manter o código original sem SP em algum lugar, se você precisar repentinamente transferir o DB para uma plataforma totalmente nova. Com toda a hospedagem de nuvem no negócio, você nunca sabe qual será a próxima plataforma de database …

Eu continuo de olho no Amazon AWS pela mesma razão.

Muita informação aqui para confundir as pessoas, o desenvolvimento de software é evolutivo. O que fizemos há 20 anos não é a melhor prática agora. De volta ao dia com o servidor cliente clássico, você não sonharia com nada além de SPs.

É absolutamente cavalos para os cursos, se você é uma grande organização com você irá usar multi-tier, e provavelmente SPs, mas você vai se importar pouco com eles, porque uma equipe dedicada irá classificá-los.

O oposto, que é onde eu me vejo tentando rapidamente criar uma solução de aplicativo web, que aprimora os requisitos de negócios, foi super rápido deixar o desenvolvedor (remoto para mim) derrubar as páginas e consultas SQL e definir o DB estrutura.

No entanto complexidade está crescendo e sem uma maneira fácil de fornecer APIs, estou olhando para usar SPs para conter a lógica de negócios. Eu acho que está funcionando bem e sensível, eu controlo isso porque eu posso construir lógica e fornecer um conjunto de resultados simples para o meu desenvolvedor offshore para construir um front end por aí.

Se eu achar que o meu software é um sucesso fenomenal, então mais separação de interesses ocorrerá e diferentes implementações do projeto acontecerão, mas por enquanto os SPs são perfeitos.

Você deve saber todos os conjuntos de ferramentas disponíveis para você e combiná-los é sábio para começar. A menos que você esteja construindo um sistema corporativo para começar, o mais rápido e simples é o melhor.

Eu acho que há muita desinformação flutuando sobre as consultas armazenadas no database.

Eu recomendaria usar o MySQL Stored Procedures se você estiver fazendo muitas consultas estáticas para manipulação de dados. Especialmente se você está movendo as coisas de uma mesa para outra (ou seja, passando de uma mesa ao vivo para uma mesa histórica por qualquer motivo). Há desvantagens, é claro, em que você terá que manter um log separado de alterações para elas (você poderia, em teoria, criar uma tabela que apenas mantenha alterações nos stored procedures que a atualização do DBA). Se você tiver muitos aplicativos diferentes fazendo interface com o database, especialmente se tiver um programa de área de trabalho escrito em C # e um programa da Web em PHP, talvez seja mais vantajoso ter alguns dos stored procedures no database, pois eles são independentes da plataforma.

Este site tem algumas informações interessantes que você pode achar úteis.

https://www.sitepoint.com/stored-procedures-mysql-php/

Como sempre, crie uma sandbox primeiro e teste.

Tente atualizar 100.000.000 de registros em um sistema ativo de uma estrutura e deixe-me saber como é. Para aplicativos pequenos, os SPs não são obrigatórios, mas para sistemas grandes e sérios, eles são um ativo real.