数据库基础PPT第6章 索引及其应用

第6章索引及其应用6.1索引概述6.2创建索引6.3管理和维护索引6.4全文索引Page129十月2023教学要求：通过本章学习，读者应掌握以下内容：索引的概念和功能；使用对象资源管理器和T-SQL命令两种方式创建、修改、删除索引的方法；全文索引的定义与使用。Page229十月20236.1索引概述索引是一个列表，这个列表中包含了某个表中一列或者若干列的集合，以及这些值的记录在数据表中存储位置的物理地址。6.1.1索引的功能使用索引可以大大提高系统的性能，其具体表现在：(1)加快数据查询(2)加快表的连接、排序和分组工作(3)索引能提高WHERE语句提取数据的速度，也能提高更新和删除数据记录的速度。(4)可以确保数据的唯一性。Page329十月20236.1.2创建索引的原则建立索引的一般原则是：（1）对经常用来搜索数据记录的字段建立索引。（2）对表中的主键字段建立索引。（3）对表中的外键字段建立索引。（4）对在查询中用来连接表的字段建立索引。（5）对经常用来作为排序基准的字段建立索引。Page429十月20236.1.3索引的分类从不同的角度，对索引的类型有不同的划分方法。按存储结构区分，有聚集索引和非聚集索引；按数据的惟一性来区分，有惟一索引和非惟一索引；按键列的个数区分，有单列索引和多列索引。1.聚集索引和非聚集索引聚集索引（ClusteredIndex）对表在物理数据页中的数据按列进行排序，然后再重新存储到磁盘上。由于表中的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL会用主键作为聚集索引。Page529十月2023与聚集索引不同的是，非聚集索引（NonClusteredIndex）尽管包含按升序排列的键值，但它丝毫不影响表中数据记录实际排列的顺序。当针对表执行以下操作时，SQL会自动重建此表所有现存的非聚集索引：（1）将表的聚集索引删除。（2）为表创建一个聚集索引。（3）更改聚集索引的键列。所以在创建非聚集索引之前，应先创建聚集索引。创建了聚集索引的表上执行查询操作比只创建了非聚集索引的表上执行查询速度快，但是，执行修改操作则比只创建了非聚集索引的表上执行的速度慢，这是因为表数据的改变需要更多的时间来维护聚集索引。一个表最多能够拥有249个非聚集索引。Page629十月20232．惟一索引和非惟一索引惟一索引要求所有数据行中任意两行中的被索引列或索引列组合不能存在重复值，包括不能有两个空值NULL，而非惟一索引(NonUniqueIndex)则不存在这样的限制。聚集索引和非聚集索引都可以是一个惟一索引或非惟一索引。3．单列索引和多列索引单列索引是指为某单一字段创建索引；多列索引则是为多个字段的组合创建索引。多列索引也叫复合索引，适用以下几种情况：（1）当两个或两个以上的字段组合在一起为最佳的搜索键值时，就非常适合为这些字段的组合创建一个多列索引。（2）当查询所引用的字段均是索引的键列时，应该为这些字段的组合创建一个多列索引。覆盖查询是一个最典型的例子。一个多列索引中最多可以有16个字段组合，并且多列索引中的所有字段必须在同一个表中。Page729十月20234．全文索引全文索引是Microsoft全文引擎(Full-textIndex)创建并管理的一种特殊类型的基于标记的功能性索引。由MicrosoftSQLServer全文引擎(MSFTESQL)服务创建和维护，可以大大提高从字符串中搜索数据的速度，用于帮助用户在字符串数据中搜索复杂的词。Page829十月20236.2创建索引6.2.1系统自动创建索引在创建或修改表时，如果添加了一个主键或惟一键约束，则系统将自动在该表上，以该键值作为索引列，创建一个惟一索引。该索引是聚集索引还是非聚集索引，要根据当前表中的索引状况和约束语句或命令而定。Page929十月20236.2.2在SQLServerManagementStudio下创建索引6.2.3使用CREATEINDEX语句创建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(字段名[,...n])[WITH[索引选项[,...n]]][ON文件组]其中各参数的含义如下。(1)UNIQUE：建立惟一索引。CLUSTERED：建立聚集索引。NONCLUSTERED：建立非聚集索引。(2)table_name/view_name。用于指定创建的索引的表或视图名称。(3)[PAD_INDEX]。用于指定索引中间级中每个页（节占）上保持开放的空间。(4)ONfilegroup。用于指定存放索引的文件组，使用创建索引向志给表创建索引。Page1029十月2023【例6.3】使用CREATEINDEX语句，在“学生信息”表的“dept_id”列和“stu_name”列上创建名为“IX_zyxm”的非聚集、复合索引。运行如下命令。

CREATENONCLUSTEREDINDEXIX_zyxmON

学生信息(dept_id,stu_name)GO

使用系统存储过程sp_helpindex查看学生信息表的索引情况。

EXECsp_helpindex学生信息Page1129十月2023用户在创建和使用惟一索引时应注意如下事项。（1）在建有聚集惟一索引的表上，执行INSERT语句或UPDATE语句时，SQLServer将自动检验新的数据中是否存在重复值。如果存在的话，当创建索引的语句指定了IGNORE_DUP_KEY选项时，SQLServer将发出警告消息并忽略重复的行。如果没有为索引指定IGNORE_DUP_KEY，SQLServer会发出一条警告消息，并回滚整个INSERT语句。（2）具有相同组合列、不同组合顺序的复合索引彼此是不同的。（3）如果表中已有数据，那么在创建惟一索引时，SQLServer将自动检验是否存在重复的值，若有重复值，则不能创建惟一索引。Page1229十月20236.3管理和维护索引6.3.1查看和维护索引信息查看表的索引信息可以使用sp_helpindex系统存储过程，例如查看“学生信息”表的索引信息使用以下的语句。

EXECsp_helpindex学生信息

在SQLServerManagementStudio的“对象资源管理器”中，依次展开到表的“索引”项，可以查看或修改已建索引。注意：创建和修改聚集索引时，SQLServer要在磁盘上对表进行重组，当表中存储了大量记录时，会产生很大的系统开销，花费的时间可能会较长。Page1329十月20236.3.2更改索引标识

可以使用系统存储过程sp_rename更改索引标识名称，语法格式如下：sp_renametable_name.OldName,NewName[,object_type]

其中，table_name是索引所在的表的名字，OldName是要重命名的索引名称，NewName是新的索引名称。【例6.5】更改“学生信息”表中的索引标识IX_zyxm为IDX_dept_name。命令如下USEjxglGOEXECsp_rename'学生信息.IX_zyxm','IDX_dept_name'GO6.3.3删除索引

删除索引可以在SQLServerManagementStudio的“对象资源管理器”中完成或用DROPINDEX命令完成。用DROPINDEX命令删除索引的格式如下

DROPINDEXtable_name.index_name[,…]

【例6.6】用DROPINDEX命令删除“学生信息”表中的“IDX_dept_name”索引。运行如下命令。USEjxglGODROPINDEX学生信息.IDX_dept_nameGO用DROPINDEX命令删除索引时，需要注意如下事项。（1）不能用DROPINDEX命令删除由PRIMARYKEY约束或UNIQUE约束创建的索引。这些索引必须通过删除PRIMARYKEY约束或UNIQUE约束，由系统自动删除。（2）在删除聚集索引时，表中的所有非聚集索引都将被重建。Page1629十月20236.3.4索引的分析与维护

1.显示碎片信息当往表中添加或从表中删除数据行以及索引的值发生改变时，SQLServer将调整索引页维护索引数据的存储。页拆分时会产生碎片，使用DBCCSHOWCONTIG命令，可以显示指定的表或视图的数据和索引的碎片信息。【例6.7】显示“学生信息”表索引标识为IX_xm索引的碎片统计信息。语法如下：USEjxglGODBCCSHOWCONTIG(学生信息,IX_xm)GO6.3.4索引的分析与维护2.索引的分析

SQLServer内部存在一个查询优化器，如何进行数据查询,查询是否使用索引等都是由查询优化器决定的。（1）显示查询计划【例6.8】执行学生成绩的查询，显示执行计划执行下面的查询语句。SELECT*FROM学生信息AINNERJOIN成绩BONA.stu_id=B.stu_idGO然后，单击“查询”|“显示估计执行计划”命令，完成显示执行计划的设置。Page1829十月2023以表格方式显示计划设置是否显示查询计划的命令如下:SETSHOWPLAN_ALLON|OFF或SETSHOWPLAN_TEXTON|OFF【例6.9】执行学生成绩的查询，以表格方式显示查询计划。SETSHOWPLAN_TEXTONSELECT*FROM学生信息AINNERJOIN成绩BONA.stu_id=B.stu_id（2）数据I/O统计设置是否显示磁盘I/O统计的命令为：SETSTATISTICSIOON|OFF【例6.10】执行学生成绩的查询，以表格的方式显示执行计划。

SETSHOWPLAN_TEXTON--打开计划显示GOSELECT*FROM学生信息AINNERJOIN成绩BONA.stu_id=B.stu_idGOSETSTATISTICSIOOFF--关闭I/O统计Page2029十月20233.重新组织索引重新组织索引是重新进行物理排序，从而对表或视图的聚集索引和非聚集索引进行碎片整理，提高索引扫描的性能。【例6.11】重新组织“学生信息”表上的索引PK_学生信息，语句如下。USEjxglGOALTERINDEXPK_学生信息ON学生信息REORGANIZEGOPage2129十月20236.4全文索引

全文索引技术是目前搜索引擎的关键技术。全文索引包含在全文目录中。每个数据库可以包含一个或多个全文目录。一个目录不能属于多个数据库，而每个目录可以包含一个或多个表的全文索引。一个表只能有一个全文索引，因此每个有全文索引的表只属于一个全文目录。全文索引必须在基本表上定义，而不能在视图、系统表或临时表上定义。普通SQL索引全文索引存储时受定义它们所在的数据库的控制存储在文件系统中，但通过数据库管理每个表允许有若干个普通索引每个表只允许有一个全文索引当对作为其基础的数据进行插入、更新或删除时，它们会自动更新将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来请求，也可以在添加新数据时自动发生不分组在同一个数据库内分为一个或多个全文目录使用SQLServer对象资源管理器、向导或T-SQL语句创建和删除使用SQLServer对象资源管理器、向导或存储过程创建、管理和删除全文索引和普通索引的区别在SQLServer数据库中使用全文索引需要以下步骤：(1)启动数据库的全文处理功能（sp_fulltext_database）。(2)建立全文目录（sp_fulltext_catalog）。(3)在全文目录中注册需要全文索引的表（sp_fulltext_table）。(4)指出表中需要全文索引的列名（sp_fulltext_column）。(5)为表创建全文索引（sp_fulltext_table）。(6)填充全文目录（sp_fulltext_catalog）。6.4.1使用SSMS创建全文索引

步骤如下：1.允许数据库使用全文索引2.创建全文目录3.查看和修改全文目录4.创建全文索引5.使用全文搜索查询使用全文搜索查询命令格式：SELECTcolumn_listFROMtable_nameWHERECONTAINS(column_name|*,'search_condition')【例6.12】在教师信息表中搜索teacher_research列中包含“数据库”的记录。SELECT*FROM教师信息

WHERECONTAINS(teacher_research,'*数据库*')6.4.2使用T-SQL创建使用全文索引

1.启用数据库的全文索引Sp_fulltext_databaseenable--启用数据库的全文索引GO2.建立全文目录（创建full_extcatalog）命令格式如下：CREATEFULLTEXTCATALOGcatalog_name[ONFILEGROUPfilegroup][INPATH‘rootpath’][WITH<catalog_option>][ASDEFAULT][AUTHORIZATIONowner_name]其中：<catalog_option>::=ACCENT_SENSITIVITY={ON|OFF}其中参数说明如下：(1)catalog_nameo为全文目录名称。(2)ONFILEGROUPfilegroup为包含全文目录的文件组名。(3)INPATH‘rootpath’为全文目录的路径。ASDEFAULT为指定该全文目录为默认目录。【例6.13】在jxgl数据库中创建一个名为teachers_FT的全文目录，其代码如下：CREATEFULLTEXTCATALOGteachers_FTONFILEGROUP[PRIMARY]INPATH'D:\data\'ASDEFAULT3.建立全文索引有了全文目录后，可以在全文目录里创建全文索引。创建全文索引的T-SQL语句格式如下：CREATEFULLTEXTINDEXONtable_name[(column_name[TYPECOLUMNtype_column_name][LANGUAGElanguags_term][,…n])]KEYINDEXindex_name[ONfulltext_catalog_name][WITH{CHANGE_TRACKING{MANUAL|AUTO|OFF[,ONPOPULATION]}}]其中参数说明如下：(1)