SQL_server_创建索引的意义

索引工作的意义 题目：理解索引的工作意义：创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？ 一,前期准备 CREATE DATABASE Student GO USE Student GO -- 创建成绩表 create table stu_grade ( stu_id int IDENTITY(1,1) PRIMARY KEY, written_score int not null, lab_score int not null ) go -- 创建记录数据录入所需时间表 create table data_insert_time ( mark int identity(1,1), datavolume int, recrement int, Time_ms int, Time_ss float ) go -- 创建维护索引所需时间表 create table maintain_index_time ( mark int identity(1,1), datavolume int, Time_ms int, Time_ss float ) go --创建记录未创建索引查询所需时间表 create table query_time_unindex ( mark int identity(1,1), datavolume int, Time_ms int, Time_ss float, Result int ) go --创建记录创建索引后查询所需时间表 create table query_time_index ( mark int identity(1,1), datavolume int, 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 create proc proc_insert_1000000 as DECLARE @count int,@account int,@start_time datetime,@end_time datetime select @count = 0,@start_time = getdate() while (@count<1000000) begin insert into stu_grade (written_score,lab_score) values(floor(100*rand()),floor(100*rand())) set @count = @count + 1 end select @end_time = getdate(),@account=(select count(stu_id) from stu_grade) insert into data_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 create proc proc_query_time_unindex as declare @start_time datetime, @end_time datetime,@count int,@result int set @start_time = getdate() select * from stu_grade where (written_score between 80 and 90 )and lab_score >90 set @result = @@rowcount select @end_time = getdate(),@count=(select count(stu_id) from stu_grade) insert into query_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 create proc proc_create_index as declare @start_time datetime,@end_time datetime,@count int set @start_time = getdate() create nonclustered index id_written_index on stu_grade(written_score,lab_score) with fillfactor = 40 select @end_time = getdate(),@count = (select count(stu_id) from stu_grade) insert into 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))/1000,4)) go -- 创建索引后查询所需时间，并将查询所需时间记录新表 go create proc 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 * from stu_grade where (written_score between 80 and 90)and lab_score >90 set @result= @@rowcount select @end_time = getdate(),@count=(select count(stu_id) from stu_grade) --select @start_time,@end_time insert into 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 select * from stu_grade (图1) – 数据表的初始状态 /* select * from data_insert_time select * from maintain_index_time select * from query_time_unindex select * from query_time_index */ ( 图2)-- 记录时间表的初始状态 四个表分别是: 1,表记录数据的录入时间(标记行,数据量行,数据增量行,时间毫秒记行,时间秒记行) 2,记录维护索引所需时间(标记行,数据量行, 时间毫秒记行,时间秒记行) 3,记录未创建索引时查询所需时间(标记行,数据量行, 时间毫秒记行,时间秒记行) 4,记录创建索引后查询所需时间(标记行,数据量行, 时间毫秒记行,时间秒记行) 二,数据的测试 USE Student GO -- 测试每插入4 0000 *5行数据时, declare @n int,@start_time datetime,@end_time datetime 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 drop index stu_grade.id_written_index set @n= @n+1 end set @end_time = getdate() select datediff(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 while(@n<4) begin exec proc_insert_200000 exec proc_query_time_unindex exec proc_create_index exec proc_query_time_index drop index stu_grade.id_written_index set @n= @n+1 end set @end_time = getdate() insert into new_table values(datediff(ms,@start_time,@end_time),round(convert(float,datediff(ms,@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 drop index stu_grade.id_written_index set @n= @n+1 end set @end_time = getdate() insert into new_table values(datediff(ms,@start_time,@end_time),round(convert(float,datediff(ms,@start_time,@end_time))/1000,4)) go 数据录入后的查询表结果 select * from data_insert_time (图3) -- 记录数据的录入时间 select * from maintain_index_time (图4)—-记录索引的维护时间 select * from query_time_unindex (图5) -– 记录未创建索引时查询所需时间 select * from query_time_index (图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 存储过程及索引操作语句 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 */ 数据综合分析语句 Go create view view_analyse as select a.mark,a.datavolume,a.Time_ms-b.Time_ms as Time_difference_ms,a.Time_ss-b.Time_ss as Time_difference_ss from query_time_unindex a inner join query_time_index b on a.mark=b.mark 视图 view_analyse 用于测试创建索引前后查询数据所需的时间差 mark 标记行, datavolume 数据量行, Time_difference_ms 时间差毫秒记 Time_difference_ss 时间差秒记 select *from view_analyse --drop view view_analyse （图7)—-创建索引前后查询时间差 select a.mark,a.datavolume,a.Time_ms-b.Time_ms as Time_difference_ms,a.Time_ss-b.Time_ss as Time_difference_ss, c.Time_ms,c.Time_ss from query_time_unindex a inner join query_time_index b on a.mark=b.mark inner join maintain_index_time c on a.mark = c.mark （图8)—-创建索引前后查询时间差及维护索引时间 当数据量都在1000 0000 时所需查询情况： （图9） -- 当数据量为1000 0000 查询时间差 查看索引信息 exec sp_helpindex stu_grade exec sp_spaceused （图9)—-索引信息 --显示指定数据表的数据和索引的碎片信息 dbcc showcontig(stu_grade,id_written_index) （图10） -- 显示索引的碎片信息 --清除索引的上的碎片 dbcc indexdefrag(Student,stu_grade,id_written_index) （图11）-- 清除索引上的碎片 三, 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 从以上得出： 1， 当数据量相对较少时，创建索对提高系统的检索速度效率并不高，索引也占用的一定的物理存储空间 2， 当数据量相对适中（500 0000左右）时，此时的创建的复合索引系统检索速度效率提高了不少。 3， 当数据量超过600 0000时，复合索引对提高系统检索速度没有提高反而出现了下降现象。说明当数据量很大时而数据值又相对较少时，增加索引，并不能明显加快检索速度，反而，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。（此题创建的时复合索引，如果不是复合索引，那效果？？？） 4， 索引的创建及维护随着数据量的增大，耗费的时间也会成倍的增加。