Goodspeed

SELECT * 的真相: 索引覆盖(index coverage)

SELECT *的效率很糟糕吗？当然，所有人都知道这一点，但是为什么呢？

是因为返回了太多的数据？

这是一个普遍的回答，但我不这样认为。如果你的数据库设计规范合理，那么带宽占用实际上非常的小。

让我们看看下面的例子。下面的查询将会从AdventureWorks.dbo.TransactionHistoryArchive（总共大约有近9万行数据）中选择出326行数据。第一个使用了SELECT * 查询，后一个查询则有明确的字段。

SELECT * FROM Production.TransactionHistoryArchive 
WHERE ReferenceOrderID < 100

SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive 
WHERE ReferenceOrderID < 100

在这种情况下，两者在网络带宽的区别只有15K（180K-165K），大约10%的带宽差异。的确值得去优化，但不会有很大的效果。

SELECT * 将造成表/索引扫描

SELECT * 的最大问题是将影响查询计划。SQL Server主要使用索引去查询你需要的数据，当索引包括所有的你请求查询的字段，SQL Server将不需要去在表中查询。这个概念称做索引覆盖。在上面的例子中，第一个查询结果是在聚集索引扫描中，反过来，第二个例子使用了更多更有效率的索引扫描。在这个案例中，索引扫描比聚集索引扫描快100倍。



除非你已经将为每个字段建立了索引（显然不是个好主意），SELECT *是不能够利用到索引覆盖，你只能去做扫描操作（非常的没有效率）。

如果你只是查询你所需要的字段，那你更可能的覆盖到你的索引。我想这就是不推荐使用SELECT *的主要的原因。

稳定性方面

在维护一个应用程序时，SELECT *也会带来一些意想不大的问题。它会引起你的代码发生一些不确定性。如果你增加了一个行（译注：我觉得这里应该是字段）到一个表中，那么SELECT * 返回的结果到你的应用程序中将会在结构上发生变化。良好的应用程序应该是使用字段名称的，而不应该受此影响。当外界发生变化时，良好的应用程序设计也应该最小化的更改。

英文原稿: http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries-are-bad-index-coverage.aspx

posted on 2007-07-20 00:25 Goodspeed 阅读(5617) 评论(37) 编辑 收藏

评论

#1楼 2007-07-20 00:44 Jeff Yang      

没有特别的理由，一般不提倡用Select *的 　回复　引用　查看　  

#2楼 2007-07-20 01:13 socrates[未注册用户]

重要的是索引啊 　回复　引用　  

#3楼 2007-07-20 01:28 Lostinet      

你的表结构是如何的??

这个情况应该是Index Seek然后Bookmark Lookup的啊.

　回复　引用　查看　  

#4楼 2007-07-20 03:08 volnet（可以叫我大V）      

select * from Table
只用于测试查询（一般在查询分析器中使用）
其他情况一律选择使用指定列名～呵呵
已经成为一种习惯了，虽然不太清楚其中的原因（有点忘了）但是，知道效率低倒是真的 　回复　引用　查看　  

#5楼 2007-07-20 07:53 补丁      

表示一下不理解 　回复　引用　查看　  

#6楼 2007-07-20 08:02 丁学      

select *要扫描所有列，而后一个select只要扫描主键列，聚集索引的扫描速度是不容置疑的 　回复　引用　查看　  

#7楼 2007-07-20 08:52 鼠标王[未注册用户]

呵呵，字段多了你就不用select *了。 　回复　引用　  

#8楼 2007-07-20 08:57 Nineteen@newsmth      

显然楼主对一些重要概念性没有理解清楚。应该首先明白聚集索引是啥，非聚集索引是啥，覆盖索引是啥，create到多列上的index和create到一列上的index include其他列是啥。

如果选择性很高，select *和select 某个字段，IO和CPU在服务器端几乎相同。

btw:聚集索引从一般意义上来说，那就是一张表上最大的覆盖索引。 　回复　引用　查看　  

#9楼 2007-07-20 08:58 路人[未注册用户]

说的不对吧
我要测试看看 　回复　引用　  

#10楼 2007-07-20 09:00 麒麟.NET      

如果一个表有100个字段，难道还不用select * 吗？ 　回复　引用　查看　  

#11楼 2007-07-20 09:11 cnlamar      

：D 　回复　引用　查看　  

#12楼 2007-07-20 09:20 allen

两个是完全一样的, 之所以有IO差别, 那是因为后面那个少了些字段. 　回复　引用　  

#13楼 2007-07-20 09:20 OK+[未注册用户]

有个问题不解
如果要引用一个表的所有字段,那用*和不用*那个效率高呢? 　回复　引用　  

#14楼 2007-07-20 09:26 木野狐      

英文原稿也是你写的吗，这个文章是不是翻译，没看明白。 　回复　引用　查看　  

#15楼 2007-07-20 09:41 winnerzone      

恩,以后不用了. 　回复　引用　查看　  

#16楼 2007-07-20 09:50 Jade[未注册用户]

显然楼主对一些重要概念性没有理解清楚 　回复　引用　  

#17楼 2007-07-20 10:51 QFSoft      

一般不用....测代码的时候才用.. 　回复　引用　查看　  

#18楼 2007-07-20 10:52 Boler Guo      

我觉得有两处可以用SELECT *：
1. SELECT COUNT(*) FROM ...
2. IF EXISTS(SELECT * FROM ...) 　回复　引用　查看　  

#19楼 2007-07-20 11:03 我是小草      

高手啊 　回复　引用　查看　  

#20楼 2007-07-20 11:42 a2z[未注册用户]

所谓的索引覆盖应该是覆盖where部分的列,
而不是select部分的列,
所谓的网络带宽差距是数据量上的差距,仅此而已:
传输一个列的数据当然比传输所有列的数据消耗的带宽要少 　回复　引用　  

#21楼 2007-07-20 12:03 a2z[未注册用户]

所以首先要where部分的列建立索引,
若where部分的列未建立索引,不管怎么查询,都会遍历全表.

其次才考虑select部分的列,
如果select指定的列建立索引了,才会直接从索引中返回结果,
否则会多一个步骤:根据索引访问表,获取select指定列的数据.
　回复　引用　  

#22楼 2007-07-20 12:40 金色海洋（jyk）      

博克园不会也变成csdn吧。
还是csdn的都跑过来了。
1、SELECT * 和 SELECT ReferenceOrderLineID
是等效的查询语句吗？后者只返回一个字段的值，而前者返回几个字段的值呢？表里有多少就返回来多少！二者等效吗？除非 表里只有一个ReferenceOrderLineID 字段，都这是不等效了。返回的记录集都是不一样的，这样的两个语句怎么比较呀！得出来的结论能正确吗？
2、当索引包括所有的你请求查询的字段
这个只和where 后面的字段有关吧，和select 后面的字段有关系吗？说的是select 而不是 where 吧。
其他的我就不说了。 　回复　引用　查看　  

#23楼 2007-07-20 13:24 A.Z[未注册用户]

如果一个系统用户很少，表确很多（业务复杂），就不用在乎select *了，逻辑没有错已经是万幸。 　回复　引用　  

#24楼 2007-07-20 14:27 东吴居士      

9:00 麒麟.NET

有100个字段的表设计已经是很不合理了！ 　回复　引用　查看　  

#25楼 2007-07-20 17:26 风焰庄主      

是这样的啊 　回复　引用　查看　  

#26楼 2007-07-20 21:31 Little Snail      

楼主是翻译的 ，呵呵，最好注明，内容还是不错的 ，学习 　回复　引用　查看　  

#27楼 2007-07-21 02:26 JoeLee[未注册用户]

1.聚集索引和非聚集索引是不一样的。在SELECT *时，如果使用了聚集索引。那么根据聚集索引已经可以找到所在数据行了。多取几列和少取几列没多大区别。
2.表扫描？据我所知，在SQL SERVER下，除非你不定义主键，才会有这种状况。另外，表扫描也不是由SELECT * 完全决定的，还要看你的WHERE语句。为所有的字段都定义索引，那么索引页和数据页一样大了。同样甚至超过数据页的IO。还有什么优势呢？还有一种情况是基于非聚集索引的聚集索引查询。当WHERE中包括非聚集索引列时，首先会找到非聚集索引。然后查找聚集索引。找到聚集所以，也就找到了数据行。还是和SELECT 多少没关系。主要看你的WHERE。
3.*和字段名各有利弊，在*的时候。只要表字段的顺序不要乱改就好。因为取第三列时有可能是一个新增的列。而把字段名全写全了，字段名更改又麻烦。要改很多个写了各个字段名的地方。

如果很复杂的逻辑，建议还是ORM吧，好维护的多。
如此垃圾的文章，不知道你翻译了放首页做什么？难道非要把CNBLOG搞得像CSDN一样烂？ 　回复　引用　  

#28楼 2007-07-22 10:12 Joad[未注册用户]

To Ariel Y

我认为你的意思和文章是一样的。

>除非你已经将为每个字段建立了索引（显然不是个好主意），SELECT *是不能够利用到索引覆盖，你只能去做扫描操作（非常的没有效率）。

第二个查询(明确字段)能用上索引就是因为IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID。

SELECT*很难用上索引，除非每个字段都建立了索引。如果你使用明确字段做查询，那么命中索引的可能性就会大一些。

BTW,以上是我的理解。

　回复　引用　  

#29楼 2007-07-22 10:50 rex      

select * 和 select 字段 得差别仅仅在于查询分析器对该语句分析所花的时间,索引只和where 从句有关!! 　回复　引用　查看　  

#30楼 2007-08-05 11:01 丁学      

@麒麟.NET
你不感觉一张表里一百个字段有问题吗？
再说了，一百个字段一下全显示给人看？ 　回复　引用　查看　  

#31楼 2007-08-06 10:07 leek9d[未注册用户]

第一感觉: 标题好像要说 select * 其实效率不低
看完后的感觉: select * 一文不值

整体感觉: 分析的很精彩, 但是文章的结论推翻了标题 　回复　引用　  

#32楼 2007-09-27 22:52 聪动[未注册用户]

SELECT * FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100
之所以会做聚集索引扫描 ，是因为SQL Server查询优化器认为做聚集索引扫描比做索引扫描效率更高，因为你不是索引覆盖，查找完了索引还要到数据表去读取。
而
SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100 因为索引包含了要查找的字段（索引覆盖），所以SQL Server查询优化器认为做索引扫描效率高，因为直接从索引页中读出就可以，不用再去数据表中查找。
其实用那种扫描 和你数据库中的数据量和统计信息有关系的，那种效率更高 就用那种。 　回复　引用　  

#33楼 2007-12-18 17:19 Bruce Xiao      

@聪动
SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100 因为索引包含了要查找的字段（索引覆盖），所以SQL Server查询优化器认为做索引扫描效率高

1、难道seelct * 还没有包括你索引的字段吗？SELECT ReferenceOrderLineID 范围更窄吧。
2、你的所以，得出的结论，好像不那么显而易见吧，可以再解释具体些吗？ 　回复　引用　查看　  

#34楼 2008-01-19 20:31 PerfectDesign      

楼主文章并没有阐释清楚一个问题就是：
你那个索引到底是聚集索引还是非聚集的。聚集索引的话你这个测试结果就是在说瞎话了。因为聚集索引就在数据所在行，不存在速度的问题。
如果是非聚集索引那存在RID查找问题，速度自然下降。
还有一个没有阐述清楚的是：表里面是不是就只有一个字段？
如果只有一个字段，而又是聚集索引，那毫无疑问你的结论不成立。如果多个字段，那么两个查询根本没有可比性，不应拿来一起比较。
sql语句里面不写*是基本规范。
codereview首先检查的就是这个字符，其次就是exec。

可以熟悉一下sql2005的覆盖索引，这个比复合索引能满足小部分人的需求，不需要做RID查找，而且从理论上说效率又比复合索引好那么丁点。 　回复　引用　查看　  

#35楼 2008-06-20 12:15 qc[未注册用户]

20#理解覆盖索引是错误的!
覆盖索引应该是指,select 后面的列 都包含在建立的索引的列中
下面是引用microsoft的话:
"当表中包含很多列，但查询最常引用的只有很少几列时，此类索引尤其适用。通过创建一个或多个覆盖索引，可以大大改进受影响的查询的性能..." 　回复　引用　  

#36楼 2008-09-16 10:47 fcuandy[未注册用户]

先不说文章写的如何，单看第一个例子，看到这句时"索引扫描比聚集索引扫描快100倍", 可以看出本文并不严谨，甚至有误之嫌 　回复　引用　  

#37楼 2008-09-16 10:48 fcuandy[未注册用户]

有误导之嫌。上面打错，报歉。 　回复　引用　  

最新IT新闻:
· 比尔·盖茨也开腾讯微博
· M9入俄
· 阿里巴巴云计划被指“3个臭皮匠不敌诸葛亮”
· 支付公司Stripe融资1800万美元 估值1亿美元
· 诺基亚Lumia 910第三方保护壳现身亚马逊
» 更多新闻...