SQLserver创建索引的意义

索引工作的意义题目：理解索引的工作意义：创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？一,前期准备CREATEDATABASEStudentGOUSEStudentGO--创建成绩表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每插入1000000gocreateprocproc_insert_1000000asDECLARE@countint,@accountint,@start_timedatetime,@end_timedatetimeselect@count=0,@start_time=getdate()while(@count1000000)begininsertintostu_grade(written_score,lab_score)values(floor(100*rand()),floor(100*rand()))set@count=@count+1endselect@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--创建未建索引时所需查询时间的存储过程,并获取所需查询时间记录新表gocreateprocproc_query_time_unindexasdeclare@start_timedatetime,@end_timedatetime,@countint,@resultintset@start_time=getdate()select*fromstu_gradewhere(written_scorebetween80and90)andlab_score90set@result=@@rowcountselect@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--创建创建索引器的存储过程,并将创建索引器所需的时间记录新gocreateprocproc_create_indexasdeclare@start_timedatetime,@end_timedatetime,@countintset@start_time=getdate()createnonclusteredindexid_written_indexonstu_grade(written_score,lab_score)withfillfactor=40select@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--创建索引后查询所需时间，并将查询所需时间记录新表gocreateprocproc_query_time_indexasdeclare@start_timedatetime,@end_timedatetime,@countint,@resultintset@start_time=getdate()--waitfordelay'00:00:10'select*fromstu_gradewhere(written_scorebetween80and90)andlab_score90set@result=@@rowcountselect@end_time=getdate(),@count=(selectcount(stu_id)fromstu_grade)--select@start_time,@end_timeinsertintoquery_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)goselect*fromstu_grade(图1)–数据表的初始状态/*select*fromdata_insert_timeselect*frommaintain_index_timeselect*fromquery_time_unindexselect*fromquery_time_index*/(图2)--记录时间表的初始状态四个表分别是:1,表记录数据的录入时间(标记行,数据量行,数据增量行,时间毫秒记行,时间秒记行)2,记录维护索引所需时间(标记行,数据量行,时间毫秒记行,时间秒记行)3,记录未创建索引时查询所需时间(标记行,数据量行,时间毫秒记行,时间秒记行)4,记录创建索引后查询所需时间(标记行,数据量行,时间毫秒记行,时间秒记行)二,数据的测试USEStudentGO--测试每插入40000*5行数据时,declare@nint,@start_timedatetime,@end_timedatetimeselect@start_time=getdate(),@n=0while(@n5)beginexecproc_insert_40000execproc_query_time_unindexexecproc_create_indexexecproc_query_time_indexdropindexstu_grade.id_written_indexset@n=@n+1endset@end_time=getdate()selectdatediff(ms,@start_time,@end_time)Time_ms,round(convert(float,datediff(ms,@start_time,@end_time))/1000,4)Time_ssintonew_tablego--测试每插入200000*4行godeclare@nint,@start_timedatetime,@end_timedatetimeselect@start_time=getdate(),@n=0while(@n4)beginexecproc_insert_200000execproc_query_time_unindexexecproc_create_indexexecproc_query_time_indexdropindexstu_grade.id_written_indexset@n=@n+1endset@end_time=getdate()insertintonew_tablevalues(datediff(ms,@start_time,@end_time),round(convert(float,datediff(ms,@start_time,@end_time))/1000,4))go--测试每插入1000000*9行数据时godeclare@nint,@start_timedatetime,@end_timedatetimeselect@start_time=getdate(),@n=0while(@n9)beginexecproc_insert_1000000execproc_query_time_unindexexecproc_create_indexexecproc_query_time_indexdropindexstu_grade.id_written_indexset@n=@n+1endset@end_time=getdate()insertintonew_tablevalues(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_gradedroptablestu_gradedroptabledata_insert_timedroptablemaintain_index_timedroptablequery_time_unindexdroptablequery_time_index存储过程及索引操作语句execproc_insert_40000execproc_query_time_unindexexecproc_create_indexexecproc_query_time_indexdropprocproc_creat