在MySQL和PHP中进行计算 [英] Doing calculations in MySQL vs PHP

问题描述

上下文:

• 我们有一个PHP/MySQL应用程序.
• 某些计算部分直接在SQL中完成.例如:过去24小时内创建的所有用户都将通过SQL查询返回(NOW()– 1天)

同一个开发人员和我之间正在进行辩论，我认为我们应该这样做:

There's a debate going on between a fellow developer and me where I'm having the opinion that we should:

A.将所有计算/代码/逻辑保存在PHP中，并将MySQL视为愚蠢的"信息存储库

A. Keep all calculations / code / logic in PHP and treat MySQL as a 'dumb' repository of information

他的意见:

B.进行混搭取决于更容易/更快. http://www.onextrapixel.com/2010/06/23/mysql-has-functions-part-5-php-vs-mysql-performance/

B. Do a mix and match depending on whats easier / faster. http://www.onextrapixel.com/2010/06/23/mysql-has-functions-part-5-php-vs-mysql-performance/

我正在研究可维护性的观点.他正在研究速度(如本文所指出的那样，在MySQL中某些操作更快).

I'm looking at maintainability point-of-view. He's looking at speed (which as the article points out, some operations are faster in MySQL).

@ bob-the-destroyer @tekretic @OMG小马 @mu太短 @都铎·康斯坦丁 @tandu @哈雷

@bob-the-destroyer @tekretic @OMG Ponies @mu is too short @Tudor Constantin @tandu @Harley

我同意(并且很显然)高效的WHERE子句属于SQL级别.但是，诸如此类的示例呢?

I agree (and quite obviously) efficient WHERE clauses belong in the SQL level. However, what about examples like:

1. 使用NOW()-在SQL中使用1天来选择过去24小时内创建的所有用户来计算24个期间吗?
2. 返回所有用户的大写名字和姓氏吗?
3. 连接字符串?
4. (想法，伙计?)

清除属于SQL域的示例:

Clear examples belonging in the SQL domain:

1. 特定的WHERE选择
2. 嵌套SQL语句
3. 订购/排序
4. 选择DISTINCT项目
5. 计算行数/项目

推荐答案

我会发挥每个系统的优势.

I'd play to the strengths of each system.

聚集，联接和过滤逻辑显然属于数据层.它的速度更快，不仅是因为大多数数据库引擎为此进行了10多年的优化，而且还可以最大程度地减少在数据库和Web服务器之间转移的数据.

Aggregating, joining and filtering logic obviously belongs on the data layer. It's faster, not only because most DB engines have 10+ years of optimisation for doing just that, but you minimise the data shifted between your DB and web server.

另一方面，我使用的大多数数据库平台在处理单个值时的功能都非常差.日期格式和字符串操作之类的东西在SQL中很烂，最好在PHP中完成.

On the other hand, most DB platforms i've used have very poor functionality for working with individual values. Things likes date formatting and string manipulation just suck in SQL, you're better doing that work in PHP.

基本上，使用每个系统来完成其工作.

Basically, use each system for what it's built to do.

就可维护性而言，只要清楚区分发生在什么地方的情况，将这些与逻辑类型分开就不会造成太大问题，当然也不足以抵消收益.在我看来，代码的清晰度和可维护性更多的是一致性，而不是将所有逻辑放在一个地方.

In terms of maintainability, as long as the division between what happens where is clear, separating these to types of logic shouldn't cause much problem and certainly not enough to out way the benefits. In my opinion code clarity and maintainability are more about consistency than about putting all the logic in one place.

回复:具体示例...

Re: specific examples...

1. 我知道这也不是您所指的，但日期几乎是一种特殊情况.您要确保系统生成的所有日期都在Web服务器或数据库上创建.如果将db服务器和Web服务器配置为不同的时区，则否则会导致一些隐患(我已经看到了这种情况).例如，想象一下，您有一个createdDate列，其默认值为getDate()，该列应用于数据库的插入 .如果要插入一条记录，则使用在PHP中生成的日期(例如，date("Y-m-d", time() - 3600))，选择在过去一小时内创建的记录，您可能无法获得期望的结果.为此，我希望使用数据库，例如在示例中，它使您可以使用列默认值.

1. I know this isn't what you're referring too but dates are almost a special case. You want to make sure that all dates generated by the system are created either on the web server OR the database. Doing otherwise will cause some insidious bugs if the db server and webserver are ever configured for different timezones (i've seen this happen). Imagine, for example, you've got a createdDate column with a default of getDate() that is applied on insert by the DB. If you were to insert a record then, using a date generated in PHP (eg date("Y-m-d", time() - 3600), select records created in the last hour, you might not get what you expect. As for which layer you should do this on, i'd favour the DB for, as in the example, it lets you use column defaults.

对于大多数应用程序，我会在PHP中执行此操作.混合姓氏和名字听起来很简单，直到您意识到有时也需要在其中使用称呼，标题和中间名缩写.另外，您几乎肯定会以想要用户的名字，姓氏和称呼称呼+名字+姓氏的组合结尾.将它们串联在DB端意味着您最终将移动更多的数据，尽管实际上，它很小.

For most apps i'd do this in PHP. Combining first name and surname sounds simple until you realise you need salutations, titles and middle initials in there sometimes too. Plus you're almost definitely going to end up in a situation where you want a users first name, surname AND a combine salutation + firstname + surname. Concatenating them DB-side means you end up moving more data, although really, it's pretty minor.

取决于.如上所述，如果您想单独使用它们，则最好从性能角度考虑，将它们分别拉出并在需要时进行连接.也就是说，除非您要处理的数据集庞大，否则可能还有其他因素(如您提到的可维护性)具有更大的影响力.

Depends. As above, if you ever want to use them separately you're better off performance-wise pulling them out separately and concatenating when needed. That said, unless the datasets your dealing with are huge there are probably other factors (like, as you mention, maintainability) that have more bearing.

一些经验法则:

• 生成增量ID应该在数据库中进行.
• 我个人而言，我喜欢数据库应用的默认设置.
• 选择时，任何减少记录数量的操作都应由数据库完成.
• 做一些通常可以减少数据库侧数据集大小的事情(例如上面的字符串示例).
• 正如您所说；排序，聚合，子查询，联接等应始终在数据库端.
• 我们还没有讨论它们，但是触发器通常是坏的/必要的.

在这里您需要面对一些核心的权衡，而平衡实际上取决于您的应用程序.

There are a few core trade-offs your facing here and the balance really depends on you application.

某些事情绝对应该总是在SQL中完成.排除许多任务的某些异常(例如日期事件)，SQL可能很笨拙，并且可能使您陷入逻辑混乱的境地.在代码库中搜索对特定列的引用时(例如)， 很容易错过视图或存储过程中包含的内容.

Some things should definitely-everytime-always be done in SQL. Excluding some exceptions (like the dates thing) for lot of tasks SQL can be very clunky and can leave you with logic in out of the way places. When searching your codebase for references to a specific column (for example) it is easy to miss those contained in a view or stored procedure.

性能始终是一个考虑因素，但取决于您的应用程序和特定示例，也许不是一个大问题.您对可维护性的担忧，可能非常有效，我提到的一些性能优势非常轻微，因此请提防过早优化.

Performance is always a consideration but, depending on you app and the specific example, maybe not a big one. Your concerns about maintainability and probably very valid and some of the performance benefits i've mentioned are very slight so beware of premature optimisation.

此外，如果其他系统正在直接访问数据库(例如，用于报告或导入/导出)，您将受益于数据库中更多的逻辑.例如，如果要直接从另一个数据源导入用户，则可以在SQL中实现诸如电子邮件验证功能之类的可重用功能.

Also, if other systems are accessing the DB directly (eg. for reporting, or imports/exports) you'll benefit from having more logic in the DB. For example, if you want to import users from another datasource directly, something like an email validation function would be reusable is implemented in SQL.

简短的回答:这要看情况. :)

Short answer: it depends. :)

这篇关于在MySQL和PHP中进行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！