Question

怎么优化这条sql

<select id="accountBalancelist" resultType="map"> SELECT smi.storeName, smi.storeNo, smi.uuid, smi.createOpeTime, CASE WHEN started.start_balance IS NULL THEN 0 ELSE started.start_balance END start_balance, CASE WHEN ended.end_balance IS NULL THEN 0 ELSE ended.end_balance END end_balance, CASE WHEN added.add_amount IS NULL THEN 0 ELSE added.add_amount END add_amount, CASE WHEN reduced.reduce_amount IS NULL THEN 0 ELSE reduced.reduce_amount END reduce_amount FROM store_main_info smi LEFT JOIN ( SELECT ca2.balance start_balance, ca1.crt, ca2.storeUuid FROM ( SELECT ca.storeUuid, MAX(ca.createOpeTime) crt FROM store_capital_virtual_account_detail ca WHERE ca.createOpeTime <#{starttime} GROUP BY ca.storeUuid ) ca1 LEFT JOIN store_capital_virtual_account_detail ca2 ON ca1.storeUuid = ca2.storeUuid AND ca1.crt = ca2.createOpeTime ) started ON smi.uuid = started.storeUuid LEFT JOIN ( SELECT ca2.balance end_balance, ca1.crt, ca2.storeUuid FROM ( SELECT ca.storeUuid, MAX(ca.createOpeTime) crt FROM store_capital_virtual_account_detail ca WHERE ca.createOpeTime <#{endtime} GROUP BY ca.storeUuid ) ca1 LEFT JOIN store_capital_virtual_account_detail ca2 ON ca1.storeUuid = ca2.storeUuid AND ca1.crt = ca2.createOpeTime ) ended ON smi.uuid = ended.storeUuid LEFT JOIN ( SELECT d.storeUuid, SUM(d.operAmount) add_amount FROM store_capital_virtual_account_detail d WHERE d.inOrOut = 1 AND d.createOpeTime BETWEEN #{starttime} AND #{endtime} GROUP BY d.storeUuid ) added ON smi.uuid = added.storeUuid LEFT JOIN ( SELECT d.storeUuid, SUM(d.operAmount) reduce_amount FROM store_capital_virtual_account_detail d WHERE d.createOpeTime BETWEEN #{starttime} AND #{endtime} AND d.inOrOut = 2 AND d.businessType != 52 GROUP BY d.storeUuid ) reduced ON smi.uuid = reduced.storeUuid where 1=1 <if test="storeName != null and storeName != ''"> and smi.storeName LIKE "%"#{storeName}"%" </if> <if test="storeNo != null and storeNo != ''"> and smi.storeNo LIKE "%"#{storeNo}"%" </if> <if test="offset != null and limit != null"> limit #{offset}, #{limit} </if> </select>





Answer 1

你贴上来的语句中，有两个ca1
修改如下：
SELECT smi.storeName, smi.storeNo, smi.uuid, smi.createOpeTime,
CASE WHEN started.start_balance IS NULL THEN 0 ELSE started.start_balance END start_balance,
CASE WHEN ended.end_balance IS NULL THEN 0 ELSE ended.end_balance END end_balance,
CASE WHEN added.add_amount IS NULL THEN 0 ELSE added.add_amount END add_amount,
CASE WHEN reduced.reduce_amount IS NULL THEN 0 ELSE reduced.reduce_amount END reduce_amount
FROM store_main_info smi
LEFT JOIN (SELECT ca2.balance start_balance,ca1.crt,ca2.storeUuid
FROM(SELECT ca.storeUuid, MAX(ca.createOpeTime) crt
FROM store_capital_virtual_account_detail ca
WHERE ca.createOpeTime <{starttime}
GROUP BY ca.storeUuid ) ca1 LEFT JOIN store_capital_virtual_account_detail ca2 ON ca1.storeUuid = ca2.storeUuid AND ca1.crt = ca2.createOpeTime ) ended ON smi.uuid = ended.storeUuid
LEFT JOIN ( SELECT d.storeUuid, SUM(d.operAmount) add_amount
FROM store_capital_virtual_account_detail d
WHERE d.inOrOut = 1 AND d.createOpeTime BETWEEN {starttime} AND {endtime} GROUP BY d.storeUuid )
added ON smi.uuid = added.storeUuid
LEFT JOIN ( SELECT d.storeUuid, SUM(d.operAmount) reduce_amount FROM store_capital_virtual_account_detail d
WHERE d.createOpeTime BETWEEN #{starttime} AND #{endtime} AND d.inOrOut = 2 AND d.businessType != 52 GROUP BY d.storeUuid ) reduced ON smi.uuid = reduced.storeUuid where 1=1

Answer 2

推荐方法：
1、搜索条件中有like的话，推荐使用 ‘xxx%’的方式，这样可以用上索引，而 ‘%xxx%’这种方式会进行全表扫描
2、将left join 在查询完store_main_info后根据uuid单独查询

Answer 3

我勒个去 能先优化一下表结构嘛？这字段多的

Answer 4

子查询有点多了,如果数量级比较大可能会影响效率其他的没看出什么了