SQL server 创建索引的意义.docx

1、SQL server 创建索引的意义索引工作的意义题目：理解索引的工作意义：创建一个成绩表，在成绩表中插入几万条记录，尝试执行某个关于笔试成绩的查询，计算出执行该查询的执行时间；然后在笔试（字段）建立索引后，再执行相同的查询，比较这两次（索引建立前后的执行时间）来理解索引创建的意义，将中间的执行过程，你的思路、截图？一,前期准备CREATE DATABASE StudentGOUSE StudentGO- 创建成绩表create table stu_grade( stu_id int IDENTITY(1,1) PRIMARY KEY, written_score int not null,

2、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

3、 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_in

4、sert_40000 每插入4 0000- create proc proc_insert_200000 每插入20 0000- create proc proc_insert_1000000 每插入100 0000gocreate proc proc_insert_1000000asDECLARE count int,account int,start_time datetime,end_time datetimeselect count = 0,start_time = getdate()while (count90set result = rowcountselect end_time

5、= 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- 创建创建索引器的存储过程,并将创建索引器所需的时间记录新gocreate proc proc_create_indexas

6、declare start_time datetime,end_time datetime,count intset start_time = getdate()create nonclustered index id_written_indexon stu_grade(written_score,lab_score)with fillfactor = 40select end_time = getdate(),count = (select count(stu_id) from stu_grade)insert into maintain_index_time (datavolume,Tim

7、e_ms,Time_ss)values(count,datediff(ms,start_time,end_time),round(convert(float,datediff(ms,start_time,end_time)/1000,4)go- 创建索引后查询所需时间，并将查询所需时间记录新表gocreate proc proc_query_time_index asdeclare start_time datetime,end_time datetime,count int,result intset start_time = getdate()-waitfor delay 00:00:10

8、select * from stu_grade where (written_score between 80 and 90)and lab_score 90set result= rowcountselect end_time = getdate(),count=(select count(stu_id) from stu_grade)-select start_time,end_timeinsert into query_time_index (datavolume,Time_ms,Time_ss,Result)values(count,datediff(ms,start_time,end

9、_time),round(convert(float,datediff(ms,start_time,end_time)/1000,4),result)go select * from stu_grade (图1) 数据表的初始状态/*select * from data_insert_timeselect * from maintain_index_timeselect * from query_time_unindexselect * from query_time_index*/( 图2)- 记录时间表的初始状态 四个表分别是: 1,表记录数据的录入时间(标记行,数据量行,数据增量行,时间

10、毫秒记行,时间秒记行) 2,记录维护索引所需时间(标记行,数据量行, 时间毫秒记行,时间秒记行) 3,记录未创建索引时查询所需时间(标记行,数据量行, 时间毫秒记行,时间秒记行) 4,记录创建索引后查询所需时间(标记行,数据量行, 时间毫秒记行,时间秒记行)二,数据的测试USE StudentGO- 测试每插入4 0000 *5行数据时,declare n int,start_time datetime,end_time datetimeselect start_time = getdate(),n = 0while(n5)begin exec proc_insert_40000 exec p

11、roc_query_time_unindex exec proc_create_index exec proc_query_time_index drop index stu_grade.id_written_index set n= n+1endset 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_tablego- 测试每插入20 0000*4 行g

12、odeclare n int,start_time datetime,end_time datetimeselect start_time = getdate(),n = 0while(n4)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+1endset end_time = getdate()insert into new_tabl

13、e values(datediff(ms,start_time,end_time),round(convert(float,datediff(ms,start_time,end_time)/1000,4)go- 测试每插入100 0000*9 行数据时godeclare n int,start_time datetime,end_time datetimeselect start_time = getdate(),n = 0while(n9)begin exec proc_insert_1000000 exec proc_query_time_unindex exec proc_create_

14、index exec proc_query_time_index drop index stu_grade.id_written_index set n= n+1endset 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 *

15、 from maintain_index_time (图4)-记录索引的维护时间select * from query_time_unindex (图5) - 记录未创建索引时查询所需时间select * from query_time_index (图6) - 记录创建索引后查询所需时间 表操作语句/*delete from stu_gradedrop table stu_gradedrop table data_insert_timedrop table maintain_index_timedrop table query_time_unindexdrop table query_tim

16、e_index 存储过程及索引操作语句exec proc_insert_40000exec proc_query_time_unindexexec proc_create_indexexec proc_query_time_indexdrop proc proc_create_indexdrop proc proc_insert_1000000drop index stu_grade.id_written_index*/数据综合分析语句Gocreate view view_analyseasselect a.mark,a.datavolume,a.Time_ms-b.Time_ms as Ti

17、me_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)-

18、创建索引前后查询时间差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_ssfrom 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

19、 0000 时所需查询情况： （图9） - 当数据量为1000 0000 查询时间差查看索引信息exec sp_helpindex stu_gradeexec sp_spaceused（图9)-索引信息-显示指定数据表的数据和索引的碎片信息dbcc showcontig(stu_grade,id_written_index) （图10） - 显示索引的碎片信息-清除索引的上的碎片dbcc indexdefrag(Student,stu_grade,id_written_index) （图11）- 清除索引上的碎片三,总结 从以上得出：一， 当数据量相对较少时，创建索对提高系统的检索速度效率并不高，索引也占用的一定的物理存储空间二， 当数据量相对适中（500 0000左右）时，此时的创建的复合索引系统检索速度效率提高了不少。三， 当数据量超过600 0000时，复合索引对提高系统检索速度没有提高反而出现了下降现象。说明当数据量很大时而数据值又相对较少时，增加索引，并不能明显加快检索速度，反而，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。（此题创建的时复合索引，如果不是复合索引，那效果？）四， 索引的创建及维护随着数据量的增大，耗费的时间也会成倍的增加。