SQL优化（exist和notexist优化为join提速30s）
前段时间上线⼀个项⽬，后⾯因为查询超时直接不显⽰数据，在测试环境是没有问题的，也是因为在测试环境数据量原因没什么异常，当时因为考虑不周也没有想到测试和⽣产的区别。
第⼀个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 .SCOPE_TYPE = '1'
UNION all
SELECT
distinct
SCOPEH.HOUSE_ID,SCOPEH.BUILDING_ID
FROM
house.T_STOREGROUP_SCOPE_HOUSE SCOPEH
WHERE
SCOPEH.SCOPE_TYPE = '1'
) sh on (sh.house_id = owner.HDIC_HOUSE_ID and sh.building_id = owner.building_id) or (sh.building_id=owner.building_id and sh.house_id = -1)    WHERE
OWNER .STATUS = '1'
执⾏时间3s
第⼆个SQL：
SELECT
OWNER .*
FROM
sql优化的几种方式
OWNER .T_OWNER_INFO OWNER
WHERE
OWNER .STATUS = '1'
AND EXISTS (
SELECT
1
FROM
house.T_STOREGROUP_SCOPE_NEW SCOPE
WHERE
SCOPE .SCOPE_TYPE = '2'
AND SCOPE .BUILDING_ID = OWNER .BUILDING_ID      UNION
SELECT
1
FROM
house.T_STOREGROUP_SCOPE_HOUSE SCOPEH      WHERE
SCOPEH.SCOPE_TYPE = '2'
AND SCOPEH.HOUSE_ID = OWNER .HDIC_HOUSE_ID    )
AND NOT 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    )
执⾏时间：30s
优化后：
SELECT
OWNER .*
FROM
OWNER .T_OWNER_INFO OWNER
join
(SELECT
-1 house_id,SCOPE.BUILDING_ID building_id
FROM
house.T_STOREGROUP_SCOPE_NEW SCOPE
group by -1, SCOPE.BUILDING_ID having wm_concat(distinct SCOPE.SCOPE_TYPE)='2'
UNION all
SELECT
SCOPEH.house_id,SCOPEH.BUILDING_ID
FROM
house.T_STOREGROUP_SCOPE_HOUSE SCOPEH
group by scopeh.house_id,scopeh.building_id having wm_concat(distinct SCOPEH.SCOPE_TYPE)='2') sh
on (sh.house_id = owner.HDIC_HOUSE_ID and sh.building_id = owner.building_id) or (sh.building_id=owner.building_id and sh.house_id = -1)
WHERE
OWNER .STATUS = '1'
优化后时间⼤约3s
其实还是不够快，所以⼜考虑了下索引，基本上关联的⼏个ID就是T_STOREGROUP_SCOPE_HOU
SE的house_id和building_id，两个建⼀个联合索引
T_STOREGROUP_SCOPE_NEW的building_id建索引
T_OWNER_INFO的building_id建索引
最后速度下来是不到1s
总结：
通过这次⾃⼰复盘，其实可以提前避免的。第⼀、每⼀个需求都需要提前考虑线上和⽣产的区别，或者其他⽅⾯全⾯考虑，思考各⽅⾯可能的影响来提前避免。第⼆、虽然APP没有预⽣产、但是接⼝可以先在预⽣产测试的，通过postman接⼝测试来提前看下有什么问题。第三、提⾼SQL⽔平，对于⾃⼰来说⾃⼰的SQL⽔平还有很⼤的提升空间，有时需要转换⾃⼰的四路，修炼内功，多说⽆益~

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。