由一条语句优化引发的思考,该怎么处理

2012-06-10

由一条语句优化引发的思考SQL code/******************************************************************

由一条语句优化引发的思考

SQL code

/***************************************************************************/------------------------------------------- Title   :由一条语句优化引发的思考-- Author  :小爱-- Date    :2012-03-30 19:06:10-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) --    Apr  2 2010 15:48:46 --    Copyright (c) Microsoft Corporation--    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )-----------------------------------------/***************************************************************************/--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([id] int,[num] int,[s] int)insert [tb]select 1,2,5 union allselect 1,2,5 union allselect 1,2,5 union allselect 1,3,5 union allselect 2,2,5 union allselect 2,3,5 union allselect 3,4,5---直接上结果，描述起来一时半会说不清/*id          cnt         sums----------- ----------- -----------1           2           202           2           103           1           5*/-------------------------------------------下面的语句是原著--1set statistics io on select id,count([num])as cnt ,sum([s]) as sumsfrom( select id,[num],sum([s]) as [s] from [tb] group by [id],[num]) tgroup by id--当本人目测良久只后写下了下面的语句--2select id,count(distinct [num]) as cnt,sum([s])as sums from tbgroup by id--当我自认为自己很牛逼哄哄，写下了如此简练的语句--原著也觉得我是个人才，佩服的不得了的时候--我按下了Ctrl+L--令我大吃一惊的事情发生了--方法2的开销要比1 大好多--在看IO，omg的，2比1大--这让我情何以堪啊--回头认真思考问题所在，难道是 count(distinct[num]) 这一步的问题？--好吧，我去掉了distinct 关键字，ps:暂不讨论结果是否一致--果然这次从各方面看问题2比1 都更优越，但这结果都不一样啊，--问题：count(distinct col)  使用distinct关键字 效率会很低？--先不要往下定论，看下面的，我去掉sum操作select id,count([num])as cntfrom( select id,[num] from [tb] group by [id],[num]) tgroup by id--2select id,count(distinct [num]) as cnt from tbgroup by id--这次又发现,这两个语句的执行计划一模一样,这又是为什么呢？--这个时候我又想到了索引alter table tb add ix int identity(1,1) primary key--在执行上面的语句--你们猜有没有走索引路线呢？这又是为什么呢？--到这里简单的总结一下-----------------------------------------我隔------------------------------------------1、不是最简洁的语句效率就最好--2、不是所有的子查询都慢--3、没有全面的测试不要妄下结论,如果我只认为distinct关键字会影响效率那么,那么那么...--4、性能调优的道路还很漫长----------------------------------------我再隔------------------------------------------最后告诉大家一个小秘密--1、在线上90w的表中，这两个语句的查询开销和用时差异并不大，当然前者更优异--2、表一定要有个主键，否则普通索引的利用是很差的，除非是极少用来查询的表。

[解决办法]
通过两句对比，发现
第一句写法虽繁，但执行逻辑简单，明确
第二句写法虽简，但执行逻辑繁，并且逻辑的解析完全交给MSSQL了，可MSSQL有时也不争气，硬是解析成两个中间子表然后再连接查询的方式，如果再增加这样的列，同样出现三个四个。。这样的中间子表然后再连接查询，可谓费时又费力。

看来调优往往不在于写法的繁简，而是在于逻辑的简洁明了
况且MSSQL帮我们所做的解析未必是最好的
[解决办法]
我也随便写点东西来骗点分,反正有5000,随便分点也有几百了.不对误喷

如果只是要消除重复行(不牵涉到聚合运算),distinct效率要比group by高(msdn可查,有兴趣的自己去搜索)
所以我看到例子的第一个感觉是应该第二个更快点(不考虑后面的索引,楼主说话也不爽快,什么都说一半,我颇为不爽,要不就是我理解力太差了,这里小喷一下,请大家忽略)

distinct和group by的差别是distinct不需要排序(在没有group by的情况下),但楼主现在的写法是既有group by又是distinct了,这样的情况下和第一句比起来,distinct就成为负担了,需要再有一个临时表的生成去排序,然后再过滤.以上是没有考虑索引的情况下

如果有聚集索引(一定是聚集索引)的情况下,执行引擎就跳过生成临时表的过程,直接通过聚集索引产生distinct的效果,这样一来效果就会大大的提升,所以效果和第一句就接近了(楼主是这个意思吧,别说话老是说一般让别人猜)

所以总的来说,我个人是不同意distinct效率比group by低云云的说法,两者的原理相近,但是对索引的利用,排序的规则上还是有很大的区别的.
[解决办法]
为何不把索引建在id,num 上呢。我建了一下。执行计划就大大的不同了

SQL code

select id,count([num])as cnt ,sum([s]) as sumsfrom( select id,[num],sum([s]) as [s] from [tb] group by [id],[num]) tgroup by id--当本人目测良久只后写下了下面的语句--2(1 行受影响)StmtText-----------------------------------------------------------------------------------------  |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1021],0), [Expr1006]=CASE WHEN [Expr1022]=(0) THEN NULL ELSE [Expr1023] END))       |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[tb].[id]) DEFINE:([Expr1021]=COUNT([tempdb].[dbo].[tb].[num]), [Expr1022]=COUNT_BIG([Expr1004]), [Expr1023]=SUM([Expr1004])))            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END))                 |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[tb].[id], [tempdb].[dbo].[tb].[num]) DEFINE:([Expr1019]=COUNT_BIG([tempdb].[dbo].[tb].[s]), [Expr1020]=SUM([tempdb].[dbo].[tb].[s])))                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[tb].[cidx_tbidnum]), ORDERED FORWARD)(5 行受影响)StmtText-----------------------------------select id,count([num] ) as cnt,sum([s])as sums from tbgroup by id(1 行受影响)StmtText-------------------------------------------------------------------------------------------------  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1013],0), [Expr1005]=CASE WHEN [Expr1014]=(0) THEN NULL ELSE [Expr1015] END))       |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[tb].[id]) DEFINE:([Expr1013]=COUNT([tempdb].[dbo].[tb].[num]), [Expr1014]=COUNT_BIG([tempdb].[dbo].[tb].[s]), [Expr1015]=SUM([tempdb].[dbo].[tb].[s])))            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[tb].[cidx_tbidnum]), ORDERED FORWARD) 
 
[解决办法]
不是所有的子查询都慢？

子查询并不一定慢，
但相关子查询往往会很慢，因为相关子查询在每条记录都会执行一次，
而你上面的子查询，不属于相关子查询，只执行一次，
当然就不会存在慢的问题了。

而且这种子查询可以很大程度提高效率，
为什么呢？
因为在子查询里执行了group by，记录数会大大减少，外面统计时就少统计很多记录，
我以前经常用这种方式来提高查询效率。
比如假设原始记录有1万条，我执行group by后，就只剩下100条，然后再对这100条汇总查询，
速度肯定快得多。
但相关子查询尽量少用，有时会非常慢，特别是大表。当然，也有另外，比如小表主键查询。

distinct也属于一种子查询，他的效果相当于group by。
通常，消除重复行的方法，要么用group by，要么用distinct，
所以两者并不存在效率谁低谁高的问题。

问题关键还是在于你第二条语句又distinct，又sum，复杂度是相乘的。
而第一条语句，复杂度是相加的。
所以第二条比第一条更复杂。
第二条又有汇总，又有消除单值，复杂度远远超过第一条。

打个比方，一个程序员，同时又肩负很多别的工作，又要调研，又要写需求。
调研时又要写程序，写程序时又要维护，结果几项工作同时做，程序员就崩溃了，
但如果他能分好顺序，然后按轻重缓急，合理安排，就不会那么忙乱了。

写sql语句也是如此。

热点排行