《数据库技术与应用》课件-项目7：索引

认知索引课程简介2通过本课程的学习，认知索引，掌握索引的相关知识。

主要学习内容：一．索引概述二．索引分类三．索引特点四．创建索引注意事项索引概述3如果我们想在一本很厚的书籍中找寻某些特定内容，最方便的查找方式就是先在目录中查找特定内容的所属页码，然后便可快速定位到此特定内容。数据库中存储的数据非常庞大，可以把它看成一本书，而查找其中某特定数据就要引用到“索引”。索引是有效组织表数据的方式，它由表中一列或多列按照一定的排列顺序组成。索引概述4数据库的索引与书中的目录类似，数据库利用索引可迅速找到表中特定数据，而不必扫描整个数据库。在图书中，目录是内容标题和相应页码的列表清单，而在数据库中索引就是表中某列（某些列）数据和相应存储位置的列表，如图所示。索引分类5SQLServer中包含了聚集索引和非聚集索引，唯一索引、视图索引等多种索引，其中聚集索引和非聚集索引是最基本的索引。1.聚集索引：是根据列值的顺序对表的数据页进行物理排序后，再重新存储到磁盘上，因此使用它查找数据很快，但也导致一个表只能有一个聚集索引。一般表创建了主键约束列后，那么系统就自动对该列创建聚集索引。索引分类62.非聚集索引：具有与表的数据行完全分离的结构。非聚集索引不改变数据行的物理存储顺序，查找速度低于聚集索引，一个表可以有多个非聚集索引。3.唯一索引：能够保证索引中不包含重复值，使数据表中的每一行在某种条件下具有唯一性。创建数据表时如果设置了主键，则会默认建立一个唯一索引。索引分类74.视图索引：是为视图创建的索引。其存储方法与带聚集索引的表的存储方法相同。5.其他索引：包括了包含性列索引、全文索引、XML索引等。索引特点81.使用索引能提升数据库的性能，主要体现在以下几个方面：极大地提升数据库的查询进度，这也是其最主要的优点；通过创建唯一索引，能保证数据库中的各行数据具有唯一性；建立在外码上的索引能加速表与表之间的连接，益于实现数据的参照完整性；显著减少分组和排序查询所使用的时间；索引特点92.索引也有一些负面影响：索引会占用物理存储空间，需要一定的额外物理存储来存放索引文件；索引的维护需要耗费时间，对数据表更新数据之后，相应的索引也需要动态维护；创建索引注意事项10索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用WHERE子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQLSERVER除了要更新数据表本身，还要连带更新所有的相关索引，而且过多的索引也会浪费硬盘空间。所以在建立索引时，必须权衡利弊。创建索引注意事项111.一般在下列情况下适合建立索引：（1）经常被查询搜索的列，如经常在WHERE子句的逻辑表达式中出现的列。（2）经常被作为排序依据的列，如在ORDERBY子句中使用的列。（3）外键或主键列、值唯一的列。创建索引注意事项122.在下列情况下不适合建立索引：（1）在查询中很少被引用的列。（2）包含太多重复值的列。（3）数据类型为bit、varchar(max)、varbinary(max)等的列。创建索引课程简介14通过本课程的学习，掌握使用SSMS可视化图形界面和T-SQL语句两种方法来创建索引。

主要学习内容：一．使用SSMS可视化图形界面创建索引二．使用T-SQL语句创建索引使用SSMS可视化图形界面创建索引15SSMS提供了"新建索引"窗口，用于创建索引。在默认情况下，系统会为表的主键列自动创建唯一聚集索引。由于聚集索引只能有一个，如果再建聚集索引的时候应该先将原聚集索引删除。使用SSMS可视化图形界面创建索引16【例】为了提高依据用户姓名查询相关信息的速度，在数据库“BookManageDB”中为学生表“Users”创建一个非聚集的、唯一的索引“UserIndex”。索引键为列“UserName”，升序排列。使用SSMS可视化图形界面创建索引17（1）如图所示，在"对象资源管理器"窗口中，展开数据库“BookManageDB”节点→“表”“Users”节点，右击“索引”节点，从弹出快捷菜单中选择“新建索引”命令。使用SSMS可视化图形界面创建索引18（2）在打开的"新建索引"窗口中输入索引名称，如“UserIndex”，按要求设置索引类型：非聚集，并勾选“唯一”。使用SSMS可视化图形界面创建索引19（3）单击右侧的“添加”按钮，在打开的“从“Users”中选择列”窗口中勾选要建索引的列“UserName”。完成后单击“确定”按钮，返回"新建索引"窗口。使用SSMS可视化图形界面创建索引20（4）在"新建索引"窗口中设置索引的排序顺序等。使用SSMS可视化图形界面创建索引21(5)单击“确定”按钮，完成索引创建。在“对象资源管理器”的Users节点的索引文件夹下就有了索引“UserIndex”。使用T-SQL创建索引22使用CREATEINDEX语句即可创建索引，其语法结构如下所示：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON{表名|视图名}列名[ASC|DESC][,...n])使用T-SQL创建索引23参数说明：(1)UNIQUE：表示在数据表或视图上创建唯一索引；(2)CLUSTERED：表示在数据表或视图上创建聚集索引，NONCLUSTERED

表示创建非聚集索引，省略时，表示创建非聚集索引；(3)索引名：表示该索引的名称，命名规则与数据表名类似；使用T-SQL创建索引24(4)表名或视图名指明索引所属的数据表或视图；(5)列名：指定索引基于的列名，当指定两个或多个列名时，可创建组合索引；(6)ASC|DESC：表示升序，降序。使用T-SQL创建索引25【例】为用户表“Users”的用户名称UserName列（降序）创建一个唯一的非聚集索引UserIndex1。代码：CREATEUNIQUEINDEXUserIndex1ONUsers(UserNameDESC)使用T-SQL创建索引26代码与运行结果，如图所示：使用T-SQL创建索引27在“查询编辑器”中输入以上代码并运行后，会在消息卡上提示“命令已成功完成”，表示成功创建了指定索引，这时需要刷新左侧的“对象资源管理器”表“Users”的索引节点，方可看到所创建的索引“UserIndex1”。使用T-SQL创建索引28右击索引“UserIndex1”，在快捷菜单中选择“属性”命令，打开“UserIndex1”索引属性窗口，可以看到索引键列UserName所设置的排序为降序。特别说明：如果表中已有数据，在创建UNIQUE索引时，SQLServer将自动检验是否存在重复的值，若存在重复值，则创建UNIQUE索引失败。管理索引学习目标30通过本课程的学习，掌握使用SSMS可视化图形界面和T-SQL语句两种方法来管理索引。

主要学习内容：一．删除索引二．查看索引三．重命名索引删除索引31当不需要一个索引时，可以将其从数据库中删除，以回收其占用的磁盘空间。索引可分为两类：一类是创建表约束时自动创建的索引；另一类是通过创建索引的方式创建的独立于约束的索引。这两类索引的删除方式是不一样的：删除前者须先删除对应约束，就能删除约束所使用的索引；删除后者可以使用SSMS界面或T-SQL语句进行删除。删除索引32１.使用SSMS可视化图形界面删除独立于约束的索引【例】删除表“Users”中的索引“UserIndex1”。操作步骤如下：删除索引331）如图所示，在"对象资源管理器"窗口中，展开表“Users”节点下的“索引”节点，用右键单击要删除的索引“UserIndex1”，从弹出的快捷菜单中选择“删除”命令。删除索引342）如图所示，在打开的“删除对象”窗口中，确定删除的对象即可。删除索引352.使用T-SQL语句删除索引使用DROPINDEX语句可以删除索引，其语法格式如下所示： DROPINDEX表名.索引名|视图名.索引名[,…n]。删除索引36【例】删除表“Users”的索引“UserIndex1”。代码：DROPINDEXUsers.UserIndex1代码与运行结果，如图所示：删除索引37可以看见消息卡上提示“命令已成功完成。”。在"对象资源管理器"窗口中，刷新表“Users”中的索引节点，即可看到UserIndex1已被删除。查看索引381.使用SSMS可视化图形界面查看索引【例】查看表“Users”的索引“UserIndex”信息。查看索引391）如图所示，在"对象资源管理器"窗口中，展开表“Users”节点下的“索引”节点，可以查看到表“Users”中的所有索引，用右键单击要查看的索引“UserIndex”，从弹出的快捷菜单中选择“属性”命令。查看索引402）在打开“索引属性UserIndex”窗口，可以看到索引的详细信息，同时也可以实现对索引进行修改等操作。可以实现对索引名称、索引类型等信息进行修改。查看索引412.使用T-SQL语句查看索引使用系统存储过程sp_helpindex可以返回指定表的所有索引信息，它的语法结构如下所示:

SP_HELPINDEX[@objname=]’name’查看索引42【例】查看表“Users”的所有索引信息。代码与运行结果，如图所示：从结果中可以看到表Users中所包含的所有索引，以前各索引的具体信息内容。重命名索引431.使用SSMS可视化图形界面重命名索引【例】将表“Users”的索引“UserIndex”更名为“UserIndex1”。重命名索引441）如图所示，在"对象资源管理器"窗口中，展开表“Users”节点下的“索引”节点，可以查到表“Users”中的所有索引，用右键单击要重命名的索引“UserIndex”，从弹出的快捷菜单中选择“重命名”命令，直接更名即可。重命名索引452.使用T-SQL语句重命名索引利用系统存储过程Sp_rename可以更改索引的名称，语法格式如下：

Sp_rename'表名.原索引名称','新索引名称'重命名索引46【例】将表“Users”的索引“UserIndex”更名为“UserIndex1”。代码：Sp_rename'Users.UserIndex','UserIndex1'代码与运行结果，如图所示：重命名索引47在"对象资源管理器"窗口中，刷新表“Users”节点下的“索引”节点，即可看到更名好的索引。代码中'Users.UserIndex'指出了索引UserIndex的出处来自于表Users；新索引名称，无需在前面写上表出处，否则将被视为索引名称的一部分。索引应用课程简介49通过本课程的学习，结合实例掌握索引的应用的具体原则。

主要学习内容：一．索引使用原则二．基于索引使用原则创建表索引索引使用原则50考虑到索引的优缺点，在创建索引时，为了增强索引性能，主要参考以下几个基本原则：１.数据库准则1）如果表数据中数据量很大，但数据更新少，查询操作却很多，则可为其创建多个索引以提高检索速度。2）表有大量索引，会影响数据库工作效率。索引使用原则513）表如果经常更新，应避免创建过多索引，并且索引列应尽可能少。4）数据较少的表，一般不适宜为其创建索引。5）视图包含聚合、表连接或聚合和连接的组合时，视图的索引可以显著地提升性能。6）创建索引前，可用数据库引擎优化顾问来分析数据库并生成索引建议。索引使用原则522.查询准则1）为经常用于查询中的谓词和连接条件的所有列创建非聚集索引。2）涵盖索引（本身就包含了查询语句所要查询的所有列）可以提高查询性能。3）将插入或修改尽可能多的行的查询写入单个语句内，利用优化的索引维护。4）评估查询类型以及如何在查询中使用列。例如：在完全匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。索引使用原则533.索引列准则1）推荐对唯一列和非空值列创建聚集索引，对于聚集索引，索引键长度尽量短。2）不能将ntext,text,image,varchar(max),nvarchar(max),varbinary(max)数据类型的列指定为索引键列。3）XML数据类型的列只能在XML索引中用作键列。索引使用原则544）如果索引包含多个列，则应考虑列的顺序。5）不要为重复值较多的列创建索引，否则检索时间较长。6）如果索引包含多个索引列，则要考虑列的连接顺序。7）如果查询语句对列产生计算，可考虑对计算列值创建索引。基于索引使用原则创建表索引55按照索引使用原则，在数据库“BookManageDB”中，基于各表的具体设计结构优化并创建各表的索引。【例1】优化并创建表“Grade”的各索引。基于索引使用原则创建表索引56分析：如下图所示，从“G