SqlServer数据库优化方案

第一部分第二部分第三部分第四部分SQLSERVER据库优化方案微软公司的SQLSERVER是个能完备的数据库管理系统，它提供了完整的关系数据库创建、开发和管理功能。现社会信息技术的快速发展，对数据库技术的要求也越来越高，SQLSERVER数库在信息化的过程中得到了广泛的应用。第章数库统述从20世60年开始到现在据技术经过了30多年的发展这30多的历程中数库技术的理论研究和统开发上取得了辉煌的成就立数据技术在现代计算机系统中不可或缺的地位现代信息科学与技术的重要组成部分以及计算机数据处理和信息管理系统的核心。1基本概念与数据库技术密切相关的基本概念包括据据库数据库管理系统和数据库系统四大概念。数（Data）数据是对客观事物的一种描述由能被计算机识别与处理的数值符符号构成的集合，即数据是指描述事物的符号记录。广义地说数据是一种物理符号序列于记录事物的情况是客观事物及其属性进行的一种抽象化及符号化的描述据的概念应包括数据的内容和形式两个方面据内容是指所描述的客观事物的具体特性，也就是通常所说的数据的“值的式则是指数据内容所存储的具体形式数据“类型数据可以用数据类型和值来表示。数库DataBase，DB）数据库是指长期存储在计算机内部组织的可享的数据集合即在计算机系统中按一定的数据模型组织、存储和使用的相关联的数据集合成为数据库。数据库中的数据按照一定的数据模型组织述和存储具有较小的冗余度较高的数据独立性易展性、集中性和享性文的形式存储在存储介质上的。数据库中的数据由数据库管理系统进行统一管理和控制对数据库进行的各种数据操作都是通过数据

库管理系统实现。数库理统DataBaseManagementSystem，DBMS）数据库管理系统是数据库系统的核心，是为数据库的建立、使用和维护而配置的软件，是位于操作系统与用户之间的一层数据管理软件要功能是对数据库进行定义作控制和管理。1)数定义数据的定义包括定义构成数据结构的外模式式和内模式定各个外模式和模式之间的映射，定义模式与内模式之间的映射，定义有关的约束条件。2)数处理对数据的处理操作主要包括对数据库数据的检索、插入、修改和删除等基本操作。3)安管理对数据库的安全管理主要体现在数据库进行并发控制安性检查完整性约束条件的检查和执行、数据库的内部维护（如索引、数据字典的自动维护）等。并且能够管理和监督用户的权限，防止拥护有任何破坏或者恶意的企图。4)数的组织、存储和管理负责分类地组织储管理数库数据定以何种文件结构和存取方式物理地组织数据如实现数据之间的联系以便提高存储空间利用以及提高随机查找、顺序查找、增加、删除和查改等操作的时间效率。5)建和维护数据库建立数据库包括数据库数据的初始化与数据转换等数库包括数据库的转储与恢复、数据库的重组织与重构造、性能的监视与分析等。6)数通信接口提供与其他软件系统进行通信的功能。数库统DataBaseSystem，DBS）数据库系统指在计算机系统中引入数据库后的系统构成般有数据库据管理系统、应用系统、数据库管理员和用户构成。1数据库系统的点数据库系统的点主要有：数据的结构化、高共享性、低冗余度、易扩充、较高的独立性（物理数据独立数独立数据由DBMS统一管理和控数据的安全性Security保护、数据的完整性Integrity保、并发Concurrency制、数据库恢复）。第章数库能化数据库作为一种独立的有组织的可共享的数据集合据的查询访问是数据操作中频度最高的操作据和访问频率达到一定程度的时候的响应速度就至关重要了，

这时候就需要对数据库数据存储的结构和方式进行优化，使其满足系统需要的访问响应速度。2性能影响因素常见的影响数据访问速度的因素，有以下几种：1.没索引或者没有用到索引数据库索引就像书籍中目录一样用户在访问数据库数据时必历所有数据就可以找到需要的数据。创建索引后，可以保证每行数据的唯一性，极大地提高数据检索效率，这是一中牺牲空间换取性能的方法索或者没有用到索引是数据访问速度慢最常见的因素，也是程序设计的一个缺陷所在。2.I/O吞吐量小，形成了瓶颈应I/O吞量是影响数据访问速度的客观因素（硬件因素定的硬件环境下，利用优化的部署方案可适当提高I/O吞量。3.没创建计算列导致查询不优化计算列是一个比较特殊的列不写任何设计类型用户不可以改变该列的值计算列的值是通过一定的函数公式等以另一个或多个列的值为输入值计算出的结果相的计算列，在一些数据查询的时候需要对已有数据进行计算，从而浪费一部分性能。4.内不足对数据库数据的查询访问毫无疑问会占用大量的内存空间内存不足的情况下据的访问速度会受到明显的影响甚至访问出现超时情况，是影响数据访问速度的客观因素。5.网速度慢网络速度慢是影响数据访问速度的客观因素。可通过提高网络访问的位宽来解决。6.查出的数据量过大当查询出的数据量过大时内存的占用系时间的占用等都影响数据访问的速度以采用多次查询、定位查询、和查询数据量控制来解决。7.锁者死锁锁或者死锁在数据库数据访问时会造成访问者等待时间过程或者永久无法获取到资源。这是查询慢最常见的因素之一，是程序设计的缺陷，要尽量避免。8.返不必要的行和列在一般的数据查询中都尽可能的获取数据信息这样造成了不必要的数据遍历大大的增加了数据访问的响应的时间以在一般的查询中尽量查询少的行和列将数据遍历时间降到最低以满足数据输出需求。9.查语句不够优化在数据查询访问过程中使用最频繁的是使用自定义的查询语句进行数据输出的以编写优化的查询语句能够很大程度上提高数据查询访问的速度。

2性能优化数据库性能优化主要是提高数据访问的速度提高数据库响应速度的性能指标能优化主要分为主观因素和客观因素两部分的优化主针对影响性能的客观因素进行优化。2．1主观因素化主观因素主要是指服务器的硬件环境。主要优化有以下几个方面：1、把数、日志、索引放到不的I/O备上，增加读取速度，数据量越大，提高I/O吞吐量越重要；2、纵向横向分割表，减少表尺寸sp_spaceuse3、升级件；4、提高络访问速度；5、扩大务器的内存；配置虚内存：虚拟内存大小应基于计算机上并发运行的服务进行配置，一般设置为物理内存的1.5倍如果安装了全文检索功能，并打算运行Microsoft搜索服务以便行全文索引和查询，可考虑将虚拟内存大小设置为至少计算机中物理内存的3倍6、增加务器个；其中行处理比串行处理更需要资源SQL根系统负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的询适合并行处理。不过更新操作UPDATE、INSERT不能进行并行处理。2．2客观因素化客观因素主要指的是由于设计和开发中存在的缺陷和漏洞；主要优化有以下几个方面：1.优索引（1）根据查询条件建立优化的引、优化访问方式，限制结果集的数据量。注意填充因子要适（最好是使用默认0应该尽量小使字节数小的列建里索引（参照索引的创建要对有限的几个值的字段建立单一索引（如性别字段（2）如果使用LIKE进行询话，简单的使用INDEX是行的，全文索引又太耗费空间。‘N%’使用索引，LIKE‘%N不使用索引。用LIKE‘%N%’查询时，查询耗时和字段值总长度成正比，所以不能用CHAR类型而采用。对于字段的值很长的字段建立全文索引。（3）重建索引DBCCREINDEX，收缩数据和日志DBCCSHRINKDB，DBCCSHRINKFILE。设置自动收日志，对与大的数据库不要设置数据库自动增长，它会降低服务器的性能。

2.数库部署优化（1）DB和APPLICATIONSERVER分离，OLTP和OLAP分；（2）使用分区视图布式分区视图可用于实现数据库服务器联合体合是一组分开管理的服务器，他们互相协作分担系统的处理负荷A、在实现分区视图之前，必须先水平分区表。、创建成员表后，在每个服务器上定义一个分布式分区视图且个视图有相同的名称样引用分布式分区视图名的查询可以在任何一个成员服务器上运行操如同每个成员服务器都有一个原始表的复本一样每服务器上其实只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。3.查语句优化T-SQL的写上有很大的讲究处理查询计划的过程是a查语句的词法、语法检查b、将语句提交给DBMS的询优化器、优化器做代数优化和存取路径的优化；、由预编译模块生成查询规划e在合适的时间提交给系统处理执行f、将执行结果返回给用户。（1）COMMIT和ROLLBACK的别ROLLBACK回所有的事务COMMIT提当前的事务在动态语句中写事务将务写在外面BEGINTRAN（@SQL）COMMITTRANS或者动态SQL写成函数或者存储过程。（2）在大数据两的查询输出SELECT语句中尽量不要使用自定义函数，调用自定义函数的函数时系统调用是一个迭代过程影查询输出性能的查字段时尽可能使用小字段两输出，并在WHERE句或者使用SELECTTOP10/1PERCENT来制回的记录数，使用SETROWCOUNT来制操作的记录数，避免整表扫描返回不必要的数据但浪费了服务器的资源加重了网络的负担，如果表很大的话，在表扫描期间将表锁住，禁止其他的联接访问过很严重的。（3）SQL的释申明对执行查输出没有任何影响。（4）使用计算列对数据进行简计算，尽量避免在查询语句中对数据进行运算。（5）尽可能不使用光标，它会用大量的资源。如果需要ROW-BY-ROW地执行，尽量采用非光标技术，如：客户端循环、临时表、TABLE变、子查询、CASE语句等等。（6）使用PROFILER来跟查询，得到查询所需的时间，找出SQL的问题所在，用索引优化器优化索引。（7）注意和UNION的别有要的时候不要用DUNION一样会降低查询速度，重复的记录在查询里是没有问题的。（8）用sp_configure‘querygovernorcostlimit或者SETQUERY_COVERNOR_COST_LIMIT限制查询消耗的资源。当评估查询消耗的

资源超出限制时，服务器自动取消查询，在查询之前就扼杀掉LOCKTIME设置锁的时间。（9）不要在WHERE子句中列名加函数，如CONVERT等如果必须用函数的时候建计算列在创建索引来替代会多扫描表用EXISTS、NOTEXISTS、INOUTERJOIN替代，其中比IN更快最慢的NOT操作。（10）使QUERYSQL句的查询计划和评估分析是否是优化的SQL。一般的码占用了80%的源，优化的重点就是这些慢的地方。（11）如使用了或者OR等时发现查询没有走索引显式申明指定索引Select*FromFA01(INDEX=IX_SEX)WhereIN(‘01’)。（12）在要对已有数据进行比较复杂计算才能获得查询的结果数据,将需要查询的结果预先计算好放在表,查的时候在SELECT。（13）数库有一个原则是代码离数据越近越好，所有有限选择DEFAULT，依次为RULES，CONSTRAINT来写程序的质量高，速度快。如果要插入大的二进制到IMAGE列使用存储过程，千万不要用内嵌INSERT直插入。因为这样应用程序首先将二进制转换成字符串，服务器收到字符后又将他转换成二进制。存储过程直接传入二进制参数即可，处理速度明显改善，如：CREATEPROCEDUREimage_insert@imagevarbinaryasInsertintotable(fImage)values(@image)。（14）在些时候比IN度更快，更快地根据索引找到范围。由IN会比较多次，所以有时会慢些。（15）尽不要建没有作用的事务例如产生报表时费资源只有在必须使用事务时才建立合适的事务。（16）用OR的字可以分解成个查询，并通过UNION连接多个查询。速度取决与是否使用索引。如果查询需要用联合索引，用UNION执行的效率更高些。（17）尽少用视图视图的率低视图操作比直接对表操作慢以SROREDPROCEDURE来代替特别是不要视图嵌套套视图增加了寻找原始资料的难度视图是存放在服务器上的被化好了的已经产生查询规划的SQL对表数据检索时，不要使用指向多表的视图，否则增加了不必要的系统开销，查询也会受到干扰。没有必要时不要用和ORDERBY，些动作可以改在客户端执行，增加了额外的开销，这同和UNION原理相同。（18）当用SELECT和CREATETABLE时，会锁住系统表（SYSOBJECTS，SYSINDEXES等而塞其他连接的存取。所以千万不要在事务内部使用。如果经常要用到临时表时请使用实表或者临时表变量。尽量少用临时表，用结果集和TABLE类的变量来代替。

（19）在用GROUP子句时用前剔除多余的行避使用HAVING子句剔除行工作。剔除行最优的执行顺序是SELECT的WHERE子句选择所有合适的行，GROUPBY用分组统计行HAVING句用来剔除多余的分组。如果只是分组不进行计算则DISTINCT比GROUPBY速度。2．3SP编程容开发人员如果用到其他库的TABLE或VIEW在当前库中建立VIEW来实现跨库操作，最好不要直接使用database.dbo.table_name为sp_depends不能示出该SP所用的跨库TABLE和VIEW方便校验提交SP前先用SETSHOWPLANON分过查询计划，做自身的查询优化检查。优化应用程序得到高速的运行效率，在SP编过程中应该注意以下几点：1.SQL使用规范A、尽避免大事务操作，慎用HOLDLOCK子句，提高系统并发能力。B、尽避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。C、尽避免使用游标，因为游标的性能较差，如果游标操作的数据超过一万行，那么就应该改写，如果使用了游标，就尽量避免在游标循环中再进行表连接操作。D、注WHERE子句写,必须虑语句顺应该根据索引顺序范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序一直，范围从大到小。尽量不要在WHERE子句的=左边进行函数、算术或其他表达式运算，否则系统可能无法正确使用索引。尽量使用>=”,使用>E、尽使用代SELECTCOUNT（1来判断是否存在记录COUNT函只有在统计表中所有行数时使用，而且COUN