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

case when 的效率低于isnull()，这是第一个要改的
-----------------------------------------------------------
然后可以使用表表达式来优化查询语句本身，表表达式在查询中只执行一次，这就避免了一些重复的工作 。
started与endeds可以共用一个表表达式：
select storeUuid
,case when createOpeTime < #{starttime} then 0 else 1 end started
,max(createOpeTime) createOpeTime
from store_capital_virtual_account_detail
where createOpeTime >= #{endtime}
group by storeUuid
,case when createOpeTime < #{starttime} then 0 else 1 end
类似的，added与reduced也可以共用一个表表达式：

select storeUuid
,inOrOut
,sum(operAmount) add_amount
from store_capital_virtual_account_detail
where (inOrOut=1 or (inOrOut=1 and businessType != 52))
and createOpeTime between #{starttime} and #{endtime}
group by storeUuid,inOrOut
------------------------------------------------------------

再然后如果还需要进一步优化,那么，就要考虑那些可选的条件了。例如，where 条件直接写成smi.storeName LIKE .... and smi.storeNo LIKE ...,对参数进行预处理,没有它们就处理为''
如果能够限定这两个参数至少有一个不为空，那么，可以预见smi集就会小很多，那么，可以进一步把smi相关的查询部分也做成cte，并且用它们对cte_1和cte_2进行过滤以减少cte_1和cte_2做group时前中间集的大小，就能进一步提高效率。
----------------------------------------------------------
如果还不能满足需要，那么，还可以对表结构进行优化。
很多使用java的程序员设计表时为了方便,将很多字段都设计为字符类型,这减少了java代码中对类型的转换处理，但是，查询过程中计算压力都转稼到了sql引擎。当数据量大的时候，对字符字段的搜索效率是远低于一个适当的字段类型的，因为在查询过程中动态索引时，字段宽度与索引时间是强相关的。
inOrOut可以定义为tinyint，这只需要一个字节,可以存放256个不同的值(0~255或-128~127),记录时间的字段首选timestamp,如果不能满足需要可用datetime,它们分别需要4字节与8字节存储空间。

Answer 2

SELECT smi.storeName, smi.storeNo, smi.uuid, smi.createOpeTime
, CASE
WHEN started.start_balance IS NULL THEN 0
ELSE started.start_balance
END AS start_balance
, CASE
WHEN ended.end_balance IS NULL THEN 0
ELSE ended.end_balance
END AS end_balance
, CASE
WHEN added.add_amount IS NULL THEN 0
ELSE added.add_amount
END AS add_amount
, CASE
WHEN reduced.reduce_amount IS NULL THEN 0
ELSE reduced.reduce_amount
END AS reduce_amount
FROM store_main_info smi
LEFT JOIN (
SELECT ca2.balance AS start_balance, ca1.crt, ca2.storeUuid
FROM (
SELECT ca.storeUuid, MAX(ca.createOpeTime) AS 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 AS end_balance, ca1.crt, ca2.storeUuid
FROM (
SELECT ca.storeUuid, MAX(ca.createOpeTime) AS 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) AS 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) AS 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
以上是你的SQL
首先你WHERE条件是1=1等于针对 store_main_info 进行全表扫描。如果store_main_info数据量大，这样任何优化都无意义。
其次ca1 效果等同子查询，你这样是可以利用子查询替代的。
started和ended比较搞笑了，之前逻辑照遍一次，就是ca1的时间不同 这个情况下其实可以通过 case ca.createOpeTime 这个值取歧义来进行效率更快，或者使用子查询就不需要重复差ca2的表了。
added和reduced 也是一个逻辑，只是查询条件 inOrOut 不同还有就是 reduced 多了个 businessType != 52
先确认一下 store_main_info 的数量这个是关键。

Answer 3

这是建表语句，不存在是否需要优化。
另外优化sql，要和你的实际业务结合，不是单纯的看到sql就可以优化的。
祝好运，望采纳。

Answer 4

首先。。可以把格式缩进规范化，方便迭代开发人员的阅读理解和修改。
其次，建议优化数据库结构设计，这么多表关联查询。。。一般会有更好的设计可以优化。
同时。。like模糊查询全表检索效率很慢，建议加一些索引以及使用INSTR函数提高执行效率。

Answer 5

其实涉及到判断是否为空的，是null就赋值0的，你可以这样简单写：
isnull( started.start_balance ,0)
这句话可以代替你用 ：CASE WHEN started.start_balance IS NULL THEN 0 ELSE started.start_balance END
你其他的都涉及判断null的都可以这样写，就简化了很多。