在学习完本章后，您将可以：
l 生成执行计划
l 阅读执行计划
l 设计数据库，使其可以依据数据的用途高效地存储数据
l 使用聚集索引和非聚集索引
l 索引XML列
l 索引视图
l 整理索引碎片
l 使用数据库引擎优化顾问
第5章学习了如何在数据库中获取数据的汇总信息。如果数据正确地存储在数据库中，SQL Server可以快速而高效地返回包括汇总信息的结果。本章将解释SQL Server存储数据的不同方式，它如何获取数据，以及设计数据库的时候应该考虑什么，以便在SQL Server中获得最高的执行效率。
6.1 了解执行计划
SQL Server执行查询的时候，首先会确定执行该查询的最佳方式。这个决定包括如何且以何种顺序来访问和联接数据，如何且何时执行计算和聚合等等。这些工作由SQL Server中 一个称为“查询优化器”的子系统负责。查询优化器使用数据分布的统计信息、与查询涉及的数据库对象相关的元数据、索引信息和其他因素来计算多种执行计划的 可能性。对于每一种可能，查询优化器会基于数据的统计信息估计出执行的开销并选择执行开销最小的计划。当然，对于有些查询来说，执行这个计算的过程可能超 过了按效率最低的计划执行所花费的时间，因此，SQL Server并不计算每一个查询的所有计划。因而，SQL Server有一个复杂的算法来找出一个合理的、接近最小可能开销的计划。这个执行计划生成之后，会被存储在一个缓存器中(大部分在SQL Server的虚拟内存中)。这个查询随后由数据库引擎按该计划所指示的方式执行。
注意 缓存器中的执行计划可以在执行相同或者类似查询的时候重用。因此，执行计划会尽量存储在缓存器中。要想深入了解缓存执行计划，请访问http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx，参考题为“Batch Compilation, Recompilation,and Plan Caching Issues in SQL Server 2005”的白皮书。
SQL Server查询优化器能否针对给定的查询生成高效的执行计划，取决于以下两个因素：
l 索引 就像一本书的索引一样，数据库索引提供了在表中快速查询特定行的能力。每一张表中可以存在许多索引。在表中索引的支持下，SQL Server查询优化器可以找出并使用正确的索引来优化对数据的访问。如果没有索引，查询优化器只有一个选择，那就是对表中的数据进行全部扫描以找出要找的数据行。本章后文将介绍索引是如何工作的以及如何设计和创建索引。
l 数据的分布统计 SQL Server会保存数据分布的有关统计信息。如果这些统计信息丢失或者过时了，查询优化器就无法计算出高效的执行计划。在许多情况下，统计信息会自动生成并更新。本章后文将介绍如何生成统计信息，以及如何管理这些信息。
可以看出，执行计划的高效与否决定了这个查询是否能够在毫秒、秒、甚至分钟数量级的时间内完成，因此对于SQL Server性能，执行计划的生成是至关重要的。我们可以通过分析低效查询的执行计划来确定是否丢失了索引，数据分布统计信息是否过时或丢失了，或SQL Server是否选择了一个低效的计划(这种情况不常发生)。
注意 当然，一个好的执行计划也可能造成查询被低效执行。在这种情况下，查询优化并不是问题。问题可能更多出现在其他方面，例如查询设计、对资源的争夺、磁盘I/O (输入/输出)、内存、CPU和网络等等。您可以访问www.microsoft.com/ technet/prodtechnol/sql/2005/tsprfprb.mspx，阅读白皮书“Troubleshooting Performance Problems in SQL Server 2005”，了解这方面的情况。
Ø 查看查询执行计划
1. 从“开始”菜单中，依次选择“所有程序”|“Microsoft SQL Server 2005”|“SQL Server Management Studio”。单击“新建查询”按钮打开一个“新建查询”窗口并在“可用数据库”下拉菜单中选择“AdventureWorks”将数据库上下文更改为AdventureWorks。
2. 执行以下SELECT语句。这个示例的代码包含在示例文件Viewing Query Plans.sql中。
SELECT SalesOrderID, OrderQTY
FROM Sales.SalesOrderDetail
WHERE ProductID = 712
ORDER BY OrderQTY DESC
3. 按Ctrl+L或者在“查询”菜单上选择“显示估计的执行计划”显示这个查询的执行计划。执行计划如图6.1所示。
估 计执行计划是在不真正执行查询的情况下生成的。查询由查询优化器进行优化，但是并没有执行。在处理运行时间长的查询时，查询优化器的这一特性具有明显的优 势，因为没有必要在查询完成之后再看执行计划。阅读图形化显示的执行计划时，应该从右向左，从上向下阅读。每一个图标代表计划中的一个运算符，并且图中的 箭头表示了在这些运算符之间的数据交换过程。箭头的宽度代表运算符之间传递数据的数量。这里不打算具体介绍每一个可用的运算符，但会介绍图6.1所示的执行计划中的运算符：
l SQL Server使用聚集索引扫描来访问数据。这种扫描是真实的数据访问操作，详见后文描述。
l 数据随后传递到排序运算符，它将根据语句的ORDER BY子句来对数据进行排序。
l 数据随后发送至客户。
图6.1 查看“执行计划”
在学习索引和联接的时候，我们将讨论SQL Server所用的最重要的操作符。完整的操作符列表请参见SQL Server Books Online的主题“Graphical Execution Plan Icons”。
4. 鼠标指针移到“聚集索引扫描”操作符，此时会出现一个黄色窗口，如图6.2所示。
这个窗口提供了此操作的详细信息。刚才，只知道SQL Server用扫描操作来获取数据。但是在这个窗口中可以看到，它会基于Sales.SalesOrderDetail表中的聚集索引执行一次聚集索引扫描操作来找出ProductID为712的记录。这些信息可以在“谓词”区域找到。同样，这个窗口还显示了估计的开销、估计的行数和估计行的大小。行数是基于SQL Server为该表所存储的统计信息而估计出的，而开销数是基于统计信息和参考系统的开销数得出的，因此根据开销数并不能计算出这个查询会在计算机上运行多长的时间。这些信息只能用于判断一个操作和其他操作相比是节约还是昂贵。
5. 以上有关操作符的信息还可以在SQL Server Management Studio的“属性”窗口中看到。右键单击操作符图标，在弹出菜单中选择“属性”即可打开“属性”窗口。
6. 执行计划还可以保存。右键单击要保存的计划，然后在弹出菜单中选择“将执行计划另存为”来保存该执行计划。执行计划将以XML的格式存储，扩展名为.sqlplan。在SQL Server Management Studio中，在“文件”菜单选择“打开”|“文件”即可打开文件。已保存的执行计划。
7. 目前看到的只是一个查询的估计执行计划。还可以显示一个实际的执行计划。实际的执行计划与估计的执行计划类似，但会包括实际的数量(不是估计的)，例如行数和重绕次数等。按“Ctrl+M”或在“查询”菜单中选择“包括实际的执行计划”来包括实际的执行计划。随后按“F5”执行查询。执行结果会按通常的方式显示出来，但执行计划将显示在“执行计划”选项卡中。
接下来看索引是如何工作以及它们是如何提高查询性能的。在SQL Server中可以定义两种不同类型的索引：聚集索引和非聚集索引。为了理解索引是如何提高数据访问速度的，以及在特定情况下该使用哪种索引，我们首先要了解数据和索引是如何存储在数据文件中的，以及SQL Server如何访问数据文件中的数据。
6.2.1 堆结构
SQL Server数据库中的一个数据文件会以8KB大小分页。每一页可以包括数据、索引、或者其他SQL Server需要为其维护数据文件的数据类型。然而，大多数的页是数据页或者索引页。页是SQL Server读、写数据文件的单元。每一页只包括一个数据对象的数据或索引信息。所以，在每一个数据页上，只能找到一个对象的数据。同样地，在一个索引页上，也只能找到一个索引的信息。在SQL Server 2000中，将一个数据行分别存储在不同页上是不可能的，这意味着一个数据行必须在一页上，这会导致数据行有8 060字节的大小限制(大型对象数据除外)。在SQL Server 2005中，对于变长数据类型，例如nvarchar，varbinary，CLR等，这个限制不复存在。对于变长数据类型，数据行可以跨越几个页，但是对于定长数据类型，一个数据行依然必须存储在一页上。
创建一个没有任何索引的表并向其中插入数据的时候，SQL Server会搜索未被使用的页来存储这些数据。为了追踪哪些页保存了这个表的数据，SQL Server会为每一个表设立一个或多个IAM (索引分配映射)页。这些IAM页指向保存表数据的页。这个表的数据以无索引的方式存储在页上，并且只是通过IAM页联系在一起，所以这个表被称作堆。SQL Server必须通过阅读这个表的IAM页并且通过扫描IAM页指向的所有页来访问一个堆的数据。这种操作称为表扫描。表扫描以无序的方式读取所有数据。如果一个查询要搜索一个特定的行，那么一个堆的表扫描必须读取表中的所有行来找到它，这是一种非常低效的操作。
Ø 检验堆结构
1. 打开SQL Server Management Studio。打开一个“新建查询”窗口并更改数据库上下文为“AdventureWorks”。
2. 在以下示例中，将创建两个表，分别为dbo.Orders 和 dbo.OrderDetails。键入并执行以下语句来创建表并为其添加数据。此示例的完整代码包含在示例文件Examining Heap Structures.sql中。
3. 现在创建了两张堆结构的表。键入以下语句来查询dbo.Orders表。在执行之前按“Ctrl+M”或在“查询”菜单中选择“包括实际的执行计划”来包括实际的执行计划。然后执行这个查询。
SET STATISTICS IO ON;
SELECT * FROM dbo.Orders
SET STATISTICS IO OFF
选项会打开一个特性，使SQL Server将语句执行期间I/O操作的有关信息发回给用户。这是一个用于判断查询I/O开销的极好特性。
4. 切换到“消息”选项卡。您会看到与图6.3相似的信息。
输出信息表明SQL Server需要对表中的数据进行一次扫描并需要为此操作执行178页的读操作(逻辑读)。输出信息同样表明，为了执行此操作没有用到物理读(物理读或者物理先读)。没有物理读的原因是，在此例中，数据已经保存在缓存中。如果您的信息窗口表明对于这个查询进行了物理读，那么在再次执行此查询之后您会看到物理读的数量会比上次执行时少了。因为SQL Server会将最近访问的数据页保存在缓冲中以提高性能。
图6.3 “消息”选项卡
5. 切换到“执行计划”选项卡。在执行计划中，如图6.4所示，可以看出，SQL Server使用一次表扫描操作来访问数据，这是惟一的选择。
图6.4 使用表扫描操作来访问数据
6. 现在对这个查询稍作修改以获取特定的行。
SET STATISTICS IO ON;
SELECT * FROM dbo.Orders
WHERE SalesOrderID =46699;
SET STATISTICS IO OFF;
7. 检查输出的消息和图形化的执行计划。，可以看出SQL Server仍然需要为此查询读取178页并使用表扫描。使用表扫描是因为SQL Server没有索引可用，因此只能在表中扫描所有的数据来找到需要查找的行。
可以看出，在表没有索引的时候，SQL Server会使用表扫描来访问表。这种扫描迫使SQL Server扫描所有的数据，全然不顾表的大小。在非常大的表中，表扫描会花费很长的时间。
6.2.2 表中的索引(1) SQL Server有两种类型的索引：聚集索引和非聚集索引。这两种索引虽然都是平衡树，但是它们的构建方式不同。现在来看看它们有什么区别。
6.2.2.1 聚集索引
聚集索引是一种特殊的平衡树。这种平衡树与前面的平衡树相比，差别在于索引的叶子级。在聚集索引中，叶子级并不包括索引键和指针；它们就是数据本身。这个差异意味着数据并不存储在堆结构中。它们存储在索引的叶子级，并按索引键进行排序。这种设计具有两个优点：
l SQL Server不需要依据指针来访问数据。数据直接存储在索引中。
l 数据依据索引键排序，这是主要的优点。无论什么时候，只要SQL Server需要依据索引键排序数据，都不必再执行排序操作，因为数据已经排好序了。
由于数据包含在聚集索引之中，因此只能为每个表定义一个聚集索引。以下语法用于创建一个聚集索引：
版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！
转载请注明出处，原文链接：https://www.xkablog.com/cjjbc/54003.html