SQL server 创建索引的意义.docx
《SQL server 创建索引的意义.docx》由会员分享，可在线阅读，更多相关《SQL server 创建索引的意义.docx（16页珍藏版）》请在冰豆网上搜索。
SQLserver创建索引的意义
索引工作的意义
题目：
理解索引的工作意义：
创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？
一,前期准备
CREATEDATABASEStudent
GO
USEStudent
GO
--创建成绩表
createtablestu_grade
（
stu_idintIDENTITY（1,1）PRIMARYKEY,
written_scoreintnotnull,
lab_scoreintnotnull
）
go
--创建记录数据录入所需时间表
createtabledata_insert_time
（
markintidentity（1,1）,
datavolumeint,
recrementint,
Time_msint,
Time_ssfloat
）
go
--创建维护索引所需时间表
createtablemaintain_index_time
（
markintidentity（1,1）,
datavolumeint,
Time_msint,
Time_ssfloat
）
go
--创建记录未创建索引查询所需时间表
createtablequery_time_unindex
（
markintidentity（1,1）,
datavolumeint,
Time_msint,
Time_ssfloat,
Resultint
）
go
--创建记录创建索引后查询所需时间表
createtablequery_time_index
（
markintidentity（1,1）,
datavolumeint,
Time_msint,
Time_ssfloat,
Resultint
）
go
--创建插入数据的存储过程,并计算插入数据所需时间,同时记录所需插入时间
--分别创建下列存储过程
--createprocproc_insert_40000每插入40000
--createprocproc_insert_200000每插入200000
--createprocproc_insert_1000000每插入1000000
go
createprocproc_insert_1000000
as
DECLARE@countint,@accountint,@start_timedatetime,@end_timedatetime
select@count=0,@start_time=getdate（）
while（@count<1000000）
begin
insertintostu_grade（written_score,lab_score）values（floor（100*rand（））,floor（100*rand（）））
set@count=@count+1
end
select@end_time=getdate（）,@account=（selectcount（stu_id）fromstu_grade）
insertintodata_insert_time（datavolume,recrement,Time_ms,Time_ss）
values（@account,@count,datediff（ms,@start_time,@end_time）,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4））
go
--创建未建索引时所需查询时间的存储过程,并获取所需查询时间记录新表
go
createprocproc_query_time_unindex
as
declare@start_timedatetime,@end_timedatetime,@countint,@resultint
set@start_time=getdate（）
select*fromstu_gradewhere（written_scorebetween80and90）andlab_score>90
set@result=@@rowcount
select@end_time=getdate（）,@count=（selectcount（stu_id）fromstu_grade）
insertintoquery_time_unindex（datavolume,Time_ms,Time_ss,Result）
values（@count,datediff（ms,@start_time,@end_time）,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4）,@result）
go
--创建创建索引器的存储过程,并将创建索引器所需的时间记录新
go
createprocproc_create_index
as
declare@start_timedatetime,@end_timedatetime,@countint
set@start_time=getdate（）
createnonclusteredindexid_written_index
onstu_grade（written_score,lab_score）
withfillfactor=40
select@end_time=getdate（）,@count=（selectcount（stu_id）fromstu_grade）
insertintomaintain_index_time（datavolume,Time_ms,Time_ss）
values（@count,datediff（ms,@start_time,@end_time）,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4））
go
--创建索引后查询所需时间，并将查询所需时间记录新表
go
createprocproc_query_time_index
as
declare@start_timedatetime,@end_timedatetime,@countint,@resultint
set@start_time=getdate（）
--waitfordelay'00:
00:
10'
select*fromstu_gradewhere（written_scorebetween80and90）andlab_score>90
set@result=@@rowcount
select@end_time=getdate（）,@count=（selectcount（stu_id）fromstu_grade）
--select@start_time,@end_time
insertintoquery_time_index（datavolume,Time_ms,Time_ss,Result）
values（@count,datediff（ms,@start_time,@end_time）,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4）,@result）
go
select*fromstu_grade
（图1）–数据表的初始状态
/*
select*fromdata_insert_time
select*frommaintain_index_time
select*fromquery_time_unindex
select*fromquery_time_index
*/
（图2）--记录时间表的初始状态
四个表分别是:
1,表记录数据的录入时间（标记行,数据量行,数据增量行,时间毫秒记行,时间秒记行）
2,记录维护索引所需时间（标记行,数据量行,时间毫秒记行,时间秒记行）
3,记录未创建索引时查询所需时间（标记行,数据量行,时间毫秒记行,时间秒记行）
4,记录创建索引后查询所需时间（标记行,数据量行,时间毫秒记行,时间秒记行）
二,数据的测试
USEStudent
GO
--测试每插入40000*5行数据时,
declare@nint,@start_timedatetime,@end_timedatetime
select@start_time=getdate（）,@n=0
while（@n<5）
begin
execproc_insert_40000
execproc_query_time_unindex
execproc_create_index
execproc_query_time_index
dropindexstu_grade.id_written_index
set@n=@n+1
end
set@end_time=getdate（）
selectdatediff（ms,@start_time,@end_time）Time_ms,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4）Time_ssintonew_table
go
--测试每插入200000*4行
go
declare@nint,@start_timedatetime,@end_timedatetime
select@start_time=getdate（）,@n=0
while（@n<4）
begin
execproc_insert_200000
execproc_query_time_unindex
execproc_create_index
execproc_query_time_index
dropindexstu_grade.id_written_index
set@n=@n+1
end
set@end_time=getdate（）
insertintonew_table
values（datediff（ms,@start_time,@end_time）,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4））
go
--测试每插入1000000*9行数据时
go
declare@nint,@start_timedatetime,@end_timedatetime
select@start_time=getdate（）,@n=0
while（@n<9）
begin
execproc_insert_1000000
execproc_query_time_unindex
execproc_create_index
execproc_query_time_index
dropindexstu_grade.id_written_index
set@n=@n+1
end
set@end_time=getdate（）
insertintonew_table
values（datediff（ms,@start_time,@end_time）,round（convert（float,datediff（ms,@start_time,@end_time））/1000,4））
go
数据录入后的查询表结果
select*fromdata_insert_time
（图3）--记录数据的录入时间
select*frommaintain_index_time
（图4）—-记录索引的维护时间
select*fromquery_time_unindex
（图5）-–记录未创建索引时查询所需时间
select*fromquery_time_index
（图6）--记录创建索引后查询所需时间
表操作语句
/*
deletefromstu_grade
droptablestu_grade
droptabledata_insert_time
droptablemaintain_index_time
droptablequery_time_unindex
droptablequery_time_index
存储过程及索引操作语句
execproc_insert_40000
execproc_query_time_unindex
execproc_create_index
execproc_query_time_index
dropprocproc_create_index
dropprocproc_insert_1000000
dropindexstu_grade.id_written_index
*/
数据综合分析语句
Go
createviewview_analyse
as
selecta.mark,a.datavolume,a.Time_ms-b.Time_msasTime_difference_ms,a.Time_ss-b.Time_ssasTime_difference_ss
fromquery_time_unindexainnerjoinquery_time_indexbona.mark=b.mark
视图view_analyse用于测试创建索引前后查询数据所需的时间差
mark标记行,
datavolume数据量行,
Time_difference_ms时间差毫秒记
Time_difference_ss时间差秒记
select*fromview_analyse
--dropviewview_analyse
（图7）—-创建索引前后查询时间差
selecta.mark,a.datavolume,a.Time_ms-b.Time_msasTime_difference_ms,a.Time_ss-b.Time_ssasTime_difference_ss,c.Time_ms,c.Time_ss
fromquery_time_unindexainnerjoinquery_time_indexbona.mark=b.markinnerjoinmaintain_index_timecona.mark=c.mark
（图8）—-创建索引前后查询时间差及维护索引时间
当数据量都在10000000时所需查询情况：
（图9）--当数据量为10000000查询时间差
查看索引信息
execsp_helpindexstu_grade
execsp_spaceused
（图9）—-索引信息
--显示指定数据表的数据和索引的碎片信息
dbccshowcontig（stu_grade,id_written_index）
（图10）--显示索引的碎片信息
--清除索引的上的碎片
dbccindexdefrag（Student,stu_grade,id_written_index）
（图11）--清除索引上的碎片
三,总结
从以上得出：
一，当数据量相对较少时，创建索对提高系统的检索速度效率并不高，索引也占用的一定的物理存储空间
二，当数据量相对适中（5000000左右）时，此时的创建的复合索引系统检索速度效率提高了不少。
三，当数据量超过6000000时，复合索引对提高系统检索速度没有提高反而出现了下降现象。
说明当数据量很大时而数据值又相对较少时，增加索引，并不能明显加快检索速度，反而，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
（此题创建的时复合索引，如果不是复合索引，那效果？
？
？
）
四，索引的创建及维护随着数据量的增大，耗费的时间也会成倍的增加。
升级会员 