千万数据的连续ID表,快速读取其中指定的某1000条数据？

有这样一个需求：一张上千万数据的表，结构很简单：ID是自增的，你怎么快速读取其中指定的某1000条数据，比如100万到100万零1000？

这个需求其实很简单，因为是自增型ID，可能分两种状况：有聚集索引或Heap，如果是后者，我想用ID和新增时间组建非聚集索引。效果应该相差不大。

于是动手，过程如下：

一、准备测试数据：

基本测试环境：

插入1000万测试数据：

/***************创建千万级测试数据库***********
****************downmoon 3w@live.cn ***************/

Create database HugeData_10Millons
go
use HugeData_10Millons
go

/***************创建测试表*********************
****************downmoo  3w@live.cn ***************/

IF NOT OBJECT_ID('[bigTable]') IS NULL
    DROP TABLE [bigTable]
GO
Create table bigTable
(PID int identity(1,1) primary key not null
,PName nvarchar(100) null
,AddTime dateTime null
,PGuid Nvarchar(40)
)
go

truncate table [bigTable]

/***************创建第一个25万测试数据*********************
****************downmoo  3w@live.cn ***************/

declare @d datetime 
set @d=getdate() 

declare @i int
set @i=1
while @i<=250000
begin
    insert into [bigTable]
    select cast(datepart(ms,getdate()) as nvarchar(3))+Replicate('A',datepart(ss,getdate()))
    ,getdate()
    ,NewID()
    set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 

/*
语句执行花费时间(毫秒)
94750
*/

/***************创建第二个25万测试数据*********************
****************downmoo  3w@live.cn ***************/

declare @d datetime 
set @d=getdate() 

declare @i int
set @i=1
while @i<=250000
begin
    insert into [bigTable]
    select cast(datepart(ms,getdate()) as nvarchar(3))+Replicate(Substring(cast(NEWID() as nvarchar(40)),1,6),3)
    ,getdate()
    ,NewID()
    set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 

/*
语句执行花费时间(毫秒)
115640
*/

/***************创建900万测试数据*********************
****************downmoo  3w@live.cn ***************/

declare @d datetime 
set @d=getdate() 

declare @i int
set @i=1
while @i<=9000000
begin
    insert into [bigTable]
    select replicate('X',ROUND((RAND()* 60),0) )+cast(datepart(ms,getdate()) as nvarchar(3))
    ,getdate()
    ,NewID()
    set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 
/*
语句执行花费时间(毫秒)
3813686
*/

/***************创建最后50万测试数据*********************
****************downmoo  3w@live.cn ***************/

declare @d datetime 
set @d=getdate() 

declare @i int
set @i=1
while @i<=500000
begin
    insert into [bigTable]
    select replicate('X',ROUND((RAND()* 60),0) )+cast(NewID() as nvarchar(40))
    ,getdate()
    ,NewID()
    set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 
/*
语句执行花费时间(毫秒)
207436
*/

/*
检查数量
select count(1) from dbo.bigTable
----------10000000
清除日志
DUMP TRANSACTION HugeData_10Millons WITH NO_LOG
BACKUP LOG HugeData_10Millons WITH NO_LOG
DBCC SHRINKDATABASE(HugeData_10Millons)

*/

完成后，数据文件大小如下：

二、创建一个存储过程用于测试：

/***************查中间某段1000条顺序数据*********************
****************downmoo  3w@live.cn ***************/
Create procedure GetTop1000RecordsByRange
(@begin int
,@end int
)
as 
select top 1000 * from [bigTable]
where PID between @begin and @end
go

邀月说明：其实，加不加top对查询并没有影响。后面的测试证实了这一点。因为将top　1000 去掉后，清除过程计划缓存，仍然得出相同的计划结果。

测试语句：

declare @d datetime 
set @d=getdate() 

exec GetTop100RecordsByRange 1000000,10001000

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 

此时，由于SQL Server默认为主键PID创建了聚集索引，查询速度比较理想，平均为0-16毫秒之间，更接近于0

查询计划也如我所料：

 而如果以Pguid作为聚集索引键，查询计划如下：

如果以AddTime作为聚集索引键，查询计划：

 三、修改聚集索引，以检查查询速度

/*删除系统自动创建的聚集索引
*/
ALTER TABLE [dbo].[bigTable] DROP CONSTRAINT [PK__bigTable__7C8480AE]
go


/*创建一个非聚集索引
在PID和addtime字段
*/
CREATE NONCLUSTERED INDEX bigTable_NoClusIdx
ON [bigTable]([AddTime] ASC,[PID] ASC);
go
DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]


/*创建一个非聚集索引
在PID字段
*/

Create NONCLUSTERED INDEX bigTable_NoclusIdx
ON [bigTable](PID);
go

DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]

/*创建一个非聚集索引
在AddTime字段
*/
CREATE NONCLUSTERED INDEX bigTable_NoclusIdx
ON [bigTable](AddTime);
go

DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]

/*创建一个非聚集索引
在GUID字段
*/

CREATE NONCLUSTERED INDEX bigTable_NoclusIdx
ON [bigTable](PGuid);
go

DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]


/*创建一个聚集索引
在GUID字段
*/

CREATE CLUSTERED INDEX bigTable_ClusIdx
ON [bigTable](PGuid);
go

DROP Index [bigTable_ClusIdx] on dbo.[bigTable]

/*创建一个聚集索引
在addTime字段
*/
CREATE CLUSTERED INDEX bigTable_ClusIdx
ON [bigTable](AddTime);
go

DROP Index [bigTable_ClusIdx] on dbo.[bigTable]


/*创建一个聚集索引
在PID字段
*/
CREATE CLUSTERED INDEX bigTable_ClusIdx
ON [bigTable](PID);
go

测试结果有些令我意外：

1、在没有聚集索引的前提下，无论在GUID,AddTime,PID创建非聚集索引，查询的速度均相差甚远。平均在200毫秒以上，并且此时每次查询均在10秒以上。这与查询的计划缓存有关。

2、在创建聚集索引时，性能PID>AddTime>PGuid,但总体相差不明显。

四、检查索引存储内部

使用微软未公开的一个命令DBCC IND

DBCC IND (HugeData_10Millons, bigTable, -1);

结果约有21万个数据页：(211985 row(s) affected)

 

为了更方便找出根页(Root Page),我们使用一个表来存放DBCC IND的查询结果：

IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL
    DROP TABLE sp_tablepages;
go

CREATE TABLE sp_tablepages
(
    PageFID         tinyint,
    PagePID         int,
    IAMFID          tinyint,
    IAMPID          int,
    ObjectID        int,
    IndexID         tinyint,
    PartitionNumber tinyint,
    PartitionID     bigint,
    iam_chain_type  varchar(30),
    PageType        tinyint,
    IndexLevel      tinyint,
    NextPageFID     tinyint,
    NextPagePID     int,
    PrevPageFID     tinyint,
    PrevPagePID     int,
    CONSTRAINT sp_tablepages_PK
        PRIMARY KEY (PageFID, PagePID)
);
go
--TRUNCATE TABLE sp_tablepages;
INSERT sp_tablepages
EXEC ('DBCC IND (HugeData_10Millons, bigTable, 1)');
go

SELECT IndexLevel
    , PageFID
    , PagePID
    , PrevPageFID
    , PrevPagePID
    , NextPageFID
    , NextPagePID
FROM sp_tablepages
ORDER BY IndexLevel DESC, PrevPagePID;
GO 

假定我们要找PID为100000的记录。附查找过程如下：

小结：

1、一个聚集索引的叶级正好就是数据自身，所以当一个聚集索引被创建时，表中数据被复制并依据聚集键排序，聚集索引被逻辑维护而不是物理维护。这样，查询时通过逻辑扫描可以很快找到某行所在的索引页，进而找出连续的1000条记录所在的页。

2、对于一个非聚集索引来说，如果是Heap，行的标识就是它们的物理行标识(RID)；如果是聚集表，则为聚集健，这个值称为书签值(bookmaark value)，它和索引键、包含性列一起组成了非聚集索引的叶级。另外，在B树查找非叶级的页时，将可能不得不通过指向子页的指针进行物理定位，这可能会增加查询的时间。还有，非聚集索引仅仅包含被索引定义的数据，对于没有在索引中定义的数据，可能需要在物理行进行一个书签查找(bookmark lookup)。

3、对于大数据量的查询，建立聚集索引是必须的。如果查询以ID序列为主，可以直接在标识列建立聚集索引。如果查询以时间段为主，则可以考虑用时间和标识列建聚集索引。

以上结论谨供参考，欢迎交流。

关于索引的物理存储与查询，请查看：《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(3)