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

newkid

我在文档《面向程序员的数据库访问性能优化法则》http://blog.csdn.net/yzsind/archive/2010/12/06/6059209.aspx
里写了存储过程性能的优点，主要是减少网络交互。
以下存储过程存在问题的详细描述：
a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。

我上次已经说得很清楚了，现在抄一段TOM的书：Oracle 9i10g编程艺术
1.3.4 数据库独立性?
至此，你可能想到这一节要讲什么了。我提到了其他的数据库，也谈到各个数据库中会以不同的方
式实现特性。除了一些只读应用外，我的观点是：要构建一个完全数据库独立的应用，而且是高度可扩缩
的应用，是极其困难的。实际上，这几乎不可能，除非你真正了解每个数据库具体如何工作。另外，如果
你清楚每个数据库工作的具体细节，就会知道，数据库独立性可能并不是你真正想要的（这个说法有点
绕！）。
例如，再来看最早提到的资源调度例子（增加FOR UPDATE 子句之前）。假设在另一个数据库上开发这
个应用，这个数据库有着与Oracle 完全不同的锁定/并发模型。我想说的是，如果把应用从一个数据库移
植到另一个数据库，就必须验证它在完全不同的环境下还能正常地工作，而且为此我们要做大幅修改！
假设把这个资源调度应用部署在这样一个数据库上，它采用了阻塞读机制（读会被写阻塞）。现在业
务规则通过一个数据库触发器实现（在INSERT 之后，但在事务提交之前，我们要验证表中对应特定时间片
的记录只有一行，也就是刚插入的记录）。在阻塞读系统中，由于有这种新插入的数据，所以表的插入要串
行完成。第一个人插入他（她）的请求，要在星期五的下午2:00 到下午3:00 预订“房间A”， 然后运行
Begin
or x in (select * from t)
loop
insert into t values (x.username, x.user_id, x.created);
end loop;
end;
insert into t select * from t;
71
/ 849
一个查询查看有没有重叠的预订。下一个人想插入一个重叠的请求，查找重叠情况时，这个请求会被阻塞
（它发现有新插入的数据，但要等待直到这些数据确实可以读取）。在这个采用阻塞读机制的数据库中，
我们的应用显然可以正常工作（不过如果两个人都插入自己的行，然后试图读对方的数据，就有可能得到
一个死锁，这个概念将在第6 章讨论），但不能并发工作，因为我们是一个接一个地检查是否存在重叠的
资源分配。
如果把这个应用移植到Oracle，并简单地认为它也能同样地工作，结果可能让人震惊。由于Oracle
会在行级锁定，并提供了非阻塞读，所以看上去一切都乱七八糟。如前所示，必须使用FOR UPDATE 子句来
完成串行访问。如果没有这个子句，两个用户就可能同时调度同一个资源。如果不了解所用数据库在多用
户环境中如何工作，就会导致这样的直接后果。
将应用从数据库A 移植到数据库B 时，我时常遇到这种问题：应用在数据库A 上原本无懈可击，到了
数据库B 上却不能工作，或者表现得很离奇。看到这种情况，我们的第一个想法往往是，数据库B 是一个
“不好的”数据库。而真正的原因其实是数据库B 的工作方式完全不同。没有哪个数据库是错的或“不好
的”，它们只是有所不同而已。应当了解并理解它们如何工作，这对于处理这些问题有很大的帮助。将应用
从Oracle 移植到SQL Server 时，也会暴露SQL Server 的阻塞读和死锁问题，换句话说，不论从哪个方向
移植都可能存在问题。
................

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

实际上，如果已经掌握了SQL和其他开发语言，学习PLSQL的成本并不高，几天之内就可以搞定。如果不习惯SQL的思维方式，一切都想用嵌套循环来搞定，那么就有一个适应的过程。但是学习SQL的付出是值得的，如果你依靠SQL生成器，那就写不出好的应用。
学习PLSQL的成本也是值得的。TOM有句话：applications come, applications go, data stays forever. 而PLSQL是最靠近数据的一层，从来没有一种语言能够这样和SQL无缝结合，如果你采用PLSQL开发，你的代码就有可能stays forever. 而那些华丽的包装器，往往只是各领风骚若干年，今天的新潮明天就成老土，你可能不得不反复投资才跟得上。PLSQL看似粗陋，实则强大，具有顽强的生命力。
关于这个话题我以前有过一个超长贴：
http://www.itpub.net/thread-1088197-1-1.html

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成为瓶颈的运算不宜放在数据库端处理。当然，这种需求不常见。

据我的经验，密集型的运算都是很简单的四则运算而已，你这类需求及其罕见，就是有也不会密集出现，因此我放到数据库实现也不用担心CPU问题。

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

等会我就贴出顶楼文章的翻译，这个问题ORACLE已经完美解决了。
难道把代码写到应用服务器，你就不用编译？

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

开发人员多了，其他语言就不存在管理问题？

newkid

顶楼提及的TOM写的文章翻译已经登出：
http://www.itpub.net/thread-1396183-1-1.html

qingyun

newkid兄，真知灼见，我也和您观点相同；

不过现在的潮流好像确实是把业务在中间层的java或C#用各种框架动态生成一坨坨SQL和数据库交互；


我做项目，基本是滥用PL/SQL ;所有业务都用存储过程写，尽量避免游标，滥用各种oracle特有的SQL，滥用分析函数；
如果不用存储过程，我都不知道高并发情况下，如何防止住各个地方隐藏的并发脏数据漏洞；

其他外围的功能，我全部用PL/SQL ;
比如：导出excel,xml，调用webService ,tcp/ip通讯；调用dll等等，我全部用oracle的存储过程实现，目的只有一个：
就是不受前台语言限制，方便移植；前台N种语言任意更换，都很easy，因为前台只是一个shell.

[ 本帖最后由 qingyun 于 2011-2-15 02:58 编辑 ]

newkid

原帖由 qingyun 于 2011-2-15 02:55 发表
newkid兄，真知灼见，我也和您观点相同；

不过现在的潮流好像确实是把业务在中间层的java或C#用各种框架动态生成一坨坨SQL和数据库交互；


我做项目，基本是滥用PL/SQL ;所有业务都用存储过程写，尽量避免游标，滥用各种oracle特有的SQL，滥用分析函数；
如果不用存储过程，我都不知道高并发情况下，如何防止住各个地方隐藏的并发脏数据漏洞；

其他外围的功能，我全部用PL/SQL ;
比如：导出excel,xml，调用webService ,tcp/ip通讯；调用dll等等，我全部用oracle的存储过程实现，目的只有一个：
就是不受前台语言限制，方便移植；前台N种语言任意更换，都很easy，因为前台只是一个shell.

我喜欢你这“一坨坨”的说法！确实有时候臭不可闻，哈哈！
潮流不说明什么，大部分跟潮流的人都是稀里糊涂的。

newkid

关于AES加密，随便GOOGLE了一下：

http://download.oracle.com/docs/ ... b14258/d_crypto.htm

DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).
......

虽然此类操作比较昂贵，但如果非密集型调用，我还是更愿意放到数据库里来实现。

newkid

性能如何？马上做个测试：

CREATE OR REPLACE FUNCTION f_aes(p_str IN VARCHAR2)
RETURN RAW
AS
BEGIN
   RETURN DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (p_str,'AL32UTF8'),DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5,HEXTORAW('1A32602CA9DC740A53F3E9B7151BCA5BC040835F531D3DDE85869C05B3447FF0'));
END f_aes;
/


CREATE TABLE TEST_AES (OBJECT_NAME VARCHAR2(30),ENC_NAME RAW(32));

INSERT INTO TEST_AES
SELECT OBJECT_NAME,f_aes(OBJECT_NAME)
   FROM ALL_OBJECTS;

55868 rows created.

Elapsed: 00:00:10.44

偶尔用用还是没关系的。你可别说淘宝的每笔交易都要用AES加密。

〇〇

nice 一坨坨

atgc

想要用"标准SQL" 去适应不同数据库,我觉得是很难的事情,基本不太可能吧
比如分页,不同的数据库不同的语法,没有标准

yzsind

原帖由 atgc 于 2011-2-15 08:41 发表
想要用"标准SQL" 去适应不同数据库,我觉得是很难的事情,基本不太可能吧
比如分页,不同的数据库不同的语法,没有标准

从DBA的角度看确实很麻烦，但是在架构师眼里不是问题，因为很多系统的底层框架就支持了不同数据库的分页，程序员只要给出SQL,底层框架自动会转换成不同数据库的分页语法。实际上很多实现跨平台数据库的解决方案都需要有底层框架的支持，程序员只要按规范做就OK了。
很多系统要求跨数据库平台都是出于客户需求。如，你一个软件开发出来，软件卖5万元一套，数据库支持的SQLSERVER（2万元），当有一天你的新客户说我不想采购SQLSERVER，因为我已经有了ORACLE数据库，而且我有ORACLE专业DBA维护系统，如果你的软件不支持ORACLE那我就选择其它软件。OK，软件开发商就会研究如何让他的软件适应于ORACLE。当客户越来越多了，为了打开市场，他可能又开始支持DB2,MYSQL之类的数据库，在这样的发展过程中架构师首先想到的是减少非标准SQL，并且通过底层架构去屏蔽不同数据库的语法，最终实现了跨数据库支持。
存储过程由于语法差异太大，而且是放在数据库系统里，所以从架构上很难支持跨平台。

dayspring_chen

应用是复杂的。
我项目初始，大致面临这样的应用：
企业所得税申报表查询：业务分为核定征收（B类）、查账征收（A类），时间线上分为季度预缴、年终汇算清缴，A类还细分为金融、小会计、其他等；每类申报表约有十几张，每张申报表有数百到上千项不等；申报表两三年会换一次版,有些表表内有主从。
客户的要求很简单：所有的数据项都能做条件、所有的数据项都能做结果，能查清单，能做汇总，能导出xls，能打开带格式的套表。总之一句话“想查什么就查什么，想怎么查就怎么查”。
不仅如此，还要关联上登记信息、税费信息、申报信息、入库信息、稽查信息。
这个绝对是50个静态SQL搞不定的啦。组合起来估计10万百万都未必挡得住。
经过验证，9i10g后的动态sql还是可以的。尤其是11g，有了更大的增强。（execute immediate 不受varchar2的限制，跟dbms_sql一样了）
当然，大量用表函数的是另一个项目。不过总体上在存储过程里，不用动态sql简直是没法过活。呵呵，我在当年oracle 7的时候就用dbms_sql做动态sql的项目了。当时做了一个通用接口程序用于交易系统，也没碰到效率问题。
至于维护问题，有了稳定的熟悉数据库编程的团队，比一群只会struts的java散兵游勇让人放心多了。会java的人很多，真正明白机理，能够趋利避害的人就太少了。
看着一坨一坨的java里的业务逻辑，才让人抓狂呢。
----------------------------------------------------------------------------------------------------------------------------
至于业务逻辑就应是代码的说法，我觉得也有些绝对。各种框架里的配置文件，不就是各个框架抽离出的业务逻辑吗。
如果抽象的好的话，完全可以把业务逻辑抽离成元数据，再用代码解析请求，与元数据逻辑进行结合，完成业务处理。可维护性不见得比某些繁琐的框架更差吧。至于叫业务规则引擎，也未尝不可，只是看规则的范畴和抽象的粒度而已。我是考虑一直抽象到可形成持久化语句的。
---------------------------------------------------------------------------------------------------------------------------

[ 本帖最后由 dayspring_chen 于 2011-2-15 14:04 编辑 ]