我们知道,一般在查询语句出现性能瓶颈,需要对其进行优化时,一个比较有效的手段就是DBA通过调整sql语句的执行计划,或者建立有效索引以达到数据执行时间最短的效果.
我们系统中有两个表I_ORG_PARTY_RELATIONSHIP(部门基础表),数据记录月5w条:
createtable ODS.I_ORG_PARTY_RELATIONSHIP
(
PARTY_ID VARCHAR2(40),
PARTY_ID_NAME VARCHAR2(60),
CHILD_PARTY_ID VARCHAR2(40),
CHILD_PARTY_ID_NAME VARCHAR2(60),
IF_LEAF CHAR(1)
);
createindex ODS.I_ORG_PARTY_RELATIONSHIP_X1 on ODS.I_ORG_PARTY_RELATIONSHIP (CHILD_PARTY_ID);
另有表ODS.CR_PARTY_ATTRIBUTE(部门属性表),数据记录行数月为300W条.
createtable ODS.CR_PARTY_ATTRIBUTE
(
PARTY_ID VARCHAR2(40) notnull,
ATTR_NAME VARCHAR2(60) notnull,
ATTR_VALUE VARCHAR2(255)
)
createindex ODS.CR_PARTY_ATTRIBUTE_X1 on ODS.CR_PARTY_ATTRIBUTE (ATTR_NAME);
createindex ODS.CR_PARTY_ATTRIBUTE_X2 on ODS.CR_PARTY_ATTRIBUTE (PARTY_ID);
前期设计数据库的时候为了增加数据库的业务灵活性,使用了上述设计方式,但随着系统的推广,数据量的增加,这种设计方式的性能缺陷越来越严重.
业务处理过程中有如下查询.
select t1.child_party_id as customer_id,
MAX(decode (t2.attr_name,'公司地址',t2.attr_value,NULL)) AS ADDRESS ,
MAX(decode (t2.attr_name,'企业性质',t2.attr_value,NULL)) AS CORPORATION_ATTRIBUTE,
MAX(decode (t2.attr_name,'客户类型',t2.attr_value,NULL)) AS CUSTOMER_TYPE,
MAX(decode (t2.attr_name,'营业执照',t2.attr_value,NULL)) AS business_license,
MAX(decode (t2.attr_name,'登记机关',t2.attr_value,NULL)) AS REGISTER_DEPARTMENT,
MAX(decode (t2.attr_name,'大客户',t2.attr_value,NULL)) AS IS_LARGECUSTOMER,
MAX(decode (t2.attr_name,'覆盖范围',t2.attr_value,NULL)) AS COVER_RANGE,
MAX(decode (t2.attr_name,'成立时间',t2.attr_value,NULL)) AS CREATE_TIME,
MAX(decode (t2.attr_name,'企业负责人',t2.attr_value,NULL)) AS CORPORATION_PRINCIPAL,
MAX(decode (t2.attr_name,'性别',t2.attr_value,NULL)) AS SEX,
MAX(decode (t2.attr_name,'职位',t2.attr_value,NULL)) AS POSITION,
MAX(decode (t2.attr_name,'联系电话',t2.attr_value,NULL)) AS PHONE
from ods.i_org_party_relationship t1,ods.cr_party_attribute t2
where t1.child_party_id = t2.party_id
AND t1.if_leaf = 1
groupby t1.child_party_id;
本查询在正常情况下的执行速度为70秒,检查oracle的执行计划后我们发现是结果集较大的表ods.cr_party_attribute 在做driving table,而ods.i_org_party_relationship在做被探查表.我们知道这种方式一般来说会造成执行速度较慢的情况.所以我将from后面的表顺序做了个调整,使得查询转换为如下形势.
select t1.child_party_id as customer_id,
MAX(decode (t2.attr_name,'公司地址',t2.attr_value,NULL)) AS ADDRESS ,
MAX(decode (t2.attr_name,'企业性质',t2.attr_value,NULL)) AS CORPORATION_ATTRIBUTE,
MAX(decode (t2.attr_name,'客户类型',t2.attr_value,NULL)) AS CUSTOMER_TYPE,
MAX(decode (t2.attr_name,'营业执照',t2.attr_value,NULL)) AS business_license,
MAX(decode (t2.attr_name,'登记机关',t2.attr_value,NULL)) AS REGISTER_DEPARTMENT,
MAX(decode (t2.attr_name,'大客户',t2.attr_value,NULL)) AS IS_LARGECUSTOMER,
MAX(decode (t2.attr_name,'覆盖范围',t2.attr_value,NULL)) AS COVER_RANGE,
MAX(decode (t2.attr_name,'成立时间',t2.attr_value,NULL)) AS CREATE_TIME,
MAX(decode (t2.attr_name,'企业负责人',t2.attr_value,NULL)) AS CORPORATION_PRINCIPAL,
MAX(decode (t2.attr_name,'性别',t2.attr_value,NULL)) AS SEX,
MAX(decode (t2.attr_name,'职位',t2.attr_value,NULL)) AS POSITION,
MAX(decode (t2.attr_name,'联系电话',t2.attr_value,NULL)) AS PHONE
from ods.cr_party_attribute t2 ,ods.i_org_party_relationship t1
where t1.child_party_id = t2.party_id
AND t1.if_leaf = 1
groupby t1.child_party_id;
过段时间发现查询的执行时间非但没有减少,反而变得更加无法接受,达到了130秒,比原来的时间差不多增长了一倍.
很是郁闷.
后经仔细分析执行语句发现了问题所在,由于我们的结果集中都是大表的内容,所以在小表做驱动表的情况下,执行的过程如下:
1,遍历表ods.i_org_party_relationship,
2,对表ods.i_org_party_relationship的每一条记录,通过索引ODS.CR_PARTY_ATTRIBUTE_X2找出一批表ODS.CR_PARTY_ATTRIBUTE的记录
3,然后再根据索引给定的记录rowid,找到表的实际记录.
造成这种执行方式的原因在于表ods.CR_PARTY_ATTRIBUTE的索引不能包含所有要检索的结果集导致多一步的物理记录检索.而在大表做驱动表的时候,由于我们并不需要检索出小表中的任何字段,所以这种连接方式将只会遍历大表和小表的索引.这也是后者执行速度反而比前者慢的原因.
找到问题的原因之后,我们来看,如果我们能在大表上建立包含所有需要结果集合的索引,然后执行小表作为驱动的查询,结果表明查询时间缩小了一倍,变成30秒.
createindex ODS.CR_PARTY_ATTRIBUTE_X3 on ODS.CR_PARTY_ATTRIBUTE (PARTY_ID,ATTR_NAME,attr_value);
由于系统运行已经有很长时间了,如果修改表结构的话会造成非常大的相关变动,所以在不能修改表结构的前提上,我不知道这样的优化是不是最好的方式.
另外,我也曾考虑过在数据插入的时候将另外建立存储表,在对属性表执行维护的时候通过触发器将数据写入另外建立的表中,这样也可以有效提高查询速度.
欢迎高手给点意见
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-16 16:31:55 |
赞(
操作
