1、数据库应用软件,计算机科学与工程学院 网信系 魏永山,第6章 索引,创建索引的原因及应该考虑的问题索引类型创建索引索引的查看和删除全文索引,6.1 创建索引的原因及应该考虑的问题,下面两个表格，如果查找82，可以采用哪些查找方法？哪个查找效率高？,6.1 创建索引的原因及应该考虑的问题,索引是对数据库表中一个或多个列的值进行排序的结构，用于快速访问数据库表中的特定数据。 索引是由索引页面组成，每个索引页面中的行都包含逻辑指针，通过该指针可以直接检索到数据，从而加快物理数据的索引。,索引有下述优点 1. 提高查询速度 2. 提高连接、ORDER BY和GROUP BY执行的速度 3. 查询优化器
2、依靠索引起作用 4. 强制实施行的唯一性,6.1.1 创建索引的原因,需要创建索引的情况：经常查询某列中的数据时，才需要在表上创建包含该列的索引。索引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在多数情况下，索引所带来的检索速度的优势超过他的不足之处。然而，如果应用程序频繁地更新数据或磁盘空间有限，最好限制索引的数量。,6.1.1 创建索引的原因,索引对下列查询有帮助 精确匹配查询：搜索符合特定搜索关键字值的行 范围查询：搜索其搜索关键字的值为范围值的行 连接查询 分组查询 搜索重复值，以实施PRIMAY KEY和UNIQUE约束 搜索已定义了FOREIGN KEY约束的两个表之间匹
3、配的行,6.1.2 创建索引应该考虑的问题,6.1.2 创建索引应该考虑的问题,创建索引应考虑的主要因素： 一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有索引都需进行适当的调整。 另一方面，对于不需要修改数据的查询，大量索引有助于提高性能，因为SQL Server有更多的索引可供选择，以便确定以最快速度访问数据的最佳方法。,覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。因为检索数据时只需引用表的索引页，不必引用数据页。 对小型表进行索引可能不会产生优化效果，因为SQL Server在遍历索引以搜索数据
4、时，花费的时间可能会比简单的表扫描还长。 应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。 可以在视图和计算列上指定索引。,6.1.2 创建索引应该考虑的问题,下面的情况则不考虑建立索引： 从来不或者很少在查询中引用的列 值很少的列，例如性别（男或女） 记录数很少的表,6.1.2 创建索引应该考虑的问题,6.2 索引类型,聚集索引：数据库表中记录的物理顺序与索引顺序相同，一个表只有一个。 非聚集索引：数据库表中记录的物理顺序与索引顺序可以不同。一个表可有多个。 唯一索引：索引列不包含重复的值 组合索引：索引中包含多列,6.2.1 B-Tree索引结构,B-Tree的顶端结点
5、称为根结点，底层结点称为叶结点，在根结点和叶结点之间的称为中间结点。 B-Tree数据结构从根结点开始，以左右平衡的方式排列数据，中间可以根据需要分成许多层。,6.2.1 B-Tree索引结构,如：,6.2.1 B-Tree索引结构,聚集索引和非聚集索引都使用B-Tree结构建立。 聚集索引和非聚集索引都包括索引页和数据页，索引页用来放索引键值和指到下一层的指针，数据页用来存放记录。,6.2.2 聚集索引和非聚集索引,6.2.2 聚集索引和非聚集索引,1.聚集索引：表中存储的数据按照索引的顺序存储，即表中记录的物理顺序与索引顺序相同。 聚集索引的一个数据页包含一笔记录，再由多个数据页生成一个中
6、间结点的索引页，接着由数个中间节点的索引页合成更上层的索引页，组合后生成最顶层的根节点的索引页。,聚集索引示例,索引页,数据页,Page 140 root,Page 141,Page 145,Page 100,Page 110,Page 120,Page 130,聚集索引的特点： 聚集索引对经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理顺序上也是相邻的。 聚集索引的检索效率比非聚集索引高，但对数据新增、修改和删除的影响比较大。 一个表只有一个聚集索引。,6.2.2 聚集索引和非聚集索引,如果对从表中检索的数据进行排序时经常要用到某一列，则可
7、以将该表在该列上进行聚集索引，避免每次查询该列时都进行排序，从而节省检索时间。 如果表中没有创建其他的聚集索引，则在表的主键列上自动创建聚集索引。 如果表中未创建聚集索引，则按输入顺序存储。,6.2.2 聚集索引和非聚集索引,可考虑将聚集索引用于下面几种情况： 包含大量非重复值的列。 使用下列运算符返回一个范围值的查询：BETWEEN、=、和=。 被连续访问的列。 返回大型结果集的查询。 经常被使用连接或GROUP BY子句的查询访问的列。 OLTP（联机事务处理）类型的应用程序。这些应用程序要求进行非常快的单行查询。,6.2.2 聚集索引和非聚集索引,2.非聚集索引：对表中的数据进行逻辑排序
8、，不影响表中的数据存储顺序，即数据存储在一个地方，索引存储在另一个地方。 非聚集索引与聚集索引一样有B-树结构，但有两个差别： 数据行不按非聚集索引键的顺序排序和存储。 非聚集索引的叶层不包含数据页，而包含索引行。 非聚集索引的数据结构。图6.3,6.2.2 聚集索引和非聚集索引,非聚集索引示例,索引页,数据页,Page 140 root,Page 141,Page 145,Page 100,Page 110,Page 120,Page 130,Page 705,Page 706,Page 707,Page 708,非聚集索引的特点： 索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺
9、序存储，而表中的信息按另一种顺序存储。 在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。 非聚集索引检索效率比聚集索引低，对数据新增、修改和删除的影响较少。一个表可有多个非聚集索引。,6.2.2 聚集索引和非聚集索引,可考虑将非聚集索引用于下面的情况： 包含大量非重复值的列。 不返回大型结果集的查询。 返回精确匹配的查询的搜索条件（WHERE子句）中经常使用的列。 在特定的查询中覆盖一个表中的所有列。 经常需要连接和分组的决策支持系统应用程序。,6.2.
10、2 聚集索引和非聚集索引,唯一索引：唯一索引表示表中任何两条记录的索引值都不相同。它可以确保索引列不包含重复的值。 在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。 在创建主键和唯一约束的列上会自动创建唯一性索引。 组合索引：索引中包含多个字段。,6.2.3 唯一索引和组合索引,6.3 创建索引,SQL Server提供了两种方法来创建索引： 直接创建索引 使用CREATE INDEX语句或者SQL Server Management Studio来直接创建索引。 间接创建索引 使用CREATE TABLE语句创建表时，或者使用ALTER TABLE修改表时，如果指定PRI
11、MARY KEY约束或者UNIQUE约束，则SQL Server自动为这些约束创建索引。,在创建索引时，需要指定索引的特征： 聚集还是非聚集 唯一还是不唯一 单列还是多列 索引中的列顺序为升序还是降序 覆盖还是非覆盖,6.3 创建索引,例： 使用SQL Server管理控制器，在school数据库中student表的sclass列上创建一个升序的非聚集索引Q_sclass。 使用SQL Server管理控制器，在school数据库中score表的cno、degree列上创建一个先按cno升序排， cno一样再按degree降序排的非聚集索引Q_cd。,6.3.1 通过SQL Server Ma
12、nagement创建索引,具有包含性列的索引 在 SQL Server 2005 中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。 非键列具有下列优点： 它们可以是不允许作为索引键列的数据类型。 在计算索引键列数或索引键大小时，数据库引擎不考虑它们。 当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。 当索引包含查询引用的所有列时，称为“覆盖查询”。 键列存储在索引的所有级别中，而非键列仅存
13、储在叶级别中。,6.3.1 通过SQL Server Management创建索引,利用SQL Server管理平台创建索引：展开指定的【服务器】和【数据库】，选择要创建索引的表，展开该表，选择【索引】选项（如图所示），右键单击【索引】，从弹出的快捷菜单中选择【新建索引】，就会出现【新建索引】对话框（如图2所示）。,（图）,（图2）,点击【添加】按钮，会弹出【从“创建索引表”中选择列】，可选择用于创建索引的字段，如图3所示从“dbo.s”中选择列。,打开创建索引对话框的【选项】页框，在此还可以设定索引的属性，如图4所示。,（图3）,（图4）,CREATE INDEX语句创建索引的语法格式： C
14、REATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view (column ASC | DESC,n) WITH index_option ,n ON filegroup其中： index_option定义为 PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB ,6.3.2 使用SQL语言创建索引,FILLFACTOR：指定各索引叶级的填满程度。
15、 PAD_INDEX ：指定索引中间级中每个页上保持开放的空间。 IGNORE_DUP_KEY： 控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。 DROP_EXISTING：指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。 STATISTICS_NORECOMPUTE ：指定过期的索引统计不会自动重新计算。若要恢复自动更新统计，可执行没有NORECOMPUTE子句的UPDATE STATISTICS SORT_IN_TEMPDB：指定生成索引的中间排序结果将存储在tempdb数据库中。,6.3.3 创建索引的选项设置,CREATE U
16、NIQUE NONCLUSTERED INDEX IDX_Employee_LoginID ON HumanResources.Employee ( LoginID ASC),6.3.4 创建索引的空间考虑,创建聚集索引要求数据库中的可用空间大约为数据大小的1.2倍。该空间将对数据进行复制以创建聚集索引。 使用DROP_EXISTING子句时，聚集索引所需的空间数量与现有索引的空间要求相同。所需的额外空间可能还受指定的FILLFACTOR的影响。 如果tempdb在不同于用户数据库所在的磁盘上，则选项SORT_IN_TEMPDB可能减少创建索引所需的时间，但会增加用于创建索引的磁盘空间。除了在
17、用户数据库中创建索引所需的空间外，数据库tempdb还必须有大约相同的额外空间来存储中间排序结果。,6.3.5 在视图和计算列上创建索引,在SQL Server 2008中，可以在计算列和视图上创建索引。 在计算列上创建索引时，计算列必须具有确定性，必须精确，且不能包含text、ntext或image列。 例：在test数据库中创建一个表t1，然后在此表的计算列c上创建一个索引： USE test CREATE TABLE t1 (a int, b int, c AS a*b) GO CREATE INDEX Idx1 ON t1(c) GO INSERT INTO t1 VALUES (1,
18、 0) GO,6.4 索引的查看和删除,查看和删除索引的两种方法： 使用SQL Server Management Studio 使用SQL语句 使用存储过程sp_helpindex查看索引。语法格式如下：EXEC sp_helpindex 对象名 删除索引使用DROP INDEX语句。语法格式如下：DROP INDEX table.index|view.index , n ,通过右键单击【索引名称】，选择【创建索引脚本到新的查询分析器窗口】，则可以查看创建索引的SQL脚本语句，如图所示。,使用Transact-SQL语句中的DROP INDEX命令删除索引： 当不再需要某个索引时，可以将其删除，DROP INDEX命令可以删除一个或者多个当前数据库中的索引，其语法形式如下：DROP INDEX table.index | view.index ,.n 举例：删除表employees中的索引employees_name_index。程序清单如下：DROP INDEX employees.employees_name_index,
