数据蒋堂 | 存储过程的利与弊

存储过程是数据库领域中应用非常广泛的技术，关于它的利弊讨论由来已久，我们这里针对存储过程的两个公认度较高的优点进行剖析，从而更清楚存储过程的潜在风险及应用场景。

界面与逻辑分离是现代应用开发的一个基本准则。相对于后台数据处理逻辑，界面会有更多样性的环境，如PC、手机等，而且业务稳定性也不强，经常会改。如果能把两者分离，开发和维护界面时绑着数据处理逻辑一起改，成本就低很多。

支持存储过程的观点认为，使用存储过程能实现界面与逻辑分离。存储过程在后台数据库中运算，只要向前端提供数据，而不必关心界面的形式和异动。把所有的数据处理逻辑都写成存储过程，还有利于统一数据的出入口，易于实现数据权限管控。

但是，仔细想想会发现，实现界面与逻辑分离并不是存储过程的专利。只要做一个数据访问层，所有数据的进出都通过这个访问层，也会有同样效果，事实上也确实有些应用是这么做的，但并不普遍。这是什么原因呢？这样的数据访问层和采用存储过程有什么区别呢？

差别在于开发复杂度上。

数据处理逻辑会经常涉及到批量结构化数据的处理，而数据库之外的程序设计语言在这方面能力都很弱，用Java写个数据求和都要很多行，更不要说过滤、分组之类的运算了。而存储过程的基本部件是SQL，这方面支持得很好，虽然存储过程也有开发调试困难的毛病，但大多数情况下用于编写复杂的数据处理逻辑还是要比高级语言更容易。

换句话说，存储过程确实利于界面与逻辑分离，不过 存储过程实现后台数据逻辑的优势是SQL的集合运算能力支撑的。 它主要来自于开发便捷，而不是应用结构。

有些场合无法利用存储过程的计算能力，就只能实现库外的数据访问层了。比如数据来源涉及多数据库或非数据库的情况。

界面与逻辑分离的准则还有两面性，它并没有明确定义什么程序算是界面，更没有说界面环节就不再有数据计算任务。

一个典型的任务就是报表。报表要在界面中呈现，其业务稳定性也较弱，经常增改，很显然属于界面环节的事务。但是，报表经常却有复杂的数据源计算过程，如果把这部分计算也作为后台逻辑强行放进存储过程中，不仅不会获得界面与逻辑分离的好处，反而会带来巨大的麻烦，这与网上许多推荐在复杂报表的计算过程中采用存储过程的观点正好相反。

报表的呈现模板一般是由报表工具绘制的，以文件形式存放在应用中，如果数据源计算由存储过程完成，则这两个紧密相关的部分在物理上分别存放在两处，要修改一张报表时需要两个部分同步调整，不仅容易遗漏出错，还可能增加沟通成本（两部分的负责人员可能不同）。共享数据库中的存储过程还可能被其它报表甚至其它应用调用，修改时就可能造成其它模块的不正常。 用存储过程实现报表数据源会破坏应用的模块结构，增大应用的耦合度 ，造成维护成本升高。

采用存储过程还会造成安全性和高效性的矛盾。 原则上开发报表只需要对数据库有只读权限，但如果数据源是存储过程开发的，则需要向报表开发人员开放编译和运行存储过程的权限，这几乎可以对数据库做一切操作了，安全隐患非常大。一个办法是加强管理，所有上载的存储过程都需要多人审核把关，但这势必会导致低效率，本来报表开发人员自己就能完成的事情要涉及更多岗位。

如果有不依赖于数据库的便捷计算能力，则可以避免掉存储过程的这些劣势。把业务稳定性不强、与界面相关紧密的计算移到数据库外，和应用程序集成到一起，维护成本更低。即使业务稳定性强的计算逻辑也可以用库外计算实现，能够解决多数据库、非数据库等多样性数据源的问题。不采用存储过程的整体应用结构更为合理。

实际测试表明，用存储过程实现数据计算，常常比用SQL取出数据后在外部计算的性能更好。存储过程快在哪里了？

网上有观点说，因为存储过程是预编译的，而每次执行SQL时要临时编译，所以存储过程会更快。其实编译SQL的那点时间相对于数据计算而言可以忽略不计，以不同参数反复执行的SQL也可以预先准备，只要编译一次。有些程序员把不同参数拼进SQL，每次向数据库发送不同SQL，编译时间就不可忽略了。

存储过程的快，主要在于数据不出库。外部程序访问库内数据时必须通过数据库提供的接口，而这些接口的性能大都不好，特别是面向Java程序的JDBC接口。每次发出SQL让数据库执行都会调用这个接口，速度就上不去。如果应用程序和数据库不在同一台物理机器上时，还会有一些网络延迟，不过和接口的低性能相比并不算严重。 在外部计算时，从数据库获取数据的时间常常会超过计算本身的时间。

存储过程本身的执行性能并不好。我们针对某著名商用数据库进行过测试：一句SQL可以完成的运算（比如对某个大表的字段求和），如果改用存储过程把数据一行行取出来计算，差不多会慢出一个数量级。用Java等语言从文件系统中读数做同样的计算，也会比存储过程快很多；外部计算相对容易写出并行代码，充分利用现代服务器多CPU的优势，存储过程一般都没有这个机制了。而且，如果把很多计算都放到存储过程中，并发运算时会加重数据库的负担，使本来就不快的存储过程更慢。

存储过程的性能更好，与其说是优势，倒不如说是被低效的数据库访问接口绑架所致。

目前业内还只有关系数据库有较好的交易一致性能力，适合充当OLTP业务的后台，这样从前端采集到的数据会直接进入关系数据库，这导致原始数据大量存储于数据库中。如果要对这些数据进行计算，采用外部计算方案时，取出数据太慢，总体性能就会很差；而使用存储过程，虽然计算本身不快，但数据不出库也会获得较好性能。这是存储过程不能被完全替代的主要原因和场景。