oracle调优思想--sql写在哪里更合适

存储过程还是ORM还是纯JDBC

              本文仅仅是个人在很多现实系统中的经历和总结出来的经验，并且每句话都经过了生产的验证，并且笔者在写文档的时候不站在特定立场，如JAVA开发人员或数据库开发人员再或者是DBA。

对于SQL语句到底是应该在存储过程还是ORM还是纯JDBC中呢，无论是网上还是我的身边，几乎不同角色的人都有自己的想法，包括各种应用的厂商也是如此。比如说数据库厂商和数据库开发人员通常都认为把所有逻辑放在存储过程中是最好的选择（至少是说数据库能完成的尽量让数据库完成），这样不仅安全性提升了，SQL解析减少了，网络负载也减少了，而且更容易维护和优化；而Java开发人员则倾向于使用ORM工具，他们的理由则是工作效率可以增加很多，业务清晰很多，而且可以增加应用程序通用性，而且他们只要知道基本的SQL就可以了，不用学数据库，也不用学PL/SQL或T-SQL。

 

总的来说把SQL语句放在存储过程或者代码中都有各自的好处，不过一味的从一个角度去考虑必定具有其局限性，因为每种方式的产生总是有其一定的优势的。

 

因此，总的来说，一、对于纯粹的业务比较简单的OLTP型并且要求不是很高的应用，可以考虑使用ORM工具自动映射（总的来说，这种业务所占的份额非常少）；二、如果公司或者项目组有专门的数据库开发人员，并且对数据密集型要求性较高的应用，一般还是会选择使用存储过程；三、至于直接把代码嵌入在Java中的，目前来说这是非常落伍的一种方式。四、将SQL代码抽象到文本文件如XML中（可以完成该工作的工具已经非常的多如ibatis等）；五、PL/SQL或T-SQL中（目前根据我的经验，综合看来这个是最好的方式，下面我会具体讲到）。

 

很简单的增删改虽然可以借助ORM，不过还是不推荐，对于语句可能比较长的情形，可以考虑使用存储过程。但是在一个系统中通常几乎不太可能使用多种方式去完成一种相同的工作，因此比较推荐将SQL代码抽象到文本文件如XML中（ibatis和其他一些框架可以做到），同时对于是否使用绑定变量根据模型的均衡度做好开关控制（这是一个非常重要的方面，如果你是dba，你必须知道是否应该选择硬解析），但是这方面这些框架做的就不够好了，基本上都是绑定变量。

 

使用第四种方式的好处是：一、SQL代码全部整合到一个地方，容易中心管理；二、SQL代码可以随时更新，容易优化；三、逻辑清晰，维护人员方便接手；四、这一点跟存储过程的编译有关，在24*7有业务的系统中，代码的优化或升级维护可能会严重推迟，对系统造成非常严重的影响，特别是在数据库优化的时候，关于这个情形的例子，可以参见OWI一节中的Library cache lock和library cache pin事件的解释。这种方式有一个致命的缺陷：因为所有的配置文件往往都是在启动的时候加载一次，而不是每次都去重新加载的，这就会导致虽然代码能够优化，但是却无法生效。所以，这种方式仅仅针对的是业务比较简单的应用或者纯OLTP应用，如果涉及了DSS类似的应用，并且应用又很重要，这种情况下，造成的后果将是很严重的。

 

       最后一种方式把SQL全部抽象出来到PL/SQL或T-SQL的好处是：第四种方式的绝大部分优势都兼有，比如代码整合到一个地方、SQL代码可以随时更新优化，并且解决了其无法动态优化的缺点，另外还可以控制是否选择使用绑定变量。不过，这里存在的一个比较大的挑战是中间层如JAVA必须调用存储过程，传递参数，存储过程里面就一个很简单的OPEN CURSOR，返回结果集供客户端使用。虽然本身没什么问题，但往往大部分开发人员都极力反对，特别是某些做项目但是不重视维护的情况下非常严重。因为这些开发人员会认为这增加了其很多工作量，但事实上一点没有。

 

在本章中，关于SQL代码到底应该在什么地方的问题，这里还有一个要提及的很常见的问题是动态SQL。其实大部分情况都没有必要使用动态SQL，当然也有些情况确实应该使用动态SQL。不应该使用动态SQL但使用了动态SQL的情形如下，一个查询要求访问一些表，其中有很多条件都是可选的，比如产品查询的事务，可以根据产品的名称，类型，产地，颜色等等进行查询，因为这些条件都是可选的，因此，有不少的开发人员在写代码的时候会先使用if进行判断，最后拼接起来执行，当我和开发人员交谈的时候，他们通常会说这样会影响性能。虽然可以使用这种方式完成工作，但是除非系统CPU很紧张并且要求非常高，否则我们一般尽可能的让开发人员使用nvl完成，因为是在pl/sql中还是在java中，使用非拼接方式编写的代码要比拼接方式编写的代码在调试、维护和优化成本要低很多，并且总的来说，这里的额外性能消耗基本上可以忽略。

这里我们再举一个不应该使用动态SQL，但是因为模型设计上的严重错误，导致不得不使用动态SQL的例子，有这样一个应用，它要查询客户表、任务表、任务完成状态表、员工、员工部门等等一系列的表，因为它要求查询结果可以选择包含任务状态，也可以不包含，可以包含员工部门，也可以不包含员工部门。因为员工表只有部门编号，任务表也没有状态的信息，为了满足这种需求，开发人员为了减少些SQL，选择根据查询传递的条件判断是否应该关联一些可选的表，返回结果集，然后在结果集中根据标志为判断应该赋值给哪个类型的记录集。因为客户表，任务表之类的本身由于设计上的问题已经有四五十个被称之为属性的字段，即使超过一半以上的这些字段基本上不会被应用所用到，导致开发人员最后甚至不敢再往这些表增加真正有用的字段。因此导致了代码的难以维护和优化。

 

下面说说应该使用动态SQL但是却没有使用SQL的情况。考虑真是应用中很常见的一种设计，流水表的设计通常为当前表+历史表，当前表一般存储当天或最近一个月的记录，历史表则存储之前的数据。而应用中，大部分应用一般查询当前表为主，有少部分会查询历史表或同时查询当前表和历史表。因为当前表和历史表的结构往往完全相同，在这种情况下，我们曾看到不少的开发人员为了省事，通常直接同时查询两张表，使用union合并结果集。因为如果不这么做，就意味着开发人员需要先根据传递的时间字段进行判断，然后确定应该查询当前表，历史表还是两者。但是额外访问当前表和历史表的所需要的资源消耗要比之前所说的昂高的多。再举个应该使用动态SQL但是没有使用动态SQL的情形，在通常情况下，当静态SQL语句写在PL/SQL中时，SQL语句中的变量条件是会被自动优化为绑定变量的；而当SQL语句写在Java中时，现在的绝大部分开发人员都会使用prepareStatement …, set …, executeStmt这种方式来避免SQL的重复解析，但是这里有个需要注意的问题就是如果SQL比较复杂的时候，执行计划往往会不正确，这个时候我们通常会先通过hint以及重写SQL获得较为合理的执行计划，然后固定住。有过优化经验的读者应该都知道，我们要想把通过常量的方式优化出来的语句更改为在使用绑定变量的时候仍然是完全相同的执行计划的虽然也是可以做到的，但其代价是非常高的，至少语句复杂的语句来说是如此，因为对于每张表的每种查询方式、关联方式、并行方式等等你都要非常的清楚，这不仅要求对优化器提供的各种可能的选项了如指掌，你还要仔细斟酌每种选择（但是公开的hint就很多了，更何况公开的只是很少的一部分，很多时候我们确实会用非公开的hint或者说在低版本中使用了高版本的特性，这些特性是作为隐含特性出现的）。在95%以上的情况下，我们在语句优化的时候，基本上不会对于每个不合理的脚本都花费这么多时间（虽然我们可以做到），我们只对具有决定性意义的步骤做指示，剩余由优化器自动决定如何处理，比如说一个语句涉及了8张表的关联，这些表有些需要外关联，有些需要聚合分析函数，有些在IN子查询中，有些在NOT EXISTS子查询中，最后还有一些可能是函数返回的结果集构造成的表函数，在CRM一些商业系统中，这些需求是非常非常多的。

总结，笔者总的来说推荐把SQL这一块抽象出来到存储过程中，但是逻辑判断以及参数格式之类的判断则不要放到存储过程中，因为这毕竟越在前面处理成本越低。这样可以做到SQL集中，动态优化可以随时生效，能够在语句级别决定是否硬解析。