数据库优化打怪升级

　　最近呢，总是有人反馈公司的一个系统页面反应太慢，一个列表展示，竟然需要二三十秒，我上去点了点，发现没二三十秒那么夸张，但是也是够慢了，龟速，直接影响工作效率啊。所以从这星期开始，我便硬着头皮上优化系统了。

　　这个系统使用MVC3+Easy UI做的，数据库用的是SQL Server 2012。优化呢，首先就是定位原因，刚开始呢，只能按传统方法来，想想瓶颈在哪，网络传输，程序响应和页面渲染，磁盘IO。前两个我是没法控制了，只有想想最后的法子了，从数据库入手了。

　　

　　上图就是执行页面很缓慢的界面。首先监控到其sql语句，因为手里有源代码，直接就用程序调试获取了，不用 SQL Server Profiler。拿到sql语句后，就开始对sql进行分析，看看哪里能进行优化。一条真理：尽可能的避免全表扫描。

SELECT TOP 10 *    FROM 
(     
   SELECT ROW_NUMBER() OVER (ORDER BY CREATEDATE DESC) AS RowNumber,b.* FROM 
   ( 
       SELECT     WO.WorkOrderCode,WO.WorkOrderType,WO.Status,WO.CustName,WO.CustTel1,WO.CustTel2,WO.CustIdentityNo,WO.CardId,WO.CustSex,WO.CustAge,WO.CustBirthday, WO.IsMember,WO.AccountLevel,WO.MemberDate,WO.AccountScore,WO.DestTarget,WO.DestTargetDesc,WO.Source,WO.ToType,WO.Dept,WO.HotelId,WO.HotelName, WO.HotelCreateDate,WO.HotelType,WO.HotelBrand,WO.HotelTel,WO.HotelBigArea,WO.HotelArea,WO.HotelCityGroup,'' AS HotelCity,WO.Title,WO.SelectTitle,WO.Content,WO.IsAccept,WO.CompensateDept, WO.CompensateAmountType,WO.CompensateAmount,WO.ReferDept,WO.ResponsiblePerson,W2.Attribute3 AS ResponsiblePersonName,WO.ResponsibleDate,WO.ClosePerson, WO.CloseDate,WO.CatePerson,WO.CateDate,WO.ScorePerson,WO.ScoreDate,WO.CreateUser,WO.CreateDate,WO.UpdateUser,WO.UpdateDate,W1.Attribute3 AS UserName   FROM WorkOrder WO WITH (NOLOCK)  LEFT JOIN WSCUser W1 ON WO.CreateUser=W1.UserID  LEFT JOIN WSCUser W2 ON WO.ResponsiblePerson=W2.UserID 
       WHERE 1=1  AND (WO.Title LIKE '%其他%' OR WO.SelectTitle LIKE '%其他%')  AND WO.CreateDate BETWEEN '2017-09-03 00:00:00' AND '2017-09-30 23:59:59' 
　　　 　AND WO.STATUS=3 AND W2.Attribute3 ='张三'  AND WO.WorkOrderType='T' AND WO.IsDelete=0                                                                                       
    ) b   
) a  WHERE RowNumber > 0 order by rownumber

 　　1. 从里往外看吧，最里层的子查询，是3个表的左连接查询，这个地方可以采用的方案有两种：

　　　　　　　　①在连接字段WO表的字段CreateUser上建立索引，②把这个联合查询做成视图（此法效果不好）。

　 　2.之后再看where后边，

　　　　　　　　①Title中用了like查询，可以改成=，但是标题那个地方要求有模糊搜索功能（此法不可取）。

　　　　　　　　②对于status（三种状态），workordertype（四种状态），IsDelete（两种状态）这些建立索引，但是索引建立之后，效果很差，因为对于这种区别度很小的字　段，建立索引后，查询时还会增加 KeyLookup（标签查找 ），性能并不一定会提升（此法效果不好）。

　　　　　　　　③对于这种条件不固定的查询，基本上可以把多列索引这种方案给排除了，查询出来二三十个字段，也可以把覆盖索引给排除了（此法不可取）。

　　　　　　　　④最后只能选择在CreateDate字段上建立索引了（此法可取）。

      　3.对于分页算法，分页算法有好几种，经过考虑，这种用ROW_NUMBER()的方法效率还可以。所以说换一个分页算法，需要改大量程序代码，这种方法也是不可取的。

　　  综上所述，选择的方法只有是在CreateDate上建立索引了。

　 　 ④之后还有一部操作，如下图所失，将索引重新组织一下，因为时间长了，碎片比较多。

   

注意：进行重新组织索引时，一定不要在程序访问量大时进行，因为会造成锁表，系统直接无法使用。这个过程是很有风险的，数据量太大，执行也是需要一定时间的。

　　　　本来也为好了，可是故事未完待续，我把这段sql在数据库中执行，发现用时只有0.3s左右，但是系统中能看到结果，还需要1.4s左右。我刚开始还以为是系统代码太烂，EasyUI框架太重的原因。又调试了一会儿才发现是，程序执行了分页语句之后，又执行了一段算数据总数的sql，原来是count()惹的货。

 

select count(*) from 
(
      SELECT   WO.WorkOrderCode,WO.WorkOrderType,WO.Status,WO.CustName,WO.CustTel1,WO.CustTel2,WO.CustIdentityNo,WO.CardId,WO.CustSex,WO.CustAge,WO.CustBirthday, WO.IsMember,WO.AccountLevel,WO.MemberDate,WO.AccountScore,WO.DestTarget,WO.DestTargetDesc,WO.Source,WO.ToType,WO.Dept,WO.HotelId,WO.HotelName, WO.HotelCreateDate,WO.HotelType,WO.HotelBrand,WO.HotelTel,WO.HotelBigArea,WO.HotelArea,WO.HotelCityGroup,'' AS HotelCity,WO.Title,WO.SelectTitle,WO.Content,WO.IsAccept,WO.CompensateDept, WO.CompensateAmountType,WO.CompensateAmount,WO.ReferDept,WO.ResponsiblePerson,W2.Attribute3 AS ResponsiblePersonName,WO.ResponsibleDate,WO.ClosePerson, WO.CloseDate,WO.CatePerson,WO.CateDate,WO.ScorePerson,WO.ScoreDate,WO.CreateUser,WO.CreateDate,WO.UpdateUser,WO.UpdateDate,W1.Attribute3 AS UserName   FROM WorkOrder WO WITH (NOLOCK)  LEFT JOIN WSCUser W1 ON WO.CreateUser=W1.UserID  LEFT JOIN WSCUser W2 ON WO.ResponsiblePerson=W2.UserID 
       WHERE 1=1  AND (WO.Title LIKE '%其他%' OR WO.SelectTitle LIKE '%其他%')  AND WO.CreateDate BETWEEN '2016-05-03 00:00:00' AND '2017-09-30 23:59:59' 
　　　 　AND WO.STATUS=3    AND WO.WorkOrderType='T' AND WO.IsDelete=0     
) a

 上图就是算总数据量的语句，这个语句的优化可以这样。

　　1.count（*）可以改成count（WorkOrderCode），WorkOrderCode是WorkOrder的主键，经过测试执行效率要快于count(*)。--详情请见

　　2.将查询字段全部去掉。

　　优化完成之后，语句变成这样。

SELECT count(WorkOrderCode) FROM WorkOrder WO WITH (NOLOCK)  LEFT JOIN WSCUser W1 ON WO.CreateUser=W1.UserID  LEFT JOIN WSCUser W2 ON WO.ResponsiblePerson=W2.UserID 
       WHERE 1=1  AND (WO.Title LIKE '%其他%' OR WO.SelectTitle LIKE '%其他%')  AND WO.CreateDate BETWEEN '2016-05-03 00:00:00' AND '2017-09-30 23:59:59' 
　　　 　AND WO.STATUS=3    AND WO.WorkOrderType='T' AND WO.IsDelete=0     

 　　到这里，优化算是正式结束了，搞了三四天。在这过程中也对数据库的索引，语句优化有了更加深刻的认识。当然有些也颠覆了我以前看到理论，如联合查询建成视图后，查视图效率会提升，但是我做的实验并非如此，视图效率不如联合索引。

　　抛开技术上的因素，还有很多业务上的需要考虑，像刚开始的那个查询条件表单，有些选项可能就很少使用，但刚开始我也考虑了很多种情况，后来，跟客服人员沟通，发现他们有些选项就不怎么用，之后我按照他们给的经常使用的筛选条件，进行优化，也就不那么盲目了，问题也好处理了。通过这件事呢，总结出两条哲理：“干活儿不由东，累死也误工”、“沟通很重要”。

     世事洞明皆学问 人情练达即文章，技术始终是要为人而服务的，所以不要为了技术而技术。