《SQL项目实例教程》-模块六

模块六使用索引任务描述知识要点返回任务描述索引是数据库中重要的对象之一，它类似于图书的目录。索引允许数据库应用程序迅速找到表中特定的数据，而不用扫描表中全部的数据。在数据库中，使用索引可以很大程度上提高数据的查询效率，减少查询数据的时间，改善数据库的性能。索引是数据库中一个重要的对象，本模块将详细介绍索引的基本概念、使用索引的意义及索引的操作。任务１创建索引方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建索引下一页返回任务描述在“ｅｂｏｏｋ”数据库中，为“Ｂｏｏｋ”表创建基于“ｂｏｏｋＮａｍｅ”列的非聚集索引ｂｏｏｋ＿ｎａｍｅ＿ｉｎｄｅｘ。具体操作步骤如下：①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中依次展开“数据库”、“ｅｂｏｏｋ”、“表”、“Ｂｏｏｋ”节点。这时，如果展开“索引”节点，将看到表中存在的索引。由于“Ｂｏｏｋ”表设置了主键，所以存在一个聚集索引ｐｋ＿ｂｏｏｋＩｄ，如图６.１所示。上一页下一页返回任务描述②右击“索引”节点，在弹出的如图６.２所示的快捷菜单中单击“新建索引”命令，打开如图６.３所示的“新建索引”对话框，默认进入“常规”页面。③根据任务描述，在“索引名称”对应的文本框中输入索引名称“ｂｏｏｋ＿ｎａｍｅ＿ｉｎｄｅｘ”，从“索引类型”对应的下拉列表框中选择索引类型为“非聚集”。④单击“添加”按钮，弹出如图６.４所示的“选择索引列”的对话框，从中选择要添加到索引键的表列。在此，选择“ｂｏｏｋＮａｍｅ”列，单击“确定”按钮，返回到“新建索引”对话框。上一页下一页返回任务描述⑤如果需要设置其他选项，可以单击“新建索引”对话框左上角的“选项”，进入“选项”界面，如图６.５所示。在此界面，可以根据实际需要，勾选相应的选项。⑥设置完成后，单击“确定”按钮，完成索引的创建，如图６.６所示。方法二：使用Ｔ－ＳＱＬ语句创建索引在网上书店ｅｂｏｏｋ数据库中，为“Ｂｏｏｋ”表创建基于“出版社”列的非聚集索引ｂｏｏｋ＿ｐｕｂｌｉｓｈｅｒ＿ｉｎｄｅｘ，代码如下：上一页下一页返回任务描述运行结果如图６.７所示。在“ｅｂｏｏｋ”数据库中，为“Ｂｏｏｋ”表创建基于“ｉｓｂｎ”列的唯一、非聚集索引ｂｏｏｋ＿ｉｓｂｎ＿ｉｎｄｅｘ，代码如下：运行结果如图６.８所示。提示：用户在创建和使用索引时，应注意以下事项。上一页下一页返回任务描述•必须是表的所有者才能执行ＣＲＥＡＴＥＩＮＤＥＸ语句来创建索引。•唯一索引既可以采用聚集索引的结构，也可以采用非聚集索引的结构。如果不指明ＣＬＵＳＴＥＲＥＤ选项，则ＳＱＬＳｅｒｖｅｒ默认为唯一索引，采用非聚集索引的结构。•如果表中已有数据，那么在创建唯一索引时，ＳＱＬＳｅｒｖｅｒ将自动检验是否存在重复的值。若存在重复值，则唯一索引创建失败。•具有相同组合列、不同组合顺序的复合索引是不同的。上一页下一页返回任务描述•在建立唯一索引的表上插入、更新数据时，ＳＱＬＳｅｒｖｅｒ将自动检验新的数据中是否存在重复值，如果存在，则ＳＱＬＳｅｒｖｅｒ在第一个重复值处取消语句并返回错误信息。任务２重命名索引建立索引后，索引的名称也是可以更改的。重命名索引的方法有两种：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ或者系统存储过程。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ更改索引名称上一页下一页返回任务描述将“ｅｂｏｏｋ”数据库中“Ｂｏｏｋ”表的ｂｏｏｋ＿ｉｓｂｎ＿ｉｎｄｅｘ索引名称更改为ｉｓｂｎ＿ｉｎ⁃ｄｅｘ。①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中依次展开“数据库”、“ｅｂｏｏｋ”、“Ｂｏｏｋ”表、“索引”节点。②右击要修改名称的索引，在弹出的快捷菜单中选择“重命名”命令，索引名变为可修改状态，输入新的索引名，按回车键完成对索引的更名，如图６.９和图６.１０所示。方法二：使用Ｔ－ＳＱＬ语句更改索引名称上一页下一页返回任务描述在“ｅｂｏｏｋ”数据库中，将“Ｂｏｏｋ”表基于“ｂｏｏｋＮａｍｅ”列的索引ｂｏｏｋ＿ｎａｍｅ＿ｉｎ⁃ｄｅｘ，更名为ｂｏｏｋＮａｍｅ＿ｉｎｄｅｘ，代码如下：ＥＸＥＣｓｐ＿ｒｅｎａｍｅ′Ｂｏｏｋｂｏｏｋ＿ｎａｍｅ＿ｉｎｄｅｘ′，′ｂｏｏｋＮａｍｅ＿ｉｎｄｅｘ′运行结果如图６.１１所示。任务３删除索引方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ删除索引上一页下一页返回任务描述删除“ｅｂｏｏｋ”数据库中“Ｂｏｏｋ”表的索引ｉｓｂｎ＿ｉｎｄｅｘ。具体操作步骤如下：①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中依次展开“数据库”、“ｅｂｏｏｋ”、“Ｂｏｏｋ”表、“索引”节点。②右击要删除的索引，在弹出的快捷菜单中执行“删除”命令，打开“删除对象”对话框。在“删除对象”对话框中单击“确定”按钮，即可删除选定的索引，如图６.１２所示。上一页下一页返回任务描述方法二：使用Ｔ－ＳＱＬ语句删除索引①在“ｅｂｏｏｋ”数据库中，删除“Ｂｏｏｋ”表基于“ｐｕｂｌｉｓｈｅｒ”列的索引ｂｏｏｋ＿ｐｕｂｌｉｓｈｅｒ＿ｉｎｄｅｘ，代码如下：运行结果如图６.１３所示。提示：使用ＤＲＯＰＩＮＤＥＸ命令删除表中的索引时，需要注意如下事项：•不能用ＤＲＯＰＩＮＤＥＸ删除由主键约束或唯一约束创建的索引，要删除这些索引，必须先删除主键约束或者唯一约束。上一页下一页返回任务描述•在删除聚集索引时，表中的所有非聚集索引都将被重建。②在“ｅｂｏｏｋ”数据库中，删除“Ｂｏｏｋ”表基于“ｂｏｏｋＩｄ”列的索引ｐｋ＿ｂｏｏｋＩｄ，代码如下：ＤＲＯＰＩＮＤＥＸＢｏｏｋ.ｐｋ＿ｂｏｏｋＩｄ运行结果如图６.１４所示。从中可以看出，由于ｐｋ＿ｂｏｏｋＩｄ索引为主键约束创建的索引，所以必须删除主键约束，才可以删除此索引。任务４分析索引ＳＱＬＳｅｒｖｅｒ提供了多种分析索引和查询性能的方法，常用的有ＳＨＯＷＰＬＡＮ和ＳＴＡＴＩＳＴＩＣＳＩＯ语句。上一页下一页返回任务描述１.显示查询执行信息在“ｅｂｏｏｋ”数据库中的“Ｂｏｏｋ”表上查询ｉｓｂｎ为９７８７３０２２６２２２０的图书信息，并显示查询处理过程，分析哪些索引被系统采用，代码如下：上一页下一页返回任务描述运行结果如图６.１５所示。从中可以看出，该查询使用了ｕｋ＿ｉｓｂｎ索引。２.显示磁盘活动量信息在“ｅｂｏｏｋ”数据库中的“Ｂｏｏｋ”表上查询出版社为清华大学出版社的图书信息，并分析执行数据查询所花费的磁盘活动量信息，代码如下：运行结果如图６.１６所示。在执行结果界面中选择“消息”选项卡查看结果。上一页返回知识要点一、索引的概念索引（Ｉｎｄｅｘ）是以表的字段列为基础而建立的一种数据库对象，是一种实现数据快速定位与加快数据访问速度的技术手段。索引通过存储排序的索引关键字与表记录的物理空间位置，建立了索引数据与物理数据间的映射关系,从而实现了表记录的逻辑排序。索引是与表（或视图）关联的页的集合，用于加速从表中检索或者用于强制实施的唯一性。二、索引的作用索引是数据库随机检索的常用手段，它实际上就是记录的关键字与其相应地址的对应表。通过索引可大大提高查询速度。下一页返回知识要点对于拥有复杂结构与大量数据的基本表而言，索引的作用与意义就如同目录对一本内容丰富而结构宏大的著作一样。在某种意义上，索引就是数据表的目录。使用索引可以大大提高系统的性能，其具体表现在：①通过创建唯一索引，可以保证数据记录的唯一性。②可以大大加快数据检索速度。③可以加速表与表之间的连接。④在使用ＯＲＤＥＲＢＹ和ＧＲＯＵＰＢＹ子句进行检索数据时，可以显著减少查询中分组和排序的时间。上一页下一页返回知识要点三、索引的数据结构索引本身就是记录源数据部分列码值与源数据记录行之间映射关系的物理表，这类表只包含索引关键字与地址指针两类数据域。索引关键字域的内容为基表或源视图中的一个字段列的码值，或为多个字段列组合而成的码值。地址指针域存放着指向数据所在物理页的位置与行位置的指针。四、索引的分类在ＳＱＬＳｅｒｖｅｒ数据库中，根据索引存储结构的不同，将其分成两类：聚集索引（ＣＬＵＳ⁃ＴＥＲＥＤＩＮＤＥＸ）和非聚集索引（ＮＯＮＣＬＵＳＴＥＲＥＤＩＮＤＥＸ）。上一页下一页返回知识要点（１）聚集索引聚集索引是指表中数据行的物理存储顺序与索引列顺序完全相同。当在一个表的某列创建聚集索引时，表中的数据会根据索引列的顺序进行重新排序，再存储到磁盘上。因此，每个表只能创建一个聚集索引。聚集索引一般创建在表中经常搜索的列或按顺序访问的列上。因为聚集索引对表中的数据进行了排序，当使用聚集索引找到包含的第一个值后，其他连续的值就在附近了。除个别表外，数据表都应该创建聚集索引，以提高数据的查询功能。默认情况下，ＳＱＬＳｅｒｖｅｒ为主键约束自动创建聚集索引。上一页下一页返回知识要点可以在表或视图的任何列或列的组合上创建索引，在实际开发中，一般在定义主键的列上建立聚集索引，即数据行按照主键列值物理排序。（２）非聚集索引非聚集索引不会对表和视图按照索引列值进行物理排序。如果表中没有建立聚集索引，则表中的数据行实际上是按照输入数据时的顺序排序的。非聚集索引对索引列进行逻辑排序，并能保存索引列的逻辑排序位置。在表或视图中，最多可以建立２４９个非聚集索引和一个聚集索引。上一页下一页返回知识要点五、索引的工作机制当进行数据检索时，系统先搜索索引文件，从中找到所需数据的位置指针，再通过指针直接从相应的数据页面中读取并加载相关数据。索引是基本表的目录，可以对一个基本表建立多个不同的索引，以提供不同的存取路径，如图６.１７所示。六、使用索引的代价使用索引可以提高系统的性能，大大加快数据检索的速度，那么是不是可以为表中的每一列都建立索引呢？为每一列都建立索引是不明智的，因为使用索引要付出一定的代价。上一页下一页返回知识要点•索引需要占用数据表以外的物理存储空间；•创建索引和维护索引要花费一定的时间；•当对表进行更新操作时，索引需要被重建，降低了数据的维护速度。七、创建索引ＳＱＬＳｅｒｖｅｒ在创建主键约束或唯一约束时，自动创建唯一索引，以强制实施ＰＲＩＭＡＲＹＫＥＹ和ＵＮＩＱＵＥ约束的唯一性要求。如果需要创建不依赖于约束的索引，可以使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ或Ｔ－ＳＱＬ语句创建索引。上一页下一页返回知识要点创建索引时要注意以下几点：•只有表或者视图的所有者才能创建索引。•在创建聚集索引时，会对表进行复制，对表中的数据进行排序，然后删除原始的表。因此数据库必须有足够的空闲空间来容纳数据库副本。•在使用ＣＲＥＡＴＥＩＮＤＥＸ语句创建索引时，必须指定索引名称、表及索引所应用的列的名称。•默认情况下，创建的索引是非聚集索引。•复合索引的列的最大数目为１６。上一页下一页返回知识要点八、删除索引使用索引虽然可以提高查询效率，但是对一个表来说，如果索引过多，在修改表中记录时会增加服务器维护索引的时间。因此，当不再需要某个索引的时候，应该把它从数据库中删除。对于通过主键（ＰＲＩＭＡＲＹＫＥＹ）约束和唯一（ＵＮＩＱＵＥ）约束自动创建的索引，可以通过删除约束的方法删除索引。对于用户创建的其他索引，可以使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅ⁃ｍｅｎｔＳｔｕｄｉｏ或者Ｔ－ＳＱＬ语句删除。九、有关索引的Ｔ－ＳＱＬ语句语法格式１.使用Ｔ－ＳＱＬ语句创建索引上一页下一页返回知识要点创建索引的Ｔ－ＳＱＬ语句格式如下：ＣＲＥＡＴＥ［ＵＮＩＱＵＥ］［ＣＬＵＳＴＥＲＥＤ｜ＮＯＮＣＬＵＳＴＥＲＥＤ］ＩＮＤＥＸｉｎｄｅｘ＿ｎａｍｅＯＮ｛ｔａｂｌｅ＿ｎａｍｅ｜ｖｉｅｗ＿ｎａｍｅ｝（ｃｏｌｕｍｎ＿ｎａｍｅ［ＡＳＣ｜ＤＥＳＣ］［，……ｎ］）［ＷＩＴＨ［ｉｎｄｅｘ＿ｐｒｏｐｅｒｔｙ［，……ｎ］］上一页下一页返回知识要点参数说明如下：•ＵＮＩＱＵＥ表示建立唯一索引。•ＣＬＵＳＴＥＲＥＤ表示建立聚集索引。•ＮＯＮＣＬＵＳＴＥＲＥＤ表示建立非聚集索引，省略为非聚集索引。•ｉｎｄｅｘ＿ｎａｍｅ表示索引名称。•ｔａｂｌｅ＿ｎａｍｅ表示索引所在的表名称。•ｖｉｅｗ＿ｎａｍｅ表示索引所在的视图名称。•ｃｏｌｕｍｎ＿ｎａｍｅ表示创建索引的列名称。•ＡＳＣ｜ＤＥＳＣ表示按照升序或者降序排序。上一页下一页返回知识要点ｉｎｄｅｘ＿ｐｒｏｐｅｒｔｙ：索引属性，如ＤＲＯＰ＿ＥＸＩＳＴＩＮＧ表示先删除存在的索引。２.使用Ｔ－ＳＱＬ语句更改索引名称可以使用系统存储过程ｓｐ＿ｒｅｎａｍｅ来更改索引的名称。其语法格式如下：ＥＸＥＣｓｐ＿ｒｅｎａｍｅｔａｂｌｅ＿ｎａｍｅ.ｏｌｄ＿ｉｎｄｅｘ＿ｎａｍｅ，ｎｅｗ＿ｉｎｄｅｘ＿ｎａｍｅ３.使用Ｔ－ＳＱＬ语句删除索引可以使用Ｔ－ＳＱＬ中的ＤＲＯＰＩＮＤＥＸ命令删除表中的索引，其语法格式如下：上一页下一页返回知识要点ＤＲＯＰＩＮＤＥＸｔａｂｌｅ＿ｎａｍｅ.ｉｎｄｅｘ＿ｎａｍｅ［，……