10第10章规划和维护索引操作.ppt

1、SQL Server,第10章 规划和维护索引操作,第10章 规划和维护索引操作,10.1 索引的作用与构架10.2 索引类型10.3 规划设计索引的一般原则 10.4 索引的创建和删除 10.5 索引使用中的维护,10.1 索引的作用与构架,一、什么叫索引 数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。,10.1.1

2、索引概述,二、索引的作用,通过创建唯一索引，可以保证数据记录的唯一性。 可以大大加快数据检索速度。 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。,10.1.2 SQL Server索引下的数据组织结构,在 SQL Server 数据库内，索引对象作为 8 KB 页的集合存储。 SQL Server 支持视图上的索引。视图上第一个允许的索引是聚集索引。在视图上执行 CREATE INDEX 语句时，视图的结果

3、集被具体化，并且存储在与有聚集索引的表具有相同结构的数据库中。 每个表或索引视图的数据行存储在 8 KB 数据页集合中。每个数据页都有一个 96 字节的页头，其中包含象拥有该页的表的标识符 (ID) 这样的系统信息。如果页链接在索引列表中，则页头还包含指向下一页及前面用过的页的指针。在页尾没有行偏移表。数据行填充页的剩余部分。,每个表、索引和索引视图在sysindexes 内有一个记录行，由对象标识符（id）列和索引标识符（indid）列的组合唯一标识。,1堆集结构,堆集在 sysindexes 内有一行，其 indid = 0。 sysindexes.FirstIAM 列指向 IAM（索引分

4、配映像） 页链的 IAM 首页，IAM 页链管理分配给堆集的空间。 SQL Server 使用 IAM 页在堆集中浏览。堆集内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的连接。 通过扫描 IAM 页可以对堆集进行表扫描或串行读，以找到容纳这个堆集的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆集扫描一律沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆集中的行一般不按照插入的顺序返回。,2聚集索引结构,聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体

5、的，它的叶节点中存储的是实际的数据。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。 应当在创建任何非聚集索引之前创建聚集索引。 聚集索引的大小平均是表的5%。 在创建索引时系统需要比索引的表1.2倍的磁盘空间，索引创建完成后自动回收。,2聚集索引结构（续）,聚集索引在 sysindexes 内有一行，其 indid = 1。数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行。 SQL Server将索引组织为 B 树。索引内的每一页包含一个页首，页首后面跟着索引行。每个索引行都包含一个键值以及一个指向较低级页或数据行的指针。索引的每个页

6、称为索引节点。B 树的顶端节点称为根节点。索引的底层节点称为叶节点。每级索引中的页链接在双向链接列表中。在聚集索引内数据页组成叶节点。根和叶之间的任何索引级统称为中间级。 对于聚集索引，sysindexes.root 指向它的顶端。SQL Server 沿着聚集索引浏览以找到聚集索引键对应的行。,在lname列上创建索引后，查询employee表可以看出表中记录是按lname值的顺序排列；删除lname列上的聚集索引，而在emp_id上创建聚集索引后，表中记录按照emp_id列值顺序排列。,3非聚集索引,非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚

7、集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。 如果索引时没有指定索引类型，默认情况下为非聚集索引； 应当在创建非聚集索引之前创建聚集索引； 每个表最多可以创建249个非聚集索引； 最好在唯一值较多的列上创建非聚集索引； 经常需要联接和分组查询，应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。,3非聚集索引（续）,非聚集索引与聚集索引一样有 B 树结构，但是有两个重大差别： 1、数据行不按非聚集索引键的顺序排序和存储。 2、非聚集索引的叶层不包含数据页。 相反，叶节点包含索引行。非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。 在 SQL Se

8、rver 中，非聚集索引中的行定位器有两种形式： 1、如果表是堆集（没有聚集索引），行定位器就是指向行的指针。该指针用文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID。 2、如果表有聚集索引，或者索引在索引视图上，则行定位器就是行的聚集索引键。,4扩展盘区空间的管理,索引分配映射表（IAM）页映射数据库文件中由堆集或索引使用的扩展盘区。对于任何具有ntext、text和image类型的列的表，IAM页还映射分配给这些类型的页链的扩展盘区。这些对象中的每一个都有由一个或多个记录所有分配给自己的扩展盘区的IAM页组成的链。每个对象对每个包含扩展盘区的文件都至少有一个IAM。如果分配

9、给对象的文件上的扩展盘区的范围超过了一个IAM页可以记录的范围，则扩展盘区可能会在文件上有多个IAM页。,4扩展盘区空间的管理,IAM页按需要分配给每个对象，并在文件内随机定位。Sysindexes.dbo.FirstIAM指向对象的IAM首页，这个对象的所有IAM 页用链条链接在一起。 IAM 页的页首说明IAM所映射的扩展盘区范围的起始扩展盘区。IAM中还有大位图，该位图内的每个位代表一个扩展盘区。位图的第一个位代表范围内的第一个扩展盘区，第二个位代表范围内的第二个扩展盘区，依此类推。如果一个位是0，则不将它代表的扩展盘区分配给拥有该IAM的对象。如果这个位是1，则将它代表的扩展盘区分配给

10、拥有该IAM页的对象。,10.2 索引类型,1、聚集索引和非聚集索引 2、主键索引：表定义主键时自动创建主键索引，并且会自动创建聚集索引。创建聚集索引时，可以指定填充因子，以便在索引页上留出额外的间隙和保留一定百分比的空间，供将来表的数据行增加或更新时减少发生页拆分机会。创建时可以选项填充因子。 3、唯一索引：惟一索引可以确保索引列中不包含重复值。只有惟一性是数据本身特征时指定惟一索引才有意义。使用惟一索引不能完全等同于使用主键。如果某列包含多行NULL值，不能在该列上创建惟一索引。创建惟一索引要使用UNIQUE选项。 4、单列索引和复合索引：复合索引指一个索引中包含了一个以上的列，最多可以有

11、16个列复合到一个索引中。复合的多列索引允许某一列具有相同的值。,10.3 规划设计索引的一般原则,10.3.1 什么类型查询适合建立索引 10.3.2 其它索引设计准则 10.3.3 索引的特征 10.3.4 在文件组上合理放置索引 10.3.5 索引优化建议,10.3.1 什么类型查询适合建立索引,（1）搜索符合特定搜索关键字值的行（精确匹配查询）。 （2）搜索其搜索关键字值为范围值的行（范围查询）。 （3）在表T1中搜索根据联接谓词与表T2中的某个行匹配的行。 （4）在不进行显式排序操作的情况下产生经排序的查询输出，尤其是经排序的动态游标。 （5）在不进行显式排序操作的情况下，按一种有序

12、的顺序对行进行扫描，以允许基于顺序的操作，如合并联接。 （6）以优于表扫描的性能对表中所有的行进行扫描，性能提高是由于减少了要扫描的列集和数据总量。 （7）搜索插入和更新操作中重复的新搜索关键字值，以实施PRIMARY KEY和UNIQUE约束。 （8）搜索已定义了FOREIGN KEY约束的两个表之间匹配的行。 （9）使用LIKE比较进行查询时，如果模式以特定字符串如“abc%”开头进行了索引，使用索引则会提高效率。,10.3.2 其它索引设计准则,（1）一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能 ； （2）覆盖的查询可以提高性能。 （3）对小型表进行索引

13、可能不会产生优化效果 （4）应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。 （5）可以在视图上指定索引。 （6）可以在计算列上指定索引。,10.3.3 索引的特征,在确定某一索引适合某一查询之后，可以自定义最适合具体情况的索引类型。索引特征包括： （1）聚集还是非聚集 （2）唯一还是不唯一 （3）单列还是多列 （4）索引中的列顺序为升序还是降序 （5）覆盖还是非覆盖 还可以自定义索引的初始存储特征，通过设置填充因子优化其维护，并使用文件和文件组自定义其位置以优化性能。,10.3.4 在文件组上合理放置索引,默认情况下，索引创建在基表所在的文件组上，该索引即在该基表上创建。

14、不过，可以在不同于包含基表的文件组的其它文件组上创建非聚集索引。通过在其它文件组上创建索引，可以在文件组通过自带的控制器使用不同的物理驱动器时实现性能提升。 如果表上有聚集索引，数据和该聚集索引将始终驻留在相同的文件组内。因此，可以在基表上创建一个聚集索引，指定另外一个文件组，在该文件组上新建索引（然后可以除去该索引，而只在新文件组内保留基表），从而将表从一个文件组移动到另一个文件组。 如果表的索引跨越多个文件组，则必须将所有包含该表及其索引的文件组一起备份，之后还必须创建事务日志备份。否则，只能备份索引的一部分，导致还原备份时无法恢复索引。 单个表或索引只能属于一个文件组，而不能跨越多个文件

15、组。,10.3.5 索引优化建议,（1）将更新尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。仅使用一个语句，就可以利用优化的索引维护。 （2）使用索引优化向导分析查询并获得索引建议。 （3）对聚集索引使用整型键。另外，在唯一列、非空列或 IDENTITY 列上创建聚集索引可以获得比较好的性能收益。 （4）在查询经常用到的所有列上创建非聚集索引。这可以最大程度地利用隐蔽查询。 （5）物理创建索引所需的时间在很大程度上取决于磁盘子系统 。 （6）检查列的唯一性。 （7）在索引列中要注意检查数据的分布情况。,10.4 索引的创建和删除,10.4.1 创建索引 10.4.2 删除索

16、引,10.4.1 创建索引,1创建索引的方法 （1）利用SSMS直接创建索引。其具体步骤如下： 在SSMS中，展开指定的服务器和数据库，选择要创建索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项，就会出现管理索引对话框，在该对话框中，可以选择要处理的数据库和表 。单击“新建”按钮，则出现新建索引对话框。 选择完成后单击“确定”按钮，即可生成新的索引；单击“取消”按钮，则取消新建索引的操作。,10.4.1 创建索引,（2）利用Transact-SQL语句中的CREATE INDEX命令创建索引。 语法格式： CREATE UNIQUE CLUSTERED| NONCLUS

17、TERED INDEX 索引名 ON 数据表名|视图名( 字段名 ASC | DESC ,.n ) WITH PAD_INDEX ,FILLFACTOR=填充因子 ,IGNORE_DUP_KEY ,DROP_EXISTING ,STATISTICS_NORECOMPUTE ,SORT_IN_TEMPDB ON 文件组名,CREATE INDEX命令,参数说明： （1）UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 （2）CLUSTERED：用于指定创建的索引为聚集索引。 （3）NONCLUSTERED：用于指定创建的索引为非聚集索引。 （4）ASC|DESC：用于

18、指定具体某个索引列的升序或降序排序方向。 （5）PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。必须和填充因子同时使用。 （6）FILLFACTOR =填充因子：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。,CREATE INDEX命令,参数说明： （7）IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。当使用该选项，表示当插入或更新记录时，忽略重复键值。 （8）DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集

19、索引。 （9）STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 （10）SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。,教学管理数据库表的索引设计,1、利用SSMS直接创建索引,例101为数据库“教学管理”中的学生数据表关于student.sno建立聚集索引，关于student.sname建立非聚集索引。 操作步骤如下： 在SSMS中，展开“教学管理”数据库，选择数据表student节点单击右键，在快捷菜单中选择“设计表”。 在设计器窗口中单击右键，在快捷菜单中选择“索引/键”。,2、利用SQL中的CREA

20、TE INDEX命令创建索引,利用SQL中的CREATE INDEX命令创建例10.1索引CREATE INDEX IN_sname ON student(sname) 上述命令关于student.sname建立升序非聚集索引，索引名为IN_sname。 CREATE UNIQUE CLUSTERED INDEX IN_sno ON student(sno)WITH pad_index,fillfactor=100 上述命令关于student.sno建立了升序唯一性聚集索引，索引名为IN_sno，填充因子为100。 如果student表已经有聚集索引，则会出现下列错误信息： 服务器: 消息 1

21、902，级别 16，状态 3，行 1 不能在表 STUDENT 上创建多个聚集索引。请在创建新聚集索引前除去现有的聚集索引 PK_STUDENT。,例102为数据库“教学管理”中数据表关于ame降序建立唯一索引IN_cname。USE 教学管理 GO CREATE UNIQUE INDEX IN_cname ON course(cname DESC),例10.3为数据库“教学管理”中数据表关于teacher.tname升序建立非聚集和非唯一索引IN_tname。USE 教学管理 GO CREATE INDEX IN_tname ON teacher (tname ASC),例10.4为数据库“

22、教学管理”中数据表关于enrollment.sno降序，enrollment.ono升序建立组合唯一索引IN_snoonon，填充因子为90，在插入数据时，可以忽略重复的值。如果已经存在IN_snoonon索引，则先删除后重建。 USE 教学管理 GO CREATE UNIQUE INDEX IN_snoonon ON enrollment(sno DESC,ono ASC) WITH PAD_INDEX, -保持索引开放的空间 FILLFACTOR=90, -填充因子90 IGNORE_DUP_KEY， -忽略重复键值 DROP_EXISTING -如果存在IN_snoonon索引则删除,例

23、10.5为数据库“教学管理”中数据表关于enrollment.grade降序建立非聚集索引IN_grade。USE 教学管理 GO IF EXISTS (SELECT name FROM sysindexes WHERE name=IN_grade) DROP INDEX enrollment. IN_grade -如果存在IN_grade索引删除 CREATE NONCLUSTERED INDEX IN_grade ON enrollment(grade DESC),10.4.2 删除索引,1删除索引的方法 SQL Server 删除索引的主要方法有：利用SSMS删除索引；利用SQL语句中的

24、DROP INDEX命令删除索引。 （1）利用SSMS删除索引 其具体步骤如下： 在SSMS中，展开指定的服务器和数据库，选择要删除索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项，就会出现管理索引对话框，在该对话框中，可以选择要处理的数据库和表 。 选择要删除的索引，单击“删除”按钮。 （2）利用SQL中的DROP INDEX命令删除索引 其语法形式如下： DROP INDEX 索引名，n,10.4.2 删除索引,2删除索引实例分析 USE 教学管理 GO DROP INDEX enrollment.IN_grade,10.5 索引使用中的维护,10.5.1 维护索引

25、的统计信息 10.5.2 维护索引碎片,10.5.1 维护索引的统计信息,例10.7 显示指定索引的统计信息 USE 教学管理 GO DBCC SHOW_STATISTICS (student,ksno) -显示学生表student上ksno索引的统计信息 GO 例10.8 更新指定表的索引统计信息 USE 教学管理 GO UPDATE STATISTICS student -更新学生表student的所有索引的统计 GO 例10.9 对指定数据库中所有表的索引统计进行更新 USE 教学管理 GO EXECUTE sp_updatestats,10.5.2 维护索引碎片,1、索引碎片有两类：内

26、部碎片和外部碎片。 内部碎片： 当索引页里还有空间可利用时，出现的碎片是内部碎片；内部碎片意味着索引占据了比他实际需要还要多的空间。在创建索引时指定一个较低的填充因子，就会产生内部碎片。有一定的内部碎片是好事，他可以因页里有空闲空间而避免插入多行数据时不必分裂页。分裂的新页需要重新建立索引链并且容易导致外部碎片。 外部碎片： 当数据页的逻辑顺序和物理顺序不匹配的时候，或者一个表的存储区不连续时，出现的碎片就是外部碎片。因为外部碎片，需要对多页操作，使访问效率低下，操作成本昂贵。,2、索引碎片的检测,用DBCC SHOWCONTIG命令查看索引碎片 语法格式如下： DBCC SHOWCONTIG

27、 ( table_name | view_name , index_name ) WITH ALL_INDEXES | FAST , ALL_INDEXES| TABLERESULTS , ALL_INDEXES,FAST | ALL_LEVELS ,USE pubs DBCC SHOWCONTIG (authors) WITH FAST , ALL_INDEXES GO -快速查看authors表所有索引碎片情况 DBCC SHOWCONTIG 正在扫描 authors 表. 表: authors（1977058079）；索引 ID: 1，数据库 ID: 5 已执行 TABLE 级别的扫描。

28、 - 扫描页数.: 1 - 扩展盘区开关数.: 0 - 扫描密度最佳值:实际值: 100.00%1:1 - 逻辑扫描碎片.: 0.00% DBCC SHOWCONTIG 正在扫描 authors 表. 表: authors（1977058079）；索引 ID: 2，数据库 ID: 5 已执行 LEAF 级别的扫描。 - 扫描页数.: 1 - 扩展盘区开关数.: 0 - 扫描密度最佳值:实际值: 100.00%1:1 - 逻辑扫描碎片.: 0.00% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。,USE pubs DBCC SHOWCONTIG (authors) W

29、ITH ALL_LEVELS GO DBCC SHOWCONTIG 正在扫描 authors 表. 表: authors（1977058079）；索引 ID: 1，数据库 ID: 5 已执行 TABLE 级别的扫描。 - 扫描页数.: 1 - 扫描扩展盘区数.: 1 - 扩展盘区开关数.: 0 - 每个扩展盘区上的平均页数.: 1.0 - 扫描密度最佳值:实际值: 100.00%1:1 - 逻辑扫描碎片.: 0.00% - 扩展盘区扫描碎片.: 0.00% - 每页上的平均可用字节数.: 6010.0 - 平均页密度（完整）.: 25.75% DBCC 执行完毕。如果 DBCC 输出了错误信息

30、，请与系统管理员联系。,3、删除索引碎片,从索引中删除碎片的方法很多： 1）使用DORP INDEX 和CREATE INDEX命令。当索引支持约束时，不能使用。 2）使用DBCC DBREINDEX命令。它能够在一次操作里重建一个表上的所有索引。但重建索引时表不可用。 3）使用DBCC INDEXDEFRAG命令。删除索引碎片，提高索引扫描性能。该命令是一个联机操作，它不控制长期锁。但索引若完全破坏，则无能为力。格式如下： DBCC INDEXDEFRAG （database_name,table_name | view_name,index_name ),例：对pubs数据库中表authors的aunmind索引进行整理。 DBCC INDEXDEFRAG (pubs,authors,aunmind) 结果： Pages Scanned Pages Moved Pages Removed47 41 4DBCC INDEXDEFRAG (pubs,authors,aunmind)WITH NO_INFOMSGS 结果： 命令已成功完成。,Thank You !,