精华内容
下载资源
问答
  • 2018年4月20日,移动某平台江苏某业务系统发起SQL调优请求,在本次MySQL调试过程中遇到了exists关联子查询导致的sql性能及其低下,并且相关的SQL语句执行速度及其不稳定忽快忽慢;通过重写相关SQL的exists部分修改成...
        2018年4月20日,移动某平台江苏某业务系统发起SQL调优请求,在本次MySQL调试过程中遇到了exists关联子查询导致的sql
    性能及其低下,并且相关的SQL语句执行速度及其不稳定忽快忽慢;通过重写相关SQL的exists部分修改成等值子查询,相关sql的
    性能得到极大提高,并且执行速度比较稳定。本次SQL优化过程记录如下:
        1、环境信息:
        操作系统版本:CENTOS 7.2.1151
        数据库版本:5.7.17-log
        2、查看MySQL慢日志相关信息
    ysql>show variables like '%slow_query_%';
    -+-------------------------------------------+--------------------------------------------------------------------------------+
    |        Variable_name                        |                                    Value                                      |
    -+-------------------------------------------+--------------------------------------------------------------------------------+
    |slow_query_log                               | ON                                                                            |
    |slow_query_log_file                         |/data/mysql/db/elog/slow.log                                                    |
    -+-------------------------------------------+--------------------------------------------------------------------------------+
        2、使用pt工具分析MySQL的慢日志
    cd   /data/mysql/db/elog
    pt-query-digest slow.log  --since '2018-04-13 00:00:00'  --until '2018-04-19 00:00:00' >>slow_report_log
        3、查看慢日志分析报告 slow_report_log

    通过慢日志分析报告发现两条运行缓慢的SQL语句Query_ID分别是0xEA33702BDD78E0BA、0x56B6B418ADAAB135,其中
    0xEA33702BDD78E0BA的响应时间占据了整个数据库时间的97.1%,是重点优化对象。
        4、明确需要优化的对象
    --第一条需要优化的重点对象 0xEA33702BDD78E0BA

    --SQL执行计划

    -- SQL执行统计信息概要

    --第二条要优化的SQL对象
    0x56B6B418ADAAB135

    --SQL执行计划

    -- SQL执行统计信息概要

        5、首先看第一条SQL语句,自己的优化思路,确定SQL语句中慢的具体位置
    --对第一条SQL语句拆分,以union为界分两部分
    -- 0xEA33702BDD78E0BA第一部分:0xEA33702BDD78E0BA_part1

    0xEA33702BDD78E0BA_part1部分SQL语句查询只有13条记录,执行速度非常快0.00秒完成

    -- 0xEA33702BDD78E0BA第二部分:0xEA33702BDD78E0BA_part2

    0xEA33702BDD78E0BA_part2部分SQL(该部分查询正常出结果是103条数据)语句执行超过10s,超时退出

        6、接下来着重关注 0xEA33702BDD78E0BA第二部分:0xEA33702BDD78E0BA_part2

    观察SQL语句发现有EXISTS关联子查询,我的理解是,原先sql被优化器改写,主查询执行多次,每次主查询sql都不同,
    都会发起物理读盘扫表。于是考虑将其改为等值连接子查询:

    -- SQL改写后(查询结果集与修改前一致): 两个等值查询表结果集在内存,接下来是最终 结果集匹配和过滤,执行速度只有0.01秒

    7、修改后的完整SQL如下

    --修改后的sql执行速度,有原先的超过10s超时中断退出降低到0.00秒以下。

    --SQL改写后的执行计划


    8、第二条SQL语句采用第一条SQL语句优化方法,将exists关联子查询改写为表关联的等值子查询
    --改写前的执行效率

    --改写后的执行效率

    --改写后的SQL执行计划


    9、总结
    通过观察SQL改写前后的执行计划,可以发现改写后的SQL 执行中间结果集均已缓存到内存(Using join buffer(Block Nested Loop)),内存结果集匹配过滤降低了表的物理IO,从而提高了SQL的执行性能。



    展开全文
  • 先说说 : sql的编写顺序 select .. from .. where .. group by ..having .. order by ..sql的执行顺序 ...关联子查询 : 在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在关联子查询...
    先说说 : 
    
    sql的编写顺序
        select .. from .. where .. group by ..having .. order by ..
    sql的执行顺序

        from .. where .. group by .. having .. select .. order by ..


    关联子查询 :

    在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并返回它的记录。然后,外部查询根据返回的记录做出决策。

    select * from dept d where exists(select * from emp e where e.deptno = d.deptno);

        特点
            1. 先执行外层查询

            2. 再执行内层查询


    非关联子查询:非相关子查询是独立于外部查询的子查询,子查询执行完毕后将值传递给外部查询
        select * from emp where sal = (select max(sal) from emp);
        特点
            1. 先执行内层查询
            2. 再执行外层查询
      

    展开全文
  • SQL子查询和关联子查询

    千次阅读 2020-09-05 13:03:58
    SQL语句的复杂查询语句,包括标量子查询及关联子查询

    子查询:将用来定义视图的SELECT语句直接用于FROM子句中。

    SELECT <列名1>, <列名2>,FROM (<SELECT语句>) AS <视图名称>;
    /*
    内层的SELECT语句会先执行;
    Oracle的FROM子句中不能使用AS,直接删除即可;
    */
    

    上述代码等价于:

    CREATE VIEW 视图名称(<视图列名1>, <视图列名2>,)
    AS
    <SELECT语句>
    

    标量子查询

    返回单一值的子查询是标量子查询,即必须且只能返回1行1列的结果。能够使用常数或列名的地方,无论是SELECT子句、GROUP BY子句、HAVING子句,还是ORDER BY子句,几乎所有的地方都能使用(SELECT标量子查询)。

    例:在SELECT子句中使用标量子查询

    SELECT product_id, 
           product_name, 
           sale_price,
           (SELECT AVG(sale_price)
            FROM Product) AS avg_price
           -- 标量子查询
    FROM Product; 
    

    标量子查询不能返回多行结果:

    SELECT product_id, 
           product_name, 
           sale_price,
           (SELECT AVG(sale_price)
           	FROM Product
           	GROUP BY product_type) AS avg_price
           -- 该子查询返回了多行结果,产生错误
    FROM Product; 
    

    关联子查询

    在细分的组内进行比较时,需要使用关联子查询。

    例:关联子查询

    SELECT <列名1>, <列名2>,FROM <表名> AS <别名1>
    WHERE <列名2> > (SELECT <表达式>
                     FROM <表名> AS <别名2>
                     WHERE <别名1>.<列名1> = <别名2>.<列名1>);
                     -- 关联子查询可对集合根据<列名1>进行切分
    
    SELECT product_id, 
           product_name, 
           sale_price,
           (SELECT AVG(sale_price)
           	FROM Product AS P2
           	WHERE P1.product_type = P2.product_type
           	GROUP BY P1.product_type) AS avg_sale_price
           -- 该关联子查询可当作加了条件的标量子查询
    FROM Product AS P1;
    

    关联子查询中别名的作用范围为子查询内部。

    参考资料

    《SQL基础教程》(第2版)[日] MICK著;孙淼,罗勇译

    展开全文
  • sql关联查询,子查询的优化

    千次阅读 2018-04-03 17:03:31
    项目中接收离职同事的统计部分代码的优化,客户反映随着时间查询越来越慢,最长的到了5分钟左右,客户当然反馈很强烈,系统太不稳定了,反映越来越慢。。。,不多说直接上sql代码SELECT ajjbxx.ssbm, pqNum , round...

    项目中接收离职同事的统计部分代码的优化,客户反映随着时间查询越来越慢,最长的到了5分钟左右,客户当然反馈很强烈,系统太不稳定了,反映越来越慢。。。,不多说直接上sql代码

    SELECT ajjbxx.ssbm, pqNum
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE 20 - ISNULL(gzNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) + round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE 20 - (ISNULL(fgNum, 0) + ISNULL(fgNum2, 0)) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) + round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(aqNum, 0) * 40.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) + round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(clfkNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS df
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE 20 - ISNULL(gzNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS gzldf
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(gzNum, 0) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS gzl
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE 20 - (ISNULL(fgNum, 0) + ISNULL(fgNum2, 0)) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS fgldf
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE (ISNULL(fgNum, 0) + ISNULL(fgNum2, 0)) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS fgl
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(aqNum, 0) * 40.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS aqczldf
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(aqNum, 0) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS aqczl
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(clfkNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS czldf
    	, round(CASE 
    		WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
    		ELSE ISNULL(clfkNum, 0) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
    	END, 2) AS czl
    	, ISNULL(pqNum, 0) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) AS yczNum
    	, zzjg.dwmc, clfkNum, htNum, aqNum, cqNum
    	, ISNULL(fgNum, 0) + ISNULL(fgNum2, 0) AS fgNum
    	, clzNum, zpqNum, zhtNum, zfNum, gzNum
    	, cqwclNum
    FROM (
    	SELECT *
    	FROM t_ajjbxxb
    ) ajjbxx
    	LEFT JOIN (
    		SELECT *
    		FROM t_zzjgb
    	) zzjg
    	ON zzjg.zzbh = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS pqNum, a.ssbm
    		FROM (
    			SELECT ajbh, ssbm, lcbh, sbsj
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = '344D8F3F-8FA8-403C-AEBD-C663008BFBAA'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
    		) a
    		GROUP BY ssbm
    	) pq
    	ON pq.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS zfNum, a.ssbm
    		FROM (
    			SELECT ajbh, ssbm, lcbh, sbsj
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = 'B9AB809C-87D5-41F9-9520-48980DFA289F'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
    		) a
    		GROUP BY ssbm
    	) zf
    	ON zf.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS gzNum, a.ssbm
    		FROM (
    			SELECT ajbh, ssbm, lcbh, sbsj
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = '92D1D71F-274D-4423-8372-0B094DB50A16'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
    		) a
    		GROUP BY ssbm
    	) gz
    	ON gz.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS jyNum, a.ssbm
    		FROM (
    			SELECT ajbh, ssbm, lcbh, sbsj
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = 'A85A6190-B0DB-4C09-AB94-E386C29A1D74'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
    		) a
    		GROUP BY ssbm
    	) jy
    	ON jy.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS htNum, ssbm
    		FROM V_ajlcb WITH (NOLOCK)
    		WHERE lcbh = '5BDE3C96-C04E-4779-9F78-2287318E2F95'
    			AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    		GROUP BY ssbm
    	) ht
    	ON ht.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS zpqNum
    		FROM (
    			SELECT COUNT(1) AS zzpqNum, ajbh
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = '344D8F3F-8FA8-403C-AEBD-C663008BFBAA'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ajbh
    		) pq1
    	) zpq
    	ON 1 = 1
    	LEFT JOIN (
    		SELECT COUNT(1) AS zhtNum
    		FROM (
    			SELECT COUNT(1) AS zzhtNum, ajbh
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = '5BDE3C96-C04E-4779-9F78-2287318E2F95'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ajbh
    		) ht1
    	) zht
    	ON 1 = 1
    	LEFT JOIN (
    		SELECT COUNT(1) AS clfkNum, ssbm
    		FROM (
    			SELECT ssbm, ajbh
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
    				AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    				AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    				AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    				AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    				AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY ssbm, ajbh
    		) a
    		GROUP BY ssbm
    	) clfk
    	ON clfk.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS clzNum, ssbm
    		FROM t_ajjbxxb WITH (NOLOCK)
    		WHERE ajzt <> 'CE5B6B41-7B67-4476-884A-8590418F4DB7'
    			AND ajzt <> '19486445-BD8E-49D6-B635-DDD4A8783F5F'
    			AND ajzt <> '71668A4F-DC41-48FB-B911-2FF8AB9C5C4E'
    			AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    			AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    			AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    			AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    			AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    		GROUP BY ssbm
    	) clz
    	ON clz.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS aqNum, aq1.ssbm
    		FROM (
    			SELECT a.ajbh, a.ssbm
    			FROM (
    				SELECT *
    				FROM V_ajlcb WITH (NOLOCK)
    				WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
    					AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    					AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    					AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    					AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    					AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    			) a
    				LEFT JOIN (
    					SELECT *
    					FROM V_ajlcb WITH (NOLOCK)
    					WHERE lcbh = '45CDC555-B94D-4844-9B43-F2D20D87FE24'
    						AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    						AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    						AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    						AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    						AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    				) b
    				ON a.ajbh = b.ajbh
    			WHERE a.ajbh = b.ajbh
    				AND a.blqx > b.fqsj
    				AND a.sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY a.ssbm, a.ajbh
    			EXCEPT
    			SELECT a.ajbh, a.ssbm
    			FROM (
    				SELECT *
    				FROM V_ajlcb WITH (NOLOCK)
    				WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
    					AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    					AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    					AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    					AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    					AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    			) a
    				LEFT JOIN (
    					SELECT *
    					FROM V_ajlcb WITH (NOLOCK)
    					WHERE lcbh = '45CDC555-B94D-4844-9B43-F2D20D87FE24'
    						AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    						AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    						AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    						AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    						AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    				) b
    				ON a.ajbh = b.ajbh
    			WHERE a.ajbh = b.ajbh
    				AND a.blqx < b.fqsj
    				AND a.sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY a.ssbm, a.ajbh
    		) aq1
    		GROUP BY aq1.ssbm
    	) aq
    	ON aq.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS cqwclNum, ssbm
    		FROM t_ajjbxxb WITH (NOLOCK)
    		WHERE getdate() > blqx
    			AND ajzt <> 'CE5B6B41-7B67-4476-884A-8590418F4DB7'
    			AND ajzt <> '19486445-BD8E-49D6-B635-DDD4A8783F5F'
    			AND ajzt <> '71668A4F-DC41-48FB-B911-2FF8AB9C5C4E'
    			AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
    			AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
    			AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
    			AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
    			AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    		GROUP BY ssbm
    	) cqwcl
    	ON cqwcl.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS cqNum, ssbm
    		FROM (
    			SELECT a.ajbh, a.ssbm
    			FROM (
    				SELECT *
    				FROM V_ajlcb WITH (NOLOCK)
    				WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
    					AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    			) a
    				LEFT JOIN (
    					SELECT *
    					FROM V_ajlcb WITH (NOLOCK)
    					WHERE lcbh = '45CDC555-B94D-4844-9B43-F2D20D87FE24'
    						AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
    				) b
    				ON a.ajbh = b.ajbh
    			WHERE a.ajbh = b.ajbh
    				AND a.blqx < b.fqsj
    				AND a.sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			GROUP BY a.ssbm, a.ajbh
    		) aq1
    		GROUP BY aq1.ssbm
    	) cq
    	ON cq.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS fgNum, ssbm
    		FROM V_ajlcb WITH (NOLOCK)
    		WHERE lcbh = '2442A76F-BB18-431F-AA2C-F1EFEF5A38FE'
    			AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    			AND sbsj < '20180126'
    		GROUP BY ssbm
    	) fg
    	ON fg.ssbm = ajjbxx.ssbm
    	LEFT JOIN (
    		SELECT COUNT(1) AS fgNum2, ajbmbh
    		FROM (
    			SELECT MAX(ajbh) AS ajbh, ajbmbh
    			FROM V_ajlcb WITH (NOLOCK)
    			WHERE lcbh = '2442A76F-BB18-431F-AA2C-F1EFEF5A38FE'
    				AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
    				AND sbsj >= '20180126'
    			GROUP BY ajbmbh, ajbh
    		) fgcs
    		GROUP BY ajbmbh
    	) fg2
    	ON fg2.ajbmbh = ajjbxx.ssbm
    WHERE ajjbxx.ssbm <> ''
    	AND zzjg.dwmc <> ''
    GROUP BY zpqNum, zhtNum, ajjbxx.ssbm, pqNum, zzjg.dwmc, clfkNum, htNum, aqNum, fgNum, fgNum2, clzNum, cqNum, zfNum, gzNum, jyNum, cqwclNum
    ORDER BY yczNum DESC

    这段sql主要是通过关联查询嵌套子查询,同时有分组和排序要求,关联的表里面有视图(视图有记录表t_ajlcb和基本表t_ajjbxxb组成,以及其他字典表,基础数据表),所有表的数量大概10个表左右,记录表(视图)数据大概有15万条,基本表有1万多条。

    查询时间大概是50秒到5分钟之间,开始分析原因:

    1、查看sql执行计划,发现视图里面的t_ajlcb和t_ajjbxxb的表都是全表搜索,每个查询t_ajlcb占到了4%,t_ajjbxxb占2%这样下来12个子查询或者关联查询效率就到了(4%+2%)*10=72%,其他的就是排序排序占的效率高一点。很明显影响效率的就是因为两个数据比较多的表未做索引,接下来需要给他们选择合适的索引。

    2、先看看这两个表的查询的字段是什么,然后再确定查询条件,本例中两个表最终统计的ajbh,ssbm这两个字段,查询条件已时间段为主,因此我们选择给这两个表分别创建聚集索引,创建什么样的索引根据个人的理解和经验,当然有一些原则,例如下图:

    本例中我们也是一个范围查询,因此我们选择聚集索引,创建索引过程以SQLSERVER2008R2 来说明,选择表,下拉找到索引,新建索引

      

     添加要新增的索引列

    3、接下来再看看执行计划结果,执行时间不到5秒

    起作用了最大的开销已经没了,基本表的t_ajjbxxb开销为2%是因为还有其他排序、分组,以及还有*查询的操作,需要进行代码优化。通过索引建立让原来最慢的5分钟最后降到1-2秒。

    这样的结果基本上达到要求,如果还想优化掉一些大的开销,对代码进行分析优化,比如关联的时候以数据量大的作为查询结果在进行关联数据量小的表;少用select * 查询。

    总之,分析sql效率的时候先看看执行计划,查找开销最大的,一般情况下索引就可以解决主要的问题,剩余就是根据需要优化sql。

    展开全文
  • SQL关联子查询的详解

    2020-12-14 13:40:08
    我们先来看一下SQL关联子查询的基本逻辑的定义 对于外部查询返回的每一行数据,内部查询都要执行一次。在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并...
  • 主要介绍了利用带关联子查询Update语句更新数据的方法,需要的朋友可以参考下
  • SQL优化前,优化器还是很智能的进行为此下推,讲关联子查询改成了表连接。虽然性能不错,我们还是要根据SQL的执行原理进行下一步优化。 2.2 关联子查询改为表连接 select t1.* from test1 t1  ,test2 ...
  • 子查询就是在查询的where子句中的判断依据是另一个查询的结果,表链接就是将多个表合成为一个表,但是不是向union一样做结果集的合并操作,但是表链接可以将不同的表合并,并且共享字段,感兴趣的你可以了解下本
  • 一.视图 1. 首先,什么是视图,通俗的讲 在实际的数据库中,每一张表会有很多个字段,但是不同... 再说的专业一点,视图是对SQL语句的封装,这个说法在下面进行解释 2. 为什么说 视图是对SQL语句的封装呢?这是因...
  • 主要介绍了mysql实现多表关联统计(子查询统计),结合具体案例形式分析了mysql多表关联统计的原理、实现方法及相关操作注意事项,需要的朋友可以参考下
  • 51.Oracle数据库SQL开发之 子查询——编写关联子查询.pdf
  • ORACLE SQL 表的内关联子查询

    千次阅读 2019-04-08 09:54:37
    表的内关联子查询:子查询可以用主查询里面定义的表进行关联实现查询,这样的查询我们把它叫为表的内关联子查询 下图是一个表的内关联子查询的例子: 如图所示,我们在Where条件中写入子查询,然后再调用主查询定义...
  • 一文详解SQL关联子查询

    千次阅读 2021-03-30 13:42:20
    简介:本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。 本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。 在背景介绍中我们将讲讲常见的关联子查询的...
  • sql 多表关联查询,子查询

    千次阅读 2019-06-27 14:35:44
    表:用户表:id,名称,***;(保存所有用户信息); 步数表:id,用户id,步数,时间,***;(保存所有用户步数信息,假如当天有步数一天一条,如果没有当天无记录); 好友表:id,好友1 id,好友2 id,状态,...
  • MySQL 表子查询子查询是指子查询返回的结果集是 N 行 N 列的一个表数据。 MySQL 表子查询实例 下面是用于例子的两张原始数据表: article 表: blog 表: SQL 如下: SELECT * FROM article WHERE (title,...
  • SQL一对多,关联查询

    千次阅读 2018-12-03 13:08:10
    SQL一对多,关联查询 两张表:主表A(合作伙伴),从表B(维修方式) 表关系:A:B-----1:N 表A: 只有一条 partner_id为A20181203120246889b15e990d72464a的合作伙伴 表B: 主从表关联字段:partner_id 目前从...
  • 运营组的同事最近提出一个需求,希望可以统计出用系统用户及订单情况,于是乎我们很想当然的写出了一个统计SQL,用户表user和行程表直接join,并且针对行程做了group,但SQL执行速度出奇的慢。 explain select users...
  • Oracle 普通子查询和关联子查询

    千次阅读 2017-09-02 21:22:20
    子查询(普通子查询和关联子查询) 子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询的综合体; 所谓子查询:指的就是在一个查询之中嵌套了其他的若干查询,嵌套子查询之后的查询SQL语句如下: SELECT [DISTINCT...
  • 子查询结果返回一个值(单列单行) 选择比较运算符:> < >= <= = <> select ename from emp_xxx where salary > ( select salary from emp_xxx where ename = '张无忌' ) ; 子查询结果返回...
  • 关联子查询的执行顺序是什么

    千次阅读 2021-01-19 17:42:57
    sql的编写顺序select .. from .. where .. group by ..having .....关联子查询 :在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在关联子查询中是信息流是双向的。外部查询的...
  • 其实最后得出的结论,就是关联查询速度快于子查询 写这篇的目的主要是把尝试过的方法给记录下,同时复习下有一段时间没有用过的oracle存储过程、自定义函数、包的写法 一、问题说明 为了说清楚问题的核心,我把...
  • SQL语句多表关联查询语法

    千次阅读 2021-03-03 10:11:37
    **sql语句多表关联查询语法** 一、外连接 1.左连接 left join 或 left outer join SQL语句:select * from student left join score on student.Num=score.Stu_id; 2.右连接 right join 或 right outer join SQL...
  • 子查询:(如果最终结果在一张表中,优先选择子查询,再考虑连接查询)嵌套查询,将一个查询结果作为另一个查询条件或组成部分的查询 联合查询:全连接[联合查询]、并集去交集等,将两个结果集联合到一起的查询 ...
  • SQL子查询优化

    千次阅读 2018-07-26 23:26:36
    在此研究下SQL子查询的相关知识 ~ 以下内容主要参考《数据库查询优化器的艺术》一书 一、子查询介绍 概念:当一个查询是另一个查询的子部分时,称之为子查询(查询语句中嵌套有查询语句)。 子查询出现的位置...
  • SQL优化--使用关联查询代替子查询

    千次阅读 2016-09-13 09:42:00
    为什么80%的码农都做不了架构师?>>>   ... 使用子查询关联查询,一般情况下如果能用关联查询就不用子查询, 转载于:https://my.oschina.net/ldm95/blog/746067
  • sql语句优化-左连接和子查询

    千次阅读 2019-04-29 11:04:35
    现有a,b,c表需要连表查询,a表是主表接近百万数据,b,c表只有几百条, select a.x, b.x, c.x from a left join b on a.key = b.key left join c on a.key = c.key where ... 修改之后 select a.x, (select b.x...
  • SQL系列五——子查询(SELECT)和组合查询(UNION)

    万次阅读 多人点赞 2018-07-19 10:01:01
    1、子查询: 为什么要子查询? 现有一数据表如下: 根据之前的知识我们可以查出每门科目的最高分,但是要想查出取得最高分的学生信息就做不到了。这时就需要用到子查询来取得完整的信息。 什么是子查询...
  • 首先我有三张相互关联的shu表
  • 关联查询和子查询性能比较

    千次阅读 2019-06-24 13:08:42
    在项目开发的过程中,对于一些实体类,它的大部分字段都可以在一张表中查询到,但另一些属性来来自于其它表,需要用到关联查询或者子查询。下面来探讨一下这两种查询的区别。 例如评论表,它的点赞数来自另一张表 ...
  • sql:关联子查询

    千次阅读 2018-08-13 22:10:37
    关联子查询会在细分的组内进行比较的时候使用。 假设要按照商品的种类与平均销售单价进行比较,正常的逻辑是:先取出平均值再与销售单价进行对比 select product_id, product_name, sale_price from Product ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 135,455
精华内容 54,182
关键字:

关联子查询sql