数据库第8章-索引课件.ppt

1、第8章 索引v8.1 8.1 基本概念基本概念v8.2 8.2 索引结构及类型类型索引结构及类型类型v8.3 8.3 创建索引创建索引1/558.1 8.1 索引基本概念索引基本概念v索引与书籍中的目录类似。索引与书籍中的目录类似。v索引使对数据的查找不需要对整个表进索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。行扫描，就可以在其中找到所需数据。v可以为表中的单个列建立索引，也可以可以为表中的单个列建立索引，也可以为一组列（索引项）建立索引。为一组列（索引项）建立索引。v索引一般采用索引一般采用B B树结构。树结构。2/55索引及数据间的对应关系示意图索引及数据间的对应关

2、系示意图 3/55索引的组织方式索引的组织方式v索引项按数据页索引项按数据页(一块固定大小的连一块固定大小的连续存储空间续存储空间)存储。存储。v表中的全部索引连在一起。表中的全部索引连在一起。4/558.2 索引存储结构及类型索引存储结构及类型v从索引对物理数据的影响来划分，索引分为：从索引对物理数据的影响来划分，索引分为：聚集索引（Clustered Index）非聚集索引（Non-clustered Index)vSQL Server 2008SQL Server 2008根据索引的功能，将索引细根据索引的功能，将索引细分为：聚集索引、非聚集索引、唯一索引、分为：聚集索引、非聚集索引、唯

3、一索引、包含列索引、筛选索引等。包含列索引、筛选索引等。v聚集索引和非聚集索引都采用聚集索引和非聚集索引都采用B-B-树结构树结构来存来存储索引项，而且都包含数据页和索引页，其储索引项，而且都包含数据页和索引页，其中索引页存放索引项和指向下一层的指针，中索引页存放索引项和指向下一层的指针，数据页用来存放数据。数据页用来存放数据。5/558.2.1 8.2.1 B-树结构树结构 6/558.2.2 8.2.2 索引类型索引类型v聚集索引聚集索引v非聚集索引非聚集索引v唯一索引唯一索引v包含列索引包含列索引v筛选索引筛选索引v视图索引视图索引vXMLXML索引索引v全文索引全文索引7/551.1.

4、聚集索引聚集索引 v聚集索引的聚集索引的B-树按自下而上建立，最下层树按自下而上建立，最下层的叶级节点存放数据，它同时也是数据页。的叶级节点存放数据，它同时也是数据页。v多个数据页生成一个中间层节点的索引页，多个数据页生成一个中间层节点的索引页，然后再由数个中间层的节点的索引页合成然后再由数个中间层的节点的索引页合成更上层的索引页，更上层的索引页，v如此上推，直到生成顶层的根节点的索引如此上推，直到生成顶层的根节点的索引页。页。8/55建有聚集索引的表的存储结构示意图建有聚集索引的表的存储结构示意图9/55数据示例数据示例eidenamedeptE01ABCSE02AACSE03BBISE04

5、BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS聚集索引聚集索引非聚集索引非聚集索引10101聚簇索引树构建过程聚簇索引树构建过程在在eideid列上建立聚簇索引列上建立聚簇索引叶级索引、数据页104110111101104110111203202中间级根原始数据原始数据11/55查找过程查找过程v当在建有聚集索引的列上查找数据时当在建有聚集索引的列上查找数据时v首先从聚集索引树的入口（根节点）开首先从聚集索引树的入口（根节点）开始逐层向下查找，始逐层向下查找，v直到达到直到达到B-B-树索引的叶级，也就是达到树索引的叶级

6、，也就是达到了要找的数据所在的数据页，了要找的数据所在的数据页，v最后只在这个数据页中查找所需数据最后只在这个数据页中查找所需数据12/55查找示例查找示例SELECT SELECT*FROM employee WHERE eid=E08 FROM employee WHERE eid=E0813/55说明说明v在聚集索引的叶节点中，数据按聚集索在聚集索引的叶节点中，数据按聚集索引项的值进行物理排序。引项的值进行物理排序。v因此，聚集索引很类似于电话号码簿。因此，聚集索引很类似于电话号码簿。v一个表只能包含一个聚集索引。一个表只能包含一个聚集索引。v但但一个一个索引可以由多个列（组合索引）索引

7、可以由多个列（组合索引）组成组成。14/55下列情况可考虑创建聚集索下列情况可考虑创建聚集索 v包含大量非重复值的列。包含大量非重复值的列。v使用下列运算符返回一个范围值的查询：使用下列运算符返回一个范围值的查询：BETWEEN ANDBETWEEN AND、=、和和 =。v被连续访问的列。被连续访问的列。v返回大型结果集的查询。返回大型结果集的查询。v经常被用作连接的列。经常被用作连接的列。vORDER BYORDER BY或或GROUP BYGROUP BY子句中指定的列。子句中指定的列。15/55下列情况不适于建立聚集索引下列情况不适于建立聚集索引v频繁更改的列。频繁更改的列。v字节长的

8、列。字节长的列。因为聚集索引的因为聚集索引的索引项的值将被所有非聚集索索引项的值将被所有非聚集索引作为查找关键字使用，并被引作为查找关键字使用，并被存储在每个非聚集索引的存储在每个非聚集索引的B B树的树的叶级索引项中。叶级索引项中。16/55非聚集索引非聚集索引v非聚集索引与图书后边的术语表类似。数非聚集索引与图书后边的术语表类似。数据存储在一个地方，术语表存储在另一个据存储在一个地方，术语表存储在另一个地方。而且数据并不按术语表的顺序存放，地方。而且数据并不按术语表的顺序存放，但术语表中的每个词在书中都有确切的位但术语表中的每个词在书中都有确切的位置。置。v非聚集索引就类似于术语表，而数据

9、就类非聚集索引就类似于术语表，而数据就类似于一本书的内容。似于一本书的内容。17/55非聚集索引的存储示意图非聚集索引的存储示意图 18/55非聚集索引与聚集索引的差别非聚集索引与聚集索引的差别v数据不按非聚集索引关键字值的顺序排序和数据不按非聚集索引关键字值的顺序排序和存储存储。v叶级节点不是存放数据的数据页叶级节点不是存放数据的数据页。v非聚集索引非聚集索引B B树的叶级节点是索引行。每个索树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多引行包含非聚集索引关键字值以及一个或多个个行定位器行定位器，这些行定位器指向该关键字值，这些行定位器指向该关键字值对应的数据行（如果索引

10、不唯一，则可能是对应的数据行（如果索引不唯一，则可能是多行）多行）19/55行定位器行定位器v非聚集索引可以建立在有聚集索引的表上非聚集索引可以建立在有聚集索引的表上，也可以建立在无任何索引的表上。，也可以建立在无任何索引的表上。v在在SQL ServerSQL Server中，非聚集索引中的行定位中，非聚集索引中的行定位器有两种形式：器有两种形式：v如果该表如果该表没有定义聚集索引没有定义聚集索引，则行定位器，则行定位器就是指向行的就是指向行的指针指针(用文件标识符（用文件标识符（IDID）、）、页码和页上的行序号生成页码和页上的行序号生成)。v如果该表如果该表定义有聚集索引定义有聚集索引，

11、则行定位器就，则行定位器就是该行的是该行的聚集索引关键字聚集索引关键字的值。的值。20/55101104110111在在eideid列上建有非聚集索引的情形列上建有非聚集索引的情形原始数原始数据页据页叶级叶级索引索引201202220227302303中间级中间级根根41021/55在有聚集索引的表上建立非聚集索引的情形在有聚集索引的表上建立非聚集索引的情形在eid列上建立有聚集索引，在ename列上建立有非聚集索引聚簇索引聚簇索引叶级、叶级、数据页数据页非聚簇索非聚簇索引叶级引叶级非聚簇索非聚簇索引中间级引中间级非聚簇非聚簇索引根索引根1011021201212022033005015025

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

13、CT ename FROM employees WHERE eid=e0826在有聚集索引和非聚集索引的表上查找SELECT eid,dept FROM employees WHERE ename=BC（1 1）在非聚集索引上查找）在非聚集索引上查找E04E0427在有聚集索引和非聚集索引的表上查找(续)SELECT eid,dept FROM employees WHERE eid=E04（2 2）在聚集索引上查找）在聚集索引上查找28下述情况可考虑建立非聚集索引下述情况可考虑建立非聚集索引v包含大量非重复值的列。包含大量非重复值的列。v不返回大型结果集的查询。不返回大型结果集的查询。v经常

14、作为查询条件使用的列。经常作为查询条件使用的列。v经常作为连接和分组条件的列。经常作为连接和分组条件的列。29/55唯一索引唯一索引v确保索引列不包含重复值。确保索引列不包含重复值。v在组合唯一索引的情况下，可以确保索引在组合唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的。列中每个值的组合都是唯一的。v例如，如果在例如，如果在last_namelast_name、first_namefirst_name和和middle_initialmiddle_initial列的组合上创建了唯一索列的组合上创建了唯一索引引full_namefull_name，则该表中任何两个人都不，则该表中任何两

15、个人都不可以具有完全相同的名字。可以具有完全相同的名字。v聚集索引和非聚集索引都可以是唯一的。聚集索引和非聚集索引都可以是唯一的。30/55说明说明v如果必须要实施唯一性来确保数据的完整性，如果必须要实施唯一性来确保数据的完整性，则应在列上创建则应在列上创建UNIQUEUNIQUE约束或约束或PRIMARY KEYPRIMARY KEY约约束，而不要创建唯一索引。束，而不要创建唯一索引。v例如，如果限制身份证号码（例如，如果限制身份证号码（sidsid）列的取值）列的取值不重复，则可在不重复，则可在sidsid列上创建列上创建UNIQUEUNIQUE约束。约束。v实际上，当在表上创建实际上，当

16、在表上创建PRIMARY KEYPRIMARY KEY约束或约束或UNIQUEUNIQUE约束时，系统会自动在这些列上创建约束时，系统会自动在这些列上创建唯一索引。唯一索引。31/554 4包含列索引包含列索引SELECT dept from employees WHERE eid=E02SELECT dept from employees WHERE eid=E02v如果如果employeesemployees只在只在eideid列上建有一个非聚集列上建有一个非聚集索引，没有聚集索引。当查找到叶级索引索引，没有聚集索引。当查找到叶级索引210210页时匹配页时匹配“eid=eid=E02E0

17、2”的匹配数据，下的匹配数据，下一步要做的事情，是取出一步要做的事情，是取出E02E02索引关键字对应索引关键字对应的行定位器值。的行定位器值。v根据行定位器值到数据页中查找该职工所在根据行定位器值到数据页中查找该职工所在部门。如果扩展叶级索引项内容，可以省略部门。如果扩展叶级索引项内容，可以省略最后一步从叶级索引到数据页的查找。最后一步从叶级索引到数据页的查找。eideiddeptdept行定位器行定位器32/55建立包含列索引的好处建立包含列索引的好处v如果查询涉及的所有列都包含在索引中，如果查询涉及的所有列都包含在索引中，则可以显著提高查询性能。则可以显著提高查询性能。v因为因为DBMS

18、DBMS可直接在索引可直接在索引B-B-树中找到所有的树中找到所有的列值，而不需要再访问表或聚集索引数据列值，而不需要再访问表或聚集索引数据，从而减少磁盘，从而减少磁盘 I/O I/O 操作。操作。v因此可考虑扩展非聚集索引的叶级，使其因此可考虑扩展非聚集索引的叶级，使其除了包含索引关键字外，还包含查询涉及除了包含索引关键字外，还包含查询涉及的列。的列。v这种将非索引关键字也放置到非聚集索引这种将非索引关键字也放置到非聚集索引叶级的索引就称为叶级的索引就称为包含列索引包含列索引。33/55建立包含列索引的好处（续）建立包含列索引的好处（续）v非键列可以是不允许作为索引键列的数非键列可以是不允许

19、作为索引键列的数据类型（有些数据类型是不允许建立索据类型（有些数据类型是不允许建立索引的，比如引的，比如texttext等）。等）。v在计算索引键列个数和索引键大小时，在计算索引键列个数和索引键大小时，数据库管理系统不考虑这些非索引键列数据库管理系统不考虑这些非索引键列。34/555 5筛选索引筛选索引v筛选索引是一种经过优化的非聚集索引筛选索引是一种经过优化的非聚集索引，适用于涵盖从定义完善的数据子集中，适用于涵盖从定义完善的数据子集中选择数据的查询。选择数据的查询。v筛选索引使用筛选谓词对表中的部分数筛选索引使用筛选谓词对表中的部分数据行进行索引。据行进行索引。v与对全表建立索引相比，设计

20、良好的筛与对全表建立索引相比，设计良好的筛选索引可以提高查询性能、减少索引维选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。护开销并可降低索引存储开销。35/55设计筛选索引注意事项设计筛选索引注意事项v为了设计有效的筛选索引，必须了解应为了设计有效的筛选索引，必须了解应用程序使用哪些查询以及这些查询与数用程序使用哪些查询以及这些查询与数据子集的关联。据子集的关联。v例如：例如：所含值中大部分为 NULL 的列 含异类类别的值的列 含不同范围的值的列v都属于具有定义完善子集的数据。都属于具有定义完善子集的数据。36/55筛选索引优点筛选索引优点v提高了查询性能和计划质量；提高了查

21、询性能和计划质量；v减少了索引维护开销；减少了索引维护开销；v减少了索引存储开销。减少了索引存储开销。37/55适合建立筛选索引的情况适合建立筛选索引的情况v当只需要查询列中少量的相关值时，可当只需要查询列中少量的相关值时，可以针对值的子集创建筛选索引。以针对值的子集创建筛选索引。v异类数据的筛选索引。表中含有异类数异类数据的筛选索引。表中含有异类数据行时，可以为一种或多种类别的数据据行时，可以为一种或多种类别的数据创建筛选索引。创建筛选索引。38/55适宜构建筛选索引的情况适宜构建筛选索引的情况v仅包含少量非仅包含少量非NULLNULL值的稀疏列。值的稀疏列。v包含多种类别数据的异类列。包含

22、多种类别数据的异类列。v包含多个范围值（如金额、时间和日期）包含多个范围值（如金额、时间和日期）的列。的列。v由列值的简单比较逻辑定义的分区表。由列值的简单比较逻辑定义的分区表。39/558.3 8.3 创建索引创建索引CREATE UNIQUE CLUSTERED|NONCLUSTERED CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name INDEX index_name ON table_or_view_name ON table_or_view_name (column ASC|DESC ,.n )(column ASC|DESC

23、 ,.n )INCLUDE(column_name ,.n )INCLUDE(column_name ,.n )WHERE WHERE WITH(,.n)WITH(,.n)ON partition_scheme_name(column_name)ON partition_scheme_name(column_name)|filegroup_name|filegroup_name|default|default ;40/55创建索引（续）创建索引（续）:=:=PAD_INDEX=ON|PAD_INDEX=ON|OFFOFF|FILLFACTOR=fillfactor|FILLFACTOR=fil

24、lfactor|IGNORE_DUP_KEY=ON|IGNORE_DUP_KEY=ON|OFFOFF|DROP_EXISTING=ON|DROP_EXISTING=ON|OFFOFF|ONLINE=ON|ONLINE=ON|OFFOFF 41/55创建索引（续）创建索引（续）:=AND:=AND:=|:=|:=column_name IN(constant,):=column_name IN(constant,):=column_name :=column_name constantconstant:=IS|IS NOT|=|!=:=IS|IS NOT|=|!=|=|!|=|!|=|!|=|!

25、:=TO :=TO 42/55示例示例v例例1.1.在在StudentStudent表的表的SnameSname列上创建一个列上创建一个非聚集索引。非聚集索引。CREATE INDEX Idx_Sname CREATE INDEX Idx_Sname ON Student(Sname)ON Student(Sname)43/55示例示例v例例2.2.为为Sales.SalesPersonSales.SalesPerson表的表的SalesQuotaSalesQuota和和 SalesYTD SalesYTD 列创建一个非聚集组合索引。列创建一个非聚集组合索引。CREATE NONCLUSTER

26、ED INDEX CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTDIX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ON Sales.SalesPerson (SalesQuota,SalesYTD)(SalesQuota,SalesYTD)44/55示例示例v例例3.3.为为Production.UnitMeasureProduction.UnitMeasure表的表的NameName列创建一个唯一非聚集索引。列创建一个唯一非聚集索引。CREATE UNIQU

27、E INDEX AK_UnitMeasure_Name CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name)ON Production.UnitMeasure(Name)45/55示例示例v例例4 4使用使用IGNORE_DUP_KEYIGNORE_DUP_KEY选项创建索引。选项创建索引。v比较：比较：CREATE UNIQUE INDEX UK_Index CREATE UNIQUE INDEX UK_Index ON#Student(Sname)ON#Student(Sname)WITH(WITH(

28、IGNORE_DUP_KEY=ONIGNORE_DUP_KEY=ON)CREATE UNIQUE INDEX UK_Index CREATE UNIQUE INDEX UK_Index ON#Student(Sname)ON#Student(Sname)WITH(WITH(IGNORE_DUP_KEY=OFFIGNORE_DUP_KEY=OFF)46/55示例示例v例例5.5.使用使用DROP_EXISTINGDROP_EXISTING删除和重新创建删除和重新创建索引。在索引。在StudentStudent表的表的SnameSname列上删除并重列上删除并重新创建索引，同时将叶级索引页和中间级

29、新创建索引，同时将叶级索引页和中间级索引页的充满度设置为索引页的充满度设置为80%80%。CREATE NONCLUSTERED INDEX Idx_Sname CREATE NONCLUSTERED INDEX Idx_Sname ON Student(Sname)ON Student(Sname)WITH(WITH(FILLFACTOR=80FILLFACTOR=80,PAD_INDEX=ON,PAD_INDEX=ON,DROP_EXISTING=ON)DROP_EXISTING=ON)47/55示例示例v例例6.6.创建包含列索引。创建包含列索引。CREATE NONCLUSTERED

30、INDEX CREATE NONCLUSTERED INDEX IX_Address_PostalCode IX_Address_PostalCode ON Person.Address(PostalCode)ON Person.Address(PostalCode)INCLUDEINCLUDE(AddressLine1,AddressLine2,(AddressLine1,AddressLine2,City,StateProvinceID)City,StateProvinceID)48/55示例示例v例例 7.7.创 建 筛 选 索 引。筛 选 条 件 是创 建 筛 选 索 引。筛 选 条

31、件 是EndDateEndDate为非为非NULLNULL的数据的数据CREATE NONCLUSTERED INDEX CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDateFIBillOfMaterialsWithEndDate ON Production.BillOfMaterials ON Production.BillOfMaterials (ComponentID,StartDate)(ComponentID,StartDate)WHEREWHERE EndDate IS NOT NULL EndDate IS NOT NULL

32、49/55示例示例v例例8.8.创建带筛选条件的组合非聚集索引。创建带筛选条件的组合非聚集索引。索引筛选范围为索引筛选范围为ProductSubcategoryIDProductSubcategoryID在在2727到到3636范围内的数据。范围内的数据。CREATE NONCLUSTERED INDEX CREATE NONCLUSTERED INDEX FIProductAccessoriesFIProductAccessories ON Production.Product(Name,ListPrice)ON Production.Product(Name,ListPrice)WHEREWHERE ProductSubcategoryID=27 ProductSubcategoryID=27 AND ProductSubcategoryID=36 AND ProductSubcategoryID)()、小于、小于()()或或 BETWEEN BETWEEN 搜索条件的搜索条件的 WHERE WHERE 子句或者参与子句或者参与联接的列应该放在最前面。联接的列应该放在最前面。54/55