I've read that (all things equal) PHP is typically faster than MySQL at arithmetic and string manipulation operations. This being the case, where does one draw the line between what one asks the database to do versus what is done by the web server(s)? We use stored procedures exclusively as our data-access layer. My unwritten rule has always been to leave output formatting (including string manipulation and arithmetic) to the web server. So our queries return:
我已经读过(所有条件相同)PHP在算术和字符串操作操作中通常比MySQL快。在这种情况下,人们要求数据库做什么与Web服务器做什么之间划清界线?我们专门使用存储过程作为数据访问层。我的不成文规则一直是将输出格式(包括字符串操作和算术)留给Web服务器。所以我们的查询返回:
- unformatted dates
- null values
- no calculated values (i.e. return values for columns "foo" and "bar" and let the web server calculate foo*bar if it needs to display value foobar)
- no substring-reduced fields (except when shortened field is so significantly shorter that we want to do it at database level to reduce result set size)
- two separate columns to let front-end case the output as required
没有计算值(即列“foo”和“bar”的返回值,如果需要显示值foobar,则让Web服务器计算foo * bar)
没有减少子字符串的字段(除非缩短的字段非常短,我们希望在数据库级别执行此操作以减少结果集大小)
两个单独的列让前端根据需要输出输出
What I'm interested in is feedback about whether this is generally an appropriate approach or whether others know of compelling performance/maintainability considerations that justify pushing these activities to the database.
我感兴趣的是关于这是否通常是一种合适的方法的反馈,或者其他人是否知道将这些活动推送到数据库的合理性能/可维护性考虑因素。
Note: I'm intentionally tagging this question to be dbms-agnostic, as I believe this is an architectural consideration that comes into play regardless of one's specific dbms.
注意:我故意将此问题标记为与dbms无关,因为我认为这是一个架构考虑因素,无论特定的dbms如何都会发挥作用。
4 个解决方案
#1
I would draw the line on how certain layers could rotate out in place for other implementations. It's very likely that you will never use a different RDBMS or have a mobile version of your site, but you never know.
我想就某些层如何为其他实现旋转到适当的位置划清界限。您很可能永远不会使用不同的RDBMS或拥有您网站的移动版本,但您永远不会知道。
The more orthogonal a data point is, the closer it should be to being released from the database in that form. If on every theoretical version of your site your values A and B are rendered A * B, that should be returned by your database as A * B and never calculated client side.
数据点越正交,就越接近以该形式从数据库中释放。如果在您网站的每个理论版本上,您的值A和B都呈现为A * B,那么您的数据库应该返回A * B并且从不计算客户端。
Let's say you have something that's format heavy like a date. Sometimes you have short dates, long dates, English dates... One pure form should be returned from the database and then that should be formatted in PHP.
假设你的格式像日期一样重。有时你有短日期,长日期,英语日期...应该从数据库返回一个纯表单,然后应该用PHP格式化。
So the orthogonality point works in reverse as well. The more dynamic a data point is in its representation/display, the more it should be handled client side. If a string A is always taken as a substring of the first six characters, then have that be returned from the database as pre-substring'ed. If the length of the substring depends on some factor, like six for mobile and ten for your web app, then return the larger string from the database and format it at run time using PHP.
所以正交点也是相反的。数据点在其表示/显示中越动态,客户端应该处理得越多。如果字符串A始终被视为前六个字符的子字符串,则将其作为pre-substring'ed从数据库返回。如果子字符串的长度取决于某个因素,例如6个用于移动设备,10个用于您的Web应用程序,则从数据库返回较大的字符串并使用PHP在运行时格式化它。
#2
Usually, data formatting is better done on client side, especially culture-specific formatting.
通常,数据格式化最好在客户端完成,尤其是特定于文化的格式化。
Dynamic pivoting (i. e. variable columns) is also an example of what is better done on client side
动态旋转(即变量列)也是在客户端更好地完成的示例
When it comes to string manipulation and dynamic arrays, PHP is far more powerful than any RDBMS I'm aware of.
在字符串操作和动态数组方面,PHP比我所知的任何RDBMS都强大得多。
However, data formatting can use additional data which is also kept in the database. Like, the coloring info for each row can be stored in additional table.
但是,数据格式化可以使用也保存在数据库中的其他数据。比如,每行的着色信息可以存储在附加表中。
You should then correspond the color to each row on database side, but wrap it into the tags on PHP side.
然后,您应该将颜色与数据库端的每一行对应,但将其包装到PHP端的标记中。
The rule of thumb is: retrieve everything you need for formatting in as few database round-trips as possible, then do the formatting itself on the client side.
经验法则是:在尽可能少的数据库往返中检索格式化所需的所有内容,然后在客户端进行格式化。
#3
I believe in returning the data pretty much as-is from the database and letting it be formatted on the front-end instead. I don't stick to it religously, but in general I think it's better as it provides greater flexibility - e.g. 1 sproc can service n different requirements for data, each of which can format the data as each individually needs. Otherwise, you end up either with multiple queries returning the same data with slightly different formatting from the DB (from a SQL Server point of view, thus reducing execution plan caching benefits - therefore negative impact on performance).
我相信几乎从数据库中返回数据并让它在前端进行格式化。我并不坚持宗教,但总的来说我觉得它更好,因为它提供了更大的灵活性 - 例如1个sproc可以满足不同的数据要求,每个数据都可以根据每个需求格式化数据。否则,您最终要么使用多个查询返回相同的数据,格式与DB略有不同(从SQL Server的角度来看,从而减少执行计划缓存的好处 - 因此对性能产生负面影响)。
#4
Leave output formatting to the web server
将输出格式保留为Web服务器
#1
I would draw the line on how certain layers could rotate out in place for other implementations. It's very likely that you will never use a different RDBMS or have a mobile version of your site, but you never know.
我想就某些层如何为其他实现旋转到适当的位置划清界限。您很可能永远不会使用不同的RDBMS或拥有您网站的移动版本,但您永远不会知道。
The more orthogonal a data point is, the closer it should be to being released from the database in that form. If on every theoretical version of your site your values A and B are rendered A * B, that should be returned by your database as A * B and never calculated client side.
数据点越正交,就越接近以该形式从数据库中释放。如果在您网站的每个理论版本上,您的值A和B都呈现为A * B,那么您的数据库应该返回A * B并且从不计算客户端。
Let's say you have something that's format heavy like a date. Sometimes you have short dates, long dates, English dates... One pure form should be returned from the database and then that should be formatted in PHP.
假设你的格式像日期一样重。有时你有短日期,长日期,英语日期...应该从数据库返回一个纯表单,然后应该用PHP格式化。
So the orthogonality point works in reverse as well. The more dynamic a data point is in its representation/display, the more it should be handled client side. If a string A is always taken as a substring of the first six characters, then have that be returned from the database as pre-substring'ed. If the length of the substring depends on some factor, like six for mobile and ten for your web app, then return the larger string from the database and format it at run time using PHP.
所以正交点也是相反的。数据点在其表示/显示中越动态,客户端应该处理得越多。如果字符串A始终被视为前六个字符的子字符串,则将其作为pre-substring'ed从数据库返回。如果子字符串的长度取决于某个因素,例如6个用于移动设备,10个用于您的Web应用程序,则从数据库返回较大的字符串并使用PHP在运行时格式化它。
#2
Usually, data formatting is better done on client side, especially culture-specific formatting.
通常,数据格式化最好在客户端完成,尤其是特定于文化的格式化。
Dynamic pivoting (i. e. variable columns) is also an example of what is better done on client side
动态旋转(即变量列)也是在客户端更好地完成的示例
When it comes to string manipulation and dynamic arrays, PHP is far more powerful than any RDBMS I'm aware of.
在字符串操作和动态数组方面,PHP比我所知的任何RDBMS都强大得多。
However, data formatting can use additional data which is also kept in the database. Like, the coloring info for each row can be stored in additional table.
但是,数据格式化可以使用也保存在数据库中的其他数据。比如,每行的着色信息可以存储在附加表中。
You should then correspond the color to each row on database side, but wrap it into the tags on PHP side.
然后,您应该将颜色与数据库端的每一行对应,但将其包装到PHP端的标记中。
The rule of thumb is: retrieve everything you need for formatting in as few database round-trips as possible, then do the formatting itself on the client side.
经验法则是:在尽可能少的数据库往返中检索格式化所需的所有内容,然后在客户端进行格式化。
#3
I believe in returning the data pretty much as-is from the database and letting it be formatted on the front-end instead. I don't stick to it religously, but in general I think it's better as it provides greater flexibility - e.g. 1 sproc can service n different requirements for data, each of which can format the data as each individually needs. Otherwise, you end up either with multiple queries returning the same data with slightly different formatting from the DB (from a SQL Server point of view, thus reducing execution plan caching benefits - therefore negative impact on performance).
我相信几乎从数据库中返回数据并让它在前端进行格式化。我并不坚持宗教,但总的来说我觉得它更好,因为它提供了更大的灵活性 - 例如1个sproc可以满足不同的数据要求,每个数据都可以根据每个需求格式化数据。否则,您最终要么使用多个查询返回相同的数据,格式与DB略有不同(从SQL Server的角度来看,从而减少执行计划缓存的好处 - 因此对性能产生负面影响)。
#4
Leave output formatting to the web server
将输出格式保留为Web服务器