有这样一个需求:一张上千万数据的表,结构很简单: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对查询并没有影响。后面的测试证实了这一点。
测试语句:
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)
助人等于自助! 3w@live.cn