千万数据的连续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的记录。附查找过程如下:

邀月工作室
http://nfnhgq.blu.livefilestore.com/y1p6LlHd2IySTDYOYmb31ic0ix6cKEh160UDrt_iS5eQRUDVbGpD7QOpgqHpHM9cdIsWKn08i5wU-8Y5cxvR_cKrLddPXBX1Z8r/2010-08-27%2017-35-41.png?psid=1

邀月工作室

邀月工作室

小结:

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

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

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

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

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


posted @ 2010-08-28 00:20  邀月  阅读(4536)  评论(26编辑  收藏  举报