mysql 到底用不用store procedure?

乱码

今天跟我们那个SB lead讨论问题，说起data access的技术，他是php developer,一般用mysql,但他从来不用store procedure,全都是dynamic sql从app layer传到database,而且这么多年，他从来也没见到过任何一个php developer用store procedure.

我说，在.net/sql server这边，除非是orm tools，否则直接用dynamic sql是很坏的practice.

他将信将疑的走了．

google了一下，一篇文章说mysql store proc pro/con的，觉得它的disadvantage写的比较好玩，大家乐一下吧．

http://www.mysqltutorial.org/int ... red-procedures.aspx

bulaohu

文章作者好像默认了MySQL总是在crappy hardware或者vm上面run，连个stored procedure导致的CPU开销都是个了不得的事情

kr2000

好像mysql5才有store proc
我基本上没用过，你这么一说，是该看看
我的程序里大把dynamic sql(paopaobing(14))

我感觉.net提供的东西比其他语言多多了，junior到medium的路比较好走

IsDonIsGood

都用dynamic sql他也不怕sql injection

yuba

感觉上Rails风格的CRUD可以直接由数据库提供，数据库根据domain class的定义生成表，提供增删改列的界面

更为复杂的逻辑用store procedure完成，B/S架构进化为B/D架构

dcc82

的确是没几个php developer会用mysql的store procedure, 一般没特殊情况也没必要去用, 在各大开源的php项目中还真没人在用store procedure
直接sql比较直观, 擅长sql的人一句命令可胜过写一大堆的逻辑代码.不过我个人觉得在当代php development环境下还这么做倒反而挺落伍的,起码维护起来就是个问题, 接手的人也很难看懂,现在php基本上全都是orm了,不是active record就是table data gateway

乱码

看了大家的回复，看来我们公司这个SB的确没撒谎.

.net这边也不都是用store proc，去年我见到一个team lead，他号称他的database一个sp都没有，全都用orm tool来query，还很激动地对我说：我们的database很干净。弄得我很郁闷

我问他：你们query的performance tunning怎么搞？

他说：用profiler抓，然后放在management studio看execution plan.

我接着问：很好，但如果不好怎么改？

这次轮到他郁闷了

亲亲宝贝

Performance 和 maintenance 要权衡一下. 使用SP确实有维护上和移植的问题. 另外和数据量也有关, 百万条数据以下, 区别不大. MySQL SP支持也是在5.0以后才加上去的.

Dan.and.Andy

dynamic sql 不是什么很坏的practice, 取决于你是开发网站还是产品。不一定用 stored proc 就是好的，取决于负载的均衡和业务逻辑的分配。

dynamic sql  便于部署，同时便于移植，data providers可以指定任何数据库，可以是sql server, Access, firebird, Oracle,  my sql...；至于sql injection, 对于任何的反向工程不是什么可以担心的事，除非是实在实在没有工作量而愿意手工地去做。EF的自动生成程序不也是dynamic sql吗？

乱码

是否用store proc关键是如何来看定义database和store procedure。performance倒是在其次。

有的人把database就看成一个data store,可以随意操作，本着这种思路，dynamic sql没问题. 有的人把database不仅仅看成data store，还应该有自己的api提供给他人来有限制的访问，这种api就是store procedure，它相当程度上作数据的隐藏和抽象。

至与performance，
1. dynamic sql每次过来都要做syntax parsing
2. 它没有cache中的execution plan可以利用。
3. 安全上用的不好容易sql inject,他的执行权限也不好控制(用sp的user，只需要grant一个 execute sp的权限就好了，不需要给dba的权限）

[ 本帖最后由 乱码 于 2011-1-12 22:39 编辑 ]

zxinfhs

我觉得sp才是一个不好的practice，至少在j2ee的开发中很少采用，哪怕是不用ormapping，直接写sql
问题的关键是你的业务逻辑是用object完成的，还是用table完成的，这是两个不同的思路
另外，说道performance，我也不认为sp有很大的优势，企业级系统中，往往瓶颈在数据库server上，我们总是尽量用集群上的appserver分担db server的压力
只有个别情况，sp才有优势，曾经写过一个账目稽核程序，用sp跑在oracle上，要跑48小时，为此，每月月初外部访问层关闭，专心跑sp，我们在内部连上系统，发现系统的反应非常慢，sp占用了大量的cpu和io资源

zxinfhs

向外部系统暴露接口，应该用通用程序接口，比如http, web service, rpc, ejb...，数据库sever是很敏感的，应该藏在深闺中，不好直接暴露给外人吧，哪怕是设了一定的权限

coolioo

写SP有一个问题，就是如何进行版本控制？？乱码兄能谈谈经验吗？

[ 本帖最后由 coolioo 于 2011-1-12 23:00 编辑 ]

乱码

原帖由 zxinfhs 于 2011-1-12 22:48 发表
我觉得sp才是一个不好的practice，至少在j2ee的开发中很少采用，哪怕是不用ormapping，直接写sql
问题的关键是你的业务逻辑是用object完成的，还是用table完成的，这是两个不同的思路
另外，说道performance，我也不认为sp有很大的 ...

1.在j2ee的开发中很少采用sp?-- 我不知道，但在.net/sql server这边很难想象。
2.企业级系统中，往往瓶颈在数据库server上--用多个database instance,1-2个负责write，其余的负责read，时时同步。
3.曾经写过一个账目稽核程序，用sp跑在oracle上，要跑48小时--solution同2.

乱码

原帖由 coolioo 于 2011-1-12 22:54 发表
写SP有一个问题，就是如何进行版本控制？？乱码兄能谈谈经验吗？

我们用source control来控制版本，任何的sp都要进source control的

乱码

原帖由 zxinfhs 于 2011-1-12 22:54 发表
向外部系统暴露接口，应该用通用程序接口，比如http, web service, rpc, ejb...，数据库sever是很敏感的，应该藏在深闺中，不好直接暴露给外人吧，哪怕是设了一定的权限 ...

把app跟database一般要分开，不同层面的东西

Dan.and.Andy

原帖由 乱码 于 2011-1-12 22:35 发表
是否用store proc关键是如何来看定义database和store procedure。performance倒是在其次。

有的人把database就看成一个data store,可以随意操作，本着这种思路，dynamic sql没问题. 有的人把database不仅仅看成data sto ...

database 其实就应该是一个 data store. 我觉得所有的数据库厂商应该都认同这一点，t-sql 或者 pl/sql 仅仅是操作数据用的。.net 的开始现在也是强调数据库的store,弱化database的逻辑。未来的趋势也是orm占主流，随着非关系型数据库的发展，以后数据库就应该是data store，只当做data store就对了.

乱码

至于数据移植，我经历的project，有2次database level的移植，sql server-> oracle,但app level的重写/移植不下20个，而且data的寿命比app寿命长N多倍，这是业界公认的准则。业务逻辑大范围的部署在app layer，只能给将来可能的移植造成不必要的麻烦。

乱码

原帖由 Dan.and.Andy 于 2011-1-12 23:10 发表


database 其实就应该是一个 data store. 我觉得所有的数据库厂商应该都认同这一点，t-sql 或者 pl/sql 仅仅是操作数据用的。.net 的开始现在也是强调数据库的store,弱化database的逻辑。未来的趋势也是orm占主流，随着 ...

如果你做几天dba/db developer可能就会改变看法。orm tool只有部分app dev team当宝，这种team普遍运用sql的能力都比较弱，但接受新事物的能力比较强。

如果比较刺耳你别介意阿，你知道我不是有心的，只是讨论问题

hornsay

我喜欢用stored procedure，第一是可以把许多sql query 放在一个sp中，第二个是sp是sequenced, 不会因为同时有update而引起lock或data version不一样而出错。

另外在MS SQL中，好像auto increased id 只能通过sp来实现，就是一个sp在执行insert后回一个id, 不知道这个说法对不对？

kane2001

我们Team Leader说 SP不好 把程序的部分逻辑放到数据库里那块了
于是我把所有的SQL query都写死在程序里

bulaohu

原帖由 Dan.and.Andy 于 2011-1-12 23:10 发表


database 其实就应该是一个 data store. 我觉得所有的数据库厂商应该都认同这一点，t-sql 或者 pl/sql 仅仅是操作数据用的。.net 的开始现在也是强调数据库的store,弱化database的逻辑。未来的趋势也是orm占主流，随着 ...

Oracle的Larry肯定不同意你

bulaohu

原帖由 kane2001 于 2011-1-12 23:23 发表
我们Team Leader说 SP不好 把程序的部分逻辑放到数据库里那块了
于是我把所有的SQL query都写死在程序里

参看上面乱码说的数据比application寿命长N倍的事实...

乱码

原帖由 hornsay 于 2011-1-12 23:22 发表
外在MS SQL中，好像auto increased id 只能通过sp来实现，就是一个sp在执行insert后回一个id,

这是table id column的定义，它被指定为identity，auto increment =1，跟sp无关

hornsay

原帖由 乱码 于 2011-1-12 23:28 发表


这是table id column的定义，它被指定为identity，auto increment =1，跟sp无关

对，就是这个identity. 通常我们是用sp去拿到它。想请教一下有什么其它方法去得到它？

bulaohu

原帖由 hornsay 于 2011-1-12 23:33 发表


对，就是这个identity. 通常我们是用sp去拿到它。想请教一下有什么其它方法去得到它？

我糊涂了，这个不就是primary key吗？如果要读取的话，跟其他的field没有区别，只是不能写入，写入是系统自己handle的，来保证唯一性

乱码

原帖由 hornsay 于 2011-1-12 23:33 发表


对，就是这个identity. 通常我们是用sp去拿到它。想请教一下有什么其它方法去得到它？

没明白

用max(id)? or scope_identity?

hornsay

原帖由 bulaohu 于 2011-1-12 23:37 发表


我糊涂了，这个不就是primary key吗？如果要读取的话，跟其他的field没有区别，只是不能写入，写入是系统自己handle的，来保证唯一性

对啊，如果我做一个insert query, 我没法得到这个primary key. 我感觉应该有什么机制会自动得到，但找不到这个方法。

北风

我认为要用stored procs不光是performance的方面，还有security方面
database趋向纯粹的datastore其实是说非关系数据库的，而非关系数据库一般都是用在特别需要perfomance或者IO的地方，普通应用还是关系数据库主流，sp还是很重要的

kane2001

原帖由 bulaohu 于 2011-1-12 23:27 发表


参看上面乱码说的数据比application寿命长N倍的事实...

客户嫌我们的程序search太慢 该死的NHibernate 生成的 query 优化了半天还是慢的像蜗牛 于是我自己动手写了个5分钟搞定