优化阻塞我后端事务的MySQL存储过程

Question

我有一个简单的存储过程，它每天执行一次以更新；能量"；用户的数量取决于他们有多少材料。但这需要大约2分钟才能结束，我想知道是否有更好的方法：

BEGIN
SET @energy_premium = 10;
SET @energy_free = 5;
UPDATE user
SET energy = @energy_premium
WHERE id IN (
SELECT fk_user
FROM material
GROUP BY fk_user
HAVING COUNT(fk_user)>=2 AND user.id = material.fk_user);

UPDATE user
SET energy = @energy_free
WHERE id IN (
SELECT fk_user
FROM material
GROUP BY fk_user
HAVING COUNT(fk_user)=1 AND user.id = material.fk_user);
END

此外，当这个存储过程正在执行时，我的后端服务无法对数据库进行事务处理。

Answer 1

测试这个：

BEGIN
SET @energy_premium = 10;
SET @energy_free = 5;
UPDATE user
JOIN ( SELECT fk_user, CASE COUNT(fk_user) WHEN 1 
THEN @energy_free
ELSE @energy_premium
END energy
FROM material
GROUP BY fk_user ) mat ON user.id = mat.fk_user
SET user.energy = mat.energy;
END;