这种方法对于提高 MySQL 性能是好还是坏？
我正在构建一个电子商务 CMS，在管理部分我想显示订单总数、所述订单的总收入，并分别显示每个类别的总计。
我可以通过查询各种表并计算总和来做到这一点，但这是否是一个更好的方法：
相反，我正在考虑有一个看起来像这样的表（并且只有一条记录）：
id | total_orders | total_earnings | toy_cat_sales | apparel_cat_sales | etc...
--------------------------------------------------------------------------------
1 | 10 | 10034 | 4 | 6 | etc...
现在每次购买时我可以更新此记录，例如，当发出新的玩具订单时，我可以更新 toy_cat_sales 列以及total_orders 和total_earnings 列以反映新的购买。
在实际的查找过程中，查询这个表并仅显示其值显然比对可能有数万条记录的多个表执行计数和计算要快得多。
但这种方法总体上值得吗？我确实知道只有一名管理员和许多客户。与管理员在后端检查这些统计数据相比，订单也会发生更多。因此，如果实施第二种方法，更新将比管理端的查找更多。
我不是 MySQL 专家，所以这里的专家会做什么，我就这么做。
I am building an ecommerce CMS, in the admin section I want to display the total number of orders made, total earnings from said orders, and also display the totals from each category individually.
I can do this by querying various tables and counting up the sums, but would this be a better way:
Instead I am thinking of having a table that looks like this (and has just one record):
id | total_orders | total_earnings | toy_cat_sales | apparel_cat_sales | etc...
--------------------------------------------------------------------------------
1 | 10 | 10034 | 4 | 6 | etc...
Now every time a purchase is made I can have this record be updated, for example when a new toy order is made I can update the toy_cat_sales column and also the total_orders and total_earnings columns to reflect the new purchase.
During the actual looking up process, querying this one table and just displaying it's values would obvisly be much faster than performing counts and calculations on multiple tables with possibly tens of thousands of records.
But is this approach worth it overall? I do know that there will be just one admin and many customers. Orders will take place a lot more too as compared to the admin checking these stats in the back end. So the updates will take place a lot more than the lookups on the admin side if the 2nd approach is implemented.
I'm no MySQL expert so what would the experts here do, and I will just go with that.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这可能不是一个好主意。
虽然它会使一些查询更简单/更快，但每次插入都是额外的工作。更不用说保持更新的新编程要求了。当您承认“订单将会发生更多”时，您似乎意识到在最后一段中这是不值得的。
这种单行表解决方案的可扩展性也不是很好。每个类别都需要自己的列，这需要更改表。
我认为最好的解决方案就是简单地编写一个查询来在需要时查找这些统计信息。顶多是个风景。其中任何一个都可以包含与类别表的联接，从而使其在添加（或删除）类别方面具有更大的可扩展性。
即使您认为保留此元数据是值得的（很可能不值得），也有比此表更好的方法。
This is probably not a good idea.
While it will make some queries simpler/faster, it is extra work on every insert. Not to mention the new programming requirement of keeping this updated. You seem to realize that this is not worth it in your last paragraph when you acknowledge 'Orders will take place a lot more'
This single-row table solution is not very extensible either. Every category needs its own COLUMN, something that requires altering a table.
I think your best solution is simply writing a query to find these stats when needed. At most a view. Either of these can be made to include a join with your categories table, making it far more extensible for the addition (or removal) of categories.
Even if you decide that keeping this metadata is worthwhile (which it most likely is not), there are better ways to do it than this table.
使用这种方法遇到的问题是，每次携带新产品时，都必须向该表添加新列。你真的不想这样做。
您最好使用产品表（其中每一行都是一个产品）、一个订单表和一个orders_products 表，该表具有订单和产品的 fk 以将特定订单中的每个产品与该订单联系起来。命令。然后在需要时将所有内容记录下来。与使用上述想法进行维护所浪费的时间相比，计算机执行此数学运算所需的时间微不足道，尤其是当您开始向该表添加新列并且其中有数百万行时，这需要一些时间。
The problem you run into with this approach is that you have to add a new column to that table every time a new product is carried. You really don't want to do that.
You'd be much better off with something along the lines of a products table where each row is a product, an orders table, and an orders_products table that has an fk of the order and product to tie each product from a specific order to that order. Then just tally everything up when needed. The time required for a computer to do that math is nothing compared to the time you'll waste with maintenance with the idea above, especially once you start adding new columns to that table and it has millions of rows in it, which takes some time.