使用with as优化sql解决filter

最新推荐文章于 2022-10-25 13:11:58 发布
最新推荐文章于 2022-10-25 13:11:58 发布
阅读量3.8k 收藏 2
点赞数
CC 4.0 BY-SA版权
分类专栏： SQL TUNING 文章标签： filter sql 优化 action crm 产品
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/colin_liu2009/article/details/6955195
本文详细阐述了一个地市报表系统中数据出得慢的问题，通过优化SQL语句，使用WITH as方法，最终将查询时间缩短至12秒内。包括收集统计信息、查看执行计划、识别性能瓶颈并采取相应措施的过程。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 最近一个地市的报表系统老是说数据出得慢，基本上要到中午才能出数据，但是查看过程是比较简单的。后来发现一条sql有问题，居然要跑5个多小时。

查看跑2个小时以上的sql
SELECT SQ.SQL_TEXT,
       S.SQL_ID,
       S.SID,
       S.SERIAL#,
       S.LAST_CALL_ET,
       TO_CHAR(S.LOGON_TIME, 'yyyymmdd hh24:mi:ss') LOGON_TIME
FROM   V$SESSION S,
              V$SQLTEXT SQ
WHERE  S.SQL_ID = SQ.SQL_ID AND
                 LAST_CALL_ET > 10000 AND
                 TO_CHAR(LOGON_TIME,'yyyymmdd') =TO_CHAR(SYSDATE,'yyyymmdd');


NTERVAL '7' DAY < D.COMPLETE_DT) 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
移机','资费变更') AND                          A.COMPLETE_DT - I 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
         A.BO_ACTION_NAME IN                          ('新装', ' 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
                     A.PROD_ID != D.PROD_ID AND                  4kyzp58tz0xya 232 811 15400 20111110 06:27:38
ARTY_ID AND                          A.BO_ID != D.BO_ID AND      4kyzp58tz0xya 232 811 15400 20111110 06:27:38
TAFF_ID LIKE '36%' AND                          A.PARTY_ID = D.P 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
               FROM   DW_BO_ORDER D                   WHERE  D.S 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
欢乐送之E6套餐') AND                 NOT  EXISTS (SELECT  *      4kyzp58tz0xya 232 811 15400 20111110 06:27:38
   '新春欢乐送之E8套餐',                                  '新春 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
) 全省_紧密融合型E9套餐产品规格',                                4kyzp58tz0xya 232 811 15400 20111110 06:27:38
紧密融合型E9套餐产品规格',                                  '(新 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
 '普通E9','普通新版E8',                                  '全省_ 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
2S','E6移动版', 'E9版1M(老版)',                                  4kyzp58tz0xya 232 811 15400 20111110 06:27:38
', 'ADSL','LAN', '手机',                                  'E8 -  4kyzp58tz0xya 232 811 15400 20111110 06:27:38
移机','资费变更') AND                  B.PROD_SPEC IN ('普通电话 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
D LIKE '36%' AND                  A.BO_ACTION_NAME IN ('新装',' 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
   A.CHANNEL_ID = C.CHANNEL_ID AND                  A.SO_STAFF_I 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
     A           WHERE  A.PROD_ID = B.PROD_ID AND                4kyzp58tz0xya 232 811 15400 20111110 06:27:38
                 DW_CRM_DAY_USER B,                  DW_BO_ORDER 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
                 A.PROD_ID            FROM   DW_CHANNEL      C,  4kyzp58tz0xya 232 811 15400 20111110 06:27:38
              A.SO_STAFF_ID,                  A.ATOM_ACTION_ID,  4kyzp58tz0xya 232 811 15400 20111110 06:27:38
              B.START_DT,                  A.BO_ACTION_NAME,     4kyzp58tz0xya 232 811 15400 20111110 06:27:38
              B.ACCESS_NUMBER,                  B.PROD_SPEC,     4kyzp58tz0xya 232 811 15400 20111110 06:27:38
 CHANNEL_NAME,                  B.NAME           PARTY_NAME,     4kyzp58tz0xya 232 811 15400 20111110 06:27:38
                  B.AREA_NAME,                  C.NAME           4kyzp58tz0xya 232 811 15400 20111110 06:27:38
SELECT                   B.AREA_ID,                  A.PARTY_ID, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
         CREATE TABLE TMP_DTBB_FMT_10000_1 NOLOGGING AS          4kyzp58tz0xya 232 811 15400 20111110 06:27:38

找到的sql为如下：

SELECT  
               B.AREA_ID, 
               A.PARTY_ID, 
               B.AREA_NAME, 
               C.NAME           CHANNEL_NAME, 
               B.NAME           PARTY_NAME, 
               B.ACCESS_NUMBER, 
               B.PROD_SPEC, 
               B.START_DT, 
               A.BO_ACTION_NAME, 
               A.SO_STAFF_ID, 
               A.ATOM_ACTION_ID, 
               A.PROD_ID  
        FROM   DW_CHANNEL      C, 
               DW_CRM_DAY_USER B, 
               DW_BO_ORDER     A 
        WHERE  A.PROD_ID = B.PROD_ID AND 
               A.CHANNEL_ID = C.CHANNEL_ID AND 
               A.SO_STAFF_ID LIKE '36%' AND 
               A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND 
               B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机', 
                               'E8 - 2S','E6移动版', 'E9版1M(老版)', 
                               '普通E9','普通新版E8', 
                               '全省_紧密融合型E9套餐产品规格', 
                               '(新) 全省_紧密融合型E9套餐产品规格', 
                               '新春欢乐送之E8套餐', 
                               '新春欢乐送之E6套餐') AND 
              NOT  EXISTS (SELECT  *  
                FROM   DW_BO_ORDER D 
                WHERE  D.STAFF_ID LIKE '36%' AND 
                       A.PARTY_ID = D.PARTY_ID AND 
                       A.BO_ID != D.BO_ID AND 
                       A.PROD_ID != D.PROD_ID AND 
                       A.BO_ACTION_NAME IN 
                       ('新装', '移机','资费变更') AND 
                       A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT)
下面根据sql语句去查找相关的session，发现该语句已经跑了将近4个小时，从早上6点多开始跑。

SQL> SELECT S.SQL_ID,to_char(S.LOGON_TIME,'yyyymmdd hh24:mi:ss') LOGON_TIME,S.LAST_CALL_ET,S.STATE FROM V$SESSION S WHERE S.SID=232;
 
SQL_ID        LOGON_TIME        LAST_CALL_ET STATE
------------- ----------------- ------------ -------------------
4kyzp58tz0xya 20111110 06:27:38        13647 WAITED SHORT TIME
 
SQL> select 13647/3600 from dual;
 
13647/3600
----------
3.79083333

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') sysdat from dual;
 
SYSDAT
-----------------
20111110 10:18:39

OK，现在知道导致数据出的慢的原因,现在就开始来优化这条sql，首先收集一下统计图，然后看执行计划

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'LBAS',
                                tabname          => 'DW_BO_ORDER',
                                estimate_percent => 20,
                                method_opt       => 'for all columns size auto',
                                no_invalidate    => FALSE,
                                degree           => 4,
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;
/

  • Plan hash value: 2142862569  
  •    
  • ----------------------------------------------------------------------------------------------------------  
  • | Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  • ----------------------------------------------------------------------------------------------------------  
  • |   0 | SELECT STATEMENT       |                 |   905 |   121K|  4152K  (2)| 13:50:32 |       |       |  
  • |*  1 |  FILTER                |                 |       |       |            |          |       |       |  
  • |*  2 |   HASH JOIN            |                 |   905 |   121K| 12616   (2)| 00:02:32 |       |       |  
  • |*  3 |    HASH JOIN           |                 |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |  
  • |   4 |     PARTITION RANGE ALL|                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  • |*  5 |      TABLE ACCESS FULL | DW_BO_ORDER     |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  • |*  6 |     TABLE ACCESS FULL  | DW_CRM_DAY_USER |   309K|    15M|  3277   (2)| 00:00:40 |       |       |  
  • |   7 |    TABLE ACCESS FULL   | DW_CHANNEL      | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |  
  • |*  8 |   FILTER               |                 |       |       |            |          |       |       |  
  • |   9 |    PARTITION RANGE ALL |                 |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |  
  • |* 10 |     TABLE ACCESS FULL  | DW_BO_ORDER     |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |  
  • ----------------------------------------------------------------------------------------------------------  
  •    
  • Predicate Information (identified by operation id):  
  • ---------------------------------------------------  
  •    
  •    1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DW_BO_ORDER" "D" WHERE (:B1='新装' OR :B2='移机' OR   
  •               :B3='资费变更') AND "D"."PARTY_ID"=:B4 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND   
  •               "D"."COMPLETE_DT">:B5-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B6 AND   
  •               "D"."BO_ID"<>:B7))  
  •    2 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID")  
  •    3 - access("A"."PROD_ID"="B"."PROD_ID")  
  •    5 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR   
  •               "A"."BO_ACTION_NAME"='移机' OR "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE   
  •               '36%')  
  •    6 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR   
  •               "B"."PROD_SPEC"='E6移动版' OR "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR   
  •               "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR "B"."PROD_SPEC"='普通电话' OR   
  •               "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机' OR   
  •               "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐')  
  •    8 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更')  
  •   10 - filter("D"."PARTY_ID"=:B1 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND   
  •               "D"."COMPLETE_DT">:B2-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B3 AND   
  •               "D"."BO_ID"<>:B4)          

     

    执行计划里面有个filter在里面，这个是导致这条sql跑的慢的原因。主要语句就是在not exists 里面居然存在!=， 哎，这sql语句写得真坑爹，由于有!=的存在，CBO不能选择 HASH_AJ join的方式，准备修改sql语句，但是不管怎么改，业务逻辑总不对，最后的结果也不对。得想办法吧filter搞掉，加多种hint，最后的结果还是一样的，在朋友的帮下，最后使用了了with as.  good最后居然实现了在12秒以内出数据。下面看一下

    WITH D AS
     (SELECT /*+ materialize */
       A.PARTY_ID,
       A.BO_ID,
       A.PROD_ID,
       A.COMPLETE_DT
      FROM   DW_BO_ORDER A
      WHERE  STAFF_ID LIKE '36%' AND
             A.BO_ACTION_NAME IN ('新装',
                                  '移机',
                                  '资费变更'))
    SELECT B.AREA_ID,
           A.PARTY_ID,
           B.AREA_NAME,
           C.NAME           CHANNEL_NAME,
           B.NAME           PARTY_NAME,
           B.ACCESS_NUMBER,
           B.PROD_SPEC,
           B.START_DT,
           A.BO_ACTION_NAME,
           A.SO_STAFF_ID,
           A.ATOM_ACTION_ID,
           A.PROD_ID
    FROM   DW_CHANNEL      C,
           DW_CRM_DAY_USER B,
           DW_BO_ORDER     A
    WHERE  A.PROD_ID = B.PROD_ID AND
           A.CHANNEL_ID = C.CHANNEL_ID AND
           A.SO_STAFF_ID LIKE '36%' AND
           A.BO_ACTION_NAME IN ('新装',
                                '移机',
                                '资费变更') AND
           B.PROD_SPEC IN ('普通电话',
                           'ADSL',
                           'LAN',
                           '手机',
                           'E8 - 2S',
                           'E6移动版',
                           'E9版1M(老版)',
                           '普通E9',
                           '普通新版E8',
                           '全省_紧密融合型E9套餐产品规格',
                           '(新) 全省_紧密融合型E9套餐产品规格',
                           '新春欢乐送之E8套餐',
                           '新春欢乐送之E6套餐') AND
           NOT EXISTS (SELECT *
            FROM   D
            WHERE  A.PARTY_ID = D.PARTY_ID AND
                   A.BO_ID != D.BO_ID AND
                   A.PROD_ID != D.PROD_ID AND
                   A.COMPLETE_DT - INTERVAL '7'
             DAY < D.COMPLETE_DT);

     

  • 已用时间:  00: 00: 12.37  
  •   
  • 执行计划  
  • --------------------------------------------------------------------------------------------------------------------------  
  • Plan hash value: 2591883460  
  •    
  • --------------------------------------------------------------------------------------------------------------------------  
  • | Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  • --------------------------------------------------------------------------------------------------------------------------  
  • |   0 | SELECT STATEMENT           |                             |   905 |   121K| 62428   (2)| 00:12:30 |       |       |  
  • |   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |  
  • |   2 |   LOAD AS SELECT           | DW_BO_ORDER                 |       |       |            |          |       |       |  
  • |   3 |    PARTITION RANGE ALL     |                             |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |  
  • |*  4 |     TABLE ACCESS FULL      | DW_BO_ORDER                 |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |  
  • |*  5 |   FILTER                   |                             |       |       |            |          |       |       |  
  • |*  6 |    HASH JOIN               |                             |   905 |   121K| 12616   (2)| 00:02:32 |       |       |  
  • |*  7 |     HASH JOIN              |                             |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |  
  • |   8 |      PARTITION RANGE ALL   |                             |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  • |*  9 |       TABLE ACCESS FULL    | DW_BO_ORDER                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  • |* 10 |      TABLE ACCESS FULL     | DW_CRM_DAY_USER             |   309K|    15M|  3277   (2)| 00:00:40 |       |       |  
  • |  11 |     TABLE ACCESS FULL      | DW_CHANNEL                  | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |  
  • |* 12 |    FILTER                  |                             |       |       |            |          |       |       |  
  • |* 13 |     VIEW                   |                             |   114K|  6791K|    90   (3)| 00:00:02 |       |       |  
  • |  14 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D662E_D625B872 |   114K|  3228K|    90   (3)| 00:00:02 |       |       |  
  • --------------------------------------------------------------------------------------------------------------------------  
  •    
  • Predicate Information (identified by operation id):  
  • ---------------------------------------------------  
  •    
  •    4 - filter(TO_CHAR("STAFF_ID") LIKE '36%')  
  •    5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "STAFF_ID","C1"   
  •               "PARTY_ID","C2" "BO_ID","C3" "PROD_ID","C4" "COMPLETE_DT" FROM "SYS"."SYS_TEMP_0FD9D662E_D625B872" "T1") "D"   
  •               WHERE (:B1='新装' OR :B2='移机' OR :B3='资费变更') AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B4 AND   
  •               "D"."BO_ID"<>:B5 AND "D"."PROD_ID"<>:B6 AND "D"."COMPLETE_DT">:B7-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0)))  
  •    6 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID")  
  •    7 - access("A"."PROD_ID"="B"."PROD_ID")  
  •    9 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR "A"."BO_ACTION_NAME"='移机' OR   
  •               "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE '36%')  
  •   10 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR "B"."PROD_SPEC"='E6移动版' OR   
  •               "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR   
  •               "B"."PROD_SPEC"='普通电话' OR "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机'   
  •               OR "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐')  
  •   12 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更')  
  •   13 - filter(TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B1 AND "D"."BO_ID"<>:B2 AND   
  •               "D"."PROD_ID"<>:B3 AND "D"."COMPLETE_DT">:B4-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0))     
  •   
  •   
  • 统计信息  
  • ----------------------------------------------------------  
  •           2  recursive calls  
  •          29  db block gets  
  •      110506  consistent gets  
  •          22  physical reads  
  •         656  redo size  
  •     2438096  bytes sent via SQL*Net to client  
  •         449  bytes received via SQL*Net from client  
  •          11  SQL*Net roundtrips to/from client  
  •           0  sorts (memory)  
  •           0  sorts (disk)  
  •       49245  rows processed  

     

    问题解决啦，这次过程中也学到了很多东西。谢谢那位哥们。

SQL优化之FILTER
zhaoyangjian724的专栏
06-26 2420
select count(1) over() as tcnt, rownum as rn, O.CustomerID, O.CustomerName, O.CustomerType, O.CertType, O.CertID, O.MFCustomerID, O.MCCustomer...
Text2SQL推理类大模型本地部署的解决方案
热门推荐
herosunly的博客
03-27 6万+
本文主要介绍了Text2SQL推理类大模型本地部署的解决方案，希望对使用大语言模型的同学们有所帮助。 文章目录 1. 前言 2. 配置环境 2.1 安装虚拟环境 2.2 安装依赖库 2.3 下载模型 3. 实战代码
使用WITH AS 优化SQL
南山南的Python和大数据学习笔记
09-06 1131
马上就要单身节了，正在想今年我去祸害谁家的姑娘，突然QQ好友发来信息，说能否帮忙优化一个SQL，SQL调优做得实在太多了，都已经麻木了，反正优化一个SQL也就几秒钟到几分钟的事情。 哥们说下面的SQL要跑5个多小时 [html] view plaincopyprint? SELECT                   B.AREA_ID,
sql数据过滤
旭灿的专栏
11-23 1377
 一. 查找重复记录 1. 查找全部重复记录 Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1) 2. 过滤重复记录(只显示一条) Select * From HZT Where ID In (Select Max(ID) Fro
SQL过滤函数
heroooooo的专栏
07-09 2575
           众所周知，SQL注入的问题现在是一个很大的问题，网上现在也出现了很多的通用防注入程序，不得不承认的是，这些程序有一些确实是写得不错，通过3次循环遍历，将POST，GET，COOKIES这三个方面的可能注入的都考虑了.这些程序只要将他们包含在数据库的连接文件中就可以得到很好的使用，因为现在大部分的程序都和数据库有联系，不过这些程序实在是防得太严格了，导致一些SQL语句根本就没
ORACLE使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率低下
专注于ORACLE
07-16 1万+
ORACLE使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率低下
SQL优化 第九章 With改写优化SQL
songjian1104的专栏
08-03 540
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 9.1 提取公共项 9.1.1. 数据脚本 drop table test1; drop table test2; create table test1 as select * from...
利用WITH AS 优化FILTER
zhaoyangjian724的专栏
12-31 902
SQL> explain plan for select * from fxqd_list_20131115_new where (acct_no, oper_no, seqno, trans_amt) not in (select acct_no, oper_no, seqno, trans_amt from fxqd_list
SQL优化 第十章 Filter连接那点事
songjian1104的专栏
08-03 1710
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 虽然Filter连接大部分时间不太讨喜，但是，鉴于存在即是合理的这种高大上的哲学思想，我们首先给Filter在局部范围平个反。 10.1 数据脚本 drop table test1; drop ...
filter优化
04-27
oracle sql filter 优化
array-sql-filter:使用SQL的where子句来过滤对象数组
05-02
数组SQL过滤器 向JS对象数组添加类似SQL的查询功能。 包含该库将向对象数组添加where（），select（）和orderBy（）功能。 where（）-接受SQL查询字符串作为参数。 模拟SQL WHERE子句。 处理所有表达式，包括BETWEEN和IN子句。 标量和聚合函数尚不支持。 select（）-模拟SQL SELECT。 orderBy（）-模拟SQL ORDER BY。 joinOn（）-模拟SQL内部联接。 （实验性的）将连接字段（或连接表达式）作为第一个参数，将要连接的数组作为下一个参数。 为了进行测试，在项目中包含 ＃＃＃例子 var employee = [ { empid : 100 , ename : "Chuck" } , { empid : 101 , ename : "Rick" } , { emp
oracle filter unnest,优化Oracle SQL查询
weixin_30015509的博客
04-06 237
您好我有一个SQL查询,我正在尝试优化.此查询在0.3秒内完成,但我需要为大量不同的storeId运行相同的查询 – 无论如何都要优化此查询以使其更快,或者更改它以便它立即获取所有storeIds.我总是可以在C#中创建一个新命令,该命令与命令连接,使其成为大量不同查询的联合.select /*+ PUSH_SUBQ */ *from mytable rwhere r.s in (1,7)and ...
mssql sql语句过滤百分号的方法分享
weixin_30545285的博客
06-30 150
转自:http://www.maomao365.com/?p=6743 摘要: 下文讲述sql脚本中过滤百分号的方法： 实验环境:sql server 2008 R2 百分号:在sql脚本编写中“百分号”被界定为一种通配符，那么如果我们需检索数据中是否存在百分号时，需采用何种方法呢？下文通过实例的方式分析百分号的匹配方式。 1 ---在sql...
SQL 聚合函数中的数据过滤
Tony.Dong的专栏
03-07 5197
本文介绍了如何在 SQL 聚合函数中进行数据过滤，基于满足特定条件的部分数据进行汇总。方法包括在聚合函数中使用 CASE 表达式对数据进行转换，以及 PostgreSQL 对聚合函数的 FILTER 扩展选项两种方法。本文描述的方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等数据库。
oracle执行计划中filter,oracle 执行计划 filter下多个节点的优化
weixin_39616287的博客
04-05 428
今天碰到一个性能问题，SQL执行60多s，改后2s，把这个案例写成一个例子：drop table t1 purge;drop table t2 purge;drop table t3 purge;create table t1 as select object_id,object_name,object_type from dba_objects;create table t2 as select...
sqlchemy filter和filter_by
liulanba的博客
10-25 3472
3、在使用多条件匹配的时候，filter需要借助sqlalchemy里的and_或者连续调用filter来实现， 而filter_by不需要，直接把多个匹配条件写在一起。1、filter引用列名时，使用“类名.属性名”的方式，比较使用两个等号“==”2、filter_by引用列名时，使用“属性名”，比较使用一个等号“=”4、在使用多条件匹配时，用到>=、>、
SQL必知必会-过滤数据
魂小猫的博客
09-10 2535
过滤数据 数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定 搜索条件（ search criteria ），搜索条件也称为过滤条件（ filter condition ）。 使用 WHERE 子句 在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出，如下所示： SELECT ...
SQL优化 第八章 案例模拟 8.3 FILTER优化1
songjian1104的专栏
06-14 276
8.3.1 数据环境准备 drop table test1; drop table test2; drop table test3; create table test1 as select * from dba_objects; create table test2 as select * from test1; create table test3 as select * from...
hivesql 优化
最新发布
05-15
<think>好的，用户现在想优化Hive SQL的查询性能。根据用户提供的引用内容，我需要结合之前的回答和现有的引用资料来给出建议。首先，用户之前的问题是关于平衡数据隐私和个性化推荐，这次转向了Hive SQL优化，看起来用户可能是在处理大数据平台中的性能问题，尤其是在使用Hive进行数据查询时遇到了瓶颈。 用户提供的引用里提到了Hive SQL的运行原理、UNION ALL的优化、并行执行参数、分桶采样、Join优化、索引以及数据倾斜处理。需要综合这些信息，结合常见的Hive优化策略来解答。 首先，我需要确定用户的具体需求。用户可能是在处理大规模数据集，查询速度慢，资源消耗大。他们希望了解如何调整Hive配置、改写查询语句或者使用特定技术来提升性能。 接下来，我需要考虑Hive的常见优化手段，比如MapReduce的配置、数据存储格式、分区和分桶、Join优化、并行执行、数据倾斜处理等。同时，用户提供的引用中提到了hive.exec.parallel参数，这说明用户可能已经了解并行执行的重要性，但需要更详细的指导。 另外，用户引用了分桶采样和不同的Join优化方案，如MapJoin、Bucket Map Join、SMB Join，这些都是Hive中处理Join操作时的关键优化技术。需要解释这些方法的应用场景和实现方式。 数据倾斜是一个常见问题，用户引用的资料中也提到了针对join和group by造成的数据倾斜的解决方案，比如使用随机数打散或预聚合。需要结合这些方法给出具体步骤。 索引优化部分，用户提到了Row Group Index和Bloom Filter Index，可能需要解释如何创建和使用这些索引来加速查询。 另外，用户提供的引用中有具体的SQL例子，比如设置reduce任务数、使用distribute by rand()来均匀分布数据，这些可以作为优化案例来说明。 最后，要确保回答结构清晰，分点列出，每个优化方法都附带原理、技术实现和示例，可能的话引用用户提供的引用资料来支持建议。同时，生成的相关问题需要针对用户可能进一步关心的点，比如参数调优、数据倾斜处理、执行计划分析等。</think>### Hive SQL查询性能优化综合方案 根据Hive的运行机制和大数据场景特点，结合金融行业实践经验，以下是系统化的优化方法（部分方法已通过某银行万亿级数据平台验证[^1][^3]）： --- #### 一、**基础架构优化** 1. **启用向量化执行引擎** - **原理**：将行处理改为列批处理，减少CPU消耗 - **配置**： ```sql SET hive.vectorized.execution.enabled=true; -- 启用向量化 SET hive.vectorized.execution.reduce.enabled=true; -- Reduce阶段向量化 ``` [某银行数据仓库实测查询速度提升3倍[^3]] 2. **Tez执行引擎替换MapReduce** - **优势**：通过DAG调度减少中间结果落盘 - **配置**： ```sql SET hive.execution.engine=tez; SET tez.grouping.split-count=2048; -- 控制并行度 ``` [^3] --- #### 二、**数据存储优化** 3. **ORC文件格式 + 压缩** - **实现方案**： ```sql CREATE TABLE orders_orc STORED AS ORC TBLPROPERTIES ("orc.compress"="SNAPPY") AS SELECT * FROM orders_text; ``` - **效果**：相比Text格式，存储空间减少70%，查询速度提升45%[^1] 4. **分区与分桶联合优化** - **分区设计**：按日期分区`PARTITIONED BY (dt STRING)` - **分桶配置**： ```sql CLUSTERED BY (user_id) INTO 1024 BUCKETS ``` - **优势**：在Join时触发Bucket Map Join，避免全表扫描[^3] --- #### 三、**计算过程优化** 5. **MapJoin强制优化** - **适用场景**：小表（<25MB）与大表Join - **配置**： ```sql SET hive.auto.convert.join=true; -- 自动转换 SET hive.mapjoin.smalltable.filesize=256000000; -- 提升小表阈值 ``` [某电商平台通过此优化将Join耗时从58分钟降至4分钟[^3]] 6. **Union All并行执行** - **原理解析**：Hive对Union All自动优化并行度，但需配合参数： ```sql SET hive.exec.parallel=true; -- 开启并行 SET hive.exec.parallel.thread.number=16; -- 控制并发数 ``` [^2] 7. **Reduce阶段优化** - **动态调整Reduce数量**： ```sql SET hive.exec.reducers.bytes.per.reducer=512000000; -- 每个Reducer处理数据量 ``` - **手动指定（特定场景）**： ```sql SET mapred.reduce.tasks=20; -- 如用户引用示例[^5] ``` --- #### 四、**高级调优技巧** 8. **数据倾斜解决方案** - **Join倾斜处理**： ```sql -- 对倾斜键添加随机前缀 SELECT * FROM a LEFT JOIN (SELECT *, concat(rand(),'_',key) as new_key FROM b) tmp ON a.key = tmp.new_key ``` [^3] - **Group By倾斜处理**： ```sql SET hive.groupby.skewindata=true; -- 启用两阶段聚合 ``` 9. **Bloom Filter索引加速** - **创建索引**： ```sql CREATE INDEX user_bloom ON TABLE user_log (user_id) AS 'BLOOMFILTER' WITH DEFERRED REBUILD; ``` - **应用场景**：快速过滤`WHERE user_id IN (...)`类查询[^3] --- #### 五、**SQL写法优化** 10. **谓词下推优化** - **错误写法**： ```sql SELECT * FROM (SELECT * FROM t1 WHERE dt='2023-07') a JOIN t2 ON a.id=t2.id WHERE t2.value>1000; ``` - **优化写法**： ```sql SELECT * FROM t1 JOIN (SELECT * FROM t2 WHERE value>1000) b ON t1.id=b.id WHERE t1.dt='2023-07'; ``` [使过滤条件尽早生效[^1]] 11. **避免笛卡尔积** - **检测工具**： ```sql EXPLAIN DEPENDENCY SELECT ...; -- 查看数据依赖关系 ``` [^3] --- ### 优化效果验证方法 | 验证维度 | 工具/方法 | 参考指标 | |----------------|-----------------------------|----------------------------| | 资源消耗 | YARN ResourceManager | 内存峰值降低30%+[^1] | | 执行效率 | EXPLAIN ANALYZE | Map阶段耗时减少40%[^3] | | 数据分布 | `ANALYZE TABLE ... COMPUTE STATISTICS` | 分桶数据均匀度>95%[^3] | ---
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值