对Group By 语句的一次优化过程

最新推荐文章于 2024-01-13 22:52:35 发布
最新推荐文章于 2024-01-13 22:52:35 发布
阅读量444 收藏
点赞数
分类专栏： Oracle 文章标签： 优化 sql parallel disk join filter

生产环境中发现一条语句很慢，拿回来一看，其实是一个简单的Group By语句:

表CCMMT的数据量比较大，5M多条记录。

 

1、

SQL> select CDE, CID

 2 from CCMMT

 3 GROUP BY CDE, CID

 4 having max(ADT) < sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:06:17.49

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=238583 Bytes=4771660)

   1    0   FILTER

   2    1     SORT (GROUP BY NOSORT) (Cost=414 Card=238583 Bytes=4771660)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CCMMT' (Cost=414 Card=57969096 Bytes=1159381920)

   4    3         INDEX (FULL SCAN) OF 'CCMMT_TEMP_IDX' (NON-UNIQUE) (Cost=26 Card=57969096)

 

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

    2769177 consistent gets

    1089991 physical reads

          0 redo size

   23926954 bytes sent via SQL*Net to client

     519785 bytes received via SQL*Net from client

      47196 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

     707924 rows processed

 

要6min多返回。尝试调整语句写法，用minus代替Group By:

 

2、

SQL> select DISTINCT CDE, CID

 2 from CCMMT

 3 where ADT < sysdate - 180

 4 minus

 5 select DISTINCT CDE, CID

 6 from CCMMT

 7 where ADT >= sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:00:21.53

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=190624 Card=2794940

          Bytes=111797600)

   1    0   MINUS

   2    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13049001

   3    2       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13049000

   4    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13050001

   5    4       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13050000

 

   2 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13049000 ORDER BY C0,C1

   3 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

   4 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13050000 ORDER BY C0,C1

   5 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

 

Statistics

----------------------------------------------------------

          0 recursive calls

         33 db block gets

     126566 consistent gets

     129243 physical reads

          0 redo size

   18461368 bytes sent via SQL*Net to client

     519785 bytes received via SQL*Net from client

      47196 SQL*Net roundtrips to/from client

          4 sorts (memory)

          2 sorts (disk)

     707924 rows processed

 

效果不错，Consistent gets 和 Physical Reads都下降了，同时只需要21s就返回了。但从查询计划看，用到了并行查询，因此会消耗更多的CPU。

在(ADT, CDE, CID )上创建索引，再次执行：

 

3、

SQL> select DISTINCT CDE, CID

 2 from CCMMT

 3 where ADT < sysdate - 180

 4 minus

 5 select DISTINCT CDE, CID

 6 from CCMMT

 7 where ADT >= sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:00:26.94

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36018 Card=2794940 Bytes=111797600)

   1    0   MINUS

   2    1     SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   3    2       INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

   4    1     SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   5    4       INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

 

Statistics

----------------------------------------------------------

          0 recursive calls

        118 db block gets

      22565 consistent gets

      31604 physical reads

          0 redo size

   18461368 bytes sent via SQL*Net to client

     519785 bytes received via SQL*Net from client

      47196 SQL*Net roundtrips to/from client

          1 sorts (memory)

          1 sorts (disk)

     707924 rows processed

 

效果也比较理想，consistent gets和physical reads再次大大下降，返回时间和上面差不多，在一个数量级上，但是不再使用并行查询了。

用NOT Exists代替minus:

 

4、

SQL> select DISTINCT CDE, CID

 2 from CCMMT a

 3 where ADT < sysdate - 180

 4 AND NOT EXISTS

 5 (SELECT CDE, CID FROM

 6 (select DISTINCT CDE, CID

 7 from CCMMT

 8 where ADT >= sysdate - 180) b

 9 WHERE a.CDE = b.CDE

 10 AND a.CID = b.CID);

 

707924 rows selected.

 

Elapsed: 00:10:35.70

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=600 Card=144923 Bytes=2898460)

   1    0   SORT (UNIQUE) (Cost=600 Card=144923 Bytes=2898460)

   2    1     INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE)(Cost=2 Card=144923 Bytes=2898460)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CCMMT' (Cost=2 Card=1 Bytes=20)

   4    3         INDEX (RANGE SCAN) OF 'CCMMT_TEMP_IDX' (NON-UNIQUE) (Cost=1 Card=9)

 

Statistics

----------------------------------------------------------

          5 recursive calls

        118 db block gets

   40535587 consistent gets

    3157604 physical reads

          0 redo size

   18461368 bytes sent via SQL*Net to client

     519785 bytes received via SQL*Net from client

      47196 SQL*Net roundtrips to/from client

          2 sorts (memory)

          1 sorts (disk)

     707924 rows processed

 

FT! consistent gets和physical reads爆涨，10min才返回结果！

用Not In换掉Not Exists:

 

5、

SQL> select DISTINCT CDE, CID

 2 from CCMMT a

 3 where ADT < sysdate - 180

 4 AND (CDE, CID) NOT IN

 5 (select DISTINCT CDE, CID

 6 from CCMMT

 7 where ADT >= sysdate - 180);

 

707924 rows selected.

 

Elapsed: 00:01:00.70

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36425 Card=1 Bytes=40)

   1    0   SORT (UNIQUE NOSORT) (Cost=36425 Card=1 Bytes=40)

   2    1     MERGE JOIN (ANTI) (Cost=36423 Card=1 Bytes=40)

   3    2       SORT (JOIN) (Cost=18212 Card=2898455 Bytes=57969100)

   4    3         INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

   5    2       SORT (UNIQUE) (Cost=18212 Card=2898455 Bytes=57969100)

   6    5         INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

 

Statistics

----------------------------------------------------------

          0 recursive calls

        419 db block gets

      22565 consistent gets

      98692 physical reads

          0 redo size

   18461368 bytes sent via SQL*Net to client

     519785 bytes received via SQL*Net from client

      47196 SQL*Net roundtrips to/from client

          1 sorts (memory)

          1 sorts (disk)

     707924 rows processed

 

恩，consistent gets和建了索引时的minus方式一样，但是physical reads太大，返回时间太长---1min。同时用到了刚才建的索引。（呵呵，所以说，NOT EXISTS并不是什么情况下都比NOT IN更优啊）

在尝试用left join + is null代替not in:

 

6、

SQL> SELECT a.CDE, a.CID

 2 FROM

 3 (select DISTINCT CDE, CID

 4 from CCMMT

 5 where ADT < sysdate - 180) a,

 6 (select DISTINCT CDE, CID

 7 from CCMMT

 8 where ADT >= sysdate - 180) b

 9 WHERE a.CDE = b.CDE(+)

 10 AND a.CID = b.CID(+)

 11 AND b.CDE IS NULL;

 

707924 rows selected.

 

Elapsed: 00:00:25.46

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=54675 Card=2794940 Bytes=117387480)

   1    0   FILTER

   2    1     MERGE JOIN (OUTER)

   3    2       VIEW (Cost=18009 Card=2794940 Bytes=58693740)

   4    3         SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   5    4           INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

   6    2       SORT (JOIN) (Cost=36667 Card=2794940 Bytes=58693740)

   7    6         VIEW (Cost=18009 Card=2794940 Bytes=58693740)

   8    7           SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   9    8             INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

 

Statistics

----------------------------------------------------------

         10 recursive calls

        118 db block gets

      22569 consistent gets

      31300  physical reads

          0 redo size

   18461368 bytes sent via SQL*Net to client

     519785 bytes received via SQL*Net from client

      47196 SQL*Net roundtrips to/from client

          6 sorts (memory)

          1 sorts (disk)

     707924 rows processed

 

效果不错，和有索引时使用minus在同一数量级上。

 

总结，以上几种方式中，效果最好的应该是第3种和第6种，buffer gets、磁盘IO和CPU消耗都比较少，返回时间大大减少，但是需要新建一个索引，消耗更多磁盘空间，并存在影响其它语句的正常查询计划的风险。而第2种方式应该是次好的。在返回时间上，和上面两种差不多，不需要新的索引，但是会消耗更多的内存、磁盘和CPU资源。

出于综合考虑，采用了第2种方式对生产库进行了优化。

（以上例子中的对象名进行了替换，其他都是原版）


作者： fuyuncat

来源：http://www.cnblogs.com/rootq/archive/2008/09/08/1287174.html


heqiyu34
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL group by语句如何优化
01-19
在MySQL中，新建立一张表，该表有三个字段，分别是id，a，b，插入1000条每个字段都相等的记录，如下： mysql> show create table t1\G *************************** 1. row *************************** ...
GROUP by 方法　　Ｃ#
a594455873的专栏
01-30 415
1、用两层循环计算，前提条件是数据已经按分组的列排好序的。 DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[] { new DataColumn("name", typeof(string)), new DataColum...
C#对List＜＞进行遍历（group by）
Stdio_QU的博客
11-25 3743
mainlist 、mainListLeft 为已存在 list，此函数为汇总 mainlist 的内容到 mainListLeft 中 void gather() { mainListLeft.Clear(); var query = from item in mainList group item by new { item.productCode
oracle--- to_char,to_date 函数
duruiyang603的博客
09-18 329
WHERE CREATED_DATE > to_date(to_char(sysdate-180, 'yyyymmdd') ||' 00:00:00','yyyymmdd hh24:mi:ss') 其中： to_date( to_char(sysdate-180, 'yyyymmdd') ||' 00:00:00','yyyymmdd hh24:mi:ss')   to
group by 优化
我思故我在
09-05 3867
group by 优化方法--索引 在数据量大的时候，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。 因为B+树是有序的，所以直接给group by 的字段加上索引，就可以避免创建临时表排序。 group by 优化方法 -- 直接排序 Group by 默认在内存中排序，先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表为了避免这种脱了裤子放屁的操作，我们如果知道查出数据量比较大，那就直接进行磁盘排序，给语句加上SQL_BIG_RESULT命令
MySQL优化GROUP BY方案
09-10
满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中，MySQL能够做得更好，即通过索引访问而不用创建...
oracle中使用group by优化distinct
12-16
今天mentor给了一个sql语句优化的任务。（环境是sql developer）有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的： SELECT DISTINCT 'AMEND_NEW', reporttitle, ...
MySQL利用索引优化ORDER BY排序语句的方法
01-19
创建表&创建索引 create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc...通过索引优化来实现MySQL的ORDER BY语句优化： 1、ORDER BY的索引优化 如果一个SQL语句形如： SELECT
My SQL group by取同组第一条
01-20
这里插一句，MySQL高版本5.7以后，sql_mode有变化，其中之一体现为，group by写法。可查看文章了解： 远程centOS6.8final，MySQL5.6； 表结构，数据都是一样； 测试SQL语句： SELECT SwipeID,MemberID,AddTime FROM...
C# LIST 使用GroupBy分组
热门推荐
zhangxiao0122的博客
03-15 3万+
根据论坛及博客整理。 原有list集合， List&lt;CommodityInfo&gt; commodityInfoList = new List&lt;CommodityInfo&gt;(); public class CommodityInfo { public string StoreID {get; set;} public st...
Group by 优化
weixin_43420030的博客
08-15 218
group by 优化
MySQL 数据库 group by 语句怎么优化？
Q54665642ljf的博客
11-28 4567
首先 group by 用到索引，那就在索引树上索引数据，但是因为加了 where 条件，还是需要在去表里检索几乎所有的数据， 这样子，还不如直接去表里进行全表扫，这样还更快些。就比如上面这个 sql，如果存在这个商品的名称被修改过了，但是它们的 id 确还是一样的，那么这个时候展示的商品名称是修改前的还是修改后的呢?注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。因为上面也说了，临时表也分为内存临时表和磁盘临时表。
c# list GroupBy 分组后数据输出技巧
qq165285727的专栏
07-24 6351
c#list数组 进行分组，并且对分组后的数据，进行分类输出；   List&lt;IGrouping&lt;int?,PMPModel.PF.PF_AMS_Project&gt;&gt; group = list.GroupBy(c =&gt; c.ProjectId).ToList();  foreach (IGrouping&lt;int?, PMPModel.PF.PF_AMS_Pr...
C# List中的GroupBy用法
IT Tech
10-09 1万+
setupInfoList是一个泛型集合List  //pet为对象参数，可自定义； JoinSetupid为我要分组的key query = setupInfoList.GroupBy(pet => pet.JoinSetupid, pet => pet); foreach (IGrouping info in query) { List sl=info.ToList();//分组
性能优化-group by的优化
逍遥云恋
01-15 2376
4、group by的优化 最好使用同一表中的列， 需求：每个演员所参演影片的数量-（影片表和演员表） explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id...
group by 查询慢的话,如何优化？
最新发布
liu289747235的专栏
01-13 1612
怎么确定走的是全字段排序还是rowid 排序排序呢？由一个数据库参数控制的，max_length_for_sort_data。就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分。如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次。如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据。遍历完成后，再根据字段author做。group by可能会慢在哪里？根据一定的规则，进行分组。，得到结果集返回给客户端。
【019期】告诉面试官，我能优化 Group By，而且知道得很深！
Java精选
06-06 566
>>号外：关注“Java精选”公众号，回复“2021面试题”，领取免费资料！“Java精选面试题”小程序，3000+ 道面试题在线刷，最新、最全 Java 面试题！导读当我们交...
sysdate()简单用法
weixin_30764771的博客
10-09 4884
环境： create table rq (xm varchar2(10),age number,zw varchar(10),rzrq date);insert into rq values ('小崔',23,'运维工程师',(to_date('2017-07-02','yyyy-mm-dd')));insert into rq values ('小赵',29,'java工程师',(to_dat...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值