optimizar - Los procedimientos almacenados de MySQL los usan o no para usarlos

procedimientos almacenados mysql php (10)

A diferencia del código de lenguaje de programación actual, ellos:

no portátil (cada db tiene su propia versión de PL / SQL. A veces, las diferentes versiones de la misma base de datos son incompatibles, ¡lo he visto!)
no es fácilmente comprobable: se necesita una instancia de base de datos real (dev) para probarlos y, por lo tanto, es prácticamente imposible probar su código como parte de una compilación.
no fácilmente actualizable / liberable: debe soltar / crearlos, es decir, modificar la producción db para liberarlos
no tiene soporte de biblioteca (¿por qué escribir código cuando alguien más lo tiene?)
no son fácilmente integrables con otras tecnologías (intente llamar a un servicio web de ellos)
usan un lenguaje tan primitivo como Fortran y, por lo tanto, son poco elegantes y laboriosos para obtener una codificación útil, por lo que es difícil expresar la lógica comercial, aunque típicamente ese es su propósito principal.
no ofrezca depuración / seguimiento / registro de mensajes, etc. (algunos dbs pueden soportar esto, aunque no lo he visto)
carecen de un IDE decente para ayudar con la sintaxis y el enlace a otros procedimientos existentes (por ejemplo, como Eclipse lo hace para Java)
las personas capacitadas para codificarlas son más raras y más caras que los codificadores de aplicaciones
su "alto rendimiento" es un mito, ya que se ejecutan en el servidor de la base de datos, por lo general, aumentan la carga del servidor db, por lo que su uso generalmente reducirá el rendimiento máximo de sus transacciones.
incapacidad para compartir constantes de manera eficiente (normalmente se soluciona creando una tabla y probándola desde dentro de su procedimiento, muy ineficiente)
etc.

Si tiene una acción muy específica de la base de datos (por ejemplo, una acción en la transacción para mantener la integridad de DB), o mantiene sus procedimientos muy atómicos y simples, tal vez podría considerarlos.

Se recomienda precaución al especificar "alto rendimiento" en la delantera. A menudo conduce a malas elecciones a expensas de un buen diseño y te morderá mucho antes de lo que piensas.

Use procedimientos almacenados bajo su propio riesgo (de alguien que ha estado allí y nunca quiere regresar). Mi recomendación es evitarlos como la peste.

Estamos al comienzo de un nuevo proyecto, y realmente nos estamos preguntando si deberíamos usar procedimientos almacenados en MySQL o no.

Utilizaríamos los procedimientos almacenados solo para insertar y actualizar entidades del modelo comercial. Hay varias tablas que representan una entidad modelo, y la resumiríamos en esos procedimientos almacenados insertar / actualizar.

Por otro lado, podemos llamar inserción y actualización desde la capa Modelo, pero no en MySQL sino en PHP.

En tu experiencia, ¿cuál es la mejor opción? ventajas y desventajas de ambos enfoques. ¿Cuál es el más rápido en términos de alto rendimiento?

PD: Es un proyecto web con mayor lectura y alto rendimiento es el requisito más importante.

A diferencia del código de programación, ellos:

renderizar ataques de inyección SQL es casi imposible (a menos que seas
construir y ejecutar dinámicas
SQL desde dentro de sus procedimientos)
requiere que se envíen muchos menos datos a través del IPC como parte del texto destacado
Permiten a la base de datos tener mejores planes de caché y conjuntos de resultados (esto no es tan efectivo con MySQL debido a sus estructuras internas de almacenamiento en caché)
son fácilmente comprobables en forma aislada (es decir, no como parte de las pruebas JUnit)
son portátiles en el sentido de que le permiten usar funciones específicas de db, abstraídas detrás de un nombre de procedimiento (en el código está atrapado con cosas genéricas de tipo SQL)
casi nunca son más lentos que SQL llamado desde el código

pero, como dice Bohemian, también hay muchos contras (esto es solo por ofrecer otra perspectiva). Tendrás que comparar quizás antes de decidir qué es lo mejor para ti.

En cuanto a las actuaciones, tienen el potencial de ser realmente efectivas en una futura versión de MySQL (bajo SQL Server u Oracle, ¡son un verdadero placer!). Sin embargo, para el resto ... Ellos explotan totalmente la competencia. Voy a resumir:

Seguridad: puede darle a su aplicación el EJECUTAR correctamente, todo está bien. Su SP insertará la selección de actualización ..., sin posibles fugas de ningún tipo. Significa control global sobre su modelo y una seguridad de datos forzada.
Seguridad 2: Sé que es raro, pero a veces el código php se filtra del servidor (es decir, se vuelve visible para el público). Si incluye sus consultas, los posibles atacantes conocen su modelo. Esto es bastante extraño, pero quería señalarlo de todos modos
Grupo de trabajo: sí, la creación de SP SQL eficaces requiere algunos recursos específicos, a veces más caros. Pero si crees que no necesitas estos recursos solo porque estás integrando tus consultas en tu cliente ... vas a tener serios problemas. Mencionaría la analogía del desarrollo web: es bueno separar la vista del resto porque su diseñador puede trabajar en su propia tecnología mientras que los programadores pueden enfocarse en programar la capa de negocios.
Capa empresarial encapsulante: el uso de procedimientos almacenados aísla totalmente el negocio al que pertenece: la maldita base de datos.
Rápidamente comprobable: una línea de comando debajo de su caparazón y su código es probado.
Independencia de la tecnología del cliente: si mañana desea cambiar de php a otra cosa, no hay problema. De acuerdo, solo almacenar estos SQL en un archivo separado también resolvería el problema, eso es correcto. Además, un buen punto en los comentarios sobre si decides cambiar los motores sql, tendrías mucho trabajo por hacer. Tienes que tener una buena razón para hacerlo de todos modos, porque para grandes proyectos y grandes compañías, eso rara vez sucede (debido principalmente al costo y la gestión de recursos humanos)
Implementación de desarrollos ágiles de 3 + -tiers: si su base de datos no está en el mismo servidor que su código de cliente, puede tener servidores diferentes pero solo uno para la base de datos. En ese caso, no tiene que actualizar ninguno de sus servidores php cuando necesite cambiar el código relacionado con SQL.

Ok, creo que eso es lo más importante que tengo que decir sobre el tema. Desarrollé en ambos espíritus (SP versus cliente) y realmente, realmente amo el estilo SP. Solo deseé que Mysql tuviera un IDE real para ellos porque ahora mismo es una especie de ~~dolor en el culo~~ limitado.

Le recomendaría que se mantenga alejado de los procedimientos almacenados específicos de DB.

He pasado por muchos proyectos en los que repentinamente querían cambiar la plataforma de DB y el código dentro de un SP generalmente no es muy portátil = trabajo adicional y posibles errores.

El desarrollo del Procedimiento almacenado también requiere que el desarrollador tenga acceso directamente al motor SQL, mientras que cualquier persona en el proyecto que tenga acceso mediante código solo puede cambiar una conexión normal.

En cuanto a su idea de Modelo / capa / nivel: sí, quédese con eso.

Llamadas al sitio web Business layer (BL)
BL llama capa de datos (DL)
DL llama a cualquier almacenamiento (SQL, XML, Webservice, Sockets, Textfiles, etc.)

De esta manera puede mantener el nivel lógico entre niveles. SI y SÓLO SI las llamadas DL parecen ser muy lentas, puede comenzar a jugar con los Procedimientos almacenados, pero mantener el código original no SP en alguna parte, si de repente necesita transferir el DB a una plataforma completamente nueva. Con todo el alojamiento de la nube en el negocio, nunca se sabe cuál será la próxima plataforma de BD ...

Veo muy de cerca a Amazon AWS por la misma razón.

Los procedimientos almacenados son buenos de usar porque mantienen sus consultas organizadas y le permiten realizar un lote a la vez. Generalmente, los procedimientos almacenados se ejecutan rápidamente porque están precompilados, a diferencia de las consultas que se compilan en cada ejecución. Esto tiene un impacto significativo en situaciones donde la base de datos está en un servidor remoto; si las consultas están en un script PHP, hay múltiples comunicaciones entre la aplicación y el servidor de la base de datos: la consulta se envía, se ejecuta y el resultado se devuelve. Sin embargo, si usa procedimientos almacenados, solo necesita enviar una pequeña declaración CALL en lugar de consultas grandes y complicadas.

Puede llevar un tiempo adaptarse a la programación de un procedimiento almacenado porque tienen su propio lenguaje y sintaxis. Pero una vez que estés acostumbrado, verás que tu código está realmente limpio.

En términos de rendimiento, puede no ser una ganancia significativa si usa procedimientos almacenados o no.

Mucha información aquí para confundir a las personas, el desarrollo de software es evolutivo. Lo que hicimos hace 20 años no es la mejor práctica ahora. De vuelta en el día con el clásico cliente de servidor, no soñaría con nada más que SP.

Es absolutamente un caballo para los cursos, si usted es una gran organización, usará niveles múltiples, y probablemente SP, pero le importará poco porque un equipo dedicado los resolverá.

Lo opuesto, que es donde me encuentro tratando de derribar rápidamente una solución de aplicación web, que resuelve los requisitos del negocio, fue súper rápido dejar al desarrollador (a distancia) para desmantelar las páginas y las consultas SQL y definir el DB estructura.

Sin embargo, la complejidad va en aumento y, sin una forma sencilla de proporcionar API, estoy pensando en usar SP para contener la lógica comercial. Creo que está funcionando bien y es sensato, yo controlo esto porque puedo construir lógica y proporcionar un conjunto de resultados simple para que mi desarrollador offshore construya una interfaz.

Si encuentro que mi software tiene un éxito fenomenal, se producirá una mayor separación de preocupaciones y se producirán diferentes implementaciones de n teir, pero por ahora los SP son perfectos.

Debes conocer todos los conjuntos de herramientas disponibles para ti y combinarlos es una buena idea para empezar. A menos que esté construyendo un sistema empresarial para comenzar, entonces lo mejor es lo rápido y simple.

Si su base de datos es compleja y no es un tipo de foro con respuestas, pero el almacenamiento verdadero SP definitivamente se beneficiará. Puedes sacar toda la lógica de tu negocio allí y a ningún desarrollador le va a importar, simplemente llama a tus SP. He estado haciendo esto, juntar más de 15 tablas no es divertido, y no se puede explicar esto a un nuevo desarrollador.

Los desarrolladores tampoco tienen acceso a una base de datos, ¡genial! Deje eso a los diseñadores de bases de datos y mantenedores. Si también decide que la estructura de la tabla va a cambiar, puede ocultarla detrás de su interfaz. n-Tier, ¿recuerdas?

El alto rendimiento y los DB relacionales no son algo que vaya de la mano, ni siquiera con MySQL. InnoDB es lento, MyISAM debería ser descartado ahora. Si necesita rendimiento con una aplicación web, necesita la memoria caché adecuada, Memcache u otros.

en su caso, porque usted mencionó ''Web'', yo no usaría procedimientos almacenados; si fuera un almacén de datos, definitivamente lo consideraría (utilizamos SP para nuestro almacén).

Sugerencia: ¿Desde que mencionó el proyecto web, aunque alguna vez se trata de un tipo de solución nosql? Además, necesita una base de datos rápida, ¿por qué no utilizar PostgreSQL? (tratando de defender aquí ...)

Solía usar MySql y mi comprensión de sql era pobre en el mejor de los casos, pasé bastante tiempo usando Sql Server, tengo una separación clara de una capa de datos y una capa de aplicación, actualmente cuido un servidor con 0.5 terabytes.

A veces me he sentido frustrado al no usar un ORM, ya que el desarrollo es muy rápido con los procedimientos almacenados, es mucho más lento. Creo que gran parte de nuestro trabajo podría haber sido acelerado mediante el uso de un ORM.

Cuando su aplicación alcanza la masa crítica, el rendimiento del ORM sufrirá, un procedimiento almacenado bien escrito, le dará sus resultados más rápido.

Como ejemplo de rendimiento, recopilé 10 tipos diferentes de datos en una aplicación, luego los convertí a XML, que procesé en el procedimiento almacenado, tengo una llamada a la base de datos en lugar de 10.

Sql es realmente bueno tratando con conjuntos de datos, una cosa que me frustra es cuando veo que alguien obtiene datos de sql en forma cruda y usa código de aplicación para recorrer los resultados y formatearlos y agruparlos, esto realmente es una mala práctica .

Mi consejo es aprender y entender SQL lo suficiente y sus aplicaciones realmente se beneficiarán.

Voy a dejar saber mi opinión, a pesar de mis toughts posiblemente no están directamente relacionados con la pregunta .:

Como en muchos problemas, la respuesta sobre el uso de procedimientos almacenados o una solución basada en la capa de la aplicación se basa en preguntas que impulsarán el esfuerzo general:

Lo que quieres obtener

¿Estás tratando de hacer ya sea operaciones por lotes u operaciones en línea? ¿son completamente transaccionales? ¿Cuán recurrentes son esas operaciones? ¿Cuán pesada es la carga de trabajo esperada para la base de datos?

Lo que tienes para obtenerlo

¿Qué tipo de tecnología de base de datos tiene? ¿Qué tipo de infraestructura? ¿Su equipo está completamente capacitado en la tecnología de la base de datos? ¿Su equipo es más capaz de construir una solución de diagnóstico de base de datos?

Es hora de obtenerlo.

No hay secretos sobre eso.

Arquitectura.

¿Es necesario distribuir su solución en varios lugares? ¿Se requiere su solución para usar comunicaciones remotas? ¿Su solución está funcionando en varios servidores de bases de datos, o posiblemente usando una arquitectura basada en clústeres?

Mantenimiento.

¿Cuánto se requiere para cambiar la aplicación? ¿tiene personal específicamente entrenado para mantener la solución?

Gestión del cambio.

¿Ves que la tecnología de su base de datos cambiará a corto, medio, largo plazo? ¿cree que será necesario migrar la solución con frecuencia?

Costo

¿Cuánto costará implementar esa solución usando una u otra estrategia?

El conjunto de esos puntos generará la respuesta. Por lo tanto, debe tener en cuenta cada uno de estos puntos al tomar una decisión sobre el uso o no de cualquier estrategia. Hay casos en los que el uso de procedimientos almacenados es mejor que las consultas administradas en la capa de aplicación, y otros cuando, la realización de consultas y el uso de una solución basada en capas de aplicaciones es lo mejor.

El uso de procedimientos almacenados tiende a ser más inadecuado cuando:

Su tecnología de base de datos no se proporciona para cambiar en un corto tiempo.
La tecnología de su base de datos puede manejar operaciones en paralelo, particiones de tabla o cualquier otra estrategia para dividir la carga de trabajo en varios procesadores, memoria y recursos (clustering, grilla).
Su tecnología de base de datos está completamente integrada con el lenguaje de definición de documentos almacenados, es decir, el soporte está dentro del motor de la base de datos.
Usted tiene un equipo de desarrollo que no tiene miedo de usar un lenguaje de procedimiento (lenguaje de tercera generación) para obtener un resultado.
Las operaciones que desea lograr están integradas o admitidas dentro de la base de datos (Exportar a datos XML, gestionar la integridad y la coherencia de los datos de manera apropiada con desencadenantes, operaciones programadas, etc.).
La portabilidad no es un problema importante y usted no anuncia un cambio tecnológico en un corto tiempo en su organización, incluso, no es deseable. En general, la portabilidad es vista como un hito por los desarrolladores orientados a aplicaciones y orientados en capas. Desde mi punto de vista, la portabilidad no es un problema cuando no se requiere implementar su aplicación para varias plataformas, menos cuando no hay motivos para hacer un cambio tecnológico o cuando el esfuerzo para migrar todos los datos de la organización es más alto que el beneficio de hacer un cambio. Lo que puede ganar al usar un enfoque basado en capas de aplicaciones (portabilidad) puede perder en rendimiento y valor obtenido de su base de datos (Por qué gastar miles de dólares para obtener un Ferrari que manejará no más de 60 mil / hora ?).
El rendimiento es un problema. Primero: en varios casos, puede lograr mejores resultados utilizando una sola llamada de procedimiento almacenado que múltiples solicitudes de datos de otra aplicación. Además, algunas características que necesita realizar pueden integrarse en su base de datos y su uso es menos costoso en términos de carga de trabajo. Cuando utiliza una solución impulsada por la capa de aplicación, debe tener en cuenta el costo asociado para hacer conexiones de bases de datos, hacer llamadas a la base de datos, tráfico de red, ajuste de datos (es decir, usar Java o .NET). usando llamadas JDBC / ADO.NET ya que debe envolver sus datos en objetos que representan los datos de la base de datos, por lo que la instanciación tiene un costo asociado en términos de procesamiento, memoria y red cuando los datos provienen y van al exterior).

El uso de soluciones basadas en capa de aplicación tiende a ser más adecuado cuando:

La portabilidad es un tema importante.
La aplicación se implementará en varias ubicaciones con solo uno o pocos repositorios de bases de datos.
Su aplicación utilizará reglas pesadas orientadas a los negocios, que deben ser independientes de la tecnología de base de datos subyacente.
Tiene en mente cambiar los proveedores de tecnología según las tendencias del mercado y el presupuesto.
Su base de datos no está completamente integrada con el lenguaje de procedimiento almacenado que llama a la base de datos.
Las capacidades de su base de datos son limitadas y su requerimiento va más allá de lo que puede lograr con la tecnología de su base de datos.
Su aplicación puede soportar la penalización inherente a las llamadas externas, se basa más en las transacciones con las reglas específicas de la empresa y tiene que abstraer el modelo de la base de datos en un modelo comercial para los usuarios.
Paralelizar las operaciones de la base de datos no es importante, además, su base de datos no tiene capacidades de paralelización.
Usted tiene un equipo de desarrollo que no está bien capacitado en la tecnología de base de datos y es mejor productivo al usar una tecnología basada en aplicaciones.

Espero que esto ayude a cualquiera a preguntarse qué es mejor usar.

Yo recomendaría que no use procedimientos almacenados:

Su lenguaje en MySQL es muy malo
No hay forma de enviar matrices, listas u otros tipos de estructura de datos en un procedimiento almacenado
Un procedimiento almacenado no puede cambiar su interfaz; MySQL no permite parámetros nominales ni opcionales
Hace que la implementación de nuevas versiones de su aplicación sea más complicada, digamos que tiene 10x servidores de aplicaciones y 2 bases de datos, ¿cuál se actualiza primero?
Todos los desarrolladores necesitan aprender y comprender el lenguaje de procedimientos almacenados, lo cual es una porquería (como mencioné antes)

En cambio, recomiendo crear una capa / biblioteca y poner todas sus consultas allí

Usted puede

Actualice esta biblioteca y envíela a los servidores de su aplicación con su aplicación
Tener tipos de datos ricos, como matrices, estructuras, etc.
Pruebe la biblioteca de esta unidad, en lugar de los procedimientos almacenados.

En el rendimiento:

El uso de procedimientos almacenados disminuirá el rendimiento de los desarrolladores de su aplicación, que es lo que más le importa.
Es extremadamente difícil identificar problemas de rendimiento dentro de un procedimiento almacenado complicado (es mucho más fácil para consultas simples)
Puede enviar un lote de consulta en un solo fragmento por cable (si está habilitado el indicador CLIENT_MULTI_STATEMENTS), lo que significa que no obtendrá más latencia sin procedimientos almacenados.
El código del lado de la aplicación generalmente se escala mejor que el código del lado de la base de datos