一个跨库复杂查询的SQL优化的案例_waste_land_wolf的博客-CSDN博客
一个跨库复杂查询的SQL优化的案例
waste_land_wolf
于 2017-07-31 10:22:35 发布
6764
收藏
分类专栏：
MySQL
文章标签：
mysql
优化
sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/waste_land_wolf/article/details/76419207
版权
MySQL
专栏收录该内容
1 篇文章
0 订阅
订阅专栏
导读
为了帮客户出一个报表，需要跨三个库进行一个复杂的查询，用到了 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
关注
关注
点赞
收藏
打赏
评论
一个跨库复杂查询的SQL优化的案例
导读为了帮客户出一个报表，需要跨三个库进行一个复杂的查询，用到了 in，union all，left join等关键字，其中几个字段还需要进行SUM，Count等计算。未优化前查询耗时368秒。待优化场景先看一下客户程序员写的这个复杂的SQL语句吧，看了都头大。虽说辉哥见多识广，还是被这个SQL吓到了。select distinct a.cn,e.chinese_nam
复制链接
扫一扫
专栏目录
sql 复杂查询，一些经典案例
07-12
sql 复杂查询，一些经典查询，希望对朋友有帮助！一些经典案例
评论 1
您还未登录，请先
登录
后发表或查看评论
SQL优化经验总结34条.pdf
11-03
34条SQL语句的优化方案，让你轻松写出性能高效的SQL语句。
MySQL-SQL优化
m0_50180963的博客
02-06
447
前言
在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的SQL就是整个系统性能的瓶颈。
SQL优化一般步骤
1、通过慢查日志等定位那些执行效率较低的SQL语句
2、explain 分析SQL的执行计划
需要重点关注type、rows、filtered、extra。
type由上至下，效率越来越高
ALL 全表扫描
index 索引全扫描
range 索引范围扫描，常用语<.
Mysql性能优化一：SQL语句性能优化
故人贪欢的博客
09-14
661
前几天在面试找工作，大多数都会问数据库优化、sql优化之类的，现在我基于sql优化总结了一些，另外也在网上查找了一些，这里总结了52条对sql的查询优化，下面详细来看看，希望能帮助到你！
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值。
应尽量避免在 where 子句中使用!
mysql跨库查询
最新发布
ljs62811123的博客
10-26
408
跨库查询
工作中复杂sql优化
Gakooon的博客
04-27
288
复杂sql优化思路
1 .确定方向
数据量：客户与标签关系表 ： 数据量在 3W左右 后面预计 几十W
主标签表：目前几百左右 预计在几K
子标签： 目前不到3K 预计 1W多
标签权限表：目前5K 预计5W多
目标：查询单个客户，展示格式为：
客户 + 基础信息1 +基础信息2 +基础信息3 +手动标签 +自动标签
带标签筛选 ，基础信息1 ，2 ，3 筛选 ，手动标签 筛选
2.实现方案
初始实现方案
查询客户与标签关系表 in 加外部接口筛选出来的客户
LEFT JOIN 客
SQL 复杂查询
前端精读周刊
03-14
2301
SQL 复杂查询指的就是子查询。为什么子查询叫做复杂查询呢？因为子查询相当于查询嵌套查询，因为嵌套导致复杂度几乎可以被无限放大（无限嵌套），因此叫复杂查询。下面是一个最简单的子查询例子：S...
一次复杂的SQL优化过程
lyu1026的博客
04-11
2818
一、背景
这个复杂的查询SQL语句，已经进行语法优化，写入应用程序中，且不希望修改代码，实现执行速度大幅度提升。
查询语句中，使用了用户自定义函数，多个视图嵌套，代码逻辑复杂，运行时长过长。
分析方向，基于查询计划，定位耗时较多的节点，通过改变调用对象，实现优化查询性能。
二、查询语句，优化前后的计划
SQL语句如下：
analyse;
explain (analyse ,buffers ,verbose ,costs ,timing )
with t as
(select.
sql优化的N种方法_持续更新
wang5701071的博客
09-25
5万+
当你访问网站的时候,有的时候会慢的想让你砸电脑,这个时候服务器要背锅了吗? 不,要背锅的不仅仅是服务器,数据库也有很大责任,不负责任的sql开发者更会让你崩溃的.为了提高sql响应速度,还是好好了解下sql的优化吧
sql优化的方式
1: explain关键字 :使用explain关键字来查看当前sql语句的执行情况,来对症下药.因为内容较多,放在最后进行讲解
2: 正确的建立索引:索引的建立对sql的影响是非常大的,如果对索引不太理解的可以看我另一篇文章: ............
SQL复杂查询知识梳理
wenqi
12-19
1000
视图
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
为什么使用视图
重用SQL语句
简化复杂的SQL操作，编写查询后，可以方便地重用它而不必知道它的基本查询细节
使用表的组成部分而不是整张表
保护数据，可以给用户授予表的特定部分的访问权限而不是整张表的访问权限
更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据
性能问题:
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发
sql复杂查询
m0_45226909的博客
06-29
405
create table student(
sno varchar2(12) primary key,
sname varchar2(22),
sage number(6),
ssex varchar2(8)
);
create table teacher(
tno varchar2(12) primary key,
tname varchar2(22)
);
create table .
复杂SQL优化
daiqiulong2的博客
04-03
1242
SQL：
select *
from (select distinct action_Type_Name as actionName,
toRZ,
custName,
...
SQL复杂查询：百分率问题、case when的运用
YY的博客
04-07
398
重点关注case when的运用
题目：统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select course.c_id,course.c_name,
sum(level1) as '[0-60]人数',sum(level1)*100/count(*) as '[0-60]占比',
sum(level2) as '[60-70]人数',sum(level2)*100/count(*) as '[60-70]占比',
sum.
SQL学习|复杂查询
DXRfighting的博客
08-18
126
使用视图
视图是虚拟的表。看下面一个例子：
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
可以将整个查询包装成一个productcustomers的虚拟表：
SELECT cust_name,
一个复杂子查询SQL优化
@DBA_白老大
05-02
2511
MySQL中一个复杂子查询的优化。
一次非常有意思的SQL优化经历:从30248.271s到0.001s
iteye_6274的博客
05-19
1057
场景
我用的数据库是mysql5.6，下面简单的介绍下场景
课程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
数据100条
学生表:
create table Student(
id int PRIMARY KEY,
name varchar(10)
数据70000条
...
SQL语句常见优化十大案例
weixin_33975951的博客
03-03
1039
1、慢SQL消耗了70%~90%的数据库CPU资源；2、SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；3、SQL语句可以有不同的写法；下面是我总结的一些SQL常见的优化方法，每个案例都简单易懂，在开发过程中可以作为参考：1、不使用子查询例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE...
SQL优化经典案例合集
24号信仰
09-01
2586
案例即笔记，难免有疏漏。如对案例有任何问题 请直接留言或者联系本人（微信/手机号：15652625652）
我很乐意和大家相互学习，共同进步！！
34.关注业务-把优化做到极致了解业务，优化新高度
33.一波三折：UPDATE语句改写优化 UPDATE/MERGE/分批提交 哪个高效用哪个！！！
32.一次'诡异'的执行SQL报错ORA-03113的问题处理这个ORA报错很肤浅...
史上最全SQL优化方案（一）
weixin_44542399的博客
04-02
1480
在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。
1、优化的哲学注：
优化有风险，涉足需谨慎
a 优化可能带来的问题？
（1）优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统；
（2）优化手段本来就有很大的风险，只不过你没能力意识到和预见到
（3）任何的技术可以解决一个...
数据库SQL优化大总结之 百万级数据库优化方案
热门推荐
帅性而为1号的博客
06-23
14万+
网上关于SQL优化的教程很多，但是比较杂乱。近日有空整理了一下，写出来跟大家分享一下，其中有错误和不足的地方，还请大家纠正补充。
这篇文章我花费了大量的时间查找资料、修改、排版，希望大家阅读之后，感觉好的话推荐给更多的人，让更多的人看到、纠正以及补充。
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 w
“相关推荐”对你有帮助么？
非常没帮助
没帮助
一般
有帮助
非常有帮助
提交
©️2022 CSDN
皮肤主题：大白
设计师：CSDN官方博客
返回首页
waste_land_wolf
CSDN认证博客专家
CSDN认证企业博客
码龄18年
暂无认证
原创
47万+
周排名
30万+
总排名
9296
访问
等级
121
积分
粉丝
获赞
评论
收藏
私信
关注
热门文章
一个跨库复杂查询的SQL优化的案例
6764
Python3.4 安装mysqlClient提示 Microsoft Visual C++ 10.0 is required (Unable to find vcvarsall.bat).错误的解决
2533
分类专栏
Python编程
1篇
MySQL
1篇
最新评论
一个跨库复杂查询的SQL优化的案例
qq_27020205:
你应该把优化的内容，也贴出来，别人不清楚你是怎么解决，就想没解决一样。
您愿意向朋友推荐“博客详情页”吗？
强烈不推荐
不推荐
一般般
推荐
强烈推荐
提交
最新文章
Python3.4 安装mysqlClient提示 Microsoft Visual C++ 10.0 is required (Unable to find vcvarsall.bat).错误的解决
2017年2篇
目录
目录
分类专栏
Python编程
1篇
MySQL
1篇
目录
评论 1
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
打赏作者
waste_land_wolf
你的鼓励将是我创作的最大动力
¥2
¥4
¥6
¥10
¥20
输入1-500的整数
余额支付
(余额：-- )
扫码支付
扫码支付：¥2
获取中
扫码支付
您的余额不足，请更换扫码支付或充值
打赏作者
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额
抵扣说明：
1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。 2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。
余额充值