软件开发的家园,编程爱好者的天地.

现在是:北京时间 2016/4/14 上午11:50:51 星期四

设为首页  |  加入收藏  |  网站地图

千万数据的连续ID表,快速读取其中指定的某1000条数据?
发布于:第八基地 来源:互联网 作者:天堂路上 时间:2016-04-25 点击:274

有这样一个需求:一张上千万数据的表,结构很简单: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作为聚集索引键,查询计划如下:

上一篇:没有了
对我有帮助
(0)
0%
对我没帮助
(0)
0%
返回顶部
在线反馈
在线反馈