毕业设计-sql server 数据库管理系统上的sql查询优化—论文

SQLSERVER数据库管理系统上的SQL查询优化摘要人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的WHERE子句。在对它们进行适当的优化后，其运行速度将有比较明显的提高。为了最有效地优化MICROSOFTSQLSERVER的性能，您必须明确在哪些方面性能可以得到最大程度的改进，并集中分析这些方面。否则，您在这些问题上可能花费大量的时间和精力，而并不能得到明显的性能上的提高。设计一个应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择，这取决于特定的应用需求以及开发队伍的技能。关键词SQLSERVER数据库性能优化查询数据查询查询效率安全性ABSTRACTPEOPLEWILLOFTENFALLINTOAILLIDEAWHILEUSINGSQL,NAMELYITISCORRECTTOPAYCLOSEATTENTIONTOTHERESULTTOTHEINCOMEVERYMUCH,ANDHASNEGLECTEDTHEPERFORMANCEDIFFERENCETHATMAYEXISTBETWEENDIFFERENTIMPLEMENTATIONMETHODS,THISKINDOFPERFORMANCEDIFFERENCEBEHAVESPARTICULARLYOBVIOUSLYINTHELARGESCALEORCOMPLICATEDDATABASEENVIRONMENTSUCHASONLINETRANSACTIONPROCESSINGOLTPORDSSIFINDBADSQLCOMEFROMAPPROPRIATEINDEXDESIGN,FILLCONNECTIONTERMSANDWHERECLAUSETHATCANTBEOPTIMIZEDOFCOPYOFTENAMONGWORKINGPRACTICEAFTERCARRYINGONPROPEROPTIMIZATIONTOTHEM,ITSSPEEDOFOPERATIONIMPROVESOBVIOUSLYINORDERTOOPTIMIZETHEPERFORMANCEOFMICROSOFTSQLSERVERMOSTEFFECTIVELY,YOUMUSTDEFINEINWHICHRESPECTSPERFORMANCECANGETMAXIMUMIMPROVEMENT,CONCENTRATEONANALYSINGTHESERESPECTSOTHERWISE,YOUAREONTHESEQUESTIONSMAYSPENDALARGEAMOUNTOFTIMEANDENERGY,BUTCANNOTRECEIVETHEIMPROVEMENTONOBVIOUSPERFORMANCEITSEEMSEASYTODESIGNAAPPLICATIONSYSTEM,BUTITISNOTANEASYTHINGTOWANTTOMAKETHESYSTEMREACHTHEPERFORMANCEOPTIMIZEDMOSTRESPECTOFCHOOSINGONDEVELOPINGINSTRUMENT,DATABASEDESIGN,STRUCTURE,INQUIRYDESIGN,INTERFACEOFTHEAPPLICATIONPROGRAMETCHASEXCELLENTCHOICES,THISDEPENDSONSPECIFICAPPLICATIONDEMANDANDDEVELOPSTHESKILLOFTHETEAMKEYWORDSQLSERVERDATABASEPERFORMANCEOPTIMIZINGINQUIRYDATAINQUIRYINQUIREABOUTEFFICIENCYSECURITY目录摘要1ABSTRACT2第一章综述绪论411引言412数据库优化概述8第二章设计数据库821熟悉业务系统922规范化与逆规范化1023选择数据类型1024选择索引14第三章查询优化1531主键1532通配符1933视图1934存储过程20第四章总结21致谢21第一章综述SQLSERVER是一个后台数据库管理系统，他功能强大、操作简便，日益为广大数据库用户所喜爱，越来越多的开发工具提供了与SQLSERVER的接口。了解和掌握SQLSERVER的功能，对于一个数据库开发管理人员来说非常必要。SQLSERVER是一种高性能的大型关系型数据库管理系统，广泛的应用在C/S和B/S体系结构的数据库系统中。评价系统性能优化的标准有吞吐量、响应时间、并行能力等。本文主要探讨如何优化SQL，取得最快的系统响应速度。11引言在一个大型的数据库中，性能成为人们关注的焦点之一，如何让数据库高效有效的运行成为广大数据库管理人员和开发人员必须要考虑的问题。何谓“好性能”性能是一个应用或多个应用在相同的环境下运行时对效率的衡量。性能常用响应时间和工作效率来表示。响应时间是指提交任务到得到返回结果之间的时间开销，含完成一个任务花费的时间。可以从以下三方面来减少响应时间减少竞争和等待的次数，尤其是磁盘读写等待次数用更快的部件减少利用资源所需的时间绝大多数性能的获得来自于优秀的数据库设计、精确的查询分析和适当的索引。最好性能的获得能够通过确立优秀的数据库设计，在开发时使用SQLSERVER查询优化器来实现。为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据CACHE,过程CACHE，系统资源和CPU的竞争。12数据库优化概述在SQLSERVER中，有如下优化层次应用层大部分性能的获得来自于对SQL应用中查询的优化，这必须是以好的数据库设计为基础的。数据库层应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据CACHE。服务器层在服务器层有许多共享的资源，包括数据高速缓存，存储过程高速缓存，锁，CPU等。设备层指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的IO。网络层指连接用户和SQLSERVER的网络。硬件层指可利用的CPU。操作系统层理想地，SQLSERVER是一台机器的唯一主要应用，它必须和操作系统以及其他SYBASE软件，如BACKUPSERVER或SQLSERVERMONITOR共享处理器、内存以及其他资源。在大多数情况下面，我们是对应用层进行优化，因为对应用性能的优化是设计和编程人员乐于接受的功能，其结果能被观测及检验。查询的性能是SQL应用的整个性能的一个关键。应用层上的问题包括以下内容决策支持DSS和在线事务处理OLTP需要不同的性能策略事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取关联一致性保证的策略。对数据查询修改时需要考虑JOIN操作对性能的影响索引可以改善查询性能，但也会增加修改数据的时间为了安全而设立的审计限制了性能在应用层优化的选项包括远程处理或复制处理能够把决策支持从OLTP机器中分离出来利用存储过程来减少编译时间和网络的利用利用最少量的锁去满足你的应用需要数据库层的问题包括建立备份和恢复方案在设备上分布存储数据审计操作影响性能；仅审计你所需的日常的维护活动将导致性能的降低和导致用户不能操作数据库表在数据库层上优化选择包括利用事务日志的阀值来自动转储事务日志防止其超出使用空间在数据段中用阀值来监视空间的使用利用分区来加速数据的装入对象的定位以避免硬盘的竞争把重要表和索引放入CACHE中，保证随时取得服务器层的问题有应用的类型服务器是支持OLTP还是DSS，或者两者都支持所支持的用户数影响优化决策随着用户数的增加，对资源的竞争会发生改变网络负荷当用户数和事务数达到一定的数量时复制服务器或其他分布式处理是一个解决的方法服务器层的优化的选项包括优化内存一个关键的配置参数和其他方面的参数决策是客户端处理还是服务器端处理有些处理能在客户端进行吗配置CACHE的大小和IO的大小增加多个CPU为空闲时间排定批处理任务和生成报表工作负荷发生改变，重新配置特定参数决定是否可能把DSS移到另一个SQL服务器中设备层设备层的问题包括主设备、包含用户数据库的设备，用户数据设备，或数据库日志是否要镜像怎样在设备之间分布系统数据库、用户数据库和数据库日志为获得对堆表插入操作的高性能，是否有必要进行分区设备层上优化的选项包括用多个中等大小的设备及多个控制器可能比用少量的大设备有更好的IO性能分布数据库，表和索引以在不同的设备上进行IO装载网络层实际上，SQLSERVER的所有用户都是通过网络存取他们的数据。网络层上的主要问题有网络的流量网络的瓶颈网络的速度网络层上优化的选项包括配置包的大小，以使其与应用的需要相匹配配置子网分隔出繁忙的网络运用创建一个高容量的网络配置多个网络引擎更好地设计应用，限制所需的网络传输硬件层在硬件层上的问题包括CPU的效率磁盘的存取控制器和磁盘磁盘备份内存的使用在硬件层上优化的选项包括增加CPU以适应工作负荷配置调度程序以提高CPU利用率遵循多处理器应用设计指导以减少竞争配置多个数据CACHE操作系统层操作系统层的主要问题有文件系统是否被SQLSERVER独占使用内存管理精确估算操作系统和其他程序的内存占用CPU的利用整个系统共有多少处理器可用有多少分配给SQLSERVER在操作系统层优化的选项包括网络接口在文件和原始分区之间选择增加内存把客户操作和批处理移到其他机器上SQLSERVER利用多个CPU第二章设计数据库要在良好的SQLSERVER方案中实现最优的性能，最关键的是要有一个很好的数据库设计方案。在实际工作中，许多SQLSERVER方案往往是由于数据库设计得不好导致性能很差。所以要设计好数据库就要从以下几点来讨论。21熟悉业务系统对业务系统的熟悉程度对整个数据库系统的性能有很大影响，一个对业务不熟悉的设计人员，尽管有丰富的数据库知识，也很难设计出性能最佳的数据库应用系统。所以我们在做“三江学院师资管理系统”这个项目前跟指导老师和用户收集了充足的资料，通过这些资料和经过我们的分析，我在底层数据库建立了13张数据库表，它包括教师表（TEACHER）、党派表（CLAN）、用户表（DBA）、入党表（JOINCLAN）、类别表（KIND）、学位学历表（KONWLEDGEDEGREE）、进修情况表（OPERATION）、联系电话表（PHONE）、兼职情况表（PLURALITY）、工作简历表（RESUME）、任职情况表（SCHOOLDUTY）、任课情况表（SCHOOLTEACH）、学科表（SUBJECT）。通过这些表我建立了ER图MM11MM1N1MM1M1MM11M1学历学位工作简历党派教师拥有入党时间入党地点任课任职三江学院任课情况类别三江学院任职情况拥有兼职兼职情况分类入党学科属于进修情况进修联系电话拥有22规范化与逆规范化数据库被规范化后，减少了数据冗余，数据量变小，数据行变窄。这样SQLSERVER2000的每一页可以包括更多行，那么每一区里的数据量更多，从而加速表的扫描，改进了单个表的查询性能。但是，当查询涉及多个表的时候，需要用很多连接操作把信息从各个表中组合在一起，导致更高的CPU和I/O花销。那么，有很多时候需要在规范化和逆规范化之间保持平衡，用适当的冗余信息来减少系统开销，用空间代价来换取时间代价。有查询教师全部信息表TEACHERINFO，它里面记录了教师基础信息，教师类别信息，教师兼职信息，教师任课信息，教师入党信息，教师任职信息，教师学科信息，教师进修情况信息这些信息分别在教师表、类别表、兼职情况表、任课情况表、入党表、任职情况表、学科表、进修情况表中存放。如果按照规范化的要求，TEACHERINFO查询时就必须要与这么多个表进行连接或者嵌套查询。如果TEACHERINFO表中的数据量是在百万级的，那么在一般配置的服务器上一次复杂查询所需要的时间可能会达到好几分钟。事实上，只要在设计时保证数据的逻辑有效性，很多信息都可以直接冗余在TEACHERINFO表中，这些冗余的数据能够极大的提高查询的效率，从而减少CPU和I/O操作。23选择数据类型对每一属性选择什么样的数据类型很大程度上依据表的要求，但是在不违背表要求的前提下，选择适当的数据类型可以提高系统性能。比如有TEXT列存放一本书的信息，用BLOB而不是CHARACTER1024，BLOB存放的是指针或者文件参照变量，真正的文本信息可以放在数据库之外，从而减少数据库缓存空间，使得程序运行的速度提高。用户可以根据自己的需要定义自己的数据类型。24选择索引在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。在良好的数据库设计基础上，能有效地使用索引是SQLSERVER取得高性能的基础，SQLSERVER采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQLSERVER应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。本文就SQLSERVER索引的性能问题进行了一些分析和实践。一、聚簇索引CLUSTEREDINDEXES的使用聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个事务的环境中，对最后一页的封锁严重影响系统的吞吐量。2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查BETWEEN、或使用GROUPBY或ORDERBY的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列如IDENTITY上，否则会经常引起封锁冲突。4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。5、选择聚簇索引应基于WHERE子句和连接操作的类型。聚簇索引的侯选列是主键列,该列在WHERE子句中使用并且插入是随机的。按范围存取的列，如AGE31ANDPRI_ORDER、或使用GROUPBY或ORDERBY的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列如IDENTITY上，否则会经常引起封锁冲突。4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。5、选择聚簇索引应基于WHERE子句和连接操作的类型。聚簇索引的侯选列是主键列,该列在WHERE子句中使用并且插入是随机的。按范围存取的列，如AGE31ANDPRI_ORDER35。在GROUPBY或ORDERBY中使用的列。不经常修改的列。在连接操作中使用的列。三、非聚簇索引NONCLUSTEREDINDEXES的使用SQLSERVER缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题索引需要使用多少空间。合适的列是否稳定。索引键是如何选择的，扫描效果是否更佳。是否有许多重复值。对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况某列常用于集合函数如SUM,。某列常用于JOIN,ORDERBY,GROUPBY。查寻出的数据不超过表中数据量的20。三、覆盖索引COVERINGINDEXES的使用覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且UPDATE操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于WHERE从句和JOIN表达式中。一般来说建立索引的思路是1、主键时常作为WHERE子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。2、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。3、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。4、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。5、在一个经常做插入操作的表上建索引时，使用FILLFACTOR填充因子来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把FILLFACTOR置为100。6、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。五、引的维护上面讲到,某些不合适的索引影响到SQLSERVER的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括A重建索引随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B树空间。在下面情况下需要重建索引1、数据和使用模式大幅度变化。2、排序的顺序发生改变。3、要进行大量插入操作或已经完成。4、使用大块I/O的查询的磁盘读次数比预料的要多。5、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。6、DBCC检查出索引有问题。当重建聚簇索引时,这张表的所有非聚簇索引将被重建B索引统计信息的更新当在一个包含数据的表上创建索引的时候，SQLSERVER会创建分布数据页来存放有关索引的两种统计信息分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行UPDATESTATISTICS命令1、数据行的插入和删除修改了数据的分布。2、对用TRUNCATETABLE删除数据的表上增加数据行。3、修改索引列的值。第三章查询优化31主键主键用整型会极大的提高查询效率，而字符型的比较开销要比整型的比较开销大很多，用字符型数据作主键会使数据插入、更新与查询的效率降低。数据量小的时候这点降低可能不会被注意，可是当数据量大的时候，小的改进也能够提高系统的响应速度。虽然整型能提高查询效率，但在具体的方案中考虑到教师工号的唯一性和我们所做软件的数据量，我们还是牺牲了这一效率，采用了字符型数据作主键。32通配符在数据库管理系统中，查询是一个很重要的内容。然而，在多数情况下人们不能准确知道作为查询条件的字段内容，如在“师资管理系统”中要查询教师内容，通过教师工号，教师类别号，教师姓名，教师学科号之中的一个或多个查询，而查询者可能只知道其中的姓或名，这时，为保证能查到满足条件的数据记录，只能进行模糊查询。以下是我在“师资管理系统”中为查询教师的存储过程的代码CREATEPROCEDURESEACHTEACHERTEACHERIDVARCHAR10,KINDIDCHAR2,NAMEVARCHAR12,SUBJECTIDVARCHAR8ASIFTEACHERIDISNOTNULLANDKINDIDISNOTNULLANDNAMEISNOTNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDBKINDIDKINDIDANDANAMELIKENAMEANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNOTNULLANDNAMEISNOTNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREBKINDIDKINDIDANDANAMELIKENAMEANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNULLANDNAMEISNOTNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDANAMELIKENAMEANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNULLANDNAMEISNOTNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREANAMELIKENAMEANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNOTNULLANDNAMEISNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDBKINDIDKINDIDANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNOTNULLANDNAMEISNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREBKINDIDKINDIDANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNULLANDNAMEISNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDCSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNULLANDNAMEISNULLANDSUBJECTIDISNOTNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHERECSUBJECTIDSUBJECTIDORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNOTNULLANDNAMEISNOTNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDBKINDIDKINDIDANDANAMELIKENAMEORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNOTNULLANDNAMEISNOTNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREBKINDIDKINDIDANDANAMELIKENAMEORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNULLANDNAMEISNOTNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDANAMELIKENAMEORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNULLANDNAMEISNOTNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREANAMELIKENAMEORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNOTNULLANDNAMEISNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDANDBKINDIDKINDIDORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNOTNULLANDNAMEISNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREBKINDIDKINDIDORDERBYATEACHERIDENDIFTEACHERIDISNOTNULLANDKINDIDISNULLANDNAMEISNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDWHEREATEACHERIDTEACHERIDORDERBYATEACHERIDENDIFTEACHERIDISNULLANDKINDIDISNULLANDNAMEISNULLANDSUBJECTIDISNULLBEGINSELECTATEACHERID,BKINDNAME,ANAME,CSUBJECTNAMEFROMTEACHERASAJOINKINDASBONAKINDIDBKINDIDJOINSUBJECTASCONASUBJECTIDCSUBJECTIDORDERBYATEACHERIDENDGO大家可以从以上代码中看到有4个参数就有16中情况的可能性，这大大的影响了查询的效率，在性能上是不可取的，怎么可以提高效率又可以满足上面的查询要求呢，我们这里可以用通配符来达到这种效果。在“师资管理系统”中要查询教师学科信息，有6个参数，那么就要有64个可能性，如果象上面那样做的话，那么代码太复杂了。而我用了通配符就比较简单，以下是我写的代码CREATEPROCEDURESUBJECTPROSUBJECTIDVARCHAR8,SUBJECTNAMEVARCHAR20,SUBJECTKINDVARCHAR10,ISBASECHAR2,DEPARTMENTVARCHAR20ASDECLARESUBJECTID1VARCHAR10,SUBJECTNAME1VARCHAR22,SUBJECTKIND1VARCHAR12,ISBASE1VARCHAR4,DEPARTMENT1VARCHAR22SETSUBJECTID1CASEWHENSUBJECTIDISNULLTHENELSESUBJECTIDENDSETSUBJECTNAME1CASEWHENSUBJECTNAMEISNULLTHENELSESUBJECTNAMEENDSETSUBJECTKIND1CASEWHENSUBJECTKINDISNULLTHENELSESUBJECTKINDENDSETISBASE1CASEWHENISBASEISNULLTHENELSEISBASEENDSETDEPARTMENT1CASEWHENDEPARTMENTISNULLTHENELSEDEPARTMENTENDSELECTFROMSUBJECTWHERESUBJECTIDLIKESUBJECTID1ANDSUBJECTNAMELIKESUBJECTNAME1ANDSUBJECTKINDLIKESUBJECTKIND1ANDISBASELIKEISBASE1ANDDEPARTMENTLIKEDEPARTMENT1GO对于海量数据，劣质SQL语句和优质SQL语句之间的速度差別可以达到上百倍，可見对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。用好通配符就能提高SQL语句的性能。33视图视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也包括几个被定义的数据列和多个数据行，但就本质而言，这些数据列和数据行来源与其所引用的表。所以视图不是真实存在的基础表而是一张虚表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。使用视图有以下几个优点为用户集中数据视图创建了一个受控制的环境，它允许访问某些数据，并隐藏其他的数据。不必要的、敏感的或不合适的数据都可以不在视图中显示。用户可以操作视图中数据的显示，与表类似。另外使用合适的权限和一些限制，用户可以修改视图产生的数据。掩盖数据库的复杂性视图对用户掩盖数据库设计的复杂性，使开发者具有更改设计而不影响用户与数据库打交道的能力。视图还可以隐藏比较复杂的查询，包括对异构数据的分布查询。用户可以简单地查询视图，而不用编写复杂的查询或执行脚本。简化用户权限的管理数据库拥有者可以给其他的用户授权，使他们可以通过试图来查询数据，而不是限制用户查询基表中特定列的权限。这样，还可以使得基表设计中的更改不至于影响用户的查询。组织数据导出到其他应用程序可以创建基于连接两个或多个表的复杂查询的视图，然后把数据导出到另一个应用程序作进一步的分析。视图的基础是建立在基础表上的查询，视图的优化取决于其所基于的SQL语句的优化。通常来讲，单表视图和只读视图效率最佳。34存储过程在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程和触发器，都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。存储过程在运算时生成执行方式，所以，以后对其再运算时其执行速度很快。SQLSERVER2000不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。存储过程就是将所需要的工作，预先以SQL程序写好，命名后进行保存，以后需要作这些工作时可以使用EXECUTE指令来调用，即可自动完成相应任务。这里的存储过程也是自动化的一个方面，总之就是为了方便管理的一种措施。并且存储过程有许多的优点1执行效率高（这点不容置疑）2统一的操作流程也就是通过存储过程的操作避免了一些操作过程中可能无意中认为的错误，只要确定了制作存储过程时是正确地，以后在调用过程中就不用担心了。大家使用时流程是一样的。3重复使用4安全性这一点我们在数据库的安全策略里讨论过，可以参考前边的文章。也就是说我们可以利用存储过程作为数据存储的管道。可以让客户在一定的范围内对数据进行操作。另外，存储过程是可以加密的，这样别人就看不到他的内容了。在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用存储过程来封装数据库操作。如果项目的存储过程较多，书写又没有一定的规范，将会影响以后的系统维护困难和大存储过程逻辑的难以理解，另外如果数据库的数据量大或者项目对存储过程的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍。对于存储过程的优化，一是要精心设计、调整其主干SQL语句，技巧如前文所述。此外，还要考虑所有存储过程之间合理的模块分割，保证合适的代码粒度。在存储过程中，还要尽量使用预编译SQL而少用动态SQL，以避免存储过程整体的编译开销。第四章总结经过三个多月的设计和开发，师资管理系统基本开发完毕。其功能基本符合用户需求，能够完成用户管理、教师管理、学科管理的存储和基本信息的查询以及教师年龄段内的职称分布，专任教师中的不任课人数，学历下专任教师的职称分布，学科下职称分布等的统计。并提供部分系统维护功能,使用户方便进行数据备份和恢复、数据删除。对于数据的一致性的问题也通过程序进行了有效的解决。但是由于毕业设计时间较短，所以该系统还有许多不尽如人意的地方，比如联机文档比较少，用户界面不够美观，出错处理不够等多方面问题。这些都有待进一步改善。致谢在本次毕业设计中，我从指导老师杨少雄老师身上学到了很多东西。杨老师认真负责的工作态度，严谨的治学精神和深厚的理论水平都使我收益匪浅。他无论在理论上还是在实践中，都给与我很大的帮助，使我得到不少的提高这对于我以后的工作和学习都有一种巨大的帮助，感谢他耐心的辅导。另外，在系统开发过程中杨芃，王沣，钱立娟三位同学也给于我很大的帮助，帮助解决了不少的难点，使得系统能及时开发完成。ANYWAY,IWASHEREINAMBERSOMEYEARSAGONOTDOINGMUCHOFANYTHINGJUSTVISITINGANDBEINGANUISANCEDADWASSTILLAROUND,ANDWHENINOTICEDTHATHEWASGETTINGINTOONEOFHISGRUMPYMOODS,IDECIDEDITWASTIMETOTAKEAWALKALONGONEIHADOFTENNOTICEDTHATHISFONDNESSFORMETENDEDTOINCREASEASANINVERSEFUNCTIONOFMYPROXIMITYHEGAVEMEAFANCYRIDINGCROPFORAGOINGAWAYPRESENTTOHASTENTHEPROCESSOFAFFECTION,ISUPPOSESTILL,ITWASAVERYNICECROPSILVERCHASED,BEAUTIFULLYTOOLEDANDIMADEGOODUSEOFITIHADDECIDEDTOGOLOOKINGFORANASSEMBLAGEOFALLMYSIMPLEPLEASURESINONESMALLNOOKOFSHADOWITWASALONGRIDEIWILLNOTBOREYOUWITHTHEDETAILSANDITWASPRETTYFARFROMAMBER,ASSUCHTHINGSGOTHISTIME,IWASNOTLOOKINGFORAPLACEWHEREIWOULDBEESPECIALLYIMPORTANTTHATCANGETEITHERBORINGORDIFFICULTFAIRLYQUICKLY,DEPENDINGONHOWRESPONSIBLEYOUWANTTOBEIWANTEDTOBEANIRRESPONSIBLENONENTITYANDJUSTENJOYMYSELFTEXORAMIWASAWIDEOPENPORTCITY,WITHSULTRYDAYSANDLONGNIGHTS,LOTSOFGOODMUSIC,GAMBLINGAROUNDTHECLOCK,DUELSEVERYMORNINGANDINBETWEENMAYHEMFORTHOSEWHOCOULDNTWAITANDTHEAIRCURRENTSWEREFABULOUSIHADALITTLEREDSAILPLANEIUSEDTOGOSKYSURFINGIN,EVERYCOUPLEOFDAYSITWASTHEGOODLIFEIPLAYEDDRUMSTILLALLHOURSINABASEMENTSPOTUPTHERIVERWHERETHEWALLSSWEATEDALMOSTASMUCHASTHECUSTOMERSANDTHESMOKEUSEDTOWASHAROUNDTHELIGHTSLIKESTREAMSOFMILKWHENIWASDONEPLAYINGIDGOFINDSOMEACTION,WOMEN,ORCARDS,USUALLYANDTHATWASITFORTHERESTOFTHENIGHTDAMNERIC,ANYWAYLTHATREMINDSMEAGAINHEONCEACCUSEDMEOFCHEATINGATCARDS,DIDYOUKNOWTHATANDTHATSABOUTTHEONLYTHINGIWOULDNTCHEATATITAKEMYCARDPLAYINGSERIOUSLYIMGOODANDIMALSOLUCKYERICWASNEITHERTHETROUBLEWITHHIMWASTHATHEWASGOODATSOMANYTHINGSHEWOULDNTADMITEVENTOHIMSELFTHATTHEREWERESOMETHINGSOTHERPEOPLECOULDDOBETTERIFYOUKEPTBEATINGHIMATANYTHINGYOUHADTOBECHEATINGHESTARTEDANASTYARGUMENTOVERITONENIGHTCOULDHAVEGOTTENSERIOUSBUTGERARDANDCAINEBROKEITUPGIVECAINETHATHETOOKMYPARTTHATTIMEPOORGUYHELLOFAWAYTOGO,YOUKNOWHISTHROATWELL,ANYHOW,THEREIWASINTEXORAMI,MAKINGMUSICANDWOMEN,WINNINGATCARDSANDJOCKEYINGAROUNDTHESKYPALMTREESANDNIGHTBLOOMINGWALLFLOWERSLOTSOFGOODPORTSMELLSSPICES,COFFEE,TAR,SALTYOUKNOWGENTLEFOLK,MERCHANTS,ANDPEONSTHESAMESTRAIGHTSASINMOSTOTHERPLACESSAILORSA