MySQL-动态计算字段与存储计算数据 [英] MySQL - Calculating fields on the fly vs storing calculated data
问题描述
很抱歉，以前是否有人问过这个问题，但是我似乎找不到解决有关即时计算与将字段存储在数据库中的问题的答案.
I apologise if this has been asked before, but I can't seem to find an answer to a question that I have about calculating on the fly vs storing fields in a database.
我阅读了几篇文章，建议最好在可能的情况下进行计算，但是我想知道这是否仍然适用于以下两个示例.
I read a few articles that suggested it was preferable to calculate when you can, but I would just like to know if that still applies to the following 2 examples.
示例1.假设您要存储与汽车有关的数据.您以升为单位存储油箱尺寸，以及每百公里使用多少公升油.您还想知道它可以行驶多少公里，这可以根据油箱的大小和经济性来计算.我看到了两种方法:
Example 1. Say you are storing data relating to a car. You store the fuel tank size in litres, and how many litres it uses per 100km. You also want to know how many KMs it can travel, which can be calculated from the tank size and economy. I see 2 ways of doing this:
- 添加或更新汽车时，计算KM的数量并将其作为静态字段存储在数据库中.
- 每次访问汽车时，都要实时计算KM的数量.
因为汽车经济性/油箱尺寸没有变化(尽管可以编辑)，所以KM是一个非常静态的值.我不明白为什么每次访问汽车时都要进行计算.这样会不会浪费CPU时间，而不是简单地将其存储在数据库的单独字段中并仅在添加或更新汽车时进行计算?
Because the cars economy/tank size doesn't change (although it could be edited), the KMs is a pretty static value. I don't see why we would calculate it every single time the car is accessed. Wouldn't this waste cpu time as opposed to simply storing it in a separate field in the database and calculating only when a car is added or updated?
我的下一个示例几乎是一个完全不同的问题(但在同一主题上)，涉及计数孩子.
My next example, which is almost an entirely different question (but on the same topic), relates to counting children.
假设我们有一个包含类别和项目的应用程序.我们有一个视图，其中显示所有类别，以及每个类别中所有项目的计数.再次，我想知道有什么更好的方法.要执行MySQL查询来每次访问该页面时对每个类别中的所有项目进行计数?还是将计数存储在类别表的字段中，并在添加/删除项目时更新?
Let's say we have a app which has categories and items. We have a view where we display all the categories, and a count of all the items inside each category. Again, I'm wondering what's better. To perform a MySQL query to count all the items in each category every single time the page is accessed? Or store the count in a field in the categories table and update when an item is added / deleted?
我知道存储可以计算的任何内容都是多余的，但是我担心计算字段或计算记录可能会慢于将数据存储在字段中.如果不是，请让我知道，我只想了解何时使用这两种方法.在小范围内，我想这都没有关系，但是像Facebook这样的应用程序，每次有人查看您的个人资料时，它们是否真的会计算您拥有的朋友数量，还是只是将其存储为字段?
I know it is redundant to store anything that can be calculated, but I worry that calculating fields or counting records might be slow as opposed to storing the data in a field. If it's not then please let me know, I just want to learn about when to use either method. On a small scale I guess it wouldn't matter either way, but apps like Facebook, would they really count the amount of friends you have every time someone views your profile or would they just store it as a field?
我希望能对这两种情况做出的任何反应，以及任何可以解释计算与存储的好处的资源.
I'd appreciate any responses to both of these scenarios, and any resource that might explain the benefits of calculating vs storing.
预先感谢
基督徒
推荐答案
要注意的一件事是您使用数据的方式. 如果多个应用程序或应用程序的多个层(可能是同一应用程序中的旧代码和新代码)正在访问数据，则可以通过在数据库中进行预先计算来降低计算错误的风险.这样，无论哪个应用程序请求它，您计算出的数据都将始终相同.
One thing to notice is the way you use your data. If several applications, or several layers of your application (maybe old code and new code in the same app) is accessing your data you'll reduce the risk of errors in computing by pre-calculating in the database. Then your calculated data will always be the same, no matter which application is requesting it.
对于您的第一个示例，没有理由没有一天某人必须更改您的KM的计算方式.我会将其存储在数据库中(通过触发器或通过插入/更新中的PHP进行存储-因为MySQl触发器是……好吧……不如某些其他DB触发器那么好).
For your first example, there is no reason that someone someday will have to change the way your KMs will need to be computed. I would store it in database (via triggers or via PHP on the insert/update -- because MySQl triggers are... well they are... not as good as some other DB triggers).
现在，如果我们以您的第二个示例为例，则实际上不确定是否有人会希望有一天在该类别计算上添加一些过滤器.例如，只带2到5之间的子级.那么您所有的预先计算结果都将无济于事. 如果您需要对这些内容进行一些优化和缓存，则可能更多是您需要的应用程序层缓存，例如memcache或存储在缓存表中的预先计算的结果.但是此缓存是应用程序缓存，它在某种程度上与您的应用程序参数相关(使用不同过滤器的请求将使用缓存中的不同记录).
Now if we taking your second example it's really not sure someone will not want some day to add some filters on that categories computing. For example, take only children which are between 2 and 5. Then all your pre-computed results serves nothing. If you need some optimizations and caches of theses things it's maybe more an application-layer cache you would need, something like memcache, or pre-computed results stored in a cache table. But this cache is an application cache, which is related in a certain way on your application parameters (requests with different filters would use a different record in the cache).
请注意，使用MySQl，您还可以获得很好的查询缓存，这将防止对同一查询进行过多的计算.
Note that with MySQl you've got as well a nice query cache which will prevent the same query to be computed too much.
这篇关于MySQL-动态计算字段与存储计算数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
