一个关于索引的问题(急)

Question

由于表的记录较多，大概有800万条，查询速度很慢，原本在一个字段上建了主键，且是聚集索引（该字段没什么用，只是唯一标识），后来发现大多数查询是用在日期上。
于是先删除该聚集索引：
Alter table table1
drop constraint pk_table1_id再在日期上创建聚集索引:
CREATE CLUSTERED INDEX IX_table1_date1
ON table1(date1)查询速度果然飞快，但是，这张表居然大了10G，原本大概300多M，这是什么原因呢？怎么解决？

Answer 1

索引要占用空间的sp_spaceused 'table1' 查看一下

Answer 2

日期上建索引，速度是否提上来了？如果是datetime 建索引的话，好像效果不会太明显吧 。另外，索引占空间是肯定的。 

Answer 3

这个日期存为varchar类型了，所以创建聚集索引后速度快非常明显。
但是我这表本来才300多M啊，就删了重新创建后就多出来10G，这个有点不可思议啊。
而且我现在对该库做全备速度很慢，不知道跟这个有没有关系。大侠们帮帮忙吧。

Answer 4

做下dbcc吧 看是不是显示有误

Answer 5

datetime本质上可以理解为一个decimal，建索引的效果同理。

Answer 6

这就是问题的关键。于是先删除该聚集索引：--> 需要把非聚集索引指向的聚集索引键改成文件号RID。
再在日期上创建聚集索引: --> 需要把上一步改成的RID再改成日期字段上的聚集索引。可见这两个操作是多么大的灾难。而且这个日期存为varchar类型，那么至少占10个字节以上，于是你的聚集索引和非聚集索引都会变大很大。

Answer 7

我之前曾有过一个关于聚集索引的讨论。聚集索引选取的原则：
1. 索引键尽可能小；
2. 尽量选择唯一的字段；
3. 尽量选择不经常变化的字段。另外，聚集索引键在每个非聚集索引中都会引用，因此关于聚集索引的修改一定要慎重。
实在要修改聚集索引的话，用一个CREATE CLUSTERED INDEX语句加上WITH DROP_EXISTING选项，而不是两个语句先DROP再CREATE，否则非聚集索引的重建工作吓死人。

Answer 8

针对你的情况的建议：1. 先冷静，不要匆忙做任何改动。2. 日期字段最好用datetime，datetime类型的索引操作通常比varchar更高效。但考虑到你的现有数据量和程序兼容问题，恐怕最好不要修改现有表。以后设计时注意即可。3. 索引方案：
方案一、聚集索引还是用id，在date1字段上建非聚集索引即可；这应该是你一开始选择的方案。
方案二、如果date1是datetime类型，这个字段上的非唯一聚集索引通常也还可以接受。4. 有了整体思路，做好数据库备份，再做修改。

Answer 9

在800多万条记录的表上建聚集索引真的很花时间。想问下，一般聚集索引建在什么字段上？
通常会在ID上创建主键，系统默认为聚集索引了，如果在程序中查询需要用到该ID，那么这样做是有必要的。那如果该ID只是用来唯一标识一条记录呢，那是否看查询条件来建索引呢？

Answer 10

在800多万条记录的表上建聚集索引真的很花时间。
====
是因为你的表上还有两个非聚集索引。
聚集索引一般在建表时就选择好，之后就不要轻易修改。
想问下，一般聚集索引建在什么字段上？
====
见15楼。
通常会在ID上创建主键，系统默认为聚集索引了，如果在程序中查询需要用到该ID，那么这样做是有必要的。那如果该ID只是用来唯一标识一条记录呢，那是否看查询条件来建索引呢？
====
聚集索引的选取原则上面已经说了。
一般来说，聚集索引最好选择一个唯一、定长、空间小的字段（int或decimal）。所以主键默认为聚集索引是有道理的。
如果查询条件需要，可以建非聚集索引。

Answer 11

高手不敢当。闻道有先后，术业有专攻，如是而已。1.我们一般是把查询条件经常用到的字段建一个非聚集索引。那么一般组合的聚集索引，和组合的非聚集索引用于什么地方？
====
组合的聚集索引占用空间比较大。若表是组合主键，并且表上基本不需要建非聚集索引，也可能采用组合聚集索引。
例如用户收藏夹表：Favourite(#UserID,#ItemID,CreateTime,Comment,...)。组合非聚集索引主要是优化查询。
比如：SELECT * FROM Item WHERE ItemType = 'Book' AND CreateTime BETWEEN @bdate AND @edate
如果经常需要根据ItemType和CreateTime组合查询，则可以建组合非聚集索引(ItemType,CreateTime)。
这个索引，当只有条件ItemType时可以使用，但当只有条件CreateTime时不可以使用。所以组合索引的字段次序要搞清楚。
2.由于表的记录比较多，索引空间的增长也非常惊人，如果说它和数据空间差不多大，甚至更大，那正常吗？
感谢！
====
很正常。OLTP系统的修改操作多，索引尽量要少；但对于OLAP这种以查询为主的系统，甚至有索引空间是数据空间的两三倍的情况。
总之，每多一个非聚集索引，修改操作就会慢一些。根据自己的情况综合考量吧。
一般来说，一个表的非聚集索引最多不要超过5个。

Answer 12

有兴趣，可以看看我写的这篇T-SQL查询优化乱弹。想深入了解索引的内部机制和性能优化的内容，可以看看下面两本参考书：
《SQL Server 2005技术内幕：存储引擎》
《SQL Server 2005性能调校》