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

最新推荐文章于 2022-09-09 09:48:57 发布

iteye_19871

最新推荐文章于 2022-09-09 09:48:57 发布

阅读量181

点赞数

有这样一个需求：一张上千万数据的表，结构很简单：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