《2022年SQLserver创建索引的意义 .pdf》由会员分享，可在线阅读，更多相关《2022年SQLserver创建索引的意义 .pdf（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、索引工作的意义题目： 理解索引的工作意义：创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？一,前期准备CREATE DATABASE Student GO USE Student GO - 创建成绩表createtable stu_grade ( stu_id intIDENTITY( 1, 1)PRIMARY KEY, written_score intnotnull, lab_score intno
2、tnull ) go - 创建记录数据录入所需时间表createtable data_insert_time ( mark intidentity( 1, 1), datavolume int, recrement int, Time_ms int, Time_ss float ) go - 创建维护索引所需时间表createtable maintain_index_time ( mark intidentity( 1, 1), datavolume int, Time_ms int, Time_ss float ) go - 创建记录未创建索引查询所需时间表createtable query
3、_time_unindex ( 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 12 页 - - - - - - - - - mark intidentity( 1, 1), datavolume int, Time_ms int, Time_ss float, Result int ) go - 创建记录创建索引后查询所需时间表createtable query_time_index ( mark intidentity( 1, 1), datavolume int,
4、Time_ms int, Time_ss float, Result int ) go - 创建插入数据的存储过程, 并计算插入数据所需时间, 同时记录所需插入时间- 分别创建下列存储过程- create proc proc_insert_40000 每插入 4 0000 - create proc proc_insert_200000 每插入 20 0000 - create proc proc_insert_1000000 每插入 100 0000 go createproc proc_insert_1000000 as DECLARE count int, account int, st
5、art_time datetime, end_time datetime select count = 0 , start_time = getdate() while( count 90 set result =rowcount select end_time = getdate(),count =( selectcount( stu_id)from stu_grade) insertinto query_time_unindex ( datavolume, Time_ms , Time_ss, Result) values( count , datediff( ms, start_time
6、, end_time), round ( convert( float, datediff ( ms, start_time, end_time)/1000 , 4), result) go - 创建创建索引器的存储过程, 并将创建索引器所需的时间记录新go createproc proc_create_index as declare start_time datetime, end_time datetime, count int set start_time = getdate() createnonclusteredindex id_written_index on stu_grade
7、( written_score, lab_score) withfillfactor= 40 select end_time = getdate(),count = ( selectcount( stu_id)from stu_grade) insertinto maintain_index_time ( datavolume, Time_ms , Time_ss) values( count , datediff( ms, start_time, end_time), round ( convert( float, datediff ( ms, start_time, end_time)/1
8、000 , 4) go - 创建索引后查询所需时间，并将查询所需时间记录新表go createproc proc_query_time_index as declare start_time datetime, end_time datetime, count int, result int set start_time = getdate() -waitfor delay 00:00:10 select* fromstu_grade where( written_score between80 and 90)andlab_score 90 set result= rowcount selec
9、t end_time = getdate(),count =( selectcount( stu_id)from stu_grade) -select start_time,end_time insertinto query_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
10、 select*from stu_grade 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 12 页 - - - - - - - - - (图1) 数据表的初始状态/* select * from data_insert_time select * from maintain_index_time select * from query_time_unindex select * from query_time_index */ ( 图 2)- 记录时间表的初始状态四个
11、表分别是 : 1,表记录数据的录入时间( 标记行 , 数据量行 , 数据增量行 , 时间毫秒记行 , 时间秒记行 ) 2,记录维护索引所需时间( 标记行 , 数据量行 , 时间毫秒记行 , 时间秒记行 ) 3, 记录未创建索引时查询所需时间( 标记行 , 数据量行 , 时间毫秒记行 , 时间秒记行 ) 4, 记录创建索引后查询所需时间( 标记行 , 数据量行 , 时间毫秒记行 , 时间秒记行 ) 二,数据的测试USE Student GO - 测试每插入 4 0000 *5行数据时 , declare n int, start_time datetime, end_time datetime
12、select start_time =getdate(),n = 0 while( n 5) begin exec proc_insert_40000 exec proc_query_time_unindex exec proc_create_index exec proc_query_time_index dropindex stu_grade. id_written_index set n = n+1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 12 页 - -
13、- - - - - - - 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_ss into new_table go - 测试每插入 20 0000*4 行go declare n int, start_time datetime, end_time datetime select start_time =getdate(),n = 0 w
14、hile( n 4) begin exec proc_insert_200000 exec proc_query_time_unindex exec proc_create_index exec proc_query_time_index dropindex stu_grade. id_written_index set n = n+1 end set end_time = getdate() insertinto new_table values( datediff( ms, start_time, end_time), round ( convert( float, datediff( m
15、s, start_time, end_time )/1000 , 4) go - 测试每插入 100 0000*9 行数据时go declare n int, start_time datetime, end_time datetime select start_time =getdate(),n = 0 while( n 9) begin exec proc_insert_1000000 exec proc_query_time_unindex exec proc_create_index exec proc_query_time_index dropindex stu_grade. id_
16、written_index set n = n+1 end set end_time = getdate() insertinto new_table values( datediff( ms, start_time, end_time), round ( convert( float, datediff( ms, s名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 12 页 - - - - - - - - - tart_time, end_time )/1000 , 4)
17、 go 数据录入后的查询表结果select*from data_insert_time ( 图3) - 记录数据的录入时间select*from maintain_index_time 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 12 页 - - - - - - - - - (图4) - 记录索引的维护时间select*from query_time_unindex (图5) - 记录未创建索引时查询所需时间select*from query_time_index名师
18、资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 12 页 - - - - - - - - - ( 图6) - 记录创建索引后查询所需时间表操作语句/* delete from stu_grade drop table stu_grade drop table data_insert_time drop table maintain_index_time drop table query_time_unindex drop table query_time_index 存储过程
19、及索引操作语句exec proc_insert_40000 exec proc_query_time_unindex exec proc_create_index exec proc_query_time_index drop proc proc_create_index drop proc proc_insert_1000000 drop index stu_grade.id_written_index 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 12 页 - -
20、- - - - - - - */ 数据综合分析语句Go createview view_analyse as select a . mark , a. datavolume, a. Time_ms - b. Time_ms asTime_difference_ms, a . Time_ss- b . Time_ss as Time_difference_ss from query_time_unindex a innerjoin query_time_index b on a . mark =b. mark 视图 view_analyse 用于测试创建索引前后查询数据所需的时间差 mark 标
21、记行 , datavolume 数据量行 , Time_difference_ms 时间差毫秒记 Time_difference_ss 时间差秒记select* from view_analyse -drop view view_analyse （图 7) - 创建索引前后查询时间差名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 12 页 - - - - - - - - - select a . mark , a. datavolume, a. Time_ms - b.
22、Time_ms asTime_difference_ms, a . Time_ss- b . Time_ss as Time_difference_ss,c. Time_ms , c. Time_ss from query_time_unindex a innerjoin query_time_index b on a . mark =b. mark innerjoin maintain_index_time c on a . mark = c . mark （图 8) - 创建索引前后查询时间差及维护索引时间当数据量都在1000 0000 时所需查询情况：（图 9） - 当数据量为 1000
23、 0000 查询时间差查看索引信息execsp_helpindex stu_grade 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 12 页 - - - - - - - - - execsp_spaceused （图 9) -索引信息- 显示指定数据表的数据和索引的碎片信息dbcc showcontig( stu_grade, id_written_index)（图 10） - 显示索引的碎片信息- 清除索引的上的碎片dbcc indexdefrag( Studen
24、t, stu_grade, id_written_index) （图 11）- 清除索引上的碎片三,总结从以上得出：一，当数据量相对较少时，创建索对提高系统的检索速度效率并不高，索引也占用的一定的物理存储空间二，当数据量相对适中（500 0000 左右）时，此时的创建的复合索引系统检索速度效率提高了不少。三，当数据量超过600 0000 时，复合索引对提高系统检索速度没有提高反而出现了下降现象。说明当数据量很大时而数据值又相对较少时，增加索引，并不能名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 12 页 - - - - - - - - - 明显加快检索速度，反而，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 （此题创建的时复合索引，如果不是复合索引，那效果？）四，索引的创建及维护随着数据量的增大，耗费的时间也会成倍的增加。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 12 页 - - - - - - - - -