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

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

• 不可移植(每个 db 都有自己的 PL/SQL 版本。有时候 一样数据库的不同版本是不兼容的——我见过!)
• 不容易测试——您需要一个 真的(dev)数据库实例来测试它们，因此将它们的代码作为构建的一部分进行单元测试实际上是不可能的
• 不容易更新/发布-您必须删除/创建它们，即 修改生产数据库来发布它们
• 没有库支持(为什么别人有代码却要自己编写)
• 不容易与其他技术集成(尝试从这些技术调用 Web 服务)
• 它们使用的语言大约和 Fortran 一样原始，因此完成有用的编码既不优雅又费力，所以很难表达业务逻辑，尽管这通常是它们的主要目的
• 不提供调试/跟踪/消息日志记录等功能(有些 dbs 可能支持这个功能——不过我还没有看到)
• 缺乏一个像样的 IDE 来帮助语法和链接到其他现有的过程(例如 Eclipse 为 java 所做的)
• 熟练编写应用程序的人比应用程序编码人员更少，也更昂贵
• 他们的“高性能”是一个神话，因为他们执行的数据库服务器他们通常 增加的数据库服务器负载，所以使用他们通常会 减少你的最大事务吞吐量
• 无法有效地共享常量(通常通过创建表并在过程中对其进行查询来解决——效率非常低)
• 等等。

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

在预先指定“高性能”时请注意。它往往会导致糟糕的选择，以牺牲好的设计为代价，而且它会比你想象的更快地伤害你。

使用存储过程的风险是自己承担的(来自那些已经存在并且永远不想回去的人)。我的建议是像躲瘟疫一样躲开他们。

存储过程非常适合使用，因为它们可以保持查询的组织性，并允许您立即执行批处理。存储过程的执行通常很快，因为它们是预编译的，不像每次运行时编译的查询。在数据库位于远程服务器上的情况下，这会产生显著的影响; 如果查询是在 PHP 脚本中，那么应用程序和数据库服务器之间会有多个通信——查询被发送、执行和返回。但是，如果使用存储过程，它只需要发送一个小的 CALL 语句，而不需要发送大的、复杂的查询。

适应存储过程的编程可能需要一段时间，因为它们有自己的语言和语法。但是一旦您习惯了它，您将看到您的代码是非常干净的。

就性能而言，如果使用或不使用存储过程，可能不会有任何重大收获。

与编程代码不同的是:

• 呈现 SQL 注入攻击几乎 不可能(除非你是
  动态构造和执行
  过程中的 SQL)
• 需要传送的数据要少得多 IPC作为标注的一部分
• 使数据库更好 缓存计划和结果集(这是 不可否认的是，在... 方面不是很有效 由于 MySQL 的内部缓存 建筑物)
• 很容易单独测试 (即不作为 JUnit 测试的一部分)
• 是可移植的，因为它们 允许您使用特定于 db 的 抽象出来的特征 程序名称(在代码中被卡住) 使用通用的 SQL 类型的东西)
• 几乎永远不会比 SQL 慢 从代码调用

但是，正如波西米亚人所说，也有很多弊端(这只是为了提供另一种观点)。在你决定什么对你来说是最好的之前，你可能需要做一些基准测试。

我建议您远离 DB 特定的存储过程。

我参与过很多项目，他们突然想要切换数据库平台，而 SP 中的代码通常不是很便携 = 额外的工作和可能的错误。

存储过程开发还要求开发人员能够直接访问 SQL 引擎，在 SQL 引擎中，项目中的任何人都可以通过代码访问更改正常的连接。

关于你的模型/层/层的想法: 是的，坚持下去。

• 网站调用业务层(BL)
• 基本法调用数据层(DL)
• DL 调用任何存储(SQL、 XML、 Webservice、套接字、文本文件等)

通过这种方式，您可以维护层之间的逻辑级别。IF 和 ONLY 如果 DL 调用似乎非常慢，你可以开始摆弄存储过程，但保持原来的无 SP 代码的某个地方，如果你突然需要转移数据库到一个全新的平台。随着所有的云托管业务，你永远不知道下一个 DB 平台会是什么..。

出于同样的原因，我一直密切关注亚马逊 AWS。

我建议您不要使用存储过程:

• 他们在 MySQL 中的语言非常糟糕
• 无法将数组、列表或其他类型的数据结构发送到存储过程中
• 存储过程不能更改其接口; MySQL 既不允许命名参数，也不允许可选参数
• 它使得部署应用程序的新版本变得更加复杂——假设您有10个应用程序服务器和2个数据库，您首先更新哪个？
• 您的开发人员都需要学习和理解存储过程语言——这是非常糟糕的(正如我之前提到的)

相反，我建议创建一个图层/库，并将所有查询放在其中

你可以的

• 更新这个库，并将它与你的应用程序一起发布到你的应用服务器上
• 具有丰富的数据类型，例如数组、结构等
• 单元测试这个库，而不是存储过程。

演出方面:

• 使用存储过程将降低应用程序开发人员的性能，这是您关心的主要问题。
• 在复杂的存储过程中识别性能问题是极其困难的(对于普通查询，识别性能问题要容易得多)
• 您可以通过连接(如果启用了 CLIENT _ MULTI _ STATEMENT 标志)以单个块的形式提交查询批处理，这意味着在没有存储过程的情况下不会有更多的延迟。
• 应用程序端代码的伸缩性通常比数据库端代码好

如果您的数据库是复杂的，而不是一个论坛类型的响应，但真正的仓库 SP 将肯定受益。你可以把所有的业务逻辑都放在那里，没有一个开发人员会关心它，他们只是调用你的 SP。我一直在做这个加入超过15个表是不好玩的，你不能解释这个给一个新的开发人员。

开发人员也没有访问数据库的权限，太棒了！将这个问题留给数据库设计人员和维护人员处理。如果您还决定要更改表结构，则可以将其隐藏在接口后面。n-Tier 记得吗？

高性能和关系数据库不是一回事，即使 MySQL InnoDB 很慢，MyISAM 现在也应该被抛出窗外了。如果你需要一个网络应用的性能，你需要适当的缓存，内存缓存或其他。

在您的例子中，因为您提到了“ Web”，所以我不会使用存储过程，如果它是数据仓库，我肯定会考虑使用它(我们的仓库使用 SP)。

提示: 既然您提到了 Web 项目，那么您有没有考虑过 nosql 的解决方案呢？此外，您需要一个快速数据库，为什么不使用 PostgreSQL？(试图在这里提倡...)

尽管我的想法可能与这个问题没有直接关系，我还是会表达我的观点:

在许多问题中，关于使用存储过程或应用程序层驱动的解决方案的回答依赖于驱动整体工作的问题:

• 你想要什么。

您正在尝试执行批处理操作或联机操作吗？它们完全是交易性的吗？这些手术有多频繁？等待数据库的工作负载有多重？

• 你有什么，为了得到它。

你们有什么样的数据库技术？什么样的基础设施？您的团队在数据库技术方面是否受过充分的培训？您的团队是否更有能力构建一个不可知的数据库解决方案？

• 是时候了。

没有秘密。

• 建筑学。

是否需要将解决方案分发到多个位置？你的解决方案是否需要使用远程通信？您的解决方案是在多个数据库服务器上运行，还是可能使用基于集群的体系结构？

• 维修。

应用程序需要改变多少? 您是否接受过维护解决方案的专门培训？

• 变更管理。

您认为您的数据库技术将在短期、中期、长期发生变化吗？您是否看到需要频繁地迁移解决方案？

• 成本

使用一种或另一种策略实现该解决方案的成本是多少？

这些问题的总体结果将决定答案。因此，在决定使用或不使用任何策略时，你必须关注以上每一点。在某些情况下，使用存储过程比使用应用程序层托管查询更好，而在其他情况下，执行查询和使用基于应用程序层的解决方案是最好的。

在下列情况下，使用存储过程往往更为充分:

1. 没有提供数据库技术以便在短时间内进行更改。
2. 您的数据库技术可以处理并行操作、表分区或任何其他策略，以便将工作负载划分到多个处理器、内存和资源(集群、网格)上。
3. 您的数据库技术与存储过程定义语言完全集成，也就是说，支持在数据库引擎内部。
4. 你有一个开发团队，他们不怕使用工作语言(第三代语言)来获得结果。
5. 您希望实现的操作在数据库中是内置的或支持的(将数据导出到 XML 数据，管理数据完整性和与触发器、计划操作等的一致性)。
6. 可移植性不是一个重要的问题，而且您不会在短时间内看到一项技术在您的组织中发生变化，甚至，这是不可取的。通常，可移植性被应用程序驱动和面向分层的开发人员视为一个里程碑。从我的角度来看，当你的应用程序不需要部署到多个平台上时，可移植性不是问题，当没有理由进行技术更改时，或者移植所有组织数据的工作比进行更改的好处更大时，可移植性就不是问题。通过使用应用层驱动的方法(可移植性) ，你可以在性能和从数据库中获得的价值上有所损失(为什么要花费数千美元来得到一辆时速不超过60英里的法拉利.
7. 表现是个问题。第一: 在某些情况下，使用单个存储过程调用比使用来自另一个应用程序的多个数据请求可以获得更好的结果。此外，您需要执行的一些特性可能是内置在数据库中的，并且从工作负载的角度来看，它的使用成本较低。当您使用应用程序层驱动的解决方案时，您必须考虑进行数据库连接、调用数据库、网络流量、数据包装(即，使用 Java 或。NET，当使用 JDBC/ADO.NET 调用时会有一个隐含的成本，因为你必须将数据包装到表示数据库数据的对象中，所以当数据从外部来到时，实例化在处理、内存和网络方面有一个相关的成本)。

在下列情况下，应用层驱动解决方案的使用往往更为充分:

1. 便携性是一个重要的问题。
2. 应用程序将被部署到只有一个或几个数据库存储库的多个位置。
3. 您的应用程序将使用大量面向业务的规则，这些规则需要与底层数据库技术无关。
4. 您应该根据市场趋势和预算来改变技术供应商。
5. 数据库没有与调用数据库的存储过程语言完全集成。
6. 您的数据库能力是有限的，您的需求超出了使用数据库技术可以实现的范围。
7. 您的应用程序可以支持外部调用所固有的惩罚，更多的是基于事务的业务特定规则，并且必须为用户将数据库模型抽象到业务模型中。
8. 并行化数据库操作并不重要，而且，您的数据库没有并行化能力。
9. 您的开发团队对数据库技术没有经过很好的培训，而且通过使用基于应用程序驱动的技术，开发效率更高。

希望这可以帮助任何人问自己什么是更好的使用。

在性能方面，他们将在未来的 MySQL 版本中使用 真正表现出来的潜力(在 SQLServer 或 Oracle 下，它们是真正的享受!).然而，对于其他人来说，他们完全破坏了竞争。我总结一下:

• 安全性: 您可以给您的应用程序的 EXECUTE 权利只，一切都很好。您的 SP 将插入更新选择... ，没有任何可能的泄漏排序。它意味着对模型的全局控制和强制的数据安全。

• 安全2: 我知道这种情况很少见，但是有时候 php 代码会从服务器泄露出去(即对公众可见)。如果它包含您的查询，那么可能的攻击者知道您的模型。这很奇怪，但我还是想发出信号

• 任务组: 是的，创建高效的 SQL SPs 需要一些特定的资源，有时候更昂贵。但是如果你认为你不需要这些资源只是因为你正在整合你的客户端查询... 你会有严重的问题。我想提一下 web 开发的类比: 将视图与其他视图分开是很好的，因为你的设计师可以使用他们自己的技术，而程序员可以专注于业务层的编程。

• 封装业务层: 使用存储过程完全隔离了业务所属的领域: 该死的数据库。

• 可快速测试: 在 shell 下的一个命令行中测试代码。

• 独立于客户端技术: 如果明天你想从 php 切换到其他东西，没有问题。好的，只要把这些 SQL 存储在一个单独的文件中就可以了，这是正确的。另外，如果您决定切换 sql 引擎，那么您还有很多工作要做。无论如何，你必须有一个很好的理由去这样做，因为对于大项目和大公司来说，这种情况很少发生(主要是由于成本和人力资源管理)

• 实施敏捷的3 + 层开发: 如果您的数据库与客户机代码不在同一个服务器上，那么您可能拥有不同的服务器，但是只有一个用于数据库的服务器。在这种情况下，当需要更改 SQL 相关代码时，不必升级任何 PHP 服务器。

好吧，我想这是我在这个问题上要说的最重要的事情。我在这两种精神(SP 对客户)和我真的，真的很喜欢 SP 的风格之一。我只是希望 Mysql 有一个真正的 IDE，因为现在它是有限的 讨厌鬼。

我曾经使用过 MySql，我对 Sql 的理解很差，我花了相当多的时间使用 Sql 服务器，我有一个明确的数据层和应用程序层的分离，我目前看管一个0.5 TB 的服务器。

我有时感到沮丧不使用 ORM，因为开发真的很快与存储过程，这是慢得多。我认为使用 ORM 可以加快我们的大部分工作。

当应用程序达到临界质量时，ORM 性能将受到影响，一个编写良好的存储过程将使您更快地得到结果。

作为一个性能示例，我在一个应用程序中收集10种不同类型的数据，然后将其转换为 XML，并在存储过程中处理这些数据，对数据库的调用只有一次，而不是10次。

Sql 非常擅长处理数据集，但是当我看到有人以原始形式从 Sql 获取数据，并使用应用程序代码循环处理结果、格式化结果并对结果进行分组时，我感到非常沮丧，这确实是一种糟糕的做法。

我的建议是，充分了解和理解 sql，您的应用程序将真正受益。

这里有很多让人困惑的信息，软件开发是一个进化过程。我们20年前的做法现在已经不是最佳实践了。回到过去，使用经典的客户端服务器，除了 SPs，你不会梦想任何东西。

这绝对是马的课程，如果你是一个大的组织，你会使用多层次，可能 SPs，但你不会关心他们，因为一个专门的团队将整理出来。

相反，我发现自己正试图快速构建一个 Web 应用解决方案，充实业务需求，让开发人员(对我来说是远程的)来构建页面和 SQL 查询，我定义数据库结构，这是非常快的。

然而，复杂性正在增长，而且没有一种简单的方法来提供 API，我开始使用 SP 来包含业务逻辑。我认为这是工作良好和明智的，我控制这一点，因为我可以建立逻辑和提供一个简单的结果集为我的离岸开发人员建立一个前端左右。

如果我发现我的软件取得了巨大的成功，那么将会出现更多的关注点分离，并且会出现 n teir 的不同实现，但是目前 SP 是完美的。

您应该知道所有可用的工具集，并且从一开始就匹配它们是明智的。除非您正在构建一个企业系统，否则最好是快速简单地开始。

尝试从一个框架更新活动系统上的100,000,000条记录，并让我知道它是如何工作的。对于小型应用程序来说，SP 不是必须的，但对于大型严肃的系统来说，它们是真正的资产。

我认为有很多关于数据库存储查询的错误信息。

如果您正在为数据操作执行许多静态查询，我建议您使用 MySQL 存储过程。特别是如果您正在将事物从一个表移动到另一个表(例如，不管出于什么原因，从一个活动表移动到一个历史表)。当然，还有一些缺点，那就是您必须保存对它们的更改的单独日志(理论上，您可以创建一个表，仅保存对 DBA 更新的存储过程的更改)。如果你有许多不同的应用程序与数据库接口，特别是如果你有一个用 C # 编写的桌面程序和一个用 PHP 编写的网络程序，它可能是更有益的，有一些程序存储在数据库中，因为它们是平台无关的。

这个网站上有一些有趣的信息，你可能会觉得有用。

Https://www.sitepoint.com/stored-procedures-mysql-php/

像往常一样，首先构建一个沙箱，然后进行测试。