提高性能的SQL编写建议
通过两周多的SQL优化工作，我个人学到了很多的东西，应该说感触最深的一点是“功夫下得越早越好”，问题的产生往往是从需求、设计时就埋下了隐患，只是在开发、测试甚至到上线后才显现而已。所以我也决定下早一点的功夫，结合一些前人的经验，结合我们系统的现状，写写我的优化心得，对开发提出一些建议，供大家参考，希望能够抛砖引玉，有更多的同事参与进来，帮助大家写出更加优化的SQL。
数据结构设计原则
首先要说说数据结构设计原则，虽然这里主要是说怎样编写执行效率高的SQL，但是毋庸置疑，在不好的数据结构基础上写SQL，就好比在一条崎岖颠簸的路上开车，即使司机的技术高超，即使汽车的性能强劲，也很难把车开快、开稳。当然，关于数据结构的设计原则也是一个很博大精深的问题，我们在这里只讨论与我们SQL性能相关的一些原则：主键的选取对于一个表的设计至关重要，一旦进入编码阶段，对主键的修改将会付出很大的代价。对于一个表的主键，字段选取过少可能会导致表无法适应业务的发展，字段选取过多则会使得表的效率严重降低。选取主键的方法是：选取一个唯一键作为主键的备选方案，然后试着去推翻这个选择，直到找到一个比较好的选择，或者推翻所有的唯一键，新增一个流水号字段作为主键。主键的字段应处于一个表的前几个字段，不宜超过3个字段，更多的字段最好改用流水号做主键，排列顺序按区分度从大到小，在这里“区分度”是指平均每个选值对应的数据条目数的倒数，即区分度越大的字段意味着该字段选取某一个特定值的数据占全部数据的比例越小，因为主键上会有索引，而索引也应该设计在区分度大的字段上。
根据需要定义字段的长度与类型，如果能用更短的字段则使用更短的字段，例如用Char(2)代替Char(20)来保存状态标识符更合理。这里还要特别说一下我们系统中部分字段是Varchar2类型（例如工作流、扫描表的字段），需要与char类型的字段（例如业务表数据）进行连表查询，由于char类型和Varchar2类型对于字段长度的处理机制不尽相同（char类型按照字符长度会补空格，而Varchar2类型不补充空格），查询中需要进行trim之类的处理，会大幅降低系统性能，因而我们对数据库的字段设计给出如下建议：如果该字段将与系统中已有字段进行连查，则将这个字段设计为与原有字段相同的类型与长度；对于新的意义的字段根据需要定义长度，根据系统现状一般流水号性质字段定义为Varchar2(20)，状态标识符定义为Char(2)，客户录入内容的字段（如备注、特约等修改较少，可能为空的字段）定义为Varchar2，长度根据客户要求处理，汉字数量乘二。关于Char类型和Varchar2有些小知识，是我们应该知道的：Char类型比Varchar2类型更通用，但是在可见的未来里我们不太可能把系统的数据库从Oracle换成MSSQLServer或者其它什么的，因为数据库之间关于并发的处理机制的查距太大，做这样的转换意味着大量的程序需要重写，而且难于测试；Char类型的处理速度比Varchar2的速度更快，Char类型字段比Varchar2更费空间，而且会损失一定的信息量，’abc’和’abc’在Char类型字段中保存的结果是一样的——这是效率占优的代价，但是如果一个字段需要经常修改，而且每次被修改的数据的长度不同，那么Char类型优于Varchar2，因为Varchar2的不定长保存机制，会产生‘行迁移’(RowMigration)现象，产生多余的IO，这也是为什么我们要将状态标识符置为Char类型；Char类型最长是2000字符，Varchar2类型最长是4000字符。对于新增表，避免定义可以为空的字段，在isnullisnotnull判断是否为空的查询，无法通过索引，而且对于两个可以为空的字段进行不等于的判断时要麻烦很多，例如A字段为空，B字段内容为“01”,A!=B并不成立，而我们通常的业务逻辑往往需要它成立，于是我们只好写A!=BorA=null，大大降低了SQL的执行效率和可读性。对于已有的表新增字
段，就要考虑这个表的使用范围是不是大，因为目前系统中Schema关于默认值的处理并不完善，直接增加非空字段可能会导致某些程序出Bug，因而在设计时要权衡利弊，全面考虑。字段的设计上应该考虑适当的冗余，以便将来的查询。因为我们所使用的关系数据库是以二元关系为基础的，如果数据结构层次过多会导致查询效率难以提高。例如设想通过一个案件号查询某一个特定的给付责任，这个查询需要通过案件找到个人合同，根据个人合同找到给付责任，如果中间的数据层次不够冗余，则查询可能变成，通过案件找到团单，通过团单找到个人合同，通过个人合同找到险种，通过险种找到责任，通过责任找到给付责任，这样的查询很难做到高效。
照看好你的索引
应该说索引是几乎所有SQL优化行为的核心，几乎所有的慢SQL都与没有使用索引有关：可能是有索引但是由于查询不合理而没有使用，也可能是因为干脆没有索引。索引的设计是数据库结构设计的一部分，应该在数据表设计的时候就把索引设计好，而不应该走“先污染后治理”的道路。索引的设计应当做到：经常同时存取多列，且每列都含有重复值可考虑建立组合索引，如客户信息中的五项基本要素。组合索引要尽量使关键查询形成索引覆盖，组合索引的本质是一个多次排序的数据集，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，因为索引对插入、修改、删除操作是有副作用的，过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。在一个表中同一个字段应该只在一个索引中作前导列。在需要索引处创建索引——这句话看起来是废话，但问题是我们往往并不知道哪里需要索引。应该说，好的索引设计是在表结构设计时对未来可能出现的查询的准确预期，我也只能用我浅薄的经验给出一些自己的体会。举个简单的例子，任何一个业务数据表的任何一行数据，都将面
临两类查询，一类是“有目的”查询，即程序知道某条数据在哪里，通过查询找到这条数据，或者说程序希望数据在那里，通过查询验证数据是否确实在那里，这一类的查询一般通过业务号等强区分度条件关联，举例：进行理赔立案申请时，根据客户号查询到相应的保单；另外一类的查询是“无目的”查询，即程序不关心这条数据是什么，而是关心这一类数据是什么，关心总体上有多少，或者更为简单的将所有的这一类数据列出来了事，这类查询一般通过时间、机构代码、险种代码等弱区分度条件关联，例如承保明细表。前一类大多数是业务操作产生的查询操作，也有少部分的复杂的统计会进行；后一类大概有一半是统计类的查询，而另一半来自业务操作，如应收抽档、日处理程序等。前一类查询所涉及的条件一般具有比较强的区分度，往往是数据表的主键，创建索引也是理所当然的；然而对于第二种查询，就需要设计者多花些功夫来考虑。时间（日期）从长期来看是区分度比较好的查询条件，在大多数第二类查询中都少不了时间的条件，一个数据表中往往有好几个时间，在哪几个时间字段上创建索引就很值得推敲。考虑在常用的函数上创建索引，如trim、rapd函数，CreateindexI_LLCASE_TRIMCASENOonllcase(trim(caseno))。这样做通常是一种亡羊补牢的行为，由于前期设计不合理，连表查询的字段长度不一致，在后来的查询中需要弥补长度问题进行访问，而like的写法在多表连查时难以奏效，此时只好通过在trim列上创建索引变通解决这一问题，这样做可以大幅提高查询效率，但是会在一定程度上影响更新效率，所以仅在修改不多的字段上进行。将查询条件写全，应该说这是一种懒人的做法，本质上是通过增加显式查询条件来帮助Oracle找到合理的查询计划，例如lcconta、lcgrpcontb、lcpolc三个表连查，查询所有团单号为“9028000000701288”的险种信息，不妨将条件a.grpcontno=b.grpcontnoanda.grpcontno=c.grpcontnoanda.grpcontno=’9028000000701288’修改为a.grpcontno=b.grpcontnoanda.grpcontno=c.grpcontnoand
b.grpcontno=’9028000000701288’andc.grpcontno=‘9028000000701288’anda.grpcontno=’9028000000701288’。避免使用IN、OR关键词，这两个关键词会使用工作表查询，使得索引失效，尽量使用union（all）。Oracle在解析SQL时会将OR转化成为Union，但这种转化是有代价的，也是有条件的，在这里我们不讨论这种转化，养成好的习惯，让SQL中少出现IN和OR就是了。避免在索引列上使用函数或进行其他计算，这些计算尽量在程序中完成，或者放到常数表达式中。例如：“select*fromlccontwheresigndate>sysdate-20”要比“select*fromlccontwheresigndate+20>sysdate”快成百上千倍，但是他们的含义完全相同。避免让数据库进行隐含的格式转换，这类问题在我们的系统中并不多见，但一旦出现也是很难发现的，这里指的是查询条件与目标字段类型不一致导致数据库“自作聪明”的转换，例如：“select*fromlccontwherecontno=9026000000018088”，实际执行的是“select*fromlccontwhereto_number(contno)=9026000000018088”，导致一个很简单的通过主键的查询变成了一次全表扫描，查询的损耗增加了上千倍，这就是少些两个引号的代价。经过一些简单的实验，我们可以发现这种转化一般是从字符串类型向其它类型转换，所以对于“select*fromes_doc_relationwheredocid='327'”这样的SQL并不太慢。尽量避免使用like‘%statement’，除非在这个字段上创建了反转函数的索引。根据需要尝试创建不同的索引，如果你有兴趣，可以通过互联网学习到一些关于索引的更进一步的知识，不同的索引拥有不同的特性，在不同的场合担当不同的作用，值得注意的是，这些特殊的索引在进行数据更新时可能产生大量行级锁，引起并发性能问题，所以创建特殊的索引一定要严格的进行评审，认真听取DBA的建议。
在同构数据表中创建索引，以我们系统为例：lccont表中需要的索引，lbcont表中大多也需要，即使现在不需要，将来也会需要。
其他优化手段
对比应用服务器，随着数据量的增长，数据库服务器的负担会越来越重，因为应用服务器只是在不断的新增功能，或对旧有的功能做改进，我们可以通过使用更多的应用服务器，进而让系统能够承载更多的用户，但是更多的应用服务器并不是更多的系统，因为他们最终还是要访问同一个数据库，因而理想的设计应当尽量减轻数据库的负担，使得数据库能承载更多的应用：简化业务逻辑，恐怕没有哪一个优化行为能比这个更有效。减少不必要的查询，如果程序的某一分支进行的查询可有可无，那么应该尽量避免这些查询。SQL优化是一项需要实验的工作，所有要提交的程序中的SQL都应该查看执行计划，即使你的SQL现在跑得很快，那可能是因为你的SQL所关注的数据量还很小，全表扫描的速度还很快，几个月之后数据量达到相当规模，程序就会比蜗牛还慢了。如果看不懂执行计划，最简单的方式就是看一下有没有“TABLEACCESSFULL”，有就试着优化。能用一条SQL解决的问题，就不要使用多条SQL，例如，初审录入完成时进行的数据校验，主要是校验一些必须信息是否录入，在这个过程中可能进行了多次，查询，其实完全可以在后台通过对一个Schema对象进行校验实现。当然这个原则也有例外，对于过于复杂的逻辑，还是分开写比较好。我们常用一个例子来比喻数据库的行为：100个人想要进同一间屋子，轮流排队进去再出来，这是需求；如果2个人合成了一个人，只是比原来胖了一倍，那么效率可能提高了一倍，因为只剩下50个人了，但是如果50个人合在了一起，比原来胖了49倍，那恐怕就没法通过屋子的门了，即使能通过恐怕也要挤啊挤啊挤进来，效率反而会比原来的慢——前面说了SQL优化需要实验，也许这门真的很宽呢？
减少不必要的Orderby，排序意味着O（logN）数量级的时间损耗，所以尽量避免在数据库中进行不必要的排序，如果不可避免，创建索引会提高排序的速度。另外，排序可以在数据库外进行，而且也可以做到时间、空间很节省，在条件允许的情况下我们可以考虑在应用程序中进行排序。可能的话用Unionall代替Union，Union意味着排除重复，尽管这在某些情况下也是业务逻辑所需要的，但是有些情况下Union双方的的数据本来就不可能重复，那么使用Unionall就会避免排序，对于将Union（all）的结果作为视图进行查询来说，Unionall能帮助数据库使用索引。去掉不必要的distinct，distinct同样意味着排序，逻辑上不会重复的数据，就应该去掉distinct，减少排序，两个union链接的查询语句，都是不需要加distinct的。用exists代替in，与上面的两条一样，in意味着排序，in的返回结果集超过100条时就强烈建议使用exists。查询条件中用>=(<=)代替>(<)，因为>会先按照>=执行，然后去掉=的部分，如果是整形数值A>=3会比A>2快一些。减少select*from的使用，只选取你所需的数据。另外selectc.*,’1’fromlccontc是一种极易产生bug的写法，如果lccont的字段增加了，这个标志’1’就会错位。进行相同逻辑的查询，尽量使用同一个函数以保证SQL的执行计划能被数据库重用，这样做对于程序的结构化也有好处。以上就是我关于SQL效率优化的一些心得，由于时间和水平有限，恐怕难免疏漏，恳请大家斧正。严凯：yankai@sinosoft2008-5-15
