以如下需求为例：根据一段时间的网站日志生成访问记录表tblWebVisitLog，需记录以下信息：
访问时间: VisitTime
访问者IP: IP
访问的URL: URL
同一时刻可能有多条访问记录，即VisitTime不是唯一的。数据量：3000万条记录左右常见查询情况：
1. 在一个时间范围内，按时序列出指定IP访问的URL，即根据VisitTime和IP查找。
2. 在一个时间范围内，统计各个时段（每天/每小时）的访问量/IP数，即根据VisitTime聚集统计。
3. 在一个时间范围内，统计各个或指定URL的访问量/IP数，即根据VisitTime和URL聚集统计。SQL语句：--用维度表存储URL，以压缩空间
CREATE TABLE tblURL(
intURLKey int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
strURL varchar(500) NOT NULL UNIQUE)
GO--以下为事实表的方案：--方案一：
CREATE TABLE tblWebVisitLog1(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE CLUSTERED INDEX IX_tblWebVisitLog1 ON tblWebVisitLog1(dtVisitTime) --非唯一聚集索引
CREATE INDEX IX_tblWebVisitLog1_binIP ON tblWebVisitLog1(binIP)
CREATE INDEX IX_tblWebVisitLog1_intURLKey ON tblWebVisitLog1(intURLKey)--方案二：
CREATE TABLE tblWebVisitLog2(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE INDEX IX_tblWebVisitLog2 ON tblWebVisitLog2(dtVisitTime) --表上无聚集索引
CREATE INDEX IX_tblWebVisitLog2_binIP ON tblWebVisitLog2(binIP)
CREATE INDEX IX_tblWebVisitLog2_intURLKey ON tblWebVisitLog2(intURLKey)--方案三：
CREATE TABLE tblWebVisitLog3(
intLogSN int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, --以自增的主键为唯一聚集索引
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE INDEX IX_tblWebVisitLog3 ON tblWebVisitLog3(dtVisitTime)
CREATE INDEX IX_tblWebVisitLog3_binIP ON tblWebVisitLog3(binIP)
CREATE INDEX IX_tblWebVisitLog3_intURLKey ON tblWebVisitLog3(intURLKey)
大家觉得以上三种方案，哪种方案更好些呢？
主要考虑两方面：
1. 占用存储空间
2. 查询性能或者有经验的大牛们能否分享一下关于聚集索引选择方案的心得？以下为生成测试数据的脚本，仅供参考：--根据Nums表生成测试数据INSERT INTO tblURL(strURL)
SELECT strURL = '/' + RIGHT('00000' + CAST(n AS varchar(10)),5) + '.html'
FROM Nums
WHERE n BETWEEN 1 AND 10000INSERT INTO tblWebVisitLog1
SELECT
dtVisitTime = DATEADD(second,n / (n % 5 + 5),'20100601'), --同一时刻可能有5到10条访问记录，即VisitTime不唯一
binIP = CAST(ABS(CHECKSUM(NEWID())) % 50 + 200 AS binary(1)) +
CAST(ABS(CHECKSUM(NEWID())) % 16777216 AS binary(3)), --限定IP地址在200.x.x.x到249.x.x.x范围内
intURIKey = ABS(CHECKSUM(NEWID())) % 10000 + 1 --限定有效的tblURL键值
FROM Nums
WHERE n BETWEEN 1 AND 1000000 --只测试了100万条记录的情况INSERT INTO tblWebVisitLog2
SELECT * FROM tblWebVisitLog1INSERT INTO tblWebVisitLog3
SELECT * FROM tblWebVisitLog1
测试代码示例：--存储空间
EXEC sp_spaceused 'tblWebVisitLog1'
EXEC sp_spaceused 'tblWebVisitLog2'
EXEC sp_spaceused 'tblWebVisitLog3'--按VisitTime字段查找
SELECT TOP(10) * FROM tblWebVisitLog1 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'
SELECT TOP(10) * FROM tblWebVisitLog2 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'
SELECT TOP(10) * FROM tblWebVisitLog3 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'--按IP字段查找（按URL字段查找情况类似）
SELECT TOP(10) * FROM tblWebVisitLog1 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
SELECT TOP(10) * FROM tblWebVisitLog2 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
SELECT TOP(10) * FROM tblWebVisitLog3 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
访问时间: VisitTime
访问者IP: IP
访问的URL: URL
同一时刻可能有多条访问记录，即VisitTime不是唯一的。数据量：3000万条记录左右常见查询情况：
1. 在一个时间范围内，按时序列出指定IP访问的URL，即根据VisitTime和IP查找。
2. 在一个时间范围内，统计各个时段（每天/每小时）的访问量/IP数，即根据VisitTime聚集统计。
3. 在一个时间范围内，统计各个或指定URL的访问量/IP数，即根据VisitTime和URL聚集统计。SQL语句：--用维度表存储URL，以压缩空间
CREATE TABLE tblURL(
intURLKey int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
strURL varchar(500) NOT NULL UNIQUE)
GO--以下为事实表的方案：--方案一：
CREATE TABLE tblWebVisitLog1(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE CLUSTERED INDEX IX_tblWebVisitLog1 ON tblWebVisitLog1(dtVisitTime) --非唯一聚集索引
CREATE INDEX IX_tblWebVisitLog1_binIP ON tblWebVisitLog1(binIP)
CREATE INDEX IX_tblWebVisitLog1_intURLKey ON tblWebVisitLog1(intURLKey)--方案二：
CREATE TABLE tblWebVisitLog2(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE INDEX IX_tblWebVisitLog2 ON tblWebVisitLog2(dtVisitTime) --表上无聚集索引
CREATE INDEX IX_tblWebVisitLog2_binIP ON tblWebVisitLog2(binIP)
CREATE INDEX IX_tblWebVisitLog2_intURLKey ON tblWebVisitLog2(intURLKey)--方案三：
CREATE TABLE tblWebVisitLog3(
intLogSN int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, --以自增的主键为唯一聚集索引
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE INDEX IX_tblWebVisitLog3 ON tblWebVisitLog3(dtVisitTime)
CREATE INDEX IX_tblWebVisitLog3_binIP ON tblWebVisitLog3(binIP)
CREATE INDEX IX_tblWebVisitLog3_intURLKey ON tblWebVisitLog3(intURLKey)
大家觉得以上三种方案，哪种方案更好些呢？
主要考虑两方面：
1. 占用存储空间
2. 查询性能或者有经验的大牛们能否分享一下关于聚集索引选择方案的心得？以下为生成测试数据的脚本，仅供参考：--根据Nums表生成测试数据INSERT INTO tblURL(strURL)
SELECT strURL = '/' + RIGHT('00000' + CAST(n AS varchar(10)),5) + '.html'
FROM Nums
WHERE n BETWEEN 1 AND 10000INSERT INTO tblWebVisitLog1
SELECT
dtVisitTime = DATEADD(second,n / (n % 5 + 5),'20100601'), --同一时刻可能有5到10条访问记录，即VisitTime不唯一
binIP = CAST(ABS(CHECKSUM(NEWID())) % 50 + 200 AS binary(1)) +
CAST(ABS(CHECKSUM(NEWID())) % 16777216 AS binary(3)), --限定IP地址在200.x.x.x到249.x.x.x范围内
intURIKey = ABS(CHECKSUM(NEWID())) % 10000 + 1 --限定有效的tblURL键值
FROM Nums
WHERE n BETWEEN 1 AND 1000000 --只测试了100万条记录的情况INSERT INTO tblWebVisitLog2
SELECT * FROM tblWebVisitLog1INSERT INTO tblWebVisitLog3
SELECT * FROM tblWebVisitLog1
测试代码示例：--存储空间
EXEC sp_spaceused 'tblWebVisitLog1'
EXEC sp_spaceused 'tblWebVisitLog2'
EXEC sp_spaceused 'tblWebVisitLog3'--按VisitTime字段查找
SELECT TOP(10) * FROM tblWebVisitLog1 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'
SELECT TOP(10) * FROM tblWebVisitLog2 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'
SELECT TOP(10) * FROM tblWebVisitLog3 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'--按IP字段查找（按URL字段查找情况类似）
SELECT TOP(10) * FROM tblWebVisitLog1 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
SELECT TOP(10) * FROM tblWebVisitLog2 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
SELECT TOP(10) * FROM tblWebVisitLog3 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
---使用索引优化数据库查询效率
1.不宜创建索引的情形
(1)经常插入，修改和删除的表
(2)数据量比较小的表，因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间2.适合创建索引的情形
(1)为where子句中出现的列创建索引
(2)创建组合索引
(3)为group by 子句中出现的列创建索引3.聚集索引的设计原则
(1)该列的数值是唯一的或者很少有重复的记录
(2)经常使用between ...and..按顺序查询的列
(3)定义identity的唯一列.
(4)经常用于对数据进行排序的列.---无法使用索引的select语句
1.对索引列使用了函数,如：
select * from tb where max(id)=1002.对索引列使用了'%xx'，如：
select * from tb where id like '%1'
需要注意的不是所有使用like关键字的select 语句都无法使用索引,比如
select * from tb where id like '1%'就可以使用索引3.在where子句中对列进行类型转换(其实也是使用到了函数)4.在组合索引的第1列不是使用最多的列,如在下面3个查询语句中建立组合索引,按顺序包含col2,col1,id列；
select * from tb where id='1' and col1='aa'
select id,sum(col1) from tb group by id
select * from tb where id='2' and col2='bb'
则第一句和第二句无法使用到索引 所以需要注意组合索引的顺序5.在where 子句中使用in关键字的某些句子
当在in关键字后面使用嵌套的select语句，将无法使用在该列上定义的索引
如：
select
*
from
ta
where
id
in
(select id from tb where ....)--这样可以用到索引
select * from tb where id in('1','2')
1)方案四
CREATE TABLE tblWebVisitLog4(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE CLUSTERED INDEX IX_tblWebVisitLog5 ON tblWebVisitLog5(dtVisitTime) --非唯一聚集索引
CREATE INDEX IX_tblWebVisitLog5_binIP ON tblWebVisitLog5(binIP)INCLUDE(intURLKey)2)方案一中,
SELECT TOP(10) * FROM tblWebVisitLog1 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
不会用到索引IX_tblWebVisitLog1_binIP.
因为此查询是*,假设用到IX_tblWebVisitLog1_binIP那么MS SQL会根据遍历该索引返回的Key(聚集索引的值)再去遍历IX_tblWebVisitLog1进而找到intURLKey的值,以完成*的结果.3)方案二中 无聚集索引 不可取.4)方案三中 自己建立个自增字段作为CLUSTERED Key,但是当查询
SELECT TOP(10) * FROM tblWebVisitLog3 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'时并不高效.5)可建立一个SQL Server Profiler,保存为.trc格式,用如下查询 比较性能.SELECT
--Avg_Duration = CAST(SUM(Duration) AS real)/ COUNT(EventClass)--,
Avg_TimeDiff = SUM(DATEDIFF(ms,StartTime,EndTime))/CAST(COUNT(EventClass)AS float),
Avg_Duration = SUM(Duration) / CAST(COUNT(EventClass)AS float)/1000,
Avg_Reads = SUM(Reads)/CAST(COUNT(EventClass)AS float),
Avg_Writes = SUM(Writes)/CAST(COUNT(EventClass)AS float),
Avg_CPU = SUM(CPU)/CAST(COUNT(EventClass)AS float)
FROM ::fn_trace_gettable('E:\Test\log5.trc', default)
WHERE TextData is not null AND EndTime IS NOT NULL
or EventClass in (16, -- Attention
25, -- Lock:Deadlock
27, -- Lock:Timeout
33, -- Exception
58, -- Auto Update Stats
59, -- Lock:Deadlock Chain
79, -- Missing Column Statistics
80, -- Missing Join Predicate
92, -- Data File Auto Grow
93, -- Log File Auto Grow
94, -- Data File Auto Shrink
95) -- Log File Auto Shrink
GROUP BY EventClass6)... ...
SELECT TOP(10) * FROM tblWebVisitLog1 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
我测试的结果是使用了索引IX_tblWebVisitLog1_binIP：先索引查找再键查找。这跟SELECT *无关，应该是跟表的总记录数和查询的选择性有关（比如你生成的测试数据恰好大部分都是0xD5BC????范围的）。
当然，因为是TOP(10)，这种情况下先索引查找再键查找，可能还不如聚集索引扫描（表扫描）效果好。2.
CREATE INDEX IX_tblWebVisitLog5_binIP ON tblWebVisitLog5(binIP)INCLUDE(intURLKey)
intURLKey可以作为索引键，不必放在包含性列，还不如：
CREATE INDEX IX_tblWebVisitLog5_binIP_intURLKey ON tblWebVisitLog5(binIP,intURLKey)3.
你的方案，对于主帖的查询情况3（即根据intURLKey查询）性能不好。按上面我的第2条修改索引后可能会略有提升。
当然，考虑到查询情况3的频率没有情况1、情况2高，这种方案可以考虑。4.
方案二，RID查找与聚集索引查找相比没有任何优势，直接否掉。
方案三比方案一的优势是，唯一聚集索引键intLogSN只有4个字节，而非唯一聚集索引键dtVisitTime却有12个字节（datetime类型8字节+唯一标识符4字节），因而方案三中的非聚集索引更省空间，当索引的数据页数量多时可能也更省时间。
CREATE TABLE tblWebVisitLog4(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE CLUSTERED INDEX IX_tblWebVisitLog4 ON tblWebVisitLog4(dtVisitTime) --非唯一聚集索引
CREATE INDEX IX_tblWebVisitLog4_binIP_intURLKey ON tblWebVisitLog4(binIP,intURLKey)
测试之后发现该方案比想像中还要好（原因是在这种情况下索引IX_tblWebVisitLog4_binIP_intURLKey已经包含了表中所有字段，不再需要键查找）：
1. 占用空间：方案四 < 方案三 < 方案一 < 方案二
2. 根据dtVisitTime查询的时间：方案四 = 方案一 << 方案二 <= 方案三
3. 根据binIP查询的时间：
小选择性：方案四 << 方案二 <= 方案三 <= 方案一
大选择性：方案四 << 方案一 = 方案二 = 方案三
4. 根据intURLKey查询的时间：
小选择性：方案二 <= 方案四 <= 方案三 <= 方案一
大选择性：方案一 = 方案二 = 方案三 < 方案四
其中：“=”表示接近、约等于；“<=”表示略小于；“<”表示明显小于；“<<”表示远远小于。
大多数情况下只有 dtVisitTime 和 binIP 有聚集索引，才会提升语句的性能，但是这两个列不可能同时分别具有聚集索引。如果在这两个列上建复合聚集索引，那么在单独查询索引码后部的列时，可能不会用到聚集索引。因此，无论怎样设计聚集索引，这两个查询语句都会有一个语句用不上索引。
如果分别在 dtVisitTime 和 binIP 列上建包含性的非聚集索引，倒是可以提升这两个语句的性能，但是会占用大量的空间，并且会降低 insert 性能。不过既然是日志表，必然会有大量的数据，并且一些较老的数据一般是不会被查询的，因此可以试试按 dtVisitTime 给表分区。最后建议，LZ 可以使用 SQL Server 自带的“数据库优化向导”确定怎样设计索引和分区。
索引的选取(被选用),主要考虑到该查询的返回记录的条数(即是查询影响的行数),返回越少越好.
如果返回的记录数接近表数,或则选取的是Table Scan.
还有该索引的(selectivity)选择性.一般而言针对一个字段的重复记录越少,
selectivity越高,越宜被采用..2)你的查询(针对你当前的表结构)包含*,其实是覆盖查询,所以当之前我的方案四时,优化器不必到Data Page去查找数据,而仅仅到Index 项即可.方案二中,有这种可能:即是当优化器选择IX_tblWebVisitLog1_binIP时 发现其先遍历该Index 再去通过CLUSTERED index找其它字段时 并不快捷.
可能的原因有聚集索引时,对于同一个Page ID下的读取算作一个Logic Read(逻辑读)
然而当采取Index加CLUSTERED index方式时,非聚集索引每遍历一次聚集索引的Data Page时
即算一个逻辑读.所以这样遍历可能也不快速了.3)Include方式只是在被包含的字段不被经常查询到时 高效.因为他是存在索引项上,但当该字段
因插入删除 更新 引起的索引变化,它并不跟随变化.Include是覆盖索引的特殊方式4)当建立复合索引时,MS SQL仅仅存放第一个字段的Statistics,所以Key Column很重要.5)DBCC SHOWCONTIG (tblWebVisitLog4)
DBCC SHOW_STATISTICS(tblWebVisitLog4,IX_tblWebVisitLog4)
可以帮助分析表的连贯性和索引的统计信息
2. IP + VisitTime 非聚集。或者
1. VisitTime + URL 非聚集。
2. IP + VisitTime 聚集。都测试下速度，方案要以测试为准。
黔西南网 www.goveduorg.cn
www.Baidulaozuzong.cn
运动鞋_ 运动鞋品牌,运动鞋批发,耐克新款,匡威帆布鞋,阿迪达斯鞋专业提供运动鞋批发.主要有:耐克新款_匡威帆布鞋-阿迪达斯鞋—Adidas阿迪达斯、Converse匡威帆布鞋_Puma彪马等高仿鞋批发,精仿鞋批发品牌板鞋_跑鞋_篮球鞋
如果你采用的是方案三的话，其空间情况如下
intLogSN int 4bytes
dtVisitTime datetime 8bytes
binIP binary (4) 4bytes
intURLKey 4bytes
一行:4+8+4+4=20bytes
一页：8060/16=403 行
3000万行，需要30000000/403=74441个页
总的空间:59553*8=595533=581M可以看到，总的才需要600M空间左右，这是非常小的，可以不用考虑空间，尽量优化吧，反正才三四个字段，怎么搞都可以。
--修改后的方案四
CREATE TABLE tblWebVisitLog4(
dtVisitTime datetime NOT NULL,
binIP binary(4) NOT NULL,
intURLKey int NOT NULL FOREIGN KEY REFERENCES tblURL(intURLKey))
CREATE CLUSTERED INDEX IX_tblWebVisitLog4 ON tblWebVisitLog4(dtVisitTime) --非唯一聚集索引
CREATE INDEX IX_tblWebVisitLog4_binIP_intURLKey ON tblWebVisitLog4(binIP,intURLKey)同意使用这个。
这样两类查询都会使用到索引查找 SELECT TOP(10) * FROM tblWebVisitLog1 WHERE dtVisitTime BETWEEN '20100601 03:00' AND '20100601 03:05'
|--Top(TOP EXPRESSION:((10)))
|--Clustered Index Seek(OBJECT:([master].[dbo].[tblWebVisitLog1].[IX_tblWebVisitLog1]), SEEK:([master].[dbo].[tblWebVisitLog1].[dtVisitTime] >= '2010-06-01 03:00:00.000' AND [master].[dbo].[tblWebVisitLog1].[dtVisitTime] <= '2010-06-01 03:05:00.000') ORDERED FORWARD)SELECT TOP(10) * FROM tblWebVisitLog1 --WITH (INDEX(IX_tblWebVisitLog1_binIP)) WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
|--Top(TOP EXPRESSION:((10)))
|--Index Seek(OBJECT:([master].[dbo].[tblWebVisitLog1].[IX_tblWebVisitLog1_binIP]), SEEK:([master].[dbo].[tblWebVisitLog1].[binIP] >= 0xD5BC0000 AND [master].[dbo].[tblWebVisitLog1].[binIP] <= 0xD5BCFFFF) ORDERED FORWARD)
即：set statistics time ON;set statistics IO ON;
如果这里面的逻辑读取小了，才能说明快了。还有需要看执行计划。
3、select * from 跟SELECT COUNT(*) FROM 肯定有区别，特别是对非聚集索引来说。
如果你查询的值都在索引内部，那么就不需要再去找一遍数据页了，直接在索引内部得出结果，如果是SELECT * 还需要查找到数据页。请参考覆盖索引部分。
第1条，表的单行记录空间大，会减少一个数据页可以存放的记录数，进而增加聚集索引叶级页面的数量和聚集索引树的大小。除此之外还有别的影响么？
如果需要显示非索引键的字段，即使聚集索引树比较大，聚集索引查找 也要比 非聚集索引查找+键查找 快很多啊。第2条，我所说的时间，是指执行计划中显示的执行时间比例，不是主观感觉上的执行时间长短。
set statistics time ON;set statistics IO ON;这两个确实很重要。
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;还有这两个也很重要，缓冲区和缓存也很影响测试结果。
1. 在一个时间范围内，按时序列出指定IP访问的URL，即根据VisitTime和IP查找。
2. 在一个时间范围内，统计各个时段（每天/每小时）的访问量/IP数，即根据VisitTime聚集统计。
3. 在一个时间范围内，统计各个或指定URL的访问量/IP数，即根据VisitTime和URL聚集统计。================改代码还不如改结构.一个表保存流水.
一个表保存每时URL/IP数.
表个表保存URL的IP数.在写流水时同时写统计表.