当前位置: 代码迷 >> Sql Server >> 关于聚集索引选择方案的疑问解决办法
  详细解决方案

关于聚集索引选择方案的疑问解决办法

热度:1016   发布时间:2016-04-27 22:00:32.0
关于聚集索引选择方案的疑问
以如下需求为例:

根据一段时间的网站日志生成访问记录表tblWebVisitLog,需记录以下信息:
访问时间: VisitTime
访问者IP: IP
访问的URL: URL
同一时刻可能有多条访问记录,即VisitTime不是唯一的。

数据量:3000万条记录左右

常见查询情况:
1. 在一个时间范围内,按时序列出指定IP访问的URL,即根据VisitTime和IP查找。
2. 在一个时间范围内,统计各个时段(每天/每小时)的访问量/IP数,即根据VisitTime聚集统计。
3. 在一个时间范围内,统计各个或指定URL的访问量/IP数,即根据VisitTime和URL聚集统计。

SQL语句:
SQL code
--用维度表存储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. 查询性能

或者有经验的大牛们能否分享一下关于聚集索引选择方案的心得?

以下为生成测试数据的脚本,仅供参考:
SQL code
--根据Nums表生成测试数据INSERT INTO tblURL(strURL)SELECT strURL = '/' + RIGHT('00000' + CAST(n AS varchar(10)),5) + '.html'FROM NumsWHERE n BETWEEN 1 AND 10000INSERT INTO tblWebVisitLog1SELECT    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 NumsWHERE n BETWEEN 1 AND 1000000  --只测试了100万条记录的情况INSERT INTO tblWebVisitLog2SELECT * FROM tblWebVisitLog1INSERT INTO tblWebVisitLog3SELECT * FROM tblWebVisitLog1


测试代码示例:
SQL code
--存储空间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 0xD5BCFFFFSELECT TOP(10) * FROM tblWebVisitLog2 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFFSELECT TOP(10) * FROM tblWebVisitLog3 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF



------解决方案--------------------
SQL code
这个有点艰难..---使用索引优化数据库查询效率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 idselect * 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')
  相关解决方案