MSSQL优化SQL语句以提高数据库访问性能

类型：电子教程大小：8.5M语言：中文评分：8.3标签:立即下载目录1。执行计划是什么？执行计划依赖什么信息？2.统一SQL语句的编写，减少解析开销。3.减少SQL语句的嵌套。4.使用“临时表”临时存储中间结果。5.OLTP系统SQL语句必须使用绑定变量。6.斜场绑定变量。7.begin tran事务应该尽可能小。8.一些SQL查询语句应该添加nolock9。添加nolock后，分页频繁的表容易被跳过或重复读取。10.聚集索引不是建立在表的order字段上的，并且该表容易出现页面拆分。11.使用复合索引提高多个where条件的查询速度。13.使用like进行模糊查询时，要注意不要尽可能使用前者和SQL Server表的三种连接方式。15.Row_number将导致对临时表进行表扫描和分页。执行计划依赖什么信息？执行计划是数据库根据SQL语句和相关表的统计信息制定的查询方案，由查询优化器自动分析。例如，如果使用一条SQL语句从有100，000条记录的表中查找一条记录，查询优化器将选择“索引搜索”方法；如果表被存档，只剩下5000条记录，查询优化器将改变方案，采用“全表扫描”的方法。可见执行计划不是固定的，是“个性化”的。生成正确的“执行计划”有两个要点：SQL语句是否清楚地告诉查询优化器它想要做什么？查询优化器获得的数据库统计信息是否是最新的和正确的？- .从对偶中选择*从对偶中选择*实际上是大小写不同的，所以查询分析器认为是两条不同的SQL语句，必须解析两次。生成2个执行计划。所以作为程序员，要保证同一条查询语句处处一致，不允许有更多的空间！- .一般来说，这样复杂的语句通常是有问题的。我拿着这2页的SQL语句去咨询原作者，他说太长了，一时半会儿听不懂。可以想象，即使是最初的作者也可能会阅读混乱的SQL语句，数据库也是如此。一般来说，通常将Select语句的结果作为子集，然后从该子集进行查询。但是，根据经验，如果嵌套超过三层，查询优化器很容易给出错误的执行计划。因为它晕了。毕竟像人工智能这样的东西，比人的分辨力还要差。如果人晕了，我可以保证数据库也会晕。此外，执行计划可以重用，SQL语句越简单，重用的可能性就越大。但是，只要一个复杂的SQL语句的一个字符发生变化，就必须重新解析，然后大量的垃圾就会被填充到内存中。你可以想象数据库的效率会有多低。- .后者查询在tempdb中，可以避免在程序中多次扫描主表，大大减少程序执行中“共享锁”对“更新锁”的阻塞，从而减少阻塞，提高并发性能。

- .上面两句话，从变更时间为' 2010-10-20 00:00:01 '的订单头中选择*从变更时间为' 2010-09-22 00336000:01 '的订单头中，被查询优化器认为是不同的SQL语句，需要解析两次。如果绑定变量从采用changetime @ chgtime @ chgtime的订单头中选择*就可以传入任何值，这样大量类似的查询就可以重用执行计划，可以大大减轻数据库解析SQL语句的负担。一次分析和多次重用是提高数据库效率的原则。- .例如，当where条件中的字段是“斜字段”时。“斜场”是指这一栏的数值大部分都是一样的，比如一份人口调查表，其中“民族”一栏90%以上是汉族。那么，如果一个SQL语句询问30岁的汉族人口，“民族”一栏必须放在where条件下。这时，如果使用绑定变量@nation，就会出现很大的问题。试想一下，如果@nation传入的第一个值是“汉族”，那么整个执行计划必然会选择表扫描。然后，第二个值被引入到布依族，可能只占万分之一，所以应该通过索引搜索。但由于重用了第一次分析的“汉族”执行计划，将第二次采用表扫描。这个问题被称为“绑定变量窥探”，建议不要将绑定变量用于“倾斜字段”。- .实际上，这是begin tran的最小化形式，就像一个begin tran隐含在每个句子的开头，一个commit隐含在结尾。在某些情况下，我们需要显式地声明begin tran。例如，要进行“插入、删除和修改”操作，我们需要同时修改几个表，这要求要么修改几个表成功，要么修改不成功。Beginttran可以起到这样的作用，它可以把几条SQL语句放在一起执行，最后一起提交。好处是保证数据的一致性，但没有什么是完美的。Begin tran付出的代价是，所有被SQL语句锁定的资源在提交之前都无法释放。可以看出，如果Begin tran捕获的SQL语句太多，数据库的性能会很差。在提交大事务之前，其他语句会被阻塞，导致许多阻塞。Begin tran使用的原则是，在保证数据一致性的前提下，开始tran陷阱的SQL语句越少越好！在某些情况下，可以使用触发器来同步数据，而不必使用begin tran。- .在oracle中没有必要这样做，因为oracle的结构比较合理，并且有一个还原表空间来保存“数据的正面镜像”。如果数据在修改过程中没有提交，那么您读取的是它在修改前的副本，它被放在还原表空间中。这样，甲骨文的读和写就可以互不影响，这也是甲骨文广为称道的地方。SQL Server的读写会互相阻塞。为了提高并发性能，可以在一些查询中添加nolock，这样可以在读取时允许写入，但缺点是可能会读取未提交的脏数据。使用nolock有三个原则。

(1)如果查询结果用于“插入、删除、修改”，则不能添加nolock！(2)查询的表属于频繁分页，谨慎使用nolock！(3)使用临时表可以保存“数据伏笔”，其功能类似于oracle的撤销表空间。如果可以使用临时表来提高并发性能，请不要使用nolock。- .然而，由于“插入、删除和更改”同时发生，在某些情况下，一旦数据页已满，就不可避免地会出现页面拆分，此时，nolock的查询正在发生。例如，由于页面拆分，已经在页面100上读取的记录可能被分成页面101，这可能使nolock query在读取页面101时重复读取数据，导致“重复读取”。同样，如果第100页上的数据在被读取之前被分成第99页，nolock查询可能会错过该记录，从而导致“跳过读取”。上面提到的小伙伴，添加nolock后，有些操作报错。估计可能会因为nolock查询出现重复读取，两个相同的记录会被插入到其他表中，这当然会导致主键冲突。- .对于该表，顺序添加订单编号。如果在orderid中添加了聚集索引，那么所有新添加的行都会添加到末尾，这样不容易导致频繁的页面拆分。但是，由于大多数查询都是基于客户号的，因此将聚集索引添加到contactid是有意义的。而contactid不是订单表的序列字段。例如，如果张三的联系人id为001，则张三的订单信息必须放在该表的第一个数据页上。如果张三今天下新订单，订单信息不能放在表格最后一页，而是放在第一页！如果第一页写满了呢？对不起，但是这张表中的所有数据都必须移回来，以便为这个记录腾出空间。SQL Server的索引不同于oracle的索引。SQL Server的聚集索引实际上是根据聚集索引字段的顺序对表进行排序，这相当于Oracle的索引组织表。SQL Server的聚集索引是表本身的一种组织形式，所以效率很高。正因为如此，插入一条记录，它的位置不是随机放置的，而是应该按顺序放在数据页上。如果该数据页上没有空间，将导致页面拆分。所以很明显，聚集索引不是建立在表的order字段上的，表容易出现页面分裂。一旦遇到一种情况，一个好友的表被重新索引后，插入效率明显下降。估计是这样的。表的聚集索引可能不是建立在表的order字段上的，并且表经常被存档，因此表的数据以稀疏状态存在。比如张三下了20个订单，但最近3个月只有5个订单，归档策略是保留3个月的数据，那么张三过去的15个订单就已经归档了，留下了15个空缺，发生插入时可以重用。在这种情况下，因为有可用空间，所以不会发生页面拆分。但是，查询性能会相对较低，因为在查询过程中必须扫描没有数据的空缺。重建聚簇索引后，情况就变了，因为重建聚簇索引就是重新排列表中的数据，原来的空位没有了，页面填充率很高，所以在插入数据的时候经常会发生页面拆分，所以性能下降很大。对于聚集索引不是建立在顺序字段上的表，是否要降低页填充率？是否要避免重建聚集索引？是一个值得考虑的问题！- .

而且是专门为where条件设置的索引，已经排序，所以查询速度比单个索引快。综合指数的领先领域必须是具有高选择性的领域。例如，有三个字段：日期、性别和年龄。看，应该用哪个字段作为引导字段？显然，“日期”应该作为引导字段。日期是三个字段中最具选择性的字段。这里有一个例外。如果日期也是聚集索引的前导字段，可以直接去聚集索引，不用构建复合索引，效率比较高。不要将聚集索引构建成“复合索引”。聚集索引越简单越好。选择性越高越好！聚集索引包含2个字段，这是可以接受的。但是，如果有2个以上的字段，应该考虑将一个自添加字段作为主键，聚集索引可能不是主键。- .由于“%”用在yue前面，所以查询必须扫描整个表。除非必要，否则不要在关键字前添加%。- .散列连接SQL Server 2000只有一种连接模式：——嵌套循环连接。如果的结果集很小，默认情况下会将其作为外观。a中的每条记录都应该在B中扫描，实际扫描的行数相当于一个结果集中的行数x B结果集中的行数。所以如果两个结果集都很大，那么Join的结果就很糟糕。合并联接是在SQL Server 2005中添加的。如果表a和表b的联接字段恰好是聚集索引所在的字段，那么表的顺序已经安排好了，只要两边放在一起。这种连接的代价相当于表a的结果集中的行数加上表b的结果集中的行数，一个相加，一个相乘，说明合并连接的效果比Nested Loop Join好很多。如果连接的字段上没有索引，那么SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于给A表和B表的结果集临时添加索引，所以SQL2005的效率比SQL2000有很大的提升，这是一个重要的原因。综上所述，在联接表时要注意以下几点：(1)在联接字段中尽量选择聚集索引所在的字段；(2)仔细考虑where条件并最小化表a和b的结果集；(3)如果很多联接联接字段缺少索引，而你还在使用SQL2000，那就做好升级工作......................用临时表分页更好：用ROW_Number分页：CPU时间=317，265毫秒，经过时间=423，090毫秒；用临时表分页：CPU时间=1，266毫秒，经过时间=6，705毫秒。ROW_Number的实现是基于order by的，排序对查询的影响是明显的。- .