• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

关于SELECT 1的效率问题,不要不看查询计划

guguda2008 2013-10-24 11:50:53
加精
刚看到一个讨论SELECT 1,SELECT ID,SELECT *的效率问题的推荐贴,里面竟然没有一个人去看查询计划,还有人以测试结果为准。这里普及一下这个问题的知识点。

会用到SELECT 1的地方只有EXISTS和NOT EXISTS,这两个关键字用到的迭代器是三个LOOP,关联方式为SEMI JOIN也就是半连接,所以无论你在EXISTS里的SELECT后面写什么都不会真正去表里查询,最终输出的只有需要比较的条件。

我简单写三个语句,在master库里执行
select *
from spt_values t1
where exists(
select 1 FROM spt_values T2
WHERE T2.number>T1.number
)

select *
from spt_values t1
where exists(
select t2.low,t2.high FROM spt_values T2
WHERE T2.number>T1.number
)

select *
from spt_values t1
where exists(
select * FROM spt_values T2
WHERE T2.number>T1.number
)

执行计划的图片懒得上传了,大家自己去按CTRL+L执行一下,生成的计划是一模一样的。在下方的index seek里,最终输出的都只有number一列,所以这三种写法在比较条件相同的时候是完全一样的,写1不会生成一列计算列,也不会因为SELECT索引键列走某个索引,也不会因为写*去找系统表读所有列。

所以无论实测结果是什么,本质上测试的都是同一个执行计划,所以效率完全没有差异,我爱用SELECT 1只是因为恶趣味,你也可以写SELECT 'SB'来展示你的恶趣味,对效率完全没影响。当别人很诧异的看你的语句时,你就可以低调而又坚定的告诉他:这里是随便写的,不用管它。如果他质疑你,就拿这篇文章扔他脸。
...全文
2807 点赞 收藏 71
写回复
71 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
guguda2008 2013-11-10
引用 64 楼 zhanwengong 的回复:
[quote=引用 60 楼 guguda2008 的回复:] [quote=引用 58 楼 zhanwengong 的回复:] [quote=引用 57 楼 youbl 的回复:] [quote=引用 55 楼 zhanwengong 的回复:] 大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
如果你的“可空字段名”没创建索引,那么Count的执行计划,就更烂了[/quote] 我测试用的数据库是:Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 测试表:ID为主键,Name为可空,无索引。表数据量为1463372,name中空值量为69853。 测试结果:count(*),count(1),count(ID),count(Name)执行计划是一致的。 不知道你是否自己试验过,愿闻其详。[/quote] 这人说的没错,你这四个计划一样意味着都是最烂的执行计划--聚集索引扫描。 如果你在NAME上建个索引,COUNT(NAME)走的就是索引扫描,计划就会不一样了。[/quote] 没错,不建索引,执行计划都是聚集索引扫描;建立索引后都是索引扫描。 但是结果都一样:这四个语句的执行计划还是完全一致的。[/quote] 请稳步到我的新贴中讨论,专门为你开的 关于COUNT的效率问题,不要不看查询计划
回复
--小F-- 2013-11-09
回复
脸肿了 2013-11-09
谢谢分享!!!1
回复
gogodiy 2013-11-08
guguda研究的比较深,比较实在,
回复
北亮bl 2013-11-08
贴一个我这边不一样的执行计划图片吧
回复
北亮bl 2013-11-08
引用 64 楼 zhanwengong 的回复:
没错,不建索引,执行计划都是聚集索引扫描;建立索引后都是索引扫描。 但是结果都一样:这四个语句的执行计划还是完全一致的。
参考我楼上帖的测试代码,建立索引建在其它字段,不要建在name,就不一样了, 我最之前表述有误,抱歉
回复
北亮bl 2013-11-08
引用 58 楼 zhanwengong 的回复:
[quote=引用 57 楼 youbl 的回复:] [quote=引用 55 楼 zhanwengong 的回复:] 大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
如果你的“可空字段名”没创建索引,那么Count的执行计划,就更烂了[/quote] 我测试用的数据库是:Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 测试表:ID为主键,Name为可空,无索引。表数据量为1463372,name中空值量为69853。 测试结果:count(*),count(1),count(ID),count(Name)执行计划是一致的。 不知道你是否自己试验过,愿闻其详。[/quote] 我是直接用我的实际数据测试的,所以测试出来的结果不太一样, 你的这个情况,确实执行计划都是一样的,都是走聚集索引, 如果你在第3个字段建立一个普通索引,那么count(*),count(1),count(ID)都会走普通索引,count(name)还是走聚集索引 我的测试代码:
CREATE TABLE aa(id INT IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(10), age int)
GO

DECLARE @i INT;
DECLARE @age INT;
SET @i=0;
WHILE @i<10000
BEGIN
  IF @i % 10 = 0
    SET @age = NULL
  else
    SET @age = @i
  INSERT INTO aa (name, age) VALUES(CAST(@i AS VARCHAR), @age)
  SET @i=@i+1
END

GO
CREATE INDEX idx1 ON aa (name)
GO

SELECT COUNT(id) FROM aa
SELECT  COUNT(age) FROM aa


回复
Persistence_x 2013-11-07
mark
回复
zhanwengong 2013-11-07
引用 60 楼 guguda2008 的回复:
[quote=引用 58 楼 zhanwengong 的回复:] [quote=引用 57 楼 youbl 的回复:] [quote=引用 55 楼 zhanwengong 的回复:] 大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
如果你的“可空字段名”没创建索引,那么Count的执行计划,就更烂了[/quote] 我测试用的数据库是:Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 测试表:ID为主键,Name为可空,无索引。表数据量为1463372,name中空值量为69853。 测试结果:count(*),count(1),count(ID),count(Name)执行计划是一致的。 不知道你是否自己试验过,愿闻其详。[/quote] 这人说的没错,你这四个计划一样意味着都是最烂的执行计划--聚集索引扫描。 如果你在NAME上建个索引,COUNT(NAME)走的就是索引扫描,计划就会不一样了。[/quote] 没错,不建索引,执行计划都是聚集索引扫描;建立索引后都是索引扫描。 但是结果都一样:这四个语句的执行计划还是完全一致的。
回复
guguda2008 2013-11-06
引用 53 楼 youbl 的回复:
[quote=引用 52 楼 guguda2008 的回复:] 关于COUNT()的知识就一句话,括号里的值只统计不为NULL的数量
相信多数人,都是不看完整个msdn的,而且有的人连msdn都不看,别人说count是返回记录总数,就这么用了,从来不会去看说明的,所以普及一下,还是有意义的。 下面帖出COUNT的MSDN链接和知识: http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=ZH-CN&k=k(COUNT_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT)&rd=true 注释 -------------------------------------------------------------------------------- COUNT(*) 返回组中的项数。 包括 NULL 值和重复项。 COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。 COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。 对于大于 2^31-1 的返回值,COUNT 生成一个错误。 这时应使用 COUNT_BIG。 [/quote] 关于这个我重开一贴写吧
回复
chuanzhang5687 2013-11-06
引用 楼主 guguda2008 的回复:
如果他质疑你,就拿这篇文章扔他脸
砸他脸上
回复
guguda2008 2013-11-06
引用 58 楼 zhanwengong 的回复:
[quote=引用 57 楼 youbl 的回复:] [quote=引用 55 楼 zhanwengong 的回复:] 大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
如果你的“可空字段名”没创建索引,那么Count的执行计划,就更烂了[/quote] 我测试用的数据库是:Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 测试表:ID为主键,Name为可空,无索引。表数据量为1463372,name中空值量为69853。 测试结果:count(*),count(1),count(ID),count(Name)执行计划是一致的。 不知道你是否自己试验过,愿闻其详。[/quote] 这人说的没错,你这四个计划一样意味着都是最烂的执行计划--聚集索引扫描。 如果你在NAME上建个索引,COUNT(NAME)走的就是索引扫描,计划就会不一样了。
回复
fuyu6457 2013-11-06
学习了
回复
zhanwengong 2013-11-06
引用 57 楼 youbl 的回复:
[quote=引用 55 楼 zhanwengong 的回复:] 大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
如果你的“可空字段名”没创建索引,那么Count的执行计划,就更烂了[/quote] 我测试用的数据库是:Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 测试表:ID为主键,Name为可空,无索引。表数据量为1463372,name中空值量为69853。 测试结果:count(*),count(1),count(ID),count(Name)执行计划是一致的。 不知道你是否自己试验过,愿闻其详。
回复
北亮bl 2013-11-05
引用 52 楼 guguda2008 的回复:
关于COUNT()的知识就一句话,括号里的值只统计不为NULL的数量
相信多数人,都是不看完整个msdn的,而且有的人连msdn都不看,别人说count是返回记录总数,就这么用了,从来不会去看说明的,所以普及一下,还是有意义的。 下面帖出COUNT的MSDN链接和知识: http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=ZH-CN&k=k(COUNT_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT)&rd=true 注释 -------------------------------------------------------------------------------- COUNT(*) 返回组中的项数。 包括 NULL 值和重复项。 COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。 COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。 对于大于 2^31-1 的返回值,COUNT 生成一个错误。 这时应使用 COUNT_BIG。
回复
guguda2008 2013-11-05
关于COUNT()的知识就一句话,括号里的值只统计不为NULL的数量
回复
發糞塗牆 2013-11-05
引用 50 楼 youbl 的回复:
假设有10条记录,3条记录的a字段为null 那么 count(1) 结果为10 count(a) 结果为7 明白? [quote=引用 45 楼 zhanwengong 的回复:] [quote=引用 39 楼 youbl 的回复:] 还有一个 Count(1) Count(*) Count(主键) 是一样的 但是Count(可空字段),就不一样了
测试了一把,貌似没有发现可空与其他的不同。[/quote][/quote]count(1)可以粗略理解为数据集中有多少行,而count(a)仅仅针对列来计算,所以count什么,需要看具体业务
回复
北亮bl 2013-11-05
假设有10条记录,3条记录的a字段为null 那么 count(1) 结果为10 count(a) 结果为7 明白?
引用 45 楼 zhanwengong 的回复:
[quote=引用 39 楼 youbl 的回复:] 还有一个 Count(1) Count(*) Count(主键) 是一样的 但是Count(可空字段),就不一样了
测试了一把,貌似没有发现可空与其他的不同。[/quote]
回复
北亮bl 2013-11-05
引用 55 楼 zhanwengong 的回复:
大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
如果你的“可空字段名”没创建索引,那么Count的执行计划,就更烂了
回复
北亮bl 2013-11-05
引用 55 楼 zhanwengong 的回复:
大哥,现在不是说查询结果是多少的问题,而是执行计划和效率问题。你连题目都没看清啊,亲!
ok,你要谈执行计划,我跟你谈,你确定你测试的数据量足够?你建一个表,放它10万数据,你再看看Count(1) 和 Count(可空字段名)的执行计划,如果一样,就见鬼了
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2013-10-24 11:50
社区公告
暂无公告