关于聚集索引选择方案的疑问解决办法-MSSQL教程-爱易网页

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

日期：2014-05-19　浏览次数：22503 次

关于聚集索引选择方案的疑问
以如下需求为例：

根据一段时间的网站日志生成访问记录表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 Nums
WHERE n BETWEEN 1 AND 10000

INSERT 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 tblWebVisitLog1

INSERT INTO tblWebVisitLog3
SELECT * 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 0xD5BCFFFF
SELECT TOP(10) * FROM tblWebVisitLog2 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF
SELECT TOP(10) * FROM tblWebVisitLog3 WHERE binIP BETWEEN 0xD5BC0000 AND 0xD5BCFFFF

------解决方案--------------------

SQL code

这个有点艰难

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>