MySQL 中存储过程的使用

    关于 MySQL 的存储过程，我所了解的一些有规模的公司，对于使用存储过程实现业务逻辑都有严格的限制。我这里搜罗了一些资料，结合自身的经验，总结一些自己关于 MySQL 中存储过程使用的认识。由于水平有限，如有纰漏错误，还请多拍砖。

 

    存储过程的优点主要包括以下几点：

 

1. 性能提高。相对于不使用存储过程来说的，因为存储过程在创建的时候，数据库已经对其进行了一次解析和优化，而后每次调用都不会再次编译，这相对于传统的SQL语句中每次调用都需要编译的情况来说，性能有所提高，存储过程经过编译之后会比单独一条一条执行要快。而存储过程一旦执行，在内存中就会保留一份，这样下次再执行同样的存储过程时，可以从内存中直接中读取。

2. 重用性强。存储过程使用名字即可执行，也就是传说中的“一次编写，随便调用”。并且是透明的，因为保存在数据库里所以对任何应用来说都可以使用。新的应用只需要调用相应的存储过程就可以得到相应的数据服务。这样不仅提高了重用性，还减少了出错的几率，也会加快开发速度。同时不依赖某种宿主语言，如果用多种语言开发，某些通用代码不用重复。

3. 减少网络流量。这一点对于小数据量的时候体现的并不明显。存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端，因此减少了应用服务器同数据库服务器的通信频率。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能就会比一条一条的调用SQL语句有较大的提升。

4. 安全性提高。存储过程是通过向用户授予权限(而不是基于表)，它们可以提供对特定数据的访问，而且参数化的存储过程可以防止SQL注入攻击，在一定程度上保证了安全性。DBA可以对那些没有权限访问数据库中的表的应用，赋予存储过程的权限来获得数据服务，这个时候这些存储过程好像我们编程里面的“接口”。对于安全性要求很高的系统，例如银行，基本上常用的操作都是通过存储过程或者函数来进行的，这样完全对应用”隐藏“了表。

5. 灵活性增强。由于存储过程可以使用流程控制语句来编写，使得它有着很强的灵活性，可以根据实际情况来执行不同的SQL语句，而不是只能单纯的简单的执行命令。而且存储过程还可以修改其逻辑而其他部分不用改变，也就是说，我们的表的结构改变了，我们可能只需要修改相应的存储过程即可，我们的Java或者PHP等程序不需要改变。

6. 减少工作量。当业务复杂的时候，如果我们不使用存储过程，那么就会需要先从数据库中取出来数据，然后经过计算，再放入到数据库中。这些都是有开销的，其中包括我们的Java或者PHP等程序连接数据库获取结果集等若干操作。如果我们使用了存储过程，那么直接在MySQL中就能完成修改。并且可以分布式工作，应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

7. 可维护性高。更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。但这一点便利很多时候被滥用了。比如：直接在正式服务器上修改存储过程，而没有经过完整的测试，后果可能会非常严重。

 

    好，上面说了存储过程的优点，再来看看存储过程的缺点：

 

1. 编写和调试麻烦。MySQL本身并没有很像样的IDE来开发存储过程，我们很多时候还是需要一行一行的老老实实手写，这样就会比较麻烦。而存储过程的调试也是一个问题，没有很像样的调试工具，很多时候是用print来调试，而且在使用slowlog对MySQL进行性能分析时，只能记录整个存储过程的执行情况，却无法记录存储过程内具体语句的执行情况，对于调试长达数百行SQL的存储过程简直是蛋疼。

2. 性能优势不明显。在运行速度上，对于大多数的SQL语句来说，编译SQL的时间开销并不是很大，但是执行存储过程还需要检查权限等一些其他开销，所以，对于很简单的SQL，存储过程并没有很大的性能优势。而且，数据库毕竟主要用来做数据存取的，并不适合进行复杂的业务逻辑操作，承担业务压力会占用大量的系统资源(cpu、memory)。

3. 赘余功能。通常情况下，数据库服务器只向内网中的应用服务器提供服务，而且连接数据库的用户往往是同一个。对于除金融领域外的其他大多数程序来说，安全性方面的需求往往小于性能、功能等其他方面。所以，对于安全性方面的情况看上去很好，实际上优点有些多余。

4. 可移植性差。当我们的程序要更换数据库的时候，它的移植性相对于不适用存储过程要更复杂。而且MySQL的存储过程功能比起Oracle、SQLserver、乃至PostgreSQL 都要较弱一些。

5. 本末倒置。SQL本身是一种结构化查询语言，加上了一些控制（赋值、循环和异常处理等），但不是OO的，本质上还是过程化的，大量采用存储过程进行业务逻辑的开发时，由于不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架，面对复杂的业务逻辑，过程化的处理会很吃力。

6. 拓展性差。像MySQL这样的关系型数据库，出现CPU和IO瓶颈时，极难扩展，但是应用服务器出现CPU和IO瓶颈，特别是采用SNA架构的情况下，理论上可以获得无限的水平扩展能力，只需要加服务器就行了。这个方法比任何方法都见效快，而且往往也是成本最低的。

              

总结：

    对于MySQL来说，通常情况下，除了某些非常依赖数据处理的操作，其他的业务逻辑不应该使用存储过程来实现，而应该由应用层实现。

 

    PS：题外话，并不是每个应用的瓶颈都在数据库端。即便瓶颈在数据库端，具体问题具体分析，也要功能、性能、安全、成本、效率等多方面权衡，才能做出最合适的选择。