通过在 Microsoft SQL Server 2000 上使用索引来提高应用程序性能
4.60/5 (7投票s)
本文演示了如何开箱即用地开发出性能更好的应用程序，即使您没有 DBA。
业务需求
索引是一项有益但鲜为人知的过程。索引可以解决许多问题，包括锁定/阻塞和瓶颈。可以避免表扫描，并最大限度地减少物理磁盘读取。这对用户实现的性能有直接影响。尽管有这些好处，但关于如何创建索引、在哪些字段上创建索引、它们如何工作以及它们具体如何影响性能仍然存在混淆。
幸运的是，Microsoft 创建了一个索引优化向导，因此可以自动创建合适的索引。这通常会带来显著的性能提升。您不必是专家也可以使用该向导并实现其好处。有几种工具协同工作以使该向导生效。
本文面向最广泛的受众，删除了大部分技术细节。您不需要深入了解 SQL Server 即可遵循此分步索引文章。
流程概述
在第 1 步中，我们将对除主键索引以外没有其他索引的数据库的性能进行计时。为此，我们将结合使用未经定制的 Iron Speed Designer 生成的应用程序和秒表。
在第 2 步中，我们将使用 SQL Server 2000 Profiler 启动跟踪并保存跟踪文件。跟踪文件包含我们录制期间数据库服务器上的所有活动。
在第 3 步中，我们将让索引优化向导为我们分析跟踪文件。该向导将根据录制的*用户*活动推荐索引并创建索引。
最后，在第 4 步中，我们将*再次*计时第 1 步（上文）中的操作，并比较结果。
在开始之前，让我们快速看一下数据库本身。
数据库
该数据库有两个表：SalesOrder 和 Employee。SalesOrder 表大约有 170 万行记录，而 Employee 表有九行。这些是 Northwind 表的副本，已修改以添加主键 (PK)。我复制了现有数据以增加行数。
图 1 - 数据库架构。 SalesOrder 表大约有 170 万条记录。每条记录使用 EmployeeID“链接”到仅包含九行的 Employee 表。
第 1 步 – 应用程序性能计时
图 2 - 显示表屏幕（带筛选器）。 这是一个典型的 Iron Speed Designer 生成的应用程序。这是 Sales Order ShowTable 页面。请注意 Employee、Customer 和 Freight 范围的筛选器。我们将测试没有表索引时应用程序的性能。稍后，我们将运行测试以创建索引。
即使简单的 SQL 语句在索引不正确的数据库上也会运行缓慢：当点击 Sales Order Freight 菜单项时，我们会收到以下错误（图 3）。
图 3 - 应用程序超时。 糟糕。此错误消息表示查询花费时间太长而超时，阻止了行返回。
是什么 SQL 语句导致了这个错误？参见图 4。
图 4 - 数据库视图 vw_SalesOrder_Freight。 此视图从 SalesOrder 表中选择前 1,000 行，其中 Freight 和 ShipVia 匹配某些简单条件。结果按 CustomerID 升序排序。
就这样？！？这个语句似乎没什么特别的。该视图只访问一个表。它没有执行任何连接。它似乎奇怪地超时了。然而，该查询暴露了问题：缺乏索引导致 SQL Server 费力地弄清楚应该返回哪些行。
“测试结果”表（见下文）显示了不同的操作以及执行它们所需的时间（以秒为单位）。例如，第一个操作是*点击*SalesOrder 菜单。这会加载 Showtable 页面，其中包含来自 Sales Order 数据库表的前 10 行。从我点击菜单项到页面在我的浏览器中刷新，花费了 4 秒……我执行每个操作几次，以确保缓存和编译不会影响值。
测试结果
| 操作 | 之前（秒） | 之后（秒） |
点击 SalesOrder 菜单 |
4 | |
将 Employee 筛选为 Peacock |
3.5 | |
将 Customer 筛选为 ALFKI |
4 | |
将 Freight 筛选为 10 和 25 |
4 | |
搜索 Futterkiste |
6 | |
按 Customer ASC 排序 |
4 | |
按 Customer DESC 排序 |
4 | |
按 Freight ASC 排序 |
4 | |
按 Freight DESC 排序 |
4 | |
点击 SalesOrder Employee 菜单（执行 vw_SalesOrder_Employee） |
3 | |
点击 SalesOrder Freight 菜单（执行 vw_SalesOrder_Freight） |
42* |
*42 秒后发生超时错误。
渲染浏览器页面涉及几个后台步骤。当出现延迟时，通常是由于数据库由于索引不正确而效率低下地获取记录。我们将使用 Microsoft SQL Server 2000 Profiler 记录数据以进行进一步分析。
第 2 步 – Microsoft SQL Server 2000 Profiler
Profiler 用于“跟踪”（记录）SQL Server 上的活动。活动保存在日志文件中以供分析。我们将使用 Profiler 来记录和保存我们在点击操作时的所有 SQL 语句。
通过单击“开始”、“程序”、“Microsoft SQL Server”、“Profiler”来启动 Profiler。工具加载后，单击“文件”、“新建跟踪”。出现以下对话框：
图 5 - SQL 2000 Profiler：新建跟踪。 在“跟踪”名称中填入您喜欢的任何名称。对于“模板名称”，选择“SQLProfilerTuning”。Profiler 可以记录各种信息。选择“SQLProfilerTuning”后，我们将仅记录正确的活动，这些数据允许调整性能。选择“保存到文件”并指定位置和文件名。您可以在帮助中查找有关此屏幕上其他选项的详细信息。目前，将最大文件大小保留为默认设置 5MB，并选中“启用文件滚动”。最后，单击“运行”按钮以完成设置选项并开始记录 SQL 活动。
图 6 - SQL 2000 Profiler：已录制的 SQL 活动。 发送到 SQL Server 的每个 SQL 语句或存储过程调用都会被记录。执行持续时间（以毫秒为单位）也会被记录。通过单击顶部网格中的一行，我们可以在底部的窗格中看到完整的 SQL 语句。将任何长时间运行的语句复制/粘贴到查询分析器中进行进一步分析很有用。
在图 6 中，之前超时的 SQL 语句被高亮显示。请注意，直到超时，持续时间为 40,126 毫秒，即约 40 秒。
在应用程序中完成工作后，我们可以切换回 Profiler。单击顶部工具栏上的红色“停止”按钮以保存文件。现在我们有了保存的跟踪文件，可以加载索引优化向导了。通过单击“工具”然后选择“索引优化向导”从 Profiler 中运行该工具。
第 3 步 – 索引优化向导
索引优化向导会分析跟踪文件并检查我们之前录制的 SQL 语句。该向导将能够决定何时应该创建索引，何时不应该。如果向导确定应该创建索引，细节将自动处理。本教程不会讨论在自定义情况下应使用哪种索引类型。
图 7 - 索引优化向导：选择数据库。 在此屏幕上，确保选择了正确的数据库。
我经常取消选中“保留所有现有索引”选项。这是因为向导可能会确定某些索引不被使用，因此应该删除。
另外，当“优化模式”设置为“彻底”时，分析会花费更长的时间。如果您处理的是大型跟踪文件（在实际系统中），最好在*下班后*运行此过程和分析。
图 8 - 索引优化向导：指定工作负载。 工作负载文件是包含所有已录制的 SQL 语句和详细信息的跟踪文件。就本教程而言，我们将忽略“高级选项”屏幕中的设置。
图 9 - 索引优化向导：选择表。 选择要优化的表。在大多数情况下，使用“全选表”按钮以在系统中的所有表上获得索引建议。
图 10 - 索引优化向导：处理中。 将弹出一系列消息，通知您处理进度。对于小型跟踪文件，处理应该很快。但是，如果您使用的是包含许多条目的实际跟踪文件，处理可能需要数小时。在工作时间内在生产服务器上执行此操作可能会使系统资源过载。因此，我们建议您在*下班后*运行索引优化向导，当时系统上只有您一个人。
在我们的示例中，加载文件太小，处理大约需要 7 秒。
图 11 - 索引优化向导：索引建议。 以下是建议。基于我们的应用程序使用情况，向导建议我们创建四个或更多新索引（由带有黄色星标的图标指示）。如果我们允许向导为我们创建这些索引，我们应该会实现 67% 的性能提升！这些是估计值，但往往非常准确。
图 12 - 索引优化向导：应用或计划。 现在是实际创建索引的时候了。我们正在一个没有其他用户的开发数据库上工作，因此我们将直接选择“应用更改”和“立即执行建议”。这将为我们创建索引。
创建索引可能需要一段时间，具体取决于每个表中有多少行。更重要的是，创建索引会对数据库应用锁定。这可能会导致其他尝试使用系统的用户出现问题。
生产数据库注意事项：我们建议您在对数据库应用任何更改之前始终备份您的数据库。此外，为避免生产冲突，请仅在*下班后*处理和创建索引。但是，您可以在生产时间内在 Profiler 中创建跟踪文件，以准确记录真实用户在系统上的活动。
创建索引后的应用程序计时
运行与第 1 步（上文）相同的测试，会产生更好的结果。
| 操作 | 之前（秒） | 之后（秒） |
点击 SalesOrder 菜单 |
4 | 2 |
将 Employee 筛选为 Peacock |
3.5 | 1.5 |
将 Customer 筛选为 ALFKI |
4 | 2 |
将 Freight 筛选为 10 和 25 |
4 | 2 |
搜索 Futterkiste |
6 | 3 |
按 Customer ASC 排序 |
4 | 2 |
按 Customer DESC 排序 |
4 | 2 |
按 Freight ASC 排序 |
4 | 2 |
按 Freight DESC 排序 |
4 | 2 |
点击 SalesOrder Employee 菜单（执行 vw_SalesOrder_Employee） |
3 | 1 |
点击 SalesOrder Freight 菜单（执行 vw_SalesOrder_Freight） |
42* | 0.5 |
*42 秒后发生超时错误。
在大多数情况下，我们将响应时间缩短了一半！并且名为 vw_SalesOrder_Freight 的视图，该视图以前在 42 秒后超时，现在可以完美运行。它运行查询并在不到一秒钟内绘制屏幕！
结论
本文解决了应用程序程序员中一个常见的问题。索引通常由全职数据库管理员 (DBA) 执行，而许多团队都没有 DBA。本文演示了如何开箱即用地开发出性能更好的应用程序，即使您没有 DBA。索引可以在没有太多索引技术知识的情况下快速获益。索引优化应作为一个持续的过程。
- 可在此处下载示例项目。此 ZIP 文件包含一个未定制的应用程序，从中截取了屏幕截图。它还包含已填充的数据库和本文示例的跟踪文件。