MySQL 存储过程使用或不使用它们

2021-11-20 00:00:00 performance database mysql stored-procedures database-design

我们正处于一个新项目的开始阶段，我们真的很想知道是否应该在 MySQL 中使用存储过程.

We are at the beginning of a new project, and we are really wondering if we should use stored procedures in MySQL or not.

我们只会使用存储过程来插入和更新业务模型实体.有几个表代表一个模型实体，我们会在那些存储过程插入/更新中抽象它.

We would use the stored procedures only to insert and update business model entities. There are several tables which represent a model entity, and we would abstract it in those stored procedures insert/update.

另一方面，我们可以从模型层调用插入和更新，但不能在 MySQL 中调用，而是在 PHP 中调用.

On the other hand, we can call insert and update from the Model layer but not in MySQL but in PHP.

根据您的经验，哪个是最佳选择?两种方法的优缺点.就高性能而言，哪个是最快的?

PS:这是一个以读取为主的web项目，高性能是最重要的要求.

PS: It is is a web project with mostly read and high performance is the most important requisite.

推荐答案

与实际的编程语言代码不同，它们:

Unlike actual programming language code, they:

  • 不可移植(每个数据库都有自己的 PL/SQL 版本.有时相同 数据库的不同版本是不兼容的 - 我见过！)
  • 不容易测试 - 您需要一个真实(开发)数据库实例来测试它们，因此作为构建的一部分对它们的代码进行单元测试几乎是不可能的
  • 不容易更新/发布 - 您必须删除/创建它们，即修改生产数据库以发布它们
  • 没有库支持(为什么在别人有的时候编写代码)
  • 不容易与其他技术集成(尝试从它们调用网络服务)
  • 他们使用与 Fortran 一样原始的语言，因此很难完成有用的编码，因此很难表达业务逻辑，尽管这通常是他们的主要目的
  • 不提供调试/跟踪/消息记录等功能(某些数据库可能支持这一点 - 不过我还没有看到)
  • 缺乏一个像样的 IDE 来帮助处理语法和链接到其他现有程序(例如 Eclipse 为 Java 所做的那样)
  • 能够熟练编码的人比应用程序编码人员少且昂贵
  • 它们的高性能"是一个神话，因为它们在数据库服务器上执行，它们通常增加数据库服务器负载，因此使用它们通常会减少您的最大事务吞吐量
  • 无法有效地共享常量(通常通过创建一个表并在您的过程中对其进行查询来解决 - 非常低效)
  • 等
  • not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible - I've seen it!)
  • not easily testable - you need a real (dev) database instance to test them and thus unit testing their code as part of a build is virtually impossible
  • not easily updatable/releasable - you must drop/create them, ie modify the production db to release them
  • do not have library support (why write code when someone else has)
  • are not easily integratable with other technologies (try calling a web service from them)
  • they use a language about as primitive as Fortran and thus are inelegant and laborious to get useful coding done, so it is difficult to express business logic, even though typically that is what their primary purpose is
  • do not offer debugging/tracing/message-logging etc (some dbs may support this - I haven't seen it though)
  • lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
  • people skilled in coding them are rarer and more expensive than app coders
  • their "high performance" is a myth, because they execute on the database server they usually increase the db server load, so using them will usually reduce your maximum transaction throughput
  • inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure - very inefficient)
  • etc.

如果您有一个非常特定于数据库的操作(例如，用于维护数据库完整性的事务中操作)，或者让您的过程非常原子和简单，也许您可​​以考虑它们.

If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.

在预先指定高性能"时建议谨慎.它通常会以牺牲良好的设计为代价导致糟糕的选择，而且它会比您想象的更快地咬住您.

Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.

使用存储过程需自担风险(来自曾经去过那里并且再也不想回去的人).我的建议是避免它们像瘟疫一样.

Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.

相关文章