一次涉及两个大表关联的优化

原创 2013年12月05日 15:29:56

sql:

select *
  from (select fd.analystid,
               dr.objid DOCID,
               dr.title,
               row_number() over(partition by fd.analystid order by dr.doctime desc) rown
          from fa_docanalystrela fd
         inner join doc_researchreportcore dr
            on (fd.docid = dr.objid )
            where dr.doctime > sysdate - 90)
   where rown = 1
   and  analystid in ('115880',
                     '84947',
                     '114456',
                     '83715',
                     '85335',
                     '84106',
                     '116090',
                     '112112',
                     '112265',
                     '127614');

执行计划:



执行很慢,要12s左右,而只得到7条记录。


这是一个看似很简单的sql,这个sql当时优化了一下午,想了很多方法,也没拿到好的优化方法,晚上请高手帮忙也没搞定。

第三天,没事的时候,好好分析了下这两个表的关联,找到了一点突破口:

(1)、查看量表的记录数:

select count(*) from DOC_RESEARCHREPORTCORE;--832391
select count(*) from FA_DOCANALYSTRELA--699367

(2)、查看量表存储空间大小:


(3)、根据表关系查看经过过滤条件后量表关联时的记录:

select count(*) from doc_researchreportcore where doctime > sysdate - 90

--73949

select count(*) from fa_docanalystrela where analystid in ('115880',
                     '84947',
                     '114456',
                     '83715',
                     '85335',
                     '84106',
                     '116090',
                     '112112',
                     '112265',
                     '127614');
    --2829

 分析:

1、由(1)和(2)可看到,量表记录差不过,但是存储空间相差很大,刚刚开始以为是

doc_researchreportcore数据块稀疏或者有过多的空块造成的,处理使块收缩后所占存储空间基本上没有变化;

查看表结构发现该表字段很多的缘故,这里想到把所需要搜索的字段建组合索引,这个消除对大表的回表。

这里建立组合索引(DOCTIME, OBJID, TITLE)。

2、由(3)看出,入股走loop关联的话会是2829和73949的嵌套循环,这个会使开销很大;这里考虑走hashjoin,让小表fa_docanalystrela去驱动大表doc_researchreportcore,这样会极大的减少开销。

下面是修改后的sql和执行计划:执行时间为0.998s,多次请缓存区执行时间都控制在1s以内,基本上满足了需求。

select *
  from (select fd.analystid,
               dr.objid DOCID,
               dr.title,
               row_number() over(partition by fd.analystid order by dr.doctime desc) rown
          from fa_docanalystrela fd
         inner join doc_researchreportcore dr
            on (fd.docid = dr.objid )
            where dr.doctime > sysdate - 90)
   where rown = 1
   and  analystid in ('115880',
                     '84947',
                     '114456',
                     '83715',
                     '85335',
                     '84106',
                     '116090',
                     '112112',
                     '112265',
                     '127614');


执行计划:

 

 

总结:

这里用到了分析表连接关系,走正确的表关联关系和减少回表。

这里执行时间有12s降到0.998s,虽然基本上满足的需求,但是有2189次递归、43次排序、2861次物理读和3368次逻辑读,这些是必须的开销。


版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

记得5年前遇到一个SQL,就是一个简单的两表关联,SQL跑了差不多一天一夜,这两个表都非常巨大,每个表都有几十个G,数据量每个表有20多亿,表的字段也特别多。 相信大家也知道SQL慢在哪里了,单个进...

SQL优化,百万级2张表关联,从40分钟到3秒的历程

表结构如下:   CREATE TABLE `deviceback` (     `id` int(11) NOT NULL AUTO_INCREMENT,...

关联查询SQL的一次优化过程

本博文出自51CTO博客gaochaojs博主,有任何问题请进入博主页面互动讨论! 博文地址:http://jncumter.blog.51cto.com/812546/1620406 ...

记一次上机题目——两个表示数字的链表之和

你有两个用链表代表的整数,其中每个点包含一个整数。证书存储按照在原来证书中想法的顺序,是的第一个数字位于链表的开头,写出一个函数将两个整数相加,用链表返回和。(Tencent) 主要是代码风格的...

有关单链表的两个问题【遍历一次求中间节点,倒数第K个结点】

package com.zhiru; /* * 一个无附加头结点的单向链表示例 */ public class LinkedList { private Node head; private...

合并两个有序链表/链表反转(逆置)/找链表倒数第k点(遍历一次)

//1.【基础题】--合并两个有序链表,合并以后的链表依旧有序。 #include using namespace std; typedef struct Node { int data; ...

java获取两个字符串中最大相同子串。第一个动作:将短的那个串进行长度一次递减的子串打印

package day13; /* 获取两个字符串中最大相同子串。第一个动作:将短的那个串进行长度一次递减的子串打印。 "abcwerthelloyuiodef" "cvhellobnm" 思路...

EXCEL两个表如何通过关联合并

表Sheet1 字段A 字段B 字段C 1 1 BBC 2 2 BBD 5 1 BB...
  • t_kong
  • t_kong
  • 2013-03-26 22:13
  • 2348

两个关联表间如何建立触发器

实现功能描述:表C由表A、表B关联生成(其中表A、表B在物理库中,表C在内存数据库中),表A、表B数据变化后通过触发器将变化后记录插入到小表C_inc中,通过小表触发,最后用内存库的实时同步功能将C_...

关联两个表(权限表特殊)

public function lst(){ $role=D('role'); $count = $role->count();// 查询满足要求的总记录数 $Pa...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)