SQL Server 数据库性能优化

作者:课课家教育the更新于： 2017-05-18 13:47:05

　　 对一个数据库来说，只能做到更优，不可能最优，并且根据实际需要，优化方案也是有所差异的，大概需要我们关心的有它的读取速度、存储空间、可维护性以及可扩展性等，而这些方面往往又是相互矛盾的，那么本文就着重讲Sqlserver的性能调优~

      小编相信不少的朋友，无论是做开发、架构的，还是DBA等，都经常听说“调优”这个词。说起“调优”，可能会让很多技术人员心头激情澎湃，也可能会让很多人感觉苦恼。当然，也有很多人对此不屑一顾，因为并不是每个人接触到的项目都很大，也不是每个人做的项目都对性能要求很高。

　　在主流的企业级开发和互联网应用中，数据库的重要性是不言而喻的，而数据库的性能对于整个系统的性能而言也是至关重要的，这里无庸赘述。

　　sqlserver的性能调优，其实是个很宽广的话题。坦白讲，想从概念到实践的完全讲清楚并掌握透彻，可能至少需要几本书的内容。那么在本文里面，小编就先介绍一些最基本的内容。

　　一、首先搞清楚，性能调优的目标

　　从最直观，最常见的角度来讲，主要包含如下两点：

　　优化响应时间

　　何为“优化响应时间”呢？说的通俗点，就是经过调优后，执行查询、更新等操作的时候，数据库的反应速度更快，花费的时间更少。

　　比较常见的，以前执行某条sql查询语句，可能需要3秒钟，加了索引后，1秒钟不到就搞定了。加索引，这也是最典型最"廉价"的优化手段。

　　在做“优化响应时间”时，需要了解：用户环境，程序，环境，用户和数据等方面的知识。

　　优化吞吐量

　　说起“吞吐量”，那就要想到“并发”了。其实就是“同时处理请求”的能力。如何提高数据库"抗并发"的能力呢？首先要了解sqlserver是如何访问数据的，如何控制并发访问的(事务隔离级别，锁等)，如何与底层操作系统进行交互的，还要了解“多线程、进程”等方面的知识。

　　比较常见的手段，通过降低事务隔离级别（一定程度地牺牲数据一致性等），这种“软手段”通常会起到很好的效果。其次，单台DBServer达到一定瓶颈后，可以通过“集群”等方式，实现请求的“负载均衡”的，来达到“抗并发”的目的，效果也是立竿见影的。

　　基线

　　通俗点讲，就是用来计算或者比较的标准。通常以当前系统性能为基准，或者以匹配系统性能为基准。指各个组件发挥到最大。

　　成本

　　用来升级，更换等提升组件性能时的时间，金钱，劳力等等。

　　基线的定义，以用户期望值为基础，可能会涉及以下因素

　　以往的经验，应用程序的基准，业界的标准，以前版本的情况

　　基线的表示方式，包括：每秒完成的批处理(作业)，每秒传输量，每秒数据量，磁盘扫描时间等等

　　分析影响性能的因素：

　　数据库设计（是否复合范式，是否合理归档、分区、分表等）

　　软件系统（操作系统优化，数据库系统的配置，资源的规划和监控等）

　　硬件基础架构（设备规格，硬件性能，负载均衡，容灾等）

　　Sql语句的写法、索引和统计信息，事务和锁，应用程序访问代码(连接过多、频繁开关等)

　　性能调优的顺序：

　　从左往右，从技术难度、成本、实效去考虑

　　DETECT方法

　　发现问题、探究原因、提供可能的解决方法、执行最有可能的解决方案、确认是否成功解决(如果没有，重复前面的步骤)、完成其余的工作

　　二、数据库设计优化

　　1、不要使用游标

　　使用游标不仅占用内存，而且还用不可思议的方式锁定表，它们可以使DBA所能做的一切性能优化等于没做。游标里每执行一次fetch就等于执行一次select。

　　2、创建适当的索引

　　每当为一个表添加一个索引，select会更快，可insert和delete却大大变慢，因为创建了维护索引需要许多额外的工作。

　　（1）采用函数处理的字段不能利用索引

　　（2）条件内包括了多个本表的字段运算时不能进行索引

　　3、使用事务

　　对于一些耗时的操作，使用事务可以达到很好的优化效果。

　　4、小心死锁

　　按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果某个存储过程先锁定表B，再锁定表A，这可能会导致一个死锁。

　　5、不要打开大的数据集

　　6、不要使用服务器端游标

　　与服务器端游标比起来，客户端游标可以减少服务器和网络的系统开销，并且还减少锁定时间。

　　7、不要忽略同时修改同一记录的问题

　　有时候，两个用户会同时修改同一记录，这样，后一个修改者修改了前一个修改者的操作，某些更新就会丢失。处理这种情况，创建一个timestamp字段，在写入前检查它，如果允许，就合并修改，如果存在冲突，提示用户。

　　8、尽量不要使用text数据类型

　　除非使用text处理一个很大的数据，否则不要使用它。因为它不易于查询，速度慢，用的不好还会浪费大量的空间。一般varchar可以更好的处理数据。

　　9、避免在索引列上使用计算

　　where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。例如：

　　10、不同类型的索引效能是不一样的，应尽可能先使用效能高的

　　数字类型的索引查找效率高于字符串类型，定长字符串char、nchar的索引效率高于变长字符串varchar、nvarchar的索引。

　　三、SQL语句优化

　　1、不要使用select*

　　在select中指定所需要的列，将带来的好处：

　　（1）减少内存耗费和网络的带宽

　　（2）更安全

　　（3）给查询优化器机会从索引读取所有需要的列

　　2、使用参数查询

　　主要是防止SQL注入，提高安全性。

　　3、使用exists或notexists代替in或notin

　　4、isnull或isnotnull操作

　　判断字段是否为空一般是不会应用索引的，因为索引不索引空值。不能用null作索引，任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用isnull或isnotnull的语句优化器都不允许使用索引。

　　推荐方案：用其他相同功能的操作运算代替，如：aisnotnull改为a>0或a>''等。

　　5、<及>操作

　　大于或小于一般情况不用调整，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化。如一个表有100万记录，那么执行>2与>=3的效果就有很大区别了。

　　6、like操作

　　like操作可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用不好则会产生性能上的问题，如lide'%5400%'这种查询不会引用索引，而like'X5400%'则会引用范围索引。

　　7、where后面的条件顺序影响

　　where子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：

　　以上两个查询，两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较。而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

　　8、用union替换or（适用于索引列）

　　通常情况下，用union替换where子句中的or将会起到较好的效果。对索引列使用or将造成全表扫描。注意：这个规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择or而降低。下面的例子中loc_id和region上都有建索引。

　　9、优化groupby

　　提高groupby语句的效率，可以通过将不需要的记录在groupby之前过滤掉。

　　10、使用存储过程

　　可以考虑使用存储过程封装那些复杂的SQL语句或业务逻辑，这样有几个好处：

　　（1）存储过程的执行计划可以被缓存在内存中较长的时间，减少了重新编译的时间。

　　（2）存储过程减少了客户端和服务器的繁复交互。

　　（3）如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。

　　11、用sp_configure'querygovernorcostlimit'或者SETQUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。SETLOCKTIME设置锁的时间。

　　12、使用selecttop或setrowcount来限制操作的行。

　　13、如果使用了in或or等时发现查询没有走索引，使用显式申明指定索引：SELECT*FROMPersonMember(INDEX=IX_Title)WHEREprocessidIN('男'，'女')。

　　14、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌insert来插入(不知java是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值。存储过程就没有这些动作:方法：Createprocedurep_insertasinsertintotable(Fimage)values(@image),在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

　　15、分析selectemp_nameformemployeewheresalary>3000在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

　　小编结语：

　　更多内容尽在课课家教育！