一个跨库复杂查询的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多倍,其中
索引有一大半的功劳,另外两个视图的建立也不可忽略。



分享给朋友:
您可能感兴趣的文章:
随机阅读: