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

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如下（使用.net的DataSet获取多行数据）

　　　　EXEC pro_OrderList_Select @OrderNo = '10002'
　　　　EXEC pro_OrderList_Select @OrderNo = '10003'
    
　　执行统计信息：
    由于是分开执行，执行计划太多，我只列其中第四部分有问题的那个表的信息，执行总时间通过客户端统计信息查看，平均在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年，都是在系统运行过程中，业务量的不断增长，性能问题开始突显，以及对系统要求的不断提高，而驱动研发去不断的优化。每个阶段优化的方案也有所不同，最好的不一定是最优的，找合适系统现阶段发展的才是最优的。这是一个系统不断优化的过程，也是整个团队能力不断提升的过程。

posted @ 2018-07-06 17:06 钛氢硼阅读(1391) 评论(2) 编辑收藏举报

刷新页面返回顶部

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

公告