数据库课件1-索引

显示查询消耗时间
declare @dd datetime set @dd=GETDATE() select 学号,姓名 from xuesheng where 学号='201710108' select [语句执行花费时间(毫秒)]=datediff(ms,@dd,getdate())
如：禁用名为 NonClu_Index 的索引
alter index NonClu_Index on Student disable
删除和查看索引：
--查看指定表 Student 中的索引 exec sp_helpindex xuesheng
--删除指定表 Student 中名为 Index_StuNo_SName 的索引 drop index xuesheng.Index_StuNo_SName --检查表 Student 中索引 UQ_S_StuNo 的碎片信息 dbcc showcontig(xuesheng,UQ_S_StuNo) --整理 Test 数据库中表 Student 的索引 UQ_S_StuNo 的碎片
复习
游标提供了对一个结果集进行逐行处理的能力，游标可看做一种特殊的指 针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定位置 的数据进行处理。
创建一个游标xs_cur100,结果集为教师名，学号，课程名，成绩，要指针可 以回滚，不需要修改任何数据。
1，声明游标 2，打开 3，分别使用last, 4，关闭游标 5，删除游标
创建非聚集复合索引：
--创建非聚集复合索引
create nonclustered index Index_StuNo_SName on xuesheng(S_StuNo,S_Name) with(drop_existing=on)
--创建非聚集复合索引，未指定默认为非聚集索引
create index Index_StuNo_SName on xuesheng(S_StuNo,S_Name) with(drop_existing=on)
--创建非聚集索引 create nonclustered index NonClu_Index
on Student(S_StuNo) with (drop_existing=on)
--创建唯一索引
create unique index NonClu_Index on Student(S_StuNo) with (drop_existing=on)
first, prior ，relative,absolute 读取数据。
索引
什么是索引？
索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可 以快速访问数据库表中的特定信息。数据库中的索引类似于一本书的目录， 在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。
索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建 少了，用 WHERE 子句找数据效率低，不利于查找数据。索引建多了，不利于新 增、修改和删除等操作，因为做这些操作时，SQL SERVER 除了要更新数据表本 身，还要连带立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。
--表示使用填充 --表示填充因子为50% --表示向唯一索引插入重复值会忽略重复值
--表示启用统计信息自动更新功能
)
当 create index 时，如果未指定 clustered 和 nonclustered，那么默 认为 nonclustered。
--创建聚集索引 create clustered index Clu_Index on Student(S_StuNo) with (drop_existing=on)
修改索引：
--修改索引语法 ALTER INDEX { 索引名| ALL } ON <表名|视图名> { REBUILD | DISABLE | REORGANIZE }
rebuild：表示指定重新生成索引。 disable：表示指定将索引标记为已禁用。 reorganize：表示指定将重新组织的索引叶级。
如何建索引？
1.不是越多越好，因为如果建立过多的索引，保存的速度就会下降 2.常更新的表越少越好，因为在字段中做更新（插入）操作后，索引也会更新的， 这样的话效率会大大降低 3.数据量小的表最好不要建立索引，因为小的表即使建立索引也不会有大的用处,还 会增加额外的索引开销 4.不同的值比较多的列才需要建立索引 5.某种数据本身具备唯一性的时候，建立唯一性索引，可以保证定义的列的数据完 整性，以提高查询熟度 6.频繁进行排序或分组的列(group by或者是order by)可以建立索引，提高搜索速度. 7.经常用于查询条件的字段应该建立索引不经常引用的列不要建立索引,因为不常用, 即使建立了索引也没有多大意义经常频繁更新的列不要建立索引,因为肯定会影响插 入或更新的效率索引并不是一劳永逸的,用的时间长了需要进行整理或者重建
dbcc indexdefrag(kecheng_db, xuesheng,UQ_S_StuNo) --更新表 Student 中的全部索引的统计信息 update statistics xuesheng
练习
根据下面的表规划索引
Shengdian(商店号、商店名、地址、联系电话) Shangpin(商品号、商品名、单价，类别) Xiaoshou(商品号, 商店号, 数量, 日期)
索引的优点、缺点？
索引的优点
1.通过创建唯一索引，可以保证数据库每一行数据的唯一性 2.可以大大提高查询速度 3.可以加速表与表的连接 4.可以显著的减少查询中分组和排序的时间。
索引的缺点
1.创建索引和维护索引需要时间，而且数据量越大时间越长 2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达 到最大文件尺寸 3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降 低了数据的维护速度
使用总结
什么时候需要创建索引
主键自动建立唯一索引 频繁作为查询条件的字段应该创建索引 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找 查询中统计或者分组的字段；
什么时候不需要创建索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新 索引，保存索引文件 where条件里用不到的字段，不创建索引； 表记录太少，不需要创建索引； 经常增删改的表； 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。 注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性 别字段，只有男女，不适合建立索引。
--索引名称
on xuesheng(学号)
--数据表名称（建立索引的列名）
with (
pad_index= on,
--表示使用填充
fillfactor=50, ignore_dup_key=on, statistics_norecompute=off
--表示填充因子为50% --表示向唯一索引插入重复值会忽略重复值
--表示启用统计信息自动更新功能
)
唯一索引可以是聚集索引也可以非聚集索引
创建唯一非聚集索引：
Create unique nonclustered
--表示创建唯一聚集索引
index uq_clu_x-数据表名称（建立索引的列名）
with (
pad_index= on, fillfactor=50, ignore_dup_key=on, statistics_norecompute=off
索引的分类
1．聚集索引（clustered）
聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表 物理顺序与索引顺序一致。
聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上 连续存在的，即拼音 a 过了后面肯定是 b 一样。
2．非聚集索引（ nonclustered ）
非聚集索引完全独立于数据行的结构。SQL Server 2008也是按B树方式组织 非聚集索引的，与聚集索引的不同之处在于：非聚集索引B树的叶节点不存放 数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含 该键值的数据行。
非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续， 物理存储并不连续。
一张表中，只能有一个聚集索引，但可以有多个非聚集索引
在具体的操作中，可以按照如下进行创建索引
创建唯一聚集索引：
-- 创建唯一聚集索引
Create unique clustered
--表示创建唯一聚集索引
index uq_clu_xuehao