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 (select1 a
from (
SELECT DISTINCT*,
(
SELECT'@#@#@'+rtrim (pd.Remark) FROM dbo.wf_pda pd
WHERE pd.ProcessID=temp.ProcessID AND pd.ActID=-2FOR 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_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 where1=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=-2FOR 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_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 where1=1
) as temp_table_temp where RowId between1and10
执⾏统计信息：
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 (select1 a
from (
SELECT tpo.OrderNo,tpo.OpDate
FROM dbo.tp_orderMain tpo
INNER JOIN dbo_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 where1=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_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 where1=1
) as temp_table_temp where RowId between1and10
执⾏统计信息：
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=-2FOR 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
sql语句优化方式INNER JOIN dbo_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 count16, 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 count8, 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=-2FOR 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_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=-2FOR 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_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=-2FOR 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_juris AS juris ON juris.UserId ='200000'AND juris.CompanyId = tpo.ClientCode
where tpo.OrderNo =@OrderNo
) AS temp
END
GO
3、根据订单号查询信息
具体SQL如下（使⽤的DataSet获取多⾏数据）
EXEC pro_OrderList_Select @OrderNo='10002'
EXEC pro_OrderList_Select @OrderNo='10003'
执⾏统计信息：
由于是分开执⾏，执⾏计划太多，我只列其中第四部分有问题的那个表的信息，执⾏总时间通过客户端统计信息查看，平均在100毫秒左右。
Table'tp_orderMain'. Scan count2, 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 count0, 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年，都是在系统运⾏过程中，业务量的不断增长，性能问题开始突显，以及对系统要求的不断提⾼，⽽驱动研发去不断的优化。每个阶段优化的⽅案也有所不同，最好的不⼀定是最优的，合适系统现阶段发展的才是最优的。这是⼀个系统不断优化的过程，也是整个团队能⼒不断提升的过程。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论