I'm running a website for a jewellery wholesaler.
我正在为一家珠宝批发商运营一个网站。
The prices on all their products are calculated using the current bullion metal fixes that are updated every night.
所有产品的价格均使用当前每晚更新的金银金属修正计算。
Currently, for the website, the calculations are worked out via a php include function, which works fine under current circumstances.
目前,对于该网站,计算是通过php include函数计算出来的,该函数在当前情况下工作正常。
There are around 10,000 products, but the prices are calculated in real-time (ie when the web page is requested). The calculations are simple, but there are lots of them (Around 50+) and i'm worried that an increase in traffic may slow the current script down.
大约有10,000种产品,但价格是实时计算的(即请求网页时)。计算很简单,但有很多(大约50+),我担心流量增加可能会减慢当前脚本的速度。
I'm redesigning the site and was wondering whether it would be beneficial to create a procedure in MySQL to do the calculations instead.
我正在重新设计该网站,并想知道在MySQL中创建一个程序来进行计算是否有益。
Is this likely to be faster that the current php script? Anyone know any good reading reference on using procedures?
这可能比当前的PHP脚本更快吗?有人知道有关使用程序的任何好的阅读参考吗
4 个解决方案
#1
12
Here's a benchmark with stored procedure vs php.
这是存储过程与php的基准。
http://mtocker.livejournal.com/45222.html
The stored procedure was slower by 10x.
存储过程慢了10倍。
You might also want to look at this:
您可能还想看看这个:
http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
#2
6
If the reason you are thinking about this is due to performance and scalability, then I would recommend continuing the calculation in PHP.
如果您考虑这个原因是由于性能和可伸缩性,那么我建议继续使用PHP进行计算。
The reason for this is that regardless whether there is a performance penalty in your PHP, when you are scaling your web application it is generally much easier to move to multiple web servers than multiple database servers. It is therefore preferable to do more calculation in PHP and less in MySQL.
这样做的原因是,无论您的PHP是否存在性能损失,当您扩展Web应用程序时,移动到多个Web服务器通常比多个数据库服务器更容易。因此,最好在PHP中进行更多计算,在MySQL中进行更少计算。
Other than the performance aspect, I still generally prefer avoiding stored procedures in favour of having the logic in the application because
除了性能方面,我仍然倾向于避免使用存储过程而不是在应用程序中使用逻辑
- It can be less portable. Stored procedure add to the effort required to deploy a new instance of your application.
- They are written in a different language than PHP, so a PHP developer may not find them easy to understand.
- It can be difficult to have them kept in source control.
它可以减少携带。存储过程增加了部署应用程序新实例所需的工作量。
它们使用与PHP不同的语言编写,因此PHP开发人员可能不会发现它们易于理解。
将它们保存在源代码管理中可能很困难。
These problems can of course all be solved, without a huge amount of difficulty, if you want to use stored procedures.
如果你想使用存储过程,这些问题当然可以解决,没有太大的困难。
#3
4
If it is absolutely necessary to update the prices on every page request and you're worried that the site will be getting a lot of traffic I wouldn't recommend stored procedures.
如果绝对有必要更新每个页面请求的价格,并且您担心该网站将获得大量流量,我不建议存储过程。
I'd recommend caching the information you use (and it is hard to elaborate without knowing how you're doing this) in memory (perhaps using memcached) and keep reading it from PHP.
我建议在内存中缓存你使用的信息(很难详细说明,而不知道你是怎么做的)(也许是使用memcached)并继续从PHP中读取它。
I'll admit that I haven't done any benchmarking between stored procedures vs in-memory PHP performance but if the procedure doesn't directly affect your query, I recommend caching.
我承认我没有在存储过程与内存中PHP性能之间进行任何基准测试,但如果该过程不直接影响您的查询,我建议使用缓存。
#4
3
In short, keep them in php. Easier to maintain.
简而言之,请将它们保存在php中。更易于维护。
For the current site, there is unlikely to ever hit a performance problem where the difference between the speed of calc in php vs the speed of the calc in the database is ever noticeable. If you were then there is something fundamentally wrong with the code of the site. (This includes realtime currency conversions if being done).
对于当前站点,不太可能遇到性能问题,其中php中的calc速度与数据库中calc的速度之间的差异是显而易见的。如果你那时网站的代码存在根本性的错误。 (这包括完成后的实时货币转换)。
Saying that, keeping the calc in PHP is usually preferred as it is easier to control and debug. It does require the web coders to know the database somewhat but that is normally not a problem. 90% of the code speed ups happen on 10% of the code and it would be easy enough for a dba to identify the queries causing db load if it ever happened.
这样说,保持PHP中的calc通常是首选,因为它更容易控制和调试。它确实需要Web编码器稍微了解数据库,但这通常不是问题。 90%的代码加速发生在10%的代码上,如果发生这种情况,dba就很容易识别导致数据库负载的查询。
#1
12
Here's a benchmark with stored procedure vs php.
这是存储过程与php的基准。
http://mtocker.livejournal.com/45222.html
The stored procedure was slower by 10x.
存储过程慢了10倍。
You might also want to look at this:
您可能还想看看这个:
http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
#2
6
If the reason you are thinking about this is due to performance and scalability, then I would recommend continuing the calculation in PHP.
如果您考虑这个原因是由于性能和可伸缩性,那么我建议继续使用PHP进行计算。
The reason for this is that regardless whether there is a performance penalty in your PHP, when you are scaling your web application it is generally much easier to move to multiple web servers than multiple database servers. It is therefore preferable to do more calculation in PHP and less in MySQL.
这样做的原因是,无论您的PHP是否存在性能损失,当您扩展Web应用程序时,移动到多个Web服务器通常比多个数据库服务器更容易。因此,最好在PHP中进行更多计算,在MySQL中进行更少计算。
Other than the performance aspect, I still generally prefer avoiding stored procedures in favour of having the logic in the application because
除了性能方面,我仍然倾向于避免使用存储过程而不是在应用程序中使用逻辑
- It can be less portable. Stored procedure add to the effort required to deploy a new instance of your application.
- They are written in a different language than PHP, so a PHP developer may not find them easy to understand.
- It can be difficult to have them kept in source control.
它可以减少携带。存储过程增加了部署应用程序新实例所需的工作量。
它们使用与PHP不同的语言编写,因此PHP开发人员可能不会发现它们易于理解。
将它们保存在源代码管理中可能很困难。
These problems can of course all be solved, without a huge amount of difficulty, if you want to use stored procedures.
如果你想使用存储过程,这些问题当然可以解决,没有太大的困难。
#3
4
If it is absolutely necessary to update the prices on every page request and you're worried that the site will be getting a lot of traffic I wouldn't recommend stored procedures.
如果绝对有必要更新每个页面请求的价格,并且您担心该网站将获得大量流量,我不建议存储过程。
I'd recommend caching the information you use (and it is hard to elaborate without knowing how you're doing this) in memory (perhaps using memcached) and keep reading it from PHP.
我建议在内存中缓存你使用的信息(很难详细说明,而不知道你是怎么做的)(也许是使用memcached)并继续从PHP中读取它。
I'll admit that I haven't done any benchmarking between stored procedures vs in-memory PHP performance but if the procedure doesn't directly affect your query, I recommend caching.
我承认我没有在存储过程与内存中PHP性能之间进行任何基准测试,但如果该过程不直接影响您的查询,我建议使用缓存。
#4
3
In short, keep them in php. Easier to maintain.
简而言之,请将它们保存在php中。更易于维护。
For the current site, there is unlikely to ever hit a performance problem where the difference between the speed of calc in php vs the speed of the calc in the database is ever noticeable. If you were then there is something fundamentally wrong with the code of the site. (This includes realtime currency conversions if being done).
对于当前站点,不太可能遇到性能问题,其中php中的calc速度与数据库中calc的速度之间的差异是显而易见的。如果你那时网站的代码存在根本性的错误。 (这包括完成后的实时货币转换)。
Saying that, keeping the calc in PHP is usually preferred as it is easier to control and debug. It does require the web coders to know the database somewhat but that is normally not a problem. 90% of the code speed ups happen on 10% of the code and it would be easy enough for a dba to identify the queries causing db load if it ever happened.
这样说,保持PHP中的calc通常是首选,因为它更容易控制和调试。它确实需要Web编码器稍微了解数据库,但这通常不是问题。 90%的代码加速发生在10%的代码上,如果发生这种情况,dba就很容易识别导致数据库负载的查询。