5-索引的创建和使用

1第5章索引的创建和使用索引：是一种与数据表相关的类似于目录的一种数据结构。使用索引可以提高查询的效率。一、索引概述1、索引提高查询效率的原理（1）索引它包含由列生成的键值+数据页地址的指针。（2）索引的键值是排序的。排序的数据可以利用各种高效的查找算法（折半查找等）。2、索引的优点（索引的功能，为什么要使用索引？）（1）提高查询效率。（2）提高表连接的效率。（3）唯一索引还可以保证记录唯一。（需要唯一的字段，可以使用唯一索引，以限制非法的数据）。3、索引的缺点（使用索引带来的问题？）索引可以极大提高查询效率，但并不是索引创建的越多越好。因为：（1）索引需要额外的维护时间--导致插入数据、更新数据需要更多的时间。（2）索引需要额外的存储空间。4、使用索引的原则科学的设计索引，在提高查询效率同时，尽量减少索引带来的副作用。（1）考虑设置索引的情况经常检索的列-WHERE子句中使用的列。主键列、外键列-事实上主键约束列、唯一约束列会自动创建索引。经常用于表间连接的列。（2）不考虑设置索引的情况-体现不出索引的优势，可能反而会降低时空效率。检索中几乎不涉及到的列。2重复值太多的列。数据类型未text、image的列。行数极少的表没必要创建索引。-大马拉小车！插入、更新效率比查询效率更重要的情况。5、索引的分类（1）聚集索引/非聚集索引聚集索引的数据页按照键值重新排序-查询速度非常快，维护时间相对长。一个表只能有一个聚集索引（非聚集索引最多可以有249个）。非聚集索引不改变数据页位置，非聚集索引是键值+指针，查询速度较聚集索引慢一些。（2）唯一性索引/非唯一性索引唯一索引：索引值不重复、键值也不重复。唯一索引可以限制重复列数据的输入。（3）单字段索引、复合索引※复合索引-索引键值的字段有多个。复合索引最多包含16个字段。二、索引的创建SQLSERVER索引可以由系统自动创建，也可以根据需要手工创建。使用CREATETABLE/ALTERTABLE定义主键、唯一性约束时，系统自动创建与约束名同名的索引。使用企业管理器或者T-SQL语句手工创建（指定索引名、表、列，其它索引选项等，默认创建非聚集非唯一索引）。1、使用企业管理器创建索引例：为Product表增加一个名称为C_P_Index的复合索引，索引字段是Category、Price。企业管理器-数据库服务器-数据库sales-表Product-索引（右键）-“新建索引…”3图5-1图5-2指定索引名；指定列名（可以设置排序方式，也可以调整列的顺序）；检查索引类型（聚集、唯一）；检查其它索引选项，“确定”。注：选中索引右键-属性/删除-可以修改索引或者删除索引。42、使用T-SQL语句创建索引（CREATEINDEX）语法格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(列名1[ASC|DESC][，……n])WITH（索引相关选项）索引选项：={PAD_INDEX={ON|OFF}|FILLFACTOR=填充因子|DROP_EXISTING={ON|OFF}}说明：UNIQUE：唯一索引。索引值唯一，索引的列值（或列组合）唯一。缺省：非唯一。CLUSETERD/NONCLUSETERD：聚集/非聚集索引。默认非聚集索引。DROPEXISTING：是否删除现有的同名的索引。ON-删除；默认OFF-不删除（遇到创建同名的索引会提示错误）。使用带DROPEXISTING=ON的CREATEINDEX语句可以修改一个索引的定义，例如，可以指定不同的列、排序顺序、分区方案或索引选项。例5-1：在产品表Product的产品编号列ProductID上创建唯一、聚集索引。例5-2：在产品表Product的产品类别CategoryID、价格列Price上创建非唯一、非聚集索引。三、索引的管理1、使用企业管理器查看、修改、删除索引（两种方法）（1）表-表设计器-“索引和键”快捷菜单-打开“索引和键”对话框。在这个对话框完成索引的查看、修改、删除。5图5-3（2）表-索引-找到需要查看的索引、右键快捷菜单-属性（查看、修改）；右键快捷菜单-删除（删除选中的索引）图5-42、使用T-SQL命令查看、修改、删除索引（1）查看索引信息--查看一张表上所有索引的信息。语法：sp_helpindex[表名|视图名]6例5-3：查看Product表上所有索引的信息sp_helpindexProduct（2）修改索引。语法：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(列名1[ASC|DESC][，……n])WITH（DROPEXISTING=ON）使用带DROPEXISTING=ON的CREATEINDEX语句可以修改一个索引的定义，例如，可以指定不同的列、排序顺序、分区方案或索引选项。例5-4A：修改索引（使用带DROP_EXISTING选项的CREATEINDEX命令）本例：修改了索引字段CategoryID,Price-CategoryID,stocks此外：修改索引名称的语法：sp_rename对象名，新的名称[，对象类别]例5-4B：修改索引名称(注意索引名需要表名限定)（3）删除索引语法：DROPINDEX表名.索引名注意：索引名一定要用表名限定。例5-5：删除索引(注意索引名需要表名限定)