re: 关于数据库存储过程的一些讨论

不再画地为牢

这个应该看具体的应用吧，如果应用的逻辑较多且访问量比较大的话，我觉得还是少用存储过程比较好，放在外部应用中有个好处就是负载均衡比较容易，多加几个应用服务器就好了，数据库呢？换性能更好的服务器？还是垂直拆分、水平拆分？

newkid

原帖由 space6212 于 2011-2-12 09:07 发表
我也有很多条理由不用存储过程，其中最重要的一条就是：当你的系统非常繁忙，还是建议把数据库用得越简单越好

那你终究得用SQL是不是？你本来调用一次，现在要跑好几次SQL才能完成一个事务，不单不能减负，反而是增加了数据库的负担。如果有例子请举一个。

newkid

原帖由 ban_ping 于 2011-2-12 09:36 发表
newkid的观点和Tom的一致，我觉得面向传统企业的应用和面向互联网的应用是有很多差别的，对传统企业应用比如ERP系统来说，newkid的说法可能是合适的。很多互联网公司只是把数据库作为在硬盘上持久存储数据的一个工具而已，缓存才是他们核心的东西，而大多也没有十分复杂的业务逻辑，即使有复杂的业务逻辑也是通过异步操作等手段处理，这样能更灵活的处理高并发访问的情况，如果业务逻辑都封在数据库里，实际对数据库运行环境的要求是很高的，扩展方面可能也有问题。

异步操作的代价是复杂化，往往伴随着分布式事务。还要牺牲用户体验，因为虽然响应时间快了，但是得不到一个明确结果，到底事务成功了没有？
存储过程扩展性问题的例子？

newkid

原帖由 regonly1 于 2011-2-12 10:14 发表
我也觉得应该用灵活的角度去看待这个问题，我现在用的环境是newkid说的，把很多逻辑都放在包中实现了
但是在数据库本身访问压力大的情况下，再用数据库来实现业务逻辑，就必然会出现瓶颈，以前听taobao的架构师讲过，他们是尽量只把核心数据库当作数据存储来使用，逻辑之类的都放到前端来实现了。

还是那句话，一个事务要用多少SQL是跑不了的，除此之外的所谓的“逻辑”只是一些轻量级的东西，拿到数据库之外丝毫不能为数据库减负。
要是有不同意见请举个具体例子。

newkid

原帖由 不再画地为牢 于 2011-2-12 10:33 发表
这个应该看具体的应用吧，如果应用的逻辑较多且访问量比较大的话，我觉得还是少用存储过程比较好，放在外部应用中有个好处就是负载均衡比较容易，多加几个应用服务器就好了，数据库呢？换性能更好的服务器？还是垂直拆分、水平拆分？

同上一楼，不管你有多少个应用服务器，你只连一个库，你的所有SQL都要到库上执行，一个也少不了。

newkid

顶楼提到的文章我下周翻译, 敬请期待. 确实是非常好的东西!

newkid

你这个属于典型的异步应用,有别于传统的在线事务,但我也可以用存储过程提交一个JOB,或用AQ发送一个任务给调度者,由它再去调用其它库的存储过程.
不用存储过程,也是要做这些事,看不到有什么优势.你总不会做10W个INSERT吧?

dayspring_chen

呵呵，真理多走一步就是谬误。业务场景永远是最重要的。我的用法其实跟你的反驳并无本质区别。
----------------------------------------------------------------------------------------------------------
表函数主要是一种简化调用的方式：在某些返回结果集确定，中间需要根据规则改变计算关系的情况下用表函数。
这些表函数一定是直接被最终应用直接调用的，一定会避免嵌套调用的混乱局面。
例如我们的通用即席查询：动态sql的骨架是固定的，但其中部分语句是根据规则拼接而成（包括用聚合规则、集合操作），再以中间表、嵌套sql、标题子查询进行串联。这种是无法以bind的方式简单搞定的。
我们是采用global temporary table，将根据规则生成的结果放入中间表（中间表可能有多个），再以表函数形式进行输出。
事物处理是显示的commit和rollback，不会搞到动态sql里去。
---------------------------------------------------------------------------------------------------------
当前主要是用在一些BI类别的系统中，最近也想进行另外一种实践方式用于交易
即把元数据与执行数据库（目标库）分开，元数据里存储规则，在中间件里根据请求格式去读取规则，再根据目标库的类型（oracle、DB2、SYBASE、MYSQL、LDAP）等去形成持久化语句，期间实现数据检查、业务规则检查、日志、出错处理。这样元数据就跟配置文件类似，但能获得一些配置文件没有的优势。
总而言之，oracle到一定地步，sql与存储过程并无本质之区别。重要的是团队能力也业务场景。
---------------------------------------------------------------------------------------------------------

我很不赞同你的一些做法。
2.为了更方便不同客户端调用，可以用表函数作为访问方式（以自治事务进行DML，用表函数返回状态信息）
为什么用表函数就更方便？
你全都是用表函数返回嵌套表，然后用SELECT...FROM TABLE(...)? 这样有什么好处？
如果要返回结果集，可以打开一个REF CURSOR并返回。
自治事务要特别小心。基本上，除了写日志，其他的应用都是错的。你这样做，将不得不把COMMIT放到你的函数里。这是不提倡的，COMMIT最好交给客户端去处理。另外你的函数将很难模块化，因为每个都是不同的事务！比如函数A调用函数B, A和B应该在同一个事务里，你如果在A,B里用了自治事务，全乱套了。

3.存储过程里面用动态sql.....
动态SQL是应该尽力避免的。它的应用范围及其有限，一般是为了组合灵活的查询条件。如果你把事务处理也用动态SQL实现，一定有问题。
动态SQL程序很难读，很难维护，无法在编译的时候发现语法问题；因为多隔了一层，效率也要打个折扣。
你可以把业务逻辑中一些灵活的部分抽出来进行参数化，也就是通过配置的方式来进行控制，但是“执行动作”应该都是静态代码，它只是根据不同的配置走不同的分支。
如果你的需求决定了有很多雷同的代码，那么你可以利用你的元数据来生成静态代码，这样节省了人工，但是最终编译、执行的代码还是静态的。
4.通过维护元数据表来进行....
如果元数据表是我上述提到的配置表，这样的做法是可取的。

yzsind

我在文档《面向程序员的数据库访问性能优化法则》http://blog.csdn.net/yzsind/archive/2010/12/06/6059209.aspx
里写了存储过程性能的优点，主要是减少网络交互。
以下存储过程存在问题的详细描述：
a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。
当你的系统需要考虑适应各种数据库的，存储过程会是一个很大的瓶颈，比如SQLSERVER,ORACLE,MYSQL,DB2等等，每种数据库存储过程的语法不同，MYSQL早期流行版本甚至不支持存储过程，所以要实现存储过程的业务逻辑可移植性很难，如果性能不是指数级的增加都不应该考虑用存储过程。要能深入掌握各种数据存储过程，公司需要具备相应的人才。因此各种数据库存储过程的开发调试成本、测试成本，维护成本远大于这点性能提高带来的收益。采用常用标准语法或者采用HSQL之类的作法，逻辑放在应用程序里移植性会好很多。
如果你的数据库是固定的，那可以忽略这点，如很多定制型信息化系统数据库平台都是固定的。

b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。
这里说的学习成本高是一种相对开发人员的说法，一般程序员在学校里更多掌握的是常用开发语言，很少有人掌握PL/SQL，所以当你大量采用存储过程时，招来的人都需要培训PL/SQL。
我以前的一个团队有6个人，4个人都熟悉PL/SQL，C/S应用，因此很多业务逻辑都采用了存储过程，开发速度也不错，但后来换成B/S架构，系统也更复杂了，有20个人都不会PL/SQL，在一次系统重构时将一些业务逻辑的存储过程全换了，因为这些逻辑性能不是问题，取消存储过程后让普通程序员调试更简单，代码管理也更简单。
存储过程的语法、事务控制、调试方法这些与普通开发语言不同，当大量程序员不熟悉存储过程时架构师也不愿意把原本简单的架构搞得更复杂。

c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。
业务逻辑集中管理会更易于维护与调试，你很难做到业务逻辑都放在存储过程里，比如关于一些客户输入数当据的简单校验，会话数据的校验，应用服务器缓存数据的校验。

d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。
这个不太想举例，因为环境不一样，每系统的需求也不一样，如AES高级加密算法，也许这些在应用程序中已经有好的实现，那就不需要在存储过程中再搞一个了。

e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
select sum(object_id) c1 from all_objects;
select sum(power(object_id, 1.1)) c1 from all_objects;
可以试试这两条SQL的性能。power带小数的运算性能不好，如果在数据库中有大量这样的运算，CPU很容易成为瓶颈，而应用服务器可以更好的扩展，而且更善于做CPU运算的优化，所以这类会让CPU成为瓶颈的运算不宜放在数据库端处理。当然，这种需求不常见。

f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
这个问题只是针对高并发应用的系统，如果你的系统并发很低，每秒只有几次或几十次调用，可以完全不用考虑这个问题。

个人观点：普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。存储过程可以快速解决问题，但是移植性、维护性、扩展性不好，它有时会约束软件的架构，约速程序员的思维，在你的系统没有性能问题时不建议用存储过程。如果你要完成的功能只是一次或有限次的工作，如数据订正、数据迁移等等，存储过程也可以拿上用场。
如果你的系统很小，并且有50%的开发人员熟练掌握PL/SQL，人员结构稳定，那存储过程可以减少很多代码量，并且性能不错。当系统变复杂了，开发人员多了，存储过程的弊端就会呈现，这时你需要痛下决心了。

newkid

原帖由 dayspring_chen 于 2011-2-14 13:22 发表
呵呵，真理多走一步就是谬误。业务场景永远是最重要的。我的用法其实跟你的反驳并无本质区别。
----------------------------------------------------------------------------------------------------------
表函数主要是一种简化调用的方式：在某些返回结果集确定，中间需要根据规则改变计算关系的情况下用表函数。
这些表函数一定是直接被最终应用直接调用的，一定会避免嵌套调用的混乱局面。
例如我们的通用即席查询：动态sql的骨架是固定的，但其中部分语句是根据规则拼接而成（包括用聚合规则、集合操作），再以中间表、嵌套sql、标题子查询进行串联。这种是无法以bind的方式简单搞定的。
我们是采用global temporary table，将根据规则生成的结果放入中间表（中间表可能有多个），再以表函数形式进行输出。
事物处理是显示的commit和rollback，不会搞到动态sql里去。
---------------------------------------------------------------------------------------------------------
当前主要是用在一些BI类别的系统中，最近也想进行另外一种实践方式用于交易
即把元数据与执行数据库（目标库）分开，元数据里存储规则，在中间件里根据请求格式去读取规则，再根据目标库的类型（oracle、DB2、SYBASE、MYSQL、LDAP）等去形成持久化语句，期间实现数据检查、业务规则检查、日志、出错处理。这样元数据就跟配置文件类似，但能获得一些配置文件没有的优势。
总而言之，oracle到一定地步，sql与存储过程并无本质之区别。重要的是团队能力也业务场景。
---------------------------------------------------------------------------------------------------------

我很不赞同你的一些做法。
2.为了更方便不同客户端调用，可以用表函数作为访问方式（以自治事务进行DML，用表函数返回状态信息）
为什么用表函数就更方便？
你全都是用表函数返回嵌套表，然后用SELECT...FROM TABLE(...)? 这样有什么好处？
如果要返回结果集，可以打开一个REF CURSOR并返回。
自治事务要特别小心。基本上，除了写日志，其他的应用都是错的。你这样做，将不得不把COMMIT放到你的函数里。这是不提倡的，COMMIT最好交给客户端去处理。另外你的函数将很难模块化，因为每个都是不同的事务！比如函数A调用函数B, A和B应该在同一个事务里，你如果在A,B里用了自治事务，全乱套了。

3.存储过程里面用动态sql.....
动态SQL是应该尽力避免的。它的应用范围及其有限，一般是为了组合灵活的查询条件。如果你把事务处理也用动态SQL实现，一定有问题。
动态SQL程序很难读，很难维护，无法在编译的时候发现语法问题；因为多隔了一层，效率也要打个折扣。
你可以把业务逻辑中一些灵活的部分抽出来进行参数化，也就是通过配置的方式来进行控制，但是“执行动作”应该都是静态代码，它只是根据不同的配置走不同的分支。
如果你的需求决定了有很多雷同的代码，那么你可以利用你的元数据来生成静态代码，这样节省了人工，但是最终编译、执行的代码还是静态的。
4.通过维护元数据表来进行....
如果元数据表是我上述提到的配置表，这样的做法是可取的。

你是将结果放到临时表，然后把临时表放到嵌套表，然后函数返回嵌套表？为什么不用返回REF CURSOR游标（或动态游标）的办法？大部分情况下根本不需要用临时表。
你的应用果然就是我说的动态组合查询条件，但是看起来很复杂，要记住这样的程序维护代价是很高的。
曾经有人ASKTOM上问，如果动态查询条件的组合有50种，他是倾向写50个SQL还是写动态SQL? TOM的回答是写50个静态SQL. 你可以不同意他的做法，但是动态SQL的代价可见一斑。

"最近也想进行另外一种实践方式用于交易...."
你这种实践方法已经是放弃了存储过程，你实际上想要实现的是中间层的RULE ENGINE，和存储过程区别大了去。
我的理念：业务规则就是代码，把代码作为元数据那是ORACLE干的事，你在应用层想要重新做一遍，不仅毫无必要，而且效率低下，难以维护。