3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
关于聚集索引
我新建了一张表
表中的数据按照天来存储的
现在业务的逻辑主要是需要查询给定时间段内 即 StatisticsDate >=给定时开始时间 和 StatisticsDate <给定的结束时间 之间的 ProductNum 和SupplyNum,
我决得应该在StatisticsDate字段上建立一个聚集索引.
因为按照聚集索引的定义,StatisticsDate字段建立聚集索引后,数据是按照StatisticsDate来排序,即我需要查询指定时间的数据,只需要找到要检索的所有数据中的开头时间和结尾时间即可.
但是Leader说不行,让我自己想一想.我想不明白.请各位给小弟解释一下.
谢谢.
CREATE TABLE [dbo].[Table1](
[ID] [uniqueidentifier] NOT NULL,
[ProductNum] [int] ,
[SupplyNum] [int],
[StatisticsDate] [smalldatetime]
)表中的数据按照天来存储的
现在业务的逻辑主要是需要查询给定时间段内 即 StatisticsDate >=给定时开始时间 和 StatisticsDate <给定的结束时间 之间的 ProductNum 和SupplyNum,
我决得应该在StatisticsDate字段上建立一个聚集索引.
因为按照聚集索引的定义,StatisticsDate字段建立聚集索引后,数据是按照StatisticsDate来排序,即我需要查询指定时间的数据,只需要找到要检索的所有数据中的开头时间和结尾时间即可.
但是Leader说不行,让我自己想一想.我想不明白.请各位给小弟解释一下.
谢谢.
...全文
请发表友善的回复…
发表回复
fffddd 2008-01-22
我觉得应该建立非聚集
w2jc 2008-01-22
谢谢解释 那是不是将同值的列这样处理之后放入B树下一层节点？
-------------------------------------
是的。这样的话，相同值的所有节点都在B树的同一个分支下面。
-------------------------------------
是的。这样的话，相同值的所有节点都在B树的同一个分支下面。
sinxy 2008-01-22
和Leader沟通了一下，以下是他的建议：
1.如果仅仅就我对表的操作，即按照 ID 和 StatisticsDate 组合查询的话，那么适合在这张表建立聚集索引；
2.但是每张表只能建一个聚集索引，所以聚集索引资源是宝贵的，建聚集索引就值得考虑；
3.考虑今后可能会增加表的字段，或者对表进行其他的业务操作，所以就全局来说，不适合在StatisticsDate 字段上建立聚集索引。
谢谢大家，马上结帖。hoho
1.如果仅仅就我对表的操作，即按照 ID 和 StatisticsDate 组合查询的话，那么适合在这张表建立聚集索引；
2.但是每张表只能建一个聚集索引，所以聚集索引资源是宝贵的，建聚集索引就值得考虑；
3.考虑今后可能会增加表的字段，或者对表进行其他的业务操作，所以就全局来说，不适合在StatisticsDate 字段上建立聚集索引。
谢谢大家，马上结帖。hoho
rouqu 2008-01-17
谢谢解释 那是不是将同值的列这样处理之后放入B树下一层节点？
w2jc 2008-01-17
就算附加了4个字节的序列号，有相同值的记录最后还是分布在B+树的一个分支上，所以仍然是非常快地。
w2jc 2008-01-17
其实不清楚聚集索引索引页的组织 如果同一索引列值出现8次
----------------------------
建聚焦索引不限定一定要是唯一值（即主键或有唯一索引），
如果同一个值出现多次，SQL会在内部自动附加一个4字节的序列号，以保证每个值都是唯一的。
这会增加一点内部开销。
所以，最好就是在有唯一值的字段上建聚集索引。
----------------------------
建聚焦索引不限定一定要是唯一值（即主键或有唯一索引），
如果同一个值出现多次，SQL会在内部自动附加一个4字节的序列号，以保证每个值都是唯一的。
这会增加一点内部开销。
所以，最好就是在有唯一值的字段上建聚集索引。
rouqu 2008-01-16
其实不清楚聚集索引索引页的组织 如果同一索引列值出现8次
1011 0x784F001
1011 0x784F002
1011 0x784F003
1011 0x784F004
1011 0x784F005
1011 0x784F006
1011 0x784F007
还是类似
1011 0x784F001
1011 0x784F007
1011 0x784F001
1011 0x784F002
1011 0x784F003
1011 0x784F004
1011 0x784F005
1011 0x784F006
1011 0x784F007
还是类似
1011 0x784F001
1011 0x784F007
michaelgy 2008-01-16
[StatisticsDate] [smalldatetime]
这个字段有非常多互不相同的值，（因为是精确到分钟的）建议使用非聚集索引。
如果查询返回结果基本固定而且字段不多的话，可考虑索引覆盖。
这个字段有非常多互不相同的值，（因为是精确到分钟的）建议使用非聚集索引。
如果查询返回结果基本固定而且字段不多的话，可考虑索引覆盖。
shirley_yue 2008-01-16
同意
beacyqm 2008-01-16
楼上大部分的都适合lz的情况
可以在StatisticsDate列建聚集索引
可以在StatisticsDate列建聚集索引
sp4 2008-01-15
LZ的leader肯定是个垃圾，别听他的，按照自己想的做
sp4 2008-01-15
StatisticsDate 建聚集索引是合适的
主键的guid类型修改为非聚集索引。
只能说lz的Leader脑袋有点问题。自己不清楚，自己又说不出来为什么，然后让别人想的垃圾leader
主键的guid类型修改为非聚集索引。
只能说lz的Leader脑袋有点问题。自己不清楚，自己又说不出来为什么，然后让别人想的垃圾leader
w2jc 2008-01-15
这个表有个问题，从表定义来看，ID字段数据类型是uniqueidentifier，
是否有什么特别的业务需求？例如分布式数据库，或者要使用合并复制等....
如果没有的话，最好避免用uniqueidentifier做主键，主要原因是太相当耗费空间（16字节），
任何引用这个字段的索引都体积大增，性能减低，今后做查询时更耗费内存（都要装到内存里面）。
用自增列，int 类型（甚至bigint）就可以满足大多数需求的。
比较一下：
uniqueidentifier 16字节
bigint 8字节 -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int 4字节 -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint 2字节 -2^15 (-32,768) to 2^15-1 (32,767)
最好是避免在unqiueidentifier的列上建聚合索引。
MSSQL性能调试方面的专家Brad McGehee，专门提到过这个：
“If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance. While the performance hit will be minimal if you do decide to add a clustered index to a GUID column, every little bit ads up. [7.0, 2000, 2005] Updated 10-15-2004”
比较适合见聚合索引的字段类型有：smallint, int, bigint, datetime。
并且如果是datetime用于聚合索引，这里面的值应该是不断增加的（随着时间的推移），并且很少修改。
比较适合按时间范围查询的，使between, <, > 更为有效。
这些都比较比较符合LZ的情况。
建议：
1) ID字段使用int类型的自增列
2) 在StatisticsDate建聚合索引
注：在datetime上建聚合索引是少有的能优化使用between, <, > 进行时间范围查询的手段，虽然有些限制，但LZ这里的情况是没有问题的。
是否有什么特别的业务需求？例如分布式数据库，或者要使用合并复制等....
如果没有的话，最好避免用uniqueidentifier做主键，主要原因是太相当耗费空间（16字节），
任何引用这个字段的索引都体积大增，性能减低，今后做查询时更耗费内存（都要装到内存里面）。
用自增列，int 类型（甚至bigint）就可以满足大多数需求的。
比较一下：
uniqueidentifier 16字节
bigint 8字节 -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int 4字节 -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint 2字节 -2^15 (-32,768) to 2^15-1 (32,767)
最好是避免在unqiueidentifier的列上建聚合索引。
MSSQL性能调试方面的专家Brad McGehee，专门提到过这个：
“If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance. While the performance hit will be minimal if you do decide to add a clustered index to a GUID column, every little bit ads up. [7.0, 2000, 2005] Updated 10-15-2004”
比较适合见聚合索引的字段类型有：smallint, int, bigint, datetime。
并且如果是datetime用于聚合索引，这里面的值应该是不断增加的（随着时间的推移），并且很少修改。
比较适合按时间范围查询的，使between, <, > 更为有效。
这些都比较比较符合LZ的情况。
建议：
1) ID字段使用int类型的自增列
2) 在StatisticsDate建聚合索引
注：在datetime上建聚合索引是少有的能优化使用between, <, > 进行时间范围查询的手段，虽然有些限制，但LZ这里的情况是没有问题的。
csshan 2008-01-15
建索引的选择性了，为查询快就要在选择度高的列建聚集索引，按 lz的说法，StatisticsDate列建索引是正确的选择，
但要考虑：
1磁盘（数据库)上有足够的空闲空间
2 这表有经常被更新，插入等操作
如果这样的话，看具体情况来定。
听听leader的声音，然后告诉大家结果
但要考虑：
1磁盘（数据库)上有足够的空闲空间
2 这表有经常被更新，插入等操作
如果这样的话，看具体情况来定。
听听leader的声音，然后告诉大家结果
w2jc 2008-01-15
但是Leader说不行,让我自己想一想.我想不明白.请各位给小弟解释一下.
--------------------------------------
呵呵，请leader给解释一下，看什么说的。也许另有高见呢。
不过这种事也没有必要较真，你可以照他说的去做，
等今后性能上出问题了，你可以再改索引，一比较就知道谁的方法好了。
如果速度也还可以的话（也许你的数据量根本也不大），那就无所谓的。
--------------------------------------
呵呵，请leader给解释一下，看什么说的。也许另有高见呢。
不过这种事也没有必要较真，你可以照他说的去做，
等今后性能上出问题了，你可以再改索引，一比较就知道谁的方法好了。
如果速度也还可以的话（也许你的数据量根本也不大），那就无所谓的。
冷箫轻笛 2008-01-14
用datetime、smalldatetime建 不能充分利用聚集索引数据页查找的优势 因为相同记录连续性较低
==================================
但是楼主说的每一天生成的数据的StatisticsDate字段值都是一样的阿
这种情况不是刚好可以用聚集索引吗？
==================================
但是楼主说的每一天生成的数据的StatisticsDate字段值都是一样的阿
这种情况不是刚好可以用聚集索引吗？
rouqu 2008-01-14
如表中记录被划分为多个大类 大类又涉及广泛的查找 对这样的大类建聚集索引较合适
用datetime、smalldatetime建 不能充分利用聚集索引数据页查找的优势 因为相同记录连续性较低
用datetime、smalldatetime建 不能充分利用聚集索引数据页查找的优势 因为相同记录连续性较低
冷箫轻笛 2008-01-14
呵呵，那期待高手了
这种情况我也会在StatisticsDate上建立聚集索引的
这种情况我也会在StatisticsDate上建立聚集索引的
sinxy 2008-01-14
我的 StatisticsDate 字段是取一个相同的时间
因为我的SQL脚本每天跑一次 所以每一天生成的数据的StatisticsDate字段值都是一样的
比如我今天跑SQL脚本 那么今天添加到表中的数据的StatisticsDate字段都是'20070114'
按照书本的理论 应该是适合在StatisticsDate上建立聚集索引的阿
因为我的SQL脚本每天跑一次 所以每一天生成的数据的StatisticsDate字段值都是一样的
比如我今天跑SQL脚本 那么今天添加到表中的数据的StatisticsDate字段都是'20070114'
按照书本的理论 应该是适合在StatisticsDate上建立聚集索引的阿
冷箫轻笛 2008-01-14
uniqueidentifier
这个类型没有用过，不了解。难道跟这个有关？
其实真正适合建聚集索引的例子是这样的：
比如部队人员编号表有100w的记录，每100个为一个连，
表的字段有id，连号，性别，。。。
对于这种情况，id因为每条记录都不重复，所以其实不适合建立聚集索引
性别这种的除了男就是女，最多加个保密，所以也不适合做聚集索引
连号是最适合的
聚集索引在查询数据的时候搜索到数据的最前面一条，然后再搜索到最后一条
然后把中间的记录返回，所以很快
楼主你的表，不知道StatisticsDate列是不是取得getdate()之类的值，就是说每条记录可能都不一样
如果是这样的话，其实严格意义上并不适合作聚集索引
但是如果你的表做其他的查询很少很少，那在这个字段上建立聚集索引也不错啊
反正也没有别的查询了，呵呵
这个类型没有用过，不了解。难道跟这个有关？
其实真正适合建聚集索引的例子是这样的：
比如部队人员编号表有100w的记录，每100个为一个连，
表的字段有id，连号，性别，。。。
对于这种情况，id因为每条记录都不重复，所以其实不适合建立聚集索引
性别这种的除了男就是女，最多加个保密，所以也不适合做聚集索引
连号是最适合的
聚集索引在查询数据的时候搜索到数据的最前面一条，然后再搜索到最后一条
然后把中间的记录返回，所以很快
楼主你的表，不知道StatisticsDate列是不是取得getdate()之类的值，就是说每条记录可能都不一样
如果是这样的话，其实严格意义上并不适合作聚集索引
但是如果你的表做其他的查询很少很少，那在这个字段上建立聚集索引也不错啊
反正也没有别的查询了，呵呵