多个表还是只有一个大表？ [英] Multiple Tables or just one big one?

问题描述

嘿伙计们，

我正在制作一个在线游戏，其中一个问题是：显然我每个用户有很多变量，但大多数页面上可能需要一半，然后其他只在选择的页面上需要。在需要任何变量的每个页面的加载中，我连接到数据库并获取最新结果以显示给用户。


当我连接它时连接到一个大表并且经常不使用大多数变量。所以我的问题是，每个页面有几个较小的表并连接几个小表，或者将它作为一个大表更好吗？


或者用另一种方式，更多的资源用于连接更大的表，还是更多的资源用于连接多个表？

Hey guys,
I am making an online game and one of the issues I have is this: Obviously I have a lot of variables per user, however probably half are needed on most pages, and then the rest are only needed on select pages. On the load of every page where any variables are needed I am connecting to the database and getting the latest results to display for the user.

When I connect it is connecting to a big table and not using most variables there very often. So my question is, would it be better to have a few smaller tables and connect to several small ones per page, or leave it as one big one?

Or to put it another way, are more resources used connecting to a bigger table, or are more resources used connecting to multiple tables?

推荐答案

您需要考虑的最重要的事情是正常化。您的表格需要采用标准化形式，以便不会出现数据冗余和更好的性能。


检查规范化及其结构

The most important thing you need to take under consideration is the Normalization. Your tables needs to be in Normalised form so that there is no data redunduncy and better performance.

Check Normalization and its Structure

您需要考虑的最重要的事情是正常化。您的表格需要采用标准化格式，以便没有数据冗余和更好的性能。


检查规范化及其结构

链接不起作用。

The link doesn''t work.

基本上它从单个表的一行获取所有数据比从许多表中获取数据的部分数据更快。我的意思是通过连接或多个查询获取的部分数据。另一方面，如果您需要经常更新数据并且只需要更新某些值，则更新会慢得多。


最好的办法是将这些经常变化的值保存在小型的内存映射表，而在较大的表中存储更常量和较少使用的数据。


游戏过程中没有变化的数据在启动过程中只能读取一次，并保存在程序服务器的缓存中。


如果如果您经常更改数据会出现性能问题，那么您可以使用断点，这意味着所有更改都在程序服务器的内存中完成，并且每隔5分钟左右就会将数据写入数据库。因此，如果游戏崩溃，您可以从这一点恢复。我的意思是它是一个游戏，而不是一个需要完全避免数据丢失的财务应用程序。

basically it''s faster to fetch all data from one row of a single table than parts of data from many tables. I mean parts of data fetched either by join or by multiple queries. On the other side, updating would be much slower if you need to update data frequently and you need to update only some values.

The best way is to keep these frequently changing values inside a small, memory-mapped table, whereas you store the more constant and less used data in a big table.

Data that does not change during the game can be read only once during startup and kept into the cache of your program server.

If you have performance problems with frequently changing data, then you can use breakpoints, that means all changes are done inside the memory of your program server, and every 5 minutes or so it writes the data to the database. So if the game crashes, you can restore from this point. I mean it''s a game and not a financial application where loss of data needs to be avoided at all.

这篇关于多个表还是只有一个大表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！