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

Question

/***************************************************************************/
-----------------------------------------------------------------------------
-- 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 all
select 1,2,5 union all
select 1,2,5 union all
select 1,3,5 union all
select 2,2,5 union all
select 2,3,5 union all
select 3,4,5
---直接上结果，描述起来一时半会说不清
/*
id          cnt         sums
----------- ----------- -----------
1           2           20
2           2           10
3           1           5
*/
-----------------------------------------
--下面的语句是原著
--1
set statistics io on 
select id,count([num])as cnt ,sum([s]) as sums
from( 
select id,[num],sum([s]) as [s] from [tb] group by [id],[num]
) t
group by id
--当本人目测良久只后写下了下面的语句
--2
select id,count(distinct [num]) as cnt,sum([s])as sums from tb
group 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 cnt
from( 
select id,[num] from [tb] group by [id],[num]
) t
group by id
--2
select id,count(distinct [num]) as cnt from tb
group by id--这次又发现,这两个语句的执行计划一模一样,这又是为什么呢？
--这个时候我又想到了索引
alter table tb add ix int identity(1,1) primary key
--在执行上面的语句
--你们猜有没有走索引路线呢？这又是为什么呢？
--到这里简单的总结一下
-----------------------------------------我隔----------------------------------------
--1、不是最简洁的语句效率就最好
--2、不是所有的子查询都慢
--3、没有全面的测试不要妄下结论,如果我只认为distinct关键字会影响效率那么,那么那么...
--4、性能调优的道路还很漫长
----------------------------------------我再隔------------------------------------------最后告诉大家一个小秘密
--1、在线上90w的表中，这两个语句的查询开销和用时差异并不大，当然前者更优异
--2、表一定要有个主键，否则普通索引的利用是很差的，除非是极少用来查询的表。

Answer 1

如果是我,肯定用:select id,count(distinct [num]) as cnt , sum(s) s from tb group by id至少第1种用了子查询,在条件一样的情况下(机器配置,索引等)其速度不可能快过第2种.

Answer 2

Good Good Love,Day Day Fuck!
明天上班，表示不爽！

Answer 3

子查询，写的人无法建立、指定索引
不知道sql解释器会不会自动建立、指定恰当的临时索引？
感觉应会，否则多层查询肯定都很慢了

Answer 4

搞明白了一点select id,count(distinct [num]) as cnt,sum([s])as sums from tb
group by idMSSQL给内部解析成两个连接查询(都有开销），一个查询输出的列为[tb].id, [tb].s, 别一个查询输出的列为[tb].id, [tb].num, 然后再将这两个中间的输出表做inner join(也有开销），最后输出的是连接查询后的结果，这些可以在执行计划里看的很清楚原因不在于列 sum([s])， 而在于列 count(distinct [num])
也就是说，随着类似count(distinct [num])的列的个数增加（如count(distinct [num2]))，开销也会增加
但 类似sum([s])的列的个数增加（如sum([s2])，开销的增加可以忽略不计在原有例子上增加一个列可以看到效果if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[num] int, [num2] int,[s] int)
insert [tb]
select 1,2,2,5 union all
select 1,2,2,5 union all
select 1,2,2,5 union all
select 1,3,3,5 union all
select 2,2,2,5 union all
select 2,3,3,5 union all
select 3,4,4,5
-----------------------------------------
--下面的语句是原著
--1
select id,count([num])as cnt ,count([num2])as cnt2 ,sum([s]) as sums
from( 
select id,[num],[num2],sum([s]) as [s] from [tb] group by [id],[num],[num2]
) t
group by id--当本人目测良久只后写下了下面的语句
--2
select id,count(distinct [num]) as cnt,count(distinct [num2]) as cnt2,sum([s])as sums from tb
group by id再按ctrl+L看看， 第二句的开销占比又增加了，但如果增加sum([s2])的话，开销占比不变
同样如果，再增加列num3,num4,...如此count(distinct))查询的话，销售占比直线上升

Answer 5

通过两句对比，发现
第一句写法虽繁，但执行逻辑简单，明确
第二句写法虽简，但执行逻辑繁，并且逻辑的解析完全交给MSSQL了，可MSSQL有时也不争气，硬是解析成两个中间子表然后再连接查询的方式，如果再增加这样的列，同样出现三个四个。。这样的中间子表然后再连接查询，可谓费时又费力。看来调优往往不在于写法的繁简，而是在于逻辑的简洁明了
况且MSSQL帮我们所做的解析未必是最好的

Answer 6

以前测试过DISTINCT的效率 单纯的用SET STATCISTICS TIME ON和SET STATISTICS I/O ON来 测试的，
发现DISTINCT的各种消耗都比较大，至于如何解析的问题，木有去深究总之，测试之后，我比较少用DISTINCT，有时候还宁愿用GROUP BY来代替。

Answer 7

顺便帮爱姐盖个章，大家来讨论一下，现在这样的探索在MSSQL版已经快消失了。

Answer 8

顺便帮爱姐盖个章，大家来讨论一下，现在这样的探索在MSSQL版已经快消失了。

Answer 9

顺便帮爱姐盖个章，大家来讨论一下，现在这样的探索在MSSQL版已经快消失了。 
 

Answer 10

膜拜各位，
多么希望，在oracle里也看到这样的帖子。
很多优化的问题，只能慢慢摸索，摸着石头过河。

Answer 11

顺便帮爱姐盖个章，大家来讨论一下，现在这样的探索在MSSQL版已经快消失了

Answer 12

mysql开源。弄到mysql里看一下。看一下执行的代码。

Answer 13

小爱还记得2年前的今天你经常的回复吗？http://topic.csdn.net/u/20100331/13/443045bc-dbb5-4cef-84c1-629eab0525c2.html

Answer 14

我认为没有聚合函数的时候distinct 和 group by 差不多。
有聚合函数的时候，group by的效率好一些。

Answer 15

网速有问题，收藏不了，Mark一下。

Answer 16

我也随便写点东西来骗点分,反正有5000,随便分点也有几百了.不对误喷如果只是要消除重复行(不牵涉到聚合运算),distinct效率要比group by高(msdn可查,有兴趣的自己去搜索)
所以我看到例子的第一个感觉是应该第二个更快点(不考虑后面的索引,楼主说话也不爽快,什么都说一半,我颇为不爽,要不就是我理解力太差了,这里小喷一下,请大家忽略)distinct和group by的差别是distinct不需要排序(在没有group by的情况下),但楼主现在的写法是既有group by又是distinct了,这样的情况下和第一句比起来,distinct就成为负担了,需要再有一个临时表的生成去排序,然后再过滤.以上是没有考虑索引的情况下如果有聚集索引(一定是聚集索引)的情况下,执行引擎就跳过生成临时表的过程,直接通过聚集索引产生distinct的效果,这样一来效果就会大大的提升,所以效果和第一句就接近了(楼主是这个意思吧,别说话老是说一般让别人猜)所以总的来说,我个人是不同意distinct效率比group by低云云的说法,两者的原理相近,但是对索引的利用,排序的规则上还是有很大的区别的.

Answer 17

子页面不是慢慢就可以被索引的么？
www.healthchinese.org

Answer 18

select id,count(distinct [num]) as cnt , sum(s) s from tb group by id

Answer 19

distinct 取出唯一列，group by 是分组，有时候在优化的时候，在没有聚合函数的时候，他们查出来的结果是一样的。
distinct 的操作是sort(union)，而group by是sort(group by)

Answer 20

为何 不把 索引建在id,num 上呢。我建了一下。执行计划就大大的不同了
select id,count([num])as cnt ,sum([s]) as sums
from( 
select id,[num],sum([s]) as [s] from [tb] group by [id],[num]
) t
group 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 tb
group 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)
不加索引 和 加索引前后 第一句的 执行计划是不变的，但是第二句 变的超简洁。
我用lz的数据量可能看不出来 具体执行时间的区别，但是如果在10W的数据量下相信 第二句的性能更好

Answer 21

收回 第二句sql 让我改了 没注意

Answer 22

DISTINCT比 group by 消耗更多

Answer 23

不是所有的子查询都慢？子查询并不一定慢，
但相关子查询往往会很慢，因为相关子查询在每条记录都会执行一次，
而你上面的子查询，不属于相关子查询，只执行一次，
当然就不会存在慢的问题了。而且这种子查询可以很大程度提高效率，
为什么呢？
因为在子查询里执行了group by，记录数会大大减少，外面统计时就少统计很多记录，
我以前经常用这种方式来提高查询效率。
比如假设原始记录有1万条，我执行group by后，就只剩下100条，然后再对这100条汇总查询，
速度肯定快得多。
但相关子查询尽量少用，有时会非常慢，特别是大表。当然，也有另外，比如小表主键查询。distinct也属于一种子查询，他的效果相当于group by。
通常，消除重复行的方法，要么用group by，要么用distinct，
所以两者并不存在效率谁低谁高的问题。问题关键还是在于你第二条语句又distinct，又sum，复杂度是相乘的。
而第一条语句，复杂度是相加的。
所以第二条比第一条更复杂。
第二条又有汇总，又有消除单值，复杂度远远超过第一条。打个比方，一个程序员，同时又肩负很多别的工作，又要调研，又要写需求。
调研时又要写程序，写程序时又要维护，结果几项工作同时做，程序员就崩溃了，
但如果他能分好顺序，然后按轻重缓急，合理安排，就不会那么忙乱了。写sql语句也是如此。

Answer 24

执行计划，可不是分析哪条语句字数更少。
因为执行计划，会对sql语句预编译为机器码，
然后分析机器码的执行效率。有时，简单的语句，往往生成更复杂的机器码，当然，也不是机器码的问题。

Answer 25

5000分给我吧
原因很简单，sql server在2008版没有很好考虑count(distinct ...)的执行计划优化，在以后版本（不知道哪一个）会解决的。

Answer 26

擦，这都被你挖出来了，其实这里不是 distinct 的问题，也不是group by的问题

Answer 27

这。我是这么认为的：认为优化器没做好选择， COUNT(DISTINCT) 和 SUM 操作, 它是分做两个并行的查询, 一个去做 COUNT, 一个去做 SUM, 最后再 JOIN 两个结果
大概是它认为并行去算这两个结果的时间会列短一些。

Answer 28

这个帖子不错哎。CSDN吸引人的地方就在于此

Answer 29

不考虑2个语句对应聚合函数或子查询的话，
有可能第二句是在group by的基础上count(distinct)对应的计算量大一点。支持楼主，学习........

Answer 30

两个SQL语句的性能差别在于多次读表和排序。
排序在程序实现中是性能优化的不二法宝。谁都不会拒绝。
分组需要排序，DISTINCT也需要排序。第一种写法刚好明确要求先按ID和NUM排序，再按照ID排序。
优化器发现第一次排序后的结果没有被破坏。可以再利用结果进行运算。
由于写法没有让优化器自由发挥的空间。所以SQL只读一次表。排一次序。
第二种写法中模糊的说明了排序原则。
(GROUP BY按ID排序，DISTINCT+GROUP BY 按ID,num排序。)
SUM的存在说明了按ID排序的必要性。
DISTINCT的存在说明了按NUM排序的必要性。
即把排序的选择权交给了MS。MS无法区分排序的重要性。所以就选择平等吧。
平等的代价是读2次表。排序2次。所以简洁的SQL在性能上却输给了繁琐的SQL语句。第二种是否真的输了呢？微软这样做是否存在好处呢？我的想法是：微软信任索引。能利用索引就利用索引。所以就多读表。以上仅为个人遇见。仅供参考。

Answer 31

我只会写基本的SQl语句，表示看不懂，也不明白。

Answer 32

学习!
个人觉得SQL语句确实应该要 逻辑思路 简洁明了
机器和程序都是死的.只要我们人把思路及规则什么的整清楚了  立马执行效率才高
让死的机器去选择思路 效率肯定是下降的
那些什么代码的简单与否都是虚的