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

 

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

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

于是动手，过程如下：

一、准备测试数据：

基本测试环境：

插入1000万测试数据：

/***************创建千万级测试数据库***************************downmoon [e-mail] ***************/Create database HugeData_10Millonsgouse HugeData_10Millonsgo/***************创建测试表*************************************downmoo  [e-mail] ***************/IF NOT OBJECT_ID('[bigTable]') IS NULL    DROP TABLE [bigTable]GOCreate table bigTable(PID int identity(1,1) primary key not null,PName nvarchar(100) null,AddTime dateTime null,PGuid Nvarchar(40))gotruncate table [bigTable]/***************创建第一个25万测试数据*************************************downmoo  [e-mail] ***************/declare @d datetime set @d=getdate() declare @i intset @i=1while @i<=250000begin    insert into [bigTable]    select cast(datepart(ms,getdate()) as nvarchar(3))+Replicate('A',datepart(ss,getdate()))    ,getdate()    ,NewID()    set @i=@i+1endselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) /*语句执行花费时间(毫秒)94750*//***************创建第二个25万测试数据*************************************downmoo  [e-mail] ***************/declare @d datetime set @d=getdate() declare @i intset @i=1while @i<=250000begin    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+1endselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) /*语句执行花费时间(毫秒)115640*//***************创建900万测试数据*************************************downmoo  [e-mail] ***************/declare @d datetime set @d=getdate() declare @i intset @i=1while @i<=9000000begin    insert into [bigTable]    select replicate('X',ROUND((RAND()* 60),0) )+cast(datepart(ms,getdate()) as nvarchar(3))    ,getdate()    ,NewID()    set @i=@i+1endselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) /*语句执行花费时间(毫秒)3813686*//***************创建最后50万测试数据*************************************downmoo  [e-mail] ***************/declare @d datetime set @d=getdate() declare @i intset @i=1while @i<=500000begin    insert into [bigTable]    select replicate('X',ROUND((RAND()* 60),0) )+cast(NewID() as nvarchar(40))    ,getdate()    ,NewID()    set @i=@i+1endselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) /*语句执行花费时间(毫秒)207436*//*检查数量select count(1) from dbo.bigTable----------10000000清除日志DUMP TRANSACTION HugeData_10Millons WITH NO_LOGBACKUP LOG HugeData_10Millons WITH NO_LOGDBCC SHRINKDATABASE(HugeData_10Millons)*/
完成后，数据文件大小如下：

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

/***************查中间某段1000条顺序数据*************************************downmoo [e-mail] ***************/Create procedure GetTop1000RecordsByRange(@begin int,@end int)as select top 1000 * from [bigTable]where PID between @begin and @endgo

 

邀月说明：其实，加不加top对查询并没有影响。后面的测试证实了这一点。

测试语句：

declare @d datetime set @d=getdate() exec GetTop100RecordsByRange 1000000,10001000select [语句执行花费时间(毫秒)]=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_NoClusIdxON [bigTable]([AddTime] ASC,[PID] ASC);goDROP Index [bigTable_NoClusIdx] on dbo.[bigTable]/*创建一个非聚集索引在PID字段*/Create NONCLUSTERED INDEX bigTable_NoclusIdxON [bigTable](PID);goDROP Index [bigTable_NoClusIdx] on dbo.[bigTable]/*创建一个非聚集索引在AddTime字段*/CREATE NONCLUSTERED INDEX bigTable_NoclusIdxON [bigTable](AddTime);goDROP Index [bigTable_NoClusIdx] on dbo.[bigTable]/*创建一个非聚集索引在GUID字段*/CREATE NONCLUSTERED INDEX bigTable_NoclusIdxON [bigTable](PGuid);goDROP Index [bigTable_NoClusIdx] on dbo.[bigTable]/*创建一个聚集索引在GUID字段*/CREATE CLUSTERED INDEX bigTable_ClusIdxON [bigTable](PGuid);goDROP Index [bigTable_ClusIdx] on dbo.[bigTable]/*创建一个聚集索引在addTime字段*/CREATE CLUSTERED INDEX bigTable_ClusIdxON [bigTable](AddTime);goDROP Index [bigTable_ClusIdx] on dbo.[bigTable]/*创建一个聚集索引在PID字段*/CREATE CLUSTERED INDEX bigTable_ClusIdxON [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;goCREATE 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_tablepagesEXEC ('DBCC IND (HugeData_10Millons, bigTable, 1)');goSELECT IndexLevel , PageFID , PagePID , PrevPageFID , PrevPagePID , NextPageFID , NextPagePIDFROM sp_tablepagesORDER 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)

 

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]