阅读更多

6顶
2踩

数据库

转载新闻 记一次苦逼的SQL查询优化

2015-07-21 15:37 by 副主编 mengyidan1988 评论(6) 有12101人浏览
最近在维护公司项目时,需要加载某页面,总共加载也就4000多条数据,竟然需要35秒钟,要是数据增长到40000条,我估计好几分钟都搞不定。卧槽,要我是用户的话估计受不了,趁闲着没事,就想把它优化一下,走你。

先把查询贴上:
select Pub_AidBasicInformation.AidBasicInfoId,
 
       Pub_AidBasicInformation.UserName,
 
       Pub_AidBasicInformation.District,
 
       Pub_AidBasicInformation.Street,
 
       Pub_AidBasicInformation.Community,
 
       Pub_AidBasicInformation.DisCard,
 
       Pub_Application.CreateOn AS AppCreateOn,
 
       Pub_User.UserName as DepartmentUserName, 
 
       Pub_Consult1.ConsultId,
 
       Pub_Consult1.CaseId,
 
       Clinicaltb.Clinical,AidNametb.AidName,
 
       Pub_Application.IsUseTraining,
 
       Pub_Application.ApplicationId,
 
       tab.num
 
FROM   Pub_Consult1
 
INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId
 
INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId                                                           
 
INNER JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical
 
            from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultId
 
left join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record  where  AidReferralId is null  group by  ApplicationId) tab on tab.ApplicationId=Pub_Consult1.ApplicationId
 
INNER JOIN(select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName  from Pub_Consult1) AidNametb on AidNametb.ConsultId=Pub_Consult1.ConsultId                              
 
LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId
 
     WHERE Pub_Consult1.Directory = 0
 
     order by Pub_Application.CreateOn desc

执行后有图有真相:



这么慢,没办法就去看看查询计划是怎么样:



这是该sql查询里面执行三个函数时生成查询计划的截图,一看就知道,执行时开销比较大,而且都是花费在聚集索引扫描上,把鼠标放到聚集索引扫描的方块上面,依次看到如下详细计划:






从这几张图里,可以看到查询I/O开销,运算符开销,估计行数,以及操作的对象和查询条件,这些都为优化查询提供了有利证据。第1,3张图IO开销比较大,第2张图估计行数比较大,再根据其它信息,首先想到的应该是去建立索引,不行的话再去改查询。

先看看数据库引擎优化顾问能给我们提供什么优化信息,有时候它能够帮我们提供有效的信息,比如创建统计,索引,分区什么的。

先打开SQL Server Profiler 把刚刚执行的查询另存为跟踪(.trc)文件,再打开数据库引擎优化顾问,做如下图操作



最后生成的建议报告如下:



在这里可以单击查看一些建议,分区,创建索引,根据提示创建了如下索引:
CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_AidBasicInformation]
 
(
 
    [AidBasicInfoId] ASC
 
)
 
 
CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Application]
 
(
 
    [ApplicationId] ASC,[ReviewUserId] ASC,[AidBasicInfoId] ASC,[CreateOn] ASC
 
)
 
CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Consult1]
 
(
 
    [Directory] ASC,[ApplicationId] ASC
 
)
 
  
 
CREATE NONCLUSTERED INDEX idnex1 ON [dbo].[Review_Aid_UseTraining_Record]
 
(
 
    [AidReferralId] ASC,[ApplicationId] ASC
 
)

索引创建后,再次执行查询,原以为可提高效率,没想到我勒个去,还是要30几秒,几乎没什么改善,优化引擎顾问有时候也会失灵,在这里只是给大家演示有这种解决方案去解决问题,有时候还是靠谱的,只是这次不靠谱。没办法,只有打开函数仔细瞅瞅,再结合上面的查询计划详细图,删除先前创建的索引,然后创建了如下索引:
CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment_Aid
 
(
 
    AdapterAssessmentId ASC, ProductDirAId  ASC
 
)
 
CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment
 
(
 
    ConsultId ASC
 
)

再次执行查询



好了,只需3.5秒,差不多提高10倍速度,看来这次是凑效了哈。

再来看看查询计划是否有改变,上张图来说明下问题:



从上图当中我们可以看到,索引扫描不见了,只有索引查找,聚集索引查找,键查找,而且运算符开销,I/O开销都降低了很多。索引扫描(Index Scan),聚集索引扫描(Clustered Index Scan)跟表扫描(Table Scan)差不多,基本上是逐行去扫描表记录,速度很慢,而索引查找(Index Seek),聚集索引查找,键查找都相当的快。优化查询的目的就是尽量把那些带有XXXX扫描的去掉,换成XXXX查找。

这样够了吗?但是回头又想想,4000多条数据得3.5秒钟,还是有点慢了,应该还能再快点,所以决定再去修改查询。看看查询,能优化的也只有那个三个函数了。

为了看函数执行效果先删除索引,看看查询中函数f_GetAidNamebyConsult1要干的事情,截取查询中与该函数有关的子查询:
select Pub_Consult1.ConsultId,AidName from (select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName
 
from Pub_Consult1) AidNametb inner join Pub_Consult1
 
on AidNametb.ConsultId=Pub_Consult1.ConsultId

得到下图的结果:



没想到就这么点数据竟然要46秒,看来这个函数真的是罪魁祸首。

该函数的具体代码就不贴出来了,而且该函数里面还欠套的另外一个函数,本身函数执行起来就慢,更何况还函数里子查询还包含函数。其实根据几相关联的表去查询几个字段,并且把一个字段的值合并到同一行,这样没必要用函数或存储过程,用子查询再加sql for xml path就行了,把该函数改成如下查询:
with cte1 as
 
(
 
    select A.AdapterAssessmentId,case when B.AidName is null then A .AidName else B.AidName end AidName
 
    from Report_AdapterAssessment_Aid as A left join Pub_ProductDir as B
 
    on A.ProductDirAId=B.ProductDirAId
 
),
 
 cte2 as
 
(
 
    
--根据AdapterAssessmentId分组并合并AidName字段值
 
    select AdapterAssessmentId,(select AidName+',' from cte1
 
                              where AdapterAssessmentId= tb.AdapterAssessmentId
 
                              for xml path(''))as AidName
 
    from cte1 as tb
 
    group by AdapterAssessmentId
 
),
 
cte3 as
 
(
 
    select ConsultId,LEFT(AidName,LEN(AidName)-1) as AidName
 
    from
 
    (
 
       select Pub_Consult1.ConsultId,cte2.AidName from Pub_Consult1,Report_AdapterAssessment,cte2
 
       where Pub_Consult1.ConsultId=Report_AdapterAssessment.ConsultId
 
       and Report_AdapterAssessment.AdapterAssessmentId=cte2.AdapterAssessmentId
 
       and  Report_AdapterAssessment.AssessTuiJian is null
 
    ) as tb)

这样查询出来的结果在没有索引的情况下不到1秒钟就行了。再把主查询写了:
select distinct  Pub_AidBasicInformation.AidBasicInfoId,
 
       Pub_AidBasicInformation.UserName,
 
       Pub_AidBasicInformation.District,
 
       Pub_AidBasicInformation.Street,
 
       Pub_AidBasicInformation.Community,
 
       Pub_AidBasicInformation.DisCard,
 
       Pub_Application.CreateOn AS AppCreateOn,
 
       Pub_User.UserName as DepartmentUserName, 
 
       Pub_Consult1.ConsultId,
 
       Pub_Consult1.CaseId,
 
       Clinicaltb.Clinical,
 
       cte3.AidName,
 
       Pub_Application.IsUseTraining,
 
       Pub_Application.ApplicationId,
 
       tab.num
 
from   Pub_Consult1
 
INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId
 
INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId                                                           
 
INNER  JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical
 
            from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultId
 
left join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record
 
           where  AidReferralId is null
 
           group by  ApplicationId) tab
 
           on tab.ApplicationId=Pub_Consult1.ApplicationId
 
left JOIN cte3 on cte3.ConsultId=Pub_Consult1.ConsultId                              
 
LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId
 
           where Pub_Consult1.Directory = 0
 
order by Pub_Application.CreateOn desc

这样基本上就完事了,在没有建立索引的情况下需要8秒钟,比没索引用函数还是快了27秒。



把索引放进去,就只需1.6秒了,比建立索引用函数而不用子查询和sql for xml path快了1.9秒。



查询里面还有个地方用了函数,估计再优化下还能提高执行效率,因为时间有限再加上篇幅有点长了,在这里就不多讲了。

最后做个总结吧,查询优化不外乎以下这几种办法:

1:增加索引或重建索引。通常在外键,连接字段,排序字段,过滤查询的字段建立索引,也可通过数据库引擎优化顾问提供的信息去建索引。有时候当你创建索引时,会发现查询还是按照索引扫描或聚集索引扫描的方式去执行,而没有去索引查找,这时很可能是你的查询字段和where条件字段没有全部包含在索引字段当中,解决这个问题的办法就是多建立索引,或者在创建索引时Include相应的字段,让索引字段覆盖你的查询字段和where条件字段。

2:调整查询语句,前提要先看懂别人的查询,搞清楚业务逻辑。

3:表分区,大数据量可以考虑。

4:提高服务器硬件配置。

本文转自:云在青天水在哪
  • 大小: 37.5 KB
  • 大小: 142.5 KB
  • 大小: 85.3 KB
  • 大小: 88.8 KB
  • 大小: 27 KB
  • 大小: 40.2 KB
  • 大小: 19.8 KB
  • 大小: 27.2 KB
  • 大小: 32.9 KB
  • 大小: 19 KB
  • 大小: 19.9 KB
6
2
评论 共 6 条 请登录后发表评论
6 楼 windlike 2015-07-29 13:45
微软的开发工具就是牛逼。
5 楼 pepple 2015-07-28 09:24
linyuliang 写道
你用的看解释计划的工具是什么呀?我的怎么没有那么高级

sqlserver客户端,执行按钮右边第3个.
4 楼 秋风未动蚕先觉 2015-07-25 15:01
辛苦的过程,理想的结果,学习了!
3 楼 tang_kun_cool 2015-07-22 16:30
一看,就知道是表值函数的问题。
2 楼 五行天下 2015-07-22 12:50
1 楼 linyuliang 2015-07-22 10:40
你用的看解释计划的工具是什么呀?我的怎么没有那么高级

发表评论

您还没有登录,请您登录后再发表评论

相关推荐

  • EJB3中MessageDrivenBean知识

    个人总结的EJB3中MessageDrivenBean的相关知识,现分享与大家,欢迎下载。

  • EJB(三)细说message-driven bean

    EJB的Message-driven bean基于JMS来实现的。在说明Message-driven bean之前需要来了解一下jms。 JMS java-message-service,java程序和企业级应用交互的一套规范,定义一组用于创建、发送、以及接受消息的API。发送者不需要知道接受者的任何内容,接受者也是同样。对于双方都是一种透明的方式来进...

  • EJB3学习笔记_Message-Driven Bean

    Why Messageing 1、Asynchrony(异步)A typical RMI-IIOP(Session Bean) client must wait while the server performs its processing2、Decoupling(解耦)An RMI-IIOP client has to know the individual s

  • 【EJB基础】Message Driven Bean

    MessageDriven Bean是EJB2.0中引入的新的企业Bean,它基于JMS消息,只能接收客户端发送的JMS消息然后处理。对客户端来说,message-driven bean就是异步消息的消费者,当消息到达之后,由容器负责调用MDB。客户端发送消息到destination,MDB作为一个MessageListener接收消息。   JMS支持两种消息模型:Point-to-Po

  • 消息驱动Bean(Message Driven Bean)

    定义:消息驱动是专门用来处理基于消息请求的组件。 消息模型:点对点消息传递和发布/订阅消息传递 点对点消息传递模型:一条信息只能传递给一个队列接收方 发布/订阅消息传递:一条消息可以由多个接收者接收 详细解析:首先它是无状态的Session Bean,客户端调用MDB时,无需等待,可以立即返回,MDB会异步处理客户的请求;MDB必须实现MessageListener接口,当容器守候检测到一...

  • 【进阶EJB】深入探讨三种Bean(三)——MessageDriven Bean

    通过前两篇文章 【进阶EJB】深入探讨三种Bean(一)——Session Bean 【进阶EJB】深入探讨三种Bean(二)——Entity Bean 已经介绍了前两种Bean,今天我们继续介绍第三种Bean——MessageDriven Bean。

  • MDB(message driven bean)收消息

    <br />一、简述<br />EJB2.0开始,引进了消息驱动的EJB,简称MDB(message driven bean)。当MOM收到消息时,能够自动传达给这种Bean。跟事件驱动一个道理。注:只是接收驱动。<br />EJB2.1,MDB又得到了加强,能够处理非JMS的消息,如:MailMessage,SMSMessage,SOAPMessage。<br />二、例子<br />见上章提到的源码。<br />(1)MDBQueueBean.java  这是EJB,它不需要其它什么 Home,Remo

  • Java:JEE环境中资源获取的一些方式的说明

    JEE环境中资源获取的一些方式的说明

  • 消息驱动Bean (Message Driven Bean)

    消息驱动Bean(MDB)是设计用来专门处理基于消息请求的组件。它是一个异步的无状态Session Bean,客户端调 用MDB 后无需等待,立刻返回,MDB 将异步处理客户请求。一个MDB 类必须实现MessageListener 接口。当 容器检测到bean 守候的队列一条消息时,就调用onMessage()方法,将消息作为参数传入。MDB 在OnMessage() 中决定如何处理该消息...

  • EJB3图文教程之开发Message Driven Bean

    EJB3图文教程之开发Message Driven Bean

  • EJB-MessageDrivenBean -Queue/Topic 入门案例(亲测OK)

    EJB理解小结-2 ● EJBproject - MDB(Queue And Topic) ① EJB_queue.jar 詳細 ReciveFrom_testQueue01.java代码 package queueDMB; import javax.ejb.ActivationConfigProperty; import javax.ejb.MessageDriven; import ja...

  • jboss下的ejb3中MessageDrivenBean的一个简单案例

         最近在看一本介绍ejb3的新书---《EJB3 in action》, 我觉得还写得可以,如果再结合网上黎活明写的开源文档---《JbossEJB3.0实例教程》一起学习,肯定会达到事半功倍的效果。(这两本书都可以在网上搜到,也可以上得益网下载)。    1.本案例相对简单,但基本用到了ejb3.0的一些基础知识,包括stateless session bean,entity bea

  • SessionBean与MessageDrivenBean

     最近刚初步了解了一下EJB的基本知识。其中EJB有3种:会话Bean,消息驱动Bean以及实体Bean。每种Bean都有自己的特点,先总结前两种 SessionBean(会话bean) 我觉得会话Bean的作用主要是为客户端提供服务。它的分类有两种,有状态和无状态为一种,本地调用和远程调用是一种。有状态与无状态主要是针对EJB,当客户端调用EJB时,无状态每次返回一个新的Sessio

  • JMS与Message-Driven Bean使用总结

    1、JMS是一个由AS提供的Message服务。它能接受消息产生者(Message Provider)所发出的消息,并把消息转发给消息消费者(Message  Consumer)。2、JMS提供2种类型的消息服务:(1)Queue,即点对点,每个消息只转发给一个消息消费者使用。(2)Topic,即发布和订阅,每个消息可以转发给所有的订阅者(消费者)。3、WEBLOGIC 8下的JMS配置:(1)配

  • 消息驱动EJB(一)JMS与EJB

    本文以消息之间的通信为起点介绍如下内容: JMS消息系统中的组件分为: JMS消息生产者:发送消息、不连续 JMS消息消费者:接收消息、一直监听消息 同步:易阻塞、效率低、更可靠(可立即获取异常信息) 异步:不易阻塞、效率高、不可靠 【MDB】属于异步消息消费者。简化开发、被看做是一种特殊的控制器。

  • Java Message Service :Spring POJO (MDP)与EJB3的Java Message Driven (MDB)

    Spring提供了一个用于简化JMS API使用的抽象框架,并且对用户屏蔽了JMS API中1.0.2和1.1版本的差异。 JMS的功能大致上分为两块,叫做消息制造和消息消耗。JmsTemplate 用于制造消息和同步消息接收。和Java EE的事件驱动Bean风格类似,对于异步接收消息,Spring提供了一些消息侦听容器来创建消息驱动的POJO(MDP)。 ...

Global site tag (gtag.js) - Google Analytics