sum函数会走全表?

【 TiDB 使用环境】开发环境
【 TiDB 版本】6.5
【复现路径】sql查询
【遇到的问题:问题现象及影响】
原sql:
IF(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM(G.AMOUNT), 0)
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID),
(SELECT IFNULL(SUM(PRICE * NUM), 0)
FROM CIS_ORDER_FEE F
WHERE F.ORDER_NO = A.ORDER_NO
AND F.IN_VISIT_ID = A.REG_ID)) REQUEST_FEE
执行计划中,走全表,爆内存
修改sql为
IF
(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM((select G.AMOUNT
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID)
), 0)),
(SELECT IFNULL(
(select SUM(TEMP.PRICE * TEMP.NUM)
from (select PRICE,
NUM
FROM CIS_ORDER_FEE F
WHERE F.IN_VISIT_ID = A.REG_ID
and F.ORDER_NO = A.ORDER_NO) TEMP), 0))
) REQUEST_FEE
全命中索引
试了几个都是这样,区别就是没有再包一层,sum等这些聚合函数这么写就无法命中索引?
【资源配置】
【附件:截图/日志/监控】

上传一下执行计划看看

image

这种写法对吗,虽然能执行

修改前
IF(A.SOURCE_TYPE = 1,
(SELECT IFNULL ( SUM( G.AMOUNT ), 0 ) FROM
OIS_BILL F,
OIS_BILL_DETAILS G
WHERE
F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID
),
(SELECT IFNULL ( SUM( PRICE * NUM ), 0 ) FROM
CIS_ORDER_FEE F
WHERE
F.ORDER_NO = A.ORDER_NO
AND F.IN_VISIT_ID = A.REG_ID
)) REQUEST_FEE

修改后
IF
(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM((select G.AMOUNT
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID)
), 0)),
(SELECT IFNULL(
(select SUM(TEMP.PRICE * TEMP.NUM)
from (select PRICE,
NUM
FROM CIS_ORDER_FEE F
WHERE F.IN_VISIT_ID = A.REG_ID
and F.ORDER_NO = A.ORDER_NO) TEMP), 0))
) REQUEST_FEE

原sql

IF(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM(G.AMOUNT), 0)
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID),
(SELECT IFNULL(SUM(PRICE * NUM), 0)
FROM CIS_ORDER_FEE F
WHERE F.ORDER_NO = A.ORDER_NO
AND F.IN_VISIT_ID = A.REG_ID)) REQUEST_FEE

上TiFlash,速度嗖嗖的

为何要在数据库执行函数操作,有点费解