ITPUB论坛-中国最专业的IT技术社区

   
 注册
热搜:
查看: 14696|回复: 31

[精华] 存储过程优势讨论

[复制链接]
论坛徽章:
14
紫蛋头
日期:2012-03-13 16:37:182013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2012-12-06 19:27:46ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42灰彻蛋
日期:2012-02-06 14:20:122012新春纪念徽章
日期:2012-01-04 11:57:56茶鸡蛋
日期:2011-12-20 15:00:13ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29灰彻蛋
日期:2011-12-26 14:20:13
跳转到指定楼层
1#
发表于 2012-3-5 11:47 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
  procedure对大家来说并不陌生,如果要说服未接触过的开发同事应用一些过程到项目中,应该如何去说服呢,我最近遇到这样的问题,自己做了一些过程的优势的总结,性能上做了小测试,大家有什么妙招?

概念:存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段
重复调用
    具有特定功能的存储过程在被创建以后可以在程序中被多次调用,而且对存储过程进行修改对应用程序源代码毫无影响,既减少了开发人员的工作量同时提高了程序的可移植性,对存储过程中的sql的语句的优化不会带来代码的更改。
执行速度
  存储过程是预编译的在首次运行一个存储过程时查询优化器对其进行分析优化并给出最终被存在系统表中的执行计划。而批处理的Transaction-SQL 语句在每次运行时都要进行编译和优化,应用程序中的sql语句数量很大,系统将消耗大量的资源在sql解析上面,而运用过程封装部分sql能有效缓解系统压力,并提高查询响应速度。(详见后续测试)
网络流量
  一个事务需要用到10sql,假设每个sql大小为1kb,单从流量上看,使用过程将节省9KB。同时还有交互过程中的网络流量,例如在一个事务性质的sql组合中,每条sql独立提交并获取结果数据做逻辑处理后传值(一个值,一个字符串,甚至一个表)给下一条sql。如果使用存储过程,sql之间的数据交互将在数据库内部进行,减少了大量的网络交互,同时减少的是整个事务的完成时间。
4安全
   存储过程的运行时依据其创建者的权限。例如用户U不具备A表的任何权限,又需要为其提供部分的数据,可以赋予其调用过程P的权限,U通过存储过来访问数据,在过程中进行参数的审核,一定程度有效防止sql注入攻击,并可以控制其能够得到的数据内容。
5.事务
         存储过程可结合事务执行。多sql在执行中mysql宕机或网络中断等原因,事务的回滚只能通过程序本身完成,由于程序大部分时间无法确定最后一次sql是否提交成功,回滚的可靠性低,将事务封装在过程中,数据库会有效地进行整个事务的提供和回滚操作。
测试
测试环境:
  mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline6.1  2G
初始化:测试的3个表分别初始化50w条数据。
2.1 执行性能
业务场景:(完成selectupdatedeleteinsert组合)
给出一个id,更新t1中此id对应数据,删除t2此id数据。向t2插入t1此id数据,返回t3表的数据。
Sqls
sql0='flush query cache'    刷新查询缓存,保证测试数据有效
       sql1='UPDATE t1 SET a_id=110 AND a_type=110 AND last_modified=NOW()WHERE id = %s'
       sql2='DELETE FROM t2 WHERE id=%s'
       sql3='INSERT INTO t2(guid,t_group,from_a_id,to_a_id) SELECT a_id,a_type,a_status,1FROM t1 WHERE id=%s'
       sql4="SELECT MIN(t3.t_type),MAX(sys_id),COUNT(DISTINCT t2.`guid`)FROM t3 JOIN t2 ON t3.id=t2.id"
       sql5='SELECT a.a_status,b.guid FROM t1 a,t2 b WHERE a.`a_id`=b.`guid`'
数据及分析:
Pro:存储过程    sql:多次sql       时间单位:s
序号
类别
开始时间
结束时间
时间消耗
随机id
1
pro
1330742912.74
1330742918.3
5.56233501434
136369
sql
1330742929.56
1330742938.79
9.22886991501
53571   
2
pro
1330743059.51
1330743065.2
5.69001603127
115445
sql
1330743046.83
1330743056.14
9.30604100227
143042
3
Pro
1330744056.55
1330744062.16
5.61571311951
68169
Sql
1330743738.2
1330743747.48
9.28886985779
144629
4
Pro
1330744123.48
1330744129.07
5.59134888649
69447
Sql
1330744157.11
1330744166.36
9.24880003929
14114
5
Pro
1330744196.59
1330744202.26
5.67109799385
55165
sql
1330744211.09
1330744220.46
9.36547088623
80620
平均值:sql 9.28761s   pro5.626102s
分析:
         存储过程中的sql在编译时mysql会保存优化后的执行计划,所以调用时不需要进行sql解析和优化。
多次sql执行,需要对每一个sql语句进行解析和优化,如果sql语句量增加,系统将消耗大量资源在解析和优化上面,降低数据库整体性能。

论坛徽章:
14
紫蛋头
日期:2012-03-13 16:37:182013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2012-12-06 19:27:46ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42灰彻蛋
日期:2012-02-06 14:20:122012新春纪念徽章
日期:2012-01-04 11:57:56茶鸡蛋
日期:2011-12-20 15:00:13ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29灰彻蛋
日期:2011-12-26 14:20:13
2#
 楼主| 发表于 2012-3-5 12:30 | 只看该作者
感谢金兄,貌似是第一次精华,看来以后得多分享一些总结上来

使用道具 举报

回复
论坛徽章:
3
ITPUB 11周年纪念徽章
日期:2012-10-09 18:08:152014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02
3#
发表于 2012-3-6 14:24 | 只看该作者
数据库是相当昂贵的投资,能让他多干点就多干点活。存储过程的优势还是非常强的(在没有跨平台的要求下)。
貌似我们公司那帮java的就从来不知道存储过程为何物,用java的框架统一生成,到处是拼接SQL.

使用道具 举报

回复
论坛徽章:
14
紫蛋头
日期:2012-03-13 16:37:182013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2012-12-06 19:27:46ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42灰彻蛋
日期:2012-02-06 14:20:122012新春纪念徽章
日期:2012-01-04 11:57:56茶鸡蛋
日期:2011-12-20 15:00:13ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29灰彻蛋
日期:2011-12-26 14:20:13
4#
 楼主| 发表于 2012-3-6 14:51 | 只看该作者
grass_root 发表于 2012-3-6 14:24
数据库是相当昂贵的投资,能让他多干点就多干点活。存储过程的优势还是非常强的(在没有跨平台的要求下)。 ...

我最近遇到这样的情况,所以准备做整理一些有力的说明方式,大家提下自己看法。。。

使用道具 举报

回复
论坛徽章:
28
2010数据库技术大会纪念徽章
日期:2010-05-13 09:34:232012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25版主2段
日期:2012-07-05 02:21:032013年新春福章
日期:2013-02-25 14:51:24ITPUB社区12周年站庆徽章
日期:2013-08-12 09:34:36马上有车
日期:2014-02-19 11:55:14
5#
发表于 2012-3-6 15:09 | 只看该作者
数据库是昂贵的投资是oralce的说法,mysql是免费的

使用道具 举报

回复
论坛徽章:
2
奥运会纪念徽章:篮球
日期:2012-11-05 16:49:012013年新春福章
日期:2013-02-25 14:51:24
6#
发表于 2012-3-6 17:23 | 只看该作者
数据库捏人出来  我要学习下啊

使用道具 举报

回复
论坛徽章:
1
2013年新春福章
日期:2013-02-25 14:51:24
7#
发表于 2012-3-14 18:39 | 只看该作者
存储过程的好处是高效节能,
但问题的关键是如何划分存储过程和应用程序的边界。按照Tom的说法,能在数据库做的事情,就不要放到数据库外面。
需要交互的,或者从非数据库的源头获取程序输入或控制的,就只能在client端做。

使用道具 举报

回复
论坛徽章:
14
紫蛋头
日期:2012-03-13 16:37:182013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2012-12-06 19:27:46ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42灰彻蛋
日期:2012-02-06 14:20:122012新春纪念徽章
日期:2012-01-04 11:57:56茶鸡蛋
日期:2011-12-20 15:00:13ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29灰彻蛋
日期:2011-12-26 14:20:13
8#
 楼主| 发表于 2012-3-14 21:17 | 只看该作者
需要些更多的测试数据才更有说服力,哪位兄台有时间分享下?

使用道具 举报

回复
论坛徽章:
3
ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26迷宫蛋
日期:2012-05-29 17:44:57ITPUB 11周年纪念徽章
日期:2012-10-09 18:08:15
9#
发表于 2012-6-12 13:31 | 只看该作者
作为编程语言,存储过程当然不如java,c之类的效率高,办法多;但作为数据库的应用,却要比它们有效率。
在当前海量数据的环境下,关系型数据库本身都需要NoSQL的补充,存储过程的使用也就受到了约束。
对于相当多数的程序员来讲,他们更熟悉语言框架,数据库更多地只作为storage。这也影响到了存储过程的使用。

所以,如果你的数据量还没有大到RDBMS处理不了,那倒是可以考虑使用存储过程。

使用道具 举报

回复
论坛徽章:
0
10#
发表于 2012-6-18 17:00 | 只看该作者
存储过程作为一种过时的语言,只能存在于较少的业务场景了,比如规范的数据仓库数据清洗、标准、简单的多数据库操作等。在良好的业务系统中应该尽量抛弃存储过程和触发器之类的东西。
1、从设计角度看,逻辑封装很重要,不是存储过程那一点封装,而是整个业务逻辑。如果把业务逻辑分散在程序代码和存储过程两部分,实际上是业务碎片化,不利于表述业务逻辑,造成后期阅读维护的困难。
2、存储过程自身并不是一种结构化良好的语言,对于习惯于面向对象编程的人而言,简直就是乱麻一堆。代码可读性在工程上很重要。
3、很多真正提高效率的终极办法是使用缓存而不是在数据库中运算,靠数据库预编译或减少网络流量那点优化就可以,那说明性能要求原本不高
4、如果有数据库解耦的需求,就更不应该使用存储过程
好的架构师多是从程序员发展来的,有几个是DBA发展来的?有几个好的架构师常推荐存储过程呢?所以DBA兄弟们最好还是听程序员的话,优化sql即可。
如果真想从架构思路上去优化,先应该全盘考虑系统架构问题

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表