关于union all 的优化

最新推荐文章于 2024-04-26 18:29:39 发布
最新推荐文章于 2024-04-26 18:29:39 发布
阅读量6.9k 收藏 5
点赞数
分类专栏： sql 文章标签： filter access table sql 优化 null
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kelly1984/article/details/8058911
版权

1：还是先看看这个差性能的sql执行计划，本来此类sql的执行计划凭经验就知道有多少集合就有多少次对表的扫描。

SQL> explain plan for
  2  select count(*) from playinfo t where tableid=1 and shoeid=11493 and betresult=1 
  3  union all
  4  select count(*) from playinfo t where tableid=1 and shoeid=11493 and betresult=2 
  5  union all
  6  select count(*) from playinfo t where tableid=1 and shoeid=11493 and betresult=3
  7  union all
  8  select count(*) from playinfo t where tableid=1 and shoeid=11494 and betresult=1 
  9  union all
 10  select count(*) from playinfo t where tableid=1 and shoeid=11494 and betresult=2 
 11  union all
 12  select count(*) from playinfo t where tableid=1 and shoeid=11494 and betresult=3
 13  union all
 14  select count(*) from playinfo t where tableid=1 and shoeid=11495 and betresult=1 
 15  union all
 16  select count(*) from playinfo t where tableid=1 and shoeid=11495 and betresult=2 
 17  union all
 18  select count(*) from playinfo t where tableid=1 and shoeid=11495 and betresult=3 
 19  union all
 20  select count(*) from playinfo t where tableid=1 and shoeid=11496 and betresult=1 
 21  union all
 22  select count(*) from playinfo t where tableid=1 and shoeid=11496 and betresult=2 
 23  union all
 24  select count(*) from playinfo t where tableid=1 and shoeid=11496 and betresult=3
 25  union all
 26  select count(*) from playinfo t where tableid=1 and shoeid=11497 and betresult=1 
 27  union all
 28  select count(*) from playinfo t where tableid=1 and shoeid=11497 and betresult=2 
 29  union all
  30  select count(*) from playinfo t where tableid=1 and shoeid=11497 and betresult=3
 31  union all
 32  select count(*) from playinfo t where tableid=1 and shoeid=11498 and betresult=1 
 33  union all
 34  select count(*) from playinfo t where tableid=1 and shoeid=11498 and betresult=2 
 35  union all
 36  select count(*) from playinfo t where tableid=1 and shoeid=11498 and betresult=3
 37  union all
 38  select count(*) from playinfo t where tableid=1 and shoeid=11499 and betresult=1 
 39  union all
 40  select count(*) from playinfo t where tableid=1 and shoeid=11499 and betresult=2 
 41  union all
 42  select count(*) from playinfo t where tableid=1 and shoeid=11499 and betresult=3
 43  union all
 44  select count(*) from playinfo t where tableid=1 and shoeid=11500 and betresult=1 
 45  union all
 46  select count(*) from playinfo t where tableid=1 and shoeid=11500 and betresult=2 
 47  union all
 48  select count(*) from playinfo t where tableid=1 and shoeid=11500 and betresult=3
 49  union all
 50  select count(*) from playinfo t where tableid=1 and shoeid=11501 and betresult=1 
 51  union all
 52  select count(*) from playinfo t where tableid=1 and shoeid=11501 and betresult=2 
 53  union all
 54  select count(*) from playinfo t where tableid=1 and shoeid=11501 and betresult=3 
 55  union all
 56  select count(*) from playinfo t where tableid=1 and shoeid=11502 and betresult=1 
 57  union all
select count(*) from playinfo t where tableid=1 and shoeid=11502 and betresult=2 
 59  union all
 60  select count(*) from playinfo t where tableid=1 and shoeid=11502 and betresult=3
 61  union all
 62  select count(*) from playinfo t where tableid=1 and shoeid=11503 and betresult=1 
 63  union all
 64  select count(*) from playinfo t where tableid=1 and shoeid=11503 and betresult=2 
 65  union all
select count(*) from playinfo t where tableid=1 and shoeid=11503 and betresult=3
 67  union all
 68  select count(*) from playinfo t where tableid=1 and shoeid=11504 and betresult=1 
 69  union all
 70  select count(*) from playinfo t where tableid=1 and shoeid=11504 and betresult=2 
 71  union all
 72  select count(*) from playinfo t where tableid=1 and shoeid=11504 and betresult=3
 73  union all
 74  select count(*) from playinfo t where tableid=1 and shoeid=11505 and betresult=1 
 75  union all
 76  select count(*) from playinfo t where tableid=1 and shoeid=11505 and betresult=2 
 77  union all
 78  select count(*) from playinfo t where tableid=1 and shoeid=11505 and betresult=3
 79  union all
 80  select count(*) from playinfo t where tableid=1 and shoeid=11506 and betresult=1 
 81  union all
 82  select count(*) from playinfo t where tableid=1 and shoeid=11506 and betresult=2 
 83  union all
 84  select count(*) from playinfo t where tableid=1 and shoeid=11506 and betresult=3
 85  union all
 86  select count(*) from playinfo t where tableid=1 and shoeid=11507 and betresult=1 
 87  union all
 88  select count(*) from playinfo t where tableid=1 and shoeid=11507 and betresult=2 
 89  union all
 90  select count(*) from playinfo t where tableid=1 and shoeid=11507 and betresult=3
 91  union all
 92  select count(*) from playinfo t where tableid=1 and shoeid=11508 and betresult=1 
 93  union all
 94  select count(*) from playinfo t where tableid=1 and shoeid=11508 and betresult=2 
 95  union all
 96  select count(*) from playinfo t where tableid=1 and shoeid=11508 and betresult=3
 97  /

Explained.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3417941786

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    48 |   528 |   241  (98)| 00:00:03 |
|   1 |  UNION-ALL          |          |       |       |            |          |
|   2 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|*  3 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|*  5 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|   6 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|*  7 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|   8 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|*  9 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  10 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 11 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  12 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 13 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  14 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 15 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  16 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 17 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  18 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 19 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  20 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 21 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  22 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 23 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  24 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 25 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  26 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 27 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  28 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 29 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  30 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
* 31 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  32 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 33 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  34 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 35 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  36 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 37 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  38 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 39 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  40 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 41 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  42 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 43 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  44 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 45 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  46 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 47 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  48 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 49 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  50 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 51 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  52 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 53 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  54 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 55 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  56 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 57 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  58 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 59 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  60 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 61 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  62 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 63 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  64 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 65 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  66 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 67 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  68 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 69 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  70 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 71 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  72 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 73 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  74 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 75 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  76 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 77 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  78 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 79 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  80 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 81 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  82 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 83 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  84 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 85 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  86 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 87 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  88 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 89 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  90 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 91 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|  92 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 93 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  94 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 95 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
|  96 |   SORT AGGREGATE    |          |     1 |    11 |            |          |
|* 97 |    TABLE ACCESS FULL| PLAYINFO |    18 |   198 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("SHOEID"=11493 AND "BETRESULT"=1 AND "TABLEID"=1)
   5 - filter("SHOEID"=11493 AND "BETRESULT"=2 AND "TABLEID"=1)
   7 - filter("SHOEID"=11493 AND "BETRESULT"=3 AND "TABLEID"=1)
      9 - filter("SHOEID"=11494 AND "BETRESULT"=1 AND "TABLEID"=1)
  11 - filter("SHOEID"=11494 AND "BETRESULT"=2 AND "TABLEID"=1)
  13 - filter("SHOEID"=11494 AND "BETRESULT"=3 AND "TABLEID"=1)
  15 - filter("SHOEID"=11495 AND "BETRESULT"=1 AND "TABLEID"=1)
  17 - filter("SHOEID"=11495 AND "BETRESULT"=2 AND "TABLEID"=1)
  19 - filter("SHOEID"=11495 AND "BETRESULT"=3 AND "TABLEID"=1)
  21 - filter("SHOEID"=11496 AND "BETRESULT"=1 AND "TABLEID"=1)
  23 - filter("SHOEID"=11496 AND "BETRESULT"=2 AND "TABLEID"=1)
  25 - filter("SHOEID"=11496 AND "BETRESULT"=3 AND "TABLEID"=1)
  27 - filter("SHOEID"=11497 AND "BETRESULT"=1 AND "TABLEID"=1)
  29 - filter("SHOEID"=11497 AND "BETRESULT"=2 AND "TABLEID"=1)
  31 - filter("SHOEID"=11497 AND "BETRESULT"=3 AND "TABLEID"=1)
  33 - filter("SHOEID"=11498 AND "BETRESULT"=1 AND "TABLEID"=1)
  35 - filter("SHOEID"=11498 AND "BETRESULT"=2 AND "TABLEID"=1)
  37 - filter("SHOEID"=11498 AND "BETRESULT"=3 AND "TABLEID"=1)
  39 - filter("SHOEID"=11499 AND "BETRESULT"=1 AND "TABLEID"=1)
  41 - filter("SHOEID"=11499 AND "BETRESULT"=2 AND "TABLEID"=1)
  43 - filter("SHOEID"=11499 AND "BETRESULT"=3 AND "TABLEID"=1)
  45 - filter("SHOEID"=11500 AND "BETRESULT"=1 AND "TABLEID"=1)
  47 - filter("SHOEID"=11500 AND "BETRESULT"=2 AND "TABLEID"=1)
  49 - filter("SHOEID"=11500 AND "BETRESULT"=3 AND "TABLEID"=1)
  51 - filter("SHOEID"=11501 AND "BETRESULT"=1 AND "TABLEID"=1)
  53 - filter("SHOEID"=11501 AND "BETRESULT"=2 AND "TABLEID"=1)
  55 - filter("SHOEID"=11501 AND "BETRESULT"=3 AND "TABLEID"=1)
  57 - filter("SHOEID"=11502 AND "BETRESULT"=1 AND "TABLEID"=1)
  59 - filter("SHOEID"=11502 AND "BETRESULT"=2 AND "TABLEID"=1)
  61 - filter("SHOEID"=11502 AND "BETRESULT"=3 AND "TABLEID"=1)
  63 - filter("SHOEID"=11503 AND "BETRESULT"=1 AND "TABLEID"=1)
  65 - filter("SHOEID"=11503 AND "BETRESULT"=2 AND "TABLEID"=1)
  67 - filter("SHOEID"=11503 AND "BETRESULT"=3 AND "TABLEID"=1)
  69 - filter("SHOEID"=11504 AND "BETRESULT"=1 AND "TABLEID"=1)
  71 - filter("SHOEID"=11504 AND "BETRESULT"=2 AND "TABLEID"=1)
  73 - filter("SHOEID"=11504 AND "BETRESULT"=3 AND "TABLEID"=1)
  75 - filter("SHOEID"=11505 AND "BETRESULT"=1 AND "TABLEID"=1)
  77 - filter("SHOEID"=11505 AND "BETRESULT"=2 AND "TABLEID"=1)
  79 - filter("SHOEID"=11505 AND "BETRESULT"=3 AND "TABLEID"=1)
  81 - filter("SHOEID"=11506 AND "BETRESULT"=1 AND "TABLEID"=1)
  83 - filter("SHOEID"=11506 AND "BETRESULT"=2 AND "TABLEID"=1)
  85 - filter("SHOEID"=11506 AND "BETRESULT"=3 AND "TABLEID"=1)
  87 - filter("SHOEID"=11507 AND "BETRESULT"=1 AND "TABLEID"=1)
  89 - filter("SHOEID"=11507 AND "BETRESULT"=2 AND "TABLEID"=1)
  91 - filter("SHOEID"=11507 AND "BETRESULT"=3 AND "TABLEID"=1)
  93 - filter("SHOEID"=11508 AND "BETRESULT"=1 AND "TABLEID"=1)
  95 - filter("SHOEID"=11508 AND "BETRESULT"=2 AND "TABLEID"=1)
  97 - filter("SHOEID"=11508 AND "BETRESULT"=3 AND "TABLEID"=1)

156 rows selected.


--分析上述sql。发现都是同一张表。并且谓词条件是范围的。那么直接改为简单的分组语句就可以达到同样的效果
--改写sql如下

SQL> explain plan for
  2  select shoeid,BETRESULT,count(*) from playinfo where shoeid between 11493 and 11508 and BETRESULT  between 1 and 3 and  tableid=1 group by shoeid,BETRESULT order by shoeid ;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 184645364

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    34 |   374 |     6  (17)| 00:00:01 |
|   1 |  SORT GROUP BY     |          |    34 |   374 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| PLAYINFO |   842 |  9262 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SHOEID">=11493 AND "SHOEID"<=11508 AND "BETRESULT">=1
              AND "BETRESULT"<=3 AND "TABLEID"=1)

15 rows selected.


--扫描次数只有一次。看到谓词条件为3个字段，但是出现了全表扫描。如果加个复合index效果更好

SQL> create index index_multi on PLAYINFO(SHOEID,BETRESULT,TABLEID);

Index created.

SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'PLAYINFO',
  3  cascade => true,
  4  estimate_percent => null,degree =>2,
  5  method_opt => 'for all columns size 1'
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.


SQL> explain plan for
  2  select shoeid,BETRESULT,count(*) from playinfo where shoeid between 11493 and 11508 and BETRESULT  between 1 and 3 and  tableid=1 group by shoeid,BETRESULT order by shoeid ;

Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1317533731

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    34 |   374 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY        |             |    34 |   374 |     3  (34)| 00:00:01 |
|*  2 |   INDEX FAST FULL SCAN| INDEX_MULTI |   842 |  9262 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SHOEID">=11493 AND "SHOEID"<=11508 AND "BETRESULT">=1 AND
              "BETRESULT"<=3 AND "TABLEID"=1)

15 rows selected.

 

--总结：

对于此类大量的union all 集合，造成重复扫描的工作。可以考虑with as 语句。

如果按照业务逻辑来直接写sql，出现这种sql的话。就要考虑换个方式来进行改写。

此类sql 的问题很经典。
 

kelly1984
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
GBase 8a Mpp Cluster集群产品性能优化篇之union all问题
zhu1981hui的博客
03-15 628
针对union all的场景，总体思想是建立临时表将各个sql结果放入临时表中，最后查询临时表统一输出，下面举例介绍几个项目遇到的union all问题。 减少集群层临时表，变成节点即可运行，如下面某项目语句，2个union的条件加在一起，就是不要条件即可。然后再将不必要的外部嵌套去掉，性能提升4倍。 原SQL： SELECT AAC001, Sum(NORMALLOCALCOLLECTMONTH) NORMALLOCALCOLLECTMONTH, Sum(NORMALOTHERCOLLECTMONTH)
mysql union 优化_mysql 5.7.3 对union all 的优化
weixin_29877175的博客
01-19 1989
一年的时间过得真快，去年这个时候，哥还在上海看mysql 5.6的feature, 今年就在北京看mysql 5.7的feature了。mysql的union语句一直是被人广为诟病的，因为它不分青红皂白，总是会创建temporary table， 然后把全部数据写入此临时表，再从中读取数据返回给用户。其实有些情况下，比如UNION ALL且没有最外层排序条件( top level ORDER BY...
MySQL5.7新特性之优化union all
Data & Analysis
11-06 1329
MySQL5.6中，使用union all相当于创建一张临时表，这在执行大的联合查询时候会增加I/O开销，降低查询速度。 例如执行以下SQL语句： (select id from accessLog order by id) union all (select id from access_test order by id); 在MySQL5.6环境： mysql>selectv...
优化SQL的方法
最新发布
weixin_44139651的博客
04-26 1883
使用join实现，但不适合join太多表，阿里巴巴开发者手册的规定，join表的数量不应该超过3个，join表数量太多时，会导致mysql在选错索引。一般SQL优化第一考虑的是索引优化，可使用explain命令，查看MySQL的执行计划，确认SQL是否有走索引。相对连接查询，子查询使用in关键字实现，具有结构化，相对简单，但是需要创建和删除临时表，增加资源消耗。索引可提升SQL效率，但索引需要额外的存储空间，而且还会有一定的性能消耗。left join，两个表的交集，以及左表剩余的数据，左表为驱动表。
一次union all 的优化
weixin_34075268的博客
12-15 393
-- 客户生产环境反应一个job跑不出结果，我在应用程序log中找到了大量类似的语句 select count(*) from (select LOAN_ID, CUSTOMER_REF, PROD_TYPE, SITE_CODE, COMPANY_CODE, LOANASS...
union all查询慢，优化办法
RayPick的博客
01-09 2万+
先简单介绍一下我的项目。 是一个购销存系统（这不重要） 由于数据量太大，所以每天都分一张表，一张表的数据量大概在18W样子，双十一这种节日能到60W数据样子一天。 其中要计算期初的语句 下面还有很多很多，语句很长。每次查一年的期初都要花很久，而且要求一次都查出来，数据量很大。 就是从一张表筛选出数据，然后疯狂union all，这就导致了查询的时间边长。 我的解决办法是，如果你的union al...
openGauss学习——优化 UNION ALL 语句
XL_up的博客
10-14 359
在本篇博客中，我对查询规划的预处理过程中对UNION ALL语句的优化过程进行了简单介绍。
简单了解MySQL union all与union的区别
09-08
MySQL中的`UNION`和`UNION ALL`是两种用于合并多个SQL查询结果集的语法，它们在处理数据集合并时有不同的策略和效果。 首先，`UNION`操作符用于合并两个或更多`SELECT`语句的结果，但不包括任何重复的行。这意味着...
Mysql联合查询UNION和UNION ALL的使用介绍
01-19
一、UNION和UNION ALL的作用和语法 UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中...
MySQL中UNION与UNION ALL的基本使用方法
12-15
在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。 MySQL中的UNION UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算...
MySQL中使用or、in与union all在查询命令下的效率对比
09-10
在MySQL数据库中，进行数据查询时，我们经常需要使用到`OR`、`IN`和`UNION ALL`这些操作符。这些操作符在不同的场景下有不同的效率表现，但并不是像网络上普遍认为的那样，`UNION ALL`总是比`OR`和`IN`更快。实际上...
sql in语句优化_SQL优化实操案例：union all 语句该如何优化？
weixin_39746382的博客
12-03 391
概要运行1857ms的语句如何下降到7ms，听我慢慢道来~~问题如何定位拆分SQL分成两部分优化SQL1执行计划SQL1执行时间耗时1110 ms，通过执行计划预测子查询结果集可能是导致语句低效的原因，进一步确认注：子查询结果集太大，导致连接时扫描的行数太多，耗时889ms，确认是由于子查询导致需要缩小结果集来优化此SQLSQL1改写通过缩小结果集来优化PROC_INST_ID_ = (SELEC...
Sql性能优化之UNION、UNION ALL
热门推荐
小小码农
09-06 3万+
SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。1、SQL UNION 语法SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s
oracle中union all优化,【讨论】UNION ALL 语句优化
weixin_35895753的博客
04-14 1403
SQL> select owner , view_name , text from dba_views where view_name='V_WARM_ASSET';OWNER VIEW_NAMETEXT---------- ---------- --------------------------------------------------ALCM V_WAR...
mysql中union可以怎样优化_mysql union，union all的优化
weixin_29378273的博客
01-28 1968
1 建表如下CREATE TABLE t92 (a1 int(10) unsigned NOT NULL ,b1 int(10) DEFAULT NULL,UNIQUE KEY (a1)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE t93 (a2 int(10) unsigned NOT NULL,b2 int(10) DEFAULT NULL...
SQL性能优化之union、union all
liubin5620的博客
05-29 1万+
SQL性能优化之union、union all
记一次mysql的union all的简单优化
weixin_34254823的博客
06-06 9712
2019独角兽企业重金招聘Python工程师标准>>> ...
hive sql union all的性能优化
weixin_39150719的博客
08-13 6025
union的巧妙用法，使用union也可以横向合并sql查询结果 我们都知道union是纵向连接查询结果，join是横向，但是用union all连接大家试过吗？ 恢复菜鸟代码如下： select ep.productid,productname,count(st.tduserid),count(distinct sl.tduserid),count(distinct sn.tduserid),a...
SQL优化 第八章 案例模拟 8.1 union all优化
songjian1104的专栏
06-14 353
8.1.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...
MySQL union all 优化
03-09
MySQL的UNION ALL操作是用于合并多个SELECT语句的结果集，它会返回所有满足条件的行，包括重复的行。在进行UNION ALL操作时，可以考虑以下几点进行优化： 1. 使用UNION ALL代替UNION：如果不需要去重操作，可以使用UNION ALL代替UNION，因为UNION ALL不会进行去重操作，相比之下，UNION需要对结果集进行排序和去重，会增加额外的开销。 2. 使用LIMIT限制结果集大小：如果只需要获取部分结果，可以使用LIMIT来限制返回的行数，这样可以减少数据的传输和处理量。 3. 确保每个SELECT语句的查询条件和索引优化：对于每个SELECT语句，都要确保查询条件和索引的使用是最优化的，这样可以减少每个子查询的执行时间。 4. 尽量减少子查询的数量：如果可能的话，尽量减少UNION ALL操作中子查询的数量，可以通过合并查询条件或者使用JOIN操作来实现。 5. 使用临时表进行优化：对于复杂的UNION ALL操作，可以考虑使用临时表来存储中间结果，这样可以减少内存的使用和查询的复杂度。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值