SQL SERVER 数据库管理系统上的SQL查询优化—计算机毕业设计.doc

《SQL SERVER 数据库管理系统上的SQL查询优化—计算机毕业设计.doc》由会员分享，可在线阅读，更多相关《SQL SERVER 数据库管理系统上的SQL查询优化—计算机毕业设计.doc（50页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、SQL Server 数据库管理系统上的SQL查询优化摘要人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中如联机事务处理OLTP或决策支持系统DSS中表现得尤为明显。不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度将有比拟明显的提高。为了最有效地优化 Microsoft SQL Server 的性能，您必须明确在哪些方面性能可以得到最大程度的改良，并集中分析这些方面。否那么，您在这些问题上可能花费大量的时间和精力，而

2、并不能得到明显的性能上的提高。设计一个应用系统似乎并不难，但是要想使系统到达最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择，这取决于特定的应用需求以及开发队伍的技能。关键词：SQL Server数据库 性能优化 查询 数据查询 查询效率 平安性ABSTRACTPeople will often fall into a ill idea while using SQL, namely it is correct to pay close attention to the result to the income very much, a

3、nd has neglected the performance difference that may exist between different implementation methods , this kind of performance difference behaves particularly obviously in the large-scale or complicated database environment (such as on-line transaction processing OLTP or DSS ). I find bad SQL come f

4、rom appropriate index design , fill connection terms and where clause that cant be optimized of copy often among working practice. After carrying on proper optimization to them, its speed of operation improves obviously! In order to optimize the performance of Microsoft SQL Server most effectively,

5、you must define in which respects performance can get maximum improvement , concentrate on analysing these respects. Otherwise, you are on these questions may spend a large amount of time and energy , but can not receive the improvement on obvious performance .It seems easy to design a application s

6、ystem, but it is not an easy thing to want to make the system reach the performance optimized most. Respect of choosing on developing instrument , database design , structure , inquiry design , interface of the application program etc. has excellent choices, this depends on specific application dema

7、nd and develops the skill of the team . Keyword: SQL Server database Performance optimizing Inquiry Data inquiry Inquire about efficiency Security 目 录摘 要-1ABSTRACT-2第一章 综述 (绪论) -41.1 引言-41.2 数据库优化概述-8第二章 设计数据库-82.1 熟悉业务系统- 92.2 标准化与逆标准化-102.3 选择数据类型-102.4 选择索引-14第三章 查询优化-153.1 主键-153.2 通配符-193.3 视图-

8、193.4 存储过程-20第四章 总结-21致谢- 21第一章 综述SQL Server 是一个后台数据库管理系统，他功能强大、操作简便，日益为广阔数据库用户所喜爱，越来越多的开发工具提供了与SQL Server 的接口。了解和掌握SQL Server 的功能，对于一个数据库开发管理人员来说非常必要。SQL Server是一种高性能的大型关系型数据库管理系统，广泛的应用在C/S和B/S体系结构的数据库系统中。评价系统性能优化的标准有：吞吐量、响应时间、并行能力等。本文主要探讨如何优化SQL，取得最快的系统响应速度。1.1引言在一个大型的数据库中，性能成为人们关注的焦点之一，如何让数据库高效有效

9、的运行成为广阔数据库管理人员和开发人员必须要考虑的问题。何谓“好性能性能是一个应用或多个应用在相同的环境下运行时对效率的衡量。性能常用响应时间和工作效率来表示。响应时间是指提交任务到得到返回结果之间的时间开销，含完成一个任务花费的时间。可以从以下三方面来减少响应时间： 减少竞争和等待的次数，尤其是磁盘读写等待次数 用更快的部件 减少利用资源所需的时间绝大多数性能的获得来自于优秀的数据库设计、精确的查询分析和适当的索引。最好性能的获得能够通过确立优秀的数据库设计，在开发时使用SQL Server查询优化器来实现。为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据c

10、ache,过程cache，系统资源和CPU的竞争。1.2数据库优化概述在SQL Server中，有如下优化层次： 应用层大局部性能的获得来自于对SQL应用中查询的优化，这必须是以好的数据库设计为根底的。 数据库层应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据cache。 效劳器层在效劳器层有许多共享的资源，包括数据高速缓存，存储过程高速缓存，锁，CPU等。 设备层指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的IO。 网络层指连接用户和SQL Server的网络。 硬件层指可利用的CPU。 操作系统层理想地，SQL Server是一台机器的唯一主要应用，它必须和操作系

11、统以及其他sybase软件，如Backup Server或SQL Server Monitor共享处理器、内存以及其他资源。在大多数情况下面，我们是对应用层进行优化，因为对应用性能的优化是设计和编程人员乐于接受的功能，其结果能被观测及检验。查询的性能是SQL应用的整个性能的一个关键。应用层上的问题包括以下内容：l 决策支持DSS.和在线事务处理(OLTP)需要不同的性能策略l 事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取l 关联一致性保证的策略。对数据查询修改时需要考虑join操作对性能的影响l 索引可以改善查询性能，但也会增加修改数据的时间l 为了平安而设

12、立的审计限制了性能在应用层优化的选项包括：l 远程处理或复制处理能够把决策支持从OLTP机器中别离出来l 利用存储过程来减少编译时间和网络的利用l 利用最少量的锁去满足你的应用需要数据库层的问题包括：l 建立备份和恢复方案l 在设备上分布存储数据l 审计操作影响性能；仅审计你所需的l 日常的维护活动将导致性能的降低和导致用户不能操作数据库表在数据库层上优化选择包括：l 利用事务日志的阀值来自动转储事务日志防止其超出使用空间l 在数据段中用阀值来监视空间的使用l 利用分区来加速数据的装入l 对象的定位以防止硬盘的竞争l 把重要表和索引放入cache中，保证随时取得效劳器层的问题有：l 应用的类型

13、效劳器是支持OLTP还是DSS，或者两者都支持l 所支持的用户数影响优化决策随着用户数的增加，对资源的竞争会发生改变l 网络负荷l 当用户数和事务数到达一定的数量时复制效劳器或其他分布式处理是一个解决的方法效劳器层的优化的选项包括：l 优化内存一个关键的配置参数和其他方面的参数l 决策是客户端处理还是效劳器端处理有些处理能在客户端进行吗l 配置cache的大小和IO的大小l 增加多个CPUl 为空闲时间排定批处理任务和生成报表l 工作负荷发生改变，重新配置特定参数l 决定是否可能把DSS移到另一个SQL效劳器中设备层设备层的问题包括：l 主设备、包含用户数据库的设备，用户数据设备，或数据库日志

14、是否要镜像l 怎样在设备之间分布系统数据库、用户数据库和数据库日志l 为获得对堆表插入操作的高性能，是否有必要进行分区设备层上优化的选项包括：l 用多个中等大小的设备及多个控制器可能比用少量的大设备有更好的IO性能l 分布数据库，表和索引以在不同的设备上进行IO装载网络层实际上，SQL Server的所有用户都是通过网络存取他们的数据。网络层上的主要问题有：l 网络的流量l 网络的瓶颈l 网络的速度网络层上优化的选项包括：l 配置包的大小，以使其与应用的需要相匹配l 配置子网l 分隔出繁忙的网络运用l 创立一个高容量的网络l 配置多个网络引擎l 更好地设计应用，限制所需的网络传输硬件层在硬件层

15、上的问题包括l CPU的效率l 磁盘的存取：控制器和磁盘l 磁盘备份l 内存的使用在硬件层上优化的选项包括：l 增加CPU以适应工作负荷l 配置调度程序以提高CPU利用率l 遵循多处理器应用设计指导以减少竞争l 配置多个数据cache操作系统层操作系统层的主要问题有：l 文件系统是否被SQL Server独占使用l 内存管理精确估算操作系统和其他程序的内存占用l CPU的利用整个系统共有多少处理器可用?有多少分配给SQL Server在操作系统层优化的选项包括：l 网络接口l 在文件和原始分区之间选择l 增加内存l 把客户操作和批处理移到其他机器上l SQL Server利用多个CPU第二章

16、设计数据库要在良好的SQL Server方案中实现最优的性能，最关键的是要有一个很好的数据库设计方案。在实际工作中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以要设计好数据库就要从以下几点来讨论。2.1 熟悉业务系统对业务系统的熟悉程度对整个数据库系统的性能有很大影响，一个对业务不熟悉的设计人员，尽管有丰富的数据库知识，也很难设计出性能最正确的数据库应用系统。所以我们在做“三江学院师资管理系统这个工程前跟指导老师和用户收集了充足的资料，通过这些资料和经过我们的分析，我在底层数据库建立了13张数据库表，它包括教师表Teacher、党派表Clan、用户表DBA、入党表J

17、oinClan、类别表Kind、学位学历表konwledgeDegree、进修情况表operation、联系 表Phone、兼职情况表Plurality、工作简历表Resume、任职情况表SchoolDuty、任课情况表SchoolTeach、学科表Subject。通过这些表我建立了E-R图党派 m入党入党时间入党地点 m 1 1 m 拥有工作简历 m 1 n 1 m 三江学院任课情况任课学科三江学院任职情况任职属于进修进修情况 联系 m 1 m 1 m拥有学历学位拥有教师 兼职情况 m 1 1 m 兼职 类别分类 12.2 标准化与逆标准化 数据库被标准化后，减少了数据冗余，数据量变小，数据

18、行变窄。这样SQL Server 2000的每一页可以包括更多行，那么每一区里的数据量更多，从而加速表的扫描，改良了单个表的查询性能。但是，当查询涉及多个表的时候，需要用很多连接操作把信息从各个表中组合在一起，导致更高的CPU和I/O花销。那么，有很多时候需要在标准化和逆标准化之间保持平衡，用适当的冗余信息来减少系统开销，用空间代价来换取时间代价。有查询教师全部信息表TeacherInfo，它里面记录了教师根底信息，教师类别信息，教师兼职信息，教师任课信息，教师入党信息，教师任职信息，教师学科信息，教师进修情况信息.这些信息分别在教师表、类别表、兼职情况表、任课情况表、入党表、任职情况表、学科

19、表、进修情况表中存放。如果按照标准化的要求，TeacherInfo查询时就必须要与这么多个表进行连接或者嵌套查询。如果TeacherInfo表中的数据量是在百万级的，那么在一般配置的效劳器上一次复杂查询所需要的时间可能会到达好几分钟。事实上，只要在设计时保证数据的逻辑有效性，很多信息都可以直接冗余在TeacherInfo表中，这些冗余的数据能够极大的提高查询的效率，从而减少CPU和I/O操作。2.3 选择数据类型对每一属性选择什么样的数据类型很大程度上依据表的要求，但是在不违背表要求的前提下，选择适当的数据类型可以提高系统性能。比方有text列存放一本书的信息，用BLOB而不是characte

20、r(1024)，BLOB存放的是指针或者文件参照变量，真正的文本信息可以放在数据库之外，从而减少数据库缓存空间，使得程序运行的速度提高。用户可以根据自己的需要定义自己的数据类型。2.4 选择索引在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广阔数据库用户所 接受的优化方法。在良好的数据库设计根底上，能有效地使用索引是SQL Server取得高性能的根底，SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大局部开销是磁盘I/O，使用索引提高性能的一个

21、主要目标是防止全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，那么查询只需读几次磁盘就可以了。所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQL Server应用。实践说明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最正确的优化方案。本文就SQL Server索引的性能问题进行了一些分析和实践。一、 聚簇索引(clustered

22、indexes)的使用聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是：1、 大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个事务的环境中，对最后一页的封锁严重影响系统的吞吐量。2、 在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、=)或使用group by或order by的查询时，一旦

23、找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，防止了大范围扫描，可以大大提高查询速度。3、 在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否那么会经常引起封锁冲突。4、 在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。5、 选择聚簇索引应基于where子句和连接操作的类型。聚簇索引的侯选列是： 主键列,该列在where子句中使用并且插入是随机的。 按范围存取的列，如age 31 and pri_order = 35 。 在group by或order by中使用的列。 不经常修

24、改的列。 在连接操作中使用的列。二、 非聚簇索引(nonclustered indexes)的使用SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题： 索引需要使用多少空间。 适宜的列是否稳定。 索引键是如何选择的，扫描效果是否更佳。 是否有许多重复值。对更

25、新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况: 某列常用于集合函数(如Sum,.)。 某列常用于join,order by,group by。 查寻出的数据不超过表中数据量的20%。三、覆盖索引(covering indexes)的使用覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这 种索引之

26、所以比拟快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。 如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。但是由于覆盖索引的索引项比拟多,要占用比拟大的空间。而且update 操 作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，那么覆盖索引的增加反而会降低性能。索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件表达于where从句和join表达式中。一般来说建立索引的思路是：(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。(2)、有大量重复值且经常有

27、范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。(3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，那么可以把fillfactor置为100。(6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过

28、这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。四、 引的维护上面讲到,某些不适宜的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化到达某一个程度时将 会影响到索引的使用。这时 需要用户自己来维护索引。索引的维护包括：a) 重建索引随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：(1)、数据和使用模式大幅度变化。(2)

29、、排序的顺序发生改变。(3)、要进行大量插入操作或已经完成。(4)、使用大块I/O的查询的磁盘读次数比预料的要多。(5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。(6)、dbcc检查出索引有问题。当重建聚簇索引时,这张表的所有非聚簇索引将被重建.b) 索引统计信息的更新当在一个包含数据的表上创立索引的时候，SQL Server会创立分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因

30、此，在下面情况下应该运行update statistics命令：(1)、数据行的插入和删除修改了数据的分布。(2)、对用truncate table删除数据的表上增加数据行。(3)、修改索引列的值。第三章 查询优化3.1 主键主键用整型会极大的提高查询效率，而字符型的比拟开销要比整型的比拟开销大很多，用字符型数据作主键会使数据插入、更新与查询的效率降低。数据量小的时候这点降低可能不会被注意，可是当数据量大的时候，小的改良也能够提高系统的响应速度。虽然整型能提高查询效率，但在具体的方案中考虑到教师工号的唯一性和我们所做软件的数据量，我们还是牺牲了这一效率，采用了字符型数据作主键。3.2 通配符在

31、数据库管理系统中，查询是一个很重要的内容。然而，在多数情况下人们不能准确知道作为查询条件的字段内容，如：在“师资管理系统中要查询教师内容，通过教师工号，教师类别号，教师姓名，教师学科号之中的一个或多个查询，而查询者可能只知道其中的姓或名，这时，为保证能查到满足条件的数据记录，只能进行模糊查询。以下是我在“师资管理系统中为查询教师的存储过程的代码：CREATE procedure seachTeacherteacherID varchar(10),kindID char(2),name varchar(12),subjectID varchar(8)as if (teacherID is not

32、 null and kindID is not null and name is not null and subjectID is not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectIDwhere a.teacherID=teacherID and b.kindID=kindID and a.name like (%+ na

33、me + %) and c.subjectID=subjectID order by a.teacherIDendif (teacherID is null and kindID is not null and name is not null and subjectID is not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjec

34、tIDwhere b.kindID=kindID and a.name like (%+ name + %) and c.subjectID=subjectID order by a.teacherIDendif (teacherID is not null and kindID is null and name is not null and subjectID is not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.k

35、indID join Subject as c on a.subjectID=c.subjectIDwhere a.teacherID=teacherID and a.name like (%+ name + %) and c.subjectID=subjectID order by a.teacherIDendif (teacherID is null and kindID is null and name is not null and subjectID is not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName

36、 from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectIDwhere a.name like (%+ name + %) and c.subjectID=subjectID order by a.teacherIDendif (teacherID is not null and kindID is not null and name is null and subjectID is not null)beginselect a.teacherID,b.ki

37、ndName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectIDwhere a.teacherID=teacherID and b.kindID=kindID and c.subjectID=subjectID order by a.teacherIDendif (teacherID is null and kindID is not null and name is null and subjectID i

38、s not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectIDwhere b.kindID=kindID and c.subjectID=subjectID order by a.teacherIDendif (teacherID is not null and kindID is null and name is null an

39、d subjectID is not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectIDwhere a.teacherID=teacherID and c.subjectID=subjectID order by a.teacherIDendif (teacherID is null and kindID is null and

40、name is null and subjectID is not null)beginselect a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectIDwhere c.subjectID=subjectID order by a.teacherIDendif (teacherID is not null and kindID is not null and name is not null and subjectID is null)beginselect a.teacherID,b.kindName,a.name,c.subject