跨库复杂查询的SQL优化的案例(转)

导读
为了帮客户出一个报表，需要跨三个库进行一个复杂的查询，用到了 in，union all，left join等关键字，
其中几个字段还需要进行SUM，Count等计算。未优化前查询耗时368秒。
待优化场景
先看一下客户程序员写的这个复杂的SQL语句吧，看了都头大。虽说辉哥见多识广，还是被这个SQL吓到了。


select distinct a.cn,e.chinese_name new_type,d.chinese_name partner,a.csum,b.region_name region,c.login_account saler,
#已出库成本 -->
(select IFNULL(SUM(purch_price),0.0000) from stock_info where is_delete='N' and out_voucher_no in
(select voucher_no from normal_outbound where is_delete='N' and contract_examination_status='5' and cn=a.cn
union all
select voucher_no from abnormal_outbound where is_delete='N' and contract_examination_status='5' and cn=a.cn
)) outbound_cost,
#销售合同成本核算成本 -->
(select IFNULL(SUM(price_contract),0.0000) from sale_contract_cost where is_delete='N' and sale_contract_id=a.cn)
sale_contract_cost,
#已出库销售金额 -->
'0.0000' out_sale_amount,
#合同结算金额 -->
(select IFNULL(SUM(con_set_amount),0.0000) from cinout_info where is_delete='N' and cn=a.cn)
con_set_amount,
#已收款金额 -->
(select IFNULL(SUM(money),0.0000) from receivable_recorder where is_delete='N' and cn=a.cn)
actual_pay,
#未收款金额=合同结算金额-已收款金额 用Excel公式计算 -->
'0.0000' not_actual_pay,
#已开票金额 -->
(select IFNULL(SUM(invoice_amount),0.0000) from invoice_info where is_delete='N' and cn=a.cn)
invoice_amount,
#合同签订数量 -->
(select sum(quantity) from sale_contract_cost where is_delete='N' and sale_contract_id=a.cn)
contract_quantity,
#实际出库数量 -->
(select COUNT(sn) from stock_info where is_delete='N' and out_voucher_no in
(select voucher_no from normal_outbound where is_delete='N' and contract_examination_status='5' and cn=a.cn
union all
select voucher_no from abnormal_outbound where is_delete='N' and contract_examination_status='5' and cn=a.cn
)) outbound_quantity,
#未开票金额=合同结算金额-已开票金额 -->
'0.0000' not_invoice_amount,
'' memo

from db0.sale_contract_info a
left join db1.region_info b on a.region=b.uuid
left join db1.user_info c on a.saler_id=c.uuid
left join db1.partner_info d on a.partner_id=d.code_by_system
left join db2.jrunion_dictionary e on a.new_type=e.english_name
where
a.is_delete='N' and a.is_close='N' and a.is_z



SQL优化思路
想要优化一个SQL，一般来说就是先看执行计划，观察是否尽可能用到索引，
同时要关注预计扫描的行数，以及是否产生了临时表（Using temporary）
或者 是否需要进行排序（Using filesort），想办法消除这些情况。
更进一步的优化策略则可能需要调整程序代码逻辑，甚至技术架构或者业务需求，
这个动作比较大，一般非核心系统上的核心问题，不会这么大动干戈，绝大多数情况，

还是需要靠DBA尽可能发挥聪明才智来解决。


优化思考
首先观察这个查询SQL语句，貌似复杂，其实有规律可寻。我们分成三部分来看，即select部分，from部分和查询条件部分。
其中比较复杂的是select部分，很多字段是通过子查询，计算和联合获得的。
其次通过分步测试，发现加上“已出库成本outbound_cost”和“实际出库数量outbound_quantity”这两个字段查询比较慢，
再对这两个字段的子查询分析，如果去年其中的查询条件cn=a.cn，查询速度可以大大提高。但是这样查出来的数据经过
计算是错误的。据此我怀疑是不是因为a的定义是在子查询外面两层，导致查询速度降低。于是我决定将这两个字段的子查询语句
做成两个view，再从这两个view里查询。这样，子查询就不用到外层去查询a表和a表的cn字段去对比，数据也不会出错。
改完这块之后，查询速度果然提高了近10倍，查询耗时缩短为39秒左右。但是这离客户要求的3-4秒还很远。
按照老叶（mySQL培训机构知数堂的创始人，国内著名mySQL专家）的提示，查看了下执行计划，发现所有表中都没有建索引。按照老叶的指导，建立了一堆单一和联合索引。再试，2.9秒！速度差不多又提高了15倍。


后记
见到复杂的SQL语句，请拍拍左胸，提醒自己不要害怕。然后心中默念几句：我是无所不能，魔挡杀魔，佛挡杀佛的全栈DBA。

老叶说过：绝大多数的SQL通过添加索引、适当调整SQL代码等简单手法来完成。本例中查询效率之所以能提高100多倍，其中
索引有一大半的功劳，另外两个视图的建立也不可忽略。
---------------------
作者：waste_land_wolf
来源：CSDN
原文：https://blog.csdn.net/waste_land_wolf/article/details/76419207
版权声明：本文为博主原创文章，转载请附上博文链接！