MySQL优化：如何避免重复调用同一子查询？

首先帮你修正原SQL里的语法错误：子查询的as Kred2位置错误，应该放在子查询的括号外面，修正后的原SQL如下：

SELECT kred - 
   ( SELECT SUM((UNIX_TIMESTAMP()-timeStampSecondsColumn)* 0.1666666666666667) as total 
     FROM sku 
     WHERE phone=(SELECT phone FROM sku WHERE id=10)
   ) as Kred2
from users 
WHERE phone=(SELECT phone FROM sku WHERE id=10)

针对你重复调用子查询的问题，有以下几种优化方式：

方案一：正确使用用户变量 #

你之前的变量赋值没生效，大概率是因为没有在同一个会话中执行赋值语句，或者查询里的引用方式不对。可以按以下两种方式实现：

1. 分两步执行：

-- 先在当前会话中赋值变量
SET @phone = (SELECT phone FROM sku WHERE id=10);

-- 再执行主查询
SELECT kred - 
   ( SELECT SUM((UNIX_TIMESTAMP()-timeStampSecondsColumn)* 0.1666666666666667) as total 
     FROM sku 
     WHERE phone=@phone
   ) as Kred2
from users 
WHERE phone=@phone;

2. 单语句执行（适合一次性运行）：

SELECT kred - 
   ( SELECT SUM((UNIX_TIMESTAMP()-timeStampSecondsColumn)* 0.1666666666666667) as total 
     FROM sku 
     WHERE phone=@phone
   ) as Kred2
from users, (SELECT @phone := phone FROM sku WHERE id=10) as init
WHERE phone=@phone;

方案二：使用JOIN关联查询（推荐，性能更稳定） #

通过JOIN先获取目标phone值，关联users表后再计算，彻底避免重复子查询：

SELECT u.kred - (
    SELECT SUM((UNIX_TIMESTAMP()-timeStampSecondsColumn)* 0.1666666666666667)
    FROM sku s
    WHERE s.phone = sku_phone.phone
) as Kred2
FROM users u
JOIN (SELECT phone FROM sku WHERE id=10) sku_phone ON u.phone = sku_phone.phone;

如果sku表中同一phone对应多条记录，也可以提前计算好总和再关联：

SELECT u.kred - s.total as Kred2
FROM users u
JOIN (SELECT phone FROM sku WHERE id=10) sku_phone ON u.phone = sku_phone.phone
JOIN (
    SELECT phone, SUM((UNIX_TIMESTAMP()-timeStampSecondsColumn)* 0.1666666666666667) as total
    FROM sku
    GROUP BY phone
) s ON sku_phone.phone = s.phone;

方案三：使用CTE（MySQL 8.0+版本支持） #

用WITH子句定义公共表达式存储目标phone，后续直接引用即可：

WITH sku_phone AS (
    SELECT phone FROM sku WHERE id=10
)
SELECT u.kred - (
    SELECT SUM((UNIX_TIMESTAMP()-timeStampSecondsColumn)* 0.1666666666666667)
    FROM sku s
    WHERE s.phone = (SELECT phone FROM sku_phone)
) as Kred2
FROM users u
WHERE u.phone = (SELECT phone FROM sku_phone);

关于你之前变量赋值失败的原因 #

• 如果是单独执行@phone:= (SELECT phone FROM sku WHERE id=10);后切换了会话再执行主查询，变量会失效，必须在同一个会话中完成赋值和主查询操作。
• 如果sku表中id=10的记录不存在，变量会被赋值为NULL，导致主查询无结果。

内容的提问来源于stack exchange，提问作者wuqn yqow