sql语句优化原则

SQL 语 句 优 化 原 则数据库性能是整个应用程序性能的重要部分。数据库优化涉及的内容非常广泛，各类数据库都提供众多的性能指标和大量的优化工具。下面我们简单介绍一下优化的基本概念。一个运行良好的数据库至少应具有以下特点：合理的物理结构及硬件能力合理的物理结构指数据库文件及整个网络的物理分布。硬件能力指是否有足够的硬件资源来完成应用程序功能。合理的物理结构至少带来两个方面的好处：1、 适量的数据冗余，提高数据安全性。2、 平衡磁盘IO，增强数据读写能力。而足够的硬件能力的作用自然不言而喻。一旦建立好数据库系统并开始运行，数据库的物理结构就不能改变。合理的系统参数对应数据库来说，随着数据量的变化，数据库性能也一直处在变化之中，因此数据库建立之初设定的系统参数会变的越来越不合适，有时甚至阻碍了数据库的正常运行，导致性能瓶颈。因此观察性能变化，随时调整系统参数，使数据库一直处于一个良好的运行状态，就成为管理员最重要的日常工作之一。对系统参数的合理调整，常常能将数据库从崩溃的边缘挽救回来。oracle、sqlserver这样的高品质数据库都为系统参数提供了灵活多变的调整方式。一般来说，只要数据库结构设计不存在重大缺陷，通过后期的调整，都可以使数据库运行在一个良好的状态下。性能优良的sql语句sql语句是在程序开发阶段就已经决定了的，由低效率的sql语句给数据库性能带来的问题，往往在数据库开始运行一段时间后才凸现出来（随着数据量的不断增加），但发现后就变的难以改变，成为不可突破的性能瓶颈。因此，作为一名合格的开发人员，应该建立基本的优化概念和良好的编程习惯，从整体上提高应用程序的质量。同时，提高sql语句的执行效率，是提高整个数据库性能的最立竿见影且价格低廉的方法之一。因为几乎所有的数据库都会不可避免的运行一些效率低下的sql语句。对数据库性能的调整，往往都是从sql语句调优开始的。下面我们简单介绍数据库是怎么执行sql语句的。sql语句是唯一从应用程序发送到数据库实例的命令。数据库实例所做的全部工作就是接受、解释和执行sql 语句。在绝大多数情况下，我们并不需要关心sql语句是怎么执行的，这是因为在当前流行的数据库软件中都无一例外的采用了高性能的优化器，而这些优化器在绝大多数的情况下都能将用户某些不合理的sql语句结构转换成更合理的形式，从而有效提高sql的执行效率。优化器的优化原理有两种：基于成本的优化对一条sql语句，优化器会生成所有可能的执行方式，估计这些执行方式将使用的硬件成本，相互比较后从中选择成本最低的执行计划。缺点是必须收集大量统计数据，对服务器造成额外的负担。基于规则的优化相对基于成本的优化，基于规则的优化则显得死板的多。比如sqlserver遇到blog_id=*的情况就会去找索引，实在找不到索引才使用全表扫描。而不考虑有时候不用索引可能效率更高。显然有些情况下基于规则的优化并不合适。/*执行计划的概念:简单的说执行计划就是指在执行sql语句前对代码进行编译时数据库实例为sql选择的执行路径。如一个sql对A、B、C三个表进行联合查询，数据库会首先以某种方式对这3个表种符合条件的记录进行查询（全表扫描或其他），再将A、B、C表中符合条件的记录读入内存，将A、B表的记录相比较后得出的结果集与C表相比较，最后得到符合的结果集。但实际情况比这复杂的多。*/oracle可以选择两种优化模式之中的一种，而其他数据库则是固定的（DB2不清楚，关于DB2的资料太少了），基本都是基于规则的优化。然而优化器毕竟不是智能的。很多时候，它不可避免的受到sql语句结构的影响。而SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表扫描的发生。sql的书写原则以下介绍的标准sql的书写原则是本文的核心，这些原则适用于绝大部分数据库。介绍这些原则的时候，我会举一些简单的例子来说明，但这些例子在实际生产中可能没有什么实际意义。1、使用索引如果没有任何索引，在执行sql语句时必定将做全表扫描。这和我们在看一本书时，如果没有目录，我们只能从第一页开始查找，直到找到查询的内容是一个道理。全表扫描是效率最低的查询方式，我们会看到绝大多数的低效率sql就是使用的全表扫描。因此在对一些大表进行查询的时候，我们需要关心一下表中是否建立了索引并尽量使用索引。总的来说索引可以分为聚集索引和非聚集索引。一个表中只能有一个聚集索引。对一个表建立一个聚集索引后，数据库会调整表记录的顺序，使表按照索引的顺序重新排列。而非聚集索引则不会改变表的结构。使用的最常见的索引就是主键索引。实际上在表中指定一列为主键的时候，就在这列上建立了唯一值索引并强制该列的值唯一，这就成了主键索引。在下面几种情况下，应该建立索引：1. 有大量重复值、且经常有范围查询（between,=,=$size$+$fromID$) AND LOCKED=1 ORDER BY BLOG_ID DESC；通过查看执行计划，我们可以看到执行查询的步骤： 1、子查询中使用blog.pk_blog（主键索引）查询BLOG中满足blog=$size$+$fromID$的记录,这步占总成本的46。2、使用blog.pk_blog（主键索引）,查询BLOG中满足LOCKED=1的记录。这步占总成本的44。3、使用Merge join。这步占总成本的10。总成本为0.23。我们可以看到两次对blog表的查询都使用了blog_id上的主键索引，同时表连接消耗的资源也比较少。应该说，对于子查询结构，这个查询的效率并不低。但是，这和下面的语句完全是等价的：SELECT TOP $size$ BLOG_ID AS blogID,BLOG_DOMINO AS blogDomino,BLOG_TEMPLATE_ID AS blogTemplateID FROM blog WHERE BLOG_ID$size$+$fromID$ AND LOCKED=1 ORDER BY BLOG_ID DESC；总成本为0.0035。优化器不是人工智能，在第一个查询中指定使用子查询结构，优化器就无法突破这个限制，尽管两个查询是相同的，优化器还是不能将第一种查询结构等价的转换成第二种。所以查询语句使用的结构对优化器影响重大，我们在进行多表查询等复杂情况的时候的时候（如可能需要使用联接查询、子查询、嵌套查询、group by等），应多进行一些考虑。一个原则是结构要尽量简单，这样在编译效率、执行效率和程序可读性方面都有好处。3、谨慎使用is null和is not null 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使在列上建立了索引，只要这些列中有一个含有null，该列就会从索引中排除。也就是说，如果某列存在空值，即使对该列建索引也不会提高性能。同时，任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。4、in和exists很多资料都声称exists的效率高于in。而实际情况证明，在子句中使用in和exists效率是一样的。SELECT ProductNameFROM Northwind.dbo.ProductsWHERE UnitPrice in(SELECT UnitPriceFROM Northwind.dbo.ProductsWHERE ProductName = Sir Rodneys Scones)SELECT ProductNameFROM Northwind.dbo.Products bWHERE exists(SELECT UnitPriceFROM Northwind.dbo.Products aWHERE a.ProductName = Sir Rodneys Sconesand a.UnitPrice=b.UnitPrice)查看执行计划可以看到，上面两个语句的执行路径和执行成本都完全一样。5、in和orsqlserver会自动将in转换成or，因此对sqlserver来说下面两个语句是一样的，而其他数据库中in的效率要高于or。 select blog_name from blogwhere blog_name like AB%or blog_name like CD%or blog_name like EF%;select blog_name from blogwhere blog_name in (AB%,CD%,EF%);6、查询字段要需要多少，查询多少我们每少提取一个字段，查询速度就会有相应的上升。这主要是因为物理读取成本降低了。所以要避免select *这样的查询，需要哪些字段就查询哪些字段。7、将行和操作数减到最少使用 WHERE 和 HAVING 子句只选择需要的行，可以将 SELECT 语句返回的行数减到最少。尽量少用不等于运算符 或 !=。数据库将必须在表或索引中扫描所有的值，以查看它们是否不等于表达式中给定的值。可以使用范围重写表达式：WHERE KeyColumn TestValue8、尽量少用格式转换，防止出现隐含的格式转换。举个简单的例子：某个表中有一个时间字段time date型，现在开发人员需要写一个sql：查询出在2004-04-21到2005-04-21之间，注册了多少blog新用户。我们比较下面两个语句：select * from blog where to_char(create_time,yyyymmdd) between 20040421and 20050421;将time转换成字符型，和20040421及20050421做比较。select * from blog where create_time=to_date(2004-04-21,yyyy-mm-dd) and create_time28847;的语句改为：where blog_id28.847;12、使用表提示使用表提示的本质是影响优化器，使优化器按照指定的执行计划来执行sql语句（通常是制定多表连接时的连接方式）。一般情况下并不需要使用表提示，因为优化器总是能选择适当的执行计划。这种做法只是增加了性能优化的可能性，并不一定会产生好的影响。由于使用表提示有时反而会降低执行效率，因此，只有当我们发现一些sql在低效运行且认为有必要干预sql执行计划的时候（可能已经严重影响了应用程序的运行）才使用。同时需要通过反复调试来达到最佳的效果。使用表提示的方法：Oracle中使用hint提示，sqlserver中使用option子句。13、慎用游标在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。14、关于多表连接和子查询在实际应用中我们常常会遇到需要使用多表连接或子查询的情况。同时我们会发现，很多时候这两种查询结构可以做等量的转换。下面的例子就是这样：select ProductNameFROM Northwind.dbo.ProductsWHERE UnitPrice in(SELECT UnitPriceFROM Northwind.dbo.ProductsWHERE ProductName = Sir Rodneys Scones)SELECT Prd1.ProductNameFROM Northwind.dbo.Products AS Prd1JOIN Northwind.dbo.Products AS Prd2 ON (Prd1.UnitPrice = Prd2.UnitPrice)WHERE Prd2.ProductName = Sir Rodneys Scones(T-sql。使用的是northwind示例数据库中的表)子查询和不包括子查询但语义上等效的语句在性能方面通常没有区别。但是，在一些必须检查存在性的情况中，使用联接会产生更好的性能。否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。所以在这些情况下，联接方式会产生更好的效果。如下面两个等效的sql：Select distinct a.blog_nameFrom blog aWhere exists(Select b.blog_idFrom blog_note bWhere a.blog_id=b.blog_idAnd b.create_time2005-04-25 );Select distinct a.blog_nameFrom blog a,blog_note bWhere a.blog_id=b.blog_id And b.create_timedeclarevid int default 1;res varchar(4);beginexecute immediate select * from blog where blog_id =:x into res using vid;dbms_output.put_line(res); -oracle系统包，用于输出。end;/（PL/SQL。这一招的用意很明显就是固定sql文本，把编译器骗过去，有点瞒天过海的意思。其他数据库使用绑定变量的方法也是一样的，只是使用的语句稍有不同。）对于sqlserver来说使用绑定变量意义不大，因为sqlserver数据库引擎可以识别出上面给出的例子，绑定变量具有一定的优势，但并不明显。另外需要说明的是在优化器内部对执行计划会存在一个衰减列表，到一定时间后执行计划还没有被重复使用，就会被清除出这个列表，这时再执行sql语句就必须重新编译了。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。另外，不同的数据库使用的优化原则不同，导致对同样的sql语句，做出完全不同的执行计划。即使是同样的数据库、同样的语句，在数据库运行的不同时期执行效率也会发生很大的变化。因此，对数据库的优化更多的时候是对数据库的观察和调试，而不是教条式的简单修改。有兴趣的同事可以使用下面的方式查看sql语句的执行计划，而执行计划反映了此刻sql语句的执行过程，使用的资源等等，是sql语句效率高低的量化数据。Oracle:sqlrdbmsadminutlxplan.sql运行utlxplan.sql脚本，会自动创建一个plan_table。这步只需要做一次。建立后每次进入sqlplus时：sqlset autotrace on 这时每次执行sql，都会显示出相应的执行计划。Sqlserver:在sqlserver中查看执行计划非常简单：进入查询分析器，在窗体中输入T-sql语句。点击【查询预计的执行计划】（该按钮在切换数据库下拉菜单的左边。或ctrll）。这时执行计划就会显示在窗体下端。其他数据库查看执行计划的方式如有需要可以上网查找。欢迎多交流。后记对于现有的每一种数据库来说，标准SQL的功能显然太弱了。因此，每一种数据库都对标准SQL进行了性能扩展。对于oracle是PL/SQL。Sqlserver是Tsql。在本文中列举的例子，并不一定在你的数据库中也能成功执行。同时，本文中的某些原则，可能对于特定数据库、特定优化模式下的sql性能并不能起到明显改善的作用。比如在使用通配符的例子中：select * from employee where last_name like %cliton%; 对于oracle，将无法使用last_name上的索引，导致这个查询会发生全表扫描。而对于sqlserver来说，仍然可以正常使用索引。尽管如此，在开发过程中对一些小细节的注意，不仅可以保证在大多数数据库中有较高的执行效率，还可以使sql语句在今后数据库运行的过程中或某些特殊情况下（如系统迁移），也能长期保持稳定的状态。本文中的sql示例凡是没有特殊注明的，都是针对Tsql。对于某些概念性的东西，为了写的简单易懂，我用自己的理解进行了修改，可能有很多不准确的地方。本文分为几个部分，大家可以使用文档结构图来查看自己感兴趣的部分。如果觉得有什么不清楚或是不认同的地方，欢迎和我交流，共同进步。陆 雁2005-04-21适应角色转变，扎实开展团的工作共青团铁东区委书记的述职报告2011年是适应角色转变、思想进一步成熟的一年。这一年，自己能够坚持正确的政治方向，紧紧围绕党的中心，立足本职岗位，较好地完成本线的工作任务。自己政治觉悟、理论水平、思想素质、工作作风等各方面有了明显的进步和提高。总的来说，收获很大，感触颇深。一、以德为先，进一步提升个人思想素质过去的一年，我以一个共产党员的标准，以一个团干部的标准严格要求自己，在个人的道德修养、党性锻炼、思想素质上有了很大的进步。一是道德修养进一步提高。作为一个团干部，我的一言一行、我的自身形象将直接影响到团委各成员，甚至更广大的青少年。因此，在日常的工作和生活中，我每时每刻提醒自己，从小事做起，注重细节问题，做到干净做人、公正做事，以平常心看待自己的工作，要求自己在工作中诚实、守信、廉洁、自律，起好表率作用。二是党性锻炼得到不断加强。不断加强自己的党性锻炼，我严格按照党章和中国共产党党员纪律处分条例来要求和约束自己的行为，牢记党的宗旨，在团的工作中，以广大青少年的权益为出发点，务求时效。三是政治思想素质不断提高。一年来，我继续加强学习，积极参加理论中心组学习，经常自发利用休息时间学习，积极参加团省委组织赴井冈山革命传统与理想信念教育专题培训班、区委区政府组织赴清华大学县域经济培训班，通过“看、听、学、思”，进一步加深了对马列主义、毛泽东思想、邓小平理论、“三个代表”重要思想的理解，进一步系统掌握了党在农村的路线、方针、政策以及对共青团工作的要求。特别是党的十七届六中全会以来，我通过学习原文、听专家讲课等，开拓了思想新境界，政治思想素质有了新的飞跃。二、以能为先，进一步加强组织工作能力在上级领导的信任和支持下，我本人也自加压力，抓住一切机会学习，注重与同事、与兄弟单位团委书记的交流，虚心请教，不耻下问，使各项工作都有序地开展。一是工作的统筹安排能力不断加强。我尽量做到工作提前一步，有计划、有安排、有预见性，保持思路清晰和决策的科学，力求操作有序，顺利开展。二是工作的协调能力不断加强。在工作中，我注重与上级的及时衔接、汇报，同时也注重与基层的交流沟通，听取多方意见和建议，从大局出发，对上做好配合，对下做好团结。三是有创新地开展工作。在工作中，我注重不断创新，使工作保持生机，使管理不断趋向人性化、合理化。三、以勤为先，进一步提高团的业务水平担任团委书记以来，认真了解情况、掌握知识，积极向团委领导、向前任书记学习、请教，了解团情、团史，努力掌握团的基本运作方式程序，便于更好地开展工作。加强沟通了解，增加感情，深入基层，了解基层团组织和团员青年的有关情况，以“活动”来强化自己的知识和水平。一年来，我立足以活动来促使自己尽快适应角色，迎接挑战。今年五四，团区委以全区人居环境整治为依托，以“五四火炬传承九十二载生生不息，铁东青年投入人居环境立志强区”为引领，积极开展了“共青团路，红领巾街”，“铁东青年林”等一系列活动。在活动中，增长了知识，深化了理解，使自己对团务工作有了全面的、系统的提高，为今后更好地提高团的业务水平打下了坚实的基础。四、以绩为先，进一步完善团的组织建设把关于进一步深化“党建带团建”工作的实施意见落到实处，把党的要求贯彻落实到团的建设中去，使团的建设纳入党的建设的总体规划。依托党建，从政策层面来解决和落实基层团组织存在的问题和困难。一是基层团干部的待遇问题。积极争取党组织在团干部配备上的重视和支持，基层团干“转业”得到了很好的安排（叶赫的荣威，住建局遇良，卫生局王国宴等）；二是解决好基层团组织活动的经费问题。积极争取专项，今年为每个乡镇街道从团省委争取经费三千元，共计三万六千元；三是团的基层组织格局创新工作。按照“14N”模式，通过换届调整选配了大批乡镇（街道）团干部，变原有的“团干部兼职”模式为现在的“兼职团干部”模式，提升了基层团组织的凝聚力和战斗力。此次工作得到了团市委的充分认可，2011年四平市组织部班工作会议在我区召开。以服务青年需求为目的，从单一组织青年开展活动转到生产环节，开展就业培训、创业交流、贫富结对；以服务党政中心为目的，发挥团组织自身优势，引导青年树立市场意识和投资意识，强化科技意识和参与意识，投身知识化、信息化和现代化、文明创建、环境整治、植绿护绿、社会治安等活动，把党政思路实践好。突出做好当前新兴的农村、社区和非公经济组织建团工作，延长团的工作手臂，丰富团的组织形式。先后与农联社、吉林银行等多家金融机构积极协调，为青年创业就业提供帮扶支持。特别是吉林银行的“吉青时代”小额贷款项目更得到团省委的无偿贴息。 五、以廉为先，进一步保持清正廉明形象作为新任职的年轻干部、党员干部，我既感受到了组织的信任与关怀，同时也感受到了责任重大。我区在党委和政府的带领下，励精图治、奋发图强，取得了辉煌的成绩。越是这种时候，就越需要我们这些干部保持清醒的头脑，保持共产党员的先进本色。深知，作为一级干部，应该努力做到“清正廉洁”。古人说“物必自腐而虫生”，腐败现象表现上看来是经济问题、道德问题，但深层次的原因却是理想信念出了问题。要不断加强实践锻炼，要结合党的历史经验、改革开放和社会主义建设的实践以及自己的工作和思想实际，来刻苦磨炼自己。勇于剖析自己，积极开展自我批评，净化自己的灵魂。不断增强拒腐防变意识。在思想上、在行动上、生活中争作表率。在团区委开展“争做勤廉表率，竭诚服务青年”主题教育活动,召开机关党风廉政建设宣传教育活动动员会，全面启动党风廉政建设宣教活动。按照学习贯彻区委、区纪委关于党风廉政建设和反腐败工作的部署和要求，学习党的十七届六中精神，强化组织领导，制定工作计划。我们根据2011年党风廉政建设责任制考评要求，为了做好党风廉政建设和反腐败工作，成立了团区委党风廉政建设领导小组，并由我任组长。按照“一岗双责”的责任要求，明确了单位正职领导作为第一责任人，每年约谈团干部一次，就有关廉洁从政个人“不准”和“禁止”行为适时对所管的团干部进行廉政谈话。在2012年即将到来之际，共青团区委迎来组织部考核组，对共青团区委一年来的工作进的实地测评，感谢组织的帮助与关怀，今后我们更要自觉地接受组织的监督与考核。铁东区的发展已经取得了令人瞩目的成就，而今又开始了新的征途。广大青年有幸成为亲历者，成为追随者，同时我们也是共享发展成果的受益者。我们应该心怀感恩，心存畏惧，“做一个组织和群众信赖的人，做一个同事和朋友敬重的人，做一个亲属子女可以引以为荣的人，做一个回顾人生能够问心无愧的人”。我们要牢记党的宗旨，全面贯彻党的方针路线，高举中国特色社会主义伟大旗帜，弘扬“攻坚克难、求富图强”的四平精神，坚定不移的实施 “五区”战略的发展规划，为建设富裕和谐新铁东的伟大目标而不懈奋斗。