- 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)