php MySQL 存储过程与复杂查询

MySQL Stored Procedure vs. complex query

提问by Arsham

How is the performance of a Stored Procedure? Is it worth using them instead of implementing a complex query in a PHP/MySQL call?

存储过程的性能如何？是否值得使用它们而不是在 PHP/MySQL 调用中实现复杂的查询？

采纳答案by Robert Casto

Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.

存储过程会给您带来小的性能提升，但它们主要用于执行通过简单查询很难或不可能完成的任务。存储过程非常适合为许多不同类型的客户端简化对数据的访问。数据库管理员喜欢它们，因为他们控制数据库的使用方式，而不是将这些细节留给开发人员。

Look to indexes and proper table design to get better performance.

查看索引和适当的表设计以获得更好的性能。

回答by Mike Gleason

Yikes I'd hate for someone to read these answers and get the wrong impression. There are some really important differences between the "Stored Whatever" implementations on "MySQL" vs "SQL server/Oracle".

哎呀，我讨厌有人阅读这些答案并得到错误的印象。“MySQL”与“SQL server/Oracle”上的“Stored What”实现之间存在一些非常重要的区别。

See: http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

参见：http: //www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

Every person that asks this question assumes something about MySQL's stored procedure implementation; they incorrectly believe that stored procedures are compiled and stored in a global stored procedure cache, similar to the stored procedure cache in Microsoft SQL Server[1] or Oracle[2].

This is wrong. Flat-out incorrect.

Here is the truth: Every single connection to the MySQL server maintains it's own stored procedure cache.

每个问这个问题的人都对 MySQL 的存储过程实现有所假设；他们错误地认为存储过程被编译并存储在全局存储过程缓存中，类似于 Microsoft SQL Server[1] 或 Oracle[2] 中的存储过程缓存。

这是错误的。完全不正确。

这是事实：与 MySQL 服务器的每个连接都维护自己的存储过程缓存。

Take a minute to read the rest of the article and comments. It's short and you'll have a much better understanding of the issues.

花一分钟时间阅读文章的其余部分和评论。它很短，你会对这些问题有更好的理解。

回答by MadMurf

As was pointed out to me in a previous answer courtesy of JohnFX:

正如 JohnFX 在之前的回答中向我指出的那样：

"The performance benefit of stored procedures is dubious and minimal at best. Some reading material on this point:

http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx

http://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html

“存储过程的性能优势是可疑的，充其量是最小的。关于这一点的一些阅读材料：

http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx

http://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html

Enjoy.

享受。

回答by MadMurf

Grossly simplified - Stored procedure performance is equal to or marginally better than code at the cost of db server load. Since most db systems are concerned with multi-user access and are using commodity hardware for the db server, using code offloading the db server will probably win overall. With high end DB servers, > 4 cores, > 32gb ram, SP load is often not an issue.

大体简化 - 以数据库服务器负载为代价，存储过程的性能等于或略好于代码。由于大多数数据库系统都关注多用户访问并且为数据库服务器使用商品硬件，因此使用代码卸载数据库服务器可能会总体上获胜。对于高端数据库服务器，> 4 个内核，> 32gb ram，SP 负载通常不是问题。

Stored procedures;

存储过程；

1. transfer less data in the query - minimal speed improvement for well written code
2. parsing & caching is "slightly better" - minimal speed improvement for well written code
3. move the execution load to the db server vs. client(s) (web servers), potentially spreading the load over many systems. - speed improvements are very dependant on the actual code and amount of data including "excess" data transferred. Quite a bit of code transfers more data than is actually used (db libraries, poorly written queries, select *, etc.)

1. 在查询中传输更少的数据 - 编写良好的代码的速度提升最小
2. 解析和缓存“稍微好一点” - 编写良好的代码的速度提升最小
3. 将执行负载转移到数据库服务器与客户端（网络服务器），可能会将负载分散到许多系统上。- 速度提升非常依赖于实际代码和数据量，包括传输的“多余”数据。相当多的代码传输的数据比实际使用的要多（数据库库、写得不好的查询、选择 * 等）

Don't optimize early.

不要过早优化。

Stored procedures have many other benefits than speed, security being high on the list.

存储过程除了速度、安全性之外还有许多其他好处。

In a single programmer environment, benefits may be offset by SP programming learning curve, SP testing framework, multiple methods of revision control - SP and code, etc..

在单个程序员环境中，好处可能会被 SP 编程学习曲线、SP 测试框架、多种修订控制方法 - SP 和代码等抵消。

Learning and using a testing and profiling framework will answer this definitively and will guide you in providing better "performance" to your app than simply choosing SP or ad-hoc queries.

学习和使用测试和分析框架将明确回答这个问题，并将指导您为您的应用程序提供更好的“性能”，而不是简单地选择 SP 或临时查询。

Answer to "is it worth it" - If you don't have a testing/profiling framework in place you'll only be guessing. Any answer based on my code and hardware is probably irrelevant on yours.

回答“值得吗” - 如果您没有适当的测试/分析框架，您只能在猜测。基于我的代码和硬件的任何答案可能与您的无关。

My real world experience on many Perl/TCL/PHP/C web apps using DB's (Sybase,Oracle,MS SQL,MySQL,Postgres) stored procs DO NOT greatly improve performance over all. But I still use them often, just for other reasons than performance. They can greatly improve a specific complex query, but that's rarely the bulk of the code and overall processing time.

我在许多使用 DB（Sybase、Oracle、MS SQL、MySQL、Postgres）存储过程的 Perl/TCL/PHP/C Web 应用程序上的实际经验并没有大大提高整体性能。但我仍然经常使用它们，只是出于性能以外的其他原因。它们可以极大地改进特定的复杂查询，但这很少涉及大量代码和整体处理时间。

回答by backslash17

In MySQL or any other SQL server as MSSQL or Oracle, stored procedures increase dramatically the speed of the queries involved because this are already compiled. Stored procedures are more secure than direct queries and as object in the database they can be administered by the owner, giving the right access to each user.

在 MySQL 或任何其他 SQL 服务器（如 MSSQL 或 Oracle）中，存储过程显着提高了所涉及查询的速度，因为它们已经被编译。存储过程比直接查询更安全，并且作为数据库中的对象，它们可以由所有者管理，为每个用户提供正确的访问权限。

Using stored procedures you can also hide the logic of the queries and procedures and give to the development team and other programmers a "black box" in wich they insert params and receive results.

使用存储过程，您还可以隐藏查询和过程的逻辑，并为开发团队和其他程序员提供一个“黑匣子”，他们可以在其中插入参数并接收结果。

Definitively stored procedures rocks!!!!

绝对的存储过程摇滚！

From MySQL 5.1 Documentation:Stored routines can be particularly useful in certain situations:

来自 MySQL 5.1 文档：存储的例程在某些情况下特别有用：

When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.

当多个客户端应用程序使用不同语言编写或工作在不同平台上，但需要执行相同的数据库操作时。

When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

当安全至上时。例如，银行将存储过程和函数用于所有常见操作。这提供了一致且安全的环境，并且例程可以确保正确记录每个操作。在这样的设置中，应用程序和用户将无法直接访问数据库表，而只能执行特定的存储例程。

Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.

存储的例程可以提供改进的性能，因为服务器和客户端之间需要发送的信息更少。权衡是这确实增加了数据库服务器的负载，因为更多的工作在服务器端完成，而在客户端（应用程序）端完成的工作更少。如果许多客户端计算机（例如 Web 服务器）仅由一个或几个数据库服务器提供服务，请考虑这一点。

Stored routines also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally (for example, by using classes). Using these client application language features is beneficial for the programmer even outside the scope of database use.

存储例程还允许您在数据库服务器中拥有函数库。这是现代应用程序语言共享的一个特性，允许在内部进行此类设计（例如，通过使用类）。即使在数据库使用范围之外，使用这些客户端应用程序语言功能对程序员也是有益的。