为了账号安全,请及时绑定邮箱和手机立即绑定

SQL性能优化-查询条件与字段分开执行,union代替in与or,存储过程代替union

标签:
SQL Server

PS:概要、背景、结语都是日常“装X”,可以跳过直接看优化历程

环境:SQL Server 2008 R2、阿里云RDS;辅助工具:SQL 审计

概要

  一个订单列表分页查询功能,单从SQL性能来讲,从几十万数据量时,适当加一些索引随便写SQL;到百来万数据量时,需要做一些SQL语句优化;再到几百万上千万的数据量情况下,很多意想不到的情况就出现了(在大部分中小公司没有专业DBA的情况下,“万能”的研发就得顶上去了)。

 

背景

  进入公司后,系统已经初具规模,已有成型的框架。随着业务的不断增长,系统功能的不断增加,一些性能问题开始涌现出来。
  以下订单列表查询页面的优化历程,有幸参与了整个过程,以前做研发或项目管理的时候,整天扑在功能开发上,很难有时间去深入研究一些比较难解决的问题。现在做团队管理,反而有更多的时间让我来思考与总结,以下的每一步优化其实都不是一开始就想到的,而是经过无数次的摸索以及线上测试验证,最终找到合适于现阶段的优化方案。
  本人非专业DBA,目前的岗位是研发管理,园友们如果有更好的解决方案欢迎讨论。当然我们也有想过一些更彻底的解决方案,我会在第五部分进行描述,如:冗余数据、数据库分区、分表、分库等,但受制于研发资源、开发周期等只能搁置。从公司的角度来讲,永远都是用最小的成本去实现最大的价值。

优化历程

  以下所有“执行统计信息”都是在现有的数据量情况下,且所有条件都是页面默认打开的情况下(订单主表大概1千万左右,其他附属表最大1亿左右,以下所有表名、字段名都替换过且有些删减)。统计信息限于篇幅,只贴出了执行时间,具体分析用的扫描次数、逻辑读次数就没贴了,时间只是其中一个参考值,扫描次数,逻辑读次数也是很重要的参考值。


  (一)、一条SQL语句实现查询条件,返回字段
    1、查询总记录数:

复制代码

    具体SQL如下:    select sum(a) from (select 1 a 
        from (            SELECT DISTINCT *,
                    (                        SELECT '@#@#@' + rtrim (pd.Remark) FROM dbo.wf_pda pd 
                        WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2 FOR XML PATH('')
                    ) AS BackReason                    FROM 
                    (SELECT tpo.OrderNo,emark.Remark,finance.a,sup.b,ph.c,ph.ProcessID,re.d,tpro.e                    FROM dbo.tp_orderMain tpo 
                        INNER JOIN dbo.tp_remark remark ON tpo.OrderNo = remark.OrderNo                        INNER JOIN dbo.tp_finance finance ON tpo.OrderNo = finance.OrderNo                        INNER JOIN dbo.tp_tpro tpro ON tpo.OrderNo = tpro.OrderNo                        LEFT JOIN dbo.wf_reph ph ON tpo.OrderNo=ph.WareCode                        LEFT JOIN dbo.tp_re re ON tpo.OrderNo=re.OrderNo                        LEFT JOIN dbo.tp_sup sup ON tpo.OrderNo = sup.OrderNo                        INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                        WHERE tpo.OpDate BETWEEN '2018-06-28' AND '2018-07-06'             
                ) AS temp 
        ) as temps where 1=1            
    ) p

    执行统计信息:
    SQL Server parse and compile time: 
       CPU time = 421 ms, elapsed time = 439 ms.

     SQL Server Execution Times:
       CPU time = 71621 ms,  elapsed time = 111959 ms.

复制代码

    2、查询第一页订单信息

复制代码

    具体SQL如下:    SELECT * FROM (        select ROW_NUMBER() Over(ORDER BY temps.OpDate) as RowId,*
            from (        
                SELECT DISTINCT *,
                        (                            SELECT '@#@#@' + rtrim (pd.Remark) FROM dbo.wf_pda pd 
                            WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2 FOR XML PATH('')
                        ) AS BackReason                        FROM 
                        (SELECT tpo.OrderNo,emark.Remark,finance.a,sup.b,ph.c,ph.ProcessID,re.d,tpro.e                    FROM dbo.tp_orderMain tpo 
                            INNER JOIN dbo.tp_remark remark ON tpo.OrderNo = remark.OrderNo                            INNER JOIN dbo.tp_finance finance ON tpo.OrderNo = finance.OrderNo                            INNER JOIN dbo.tp_tpro tpro ON tpo.OrderNo = tpro.OrderNo                            LEFT JOIN dbo.wf_reph ph ON tpo.OrderNo=ph.WareCode                            LEFT JOIN dbo.tp_re re ON tpo.OrderNo=re.OrderNo                            LEFT JOIN dbo.tp_sup sup ON tpo.OrderNo = sup.OrderNo                            INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                            WHERE tpo.OpDate BETWEEN '2018-06-28' AND '2018-07-06'             
                    ) AS temp 
                ) as temps where 1=1
        ) as temp_table_temp where RowId between 1 and 10
    
  执行统计信息:
    SQL Server parse and compile time: 
       CPU time = 1092 ms, elapsed time = 1122 ms.

     SQL Server Execution Times:
       CPU time = 36223 ms,  elapsed time = 45982 ms.

复制代码

    小结:

      在最开始的时候,很常见的写法就是一条SQL、加一些合适的索引,就实现所有功能,在数据量小的情况其实是最优的,甚至索引都是越少越好,因为索引越多插入更新的速度会更慢。但在现有的数据量的情况下,已经完全无法接受了,我们抛开编译时间,查询总记录数耗时111.9秒,查询第一页耗时45.9秒。
      这是我们3年前的实现方式,当时也是因为数据量的增长,查询慢,通过不断的测试,最后有了第二部分。

 

  (二)、SQL拆分:先查询满足条件的订单号、再根据订单号查询数据(in)

    1、查询总记录数:

复制代码

    具体SQL如下:    select sum(a) from (select 1 a 
        from (            SELECT tpo.OrderNo,tpo.OpDate                    FROM dbo.tp_orderMain tpo 
                    INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                WHERE tpo.OpDate BETWEEN '2018-06-28' AND '2018-07-06'
        ) as temps where 1=1            
    ) p    
  执行统计信息:
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 27 ms.

     SQL Server Execution Times:
       CPU time = 31 ms,  elapsed time = 124 ms.

复制代码

    2、查询第一页订单号

复制代码

    具体SQL如下:    SELECT * FROM (        select ROW_NUMBER() Over(ORDER BY temps.OpDate) as RowId,*
            from (                SELECT tpo.OrderNo,tpo.OpDate                        FROM dbo.tp_orderMain tpo 
                        INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                    WHERE tpo.OpDate BETWEEN '2018-06-28' AND '2018-07-06'
            ) as temps where 1=1        
        ) as temp_table_temp where RowId between 1 and 10
    
  执行统计信息:
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 4 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

复制代码

    3、根据订单号查询信息

复制代码

    具体SQL如下:    SELECT *,
        (            SELECT '@#@#@' + rtrim (pd.Remark) FROM dbo.wf_pda pd 
            WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2 FOR XML PATH('')
        ) AS BackReason        FROM 
        (SELECT tpo.OrderNo,emark.Remark,finance.a,sup.b,ph.c,ph.ProcessID,re.d,tpro.e                    FROM dbo.tp_orderMain tpo 
            INNER JOIN dbo.tp_remark remark ON tpo.OrderNo = remark.OrderNo            INNER JOIN dbo.tp_finance finance ON tpo.OrderNo = finance.OrderNo            INNER JOIN dbo.tp_tpro tpro ON tpo.OrderNo = tpro.OrderNo            LEFT JOIN dbo.wf_reph ph ON tpo.OrderNo=ph.WareCode            LEFT JOIN dbo.tp_re re ON tpo.OrderNo=re.OrderNo            LEFT JOIN dbo.tp_sup sup ON tpo.OrderNo = sup.OrderNo            INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                where tpo.OrderNo IN ('10001','10002','10003','10004','10005','10006','10007','10008','10009')  
        ) AS temp 
        ORDER BY temp.OpDate DESC
    执行统计信息:
    SQL Server parse and compile time: 
       CPU time = 46 ms, elapsed time = 59 ms.    Table 'tp_orderMain '. Scan count 16, logical reads 617278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.    Table 'tp_re'. Scan count 8, logical reads 37312, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     SQL Server Execution Times:
       CPU time = 2044 ms,  elapsed time = 2075 ms.

复制代码

    小结:

      从上面的结果可以看出来,获取订单总记录数、获取第一页的订单信息总时间在150毫秒左右就可以查出来,不过在根据订单号in查询订单信息的时候,发现耗时2秒。细心的园友应该也发现我在这个“统计信息”里面多复制了一些东西,有两个表的扫描次数、逻辑读次数都不正常,其中主表617278次,导致总耗时2秒。但这条语句最特殊的是不同的订单号执行结果完全不一样,我开始以为是订单号跨度越大越慢(我的猜测依据是订单号是主键且升序排列),但在我测试的过程中发现完全没有规律,甚至在我查8条记录的情况下,耗时2秒(执行多次结果一致),我再随便找2个17年的订单号一起查询,结果可以几十毫秒出来。我们通过SQL审计发现至少有一半以上该语句执行时间在1-2秒,甚至有一些达到3-4秒,不稳定(一直没有找到原因,现在只是找了替代方法满足了稳定了性能,参考第三,第四部分)。
      这种写法是3年前我们优化的结果,在当时的执行统计信息不是今天这样的结果,基本可以在几百毫秒完成查询,运行到前段时间基本也没有用户再抱怨此功能慢的问题。但最近又开始陆续有用户抱怨这里慢的问题,我们通过SQL审计发现3年前很“优秀”的SQL,现在不灵了,性能不稳定。
      下面先分析一下这种写法:
        1、可以看出来,把显示字段跟查询条件分开后,查询的时候关联的表大大减少,大大提高的索引查询、分页查询的速度;
        2、而在需要关联多个表查询显示字段时,已经是聚焦索引查找了,在正常情况下基本可以毫秒级的完成;

 

  (三)、用union代替in与or

     查询总记录数、第一页的订单号跟第二部分是一样的,没变。

    3、根据订单号查询信息

复制代码

  具体SQL如下(PS:因为SQL但长,只列了前2条):    SELECT *,
        (            SELECT '@#@#@' + rtrim (pd.Remark) FROM dbo.wf_pda pd 
            WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2 FOR XML PATH('')
        ) AS BackReason        FROM 
        (SELECT tpo.OrderNo,emark.Remark,finance.a,sup.b,ph.c,ph.ProcessID,re.d,tpro.e                FROM dbo.tp_orderMain tpo 
            INNER JOIN dbo.tp_remark remark ON tpo.OrderNo = remark.OrderNo            INNER JOIN dbo.tp_finance finance ON tpo.OrderNo = finance.OrderNo            INNER JOIN dbo.tp_tpro tpro ON tpo.OrderNo = tpro.OrderNo            LEFT JOIN dbo.wf_reph ph ON tpo.OrderNo=ph.WareCode            LEFT JOIN dbo.tp_re re ON tpo.OrderNo=re.OrderNo            LEFT JOIN dbo.tp_sup sup ON tpo.OrderNo = sup.OrderNo            INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                where tpo.OrderNo = '10002'
        ) AS temp 
    UNION
    SELECT *,
        (            SELECT '@#@#@' + rtrim (pd.Remark) FROM dbo.wf_pda pd 
            WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2 FOR XML PATH('')
        ) AS BackReason  --订单回滚原因
        FROM 
        (SELECT tpo.OrderNo,emark.Remark,finance.a,sup.b,ph.c,ph.ProcessID,re.d,tpro.e                FROM dbo.tp_orderMain tpo 
            INNER JOIN dbo.tp_remark remark ON tpo.OrderNo = remark.OrderNo            INNER JOIN dbo.tp_finance finance ON tpo.OrderNo = finance.OrderNo            INNER JOIN dbo.tp_tpro tpro ON tpo.OrderNo = tpro.OrderNo            LEFT JOIN dbo.wf_reph ph ON tpo.OrderNo=ph.WareCode            LEFT JOIN dbo.tp_re re ON tpo.OrderNo=re.OrderNo            LEFT JOIN dbo.tp_sup sup ON tpo.OrderNo = sup.OrderNo            INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                where tpo.OrderNo = '10003'
        ) AS temp 
    
  执行统计信息:
    SQL Server parse and compile time: 
       CPU time = 967 ms, elapsed time = 1023 ms.

     SQL Server Execution Times:
       CPU time = 32 ms,  elapsed time = 22 ms.

复制代码

    小结:

      在实验了很多种写法,临时表,表变量,Wtih等后,还是没法稳定性能,在慢的那几个订单号面前,依旧兵败如山倒。当然我们也有想过可能是索引出了问题,需要重建或碎片整理,但对于投产的库,没有十足的把握,以及成熟的方案情况下,不敢去实施。
      一个偶然的机会,我拿一个订单号做实验发现很快,于是想到了union,动手测试发现每一条不同SQL第一次的编译时间为1秒左右,执行时间只有22毫秒。真实情况下,每一次的订单号都不一样的,执行总时间基本都会在1.1秒左右,超出我们的期望值,且SQL语句太长,不直观。这个方案其实是没有在我们的生产环境最终实施的,只是一个中间方案,不过它给了我一个方向,单订单号的情况执行性能很稳定,那现在唯一要解决的就是编译时间的问题,要减少编译时间那基本就想到存储过程了。

 

  (四)、用存储过程代替union

    存储过程创建:

复制代码

    CREATE PROCEDURE [dbo].[pro_OrderList_Select]
             @OrderNo VARCHAR(50)         AS
         BEGIN 
            SELECT *,
            (                SELECT '@#@#@' + rtrim (pd.Remark) FROM dbo.wf_pda pd 
                WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2 FOR XML PATH('')
            ) AS BackReason            FROM 
            (SELECT tpo.OrderNo,emark.Remark,finance.a,sup.b,ph.c,ph.ProcessID,re.d,tpro.e                    FROM dbo.tp_orderMain tpo 
                INNER JOIN dbo.tp_remark remark ON tpo.OrderNo = remark.OrderNo                INNER JOIN dbo.tp_finance finance ON tpo.OrderNo = finance.OrderNo                INNER JOIN dbo.tp_tpro tpro ON tpo.OrderNo = tpro.OrderNo                LEFT JOIN dbo.wf_reph ph ON tpo.OrderNo=ph.WareCode                LEFT JOIN dbo.tp_re re ON tpo.OrderNo=re.OrderNo                LEFT JOIN dbo.tp_sup sup ON tpo.OrderNo = sup.OrderNo                INNER JOIN dbo.com_juris AS juris ON juris.UserId = '200000' AND juris.CompanyId = tpo.ClientCode 
                where tpo.OrderNo = @OrderNo                
              ) AS temp 
        END
    GO

复制代码

    3、根据订单号查询信息

复制代码

    具体SQL如下(执行,使用了表变量):    DECLARE @temp_table_pro_OrderList_Select TABLE(
        OrderNo NVARCHAR(50),
        字段定义,略,
        BackReason NVARCHAR(MAX)
    )    INSERT INTO @temp_table_pro_OrderList_Select EXEC pro_OrderList_Select @OrderNo = '10002'
    INSERT INTO @temp_table_pro_OrderList_Select EXEC pro_OrderList_Select @OrderNo = '10003'
    SELECT * FROM @temp_table_pro_OrderList_Select
    
  执行统计信息:
    由于是分开执行,执行计划太多,我只列其中第四部分有问题的那个表的信息,执行总时间通过客户端统计信息查看,平均在100毫秒左右。    Table 'tp_orderMain'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.    Table 'tp_re'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

复制代码

    小结:

      存储过程的最大优点就是它是预编译的,编译时间短,我们这次也是充分利于它的优点。把单条订单信息查询创建一个存储过程,然后定义一个表变量,把从存储过程中查询的信息插入表变量中,再查询表变量。总执行时间基本稳定在100毫秒左右,总算是解决了问题。
      接下来我们来说说存储过程的缺点:维护成本高,版本控制不方便,并且对于数据库来说,要尽量减少业务逻辑,因为对于增加程序服务器是很容易的,增加一台服务器做负载均衡即可。但数据库虽然你可以加很多从库,但在没有分库策略的情况下,主库还是只有一个,所以我们的代码规范里面有一条就是禁止使用存储过程。通过跟架构师讨论,最后决定为了解决性能问题,放开了使用存储过程的限制,但只允许用于此类列表查询用,其他功能依旧不允许使用存储过程。

 

  (五)、更高一层次的,冗余数据,分区,分表,分库

    冗余数据:针对列表显示字段,涉及一对多关系的表,使用冗余字段保存起来;
    分区:分区需要对每条SQL进行特定优化,要保证大部分查询都在一个分区内解决,不然可能比没分区之前更慢。
    分表,分库,跟分区类似,只是更彻底,对于一个已经成熟且规模庞大的系统,无论是风险还是工作量,都是巨大的,相当于小重构。
    当然,未来随着业务量的增长,可能有一天会去做这件事情,不过可能那时会是整个系统的重构,因为一些历史遗留问题,系统拆分不合理,这个订单处理系统已经不堪负重了。

 

结语

  任何一个系统的完善都不是一蹴而就的, 以上优化其实历时3年,都是在系统运行过程中,业务量的不断增长,性能问题开始突显,以及对系统要求的不断提高,而驱动研发去不断的优化。每个阶段优化的方案也有所不同,最好的不一定是最优的,找合适系统现阶段发展的才是最优的。这是一个系统不断优化的过程,也是整个团队能力不断提升的过程。

欢迎转载,请标明出处,谢谢!

原文出处

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消