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调用中实现复杂的查询?
5 个解决方案
#1
15
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.
查找索引和适当的表设计以获得更好的性能。
#2
8
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”之间，“存储的任何”实现之间有一些非常重要的区别。
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].
问这个问题的每个人都假定MySQL的存储过程实现;他们错误地认为存储过程被编译并存储在全局存储过程缓存中，类似于Microsoft SQL Server[1]或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服务器的连接都维护自己的存储过程缓存。
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.
花一分钟阅读剩下的文章和评论。它很短，你会对这些问题有更好的理解。
#3
3
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://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://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html
Enjoy.
享受。
#4
2
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.
存储例程还允许在数据库服务器中拥有函数库。这是现代应用程序语言共享的特性，允许在内部进行这种设计(例如，通过使用类)。使用这些客户端应用程序语言特性对程序员来说是有益的，甚至在数据库使用范围之外。
#5
2
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.
大大简化了存储过程的性能，以牺牲db服务器负载为代价，等于或略优于代码。由于大多数db系统都关注多用户访问，并且正在为db服务器使用普通的硬件，使用卸载db服务器的代码可能会总体上胜出。对于高端DB服务器，> 4核，> 32gb内存，SP负载通常不是问题。
Stored procedures;
存储过程;
- transfer less data in the query - minimal speed improvement for well written code
- 在查询中传输更少的数据——对于编写良好的代码来说，速度提高最少
- parsing & caching is "slightly better" - minimal speed improvement for well written code
- 解析和缓存“稍微好一点”——对于编写良好的代码来说，速度的提高是最小的
- 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.)
- 将执行负载移动到db服务器和客户机(web服务器)，可能会将负载分散到许多系统。-速度的改善很大程度上取决于实际代码和数据量，包括传输的“多余”数据。相当多的代码传输的数据比实际使用的多(db库、写得很差的查询、select *等)。
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/ PHP/C web应用程序上的实际经验并没有显著提高性能。但我仍然经常使用它们，只是出于其他原因而不是性能。它们可以极大地改进特定的复杂查询，但这很少是大部分代码和总体处理时间。
#1
15
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.
查找索引和适当的表设计以获得更好的性能。
#2
8
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”之间，“存储的任何”实现之间有一些非常重要的区别。
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].
问这个问题的每个人都假定MySQL的存储过程实现;他们错误地认为存储过程被编译并存储在全局存储过程缓存中，类似于Microsoft SQL Server[1]或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服务器的连接都维护自己的存储过程缓存。
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.
花一分钟阅读剩下的文章和评论。它很短，你会对这些问题有更好的理解。
#3
3
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://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://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html
Enjoy.
享受。
#4
2
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.
存储例程还允许在数据库服务器中拥有函数库。这是现代应用程序语言共享的特性，允许在内部进行这种设计(例如，通过使用类)。使用这些客户端应用程序语言特性对程序员来说是有益的，甚至在数据库使用范围之外。
#5
2
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.
大大简化了存储过程的性能，以牺牲db服务器负载为代价，等于或略优于代码。由于大多数db系统都关注多用户访问，并且正在为db服务器使用普通的硬件，使用卸载db服务器的代码可能会总体上胜出。对于高端DB服务器，> 4核，> 32gb内存，SP负载通常不是问题。
Stored procedures;
存储过程;
- transfer less data in the query - minimal speed improvement for well written code
- 在查询中传输更少的数据——对于编写良好的代码来说，速度提高最少
- parsing & caching is "slightly better" - minimal speed improvement for well written code
- 解析和缓存“稍微好一点”——对于编写良好的代码来说，速度的提高是最小的
- 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.)
- 将执行负载移动到db服务器和客户机(web服务器)，可能会将负载分散到许多系统。-速度的改善很大程度上取决于实际代码和数据量，包括传输的“多余”数据。相当多的代码传输的数据比实际使用的多(db库、写得很差的查询、select *等)。
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/ PHP/C web应用程序上的实际经验并没有显著提高性能。但我仍然经常使用它们，只是出于其他原因而不是性能。它们可以极大地改进特定的复杂查询，但这很少是大部分代码和总体处理时间。