es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

  • es.davy.ai/
  • Programación /
  • Optimice el procedimiento almacenado de MySQL que está bloqueando mis transacciones de back-end.

Optimice el procedimiento almacenado de MySQL que está bloqueando mis transacciones de back-end.

19 Jul, 2023 Programación

Tengo este procedimiento almacenado simple que se ejecuta una vez al día para actualizar la “energía” de los usuarios dependiendo de cuántos materiales tengan. Pero esto tarda alrededor de 2 minutos en finalizar y me pregunto si hay una mejor manera de hacerlo:

COMIENZO
   SET @energy_premium = 10;
    SET @energy_free = 5;

    ACTUALIZAR usuario
    SET energia = @energy_premium
    DONDE id EN (
        SELECCIONAR fk_user
        DESDE material
        AGRUPAR POR fk_user
        HAVING CONTAR(fk_user)>=2 AND usuario.id = material.fk_user );

    ACTUALIZAR usuario
    SET energia = @energy_free
    DONDE id EN (
        SELECCIONAR fk_user
        DESDE material
        AGRUPAR POR fk_user
        HAVING CONTAR(fk_user)=1 AND usuario.id = material.fk_user );
FIN

Además, cuando este procedimiento almacenado se está ejecutando, mis servicios backend no pueden realizar transacciones en la base de datos.

Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    El procedimiento almacenado dado actualiza la energía de los usuarios en función del número de materiales que tienen. Sin embargo, tarda alrededor de 2 minutos en ejecutarse y los servicios de backend no pueden realizar transacciones en la base de datos durante ese tiempo.

    Una solución potencial para reducir el tiempo de ejecución del procedimiento almacenado es optimizar las consultas utilizadas en él. En lugar de usar subconsultas, el uso de operaciones JOIN podría mejorar el rendimiento. Por ejemplo, las dos consultas en el procedimiento almacenado se pueden reescribir de la siguiente manera:

    UPDATE user u
INNER JOIN (
    SELECT fk_user, COUNT(*) AS material_count
    FROM material
    GROUP BY fk_user
    HAVING material_count >= 2
) m ON u.id = m.fk_user 
SET u.energy = 10;

UPDATE user u
INNER JOIN (
    SELECT fk_user, COUNT(*) AS material_count
    FROM material
    GROUP BY fk_user
    HAVING material_count = 1
) m ON u.id = m.fk_user 
SET u.energy = 5;

    Este enfoque reduce el uso de subconsultas, que a menudo pueden ser más lentas que las operaciones JOIN. Sin embargo, es importante asegurarse de que los índices adecuados estén en su lugar para las tablas utilizadas en las consultas.

    Además, para evitar bloquear los servicios de backend, se recomienda utilizar el nivel de aislamiento READ UNCOMMITTED para el procedimiento almacenado. Esto permite que el procedimiento almacenado lea datos no confirmados, que pueden estar sujetos a cambios por otras transacciones. Esto puede mejorar el rendimiento del procedimiento almacenado y minimizar el bloqueo de otras transacciones.

    En general, optimizar las consultas utilizadas en el procedimiento almacenado y utilizar los niveles de aislamiento adecuados pueden ayudar a mejorar el rendimiento del procedimiento almacenado y minimizar el bloqueo de los servicios de backend.

Comments are closed.