在一些情况下，查询结构使优化器不能选择最好的处理策略。知道何时发生这种情况以及如何避免它是很重要的。这里主要介绍如下几点：

在小的结果集上操作；

有效使用索引；

避免优化器提示；

使用域和参照完整性；

避免资源密集型的查询；

减少网络传输数量；

减少事务开销；

　　为了改进查询性能，应限制操作的数据量，包括列数和行数。在小结果集上操作减少了查询消耗的资源数量并且增进索引效率。在小结果集上操作应当遵循以下原则：

限制选择列表中的列数；

使用高选择性的WHERe子句来限制返回的行数；

　　1、限制选择列表中的列数

　　在SELECT语句的选择列表中使用最小的列集，不要使用输出结果集中不需要的列。例如，不要使用SELECT * 返回所有的里。SELECT * 使覆盖索引无效。

　　来看查询，在Name上的索引本身的查询很快。

　　

　　而SELECT * 要访问基本表：

　　

　　如图所示：选择列表中的列越少，查询性能越好。同时太多列还增加了网络上的数据传输，从而进一步降低性能。

　　2、使用高选择性的WHERe子句

　　在WHERe子句中引用的列的选择性控制着列上索引的使用。从表中请求大量的行可能不能从索引中得到。因为书签查找的开销。

　　大部分时候，最终用户一次只关注有限数量的行。因此，应该设计数据库应用程序随着用户浏览数据而增量地请求数据。返回大的数据集代价很高，而且这些数据不能作为一个整体使用。

　　一些有效使用索引应该遵循的查询设计原则：

避免不可参数化的搜索条件；

避免在WHERe子句列上使用算数运算符；

避免WHERe子句列上的函数；

　　1、避免不可参数化的搜索条件

　　优化器从索引中获益的能力取决于搜索条件的选择性，从而也就取决于WHERe子句中引用的列的选择性。在WHERe子句中的列上的搜索断言确定列上的索引操作是否可以进行。

　　可参数化的搜索条件一般允许SQLServer在索引中查找一行并读取改行(或搜索条件保持为真的相邻范围内的行)。而不可参数化的搜索条件一般阻止优化器使用WHERe子句中引用列上的索引。排除搜索条件一般不允许SQL Server执行可使用参数的搜索条件所支持的索引查找操作，这主要是由于索引上的数据是排序的原因确定的。例如，!=条件要求扫描所有行以确定匹配的行。

类型 搜索条件 可参数化 包含条件=、>、>=、<、<=、BETWEEN、某些LIKE(xxx%) 不可参数化 排除条件<>、!=、!>、!<、NOT EXISTS、NOT IN、NOT LIKE IN、OR、某些Like(%xxx)

　　1、BETWEEN vs IN/OR

　　考虑如下使用IN的搜索条件查询

　　

　　使用BETWEEN...AND...查询

　　

　　从执行计划上看，两个执行计划相同。

　　但是更仔细地查看执行计划就会从他们的检索机制中发现差别：

　　IN搜索　　　　　　　　　　　　　　　　 BETWEEN...AND搜索

　　  

　　从上面两张图片的对比可以看出，SQL Server将包含4个值的IN条件解释为4个OR条件。同时聚集索引比访问4次。这与IO统计输出的数字也对应。而BETWEEN...AND被解释为>=和<=条件，只访问一次聚集索引。所以，BETWEEN代替IN搜索条件将这个查询的逻辑读数量从8降低到2，扫描数量也从4降低到1。尽管两个查询都是聚集索引列上的查找，优化器使用BETWEEN子句定位一系列行比IN子句快得多。如果能够在IN/OR和BETWEEN条件中选择，那么始终选择BETWEEN条件，因为它一般比IN/OR有效的多。实际上应该进一步使用>=和<=的组合来代替BETWEEN子句。

　　2、LIKE条件

　　使用LIKE搜索条件时，如果可能，尝试在WHERe子句中使用一个或多个前导字符。在LIKE子句中使用前导字符使优化器能够将LIKE条件转换有效使用索引的条件。LIKE条件中前导字符的数量越大，优化器就越能更好地确定有效的索引。如果在LIKE条件中使用一个通配符作为前导字符串将阻止优化器执行索引上的查找，它将扫描整个表。

　　考虑如下搜索：

　　

　　从上图可以看出，SQL Server查询优化器将LIKE条件转化为一个等价的>=和<的条件组合。

　　例如，可以用如下搜索代替LIKE。

　　再看逻辑读数量：

　　

　　而如果是如下搜索：

　　虽然，也是使用了索引，但是已经是全索引扫描了。这点从逻辑读(20次)上就能够看出来：

　　

　　3、!<条件 vs >=条件

　　尽管!<和>=搜索条件都检索相同的结果集，但是他们可能执行不同的内部操作。但SQL Server查询优化器并不是什么都不做的，它会把!<转换为>=搜索条件。

　　如下面的搜索：

　　看它的解释i计划如下：

　　

　　虽然SQL Server查询优化器在许多情况下自动优化查询语法以改进性能，但是不应该依赖它。从一开始就编写高校的查询是好习惯。

　　2、避免WHERe子句列上的算数运算符

　　在WHERe子句中的列上使用算数运算符可以阻止优化器使用该列上的索引，Age列上有非聚集索引。

　　考虑如下语句：

　　

　　如果将SQL语句改成这样子：

　　则执行计划如下：

　　

　　比较明显的一个是索引扫描，一个是索引查找。比如说明，如果WHERe子句写的是Age = 18也是索引查找。那么为什么在左边的算数运算符要索引扫扫描呢？

　　SQL Server对每条记录都执行了一次Age * 2乘法运算。比如上表有1万条记录，实际上是执行了1万次 x * 2 = 18?的判断。

　　因此，为了高效地使用索引并改进查询性能，要避免在WHERe子句中的列上使用算数运算符。

　　3、避免WHERe子句列上的函数

　　和算数运算符一样，WHERe子句列上的函数也伤害查询性能，理由与第二点相同。

　　1、SUBSTRING vs LIKE

　　在下面这条SELECT语句中，使用SUBSTRING将阻止列上索引的使用。

　　

　　如果将SQL子句改为LIKE实现：

　　

　　同样的理由，对于在WHERe子句的函数，SQLServer对每一条记录都执行了一次。

　　2、日期部分(Date Part)比较

　　SQL Server可以将日期和时间数据作为独立的字段或两者组合的DATETIME字段存储。但是，可能需要在一个字段中保存数据和时间，而有时候你只需要日期，这通常意味着必须应用一个转换函数来从DATETIME数据类型中提取日期部分。这么做同样阻止优化器选择该列上的索引。

　　假设我们需要读取Birthday在2007年10月的所有，可以执行如下SELECT语句：

　　

　　列上的DATEPART函数阻止了优化器较好地利用该列上的索引，因此作了一次索引扫描。

　　日期部分比较可以不使用DATETIME列上的函数完成，如下所示：

　　

　　这使优化器高效地利用了索引列。

　　因此，为了使优化器能考虑WHERe子句中引用的列上的索引，要始终避免索引列上的函数。这增进了索引的效率，可以改进查询性能。

　　SQL Server基于开销的优化器根据当前表/索引结构和数据，动态确定查询的处理策略。通常比优化器更聪明通常很困难，所以一般建议在书写SQL语句时避免使用优化器提示子句。

　　1、连接提示

　　连接类型摘要参考表：

索引列上的索引 连接表的一般大小 预排序的JOIN子句 内部表上必须有，外部表上最好有 小 可选 两个表必须有，最有状态是两个表上都有聚集或覆盖索引 大 是 内部表没有索引 任意，最优状态是内部表大，外部表小 否

　　可以用上表中的连接提示指示SQL Server使用特定的连接类型：

连接提示 LOOP JOIN MERGE JOIN HASH JOIN

　　为了理解连接提示对性能的影响，考虑如下SELECT语句。

　　

　　有时候我们可能有我们自己的理由使用我们指定的连接方式，我们可以将哈希匹配更改为嵌套循环：

　　

　　但是从执行统计来看，使用了提示花费的时间却更多：

　　使用连接提示　　　　　　　　　　　　　　　　　　　　　　不使用连接提示

　　 

　　可以看到，有连接提示的查询花费的时间长于没有提示的查询，它还增加了CPU的开销。连接提示强制优化器忽略自己的优化策略而使用查询指定的策略。连接提示一般对查询性能有害，因为：

提示阻止了自动参数化；

阻止优化器动态决定表的连接范围；

　　2、索引提示

　　前面提到过，在WHERe子句列上使用运算符阻止优化器选择该列上的索引。为了改进性能，可以重写查询，不使用WHERe子句上的算数运算符，如对应的例子中所示。作为替代，甚至可以考虑用一个索引提示强制优化器使用该列上的索引。但是，大部分时候，避免索引提示让优化器动态工作更好一些。

　　来看一下查询语句，由于返回结果集较多，因此如果使用非聚集索引，可能书签查找更加消耗资源。所以优化器选择直接扫描聚集表：

　　执行计划与IO统计

　　

　　

　　强制使用索引的效果：

　　执行计划：

　　

　　

　　从执行计划的相对开销和逻辑读来看，很明显，使用索引提示的查询确实损害了性能。由于返回结果集较多，使用非聚集索引书签书签查找需要消耗较大资源。

　　一般情况下，让优化器为查询选择最佳的索引策略，不要使用索引提示来忽略优化器的行为。而且，不使用索引提示使优化器能够动态地随着数据的随时变化而确定最佳的索引策略。

　　域和参照完整性帮助定义和强制列的有效值，维护数据库的完整性。这通过列/表的约束来实现。

　　数据访问通常是查询执行中开销最大的操作，避免冗余的数据访问能帮助优化器减少查询执行时间。域和参照完整性帮助SQL Server 2008优化器分析有效的数据值而不需要物理访问数据，这减少了查询时间。

　　1、非空约束

　　非空列约束用于定义特定咧不能输入Null值从而实现域完整性。SQL Server在运行时强制这一事实以维护该列的域参照完整性。而且，定义非空列约束帮助优化器在查询中该列上使用ISNULL函数时生成一个有效的处理策略。

　　上面的查询本意是，返回Name不等于'B'的行。但是，如果Name列上没有非空约束，那么这个SQL查询就漏掉了Name为Null的行。

　　正确的写法是：

　　加了OR Name IS NULL会造成查询的开销更大，如下图所示：

　　  

　　但是，当数据未知时，也许不能设定为默认值，这时候出现Null是不可避免的，但是要尽可能减少这种情况。

　　当不可避免地要处理Null值时，记住，可以通过过滤索引来从索引中删除Null值(http://www.cnblogs.com/kissdodog/p/3158701.html)，从而改进索引的性能。SQL Server 2008引入的稀疏列提供另外一个帮助你处理Null值的选择。稀疏列首先针对的是更高效地保存Null值，从而减少空间和性能的损失。

　　2、声明参照完整性

　　声明参照完整性用于定义父表和子表之间的参照完整性（主外键关系）。主外键约束能够帮助SQL Server 2008优化器改进性能。

　　考虑如下查询：

　　如果Province与PersonTenThousand有外键约束的情况下,SQL语句可改写为

　　第二条SELECT语句的执行计划被高度优化，不需要访问Province父表。有了声明参照完整性，优化器确定子表中的每个记录在父表中都包含对应的记录。因此，父表和子表的JOIN子句在第二条SELECT语句中是冗余的。

　　域和参照完整性是件好东西，它们不仅确保数据完整性而且还改进性能。子表的外键列应该非空，否则，子表可能有些行在父表中没有出现，这将不能再前一个查询中阻止优化器访问主表。

　　许多数据库功能可以使用各种查询技术来实现。应该采取的方法是使用非常资源友好并基于集合的查询技术。可以用于减少查询覆盖的一些技术有：

避免数据类型转换；

使用EXISTS代替COUNT(*)来验证数据存在；

使用UNIOn ALL代替UNIOn；

为聚合和排序操作使用索引；

避免批查询中的局部变量；

小心地命名存储过程；

　　1、避免数据类型转换

　　SQL Server自动将数据从一种数据类型转换成另一种，这个过程被称为隐含数据类型转换。尽管隐含数据类型转换很有用，但是它增加了查询优化器的开销。为了改进性能，使用与要比较的列相同数据类型的变量/常量。

　　2、使用EXISTS代替COUNT(*)验证数据存在

　　常见的数据库需求之一是验证一组数据是否存在，比如登录查询COUNT(Id)，再判断是否大于0。如：

　　使用COUNT(Id)验证数据的存在是高度资源密集的，因为COUNT(Id)必须扫描表中的所有行。而EXISTS只需要扫描并且在第一个匹配的EXISTS条件的记录处停止。为了改进性能，使用EXISTS代替COUNT(*)方法。

　　使用EXISTS是找到一条匹配的就停止，而COUNT()所有行都要扫描一次，因此EXISTS在很多情况下性能要由于COUNT()，比如要查找的记录较前或者数据库数据量非常大都是使用EXISTS较为有利。

　　3、使用UNIOn ALL代替UNIOn

　　可以使用UNIOn子句连接多条SELECT语句的结果集，从最终的结果集删除重复并且在每个查询上有效地运行DISTINCT。如何参与UNIOn子句的SELECT语句的结果集互相排斥，或者允许最终结果集中有重复的行，则使用UNIOn ALL代替UNIOn。这避免了侦测和删除重复的开销，改进性能。

　　可以看到，在上面的例子中（使用UNIOn），优化器合并两条SELECT语句结果的同时处理重复，因为结果集互相排斥，可以使用UNIOn ALL代替UNIOn。使用UNIOn ALL子句避免侦测重复的开销从而增进性能。

　　执行计划对比如下：

　　

　　优化器足够智能，可以发现何时两个查询产生完全不同的列表，在这些时候可以选择一个有效的UNIOn ALL操作。

　　4、为聚合和排序操作使用索引

　　一般来说，聚合函数如MIN、MAX能从对应列上的索引中获益。列上没有索引，优化器就必须扫描基本表(聚集索引)，检索所有行并执行改组(包含所有行)之上的流集合以识别最小/最大值，如下：

　　执行计划如下:

　　

　　创建索引：

　　再执行同样的语句，执行计划如下：

　　

　　使用非聚集索引，逻辑读大大下降。

　　相似地，在ORDER BY子句中引用的列上创建索引能帮助优化器快速地组织结果集，因为在索引中列值被预先排序好。GROUP BY子句的内部实现也首先排序列值，因为排序的列值使相邻的匹配值可以快速地集合。因此和ORDER BY子句类似，GROUP BY子句也从该子句所引用的列值优先排序中获益。

　　5、小心地命名存储过程

　　存储过程的名称很重要，不应该使用前缀sp_来命名存储过程。开发人员常常为其存储过程添加sp_前缀以便简单地识别存储过程。但是，SQL Server判断带有这个sp_前缀的存储过程可能是存在于master数据库之中的系统存储过程。当带有sp_前缀的存储过程被提交执行时，SQL Server按照以下顺序查找循环存储过程：

在master数据库中；

根据提供的限定符(数据库名或所有者)在当前数据库中；

如果未指定架构，在当前数据库中使用dbo作为架构；

　　因此，尽管用户创建的具有sp_前缀的存储过程存在于当前数据库，但是仍然首先查找master数据库。甚至在存储过程以数据库名称限定时，仍然如此。

　　

　　存储过程在第一次执行将其执行计划添加到过程缓冲，随后的执行重用缓冲的执行计划除非需要计划的重编译。

　　注意，在SQL Server尝试在过程缓冲中定位存储过程的计划之前出发SP_CacheMiss事件。SP:CacheMiss事件是由于SQL Server在master数据库中查找存储过程而发生，尽管存储过程的本次执行已经正确地使用用户数据库名称限定。

　　sp_前缀的这种特征在创建一个名称与现有的系统存储过程相同的存储过程时变得更加有趣。

　　这个用户自定义存储过程的执行导致master数据库中的系统存储过程sp_addmessage的执行。输出如下：

　　看到，每次都执行master里面的那个sp_addmessage这个存储过程了。

　　请不要试图在这个存储过程上执行两次DROP PROC语句，在第二次执行时，master数据库中的系统存储过程将被卸载。

　　这就是不要为存储过程使用sp_前缀的原因。

　　数据库应用程序往往执行多个查询以实现数据库操作。除了优化单个查询的性能之外，优化批的性能也很重要。为了减少多次网络传输的开销，考虑一下技术：

同时执行多个查询；

使用SET NOCOUNT；

　　1、同时执行多个查询

　　将一组查询作为批或存储过程同时提交是可取的方式。除了减少数据应用程序和服务器之间的网络传输，存储过程还提供多种性能和管理的好处。这意味着应用程序中的代码必须能够处理多个结果集，还意味着T-SQL代码可能需要处理XML数据或其他大的数据集，而不是单行插入或更新。

　　2、使用SET NOCOUNT

　　在执行一个批或存储过程时，还必须考虑一个事实。在批或存储过程中的每个查询执行之后，服务器报告所影响的行数。

　　(<Number> row(s) affected)

　　这个信息返回给数据库应用程序并增加网络开销。使用T-SQL语句SET NOCOUNT来避免这个开销，如下所示。

　　注意，SET NOCOUNT语句不会造成任何存储过程的重编译问题。

　　SQL Server中的每个操作查询被作为一个原子操作来进行，这样可使数据库表的状态保持一致性。SQL Server自动进行这一工作，并且这不能被禁用。如果一个一致性的状态到另一个一致性状态的转换需要多个数据库查询，则跨越这些查询的原子性必须使用显示定义的数据库事务来维护。每个原子操作的新旧状态被维护于事务日志(磁盘)中以确保持久性，这保证了院子操作的结果在成功完成之后不会丢失。原子操作在其执行期间使数据库所与其他数据操作隔离。根据事务的特性，对降低事务开销有两个总体的建议：

减少日志开销；

减少锁开销；

　　1、减少日志开销

　　数据库查询可能由多个数据操纵查询组成。如果每个查询分别维护原子性，那么在事务日志磁盘上要进行太多的磁盘写入操作以维护每个原子操作的持久性。因为磁盘活动与内存和CPU活动相比非常慢，过多的磁盘活动增加数据功能的执行时间。

　　创建一个测试表：

　　插入一千行的操作(每插入一行，写一次日志)：

　　每条Insert语句的执行都是原子操作，SQL Server将为其每次操作写入事务日志。

　　减少日志磁盘写操作数量的一个简单方法是将操作查询包含在一个明确的日志当中(插一千行写一次日志)，如

　　定义的事务范围(BEGIN TRANSACTION和COMMIT命令之间)将原子性扩展到包含在事务中的多条INSERT语句。这减少了日志磁盘写操作数量并改进数据库功能的性能。

　　为了测试这一条理论，在每个WHILE循环前后运行如下T-SQL命令

　　最佳的方法是处理数据集而不是单个行。WHILE循环可能是个天生开销很大的操作，就像游标一样。所以，运行一个避免使用WHILE循环而代之于基于数据集的方法将会更好。

　　运行这个查询并在前后使用DBCC SQLPERF()函数，将会显示日志已用空间的增长少于4%，并且，它运行时间几乎是瞬间完成。

　　但是要注意一点，在日志中包含太多的数据操纵查询，事务的持续时间将加长。在这时候，所有试图访问事务中引用资源的其他查询将被阻塞。

　　2、减少锁开销

　　默认情况下，所有4种SQL语句(SELECT、INSERT、UPDATE和DELETE)都使用数据库锁来将其工作与其他SQL语句隔离。这种锁管理增加了查询的性能开销。查询性能可以通过请求更少的锁来改进。而且，其他查询的性能也因为获得自己的锁所需要等待的时间较短而得到改进。

　　默认情况下，SQL Server可以提供行级锁，对于工作于大量行的查询，在所有单独的行上请求行锁为锁管理进程增加了很大的开销。可以减小锁的粒度来减少锁开销，比如，使用页面或表级别锁。SQL Server考虑锁的开销来动态地进行锁的升级。因此，一般来说，没有必要手工提升锁的级别。但是，如果有必要，可以使用如下的锁提示来编程控制查询的并发性。

　　相似地，默认情况下，SQL Server为SELECT语句使用于INSERT、UPDATE和DELETE语句不通的锁。这使SELECT语句可以读取没有被修改的数据。在某些情况下，数据可能相当静态，不会经受太多修改。这种情况下，可以使用以下方法之一来减少SELECT语句的锁开销。

　　将数据库标识为READ_onLY(只读)，如下所示。

　　这使用户能够从数据库中检索数据，但是阻止他们修改数据。这个设置立刻生效。如果偶然需要修改数据库，它可以暂时地被转换为READ_WRITE(读写)模式：

　　在一个文件组中放置特定表，并且标识该文件组为只读：

　　这使得可以只限制在特定文件组上的表的访问为只读，而保持其他文件组上表的数据访问为读写状态。这个文件组设置立即生效。如果特定表偶尔需要修改，则对应文件组的属性可以临时被转换为读写模式。

    以上就是本篇文章【八、降低事务开销】的全部内容了，欢迎阅览 ！ 文章地址：http://dfvalve.xrbh.cn/news/1270.html 
     资讯      企业新闻      行情      企业黄页      同类资讯      首页      网站地图      返回首页 迅博思语资讯移动站 http://keant.xrbh.cn/ , 查看更多