数据库第8章-索引课件

1、第8章 索引8.1 基本概念8.2 索引结构及类型类型8.3 创建索引1/558.1 索引基本概念索引与书籍中的目录类似。索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。可以为表中的单个列建立索引，也可以为一组列（索引项）建立索引。索引一般采用B树结构。2/55索引及数据间的对应关系示意图 3/55索引的组织方式索引项按数据页(一块固定大小的连续存储空间)存储。表中的全部索引连在一起。4/558.2 索引存储结构及类型从索引对物理数据的影响来划分，索引分为：聚集索引（Clustered Index）非聚集索引（Non-clustered Index)SQL Server 2

2、008根据索引的功能，将索引细分为：聚集索引、非聚集索引、唯一索引、包含列索引、筛选索引等。聚集索引和非聚集索引都采用B-树结构来存储索引项，而且都包含数据页和索引页，其中索引页存放索引项和指向下一层的指针，数据页用来存放数据。5/558.2.1 B-树结构 6/558.2.2 索引类型聚集索引非聚集索引唯一索引包含列索引筛选索引视图索引XML索引全文索引7/551. 聚集索引 聚集索引的B-树按自下而上建立，最下层的叶级节点存放数据，它同时也是数据页。多个数据页生成一个中间层节点的索引页，然后再由数个中间层的节点的索引页合成更上层的索引页，如此上推，直到生成顶层的根节点的索引页。 8/55建

3、有聚集索引的表的存储结构示意图9/55数据示例eidenamedeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS聚集索引非聚集索引10101聚簇索引树构建过程在eid列上建立聚簇索引叶级索引、数据页104110111101104110111203202中间级根原始数据11/55查找过程当在建有聚集索引的列上查找数据时首先从聚集索引树的入口（根节点）开始逐层向下查找，直到达到B-树索引的叶级，也就是达到了要找的数据所在的数据页，最后只在这个数据页中查找所需数据12/55查

4、找示例SELECT * FROM employee WHERE eid=E0813/55说明在聚集索引的叶节点中，数据按聚集索引项的值进行物理排序。因此，聚集索引很类似于电话号码簿。一个表只能包含一个聚集索引。但一个索引可以由多个列（组合索引）组成。14/55下列情况可考虑创建聚集索 包含大量非重复值的列。使用下列运算符返回一个范围值的查询：BETWEEN AND、=、 和 =。被连续访问的列。返回大型结果集的查询。经常被用作连接的列。ORDER BY或GROUP BY子句中指定的列。15/55下列情况不适于建立聚集索引频繁更改的列。字节长的列。因为聚集索引的索引项的值将被所有非聚集索引作为查

5、找关键字使用，并被存储在每个非聚集索引的B树的叶级索引项中。16/55非聚集索引非聚集索引与图书后边的术语表类似。数据存储在一个地方，术语表存储在另一个地方。而且数据并不按术语表的顺序存放，但术语表中的每个词在书中都有确切的位置。非聚集索引就类似于术语表，而数据就类似于一本书的内容。17/55非聚集索引的存储示意图 18/55非聚集索引与聚集索引的差别数据不按非聚集索引关键字值的顺序排序和存储。叶级节点不是存放数据的数据页。非聚集索引B树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多个行定位器，这些行定位器指向该关键字值对应的数据行（如果索引不唯一，则可能是多行）19/55行

6、定位器非聚集索引可以建立在有聚集索引的表上，也可以建立在无任何索引的表上。在SQL Server中，非聚集索引中的行定位器有两种形式：如果该表没有定义聚集索引，则行定位器就是指向行的指针(用文件标识符（ID）、页码和页上的行序号生成)。如果该表定义有聚集索引，则行定位器就是该行的聚集索引关键字的值。20/55101104110111在eid列上建有非聚集索引的情形原始数据页叶级索引201202220227302303中间级根41021/55在有聚集索引的表上建立非聚集索引的情形在eid列上建立有聚集索引，在ename列上建立有非聚集索引聚簇索引叶级、数据页非聚簇索引叶级非聚簇索引中间级非聚簇索

7、引根10110212012120220330050150250851022/55数据的查找在无索引数据表上的查找在有聚簇索引数据表上的查找在只有非聚簇索引数据表上的查找在有聚集索引和非聚集索引的表上查找23/55在无索引数据表上的查找SELECT ename FROM employees WHERE eid = e08AD结果：24在有聚簇索引数据表上的查找101104110111101104110111SELECT ename FROM employees WHERE eid = e0825在只有非聚集索引的表上查找SELECT ename FROM employees WHERE eid

8、= e0826在有聚集索引和非聚集索引的表上查找SELECT eid, dept FROM employees WHERE ename = BC（1）在非聚集索引上查找E0427在有聚集索引和非聚集索引的表上查找(续)SELECT eid, dept FROM employees WHERE eid = E04（2）在聚集索引上查找28下述情况可考虑建立非聚集索引包含大量非重复值的列。不返回大型结果集的查询。经常作为查询条件使用的列。经常作为连接和分组条件的列。29/55唯一索引确保索引列不包含重复值。在组合唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的。例如，如果在last_nam

9、e、first_name和middle_initial列的组合上创建了唯一索引full_name，则该表中任何两个人都不可以具有完全相同的名字。聚集索引和非聚集索引都可以是唯一的。 30/55说明如果必须要实施唯一性来确保数据的完整性，则应在列上创建UNIQUE约束或PRIMARY KEY约束，而不要创建唯一索引。例如，如果限制身份证号码（sid）列的取值不重复，则可在sid列上创建UNIQUE约束。实际上，当在表上创建PRIMARY KEY约束或UNIQUE约束时，系统会自动在这些列上创建唯一索引。 31/554包含列索引SELECT dept from employees WHERE ei

10、d=E02如果employees只在eid列上建有一个非聚集索引，没有聚集索引。当查找到叶级索引210页时匹配 “eid = E02”的匹配数据，下一步要做的事情，是取出E02索引关键字对应的行定位器值。根据行定位器值到数据页中查找该职工所在部门。如果扩展叶级索引项内容，可以省略最后一步从叶级索引到数据页的查找。eiddept行定位器32/55建立包含列索引的好处如果查询涉及的所有列都包含在索引中，则可以显著提高查询性能。因为DBMS可直接在索引B-树中找到所有的列值，而不需要再访问表或聚集索引数据，从而减少磁盘 I/O 操作。因此可考虑扩展非聚集索引的叶级，使其除了包含索引关键字外，还包含查

11、询涉及的列。这种将非索引关键字也放置到非聚集索引叶级的索引就称为包含列索引。33/55建立包含列索引的好处（续）非键列可以是不允许作为索引键列的数据类型（有些数据类型是不允许建立索引的，比如text等）。 在计算索引键列个数和索引键大小时，数据库管理系统不考虑这些非索引键列。34/555筛选索引筛选索引是一种经过优化的非聚集索引，适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分数据行进行索引。与对全表建立索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。35/55设计筛选索引注意事项为了设计有效的筛选索引，必须了解应用程序使用哪

12、些查询以及这些查询与数据子集的关联。例如：所含值中大部分为 NULL 的列含异类类别的值的列含不同范围的值的列都属于具有定义完善子集的数据。36/55筛选索引优点提高了查询性能和计划质量；减少了索引维护开销；减少了索引存储开销。 37/55适合建立筛选索引的情况当只需要查询列中少量的相关值时，可以针对值的子集创建筛选索引。异类数据的筛选索引。表中含有异类数据行时，可以为一种或多种类别的数据创建筛选索引。38/55适宜构建筛选索引的情况仅包含少量非NULL值的稀疏列。包含多种类别数据的异类列。包含多个范围值（如金额、时间和日期）的列。由列值的简单比较逻辑定义的分区表。39/558.3 创建索引C

13、REATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table_or_view_name ( column ASC | DESC ,.n ) INCLUDE ( column_name ,.n ) WHERE WITH ( ,.n) ON partition_scheme_name ( column_name ) | filegroup_name | default ; 40/55创建索引（续） := PAD_INDEX = ON | OFF | FILLFACTOR = fillfactor | IGNORE_DUP_KEY =

14、 ON | OFF | DROP_EXISTING = ON | OFF | ONLINE = ON | OFF 41/55创建索引（续）:= AND := | := column_name IN (constant , ) := column_name constant := IS | IS NOT | = | | != | | = | ! | | = | ! := TO 42/55示例例1.在Student表的Sname列上创建一个非聚集索引。 CREATE INDEX Idx_Sname ON Student (Sname)43/55示例例2.为Sales.SalesPerson表的Sa

15、lesQuota和 SalesYTD 列创建一个非聚集组合索引。CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD)44/55示例例3.为Production.UnitMeasure表的Name列创建一个唯一非聚集索引。CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name)45/55示例例4使用IGNORE_DUP_KEY选项创建索引。比较： CREAT

16、E UNIQUE INDEX UK_Index ON #Student (Sname) WITH (IGNORE_DUP_KEY = ON) CREATE UNIQUE INDEX UK_Index ON #Student (Sname) WITH (IGNORE_DUP_KEY = OFF)46/55示例例5. 使用DROP_EXISTING删除和重新创建索引。在Student表的Sname列上删除并重新创建索引，同时将叶级索引页和中间级索引页的充满度设置为80%。 CREATE NONCLUSTERED INDEX Idx_Sname ON Student(Sname) WITH (FIL

17、LFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON)47/55示例例6. 创建包含列索引。CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)48/55示例例7. 创建筛选索引。筛选条件是EndDate为非NULL的数据CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS