《课件第8章 创建数据库索引.ppt》由会员分享，可在线阅读，更多相关《课件第8章 创建数据库索引.ppt（41页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、LOGO 本章学习目标：本章学习目标：vSQL Server 2016及以前版本中提供及以前版本中提供的与索引相关的功能的与索引相关的功能v如何借助分区表和索引来使数据库更加便如何借助分区表和索引来使数据库更加便于管理和扩展于管理和扩展v实现分区表和索引实现分区表和索引v维护和调优索引维护和调优索引8.1 SQL Server 2016中新增的索中新增的索引引列存储索引列存储索引(columnstore index)是是SQL Server 2012中首次引入的索引类中首次引入的索引类型，是基于列的非聚集索引，用于为涉及型，是基于列的非聚集索引，用于为涉及大量数据的工作负载提高查询性能，通常大
2、量数据的工作负载提高查询性能，通常在数据仓库事实表中使用。在数据仓库事实表中使用。SQL Server 2016为列存储索引引入了为列存储索引引入了两种新功能：聚集列存储索引功能和更新两种新功能：聚集列存储索引功能和更新现有聚集列存储索引功能。现有聚集列存储索引功能。SQL Server 2012引入列存储索引时，引入列存储索引时，只能创建非聚集的列存储索引，并且在创只能创建非聚集的列存储索引，并且在创建之后就不能更新。在建之后就不能更新。在SQL Server 2016中，可以创建一个聚集列存储索引中，可以创建一个聚集列存储索引(就是表就是表)，并且这个表，并且这个表/列存储索引之后是列存储
3、索引之后是可以更新的。可以更新的。SQL Server 2016还针对联机索引做了还针对联机索引做了重大改进。在重大改进。在SQL Server 2016中，现中，现在可以重构单个分区，在以前版本的在可以重构单个分区，在以前版本的SQL Server中是不允许这么做的。对于有非常中是不允许这么做的。对于有非常大的表并且需要把这些表的维护工作分散大的表并且需要把这些表的维护工作分散到几天中的公司，这是一种非常重要的改到几天中的公司，这是一种非常重要的改进。进。8.1 SQL Server 2016中新增的索中新增的索引引SQL Server 2016中的另外一个新功能中的另外一个新功能是能够在是
4、能够在SHOWPLAN查询计划中显示列查询计划中显示列存储索引。存储索引。SHOWPLAN的的EstimatedExecutionMode和和ActualExecutionMode属性有两个可属性有两个可能的值：能的值：Batch或或Row。其。其Storage属属性也有两个可能的值：性也有两个可能的值：RowStore和和ColumnStore。8.2 索引和分区表索引和分区表创建索引以及如何结合使用索引和分区表创建索引以及如何结合使用索引和分区表来管理大型表和进行扩展。来管理大型表和进行扩展。8.2.1 理解索引理解索引为了实现良好的索引设计，首先需要很好为了实现良好的索引设计，首先需要很
5、好地理解索引提供的优点。地理解索引提供的优点。SQL Server中的索引与书中的目录具有中的索引与书中的目录具有相同的功能，让相同的功能，让SQL Server能够尽快定能够尽快定位和检索查询中请求的数据。位和检索查询中请求的数据。考虑一本考虑一本500页的书，其中有大量章节，页的书，其中有大量章节，但是没有目录。要找到书中的某一小节，但是没有目录。要找到书中的某一小节，读者需要翻页并依次浏览每一页，直至找读者需要翻页并依次浏览每一页，直至找到感兴趣的节。到感兴趣的节。这个类比也适用于这个类比也适用于SQL Server数据库表。数据库表。如果没有适当的索引，如果没有适当的索引，SQL Se
6、rver就必就必须扫描包含表中数据的所有数据页。对于须扫描包含表中数据的所有数据页。对于包含大量数据的表，这会是非常耗费时间包含大量数据的表，这会是非常耗费时间的、资源密集型的操作。这就是索引如此的、资源密集型的操作。这就是索引如此重要的原因所在。重要的原因所在。根据索引存储数据的方式以及索引的内部根据索引存储数据的方式以及索引的内部结构、作用和定义方式，可以采用多种方结构、作用和定义方式，可以采用多种方式分类索引。下面将简要描述这些索引类式分类索引。下面将简要描述这些索引类型。型。8.2.1 理解索引理解索引1. 基于行的索引基于行的索引基于行的基于行的(或行存储或行存储)索引是传统的索引，
7、索引是传统的索引，将数据存储为数据页中的行。这些索引包将数据存储为数据页中的行。这些索引包括聚集索引和非聚集索引。括聚集索引和非聚集索引。1) 聚集索引聚集索引聚集索引基于键列存储和排序表的叶级数聚集索引基于键列存储和排序表的叶级数据。实际的存储页链接在一起，所以可以据。实际的存储页链接在一起，所以可以按照聚集键的顺序依次读取表，导致的按照聚集键的顺序依次读取表，导致的I/O开销极小。每个表只可以有一个聚集开销极小。每个表只可以有一个聚集索引，因为只可以按照一种顺序排序数据，索引，因为只可以按照一种顺序排序数据，而且聚集索引代表了实际的表数据。而且聚集索引代表了实际的表数据。让实际数据聚集起来
8、，有助于提高顺序读让实际数据聚集起来，有助于提高顺序读取的性能。一个数据页可能包含已经排序取的性能。一个数据页可能包含已经排序好的几行到许多行实际数据。好的几行到许多行实际数据。所有非聚集索引中都会包含聚集键字段，所有非聚集索引中都会包含聚集键字段，以便引用回聚集索引的叶级行。如果选择以便引用回聚集索引的叶级行。如果选择了一个大聚集索引键，这会影响非聚集索了一个大聚集索引键，这会影响非聚集索引的大小。引的大小。当表定义包括主键约束时，就会默认创建当表定义包括主键约束时，就会默认创建聚集索引。好的聚集索引与好的主键有一聚集索引。好的聚集索引与好的主键有一些相同的属性：字段不会改变，并且总是些相同
9、的属性：字段不会改变，并且总是递增。添加新记录时，这种类型的聚集索递增。添加新记录时，这种类型的聚集索引键有助于减少页拆分。引键有助于减少页拆分。8.2.1 理解索引理解索引2) 非聚集索引非聚集索引非聚集索引包含索引键值和行定位器，行非聚集索引包含索引键值和行定位器，行定位器指向实际的数据行。如果没有聚集定位器指向实际的数据行。如果没有聚集索引，行定位器就是实际数据行的索引，行定位器就是实际数据行的RowID指针。如果存在聚集索引，行定位器就是指针。如果存在聚集索引，行定位器就是该行的聚集索引键。该行的聚集索引键。可以优化非聚集索引以满足更多的查询，可以优化非聚集索引以满足更多的查询，降低查
10、询响应时间，减少索引大小。下面降低查询响应时间，减少索引大小。下面将描述最重要的两种经过优化的非聚集索将描述最重要的两种经过优化的非聚集索引。引。3) 覆盖索引覆盖索引覆盖索引是满足覆盖索引是满足(覆盖覆盖)特定查询的所有字特定查询的所有字段需求的索引。通过在段需求的索引。通过在CREATE INDEX语句中使用语句中使用INCLUDE短语，非聚集索引短语，非聚集索引在叶级可以包含非键列，以帮助覆盖查询。在叶级可以包含非键列，以帮助覆盖查询。这些索引类型可以改进查询性能，并减少这些索引类型可以改进查询性能，并减少I/O操作，因为满足查询所需要的列作为操作，因为满足查询所需要的列作为键列或非键列
11、包括在索引自身中，不需要键列或非键列包括在索引自身中，不需要再读取实际的数据行。再读取实际的数据行。INCLUDE短语使非聚集索引更加灵活，短语使非聚集索引更加灵活，因为键中包含的字段可以具有键中原本不因为键中包含的字段可以具有键中原本不允许的数据类型，并且在计算索引大小或允许的数据类型，并且在计算索引大小或键列的数量时，也不会考虑它们。键列的数量时，也不会考虑它们。8.2.1 理解索引理解索引4) 过滤索引过滤索引过滤索引使用过滤索引使用WHERE子句指示将要索引子句指示将要索引哪些行。因为只是索引表中的部分行，所哪些行。因为只是索引表中的部分行，所以可以创建较小的数据集存储到索引中。以可以
12、创建较小的数据集存储到索引中。过滤索引总是非聚集索引，因为它们选择过滤索引总是非聚集索引，因为它们选择总记录集的一个子集，而总记录集用表上总记录集的一个子集，而总记录集用表上的聚集索引表示。如果查询的的聚集索引表示。如果查询的WHERE子子句可用过滤索引的句可用过滤索引的WHERE子句中的行满子句中的行满足，那么就会在查询计划中选择过滤索引。足，那么就会在查询计划中选择过滤索引。8.2.1 理解索引理解索引2. 基于列的索引基于列的索引基于列的索引是在单独列上创建的索引。基于列的索引是在单独列上创建的索引。基于列的索引有两种主要类型：列存储索基于列的索引有两种主要类型：列存储索引引(SQL S
13、erver 2012中首次引入中首次引入)和和XML索引索引(提供了提供了XML列中的值的索引列中的值的索引)。在在SQL Server 2012中，这些索引是不中，这些索引是不可更新的可更新的要在索引中添加值，就需要要在索引中添加值，就需要重新构建索引。重新构建索引。非聚集列存储索引具有以下限制：非聚集列存储索引具有以下限制：可以索引表中列的子集可以索引表中列的子集(聚集表或堆表聚集表或堆表)。只能通过重新构建索引来更新。只能通过重新构建索引来更新。可以与表上的其他索引合并。可以与表上的其他索引合并。需要额外的空间，以便在索引中独立于行需要额外的空间，以便在索引中独立于行值存储列的副本。值存
14、储列的副本。8.2.1 理解索引理解索引在在SQL Server 2016中，聚集列存储索中，聚集列存储索引是可以更新的，但是具有如下限制：引是可以更新的，但是具有如下限制：聚集列存储索引不能有任何非聚集索引，聚集列存储索引不能有任何非聚集索引，它是表上唯一的索引。它是表上唯一的索引。存储为聚集列存储索引的表不能用在复制存储为聚集列存储索引的表不能用在复制中。中。存储为聚集列存储索引的表不能使用变更存储为聚集列存储索引的表不能使用变更数据捕捉，这是数据捕捉，这是SQL Server功能。功能。存储为聚集列存储索引的表不能关联任何存储为聚集列存储索引的表不能关联任何FILESTREAM列。列。聚
15、集列存储索引功能只能在聚集列存储索引功能只能在SQL Server 2016的企业版、开发版和评估版中使用。的企业版、开发版和评估版中使用。在聚集列存储索引上不能创建主键，也不在聚集列存储索引上不能创建主键，也不能创建引用完整性约束。能创建引用完整性约束。8.2.1 理解索引理解索引3. 内存优化索引内存优化索引SQL Server 2016创建了新的索引来支创建了新的索引来支持内存优化表。散列索引保存在内存中，持内存优化表。散列索引保存在内存中，用于访问内存优化用于访问内存优化(Hekaton)表中的数据。表中的数据。所需要的内存量与散列索引使用的桶计数所需要的内存量与散列索引使用的桶计数有
16、关。有关。内存优化的非聚集索引将对从内存优化表内存优化的非聚集索引将对从内存优化表中访问的数据进行排序。这些索引只能使中访问的数据进行排序。这些索引只能使用用CREATE TABLE和和CREATE INDEX语句创建，并且是为范围排序扫描语句创建，并且是为范围排序扫描(按照排按照排序顺序读取大量数据序顺序读取大量数据)创建的。当内存表加创建的。当内存表加载到内存中时会创建这些索引，它们不会载到内存中时会创建这些索引，它们不会被持久化到物理表。被持久化到物理表。8.2.1 理解索引理解索引4. 其他索引类型其他索引类型SQL Server中还有其他一些类型的索引，中还有其他一些类型的索引，用于
17、支持具体的开发主题。用于支持具体的开发主题。1) XML索引索引XML索引是一种特殊的索引类型，用于索索引是一种特殊的索引类型，用于索引存储在引存储在XML列中的值。这些索引拆分列中的值。这些索引拆分XML列并存储详细信息，供在列并存储详细信息，供在SQL查询中查询中快速检索。快速检索。XML列可能很大，在运行时将列可能很大，在运行时将XML数据拆分成可读的数据元素会减缓大数据拆分成可读的数据元素会减缓大型型XML查询。通过使用查询。通过使用XML索引，这种拆索引，这种拆分是提前完成的，在运行时读取很快。分是提前完成的，在运行时读取很快。2) 全文索引全文索引创建全文索引是为了支持创建全文索引
18、是为了支持SQL Server中中的全文搜索功能。全文索引让用户和应用的全文搜索功能。全文索引让用户和应用程序能够在程序能够在SQL Server表中查询基于字表中查询基于字符的数据。必须先在表上创建全文索引，符的数据。必须先在表上创建全文索引，然后才能在全文搜索中包含它。然后才能在全文搜索中包含它。全文搜索是全文搜索是SQL Server中的一项可选功中的一项可选功能，在使用前必须先打开。能，在使用前必须先打开。8.2.1 理解索引理解索引3) 空间索引空间索引空间索引对空间数据列进行索引。空间数空间索引对空间数据列进行索引。空间数据列包含据列包含GEOMETRY或或GEOGRAPHY类型的
19、值。空间索引支持处理空间数据的类型的值。空间索引支持处理空间数据的操作，如内置的地理方法操作，如内置的地理方法(STContains()、STDistance()、STEquals()、STIntersects()等等)。为了让优化器能够。为了让优化器能够选择查询，必须在查询的选择查询，必须在查询的JOIN或或WHERE子句中使用这些方法。子句中使用这些方法。8.2.1 理解索引理解索引5. SQL Server使用索引的方式使用索引的方式为了实现优秀的索引设计，很重要的一点为了实现优秀的索引设计，很重要的一点是深入了解是深入了解SQL Server使用索引的方式。使用索引的方式。在在SQL
20、Server中，查询优化器组件确定中，查询优化器组件确定用于执行查询的最符合成本效益的选项。用于执行查询的最符合成本效益的选项。查询优化器评估大量查询执行计划并选择查询优化器评估大量查询执行计划并选择具有最低成本的执行计划。具有最低成本的执行计划。8.2.2 创建索引创建索引使用使用T-SQL命令手动创建索引：命令手动创建索引：(1) 打开打开SQL Server Management Studio并连接到并连接到SQL Server实例。实例。(2) 确保安装了确保安装了http:/ 打开新的查询窗口并遵循如下列表中打开新的查询窗口并遵循如下列表中提供的一种示例查询语法：提供的一种示例查询语
21、法：首先创建一个表的副本。在本例中，使用首先创建一个表的副本。在本例中，使用下面的脚本创建下面的脚本创建HumanResources.Employee表的一表的一个副本，此脚本将删除这些例子中不需要个副本，此脚本将删除这些例子中不需要的字段：的字段：8.2.2 创建索引创建索引首先创建一个表的副本。在本例中，使用首先创建一个表的副本。在本例中，使用下面的脚本创建下面的脚本创建HumanResources.Employee表的一表的一个副本，此脚本将删除这些例子中不需要个副本，此脚本将删除这些例子中不需要的字段：的字段：8.2.2 创建索引创建索引要在刚才创建的表上创建聚集索引，可使要在刚才创建
22、的表上创建聚集索引，可使用如下所示的用如下所示的CREATE CLUSTERED INDEX T-SQL命令：命令：要创建非聚集索引，可使用要创建非聚集索引，可使用T-SQL命令命令CREATE NONCLUSTERED INDEX。NONCLUSTERED是默认索引类型，可是默认索引类型，可以省略：以省略：8.2.2 创建索引创建索引要创建覆盖索引，可以使用要创建覆盖索引，可以使用T-SQL命令命令CREATE NONCLUSTERED INDEX以以及及INCLUDE关键字，如下所示：关键字，如下所示：要创建过滤索引，可以使用要创建过滤索引，可以使用T-SQL命令命令CREATE NONC
23、LUSTERED INDEX以以及及WHERE关键字，如下所示：关键字，如下所示：8.2.2 创建索引创建索引要创建聚集列存储索引，首先应该删除表要创建聚集列存储索引，首先应该删除表上的其他所有索引。然后，像下面这样使上的其他所有索引。然后，像下面这样使用用T-SQL命令命令CREATE COLUMNSTORE INDEX：通过删除聚集索引，将该聚集列存储索引通过删除聚集索引，将该聚集列存储索引转换回一个行存储表：转换回一个行存储表：要创建非聚集列存储索引，需要使用要创建非聚集列存储索引，需要使用T-SQL命令命令CREATE COLUMNSTORE INDEX，如下所示：，如下所示：8.2.
24、3 使用分区表和索引使用分区表和索引分区表可以帮助优化系统。分区表是将单分区表可以帮助优化系统。分区表是将单张表分布到多个单元上的一种方式，其中张表分布到多个单元上的一种方式，其中每个单元都可以建立在独立的文件组中。每个单元都可以建立在独立的文件组中。恰当使用的话，分区和索引可以帮助管理恰当使用的话，分区和索引可以帮助管理大量数据，并以更快的速度将信息返回给大量数据，并以更快的速度将信息返回给查询。查询。创建分区是为了帮助把表分解为更小的单创建分区是为了帮助把表分解为更小的单元，并给元，并给SQL查询引擎提供更好的技术来查询引擎提供更好的技术来优化查询，包括并行和分区清除。索引和优化查询，包括
25、并行和分区清除。索引和分区进一步帮助了查询引擎，通过添加一分区进一步帮助了查询引擎，通过添加一层数据访问来帮助标识和定位满足查询所层数据访问来帮助标识和定位满足查询所需要的行。需要的行。每个分区不只包含聚集索引的键字段，还每个分区不只包含聚集索引的键字段，还在每行中包含分区键。分区内的行根据分在每行中包含分区键。分区内的行根据分区键物理存储在一起。在分区表上构建的区键物理存储在一起。在分区表上构建的索引可以使用与分区表相同的分区函数索引可以使用与分区表相同的分区函数/模模式进行分区，也可以使用自己的分区函数式进行分区，也可以使用自己的分区函数和模式，还可以不分区。当非聚集索引使和模式，还可以不
26、分区。当非聚集索引使用与基础表相同的分区键用与基础表相同的分区键(聚集索引键聚集索引键)分分区时，就称为分区对齐索引。区时，就称为分区对齐索引。添加其他非分区对齐的索引可以提高性能。添加其他非分区对齐的索引可以提高性能。如果许多查询中都没有分区键，查询性能如果许多查询中都没有分区键，查询性能可能会降低。此时，创建非聚集、非分区可能会降低。此时，创建非聚集、非分区对齐的索引应该可以提高性能。对齐的索引应该可以提高性能。8.3 索引维护索引维护每次在每次在SQL Server表中插入、更新或删表中插入、更新或删除数据时，都会相应地更新索引。更新索除数据时，都会相应地更新索引。更新索引时，叶级页的数
27、据将被移动，以支持索引时，叶级页的数据将被移动，以支持索引的排序顺序，这可能导致索引碎片。引的排序顺序，这可能导致索引碎片。在行存储索引中，被删除或修改的行可以在行存储索引中，被删除或修改的行可以重用空的空间，但是页分割可能导致出现重用空的空间，但是页分割可能导致出现碎片。在列存储索引中，应该定期重新构碎片。在列存储索引中，应该定期重新构建索引以回收删除或更新操作导致的空的建索引以回收删除或更新操作导致的空的空间，以及更新所有非聚集列存储索引，空间，以及更新所有非聚集列存储索引，这是非常重要的。这是非常重要的。随着时间的推移，数据页中的数据分布可随着时间的推移，数据页中的数据分布可能会变得不再
28、平衡。一些数据页中数据的能会变得不再平衡。一些数据页中数据的填充可能非常稀疏，而其他数据页则被填填充可能非常稀疏，而其他数据页则被填满。过多稀疏填充的数据页会带来性能问满。过多稀疏填充的数据页会带来性能问题，因为需要读取更多的数据页来检索请题，因为需要读取更多的数据页来检索请求的数据。求的数据。另一方面，接近填满的页可能会在插入或另一方面，接近填满的页可能会在插入或更新数据时产生页分割。当发生页分割时，更新数据时产生页分割。当发生页分割时，会将大约一半的数据移动到新创建的数据会将大约一半的数据移动到新创建的数据页中。这种经常执行的重新组织操作会消页中。这种经常执行的重新组织操作会消耗资源并创建
29、数据页碎片。耗资源并创建数据页碎片。我们的目标是将尽可能多的数据存储到最我们的目标是将尽可能多的数据存储到最少量的数据页中，同时为数据增长留出一少量的数据页中，同时为数据增长留出一定的空间，从而防止过多的页分割。可以定的空间，从而防止过多的页分割。可以通过微调索引填充因子来实现这种微妙的通过微调索引填充因子来实现这种微妙的平衡。平衡。8.3.1 监控索引碎片监控索引碎片在在SQL Server 2016中可以使用提供的中可以使用提供的Data Management Views(数据管理数据管理视图，视图，DMV)来监控索引碎片来监控索引碎片(包括列存储包括列存储索引索引)。最有用的。最有用的D
30、MV之一是之一是sys.dm_db_index_physical_stats，它提供每个索引的平均碎片信息。它提供每个索引的平均碎片信息。例如，可以按照如下所示查询例如，可以按照如下所示查询sys.dm_db_index_physical_stats DMV：8.3.1 监控索引碎片监控索引碎片图图8-1显示了此查询的结果。显示了此查询的结果。8.3.1 监控索引碎片监控索引碎片在此在此DMV的执行结果中可以观察到具有较的执行结果中可以观察到具有较多碎片的索引。具有较高碎片百分比的索多碎片的索引。具有较高碎片百分比的索引必须进行碎片整理以避免产生性能问题。引必须进行碎片整理以避免产生性能问题。
31、根据碎片的类型根据碎片的类型(内部或外部内部或外部)，SQL Server会以无效率的方式存储和访问碎片会以无效率的方式存储和访问碎片较多的索引。外部碎片意味着没有以逻辑较多的索引。外部碎片意味着没有以逻辑顺序存储数据页。内部碎片意味着页存储顺序存储数据页。内部碎片意味着页存储的数据量少于可以容纳的数据量。这两种的数据量少于可以容纳的数据量。这两种碎片都会导致延长查询时间。进一步的碎片都会导致延长查询时间。进一步的DMV查询可以标识需要整理碎片的具体索查询可以标识需要整理碎片的具体索引。引。SQL Server 2016中的一个新功能允许中的一个新功能允许清理分区索引内的单独分区，并对其进行清
32、理分区索引内的单独分区，并对其进行碎片整理，这在帮助碎片整理，这在帮助DBA的同时，只对性的同时，只对性能产生了最小的影响，并且降低了维护活能产生了最小的影响，并且降低了维护活动的停机时间。动的停机时间。8.3.2 清理索引清理索引索引清理应该始终是所有数据库维护操作索引清理应该始终是所有数据库维护操作的一部分。根据索引由于数据改变而产生的一部分。根据索引由于数据改变而产生的碎片，需要定期执行这些索引清理任务。的碎片，需要定期执行这些索引清理任务。如果索引包含过多的碎片，就可以通过重如果索引包含过多的碎片，就可以通过重新组织或重新构建索引来对索引进行碎片新组织或重新构建索引来对索引进行碎片清理
33、。清理。重新组织索引：重新组织索引：重新排序和压缩叶级页重新排序和压缩叶级页联机执行索引重新排序联机执行索引重新排序(不使用任何长期不使用任何长期锁锁)适合于具有较低碎片百分比的索引适合于具有较低碎片百分比的索引重新构建索引：重新构建索引：重新创建新索引，然后删除原索引重新创建新索引，然后删除原索引回收磁盘空间回收磁盘空间重新排序和压缩邻近页中的行重新排序和压缩邻近页中的行使用企业版中提供的联机索引重新构建使用企业版中提供的联机索引重新构建选项选项更加适合于具有较多碎片的索引更加适合于具有较多碎片的索引8.3.2 清理索引清理索引表表8-2列出了列出了DimCustomer表的索引操表的索引操
34、作的一般性语法。作的一般性语法。8.4 使用索引改进查询性能使用索引改进查询性能SQL Server 2016包含一些包含一些DMV，可以，可以用于微调查询。用于微调查询。DMV可用于显示特定查询可用于显示特定查询的表面执行统计数据，如查询执行的次数、的表面执行统计数据，如查询执行的次数、执行的读写次数、消耗的执行的读写次数、消耗的CPU时间量、索时间量、索引查询使用情况统计数据等。引查询使用情况统计数据等。可以使用通过可以使用通过DMV获得的执行统计数据来获得的执行统计数据来微调查询，例如，可以重构微调查询，例如，可以重构T-SQL代码来代码来利用并行性和现有的索引。还可以使用这利用并行性和
35、现有的索引。还可以使用这些些DMV来标识遗漏的索引、未利用的索引，来标识遗漏的索引、未利用的索引，以及标识需要执行碎片整理的索引。以及标识需要执行碎片整理的索引。例如，研究例如，研究AdventureWorksDW数据数据库的库的FactInternetSales表中的现有索表中的现有索引。如图引。如图8-2所示，所示，FactInternetSales表已经有了良好构建的索引。表已经有了良好构建的索引。图8-2 AdventureWorksDW数据库的FactInternetSales表中的索引8.4 使用索引改进查询性能使用索引改进查询性能为了说明查询调优过程，依次运行一系列为了说明查询调
36、优过程，依次运行一系列步骤以生成可以通过步骤以生成可以通过DMV显示的执行统计显示的执行统计数据：数据：(1) 删除删除FactInternetSales表中的现表中的现有索引有索引ProductKey和和OrderDateKey，如下所示：如下所示：8.4 使用索引改进查询性能使用索引改进查询性能(2) 执行如下脚本执行如下脚本3次：次：8.4 使用索引改进查询性能使用索引改进查询性能图图8-3显示了执行的显示了执行的T-SQL脚本和结果。脚本和结果。根据计算机上的可用资源不同，执行结果根据计算机上的可用资源不同，执行结果可能有所不同。可能有所不同。图8-3 执行的T-SQL脚本和结果图8.
37、4 使用索引改进查询性能使用索引改进查询性能(3) 运行如下脚本以分析上述查询的执行运行如下脚本以分析上述查询的执行统计数据：统计数据：8.4 使用索引改进查询性能使用索引改进查询性能图图8-4显示了主要由显示了主要由sys.dm_exec_query_stats DMV报报告的执行统计数据。告的执行统计数据。图8-4 由sys.dm_exec_query_stats DMV报告的执行统计数据8.4 使用索引改进查询性能使用索引改进查询性能(4) 查询查询sys.dm_db_missing_index_details DMV，以检查是否报告了遗漏的索引，以检查是否报告了遗漏的索引，如下所示：如
38、下所示：图图8-8-5显示了显示了sys.dm_db_missing_index_details DMVsys.dm_db_missing_index_details DMV的的结果。结果。使用使用sys.dm_db_missing_ index_details DMV可以快速标识是否可以快速标识是否需要索引。数据库引擎优化顾问需要索引。数据库引擎优化顾问(Database Engine Tuning Advisor，DTA)是标识遗漏索引的另一种方式，并且是标识遗漏索引的另一种方式，并且有一个向导来帮助完成标识遗漏索引的过有一个向导来帮助完成标识遗漏索引的过程。从程。从SQL Server
39、Management Studio的的“工具工具”菜单中可以执行菜单中可以执行DTA。图8-5 sys.dm_db_missing_index_details DMV的结果8.4 使用索引改进查询性能使用索引改进查询性能(5) 继续进行查询调优，在继续进行查询调优，在FactInternetSales表上创建表上创建ProductKey和和OrderDateKey索引，索引，如下所示：如下所示：8.4 使用索引改进查询性能使用索引改进查询性能(6) 再次执行第再次执行第(2)步中定义的步中定义的Internet_ResellerProductSales查查询询3次。图次。图8-6显示这个查询的
40、读取次数显显示这个查询的读取次数显著改善，这也将改善这个查询的整体执行著改善，这也将改善这个查询的整体执行时间。时间。图8-6 查询的读取次数8.5 数据库引擎优化顾问数据库引擎优化顾问自自SQL Server 2005以来，提供给数据以来，提供给数据库管理员的一款比较有用的工具是数据库库管理员的一款比较有用的工具是数据库引擎优化顾问引擎优化顾问(DTA)。在本章中已经看到，。在本章中已经看到，使用使用DTA可以分析数据库以查找遗漏的索可以分析数据库以查找遗漏的索引并给出其他性能调优建议，如分区和索引并给出其他性能调优建议，如分区和索引视图。引视图。DTA接受如下类型的工作负载：接受如下类型的
41、工作负载：SQL脚本文件脚本文件(*.sql)跟踪文件跟踪文件(*.trc)XML文件文件(*.xml)跟踪表跟踪表计划缓存计划缓存 8.5 数据库引擎优化顾问数据库引擎优化顾问图图8-7显示了显示了DTA的工作负载选择界面，的工作负载选择界面，包括新的包括新的Plan Cache选项。选项。DTA带给带给DBA和和SQL Server开发人员的开发人员的显著优点是能够快速生成数据库性能改进显著优点是能够快速生成数据库性能改进建议，而不需要知道底层的数据库架构、建议，而不需要知道底层的数据库架构、数据结构、使用模式甚至是数据结构、使用模式甚至是SQL Server查询优化器的内部工作原理。查询
42、优化器的内部工作原理。图8-7 DTA的工作负载选择界面8.6 索引太多的成本索引太多的成本太多的索引会产生与大量额外数据页关联太多的索引会产生与大量额外数据页关联的附加系统开销，查询优化器需要遍历这的附加系统开销，查询优化器需要遍历这些数据页。同样，过多的索引需要更多的些数据页。同样，过多的索引需要更多的磁盘空间，并且需要花费更多的时间来完磁盘空间，并且需要花费更多的时间来完成维护任务。成维护任务。DTA通常会推荐大量的索引，特别是在对通常会推荐大量的索引，特别是在对许多查询分析工作负载时。隐藏在背后的许多查询分析工作负载时。隐藏在背后的原因是需要分别分析每个查询。较好的做原因是需要分别分析
43、每个查询。较好的做法是根据需要逐渐增加所应用的索引，同法是根据需要逐渐增加所应用的索引，同时保持基准以比较新的索引是否改进了查时保持基准以比较新的索引是否改进了查询性能。询性能。应该删除未被用户查询使用的索引，除非应该删除未被用户查询使用的索引，除非添加这些索引的目的是支持在特定时间点添加这些索引的目的是支持在特定时间点内发生的任务关键型工作，例如每个月或内发生的任务关键型工作，例如每个月或每个季度的数据提取和报告。未使用的索每个季度的数据提取和报告。未使用的索引会给插入、删除、更新操作以及索引维引会给插入、删除、更新操作以及索引维护操作增加系统开销。护操作增加系统开销。8.7 小结小结本章介
44、绍了本章介绍了SQL Server中可用的索引类中可用的索引类型，包括行存储索引、列存储索引和其他型，包括行存储索引、列存储索引和其他一些索引类型一些索引类型(XML、空间和全文索引、空间和全文索引)。行存储索引是以排序顺序存储的列的组合，行存储索引是以排序顺序存储的列的组合，为表的叶级数据提供了指针。为表的叶级数据提供了指针。“覆盖覆盖”索引索引是包含了满足查询需要的全部列的索引。是包含了满足查询需要的全部列的索引。列存储索引是基于列的非聚集索引，基于列存储索引是基于列的非聚集索引，基于列中的离散值存储数据。相比于常规的基列中的离散值存储数据。相比于常规的基于行的索引，这种类型的索引具有更多
45、的于行的索引，这种类型的索引具有更多的优点，包括较小规模的索引和更快的数据优点，包括较小规模的索引和更快的数据检索速度。在检索速度。在SQL Server 2016中，列中，列存储索引可更新，并且有一个新的压缩选存储索引可更新，并且有一个新的压缩选项项COLUMNSTORE_ARCHIVE。索引数据库的重要组成部分包括创建分区索引数据库的重要组成部分包括创建分区和索引，以及高级的索引技术，如过滤索和索引，以及高级的索引技术，如过滤索引和覆盖索引。重新组织和重新构建索引引和覆盖索引。重新组织和重新构建索引是一项重要的维护操作，可以用于减少和是一项重要的维护操作，可以用于减少和消除索引碎片。消除索引碎片。SQL Server 2016数据数据库引擎优化顾问库引擎优化顾问(DTA)已经得到增强，现已经得到增强，现在可以帮助你基于计划缓存优化数据库。在可以帮助你基于计划缓存优化数据库。通过使用索引优化查询，为数据库优化再通过使用索引优化查询，为数据库优化再添上一笔，通过利用来自于添上一笔，通过利用来自于DMV的数据即的数据即可实现这一点。还要记住执行如下操作带可实现这一点。还要记住执行如下操作带来的优势：查找未被用户查询使用的索引来的优势：查找未被用户查询使用的索引并删除这些索引。并删除这些索引。LOGO