基于SQLserver数据库优化查询的分析

     基于SQLserver数据库优化查询的分析                  【摘要】为了缩短大数据量的查询处理时间，提高数据库应用系统的效率，提出了针对SQL语句进行优化的策略。首先分析了数据库应用系统的时间开销；然后介绍了SQL语句执行路径和语句优化的原理；其次分析SQL语句优化的通用策略和原则，通过相应的解释加深读者对这些策略和方法的理解；最后 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 了SQL的优化意义以及技术发展的趋势。【关键词】SQLserver；数据库；优化查询随着信息化时代的到来，各类信息管理系统和数据库结合越来越紧密，其应用数据也随着时间推移而高速地增长，数据库的重要性也日渐显著。当数据量较小时，应用程序操作数据库的速度和可靠性往往被忽视，部分小型数据库系统（如FoxPro）就可以满足用户的 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 。当数据量达到几十万甚至更多时，数据库的瓶颈就会出现。因此，对数据库系统查询性能进行优化是很有必要的。　　一、C/S模式数据库系统时间开销　　基于C/S模式数据库应用系统时间开销在对数据库应用系统进行操作时，剔除硬件配置对系统性能影响时，时间开销主要由以下三部分组成：　　网络延迟的时间开销：系统工作于C／S模式，客户端向服务器端提交SQL请求，服务器端将处理结果返回客户端。数据需要在网络上传输，必然有一定的网络延迟。服务器端数据处理的时间开销：服务器端接收到客户端提交的SQL请求后，会执行数据维护、查询等操作。客户端数据处理的时间开销：客户端接收到服务器返回的数据后，需要进一步处理，如统计、排序等，最后将结果通过用户接口显示给用户。当数据库系统数据量到一定程度时，如上千万条信息，若查询时采用单条顺序扫描，则把所有的记录扫描一遍可能花上几十分钟，这样的情况在网络上 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 现为超时，这样的系统就失去了使用的意义。在处理大容量数据库时，处理时间主要耗费在服务器端。为了提高数据库应用系统的可靠性和有效性，需要采取合理的查询优化策略。二、SQL语句执行路径与语句优化原理（一）SQL语句执行路径　　当用户提交一条SQL语句时，系统会做如下操作：　　第一步：客户端把语句发送给服务器端执行。　　第二步：解析语句。客户端把SQL语句传送到服务器后，服务器进程会对该语句进行解析。服务器端具体工作步骤如下：（1）查询高速缓存。如果在高速缓存中有其他用户使用过这个查询语句，服务器进程就会直接执行这个SQL语句，省去后续的工作。（2）检查语句合法性。（3）检查语言含义。（4）获得对象解析锁。语法、语义都正确后，系统就会对需要查询的对象进行加锁。（5）核对数据访问权限。（6）确定最佳的执行计划。服务器进程会依据一定的 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf ，对语句进行优化。当服务器进程的优化器确定最佳的执行计划后，就会将SQL语句与执行计划保存到数据高速缓存中。等以后还有这个查询时，就会省略以上的语义、语法和权限检查的步骤，而直接执行SQL语句，从而提高SQL语句的处理效率。　　第三步：语句执行。分两种情况：（1）如果数据不在缓冲区中，服务器进程将从数据库文件中查询相关数据，并把这些数据放入到数据缓冲区中。（2）如果被选择的数据块已经被读取到数据缓冲区中，服务器进程会直接把这个数据传递给客户端。　　第四步：提取数据。返回语句执行结果。从以上SQL语句执行过程来看，要想提高SQL语句执行效率，必须提高SQL语句书写规范和技巧。（二）SQL语句的优化原理SQL是集数据操纵和数据定义于一体的数据库语言，可以作为独立的语言在终端上以交互方式使用，也可以作为程序设计中的子语言使用，即嵌入到高级语言中使用，用户可以通过SQL语言方便地从数据库中获得自己需要的信息。SQL语句的优化是把原有的SQL语句转换成为语义相同而处理效率更高的SQL语句。在SQL系统中，用户提交的SQL语句是系统优化的基础，系统采用基于规则的和基于成本的方法优化用户所提交的SQL语句。SQL语句的优劣至关重要，一个好的查询计划往往可以减少系统试图优化的时间从而使程序性能提高几十倍，而不良的SQL语句则以低效方式访问数据库，从而导致不必要的数据扫描和传输不必要的数据，耗费大量数据库资源。SQL语句的优化原理是尽可能减少查询中参与加工的数据量，以此达到优化空间和时间的目的。查询优化力图找出一个与给定表达式等价，但执行效率更高的表达式。一个查询往往会有许多实现方法，关键是如何找出一个与之等价的且占用资源更少的表达式。数据查询处理的代价一般取决于对磁盘的访问，因为磁盘访问比内存的访问速度要慢得多。查询优化关注的问题是怎样省空间、省时、高效率。三、SQL优化查询的原则　　（一）优化排序操作　　1.对经常进行连接和排序操作的字段建立索引。建立索引后，当服务器向这些字段发出排序请求时，将直接引用索引而不进行排序操作；为了使用基于成本的优化器，必须经常运行analyze命令，以增加数据库中的对象统计信息的准确性。随着基表数据的不断增加，索引块会不断分裂以保持二叉树的平衡，树的层次也不断增加，层次大于4的索引宜重建;基表数据删除时，索引项也随之删除，但叶块上的空间并不能被重用，除非该叶块上所有的索引项都被删除，当删除项占所有项超过20%时，这个索引也需要重建。有三种重建索引的方法：（1）先删除再重建。这种查询方法是早期版本的唯一方法，缺点是耗费最多的资源；（2）ALTERINDEX…REBUILD。这种查询方法高效快速，但需要额外的磁盘空间。用这种方法可以指定许多选项如ONLINE、TABLESPACE、COMPUTESTATISTICS、PARALLEL、NOLOG-GING等；（3）ALTERINDEX…COALESCE。这种方查询法快速，无需额外空间，锁争用也少，但缺点是选项少。2.SQL语句尽可能使用索引。创建索引后，如果SQL语句书写不当，优化器生成的执行计划也不会包含索引，应该避免下列情况：（1）避免使用‘!=’和‘<>’操作符SELECtITEM_DESCFROMCHECK_ITEMWHEReITEM_ID<>‘ABC’可以改写为：SELECtITEM_DESCFROMCHECK_ITEMWHEReITEM_ID>‘ABC’ORITEM_ID<‘ABC’（2）避免对WHERe条件后的列使用函数。　　SELECtITEM_ID，ITEM_DESCFROMCHECK_ITEMWHEReUPPER（ITEM_ID）=‘ABC’尽管对该表的ITEM_ID字段创建了索引，但该执行计划也不会使用到索引，建议使用下面语句重新建立函数索引。　　CREATEINDEXUPPER_ITEM_IDONCHECK_ITEM（UPER（ITEM_ID））；可以将执行语句改为下面书写方式，就可以正常使用索引。SELECtITEM_ID，ITEM_DESCFROMCHECK_ITEMWHEReITEM_ID=’ABC’（3）比较不匹配的数据类型，如下查询：SELECtITEM_ID,ITEM_DESCFROMCHECK_ITEMWHEReITEM_ID=9SQL会自动将语句变为WHEReTO_NUM-BER（ITEM_ID）=9，导致索引无法使用，应将查询语句改为：SELECtITEM_ID,ITEM_DESCFROMCHECK_ITEMWHEReITEM_ID=‘9’（二）减少排序1.用UNIOnALL代替UNIOnUNIOn。在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是历史表和过程表UNIOn，如：SELCT*FROMINCOME1UNIOnSELECt*FROMINCOME2SQL在运行时先取出两个表的结果，然后用排序空间进行排序并删除重复的记录，最后返回结果集，如果表数据量太大的话可能会导致用磁盘进行排序。修改方案：采用UNIOnALL操作符来替代UNIOn，因为UNIOnALL操作只是简单地将两个结果合并后就返回。2.使用WHERe代替HAVINg。尽量避免使用HAVINg子句，HAVING子句只会在检索出所有记录之后才对结果集进行过滤。这一处理需要排序、总计等操作。如果能通过WHERe子句限制记录的数目，那就能大大减少这方面的时间开销。例如：低效：SELECtTABLE1,SUM（QUARTER）FROMGL_BALANCESGROUPBYPERIOD_NAMEHAVINgPERIOD_NAME<>‘GG’高效：SELECtTABLE1,SUM（QUARTER）FROMGL_BALANCESWHERePERIOD_NAME<>‘GG’GROUPBYPERIOD_NAME3.减少I/O操作。过多的I/O操作会消耗大量内存、占用CPU时间，因此对SQL的I/O进行优化有必要的。（1）尽量使用索引，避免全表扫描，尤其是在数据量较大的、频繁使用的表上建立合理索引，如复合索引。（2）WHERe子句中的连接顺序。SQL采用自下而上WHERe子句，根据这一原理，表之间的连接必须写在WHERe条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERe子句的末尾。如：SELECtPERIOD_NAME，SUM（QUARTER）FROMGL_BALANCESWHEREPERIOD_NAME=‘GG’ANDACTUAL_FLAG=‘A’GROUPBYPERIOD_NAME（低效的，执行时间为15秒）SELECtPERIOD_NAME，SUM（QUARTER）FROMGL_BALANCESWHEReACTUAL_FLAG=‘A’ANDPERIOD_NAME=‘GG’GROUPBYPERIOD_NAME（高效的，执行时间为3.8秒）（3）选择最有效率的表名顺序。SQL的解析器按照从右到左的顺序来处理FROM子句中的表名。如果FROM子句中包含多个表，必须选择记录条数最少的表作为基础表。当SQL处理多个表时，会运用排序及合并的方式连接它们。首先扫描第一个表（FROM子句中最后的那个表）并对记录进行排序，然后扫描第二个表（FROM子句中最后第二个表），最后把从第二个表中检索出的记录与第一个表中匹配记录进行合并。例如：表TAB1有100条记录，表TAB2中有20000条记录。选择TAB1作为基础表，效果比较好。SELECtCONL1，CONL2FROMTAB2，TAB1WHERe…选择TAB2作为基础表，执行效率不理想SELECtCONL1，CONL2FROMTAB1，TAB2WHERe…4.减少表的访问次数。在执行SQL语句时，数据库内部要执行许多操作，这将严重消耗系统资源。如果能有效整合相关的SQL语句，则可以大大降低数据库的访问次数，从而提高系统性能。例如将下面两条语句整合为一条：SELECtBOOKS_ID，PERIOD_NAME，SUM（QUARTER）FROMGL_BALANCESGROUPBYPERIOD_NAMEHAVINgPERIOD_NAME=‘GG’SELECtBOOKS_ID,PERIOD_NAME，SUM（QUARTER）FROMGL_BALANCESGROUPBYPERIOD_NAMEHAVINgPERIOD_NAME=‘WIN’整合后：SELECtBOOKS_ID，PERIOD_NAME，SUM（QUARTER）FROMGL_BALANCESWHERePERIOD_NAMEIN（‘GG’，‘WIN’）GROUPBYPERIOD_NAME　　四、结语　　数据库查询优化是取得良好执行性能并简化管理的关键因素。SQL查询是一个有序的查询，不同语句的使用和使用顺序将直接影响其查询速度。凭着对具体数据库特征的了解，重新构造SQL语句的语法，开发人员能够帮助查询优化器获得更好的执行计划，这些执行计划是查询优化器无法靠自身独立工作产生。总的来说，SQL语句优化仍旧是—个实践问题。由于数据库版本各异，要求用户对数据库优化工具和相应的优化知识掌握非常多，并且优化依靠程序员的经验，所以数据库应用系统性能会因人而异。目前有些软件公司采用知识库的方法共享SQL优化方面的知识来提高程序员水平。然而，从目前的应用和开发现状看来，如何像软件辅助工具CASE一样更加方便地提升自动化程度，简化SQL优化的过程仍然是一个重要的课题。参与文献[1]江南，常春.SQLServer2000安全验证的故障诊断与分析[M].计算机与数字 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 .2006，36（4）：81～84[2]黄新立，邹志斌.提高ORACLE数据库检索速度[J].山东科技大学学报（自然科学版）.2004（23）：49～51[3]谷小秋，李得昌.索引调整优化Oracle9i工作性能的研究[J].计算机工程与应用.2005（26）：174～176[4]谷震离.SQLServer数据库应用程序性能优化方法[J].计算机工程与设计.2006，27（15）：2884～2886[5]Galindo-LegariaCA，StefaniS，WaasEQueryprocessingforSQLupdates[C]．SIGMOD，2004 -全文完-