千万数据的连续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)

转载于:https://www.cnblogs.com/downmoon/archive/2010/08/28/1810306.html