前段时间上线一个项目,后面因为查询超时直接不显示数据,在测试环境是没有问题的,也是因为在测试环境数据量原因没什么异常,当时因为考虑不周也没有想到测试和生产的区别。
第一个SQL:
SELECT
OWNER .*
FROM
OWNER .T_OWNER_INFO OWNER
WHERE
OWNER .STATUS = '1'
AND EXISTS (
SELECT
1
FROM
house.T_STOREGROUP_SCOPE_NEW SCOPE
WHERE
SCOPE .SCOPE_TYPE = '1'
AND SCOPE .BUILDING_ID = OWNER .BUILDING_ID
UNION
SELECT
1
FROM
house.T_STOREGROUP_SCOPE_HOUSE SCOPEH
WHERE
SCOPEH.SCOPE_TYPE = '1'
AND SCOPEH.HOUSE_ID = OWNER .HDIC_HOUSE_ID
)
执行时间大概20多秒
优化后:
SELECT
OWNER .*
FROM
OWNER .T_OWNER_INFO OWNER
join (
SELECT
distinct
-1 house_id,SCOPE.BUILDING_ID
FROM
house.T_STOREGROUP_SCOPE_NEW SCOPE
WHERE
SCOPE .SC