blocks|key|1143240|text|存储过程将给您带来小小的性能提升，但它们主要用于执行使用简单查询难以或不可能完成的任务。存储过程对于简化许多不同类型客户端的数据访问非常有用。数据库管理员喜欢它们，因为它们控制数据库的使用方式，而不是将这些细节留给开发人员。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1143241|注意索引和适当的表设计，以获得更好的性能。|1143242|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|4360195|text|天哪，我不喜欢有人读到这些答案，然后得到错误的印象。在"MySQL“和"SQL+server/Oracle”上的“随心所欲存储”实现之间有一些非常重要的区别。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4360196|请参阅：http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/|offset|length|4360197|4360198|每个提出这个问题的人都会对MySQL的存储过程实现做一些假设；他们错误地认为存储过程是编译并存储在全局存储过程缓存中的，这与Microsoft+SQL+Server1或Oracle2中的存储过程缓存类似。|blockquote|4360199|4360200|这是错误的。完全不正确。|4360201|事实是:到MySQL服务器的每个连接都维护着它自己的存储过程缓存。|4360202|4360203|花一分钟时间阅读文章的其余部分和评论。这篇文章很简短，你会对问题有更好的理解。|4360204|entityMap|0|LINK|mutability|MUTABLE|url|http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/^0|0|4|1W|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@$D|11|E|12|1|13]]|A|$]]|$1|F|3|-4|5|6|7|14|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|15|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|17|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|18|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1A|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]]]

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".

See: <a href="http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/" rel="noreferrer">http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/</a> 

<blockquote>
 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.
</blockquote>

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.

blocks|key|1143132|text|正如我在JohnFX之前的回答中指出的那样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1143133|1143134|“存储过程的性能优势是值得怀疑的，充其量也是微乎其微的。|blockquote|1143135|1143136|http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx|offset|length|1143137|http://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html|1143138|1143139|好好享受吧。|1143140|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|0|0|0|26|0|0|0|20|1|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|Z|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|11|8|@]|9|@$I|12|J|13|1|14]]|A|$]]|$1|K|3|L|5|6|7|15|8|@]|9|@$I|16|J|17|1|18]]|A|$]]|$1|M|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1A|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|H]]|W|$5|S|T|U|A|$V|L]]]]

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

<blockquote>
 "The performance benefit of stored
 procedures is dubious and minimal at
 best. Some reading material on this
 point:
 
 <a href="http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx" rel="noreferrer">http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx</a>
 
 <a href="http://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html" rel="noreferrer">http://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html</a>
</blockquote>

Enjoy.

blocks|key|1143191|text|大大简化-存储过程的性能等于或略高于代码，但代价是数据库服务器负载。由于大多数db系统与多用户访问有关，并且正在使用用于db服务器的商用硬件，因此使用代码卸载db服务器将可能在整体上胜出。对于高端数据库服务器，>4核，>32+DB内存，SP负载通常不是问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1143192|存储过程；|1143193|1143194|在查询中传输更少的数据-对于编写良好的代码，最小的速度改进|ordered-list-item|1143195|解析和缓存是“稍微好一点的”-对于编写良好的代码，最小的速度改进|1143196|将执行负载移动到数据库服务器与客户端(web服务器)，潜在地将负载分散到许多系统上。-速度的提高非常依赖于实际的代码和数据量，包括传输的“多余”数据。相当多的代码传输比实际使用更多的数据(db库、写得不好的查询、select+*等)+|1143197|1143198|不要过早地进行优化。|1143199|除了速度之外，存储过程还有许多其他的好处，安全性是最重要的。|1143200|在单个程序员环境中，SP编程学习曲线、SP测试框架、多种修订控制方法(+SP和代码等)可能会抵消这些好处。|1143201|学习和使用测试和分析框架将明确地回答这个问题，并将指导您为您的应用程序提供比简单地选择SP或ad-hoc查询更好的“性能”。|1143202|回答“是否值得”--如果你没有一个合适的测试/分析框架，那你只是在猜测而已。任何基于我的代码和硬件的答案都可能与你的无关。|1143203|我在许多使用DB+(Sybase，Oracle，MS+SQL，MySQL，Postgres)存储过程的Perl/TCL/PHP/C+web应用程序上的实际经验并没有从整体上大幅提高性能。但我仍然经常使用它们，只是出于性能以外的其他原因。它们可以极大地改进特定的复杂查询，但这很少是大部分代码和整体处理时间。|1143204|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|14|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|15|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1B|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Z|$]]

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.

Stored procedures; 

<ol>
<li>transfer less data in the query - minimal speed improvement for well written code</li>
<li>parsing &amp; caching is "slightly better" - minimal speed improvement for well written code</li>
<li>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.) </li>
</ol>

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.. 

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.

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.

blocks|key|4360012|text|在MySQL或任何其他SQL+server+(如MSSQL或Oracle+)中，存储过程极大地提高了所涉及的查询的速度，因为这些查询已经过编译。存储过程比直接查询更安全，并且作为数据库中的对象，它们可以由所有者管理，从而为每个用户提供正确的访问权限。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4360013|使用存储过程，您还可以隐藏查询和过程的逻辑，并为开发团队和其他程序员提供一个“黑盒”，他们可以在其中插入参数并接收结果。|4360014|绝对存储的过程太棒了！|4360015|MySQL+5.1文档中的：存储的例程在某些情况下特别有用：|offset|length|style|BOLD|4360016|当多个客户端应用程序使用不同的语言编写或在不同的平台上工作，但需要执行相同的数据库操作时。|4360017|当安全是最重要的时候。例如，银行对所有常见操作都使用存储过程和函数。这提供了一致和安全的环境，例程可以确保正确地记录每个操作。在这样的设置中，应用程序和用户将无法直接访问数据库表，而只能执行特定的存储例程。|4360018|存储的例程可以提供改进的性能，因为需要在服务器和客户端之间发送的信息更少。权衡的是，这确实增加了数据库服务器的负载，因为更多的工作是在服务器端完成的，而在客户端(应用程序)端完成的工作更少。如果许多客户机(如Web服务器)仅由一个或几个数据库服务器提供服务，请考虑这一点。|4360019|存储的例程还允许您在数据库服务器中拥有函数库。这是现代应用程序语言共享的一个特性，现代应用程序语言允许在内部进行这种设计(例如，通过使用类)。即使在数据库使用范围之外，使用这些客户端应用程序语言特性对程序员也是有益的。|4360020|entityMap^0|0|0|0|D|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@$H|Z|I|10|J|K]]|9|@]|A|$]]|$1|L|3|M|5|6|7|11|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|12|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|13|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|14|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|U|$]]

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. 

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:

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.

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.

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

MySQL Stored Procedure vs. complex query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

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

问MySQL存储过程与复杂查询
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL存储过程与复杂查询EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL存储过程与复杂查询
EN