如何优化数据库查询脚本
1. 目的 ............................................................................................................................................. 1 2. 查找数据库的压力点 ................................................................................................................. 2 2.1. SQL Server Profiler ............................................................................................................... 2
2.1.1. 跟踪事件 ...................................................................................................................... 2 2.1.2. 事情的数据列 .............................................................................................................. 3 2.1.3. 参考 .............................................................................................................................. 4 2.2. 监控脚本执行 ......................................................................................................................... 4 3. 分析SQL脚本 ........................................................................................................................... 4 3.1. 索引 ......................................................................................................................................... 5
3.1.1. 聚集索引 ...................................................................................................................... 5 3.1.2. 非聚集索引 .................................................................................................................. 6 3.1.3. 索引统计信息 .............................................................................................................. 8 3.1.4. 参考 .............................................................................................................................. 8 3.2. 数据查询方式 ......................................................................................................................... 8 3.3. 执行计划 ................................................................................................................................. 8
3.3.1. 显示执行计划 .............................................................................................................. 9 3.3.2. 了解执行计划 .............................................................................................................. 9 3.4. 分析SQL语句 ..................................................................................................................... 11
3.4.1. 分析执行计划 ............................................................................................................ 11 3.4.2. IO选项 ....................................................................................................................... 12 4. 优化SQL脚本 ......................................................................................................................... 13 4.1. 优化索引 ............................................................................................................................... 13 4.2. 调整脚本结构 ....................................................................................................................... 14 5. 附件 ........................................................................................................................................... 15 修订日期 修订内容 修订者 版本 1.
1. 目的
Smartlearning系统在客户的应用过程中，引起性能瓶颈的最大压力点在于数据库。有很多数据库脚本在执行过程中占用了太多的CPU及I/O资源，引起系统性能底下。本文档主要讨论一些常见的数据库性能优化方法。
数据库优的方法有很多种（如：数据库配置及内存优化、数据库分表/分区优化等等），本文档只讨论最基本的数据库查询脚本优化。
2. 查找数据库的压力点
引起数据库瓶颈的一般现象是数据库进程长时间占用大量的CPU。一般来说数据库进程长时间占CPU超过20%，则表明数据库存在瓶颈需要进行优化。
如果要优化数据库，那么需要先找到引起数据库压力的查询脚本，这就需要我们使用到SQL Server Profiler工具监控脚本的执行情况。
2.1. SQL Server Profiler
SQL Server Profiler 是用于从服务器捕获 SQL Server事件的工具。可以监控数据库的连接、断开、脚本执行等事件。我们主要使用它查找运行慢的查询语句。
SQL Server Profiler的执行界面如下所示：
2.1.1. 跟踪事件
SQL Server Profiler中的EventClass表示跟踪事件，SQL Server Profiler主要可以跟踪以下几个数据库事件：
? SQL：BatchStarting
用于记录数据库查询执行的开始事件 ? SQL：BatchCompleted
用于记录数据库查询执行完成事件 ? Audit Login
表示与数据库建立连接的事件 ? Audit Logout
表示数据库关闭连接的事件
2.1.2. 事情的数据列
下表列出了常用的几个Microsoft SQL Server Profiler数据列（这些数据列与 SQL 跟踪使用的数据列相同）。
数据列 说明 用程序传递的值填充，而不是由程序名填充的。 ApplicationName 与 SQL Server 实例建立连接的客户端应用程序的名称。此列由该应ClientProcessID 由主机分配给正在运行客户端应用程序的进程的 ID。如果客户端提供了客户端进程 ID，则填充此数据列。 CPU Database ID 事件使用的 CPU 时间（毫秒）。 USE database_name 语句指定的数据库的 ID；如果未对给定实例执行 USE database_name 语句，则为默认数据库的 ID。如果在跟踪中捕获到 ServerName 数据列并且服务器可用，SQL Server Profiler将显示数据库的名称。若要确定数据库的值，请使用 DB_ID 函数。 正在运行用户语句的数据库的名称。 客户端的 SQL Server 用户名。 事件的持续时间（毫秒）。 事件的结束时间。对指示事件开始的事件类（例如 SQL:BatchStarting 或SP:Starting）将不填充此列。 捕获的事件类的类型。 正在运行客户端程序的计算机的名称。如果客户端提供了主机名，则填充此数据列。若要确定主机名，请使用 HOST_NAME 函数。 用户的登录名（SQL Server 安全登录名或 Windows 登录凭据，格式为“域/用户名”）。 已登录用户的安全标识符 (SID)。您可以在 master 数据库的 sys.server_principals 视图中找到此信息。服务器中的每个登录名都具有唯一的 ID。 OLEDB 方法的名称。 一个整数，各种事件都使用它来描述事件要请求或已接收的状态。 一个整数，表示 @@NESTLEVEL 返回的数据。 Windows 用户名。 由服务器代表事件读取逻辑磁盘的次数。这些读取操作数包含在语句执行期间读取表和缓冲区的次数。 事件（如果有的话）的开始时间。 依赖于跟踪中捕获的事件类的文本值。但是，如果跟踪参数化查询，则不以 TextData 列中的数据值显示变量。 由服务器代表事件写入物理磁盘的次数。 DatabaseName DBUserName Duration EndTime EventClass HostName LoginName LoginSid MethodName Mode NestLeve NTUserName Reads StartTime TextData Writes
NTDomainName 用户所属的 Microsoft Windows 域。 2.1.3. 参考
SQL Server Profiler 简介
http://technet.microsoft.com/zh-cn/library/ms181091(v=sql.105)
用数据列描述事件
http://technet.microsoft.com/zh-cn/library/ms190762(v=sql.105)
2.2. 监控脚本执行
为了查找数据库脚本的压力点，我们使用SQL Server Profiler监控占用CPU过高的SQL语句。具体操作步骤如下所示：
? 打开SQL Server Profiler工具，点击新建跟踪按钮，打开“跟踪属性”对话框 ? 在跟踪属性对话框中选择“事件选择”标签页
? 在“事件选择”标签页面点击“列筛选器”按钮，打开“编辑筛选器”对话框 ? 在“编辑筛选器”对话框左则列表中选中“CPU”，设置条件为“大于等于30”，
并选中“排除不包含的行”复选框。
如下图所示：
3. 分析SQL脚本
引起数据库脚本性能低下的原因有很多，分析脚本的方法也是多种多样。在脚本执行中
最能体现脚本执行的两点为占用CPU和I/O。也就是说脚本优化的目的是减少对CPU及I/O资源的占用。
在分析SQL脚本之前我们需要了解一些数据的基本概念，这些内容对我们理解和分析数据库脚本的执行很重要。在这里我们主要讨论以下几个方面的内容：
? 索引 ? 数据查询方式 ? 执行计划
3.1. 索引
索引在数据库中是以B树的结构存储，便与数据是查询与检索。数据库的索引主要分为聚集索引和非聚集索引两种。
3.1.1. 聚集索引
聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。
其实数据表也是一种索引，当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。聚集索引的结构如下图所示：